关于golang:动态sql工具之gendry

前言

哈喽，我是asong。

明天给大家举荐一个第三方库 gendry，这个库是用于辅助操作数据库的Go 包。其是基于 go-sql-driver/mysql，它提供了一系列的办法来为你调用规范库database/sql 中的办法筹备参数。对于我这种不喜爱是应用 orm 框架的选手，真的是爱不释手，即便不应用 orm 框架，也能够写出动静sql。上面我就带大家看一看这个库怎么应用！

github 地址：https://github.com/didi/gendry

初始化连贯

既然要应用数据库，那么第一步咱们就来进行数据库连贯，咱们先来看一下间接应用规范库进行连贯库是怎么写的：

func NewMysqlClient(conf *config.Server) *sql.DB {connInfo := fmt.Sprintf("%s:%s@(%s)/%s?charset=utf8&parseTime=True&loc=Local", conf.Mysql.Username, conf.Mysql.Password, conf.Mysql.Host, conf.Mysql.Db)
    var err error
    db, err := sql.Open("mysql", connInfo)
    if err != nil {fmt.Printf("init mysql err %v\n", err)
    }
    err = db.Ping()
    if err != nil {fmt.Printf("ping mysql err: %v", err)
    }
    db.SetMaxIdleConns(conf.Mysql.Conn.MaxIdle)
    db.SetMaxOpenConns(conf.Mysql.Conn.Maxopen)
    db.SetConnMaxLifetime(5 * time.Minute)
    fmt.Println("init mysql successc")
    return db
}

从下面的代码能够看出，咱们须要本人拼接连贯参数，这就须要咱们时刻记住连贯参数（对于我这种记忆白痴，每回都要去度娘一下，很好受）。Gendry为咱们提供了一个 manager 库，次要用来初始化连接池，设置其各种参数，你能够设置任何 go-sql-driver/mysql 驱动反对的参数，所以咱们的初始化代码能够这样写：

func MysqlClient(conf *config.Mysql) *sql.DB {

    db, err := manager.
        New(conf.Db,conf.Username,conf.Password,conf.Host).Set(manager.SetCharset("utf8"),
        manager.SetAllowCleartextPasswords(true),
        manager.SetInterpolateParams(true),
        manager.SetTimeout(1 * time.Second),
        manager.SetReadTimeout(1 * time.Second),
            ).Port(conf.Port).Open(true)

    if err != nil {fmt.Printf("init mysql err %v\n", err)
    }
    err = db.Ping()
    if err != nil {fmt.Printf("ping mysql err: %v", err)
    }
    db.SetMaxIdleConns(conf.Conn.MaxIdle)
    db.SetMaxOpenConns(conf.Conn.Maxopen)
    db.SetConnMaxLifetime(5 * time.Minute)
    //scanner.SetTagName("json")  // 全局设置，只容许设置一次
    fmt.Println("init mysql successc")
    return db
}

manager做的事件就是帮咱们生成 datasourceName，并且它反对了简直所有该驱动反对的参数设置，咱们齐全不须要管datasourceName 的格局是怎么的，只管配置参数就能够了。

如何应用？

上面我就带着大家一起来几个 demo 学习，更多应用办法能够看源代码解锁（之所以没说看官网文档解决的起因：文档不是很具体，还不过看源码来的切实）。

数据库筹备

既然是写示例代码，那么肯定要先有一个数据表来提供测试呀，测试数据表如下：

create table users
(
    id       bigint unsigned auto_increment
        primary key,
    username varchar(64)  default '' not null,
    nickname varchar(255) default '' null,
    password varchar(256) default '' not null,
    salt     varchar(48)  default '' not null,
    avatar   varchar(128)            null,
    uptime   bigint       default 0  not null,
    constraint username
        unique (username)
)
    charset = utf8mb4;

好了数据表也有了，上面就开始展现吧，以下依照增删改查的程序顺次展现～。

插入数据

gendry提供了三种办法帮忙你结构插入 sql，别离是：

// BuildInsert work as its name says
func BuildInsert(table string, data []map[string]interface{}) (string, []interface{}, error) {return buildInsert(table, data, commonInsert)
}

// BuildInsertIgnore work as its name says
func BuildInsertIgnore(table string, data []map[string]interface{}) (string, []interface{}, error) {return buildInsert(table, data, ignoreInsert)
}

