[Java知识库]sqlx简单应用

package dbclient

import (
	"database/sql/driver"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
	"reflect"
)

var db *sqlx.DB

func InitDB() (err error) {
	dsn := "root:123456@tcp(127.0.0.1:3306)/nacos?charset=utf8mb4&parseTime=True"
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		fmt.Println("dsn 格式有误")
		return err
	}
	//设置最大连接数
	db.SetMaxOpenConns(10)
	//设置最大空闲链接数
	db.SetMaxIdleConns(5)
	fmt.Println("db connect success")
	return
}

//db.Get获取单条记录
func SelectOne() {
	sqlStr := "select username,role from roles where username = ?"
	var r role
	err := db.Get(&r, sqlStr, "nacos")
	if err != nil {
		fmt.Println("db get failed,", err)
		return
	}
	fmt.Printf("%v\n", r)
}

//db.Select 获取多条记录
func SelectMore() {
	sqlStr := "select username,role from roles"
	var rs []role
	err := db.Select(&rs, sqlStr)
	if err != nil {
		fmt.Println("db get failed,", err)
		return
	}
	fmt.Printf("%v\n", rs)
}

func Update() {
	sqlStr := "update roles set role = ? where username =?"
	exec, err := db.Exec(sqlStr, "wwwww", "heheheh")
	if err != nil {
		fmt.Printf("update is failed,err:%v\n", err)
		return
	}
	fmt.Println(exec.RowsAffected())
}

func Insert() {
	sqlStr := "insert into roles values(?,?)"
	exec, err := db.Exec(sqlStr, "wwwww", "heheheh")
	if err != nil {
		fmt.Printf("update is failed,err:%v\n", err)
		return
	}
	fmt.Println(exec.RowsAffected())
}

//使用NamedExec
//用来绑定struct中同名的字段
func NamedExec() {
	sqlStr := "insert into roles values(:username,:role)"
	r := role{
		"zhangsan",
		"admin",
	}
	exec, err := db.NamedExec(sqlStr, r)
	if err != nil {
		fmt.Printf("update is failed,err:%v\n", err)
		return
	}
	fmt.Println(exec.RowsAffected())
}

func NamedQuery() {
	sqlStr := "select * from roles"
	rows, err := db.NamedQuery(sqlStr, role{})
	if err != nil {
		fmt.Println("NamedQuery filed,err:", err)
		return
	}
	for rows.Next() {
		var r role
		rows.StructScan(&r)
		fmt.Println(r)
	}
}

func TxExec() {
	tx, err := db.Begin()
	if err != nil {
		fmt.Println("tx begin failed,err:", err)
		return
	}
	sqlStr := "update roles set role = ? where username =?"
	_, err = tx.Exec(sqlStr, "wwww11w", "heheheh")
	//err = errors.New("ss")
	if err != nil {
		fmt.Printf("update is failed,err:%v\n", err)
		return
		tx.Rollback()
	}
	tx.Commit()
}

//sqlx.in使用

type role struct {
	Username string `db:"username"`
	Role     string `db:"role"`
}

//要使用sqlx.in进行数据插入 需要结构体实现driver.Valuer接口
func (r *role) Value() (driver.Value, error) {
	return []interface{}{r.Username, r.Role}, nil
}

//使用sqlx.in进行插入
func BatchInsert(rs []interface{}) error {
	//这里不太方便需要手写固定个数的（？）
	query, args, err := sqlx.In("insert into roles values(?),(?)", rs...)
	if err != nil {
		fmt.Println("sqlx.in failed", err)
		return err
	}
	fmt.Println(query) //生成的sql语句
	fmt.Println(args)  //生成的参数
	db.Exec(query, args)
	return err
}

// todo 这个方法还是要再看一下
func NamedExecInsert() error {
	var rs = make([]*role, 2)
	rs = append(rs, &role{
		"lisi",
		"admin",
	}, &role{
		"lisi1",
		"admin1",
	})
	exec, err := db.NamedExec("insert into roles values (:username,:role)", rs)
	if err != nil {
		fmt.Println(err)
	}
	fmt.Println(exec.RowsAffected())
	return err
}

func QueryIn(usernames []string) (rs []role, err error) {
	query, args, err := sqlx.In("select * from roles where username in (?)", usernames)
	if err != nil {
		fmt.Println("sqlx.in failed,err", err)
		return nil, err
	}
	fmt.Println("query:", query)
	// sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它
	query = db.Rebind(query)
	fmt.Println(reflect.TypeOf(args))
	// todo 对切片...生成的啥 对数组...生成的切片
	err = db.Select(&rs, query, args...)
	if err != nil {
		fmt.Println("select failed", err)
		return rs, err
	}
	fmt.Println(rs)
	return rs, err
}