整合sql和sqlx

What is sqlx

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

Get sqlx

go get github.com/jmoiron/sqlx

Prepare sqlx

package mysql

import (
	"bluebell/setting"
	"fmt"

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

var db *sqlx.DB

// Init 初始化MySQL连接
func Init(cfg *setting.MySQLConfig) (err error) {
	// "user:password@tcp(host:port)/dbname"
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?parseTime=true&loc=Local", cfg.User, cfg.Password, cfg.Host, cfg.Port, cfg.DB)
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		return
	}
	db.SetMaxOpenConns(cfg.MaxOpenConns)
	db.SetMaxIdleConns(cfg.MaxIdleConns)
	return
}

// Close 关闭MySQL连接
func Close() {
	_ = db.Close()
}

How to use sqlx

Connection

// 定义一x全局对象 这是并发安全的对象 注意这个是sqlx了 不是sql
var db *sqlx.DB

func initMysql() (err error) {
   dsn := "root:1234qwer@tcp(127.0.0.1:3306)/go_test"
   // 原生的 是open ，sqlx里面是connect 里面包含了ping的操作
   db, err = sqlx.Connect("mysql", dsn)
   if err != nil {
      panic(err)
   }

   db.SetConnMaxLifetime(time.Second * 10)
   // 设置最大的连接数 默认是无限制 如果超出限制了 就会排队等待
   db.SetMaxOpenConns(200)
   // 设置最大的空闲连接数 默认是无限制 业务量小的时候 可以把多余的连接释放掉，只保留一定数量的连接数
   db.SetMaxIdleConns(10)
   return nil
}

Read

查询单行:

// 查询单条数据示例
  func queryRowDemo() {
      sqlStr := "select id, name, age from user where id=?"
      var u user
      err := db.Get(&u, sqlStr, 1)
      if err != nil {
          fmt.Printf("get failed, err:%v\n", err)
          return
      }
      fmt.Printf("id:%d name:%s age:%d\n", u.ID, u.Name, u.Age)
  }

查询多行:

        // 查询多条数据示例
    func queryMultiRowDemo() {
        sqlStr := "select id, name, age from user where id > ?"
        var users []user
        err := db.Select(&users, sqlStr, 0)
        if err != nil {
            fmt.Printf("query failed, err:%v\n", err)
            return
        }
        fmt.Printf("users:%#v\n", users)
    }
    ````