// BuildReplaceInsert work as its name says
func BuildReplaceInsert(table string, data []map[string]interface{}) (string, []interface{}, error) {return buildInsert(table, data, replaceInsert)
}

// BuildInsertOnDuplicateKey builds an INSERT ... ON DUPLICATE KEY UPDATE clause.
func BuildInsertOnDuplicate(table string, data []map[string]interface{}, update map[string]interface{}) (string, []interface{}, error) {return buildInsertOnDuplicate(table, data, update)
}

看命名想必大家就曾经晓得他们代表的是什么意思了吧，这里就不一一解释了，这里咱们以 buildInsert 为示例，写一个小 demo：

func (db *UserDB) Add(ctx context.Context,cond map[string]interface{}) (int64,error) {sqlStr,values,err := builder.BuildInsert(tplTable,[]map[string]interface{}{cond})
    if err != nil{return 0,err}
    // TODO:DEBUG
    fmt.Println(sqlStr,values)
    res,err := db.cli.ExecContext(ctx,sqlStr,values...)
    if err != nil{return 0,err}
    return res.LastInsertId()}
// 单元测试如下：func (u *UserDBTest) Test_Add()  {cond := map[string]interface{}{
        "username": "test_add",
        "nickname": "asong",
        "password": "123456",
        "salt": "oooo",
        "avatar": "http://www.baidu.com",
        "uptime": 123,
    }
    s,err := u.db.Add(context.Background(),cond)
    u.Nil(err)
    u.T().Log(s)
}

咱们把要插入的数据放到 map 构造中，key就是要字段，value就是咱们要插入的值，其余都交给 builder.BuildInsert 就好了，咱们的代码大大减少。大家必定很好奇这个办法是怎么实现的呢？别着急，前面咱们一起解密。

删除数据

我最喜爱删数据了，不晓得为什么，删完数据总有一种快感。。。。

删除数据能够间接调用 builder.BuildDelete 办法，比方咱们当初咱们要删除方才插入的那条数据：

func (db *UserDB)Delete(ctx context.Context,where map[string]interface{}) error {sqlStr,values,err := builder.BuildDelete(tplTable,where)
    if err != nil{return err}
    // TODO:DEBUG
    fmt.Println(sqlStr,values)
    res,err := db.cli.ExecContext(ctx,sqlStr,values...)
    if err != nil{return err}
    affectedRows,err := res.RowsAffected()
    if err != nil{return err}
    if affectedRows == 0{return errors.New("no record delete")
    }
    return nil
}

// 单测如下：func (u *UserDBTest)Test_Delete()  {where := map[string]interface{}{"username in": []string{"test_add"},
    }
    err := u.db.Delete(context.Background(),where)
    u.Nil(err)
}

这里在传入 where 条件时，key应用的 username in，这里应用空格加了一个操作符in，这是gendry 库所反对的写法，当咱们的 SQL 存在一些操作符时，就能够通过这样办法进行书写，模式如下：

where := map[string]interface{}{"field 操作符": "value",}

官文文档给出的反对操作如下：

=
>
<
=
<=
>=
!=
<>
in
not in
like
not like
between
not between

既然说到了这里，顺便把 gendry 反对的关键字也说一下吧，官网文档给出的反对如下：

_or
_orderby
_groupby
_having
_limit
_lockMode

参考示例：

where := map[string]interface{}{
    "age >": 100,
    "_or": []map[string]interface{}{
        {
            "x1":    11,
            "x2 >=": 45,
        },
        {
            "x3":    "234",
            "x4 <>": "tx2",
        },
    },
    "_orderby": "fieldName asc",
    "_groupby": "fieldName",
    "_having": map[string]interface{}{"foo":"bar",},
    "_limit": []uint{offset, row_count},
    "_lockMode": "share",
}

这里有几个须要留神的问题：

• 如果 _groupby 没有被设置将疏忽_having

• _limit能够这样写：

  • "_limit": []uint{a,b} => LIMIT a,b
  • "_limit": []uint{a} => LIMIT 0,a

• _lockMode临时只反对 share 和exclusive

  • share代表的是SELECT ... LOCK IN SHARE MODE. 可怜的是，以后版本不反对SELECT ... FOR SHARE.
  • exclusive代表的是SELECT ... FOR UPDATE.

更新数据

