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包的驱动程序包括:
- Amazon AWS Athena: https://github.com/uber/athenadriver
- AWS Athena: https://github.com/segmentio/go-athena
- AWS DynamoDB: https://github.com/btnguyen2k/godynamo
- Apache Avatica/Phoenix: https://github.com/apache/calcite-avatica-go
- Apache H2: https://github.com/jmrobles/h2go
- Apache Hive: https://github.com/sql-machine-learning/gohive
- Apache Ignite/GridGain: https://github.com/amsokol/ignite-go-client
- Apache Impala: https://github.com/bippio/go-impala
- Azure Cosmos DB: https://github.com/btnguyen2k/gocosmos
- ClickHouse (uses HTTP API): https://github.com/mailru/go-clickhouse
- ClickHouse (uses native TCP interface): https://github.com/ClickHouse/clickhouse-go
- CockroachDB: Use any PostgreSQL driver
- Couchbase N1QL: https://github.com/couchbase/go_n1ql
- DB2 LUW (uses cgo): https://github.com/asifjalil/cli
- DB2 LUW and DB2/Z with DB2-Connect: https://bitbucket.org/phiggins/db2cli (Last updated 2015-08)
- DB2 LUW, z/OS, iSeries and Informix: https://github.com/ibmdb/go_ibm_db
- Databricks: https://github.com/databricks/databricks-sql-go
- DuckDB: https://github.com/marcboeker/go-duckdb
- Exasol: (pure Go): https://github.com/exasol/exasol-driver-go
- Firebird SQL: https://github.com/nakagami/firebirdsql
- Genji (pure go): https://github.com/genjidb/genji
- Google Cloud BigQuery: https://github.com/solcates/go-sql-bigquery
- Google Cloud Spanner: https://github.com/googleapis/go-sql-spanner
- Google Cloud Spanner: https://github.com/rakyll/go-sql-driver-spanner
- MS ADODB: https://github.com/mattn/go-adodb
- MS SQL Server (pure go): https://github.com/microsoft/go-mssqldb
- MS SQL Server (uses cgo): https://github.com/minus5/gofreetds
- MaxCompute: https://github.com/sql-machine-learning/gomaxcompute
- MySQL: https://github.com/go-sql-driver/mysql/ [*]
- MySQL: https://github.com/siddontang/go-mysql/ [**] (also handles replication)
- MySQL: https://github.com/ziutek/mymysql [*]
- ODBC: https://bitbucket.org/miquella/mgodbc (Last updated 2016-02)
- ODBC: https://github.com/alexbrainman/odbc
- Oracle (pure go): https://github.com/sijms/go-ora
- Oracle (uses cgo): https://github.com/godror/godror
- Oracle (uses cgo): https://github.com/mattn/go-oci8
- Oracle (uses cgo): https://gopkg.in/rana/ora.v4
- Postgres (pure Go): https://github.com/jackc/pgx [*]
- Postgres (pure Go): https://github.com/lib/pq [*]
- Postgres (uses cgo): https://github.com/jbarham/gopgsqldriver
- Presto: https://github.com/prestodb/presto-go-client
- QL: https://pkg.go.dev/modernc.org/ql
- SAP ASE (pure go): https://github.com/SAP/go-ase
- SAP ASE (uses cgo): https://github.com/SAP/cgo-ase
- SAP HANA (pure go): https://github.com/SAP/go-hdb
- SAP HANA (uses cgo): https://help.sap.com/viewer/0eec0d68141541d1b07893a39944924e/2.0.03/en-US/0ffbe86c9d9f44338441829c6bee15e6.html
- SQL over REST: https://github.com/adaptant-labs/go-sql-rest-driver
- SQLite (uses cgo): https://github.com/gwenn/gosqlite - Supports SQLite dynamic data typing
- SQLite (uses cgo): https://github.com/mattn/go-sqlite3 [*]
- SQLite (uses cgo): https://github.com/mxk/go-sqlite
- SQLite: (pure go): https://modernc.org/sqlite
- SQLite: (uses cgo): https://github.com/rsc/sqlite
- SingleStore: Use any MySQL driver
- Snowflake (pure Go): https://github.com/snowflakedb/gosnowflake
- Sybase ASE (pure go): https://github.com/thda/tds
- Sybase SQL Anywhere: https://github.com/a-palchikov/sqlago
- TiDB: Use any MySQL driver
- Vertica: https://github.com/vertica/vertica-sql-go
- Vitess: https://pkg.go.dev/vitess.io/vitess/go/vt/vitessdriver
- YDB (pure go): https://github.com/ydb-platform/ydb-go-sdk
- YQL (Yahoo! Query Language): https://github.com/mattn/go-yql
标有[*]的驱动程序都包含在https://github.com/bradfitz/go-sql-test的兼容性测试套件中并通过了该测试套件。
标记为[**]的驱动程序通过兼容性测试套件,但当前未包含在其中。文章来源:https://www.toymoban.com/news/detail-639733.html
2、实现代码
2.1 sql工具类代码
例子使用了mysql驱动
封装的工具类sqldao.go文章来源地址https://www.toymoban.com/news/detail-639733.html
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>
到了这里,关于Go基于sqlx实现的sql增删改查的工具类的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!