Golang非侵入式SQL构建与数据绑定插件库Gendry的使用

Gendry是一个帮助操作数据库的Go库。基于go-sql-driver/mysql，它提供了一系列简单但有用的工具来为调用标准库database/sql中的方法准备参数。

Gendry的组成部分

Gendry由三个独立的部分组成，可以单独使用每个部分：

1. Manager - 用于初始化数据库连接池
2. Builder - 用于构建SQL
3. Scanner - 用于数据绑定
4. CLI工具 - 代码生成工具

Manager

Manager用于初始化数据库连接池(即sql.DB)。你可以为支持的MySQL驱动设置几乎所有参数。例如，初始化一个数据库连接池：

var db *sql.DB
var err error
db, err = manager
    .New(dbName, user, password, host)
    .Set(
        manager.SetCharset("utf8"),
        manager.SetAllowCleartextPasswords(true),
        manager.SetInterpolateParams(true),
        manager.SetTimeout(1 * time.Second),
        manager.SetReadTimeout(1 * time.Second)
    ).Port(3302).Open(true)

实际上，manager所做的只是生成dataSourceName，其格式为：

[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]

Builder

Builder用于构建SQL。手动编写SQL直观但难以维护。对于where in，如果in集合中有大量元素，编写起来非常困难。

Builder不是一个ORM。事实上，我们创建Gendry的最重要原因之一是我们不喜欢ORM。所以Gendry只提供了一些简单的API来帮助你构建SQL：

where := map[string]interface{}{
    "city": []string{"beijing", "shanghai"},
    "score": 5,
    "age >": 35,
    "address": builder.IsNotNull,
    "_or": []map[string]interface{}{
        {
            "x1":    11,
            "x2 >=": 45,
        },
        {
            "x3":    "234",
            "x4 <>": "tx2",
        },
    },
    "_orderby": "bonus desc",
    "_groupby": "department",
}
table := "some_table"
selectFields := []string{"name", "age", "sex"}
cond, values, err := builder.BuildSelect(table, where, selectFields)

// cond = SELECT name,age,sex FROM some_table WHERE (((x1=? AND x2>=?) OR (x3=? AND x4!=?)) AND score=? AND city IN (?,?) AND age>? AND address IS NOT NULL) GROUP BY department ORDER BY bonus DESC
// values = []interface{}{11, 45, "234", "tx2", 5, "beijing", "shanghai", 35}

rows, err := db.Query(cond, values...)

// 支持builder.Raw在where和update中
where := map[string]interface{}{"gmt_create <": builder.Raw("gmt_modified")}
cond, values, err := builder.BuildSelect(table, where, selectFields)
// SELECT * FROM x WHERE gmt_create < gmt_modified

update = map[string]interface{}{
    "code": builder.Raw("VALUES(code)"), // mysql 8.x  builder.Raw("new.code")
    "name": builder.Raw("VALUES(name)"), // mysql 8.x  builder.Raw("new.name")
}
cond, values, err := builder.BuildInsertOnDuplicate(table, data, update)
// INSERT INTO country (id, code, name) VALUES (?,?,?),(?,?,?),(?,?,?) 
// ON DUPLICATE KEY UPDATE code=VALUES(code),name=VALUES(name)

在where参数中，in操作符会根据值类型(reflect.Slice)自动添加。

此外，该库提供了一个有用的API来执行聚合查询，如count、sum、max、min、avg：

where := map[string]interface{}{
    "score > ": 100,
    "city": []interface{}{"Beijing", "Shijiazhuang"},
}
// 支持AggregateSum, AggregateMax, AggregateMin, AggregateCount, AggregateAvg
result, err := AggregateQuery(ctx, db, "tableName", where, AggregateSum("age"))
sumAge := result.Int64()
result, err = AggregateQuery(ctx, db, "tableName", where, AggregateCount("*")) 
numberOfRecords := result.Int64()
result, err = AggregateQuery(ctx, db, "tableName", where, AggregateAvg("score"))
averageScore := result.Float64()

对于复杂查询，NamedQuery可能会有帮助：

cond, vals, err := builder.NamedQuery("select * from tb where name={{name}} and id in (select uid from anothertable where score in {{m_score}})", map[string]interface{}{
    "name": "caibirdme",
    "m_score": []float64{3.0, 5.8, 7.9},
})

assert.Equal("select * from tb where name=? and id in (select uid from anothertable where score in (?,?,?))", cond)
assert.Equal([]interface{}{"caibirdme", 3.0, 5.8, 7.9}, vals)

Scanner

对于MySQL的每个响应，你可能希望将其映射到你定义良好的结构中。Scanner提供了一个直接的API来做到这一点，它基于反射：

标准库方式

type Person struct {
    Name string
    Age int
}

rows, err := db.Query("SELECT age as m_age, name from g_xxx where xxx")
defer rows.Close()

var students []Person

for rows.Next() {
    var student Person
    rows.Scan(student.Age, student.Name)
    students = append(students, student)
}

使用Scanner

type Person struct {
    Name string `ddb:"name"`
    Age int `ddb:"m_age"`
}

rows, err := db.Query("SELECT age as m_age, name from g_xxx where xxx")
defer rows.Close()

var students []Person

scanner.Scan(rows, &students)

实现ByteUnmarshaler接口的类型将接管相应的解组工作：

type ByteUnmarshaler interface {
    UnmarshalByte(data []byte) error
}

示例：

type human struct {
    Age   int       `ddb:"ag"`
    Extra *extraInfo `ddb:"ext"`
}

type extraInfo struct {
    Hobbies     []string `json:"hobbies"`
    LuckyNumber int      `json:"ln"`
}

func (ext *extraInfo) UnmarshalByte(data []byte) error {
    return json.Unmarshal(data, ext)
}

// 如果表中ext列的类型是varchar(存储合法的json字符串)或json(mysql5.7)
var student human
err := scanner.Scan(rows, &student)
// ...

ScanMap

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

ScanMap从rows中扫描数据并返回一个[]map[string]interface{}。

注意事项

• 如果不使用ScanXXXClose，不要忘记关闭rows
• Scan的第二个参数必须是一个引用