更新数据能够应用 builder.BuildUpdate 办法进行构建 sql 语句，不过要留神的是，他不反对_orderby、_groupby、_having. 只有这个是咱们所须要留神的，其余的失常应用就能够了。

func (db *UserDB) Update(ctx context.Context,where map[string]interface{},data map[string]interface{}) error {sqlStr,values,err := builder.BuildUpdate(tplTable,where,data)
    if err != nil{return err}
    // TODO:DEBUG
    fmt.Println(sqlStr,values)
    res,err := db.cli.ExecContext(ctx,sqlStr,values...)
    if err != nil{return err}
    affectedRows,err := res.RowsAffected()
    if err != nil{return err}
    if affectedRows == 0{return errors.New("no record update")
    }
    return nil
}
// 单元测试如下：func (u *UserDBTest) Test_Update()  {where := map[string]interface{}{"username": "asong",}
    data := map[string]interface{}{"nickname": "shuai",}
    err := u.db.Update(context.Background(),where,data)
    u.Nil(err)
}

这里入参变成了两个，一个是用来指定 where 条件的，另一个就是来放咱们要更新的数据的。

查问数据

查问应用的是 builder.BuildSelect 办法来构建 sql 语句，先来一个示例，看看怎么用？

func (db *UserDB) Query(ctx context.Context,cond map[string]interface{}) ([]*model.User,error) {sqlStr,values,err := builder.BuildSelect(tplTable,cond,db.getFiledList())
    if err != nil{return nil, err}
    rows,err := db.cli.QueryContext(ctx,sqlStr,values...)
    defer func() {
        if rows != nil{_ = rows.Close()
        }
    }()
    if err != nil{
        if err == sql.ErrNoRows{return nil,errors.New("not found")
        }
        return nil,err
    }
    user := make([]*model.User,0)
    err = scanner.Scan(rows,&user)
    if err != nil{return nil,err}
    return user,nil
}
// 单元测试
func (u *UserDBTest) Test_Query()  {cond := map[string]interface{}{"id in": []int{1,2},
    }
    s,err := u.db.Query(context.Background(),cond)
    u.Nil(err)
    for k,v := range s{u.T().Log(k,v)
    }
}

BuildSelect(table string, where map[string]interface{}, selectField []string)总共有三个入参，table就是数据表名，where外面就是咱们的条件参数，selectFiled就是咱们要查问的字段，如果传 nil，对应的sql 语句就是 select * ...。看完下面的代码，零碎的敌人应该会对scanner.Scan，这个就是gendry 提供一个映射后果集的办法，上面咱们来看一看这个库怎么用。

scanner

执行了数据库操作之后，要把返回的后果集和自定义的 struct 进行映射。Scanner 提供一个简略的接口通过反射来进行后果集和自定义类型的绑定，下面的 scanner.Scan 办法就是来做这个，scanner 进行反射时会应用构造体的 tag。默认应用的 tagName 是 ddb:"xxx"，你也能够自定义。应用scanner.SetTagName("json") 进行设置，scaner.SetTagName 是全局设置，为了防止歧义，只容许设置一次，个别在初始化 DB 阶段进行此项设置.

有时候咱们可能不太想定义一个构造体去存两头后果，那么 gendry 还提供了 scanMap 能够应用：

rows,_ := db.Query("select name,m_age from person")
result,err := scanner.ScanMap(rows)
for _,record := range result {fmt.Println(record["name"], record["m_age"])
}

在应用 scanner 是有以下几点须要留神：

• 如果是应用 Scan 或者 ScanMap 的话，你必须在之后手动 close rows
• 传给 Scan 的必须是援用
• ScanClose 和 ScanMapClose 不须要手动 close rows

手写SQL

对于一些比较复杂的查问，gendry办法就不能满足咱们的需要了，这就可能须要咱们自定义 sql 了，gendry提供了 NamedQuery 就是这么应用的，具体应用如下：

