Go中反射的利用(通用sql封装,字段拼接对应)
在后端接口开发中,往往需要针对某一张表写相对应的增删改查的sql方法,比如我们查询某张表的数据
func (r *DomainDao) GetHostsByModel(model *config.HostsModel, startNum int, pagesize int) ([]*config.HostsModel, error) { db := r.Db.GetDB() if db == nil { return nil, errors.New("db is nil") } result := make([]*config.HostsModel, 0) args := make([]interface{}, 0) strSql := "SELECT * FROM table WHERE 1=1 " if model != nil { if model.Gid != 0 { strSql += " AND gid=? " args = append(args, model.Gid) } if len(strings.Trim(model.CdnDomain, " ")) != 0 { strSql += " AND cdn_domain=? " args = append(args, model.CdnDomain) } if len(strings.Trim(model.CdnName, " ")) != 0 { strSql += " AND cdn_name=? " args = append(args, model.CdnName) } if model.CdnType != 0 { strSql += " AND cdn_type=? " args = append(args, model.CdnType) } if model.Master != 0 { strSql += " AND master=? " args = append(args, model.Master) } if model.Mode != 0 { strSql += " AND mode=? " args = append(args, model.Mode) } if model.AuthOutTime != 0 { strSql += " AND auth_out_time=? " args = append(args, model.AuthOutTime) } if model.Enable != 0 { strSql += " AND enable=? " args = append(args, model.Enable) } if len(strings.Trim(model.AuthKey, " ")) != 0 { strSql += " AND auth_key=? " args = append(args, model.AuthKey) } if len(strings.Trim(model.Reserve, " ")) != 0 { strSql += " AND reserve=? " args = append(args, model.Reserve) } } if startNum >= 0 && pagesize > 0 { strSql += " LIMIT ?,? " args = append(args, startNum) args = append(args, pagesize) } var err error if len(args) > 0 { _, err = db.ExecSelect(strSql, &result, args...) } else { _, err = db.ExecSelect(strSql, &result) } return result, err }
出于封装的目的,我们常常将查询条件动态封装拼接以满足我们不同的查询业务,但每张表的字段都是不同的,所有每张表都像这样写就显得很冗余低效了,所以需要对其再封装
通过反射进行封装:
//GetSelectTableSql 获取通用查询sql /** * @name: GetSelectTableSql * @Descripttion: 获取通用查询sql * @param {model} 表结构对应条件结构体 * @param {tableName} 表名称 * @param {startNum} 开始下标位置 * @param {pagesize} 条数 * @return {string} 拼装成的sql {[]interface{}} 动态参数 */ func (db *MySQL) GetSelectTableSql(model interface{}, tableName string, startNum int, pagesize int) (string, []interface{}) { strSql := " SELECT * FROM " + tableName + " WHERE 1=1 " args := make([]interface{}, 0) if model != nil { refValue := reflect.ValueOf(model) refType := reflect.TypeOf(model) fieldCount := refValue.NumField() for i := 0; i < fieldCount; i++ { fieldType := refType.Field(i) fieldValue := refValue.Field(i) gormTag := fieldType.Tag.Get("gorm") //对应的列名称(tag中定义) gormTag = strings.Replace(gormTag, "column:", "", -1) gormTags := strings.Split(gormTag, ";") if len(gormTags) > 0 { column := gormTags[0] isadd := false switch fieldType.Type.String() { case "string": if fieldValue.Len() > 0 { isadd = true } case "int", "int8", "int16", "int32", "int64": if fieldValue.Int() != 0 { isadd = true } case "time.Time": valTime := fieldValue.Interface().(time.Time) if !tool.CheckIsDefaultTime(valTime) { isadd = true } else { if column == "delete_at" { strSql += " AND delete_at IS NULL " } } } if isadd { strSql += " AND " + column + " = ? " args = append(args, fieldValue.Interface()) } } } } if startNum >= 0 && pagesize > 0 { strSql += " LIMIT ?,? " args = append(args, startNum) args = append(args, pagesize) } return strSql, args } //GetSelectTableCountSql 获取通用查询的数量sql /** * @name: GetSelectTableCountSql * @Descripttion: 获取通用查询的数量sql * @param {model} 表结构对应条件结构体 * @param {tableName} 表名称 * @return {string} 拼装成的sql {[]interface{}} 动态参数 */ func (db *MySQL) GetSelectTableCountSql(model interface{}, tableName string) (string, []interface{}) { strSql := " SELECT COUNT(1) FROM " + tableName + " WHERE 1=1 " args := make([]interface{}, 0) if model != nil { refValue := reflect.ValueOf(model) refType := reflect.TypeOf(model) fieldCount := refValue.NumField() for i := 0; i < fieldCount; i++ { fieldType := refType.Field(i) fieldValue := refValue.Field(i) gormTag := fieldType.Tag.Get("gorm") //对应的列名称(tag中定义) gormTag = strings.Replace(gormTag, "column:", "", -1) gormTags := strings.Split(gormTag, ";") if len(gormTags) > 0 { column := gormTags[0] isadd := false switch fieldType.Type.String() { case "string": if fieldValue.Len() > 0 { isadd = true } case "int", "int8", "int16", "int32", "int64": if fieldValue.Int() != 0 { isadd = true } case "time.Time": valTime := fieldValue.Interface().(time.Time) if !tool.CheckIsDefaultTime(valTime) { isadd = true } else { if column == "delete_at" { strSql += " AND delete_at IS NULL " } } } if isadd { strSql += " AND " + column + " = ? " args = append(args, fieldValue.Interface()) } } } } return strSql, args } //InsertTable 通用新增表 /** * @name: InsertTable * @Descripttion: 通用新增表 * @param {model} 表结构对应结构体数据 * @param {tableName} 表名称 * @return {int64} 新增gid {error} 错误 */ func (db *MySQL) InsertTable(model interface{}, tableName string) (int64, error) { if model == nil { return -1, errors.New("model is nil") } strSql := "insert " + tableName args := make([]interface{}, 0) strSql += " (" refValue := reflect.ValueOf(model) refType := reflect.TypeOf(model) fieldCount := refValue.NumField() for i := 0; i < fieldCount; i++ { fieldType := refType.Field(i) fieldValue := refValue.Field(i) gormTag := fieldType.Tag.Get("gorm") //对应的列名称(tag中定义) gormTag = strings.Replace(gormTag, "column:", "", -1) gormTags := strings.Split(gormTag, ";") if len(gormTags) > 0 { column := gormTags[0] isadd := false switch fieldType.Type.String() { case "string": if fieldValue.Len() > 0 { isadd = true } case "int", "int8", "int16", "int32", "int64": if fieldValue.Int() != 0 { isadd = true } case "time.Time": valTime := fieldValue.Interface().(time.Time) if !tool.CheckIsDefaultTime(valTime) { isadd = true } } if isadd { strSql += column + "," args = append(args, fieldValue.Interface()) } } } if len(args) < 1 { return -1, errors.New("args is nil") } insertKeyStr := strSql[0:len(strSql)-1] + ") " insertValueStr := " values (" for i := 0; i < len(args); i++ { insertValueStr += "?" if i != len(args)-1 { insertValueStr += "," } } insertValueStr += ")" insertSql := insertKeyStr + insertValueStr result, err := db.Exec(insertSql, args...) return result, err } //UpdateTableByColumn 通用修改 /** * @name: UpdateTableByColumn * @Descripttion: 通用修改通过表结构某一字段 * @param {model} 表结构对应结构体数据 * @param {tableName} 表名称 * @param {mapcolumn} 根据表字段修改 默认通过gid * @return {int64} 新增gid {error} 错误 */ func (db *MySQL) UpdateTableByColumn(model interface{}, tableName string, mapcolumn map[string]interface{}) (int64, error) { if model == nil { return -1, errors.New("model is nil") } strSql := "update " + tableName + " SET " args := make([]interface{}, 0) refValue := reflect.ValueOf(model) refType := reflect.TypeOf(model) fieldCount := refValue.NumField() var gid int64 for i := 0; i < fieldCount; i++ { fieldType := refType.Field(i) fieldValue := refValue.Field(i) gormTag := fieldType.Tag.Get("gorm") //对应的列名称(tag中定义) gormTag = strings.Replace(gormTag, "column:", "", -1) gormTags := strings.Split(gormTag, ";") if len(gormTags) > 0 { column := gormTags[0] if column == "gid" { gid = fieldValue.Interface().(int64) } isadd := false switch fieldType.Type.String() { case "string": if fieldValue.Len() > 0 { isadd = true } case "int", "int8", "int16", "int32", "int64": if fieldValue.Int() != 0 { isadd = true } case "time.Time": valTime := fieldValue.Interface().(time.Time) if !tool.CheckIsDefaultTime(valTime) { isadd = true } else { if column == "delete_at" { strSql += "delete_at=NULL," } } } if isadd { strSql += column + "=?," args = append(args, fieldValue.Interface()) } } } if len(args) < 1 { return -1, errors.New("args is nil") } //默认通过gid修改 if mapcolumn == nil { if gid == 0 { return -1, errors.New("update where is nil") } else { mapcolumn = make(map[string]interface{}) mapcolumn["gid"] = gid } } updateStr := strSql[0:len(strSql)-1] + " where 1=1" for k, v := range mapcolumn { updateStr += " AND " + k + "=? " args = append(args, v) } result, err := db.Exec(updateStr, args...) return result, err }
调用:
//新增 func (r *RatetemplateDao) InsertRatetemplate(model *config.RatetemplateModel) (int64, error) { db := r.Db.GetDB() if db == nil { return -1, errors.New(dao.DbErrMsg) } if model == nil { return -1, errors.New(daoErrMsg) } var intoModel interface{} if model == nil { intoModel = nil } else { intoModel = *model } result, err := db.InsertTable(intoModel, ratetemplateTableName) return result, err } //修改 func (r *RatetemplateDao) UpdateRatetemplateById(model *config.RatetemplateModel) (int64, error) { db := r.Db.GetDB() if db == nil { return -1, errors.New(dao.DbErrMsg) } if model == nil { return -1, errors.New(daoErrMsg) } var intoModel interface{} if model == nil { intoModel = nil } else { intoModel = *model } result, err := db.UpdateTableByColumn(intoModel, ratetemplateTableName, nil) return result, err } //查询 func (r *RatetemplateDao) GetRatetemplateByModel(model *config.RatetemplateModel, startNum int, pagesize int) ([]*config.RatetemplateModel, error) { db := r.Db.GetDB() if db == nil { return nil, errors.New(dao.DbErrMsg) } result := make([]*config.RatetemplateModel, 0) var intoModel interface{} if model == nil { intoModel = nil } else { intoModel = *model } strSql, args := db.GetSelectTableSql(intoModel, ratetemplateTableName, startNum, pagesize) var err error if len(args) > 0 { _, err = db.ExecSelect(strSql, &result, args...) } else { _, err = db.ExecSelect(strSql, &result) } return result, err }
传入的参数对象结构体需要在tag里面定义相应的解析值column:
type RatetemplateModel struct { Gid int64 `sql:"Gid" gorm:"column:gid;primary_key;auto_increment;comment:'唯一标识';type:bigint(20)" json:"gid"` CdnGid int64 `sql:"CdnGid" gorm:"column:cdn_gid;not null;comment:'拉流域名gid';type:bigint(20)" json:"cdn_gid"` AppName string `sql:"AppName" gorm:"column:app_name;not null;comment:'业务线名称(live)';type:varchar(32)" json:"app_name"` }
这样我们就不需要再写每张表的常规增删改查的sql语句了,而且当有大量的单一业务时,可以写一个代码生成工具根据数据库来生成这些代码
ps:反射的时候相应的传入对象结构体不能是指针类型的,如外层业务传入的是指针类型,需要转换为值类型,关键代码:
intoModel = *model,以上内容只是方便记录理解反射逻辑,某些具体sql执行方法未贴出