sqlx - golang database/sql 的通用扩展

go 操作数据库有多种方式，比如之前介绍的 gorm：go gin 封装gorm ，gorm 基本操作

今天介绍的sqlx，是Go的另一个包，它在优秀的内置database/sql包之上提供了一组扩展。

安装

go get github.com/jmoiron/sqlx

init

package db

import (
	"fmt"
	"github.com/jmoiron/sqlx"
)

var Conn *sqlx.DB

func InitDB() (err error) {
	dsn := "root:@tcp(127.0.0.1:3306)/ent?charset=utf8mb4&parseTime=True"
	// 也可以使用MustConnect连接不成功就panic
	Conn, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n", err)
		return
	}
	Conn.SetMaxOpenConns(20)
	Conn.SetMaxIdleConns(10)
	return
}

main

func main() {
	err := db.InitDB()
	if err != nil {
		return
	}
}

models

package models

import (
	"database/sql/driver"
	"enterprise-api/core/db"
	"fmt"
	"github.com/jmoiron/sqlx"
)

type User struct {
	Id         int    `json:"id"`
	Name       string `json:"name"`
	Memo       string `json:"memo"`
	CreateTime int64  `json:"create_time"`
	UpdateTime int64  `json:"update_time"`
}

// 插入数据
func InsertRowDemo() (id int64, err error) {
	sqlstr := "insert into cw_test (name, memo) values (?,?)"
	ret, err := db.Conn.Exec(sqlstr, "沙河小王子", "xx")
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	id, 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. In", id)
	return
}

// 删除数据
func DeleteRowDemo() (n int64, err error) {
	sqlstr := "delete from cw_test where id = ?"
	ret, err := db.Conn.Exec(sqlstr, 6)
	if err != nil {
		fmt.Printf("delete failed, err:% in", 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)
	return
}

// 更新数据
func UpdateRowDemo() (n int64, err error) {
	sqlstr := "update cw_test set memo=? where id = ?"
	ret, err := db.Conn.Exec(sqlstr, "xx2", 7)
	if err != nil {
		fmt.Printf("update failed, err:%\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)
	return
}

// 查询单条数据示例
func QueryRowDemo() (u User, err error) {
	sqlstr := "select id, name, memo from cw_test where id=?"
	err = db.Conn.Get(&u, sqlstr, 1)
	if err != nil {
		fmt.Printf("get failed, err:%v\n", err)
		return
	}
	fmt.Printf("id :%d name :%s memo:%sln", u.Id, u.Name, u.Memo)
	return
}

// 查询多条数据示例
func QueryMultiRowDemo() (users []User, err error) {
	sqlstr := "select id, name, memo from cw_test where id > ?"
	err = db.Conn.Select(&users, sqlstr, 0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	fmt.Printf("users :%tv\n", users)
	return
}

// NameExec方法用来鄉定SQL语句与结构体或map中的同名字段。
func InsertUserDemo() (id int64, err error) {
	sqlstr := "INSERT INTO cw_test (name, memo) VALUES (:name, :memo)"
	ret, err := db.Conn.NamedExec(sqlstr, map[string]interface{}{
		"name": "小王子2",
		"memo": "xx",
	})
	id, err = ret.LastInsertId() //新插入数据的id
	if err != nil {
		fmt.Printf("get lastinsert ID failed, err:%v\n", err)
		return
	}
	return
}

func (u User) Value() (driver.Value, error) {
	return []interface{}{u.Name, u.Memo}, nil
}

func InsertAll() (id int64, err error) {
	sqlStr := "insert into cw_test(name,memo) values(?),(?),(?),(?),(?)"
	users := []interface{}{
		User{Name: "骚包1号", Memo: "21"},
		User{Name: "骚包2号", Memo: "22"},
		User{Name: "骚包3号", Memo: "23"},
		User{Name: "骚包4号", Memo: "24"},
		User{Name: "骚包5号", Memo: "25"},
	}
	query, args, _ := sqlx.In(sqlStr, users...)
	fmt.Println(query) // 查看生成的查询语句
	fmt.Println(args)  // 查看生成的args
	ret, err := db.Conn.Exec(query, args...)
	id, err = ret.LastInsertId() //新插入数据的id
	if err != nil {
		fmt.Printf("get lastinsert ID failed, err:%v\n", err)
		return
	}
	return
}

// 通过ids进行查询数据
func FindUserByIds() (users []User, err error) {
	sqlStr := "select id, name, memo from cw_test where id in (?)"
	ids := []int{1, 2, 3, 4, 5}
	// 动态进行查询
	query, args, _ := sqlx.In(sqlStr, ids)
	query = db.Conn.Rebind(query)
	err = db.Conn.Select(&users, query, args...)
	if err != nil {
		fmt.Printf("failed, err:%v\n", err)
		return
	}
	return
}

controllers

	//id, err := models.InsertRowDemo()
	//n, err := models.DeleteRowDemo()
	//n, err := models.UpdateRowDemo()
	//u, err := models.QueryRowDemo()
	//us, err := models.QueryMultiRowDemo()
	//id, err := models.InsertUserDemo()
	//id, err := models.InsertAll()
	//us, err := models.FindUserByIds()

参考

https://www.jianshu.com/p/c8a0e56cefdd

https://blog.csdn.net/qq_43514659/article/details/121554276

http://jmoiron.github.io/sqlx/