func (db *UserDB) CustomizeGet(ctx context.Context,sql string,data map[string]interface{}) (*model.User,error) {sqlStr,values,err := builder.NamedQuery(sql,data)
    if err != nil{return nil, err}
    // TODO:DEBUG
    fmt.Println(sql,values)
    rows,err := db.cli.QueryContext(ctx,sqlStr,values...)
    if err != nil{return nil,err}
    defer func() {
        if rows != nil{_ = rows.Close()
        }
    }()
    user := model.NewEmptyUser()
    err = scanner.Scan(rows,&user)
    if err != nil{return nil,err}
    return user,nil
}
// 单元测试
func (u *UserDBTest) Test_CustomizeGet()  {sql := "SELECT * FROM users WHERE username={{username}}"
    data := map[string]interface{}{"username": "test_add",}
    user,err := u.db.CustomizeGet(context.Background(),sql,data)
    u.Nil(err)
    u.T().Log(user)
}

这种就是纯手写 sql 了，一些简单的中央能够这么应用。

聚合查问

gendry还为咱们提供了聚合查问，例如：count,sum,max,min,avg。这里就拿 count 来举例吧，假如咱们当初要统计明码雷同的用户有多少，就能够这么写：

func (db *UserDB) AggregateCount(ctx context.Context,where map[string]interface{},filed string) (int64,error) {res,err := builder.AggregateQuery(ctx,db.cli,tplTable,where,builder.AggregateCount(filed))
    if err != nil{return 0, err}
    numberOfRecords := res.Int64()
    return numberOfRecords,nil
}
// 单元测试
func (u *UserDBTest) Test_AggregateCount()  {where := map[string]interface{}{"password": "123456",}
    count,err := u.db.AggregateCount(context.Background(),where,"*")
    u.Nil(err)
    u.T().Log(count)
}

到这里，所有的根本用法根本演示了一遍，更多的应用办法能够自行解锁。

cli 工具

除了下面这些 API 以外，Gendry还提供了一个命令行来进行代码生成，能够显著缩小你的开发量，gforge是基于 gendry 的 cli 工具，它依据表名生成 golang 构造，这能够加重您的累赘。甚至 gforge 都能够为您生成残缺的 DAO 层。

装置

go get -u github.com/caibirdme/gforge

应用 gforge -h 来验证是否装置胜利，同时会给出应用提醒。

生成表构造

应用 gforge 生成的表构造是能够通过 golint 和 govet的。生成指令如下：

gforge table -uroot -proot1997 -h127.0.0.1 -dasong -tusers

// Users is a mapping object for users table in mysql
type Users struct {
    ID uint64 `json:"id"`
    Username string `json:"username"`
    Nickname string `json:"nickname"`
    Password string `json:"password"`
    Salt string `json:"salt"`
    Avatar string `json:"avatar"`
    Uptime int64 `json:"uptime"`
}

这样就省去了咱们自定义表构造的工夫，或者更不便的是间接把 dao 层生成进去。

生成 dao 文件

运行指令如下：

gforge dao -uroot -proot1997 -h127.0.0.1 -dasong -tusers | gofmt > dao.go

这里我把生成的 dao 层间接丢到了文件里了，这里就不贴具体代码了，没有意义，晓得怎么应用就好了。

解密

想必大家肯定都跟我一样特地好奇 gendry 是怎么实现的呢？上面就以 builder.buildSelect 为例子，咱们来看一看他是怎么实现的。其余原理类似，有趣味的童鞋能够看源码学习。咱们先来看一下 buildSelect 这个办法的源码：

func BuildSelect(table string, where map[string]interface{}, selectField []string) (cond string, vals []interface{}, err error) {
    var orderBy string
    var limit *eleLimit
    var groupBy string
    var having map[string]interface{}
    var lockMode string
    if val, ok := where["_orderby"]; ok {s, ok := val.(string)
        if !ok {
            err = errOrderByValueType
            return
        }
        orderBy = strings.TrimSpace(s)
    }
    if val, ok := where["_groupby"]; ok {s, ok := val.(string)
        if !ok {
            err = errGroupByValueType
            return
        }
        groupBy = strings.TrimSpace(s)
        if "" != groupBy {if h, ok := where["_having"]; ok {having, err = resolveHaving(h)
                if nil != err {return}
            }
        }
    }
    if val, ok := where["_limit"]; ok {arr, ok := val.([]uint)
        if !ok {
            err = errLimitValueType
            return
        }
        if len(arr) != 2 {if len(arr) == 1 {arr = []uint{0, arr[0]}
            } else {
                err = errLimitValueLength
                return
            }
        }
        begin, step := arr[0], arr[1]
        limit = &eleLimit{
            begin: begin,
            step:  step,
        }
    }
    if val, ok := where["_lockMode"]; ok {s, ok := val.(string)
        if !ok {
            err = errLockModeValueType
            return
        }
        lockMode = strings.TrimSpace(s)
        if _, ok := allowedLockMode[lockMode]; !ok {
            err = errNotAllowedLockMode
            return
        }
    }
    conditions, err := getWhereConditions(where, defaultIgnoreKeys)
    if nil != err {return}
    if having != nil {havingCondition, err1 := getWhereConditions(having, defaultIgnoreKeys)
        if nil != err1 {
            err = err1
            return
        }
        conditions = append(conditions, nilComparable(0))
        conditions = append(conditions, havingCondition...)
    }
    return buildSelect(table, selectField, groupBy, orderBy, lockMode, limit, conditions...)
}

