sqlx实战

链接数据库

声明全局变量 + 定义结构体 + 连接数据库

// 声明全局变量db
var db *sqlx.DB

// 结构体存储user表内输出
type User struct {
	ID   int
	Age  int `db:"age"`
	Name string `db:"name"`
}

// 连接数据库
func initDB() (err error) {

	dsn := "root:wangjian@tcp(127.0.0.1:3306)/db1?charset=utf8mb4&parseTime=True"
	// 连接数据库 open + ping
	// db = sqlx.MustConnect("mysql", dsn) // 连接不成功直接panic
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n", err)
		return
	}

	// 最大连接数
	db.SetMaxOpenConns(20)
	// 空闲链接数
	db.SetMaxIdleConns(10)
	return
}

查询

• 单条数据查询 / db.Get()
• 多条数据查询 / db.Select()
• 结构体与字段绑定查询 / db.NamedQuery()
• 多参数查询 / sqlx.In()

// 1. 查询单条数据 / db.Get() / 相当于 query + scan
func queryRowDemo() {
	// sql语句 ——> 字符串
	sqlStr := "select id, name, age from user where id=?"
	var u User
	// 指定id为1 / 将数据存入结构体实例中 
	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)
}


// 2. 查询多条数据 / db.Select()
func queryMultiRowDemo() {
	// sql语句 ——> 字符串
	sqlStr := "select id, name, age from user where id > ?"
	var users []User
	// 指定id>1 / 将数据存入结构体实例中
	err := db.Select(&users, sqlStr, 1)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	fmt.Printf("users:%#v\n", users)
}


// 3. 绑定SQL语句与结构体或map中的同名字段，进行查询操作 / db.NamedQuery() + rows.Next()
func namedQuery(){
	sqlStr := "SELECT * FROM user WHERE name=:name"

	// 1. 使用map做命名查询
	rows, err := db.NamedQuery(sqlStr,
		map[string]interface{}{"name": "Tom"},
	)
	if err != nil {
		fmt.Printf("db.NamedQuery failed, err:%v\n", err)
		return
	}
	defer rows.Close() // 延时关闭

	// 循环存入user结构体实例，并输出
	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)
	}
    
    
// 4. 批量查询 根据批量ID查询数据 / sqlx.In() + db.Rebind() + db.Select()
/*
users,_ := QueryByIDs([]int{4,5,6})
	for _, user := range users {
		fmt.Printf("user:%#v\n", user)
	}
*/
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
}

插入

• 插入单条（行）数据 / db.Exec()
• 结构体与字段绑定插入 / db.NamedExec()
• 批量插入数据 / sqlx.In()

// 1. 插入单条数据 / db.Exec()
func insertRowDemo() {
	sqlStr := "insert into user(name, age) values (?,?)"
	ret, err := db.Exec(sqlStr, "侠奢", 18)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	// 获取新插入数据的id / 验证是否插入成功
	theID, err := ret.LastInsertId()
	if err != nil {
		fmt.Printf("get lastinsert ID failed, err:%v\n", err)
		return
	}
	fmt.Printf("insert success, the id is %d.\n", theID)
}


// 2. 绑定SQL语句与结构体或map中的同名字段，进行插入操作 / db.NamedExec()
func insertUserDemo()(err error){
	sqlStr := "INSERT INTO user (name,age) VALUES (:name,:age)"
	_, err = db.NamedExec(sqlStr,
		map[string]interface{}{
			"name": "Tom",
			"age": 13,
		})
	return
}

// 3. 批量插入数据  sqlx.In / 注意传入的参数是[]interface{}

// 3.1 结构体实现driver.Valuer接口
func (u User) Value() (driver.Value, error) {
	return []interface{}{u.Name, u.Age}, nil
}

// 3.2 sqlx.In拼接 SQL 语句和 插入参数 , 注意传入的参数是[]interface{}
/*
	u1 := User{Age:  1, Name: "Daming"}
	u2 := User{Age:  2, Name: "Xiaoming"}
	u3 := User{Age:  3, Name: "Zhongming"}
	users := []interface{}{u1, u2, u3}
	BatchInsertUsers2(users)
*/
func BatchInsertUsers2(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 := db.Exec(query, args...)
	return err
}

更新

// 更新数据 / db.Exec()
func updateRowDemo() {
	sqlStr := "update user set age=? where id = ?"
	ret, err := db.Exec(sqlStr, 39, 6)
	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)
}

删除

// 删除数据 / db.Exec()
func deleteRowDemo() {
	sqlStr := "delete from user where id = ?"
	ret, err := db.Exec(sqlStr, 6)
	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)
}

事务

// 事务操作 / db.Beginx()
func transactionDemo2()(err error) {
	// 开启事务
	tx, err := db.Beginx()
	if err != nil {
		fmt.Printf("begin trans failed, err:%v\n", err)
		return err
	}

	defer func() {
		// 捕获panic
		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")
		}
	}()

	// 更新语句1
	sqlStr1 := "Update user set age=18 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")
	}

	// 更新语句2
	sqlStr2 := "Update user set age=99 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
}