### insert、update、delete
- sqlx中的exec方法与原生sql中的exec使用基本一致
```go
// 插入数据
func insertRowDemo() {
	sqlStr := "insert into user(name, age) values (?,?)"
	ret, err := db.Exec(sqlStr, "Piwriw", 18)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	theID, err := ret.LastInsertId() // 新插入数据的id
	if err != nil {
		fmt.Printf("get lastinsert ID failed, err:%v\n", err)
		return
	}
	fmt.Printf("insert success, the id is %d.\n", theID)
}

// 更新数据
func updateRowDemo() {
	sqlStr := "update user set age=? where id = ?"
	ret, err := db.Exec(sqlStr, 22, 1)
	if err != nil {
		fmt.Printf("update failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("update success, affected rows:%d\n", n)
}

// 删除数据
func deleteRowDemo() {
	sqlStr := "delete from user where id = ?"
	ret, err := db.Exec(sqlStr, 1)
	if err != nil {
		fmt.Printf("delete failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("delete success, affected rows:%d\n", n)
}

进阶特性用法

NamedExec

DB.NamedExec:方法用来绑定SQL语句与结构体或map中的同名字段,使用了KV形式

func insertUserDemo()(err error){
	sqlStr := "INSERT INTO user (name,age) VALUES (:name,:age)"
	_, err = db.NamedExec(sqlStr,
		map[string]interface{}{
			"name": "Piwriw",
			"age": 18,
		})
	return
}

NamedQuery

NamedQuery:KV下的查询方法

func namedQuery(){
	sqlStr := "SELECT * FROM user WHERE name=:name"
	// 使用map做命名查询
	rows, err := db.NamedQuery(sqlStr, map[string]interface{}{"name": "Piwriw"})
	if err != nil {
		fmt.Printf("db.NamedQuery failed, err:%v\n", err)
		return
	}
	defer rows.Close()
        // 遍历rows
	for rows.Next(){
		var u user
                 // 绑定row
		err := rows.StructScan(&u)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			continue
		}
		fmt.Printf("user:%#v\n", u)
	}

	u := user{
		Name: "Piwriw",
	}
	// 使用结构体命名查询，根据结构体字段的 db tag进行映射
	rows, err = db.NamedQuery(sqlStr, u)
	if err != nil {
		fmt.Printf("db.NamedQuery failed, err:%v\n", err)
		return
	}
	defer rows.Close()
	for rows.Next(){
		var u user
		err := rows.StructScan(&u)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			continue
		}
		fmt.Printf("user:%#v\n", u)
	}
}

事务操作

db.Beginx(): 开启事务
tx.Exec(): 执行事务
tx.Commit(): 提交事务

tx.Rollback(): 事务回滚

func transactionDemo2()(err error) {
	tx, err := db.Beginx() // 开启事务
	if err != nil {
		fmt.Printf("begin trans failed, err:%v\n", err)
		return err
	}
    // 当出现panic的时候，先回滚再执行panic操作
    // 同时出现err的时候，也进行回滚
	defer func() {
		if p := recover(); p != nil {
			tx.Rollback()
			panic(p) // re-throw panic after Rollback
		} else if err != nil {
			fmt.Println("rollback")
			tx.Rollback() // err is non-nil; don't change it
		} else {
			err = tx.Commit() // err is nil; if Commit returns error update err
			fmt.Println("commit")
		}
	}()

	sqlStr1 := "Update user set age=20 where id=?"

	rs, err := tx.Exec(sqlStr1, 1)
	if err!= nil{
		return err
	}
	n, err := rs.RowsAffected()
	if err != nil {
		return err
	}
	if n != 1 {
		return errors.New("exec sqlStr1 failed")
	}
	sqlStr2 := "Update user set age=50 where i=?"
	rs, err = tx.Exec(sqlStr2, 5)
	if err!=nil{
		return err
	}
	n, err = rs.RowsAffected()
	if err != nil {
		return err
	}
	if n != 1 {
		return errors.New("exec sqlStr1 failed")
	}
	return err
}

sqlx.In批量插入

其中sqlx.in注意使用...

// BatchInsertUsers2 使用sqlx.In帮我们拼接语句和参数, 注意传入的参数是[]interface{}
func BatchInsertUsers(users []interface{}) error {
	query, args, _ := sqlx.In(
		"INSERT INTO user (name, age) VALUES (?), (?), (?)",
		users..., 
        // 如果arg实现了 driver.Valuer, sqlx.In 会通过调用 Value()来展开它,必须使用...
	)
	fmt.Println(query) // 查看生成的querystring
	fmt.Println(args)  // 查看生成的args
	_, err := sqlxdb.Exec(query, args...)
	return err
}

关于sqlx.In一个用法，在sqlx查询语句中实现In查询和FIND_IN_SET函数。即实现SELECT * FROM user WHERE id in (3, 2, 1);和SELECT * FROM user WHERE id in (3, 2, 1) ORDER BY FIND_IN_SET(id, ‘3,2,1’)

// QueryByIDs 根据给定ID查询
func QueryByIDs(ids []int)(users []User, err error){
	// 动态填充id
	query, args, err := sqlx.In("SELECT name, age FROM user WHERE id IN (?)", ids)
	if err != nil {
		return
	}
	// sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它
	query = DB.Rebind(query)

	err = DB.Select(&users, query, args...)
	return
}

// QueryAndOrderByIDs 按照指定id查询并维护顺序
func QueryAndOrderByIDs(ids []int)(users []User, err error){
	// 动态填充id
	strIDs := make([]string, 0, len(ids))
	for _, id := range ids {
		strIDs = append(strIDs, fmt.Sprintf("%d", id))
	}
	query, args, err := sqlx.In("SELECT name, age FROM user WHERE id IN (?) ORDER BY FIND_IN_SET(id, ?)", ids, strings.Join(strIDs, ","))
	if err != nil {
		return
	}

	// sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它
	query = DB.Rebind(query)

	err = DB.Select(&users, query, args...)
	return
}

使用NamedExec实现批量插入

该功能需1.3.1版本以上，并且1.3.1版本目前还有点问题，sql语句最后不能有空格和; 详见issues/690

// BatchInsertUsers3 使用NamedExec实现批量插入
func BatchInsertUsers(users []*User) error {
	_, err := DB.NamedExec("INSERT INTO user (name, age) VALUES (:name, :age)", users)
	return err
}