go语言DB通用查询实现解析、全程高能

已于 2024-08-30 16:17:35 修改
阅读量1k 收藏 15
点赞数 28
分类专栏： go go实战全家桶 文章标签： golang 数据库 前端
于 2024-08-30 11:58:12 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/leijmdas/article/details/141714288
版权

开源：

goweb: https://gitee.com/ichub/goweb/settings#index

测试用例

func Test018_QueryShop(t *testing.T) {

    var dbRequest = Default()
    dbRequest.TableName = "contact_shop"
    dbRequest.SetPageSize(2).OrderByAsc("id")
    dbRequest.FieldsName = "id_10,id,name"
    var result = dbRequest.GeneralQuery()

    goutils.Info(result)
    assert.Equal(t, 200, result.Code)

}



测试结果



INFO[2024-08-830 11:49:10]D:/go-ichub/git.ichub.com/webcli120/goconfig/base/goutils/go_log.go:65 git.ichub.com/general/webcli120/goconfig/base/goutils.Info() [{
     "code": 200,
     "msg": "成功",
     "data": [
          {
               "name": "万1商户",
               "id_10": 0,
               "id": 722612810457022465
          },
          {
               "id_10": 0,
               "id": 722622552951128065,
               "name": "杨1商户"
          }
     ],
     "total": 621,
     "page_size": 2,
     "current": 1
}] 

代码

func (this *PageDbRequest) GeneralQuery() *page.PageResult {

    if pageResult := this.InitFields(); pageResult != nil {
       return pageResult
    }

    count, err := this.CountTable(this.TableName)
    if err != nil {
       return page.NewPageResultError(err.Error())
    }
    var pageResult = page.PageResultOf(this.PageRequest)
    pageResult.Total = count
    if count == 0 {
       return pageResult
    }

    var ret = this.FindRecords(pageResult)
    if ret.Code != 200 {
       return ret

    }
    if this.IfSubTable() {
       this.QuerySubTable(pageResult.Data.([]map[string]interface{}))
    }
    pageResult.PageSize = this.PageSize
    pageResult.PageCurrent = this.PageCurrent
    return pageResult

}

count

func (this *PageRequest) CountTable(table string) (int, error) {
    dbc := this.GetDB().Table(table)
    dbc = this.BuildWhere(dbc).Offset(0).Limit(1)
    var count int
    if err := dbc.Count(&count).Error; err != nil {
       goutils.Error(err)
       return 0, err
    }

    return count, nil

}

buildWhere

func (this *PageRequest) BuildWhere(dbc *gorm.DB) *gorm.DB {

    this.InitPage()
    if this.Fields == nil {
       return dbc
    }
    for _, field := range this.Fields {
       this.TransOpType(field)
       //if notbetween noin notlike
       if field.OpType == base.OpSign[base.Between] {
          dbc = dbc.Where(fmt.Sprintf("%s BETWEEN ? and ?", field.Field),
             field.Values[0], field.Values[1])
       }
       if field.OpType == base.OpSign[base.NotBetween] {
          dbc = dbc.Where(fmt.Sprintf("%s Not BETWEEN ? and ?", field.Field),
             field.Values[0], field.Values[1])
       }

       if field.OpType == base.OpSign[base.Ge] {
          dbc = dbc.Where(fmt.Sprintf("%s >= ?", field.Field), field.Values[0])
       }
       if field.OpType == base.OpSign[base.Gt] {
          dbc = dbc.Where(fmt.Sprintf("%s > ?", field.Field), field.Values[0])
       }
       if field.OpType == base.OpSign[base.Le] {
          dbc = dbc.Where(fmt.Sprintf("%s <= ?", field.Field), field.Values[0])
       }
       if field.OpType == base.OpSign[base.Lt] {
          dbc = dbc.Where(fmt.Sprintf("%s < ?", field.Field), field.Values[0])
       }
       if field.OpType == base.OpSign[base.Eq] {
          dbc = dbc.Where(fmt.Sprintf("%s = ?", field.Field), field.Values[0])
       }
       if field.OpType == base.OpSign[base.Ne] {
          dbc = dbc.Where(fmt.Sprintf("%s != ?", field.Field), field.Values[0])
       }
       if field.OpType == base.OpSign[base.In] {

          dbc = dbc.Where(fmt.Sprintf("%s in (%s)", field.Field, field.Values2InStr()))
       }
       if field.OpType == base.OpSign[base.NotIn] {
          dbc = dbc.Where(fmt.Sprintf("%s not in (%s)", field.Field, field.Values2InStr()))
       }
       if field.OpType == base.OpSign[base.Like] {

          dbc = dbc.Where(fmt.Sprintf("%s like ?", field.Field), this.Value2Like(field.Values[0]))
       }
       if field.OpType == base.OpSign[base.NotLike] {
          dbc = dbc.Where(fmt.Sprintf("%s not like ?", field.Field), this.Value2Like(field.Values[0]))
       }
       if field.OpType == base.OpSign[base.IsNull] {
          dbc = dbc.Where(fmt.Sprintf("%s is null", field.Field))
       }
       if field.OpType == base.OpSign[base.IsNotNull] {
          dbc = dbc.Where(fmt.Sprintf("%s is not null", field.Field))
       }
    }
    return dbc
}

