淘先锋技术网

首页 1 2 3 4 5 6 7

Go基于sqlx实现的sql增删改查的工具类

文章目录

1、驱动下载

sqlx是Go语言内置database/sql的扩展包，它在内置database/sql基础上提供更简洁的数据库操作。
GitHub地址:https://github.com/jmoiron/sqlx
API文档:https://pkg.go.dev/github.com/jmoiron/sqlx
用例文档http://jmoiron.github.io/sqlx/

1.1、依赖安装

依赖包安装命令

go get github.com/jmoiron/sqlx

1.2、SQL数据库驱动包列表

go驱动包列表https://github.com/golang/go/wiki/SQLDrivers。

database/sql和database/sql/driver包分别用于使用Go中的数据库和实现数据库驱动程序。
Go的sql包的驱动程序包括:

标有[*]的驱动程序都包含在https://github.com/bradfitz/go-sql-test的兼容性测试套件中并通过了该测试套件。
标记为[**]的驱动程序通过兼容性测试套件，但当前未包含在其中。

2、实现代码

2.1 sql工具类代码

例子使用了mysql驱动
封装的工具类sqldao.go

package dao

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

type SqlDao struct {
	db        *sqlx.DB
	tx        *sqlx.Tx
	Driver    string
	Dsn       string
	OpenConns int
	IdleConns int
}

func (dao *SqlDao) Connect() (err error) {
	dao.db, err = sqlx.Connect(dao.Driver, dao.Dsn)
	if err == nil {
		if dao.OpenConns > 0 {
			dao.db.SetMaxOpenConns(20)
		}
		if dao.IdleConns > 0 {
			dao.db.SetMaxIdleConns(20)
		}
	}
	return err
}
func (dao *SqlDao) Close() {
	dao.db.Close()
}
func (dao *SqlDao) InsertOne(sql string, args ...any) (int64, error) {
	result, err := dao.db.Exec(sql, args...)
	if err == nil {
		tid, err := result.LastInsertId()
		return tid, err
	}
	return 0, err
}

func (dao *SqlDao) InsertOneObj(sql string, obj interface{}) (int64, error) {
	result, err := dao.db.NamedExec(sql, obj)
	if err == nil {
		id, err := result.LastInsertId()
		return id, err
	}
	return 0, err
}

// 批量插入
// return int64,error 插入成功数量，错误
func (dao *SqlDao) InsertManyObj(sql string, objs []interface{}) (int64, error) {
	result, err := dao.db.NamedExec(sql, objs)
	if err == nil {
		count, err := result.RowsAffected()
		return count, err
	}
	return 0, err
}

func (dao *SqlDao) FindOne(model any, sql string, args ...interface{}) error {
	err := dao.db.Get(model, sql, args...)
	return err
}

func (dao *SqlDao) FindMany(model any, sql string, args ...interface{}) error {
	err := dao.db.Select(model, sql, args...)
	return err
}

// 更新数据
func (dao *SqlDao) Update(sql string, args ...any) (int64, error) {
	ret, err := dao.db.Exec(sql, args...)
	if err == nil {
		n, err := ret.RowsAffected()
		return n, err
	}
	return 0, err
}

// 删除数据
func (dao *SqlDao) Delete(sql string, args ...any) (int64, error) {
	ret, err := dao.db.Exec(sql, args...)
	if err == nil {
		n, err := ret.RowsAffected()
		return n, err
	}
	return 0, err
}

// 开启事务
func (dao *SqlDao) Beginx() (*sqlx.Tx, error) {
	var err error
	dao.tx, err = dao.db.Beginx() // 开启事务
	return dao.tx, err
}

// 事务回滚
func (dao *SqlDao) Rollback() error {
	return dao.tx.Rollback()
}

// 事务提交
func (dao *SqlDao) Commit() error {
	return dao.tx.Commit()
}

2.2 使用例子

package main

import (
	"fmt"
	"log"
	"mydb/dao"
	"strconv"
	"time"
)

/*
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
*/

type User struct {
	Id   int64  `sql:"id"`
	Name string `sql:"name"`
	Age  int    `sql:"age"`
}

func (s User) String() string {
	return fmt.Sprintf("%v %v %v", s.Id, s.Name, s.Age)
}
func main() {
	dao := dao.SqlDao{
		Driver: "mysql",
		Dsn:    "root:peng123@tcp(127.0.0.1:3306)/gotest?charset=utf8mb4&parseTime=True&loc=Asia%2fShanghai",
	}
	err := dao.Connect()
	if err != nil {
		log.Fatalln("connect error====")
	}
	defer dao.Close()

	insertId1, err1 := dao.InsertOne("insert into user(name, age) values(?,?)", "stu1", 11)
	fmt.Printf("insert sql insertId:%v,%v\n", insertId1, err1)

	dao.InsertOneObj("insert into user(name, age) values(:name,:age)", User{Name: "stu2", Age: 67})

	users := []interface{}{
		User{Name: "stu9", Age: 20},
		User{Name: "stu10", Age: 21},
	}
	rowsAffected, err2 := dao.InsertManyObj("insert into user(name,age) values(:name,:age)", users)
	fmt.Printf("======InsertObj2:%v,%v\n", rowsAffected, err2)

	var user3 User
	dao.FindOne(&user3, "select * from user where name=?", "stu1")
	fmt.Printf("======FindOne:%v\n", user3)

	var user4 []User
	dao.FindMany(&user4, "select * from user where age < ?", 5)
	for index, u := range user4 {
		fmt.Printf("======FindMany:%v, %v\n", index, u)
	}

	rowsAffected5, err5 := dao.Update("update user set age=? where id=?", 25, 1)
	fmt.Printf("======Update:%v,%v\n", rowsAffected5, err5)

	rowsAffected6, err6 := dao.Delete("delete from user where age=?", 11)
	fmt.Printf("======Delete:%v,%v\n", rowsAffected6, err6)

	dao.Beginx()
	for i := 0; i <= 400000; i++ {
		dao.InsertOneObj("insert into user(name, age) values(:name,:age)", User{Name: "stu" + strconv.Itoa(i), Age: i})
		if i != 0 && i%10000 == 0 {
			time.Sleep(time.Duration(1) * time.Second)
			err = dao.Commit()
			fmt.Printf("======Commit i:%v, %v\n", i, err)
			dao.Beginx()
		}
	}
}

2.3 运行效果

D:\project\go\gotest\sql>go run main.go
insert sql insertId:1,<nil>
======InsertObj2:2,<nil>
======FindOne:1 stu1 11
======Update:1,<nil>
======Delete:0,<nil>
======Commit i:10000, <nil>
======Commit i:20000, <nil>
======Commit i:30000, <nil>
======Commit i:40000, <nil>
======Commit i:50000, <nil>
======Commit i:60000, <nil>
======Commit i:70000, <nil>
======Commit i:80000, <nil>
======Commit i:90000, <nil>
======Commit i:100000, <nil>