优秀的第三方操作sql的库-sqlx

参考链接

• 李文周的博客
• http://jmoiron.github.io/sqlx/

介绍

在项目中我们通常可能会使用database/sql连接MySQL数据库。

sqlx可以认为是Go语言内置database/sql的超集，它在优秀的内置database/sql基础上提供了一组扩展。

这些扩展中除了大家常用来查询的Get(dest interface{}, ...) error和Select(dest interface{}, ...) error外还有很多其他强大的功能。

安装&连接数据库

安装：

go get github.com/jmoiron/sqlx

连接 同样不要忘记了驱动 _ "github.com/go-sql-driver/mysql"

package initialization

import (
	"fmt"

	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

func InitSqlxDB() (*sqlx.DB, error) {
	dbUser := "root"
	dbPassword := "testgomysqldriver"
	dbName := "tgo"
	dbURL := "127.0.0.1:3306"
	sqlDSN := fmt.Sprintf("%s:%s@tcp(%s)/%s", dbUser, dbPassword, dbURL, dbName)
	db, err := sqlx.Connect("mysql", sqlDSN)
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(10)
	return db, nil
}

Connect 方法内部实现其实也是 databse/sql 标准库的Open和Ping方法！进行连接和验证！

// Connect to a database and verify with a ping.
func Connect(driverName, dataSourceName string) (*DB, error) {
	db, err := Open(driverName, dataSourceName)
	if err != nil {
		return nil, err
	}
	err = db.Ping()
	if err != nil {
		db.Close()
		return nil, err
	}
	return db, nil
}

然后就是在 main 中初始化了

func main() {
	sqlxDB, err := initialization.InitSqlxDB()
	if err != nil {
		panic(err.Error())
	}
	defer sqlxDB.Close()
}

增删改查！

因为 sqlx 是对database/sql 的超集，所以完全兼容 标准库sql的写法！比如下面的查询操作！

兼容 sql 标准库查询

package main

import (
	"fmt"
	"github.com/jmoiron/sqlx"
	"go-web-0211/initialization"
)

var sqlxDB *sqlx.DB
var err error

type word struct {
	id        int
	word      string
	wordMeans string
}

// 查询单条数据示例
func queryRowDemo(uid int64) (word, error) {
	sqlStr := "select id, word,word_means  from cws_wordbook where id= ?"
	var w word
	// 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
	return w, sqlxDB.QueryRow(sqlStr, uid).Scan(&w.id, &w.word, &w.wordMeans)
}

func main() {
	sqlxDB, err = initialization.InitSqlxDB()
	if err != nil {
		panic(any(err.Error()))
	}
	defer sqlxDB.Close()
	w, err := queryRowDemo(1)
	if err != nil {
		panic(any(err.Error()))
	}
	fmt.Println(w.word)
	fmt.Println(w.wordMeans)
}

Get查询！

语法

func (db *DB) Get(dest interface{}, query string, args ...interface{}) error

• 第一个参数是空接口,一般放置的是定义的查询结果对应的定义结构体类型！

• 第二个参数是查询语句

• 第三个参数是查询参数

type word struct {
	id        int
	word      string
	wordMeans string
}

// GetQueryRowDemo 查询单条数据示例
func GetQueryRowDemo(uid int64) (word, error) {
	sqlStr := "select id, word,word_means  from cws_wordbook where id= ?"
	var w word
	return w, sqlxDB.Get(&w, sqlStr, uid)
}

其实内部是通过反射完成的！

所以我们定义结构体的时候，需要这个结构体是对外导出的！

type word struct {
	Id        int   
	Word      string
	WordMeans string
}

仅仅这样是不行的！执行还是会报错 (在结构体实例*main.word 中缺少destination name )

╰─ go run main.go                                                                                                                                                                                                                   ─╯
data base connect successfully!
panic: missing destination name word_means in *main.word

goroutine 1 [running]:
main.main()
        /Users/codehope/GolandProjects/go-web-0211/main.go:41 +0x1f7
exit status 2

首先看到源码的Get方法

func Get(q Queryer, dest interface{}, query string, args ...interface{}) error {
	r := q.QueryRowx(query, args...)
	return r.scanAny(dest, false)
}

首先这里内部执行了一个 QueryRowx 这里其实就能拿到我们查询的信息了！(注意这里的信息，字段对应的数据库字段！)

然后下一步 scanAny 内部的 操作其实就是如何把查询结果反射到我们传入的结构体实例上！

//sqlx.go 精简后的代码

func (r *Row) scanAny(dest interface{}, structOnly bool) error {
  
  // 通过反射获取结构体值类型！
	v := reflect.ValueOf(dest)
	base := reflectx.Deref(v.Type())
	scannable := isScannable(base)
  
  // 这里的 columns 就是查询到数据库中columns的字段！
	columns, err := r.Columns()
	if scannable {
		return r.Scan(dest)
	}
	m := r.Mapper
  
	fields := m.TraversalsByName(v.Type(), columns)
	// if we are not unsafe and are missing fields, return an error
	if f, err := missingFields(fields); err != nil && !r.unsafe {
		return fmt.Errorf("missing destination name %s in %T", columns[f], dest)
	}
	values := make([]interface{}, len(columns))

	err = fieldsByTraversal(v, fields, values, true)
	if err != nil {
		return err
	}
	// scan into the struct field pointers and append to our results
	return r.Scan(values...)
}

大致的流程就是：

1.内部先通过Query查询到数据库的元信息

2.然后通过反射获取查询结果对应的定义结构体类型，

3.所以需要一个定义一个 db tag 让数据库的元信息和定义结构体对应上！

所以我们写的结构体应该是对外导出的且有一个 tag 对应着数据库的原字段。其他的一些方法也是一样的！

type word struct {
	Id        int    `db:"id"`
	Word      string `db:"word"`
	WordMeans string `db:"word_means"`
}

执行测试，这次就ok了

╰─ go run main.go                                                                                                                                                                                                                   ─╯
data base connect successfully!
记忆
memory

Select 多行查询

// SelectRowsDemo 查询多条数据示例
func SelectRowsDemo(uid1 int64, uid2 int64) ([]word, error) {
	sqlStr := "select id, word,word_means  from cws_wordbook where id>=? and id <= ?"
	var w []word
	return w, sqlxDB.Select(&w, sqlStr, uid1, uid2)
}

w, err := SelectRowsDemo(1, 3)
if err != nil {
	panic("查询失败" + err.Error())
}
for i := range w {
	current := w[i]
	fmt.Println(current.Id, current.Word, current.WordMeans)
}

执行结果：

❯ go run main.go
data base connect successfully! 
1 记忆 memory
2 摄像头 camera
3 河粉 Rice noodles

插入、更新和删除

sqlx中的exec方法与原生sql中的exec使用基本一致：

插入

func InsertDemo(word_id int64, word string, word_mean string) (sql.Result, error) {
	sqlStr := "insert into cws_wordbook (word_id,word, word_means) values(?,?,?)"
	return sqlxDB.Exec(sqlStr, word_id, word, word_mean)
}

r, err := InsertDemo(123123, "男人", "man")
if err != nil {
	panic(err)
}
w, err := GetQueryRowDemo(40)
if err != nil {
	panic("查询失败" + err.Error())
}
fmt.Println(w)

执行

❯ go run main.go
data base connect successfully! 
{40 男人 man}

更新

func UpdateDemo(word_id int64, word string, word_mean string) (sql.Result, error) {
	sqlStr := "update cws_wordbook set word = ?, word_means = ?  where word_id = ?"
	return sqlxDB.Exec(sqlStr, word, word_mean, word_id)
}

r, err := UpdateDemo(123123, "女人", "woman")
if err != nil {
	panic(err)
}
fmt.Print(r.RowsAffected())
w, err := GetQueryRowDemo(40)
if err != nil {
	panic("查询失败" + err.Error())
}
fmt.Println(w)

执行

❯ go run main.go
data base connect successfully! 
1 <nil>{40 女人 woman}

删除

func DeleteDemo(word_id int64) (sql.Result, error) {
	sqlStr := "delete from  cws_wordbook where word_id = ?"
	return sqlxDB.Exec(sqlStr, word_id)
}

r, err := DeleteDemo(123123)
if err != nil {
	panic(err)
}
fmt.Print(r.RowsAffected())
w, err := GetQueryRowDemo(40)
if err != nil {
	panic("查询失败" + err.Error())
}
fmt.Println(w)

执行

❯ go run main.go
data base connect successfully! 
1 <nil>panic: 查询失败sql: no rows in result set  # 代表删除失败了，已经查不到这个数据了！

goroutine 1 [running]:
main.main()
        /Users/hope/sdy/temp_go_test/main.go:59 +0x2f4
exit status 2

NamedExec

DB.NamedExec方法用来绑定SQL语句与结构体或map中的同名字段。

我们可以把 SQL语句 的查询占位符 ？ 换成 :字段名

例如：下面的 name 和 age

sqlStr := "INSERT INTO user (name,age) VALUES (:name,:age)"

Map 映射 NamedExec

NamedExec 我们可以传入一个 map结构，包含name 和 age字段！那么map中对应的字段值会映射到 sql 语句中！

func NamedExecDemo(word_id int64, word string, word_mean string) (sql.Result, error) {
	sqlStr := "insert into cws_wordbook (word_id, word, word_means) values( :word_id, :word, :word_means )"
	return sqlxDB.NamedExec(sqlStr, map[string]interface{}{
		"word_id":    word_id,
		"word":       word,
		"word_means": word_mean,
	})
}

先执行一下插入

r, err := NamedExecDemo(123456789, "cup", "被子")
if err != nil {
	fmt.Println(err.Error())
}
fmt.Println(r.RowsAffected())

然后去查

w, err := GetQueryRowDemo(42)
if err != nil {
	panic(err.Error())
}
fmt.Print(w.Word, w.WordMeans)

执行

❯ go run main.go
data base connect successfully! 
cup被子

结构体 映射 NamedExec (结构体映射，要保证结构体 tag 正确！)

func NamedExecDemo(word_id int64, _word string, word_mean string) (sql.Result, error) {
	sqlStr := "insert into cws_wordbook (word_id,word, word_means) values(:word_id,:word,:word_means)"
	return sqlxDB.NamedExec(sqlStr, &word{
		Id:        word_id,
		Word:      _word,
		WordMeans: word_mean,
	})
}

NamedQuery

与DB.NamedExec同理，这里是支持查询。

Map 映射 NamedQuery

func NamedQuery(word string) (*sqlx.Rows, error) {
	sqlStr := "select id, word ,word_means  from cws_wordbook where word=:word"
	return sqlxDB.NamedQuery(sqlStr, map[string]interface{}{"word": word})
}

r, err := NamedQuery("cup")
if err != nil {
	panic(err.Error())
}
for r.Next() {
	var w word
	err := r.StructScan(&w)
	if err != nil {
		panic(err.Error())
	}
	fmt.Print(w.Word, w.WordMeans) // cup被子
}

执行

❯ go run main.go
data base connect successfully! 
cup被子% 

结构体 映射 NamedQuery

func NamedQuery(_word string) (*sqlx.Rows, error) {
	sqlStr := "select id, word ,word_means  from cws_wordbook where word=:word"
	return sqlxDB.NamedQuery(sqlStr, &word{
		Word: _word,
	})
}

事务操作

对于事务操作，我们可以使用sqlx中提供的db.Beginx()和tx.Exec()方法。

方便演示，这里创建一个账户表

CREATE TABLE `account` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(20) DEFAULT '',
    `money` INT(11) DEFAULT '0',
    PRIMARY KEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

然后创建两个账户，分别有100块钱在里面！完成的操作是在一个事务中，ch给zh转账50元！

id	username	money
1	   ch	    100
2	   zh	    100

sqlxDB.Begin() 开启事务

tx.Commit() 提交事务

tx.Rollback() 回滚事务

事务中，任何异常我们都会panic掉！我们使用 recover 函数来监听 panic 如果存在 panic 我们就把事务回滚，反之则commit

来看下面的代码，我们在事务中，自己抛出了一个 panic

func txDemo() {
	// 开启事务
	tx, err := sqlxDB.Begin()

	// panic 恢复
	defer func() {
		if p := recover(); p != nil {
			tx.Rollback()
			fmt.Println("回滚！")
		} else {
			tx.Commit()
		}
	}()
	if err != nil {
		panic("事务开启失败" + err.Error())
	}
	
	queryStr := "select username,money from account where username = ?"
	sqlstr := "update account set money = ? where username = ?"
	_, err = tx.Exec(sqlstr, 50, "ch")
	if err != nil {
		panic(err)
	}
	fmt.Println("转账成功！")
	var a Account
	_ = tx.QueryRow(queryStr, "ch").Scan(&a.Username, &a.Money)
	fmt.Println(a.Username, a.Money)
  
	panic("自定义error")
  
	_, err = tx.Exec(sqlstr, 150, "zh")
	fmt.Println("收账成功！")
	if err != nil {
		panic(err)
	}
}

执行

❯ go run main.go
data base connect successfully! 
转账成功！
ch 50
回滚！

可以看到，在事务中！ ch 扣钱成功的！而且扣完的钱也是 正确的 50 ！但是因为有panic，所以回滚了，我们去数据库中看一下数据！

可以看到数据库没有被改动！

那么我们把自定义的panic去掉

-	panic("自定义error")

看一下成功的情况！

❯ go run main.go
data base connect successfully! 
转账成功！
ch 50
收账成功！