Go中反射的利用（通用sql封装，字段拼接对应）

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› Go›Go教程

原作者: [db:作者] 来自: [db:来源] 收藏邀请

在后端接口开发中，往往需要针对某一张表写相对应的增删改查的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执行方法未贴出

鲜花

握手

雷人

路过

鸡蛋

该文章已有0人参与评论

请发表评论

全部评论

专题导读

More+

Go入门笔记31-日期时间格式化发布时间：2022-07-10

go 实现用户特权判断的例子发布时间：2022-07-10

客服电话

电子邮件

Go中反射的利用（通用sql封装，字段拼接对应）

请发表评论

全部评论

上一篇：

下一篇：

关于我们

产品与服务

解决方案

139-2527-9053