FindRecords

func (this *PageDbRequest) FindRecords(result *page.PageResult) *page.PageResult {

    var db = this.FindTable()
    rows, errs := db.Rows()
    if errs != nil {
       goutils.Error(errs)
       return page.NewPageResultError(errs.Error())
    }
    defer func() {
       if err := rows.Close(); err != nil {
          logrus.Error(err)
       }
    }()

    var records = dto.NewIchubRecords(this.DbClientDto)

    var err = records.TableFields(this.TableName, this.FieldsName).ScanRows(this.TimeToInt, rows)
    if err != nil {
       goutils.Error(err)
       return page.NewPageResultError(err.Error())
    }
    result.Data = records.Records
    return result
}

ScanRows

func (ir *IchubRecords) ScanRows(timeToInt bool, sqlRows *sql.Rows) error {
    ir.TimeToInt = timeToInt

    for sqlRows.Next() {
       var result, row = ir.NewIchubFields2Result()
       if err := sqlRows.Scan(result...); err != nil {
          logrus.Error(err)
          return err
       }
       ir.AppendRow(row)
    }
    ir.PtrRow2Result()
    ir.Count = len(ir.Records)
    return nil
}
func (ir *IchubRecords) NewIchubFields2Result() ([]interface{}, []*IchubField) {
    var ichubFields = ir.MakeIchubFields()
    var result = []interface{}{}
    for _, v := range ichubFields {
       result = append(result, &v.Value)
    }
    return result, ichubFields
}


func (ir *IchubRecords) MakeIchubFields() []*IchubField {
    var IchubFields = []*IchubField{}

    for _, field := range ir.Fields {
       var ichubField = MakeIchubField(ir.DbClientDto, ir.TableName, field)

       IchubFields = append(IchubFields, ichubField)
    }
    return IchubFields
}

