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>
分享到:
閱讀更多 現世碼農 的文章