• 首先会对几个关键字进行解决。
• 而后会调用 getWhereConditions 这个办法去结构sql，看一下外部实现(摘取局部)：

for key, val := range where {if _, ok := ignoreKeys[key]; ok {continue}
        if key == "_or" {
            var (orWheres          []map[string]interface{}
                orWhereComparable []Comparable
                ok                bool
            )
            if orWheres, ok = val.([]map[string]interface{}); !ok {return nil, errOrValueType}
            for _, orWhere := range orWheres {
                if orWhere == nil {continue}
                orNestWhere, err := getWhereConditions(orWhere, ignoreKeys)
                if nil != err {return nil, err}
                orWhereComparable = append(orWhereComparable, NestWhere(orNestWhere))
            }
            comparables = append(comparables, OrWhere(orWhereComparable))
            continue
        }
        field, operator, err = splitKey(key)
        if nil != err {return nil, err}
        operator = strings.ToLower(operator)
        if !isStringInSlice(operator, opOrder) {return nil, ErrUnsupportedOperator}
        if _, ok := val.(NullType); ok {operator = opNull}
        wms.add(operator, field, val)
    }

这一段就是遍历 slice，之前解决过的关键字局部会被疏忽，_or 关键字会递归解决失去所有条件数据。之后就没有特地要阐明的中央了。我本人返回到 buildSelect 办法中，在解决了 where 条件之后，如果有 having 条件还会在进行一次过滤，最初所有的数据构建好了后，会调用 buildSelect 办法来结构最初的 sql 语句。

总结

看过源码当前，只想说：大佬就是大佬。源码其实很容易看懂，这就没有做具体的解析，次要是这样思维值得大家学习，倡议大家都能够看一遍 gendry 的源码，涨常识～～。

好啦，这篇文章就到这里啦，素质三连（分享、点赞、在看）都是笔者继续创作更多优质内容的能源！

建了一个 Golang 交换群，欢送大家的退出，第一工夫观看优质文章，不容错过哦（公众号获取）

结尾给大家发一个小福利吧，最近我在看 [微服务架构设计模式] 这一本书，讲的很好，本人也收集了一本 PDF，有须要的小伙能够到自行下载。获取形式：关注公众号：[Golang 梦工厂]，后盾回复：[微服务]，即可获取。

我翻译了一份 GIN 中文文档，会定期进行保护，有须要的小伙伴后盾回复 [gin] 即可下载。

翻译了一份 Machinery 中文文档，会定期进行保护，有须要的小伙伴们后盾回复 [machinery] 即可获取。

我是 asong，一名普普通通的程序猿，让 gi 我一起缓缓变强吧。我本人建了一个 golang 交换群，有须要的小伙伴加我vx, 我拉你入群。欢送各位的关注，咱们下期见~~~

举荐往期文章：

• machinery-go 异步工作队列
• Leaf—Segment 分布式 ID 生成零碎（Golang 实现版本）
• 十张动图带你搞懂排序算法(附 go 实现代码)
• Go 语言相干书籍举荐（从入门到放弃）
• go 参数传递类型
• 手把手教姐姐写音讯队列
• 常见面试题之缓存雪崩、缓存穿透、缓存击穿
• 详解 Context 包，看这一篇就够了！！！
• go-ElasticSearch 入门看这一篇就够了(一)
• 面试官：go 中 for-range 应用过吗？这几个问题你能解释一下起因吗
• 学会 wire 依赖注入、cron 定时工作其实就这么简略！
• 据说你还不会 jwt 和 swagger- 饭我都不吃了带着实际我的项目我就来了