go-xorm封装公共CURD类
$ 前言
- go-xorm是一个简单而强大的Go语言ORM库. 通过它可以使数据库操作非常简便。
$ 配置目标
- 封装一个公共的父级操作类,
- 通过继承可让子类带有简单的操作CRUD分页查询数据库的能力
- 可减少很多冗余,重复相似性很高的查询的代码编写
- 提高代码整洁性,可维护性
app.ini配置
<code>[mysql]
url=root:abc.123@tcp(localhost:3306)/foot?charset=utf8
maxIdle=10
maxConn=50
/<code>
utils工具类源码(用于读取配置信息)
<code>package utils
import (
\t"gopkg.in/ini.v1"
\t"tesou.io/platform/foot-parent/foot-api/common/base"
)
var (
\t//配置信息
\tiniFile *ini.File
)
func init() {
\tfile, e := ini.Load("conf/app.ini")
\tif e != nil {
\t\tbase.Log.Info("Fail to load conf/app.ini" + e.Error())
\t\treturn
\t}
\tiniFile = file
}
func GetSection(sectionName string) *ini.Section {
\tsection, e := iniFile.GetSection(sectionName)
\tif e != nil {
\t\tbase.Log.Info("未找到对应的配置信息:" + sectionName + e.Error())
\t\treturn nil
\t}
\treturn section
}
func GetSectionMap(sectionName string) map[string]string {
\tsection, e := iniFile.GetSection(sectionName)
\tif e != nil {
\t\tbase.Log.Info("未找到对应的配置信息:" + sectionName + e.Error())
\t\treturn nil
\t}
\tsection_map := make(map[string]string, 0)
\tfor _, e := range section.Keys() {
\t\tsection_map[e.Name()] = e.Value()
\t}
\treturn section_map
}
func GetVal(sectionName string, key string) string {
\tvar temp_val string
\tsection := GetSection(sectionName)
\tif nil != section {
\t\ttemp_val = section.Key(key).Value()
\t}
\treturn temp_val;
}
/<code>
Page源码(分页结构体)
<code>package pojo
type Page struct {
\t//记录总数
\tCounts int64
\t//每页显示记录数
\tPageSize int64
\t//总页数
\tTotalPage int64
\t//当前页
\tCurPage int64
\t//页面显示开始记录数
\tFirstResult int64
\t//页面显示最后记录数
\tLastResult int64
\t//排序类型
\tOrderType string
\t//排序名称
\tOrderName string
}
func (this *Page) Build(counts int64, pageSize int64) {
\tthis.Counts = counts
\tthis.PageSize = pageSize
\tif (counts%pageSize == 0) {
\t\tthis.TotalPage = this.Counts / this.PageSize
\t} else {
\t\tthis.TotalPage = this.Counts/this.PageSize + 1
\t}
}
func (this *Page) GetCounts() int64 {
\treturn this.Counts
}
/**
* Counts
* the Counts to set
*/
func (this *Page) SetCounts(counts int64) {
\t// 计算所有的页面数
\tthis.Counts = counts
\t// this.TotalPage = (int)Math.ceil((this.Counts + this.perPageSize - 1)
\t// / this.perPageSize)
\tif (counts%this.PageSize == 0) {
\t\tthis.TotalPage = this.Counts / this.PageSize
\t} else {
\t\tthis.TotalPage = this.Counts/this.PageSize + 1
\t}
}
func (this *Page) GetPageSize() int64 {
\treturn this.PageSize
}
func (this *Page) SetPageSize(pageSize int64) {
\tthis.PageSize = pageSize
}
/**
* the TotalPage
*/
func (this *Page) GetTotalPage() int64 {
\tif this.TotalPage < 1 {
\t\treturn 1
\t}
\treturn this.TotalPage
}
/**
* TotalPage
* the TotalPage to set
*/
func (this *Page) SetTotalPage(totalPage int64) {
\tthis.TotalPage = totalPage
}
func (this *Page) GetCurPage() int64 {
\treturn this.CurPage
}
func (this *Page) SetCurPage(curPage int64) {
\tthis.CurPage = curPage
}
/**
* the FirstResult
*/
func (this *Page) GetFirstResult() int64 {
\ttemp := this.CurPage - 1
\tif (temp <= 0) {
\t\treturn 0
\t}
\tthis.FirstResult = (this.CurPage - 1) * this.PageSize
\treturn this.FirstResult
}
/**
* FirstResult
* the FirstResult to set
*/
func (this *Page) SetFirstResult(firstResult int64) {
\tthis.FirstResult = firstResult
}
/**
* the LastResult
*/
func (this *Page) GetLastResult() int64 {
\tthis.LastResult = this.FirstResult + this.PageSize
\treturn this.LastResult
}
/**
* LastResult
* the LastResult to set
*/
func (this *Page) SetLastResult(lastResult int64) {
\tthis.LastResult = lastResult
}
/**
* the OrderName
*/
func (this *Page) GetOrderName() string {
\treturn this.OrderName
}
/**
* OrderName
* the OrderName to set
*/
func (this *Page) SetOrderName(orderName string) {
\tthis.OrderName = orderName
}
/**
* the orderBy
*/
func (this *Page) getOrderType() string {
\treturn this.OrderType
}
/**
* orderBy
* the orderBy to set
*/
func (this *Page) SetOrderType(orderType string) {
\tthis.OrderType = orderType
}
/**
* the orderBy
*/
func (this *Page) GetOrderBy() string {
\tif len(this.GetOrderName()) <= 0 {
\t\treturn ""
\t}
\torderBy := " order by " + this.GetOrderName() + " " + this.getOrderType()
\treturn orderBy
}
/<code>
BaseService源码(公共的CRUD类)
<code>package mysql
import (
\t"container/list"
\t_ "github.com/go-sql-driver/mysql"
\t"github.com/go-xorm/core"
\t"github.com/go-xorm/xorm"
\t"gopkg.in/mgo.v2/bson"
\t"reflect"
\t"strconv"
\t"tesou.io/platform/foot-parent/foot-api/common/base"
\t"tesou.io/platform/foot-parent/foot-api/common/base/pojo"
\t"tesou.io/platform/foot-parent/foot-core/common/utils"
)
type BaseService struct {
}
var (
\tengine *xorm.Engine
)
func GetEngine() *xorm.Engine {
\tif nil == engine {
\t\tsetEngine()
\t}
\treturn engine
}
func ShowSQL(show bool) {
\tengine := GetEngine()
\tengine.ShowSQL(show)
\tengine.ShowExecTime(show)
}
func setEngine() *xorm.Engine {
\turl := utils.GetVal("mysql", "url")
\tmaxIdle, _ := strconv.Atoi(utils.GetVal("mysql", "maxIdle"))
\tmaxConn, _ := strconv.Atoi(utils.GetVal("mysql", "maxConn"))
\tvar err error
\tengine, err = xorm.NewEngine("mysql", url)
\tif nil != err {
\t\tbase.Log.Error("init" + err.Error())
\t}
\t//engine.ShowExecTime(true)
\t//则会在控制台打印出生成的SQL语句
\t//则会在控制台打印调试及以上的信息
\tengine.ShowSQL(true)
\t//engine.Logger().SetLevel(core.LOG_DEBUG)
\tengine.SetMaxIdleConns(maxIdle)
\tengine.SetMaxOpenConns(maxConn)
\ttbMapper := core.NewPrefixMapper(core.SnakeMapper{}, "t_")
\tengine.SetTableMapper(tbMapper)
\tengine.SetColumnMapper(core.SameMapper{})
\t/**
\t当使用了Distinct,Having,GroupBy方法将不会使用缓存
\t在Get或者Find时使用了Cols,Omit方法，则在开启缓存后此方法无效，系统仍旧会取出这个表中的所有字段。
\t在使用Exec方法执行了方法之后，可能会导致缓存与数据库不一致的地方。因此如果启用缓存，尽量避免使用Exec。
\t如果必须使用，则需要在使用了Exec之后调用ClearCache手动做缓存清除的工作。比如：
\tengine.Exec("update user set name = ? where id = ?", "xlw", 1)
\tengine.ClearCache(new(User))
\t*/
\t//cacher := xorm.NewLRUCacher(xorm.NewMemoryStore(), 999)
\t//engine.SetDefaultCacher(cacher)
\treturn engine
}
func init() {
\t//设置初始化数据库引擎
\tsetEngine()
}
func beforeModify(entity interface{}) {
\t//当前时间
\t//current_date := time.Now().Format("2006-01-02 15:04:05")
\t//默认更新者
\tdefault_user := "100000"
\t//对象操作
\tentity_value := reflect.ValueOf(entity).Elem()
\t/*//设置更新时间
\tfield_ModifyDate := entity_value.FieldByName("ModifyTime")
\tif field_ModifyDate.String() == "" {
\t\tfield_ModifyDate.SetString(current_date)
\t}*/
\t//设置更新者
\tfield_ModifyUser := entity_value.FieldByName("ModifyUser")
\tif field_ModifyUser.String() == "" {
\t\tfield_ModifyUser.SetString(default_user)
\t}
}
func beforeDelete(entity interface{}) {
\t//当前时间
\t//current_date := time.Now().Format("2006-01-02 15:04:05")
\t//默认删除者
\tdefault_user := "100000"
\t//对象操作
\tentity_value := reflect.ValueOf(entity).Elem()
\t/*//设置更新时间
\tfield_ModifyDate := entity_value.FieldByName("ModifyTime")
\tif field_ModifyDate.String() == "" {
\t\tfield_ModifyDate.SetString(current_date)
\t}*/
\t//设置删除者
\tfield_DeleteUser := entity_value.FieldByName("DeleteUser")
\tif field_DeleteUser.String() == "" {
\t\tfield_DeleteUser.SetString(default_user)
\t}
}
func beforeSave(entity interface{}) interface{} {
\t//当前时间
\t//current_date := time.Now().Format("2006-01-02 15:04:05")
\t//默认创建者
\tdefault_user := "100000"
\t//对象操作
\tentity_value := reflect.ValueOf(entity).Elem()
\t/*\t//设置创建时间 通过`xorm:"created"`配置
\t\tfield_CreateDate := entity_value.FieldByName("CreateTime")
\t\tif field_CreateDate.String() == "" {
\t\t\tfield_CreateDate.SetString(current_date)
\t\t}*/
\t//设置创建者
\tfield_CreateUser := entity_value.FieldByName("CreateUser")
\tif field_CreateUser.String() == "" {
\t\tfield_CreateUser.SetString(default_user)
\t}
\tbeforeModify(entity)
\tvar id interface{}
\t//设置id
\tfield_Id := entity_value.FieldByName("Id")
\tif field_Id.String() == "" {
\t\t//使用bson.NewObject作为主键
\t\tid = bson.NewObjectId().Hex()
\t\tfield_Id.Set(reflect.ValueOf(id))
\t}
\treturn id
}
func (this *BaseService) SaveOrModify(entity interface{}) {
\tb, err := engine.Exist(entity)
\tif nil != err {
\t\tbase.Log.Info("SaveOrModify:" + err.Error())
\t}
\tif b {
\t\tthis.Modify(entity)
\t} else {
\t\tthis.Save(entity)
\t}
}
func (this *BaseService) Save(entity interface{}) interface{} {
\tid := beforeSave(entity)
\t_, err := engine.InsertOne(entity)
\tif nil != err {
\t\tbase.Log.Info("Save:" + err.Error())
\t}
\treturn id
}
func (this *BaseService) SaveList(entitys []interface{}) *list.List {
\tif len(entitys) <= 0 {
\t\treturn nil
\t}
\tlist_ids := list.New()
\tfor _, v := range entitys {
\t\tid := beforeSave(v)
\t\tlist_ids.PushBack(id)
\t}
\t_, err := engine.Insert(entitys...)
\tif nil != err {
\t\tbase.Log.Info("SaveList:" + err.Error())
\t}
\treturn list_ids
}
func (this *BaseService) Del(entity interface{}) int64 {
\tbeforeDelete(entity)
\tentity_value := reflect.ValueOf(entity).Elem()
\tid_field := entity_value.FieldByName("Id")
\ti, err := engine.Id(id_field.Interface()).Delete(entity)
\tif err != nil {
\t\tbase.Log.Info("Del:", err)
\t}
\treturn i
}
func (this *BaseService) Modify(entity interface{}) int64 {
\tbeforeModify(entity)
\tentity_value := reflect.ValueOf(entity).Elem()
\tid_field := entity_value.FieldByName("Id")
\ti, err := engine.Id(id_field.Interface()).AllCols().Update(entity)
\tif err != nil {
\t\tbase.Log.Info("Modify:", err)
\t}
\treturn i
}
func (this *BaseService) ModifyList(entitys []interface{}) int64 {
\tif len(entitys) <= 0 {
\t\treturn 0
\t}
\t//i, err := engine.In("id",ids).Update(entitys)
\tfor _, v := range entitys {
\t\t//entity_value := reflect.ValueOf(v).Elem()
\t\t//id_field := entity_value.FieldByName("Id")
\t\tthis.Modify(v)
\t}
\treturn 1
}
func (this *BaseService) Exist(entity interface{}) bool {
\t//对象操作
\texist, err := engine.Exist(entity)
\tif nil != err {
\t\tbase.Log.Info("ExistByName:" + err.Error())
\t}
\treturn exist
}
func (this *BaseService) Find(entity interface{}) {
\tengine.Find(entity)
}
func (this *BaseService) FindBySQL(sql string, entity interface{}) {
\tengine.SQL(sql).Find(entity)
}
func (this *BaseService) FindAll(entity interface{}) {
\terr := engine.Find(entity)
\tif nil != err {
\t\tbase.Log.Info("FindAll: " + err.Error())
\t}
}
/**
分页查询
*/
func (this *BaseService) Page(v interface{}, page *pojo.Page, dataList interface{}) error {
\ttableName := engine.TableName(v)
\tsql := "select t.* from " + tableName + " t where 1=1 "
\treturn this.PageSql(sql, page, dataList)
}
/**
分页查询
*/
func (this *BaseService) PageSql(sql string, page *pojo.Page, dataList interface{}) error {
\t//声明结果变量
\tvar err error
\tvar counts int64
\t//获取总记录数处理
\tcountSql := " select count(1) from (" + sql + ") t"
\tcounts, err = engine.SQL(countSql).Count()
\tif nil != err {
\t\treturn err
\t} else {
\t\tpage.SetCounts(counts)
\t}
\t//排序处理
\torderBy := page.GetOrderBy()
\tif len(orderBy) > 0 {
\t\tsql += orderBy
\t}
\tsql += " limit " + strconv.FormatInt(page.GetFirstResult(), 10) + "," + strconv.FormatInt(page.GetPageSize(), 10)
\terr = engine.SQL(sql).Find(dataList)
\treturn err
}
/<code>
使用示例
<code>package service
import (
\t"tesou.io/platform/foot-parent/foot-api/common/base"
\t"tesou.io/platform/foot-parent/foot-api/module/match/pojo"
\t"tesou.io/platform/foot-parent/foot-core/common/base/service/mysql"
)
type MatchHisService struct {
//引入公共类
\tmysql.BaseService
}
func (this *MatchHisService) Exist(v *pojo.MatchHis) bool {
\thas, err := mysql.GetEngine().Table("`t_match_his`").Where(" `Id` = ? ", v.Id).Exist()
\tif err != nil {
\t\tbase.Log.Error("Exist", err)
\t}
\treturn has
}
func (this *MatchHisService) FindAll() []*pojo.MatchHis {
\tdataList := make([]*pojo.MatchHis, 0)
\tmysql.GetEngine().OrderBy("MatchDate").Find(&dataList)
\treturn dataList
}
func (this *MatchHisService) FindById(matchId string) *pojo.MatchHis {
\tdata := new(pojo.MatchHis)
\tdata.Id = matchId
\t_, err := mysql.GetEngine().Get(data)
\tif err != nil {
\t\tbase.Log.Error("FindById:", err)
\t}
\treturn data
}
func (this *MatchHisService) FindBySeason(season string) []*pojo.MatchLast {
\tsql_build := `
SELECT
la.*
FROM
foot.t_match_his la
WHERE 1=1
\t`
\tsql_build = sql_build + " AND la.MatchDate >= '" + season + "-01-01 00:00:00' AND la.MatchDate <= '" + season + "-12-31 23:59:59'"
\t//结果值
\tdataList := make([]*pojo.MatchLast, 0)
\t//执行查询
\tthis.FindBySQL(sql_build, &dataList)
\treturn dataList
}
/<code>
分享到:
閱讀更多 現世碼農 的文章