func MakeIchubField(dbcli *baseconfig.DbClientDto, table, field string) *IchubField {
    field = strings.TrimSpace(field)

    var goField = FindGoField(dbcli, table, field)
    var goType = goField.GoType
    field = strings.TrimSpace(field)

    if goType == "bool" {
       return NewIchubField(field, goType, *new(sql.NullBool)).SetFieldType(goField.ColumnType)
    }
    if goType == "string" {
       return NewIchubField(field, goType, *new(sql.NullString)).SetFieldType(goField.ColumnType)
    }
    if goType == "uint8" {
       return NewIchubField(field, goType, *new(sql.NullByte)).SetFieldType(goField.ColumnType)
    }
    if goType == "[]uint8" {
       return NewIchubField(field, goType, *new([]int8)).SetFieldType(goField.ColumnType)
    }
    if goType == "uint16" {
       return NewIchubField(field, goType, *new(uint16)).SetFieldType(goField.ColumnType)
    }
    if goType == "uint32" {
       return NewIchubField(field, goType, *new(uint32)).SetFieldType(goField.ColumnType)
    }
    if goType == "uint64" {
       return NewIchubField(field, goType, *new(uint64)).SetFieldType(goField.ColumnType)
    }
    if goType == "int" {
       return NewIchubField(field, goType, *new(sql.NullInt64)).SetFieldType(goField.ColumnType)
    }
    if goType == "int8" {
       return NewIchubField(field, goType, *new(int8)).SetFieldType(goField.ColumnType)
    }
    if goType == "int16" {
       return NewIchubField(field, goType, *new(sql.NullInt16)).SetFieldType(goField.ColumnType)
    }
    if goType == "int32" {
       return NewIchubField(field, goType, *new(sql.NullInt32)).SetFieldType(goField.ColumnType)
    }
    if goType == "int64" {
       return NewIchubField(field, goType, *new(sql.NullInt64)).SetFieldType(goField.ColumnType)
    }
    if goType == "float32" {
       return NewIchubField(field, goType, *new(float32)).SetFieldType(goField.ColumnType)
    }
    if goType == "float64" {
       return NewIchubField(field, goType, *new(sql.NullFloat64)).SetFieldType(goField.ColumnType)
    }

    if goType == "time.Time" {
       return NewIchubField(field, goType, *new(sql.NullTime)).SetFieldType(goField.ColumnType)
    }

    return NewIchubField(field, goType, *new(string)).SetFieldType("string")
}

func FindGoField(dbcli *baseconfig.DbClientDto, tableName, fieldName string) *MetaGoField {
    fieldName = strings.TrimSpace(fieldName)

    var metatable, ok = InstMetadataCache.CacheGet(dbcli.CacheKey() + tableName) //var metatable = FindMapTableGoDict(tableName)
    if !ok {
       logrus.Error("not found metatable ", tableName)
       return nil
    }

    var goField = metatable.FindGoField(fieldName)
    if goField == nil {
       goutils.Error("not found goField ", fieldName)
       return nil
    }
    return goField
}

PtrField2Value

func (ir *IchubRecords) PtrRow2Result() {

    for _, row := range ir.Rows {
       var record = make(map[string]interface{})
       for _, v := range row {
          record[v.Field] = ir.PtrField2Value(v)
       }
       ir.Records = append(ir.Records, record)

    }

}
func (ir *IchubRecords) PtrField2Value(field *IchubField) interface{} {

    var ptr = field.Value
    var checkType = ir.CheckType(field)
    if ptr == nil {
       return ir.PtrNilField2Value(field)
    }

    if checkType == "*int" {
       return ptr.(*int)
    }
    if checkType == "*bool" {
       return ptr.(*bool)
    }
    if checkType == "*uint8" {
       return ptr.(*uint8)
    }
    if checkType == "*[]uint8" {
       var s = baseutils.Any2Str(ptr)
       return &s
    }
    if checkType == "*int8" {
       return ptr.(*int8)
    }
    if checkType == "*int16" {
       return ptr.(*int16)
    }
    if checkType == "*int32" {
       return ptr.(*int32)
    }
    if checkType == "*uint32" {
       return ptr.(*uint32)
    }
    if checkType == "*uint64" {
       return ptr.(*uint64)
    }
    if checkType == "*int64" {
       return ptr.(*int64)
    }
    if checkType == "*float32" {
       return ptr.(*float32)
    }
    if checkType == "*float64" {
       return ptr.(*float64)
    }
    if checkType == "*string" {
       return ptr.(*string)
    }

    if checkType == "*time.Time" {
       return ir.PtrTimeField2Value(field)
    }

    if checkType == "int" {
       return ptr.(int)
    }
    if checkType == "bool" {
       return ptr.(bool)
    }
    if checkType == "uint8" {
       return ptr.(uint8)
    }
    if checkType == "[]uint8" {
       return baseutils.Any2Str(ptr)
    }
    if checkType == "int8" {
       return ptr.(int8)
    }
    if checkType == "int16" {
       return ptr.(int16)
    }
    if checkType == "int32" {

       return ptr.(*sql.NullInt32)
    }
    if checkType == "uint32" {
       return ptr.(uint32)
    }
    if checkType == "uint64" {
       return ptr.(uint64)
    }
    if checkType == "int64" {
       return ptr.(int64)
    }
    if checkType == "float32" {
       return ptr.(float32)
    }
    if checkType == "float64" {
       return ptr.(float64)
    }
    if checkType == "string" {
       return ptr.(string)
    }

    if checkType == "time.Time" {
       return ir.TimeField2Value(field)

    }

    return gconv.String(ptr)
}
func (ir *IchubRecords) PtrNilField2Value(field *IchubField) interface{} {

    var ptr = field.Value
    var checkType = ir.CheckType(field)

    if checkType == "*int" {
       return new(int)

    }
    if checkType == "*bool" {
       return new(bool)

    }
    if checkType == "*uint8" {

       return new(uint8)
    }
    if checkType == "*[]uint8" {

       return new([]uint8)

    }
    if checkType == "*int8" {
       return new(int8)
    }
    if checkType == "*int16" {
       return new(int16)
    }
    if checkType == "*int32" {
       return new(int32)
    }
    if checkType == "*uint32" {
       return new(uint32)
    }
    if checkType == "*uint64" {
       return uint64(0)
    }
    if checkType == "*int64" {
       return new(int64)
    }
    if checkType == "*float32" {
       return new(float32)
    }
    if checkType == "*float64" {
       return new(float64)
    }
    if checkType == "*string" {
       return new(string)
    }

    if checkType == "*time.Time" {
       return ir.PtrTimeField2Value(field)
    }

    if checkType == "int" {
       return 0

    }
    if checkType == "bool" {
       return false

    }
    if checkType == "uint8" {
       return uint8(0)
    }
    if checkType == "[]uint8" {
       return ""
    }
    if checkType == "int8" {
       return int8(0)
    }
    if checkType == "int16" {
       return int16(0)
    }
    if checkType == "int32" {
       return int32(0)
    }
    if checkType == "uint32" {
       return uint32(0)
    }
    if checkType == "uint64" {
       return uint64(0)
    }
    if checkType == "int64" {
       return int64(0)
    }
    if checkType == "float32" {
       return float32(0)
    }
    if checkType == "float64" {
       return float64(0)
    }
    if checkType == "string" {
       if field.IfFieldNumber64() {
          return "0"
       }
       if field.IfFieldNumber() {
          return 0
       }
       return ""

    }
    if checkType == "time.Time" {
       return ir.TimeField2Value(field)
    }
    return gconv.String(ptr)
}

CheckType

func (ir *IchubRecords) CheckType(field *IchubField) string {
    var i = field.Value
    switch i.(type) {
    case *string:
       return "*string"
    case *bool:
       return "*bool"
    case *int:
       return "*int"
    case *int8:
       return "*int8"
    case *int16:
       return "*int16"
    case *int32:
       return "*int32"
    case *int64:
       return "*int64"
    case *byte:
       return "*byte"
    case *time.Time:
       return "*time.Time"

    case *[]uint8:
       return "*[]uint8"

    case *uint16:
       return "*uint16"
    case *uint32:
       return "*uint32"
    case *uint64:
       return "*uint64"
    case *float32:
       return "*float32"
    case *float64:
       return "*float64"

    case time.Time:
       return "time.Time"
    case string:
       return "string"
    case bool:
       return "bool"
    case int:
       return "int"
    case []uint8:
       return "[]uint8"
    case int8:
       return "int8"
    case int16:
       return "int16"
    case int32:
       return "int32"
    case int64:
       return "int64"
    case uint8:
       return "uint8"
    case uint16:
       return "uint16"
    case uint32:
       return "uint32"
    case uint64:
       return "uint64"
    case float32:
       return "float32"
    case float64:
       return "float64"
    }

    return field.GoType
}
leijmdas
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

leijmdas

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值