广州网站建设工作室招聘,文创产品设计分析,学设计需要什么条件,软件设计师证书有用吗文章目录 1、驱动下载1.1、依赖安装1.2、SQL数据库驱动包列表 2、实现代码2.1 sql工具类代码2.2 使用例子2.3 运行效果 1、驱动下载
sqlx是Go语言内置database/sql的扩展包#xff0c;它在内置database/sql基础上提供更简洁的数据库操作。 GitHub地址:https://github.com/jmo… 文章目录 1、驱动下载1.1、依赖安装1.2、SQL数据库驱动包列表 2、实现代码2.1 sql工具类代码2.2 使用例子2.3 运行效果 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/sqlx1.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/athenadriverAWS Athena: https://github.com/segmentio/go-athenaAWS DynamoDB: https://github.com/btnguyen2k/godynamoApache Avatica/Phoenix: https://github.com/apache/calcite-avatica-goApache H2: https://github.com/jmrobles/h2goApache Hive: https://github.com/sql-machine-learning/gohiveApache Ignite/GridGain: https://github.com/amsokol/ignite-go-clientApache Impala: https://github.com/bippio/go-impalaAzure Cosmos DB: https://github.com/btnguyen2k/gocosmosClickHouse (uses HTTP API): https://github.com/mailru/go-clickhouseClickHouse (uses native TCP interface): https://github.com/ClickHouse/clickhouse-goCockroachDB: Use any PostgreSQL driverCouchbase N1QL: https://github.com/couchbase/go_n1qlDB2 LUW (uses cgo): https://github.com/asifjalil/cliDB2 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_dbDatabricks: https://github.com/databricks/databricks-sql-goDuckDB: https://github.com/marcboeker/go-duckdbExasol: (pure Go): https://github.com/exasol/exasol-driver-goFirebird SQL: https://github.com/nakagami/firebirdsqlGenji (pure go): https://github.com/genjidb/genjiGoogle Cloud BigQuery: https://github.com/solcates/go-sql-bigqueryGoogle Cloud Spanner: https://github.com/googleapis/go-sql-spannerGoogle Cloud Spanner: https://github.com/rakyll/go-sql-driver-spannerMS ADODB: https://github.com/mattn/go-adodbMS SQL Server (pure go): https://github.com/microsoft/go-mssqldbMS SQL Server (uses cgo): https://github.com/minus5/gofreetdsMaxCompute: https://github.com/sql-machine-learning/gomaxcomputeMySQL: 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/odbcOracle (pure go): https://github.com/sijms/go-oraOracle (uses cgo): https://github.com/godror/godrorOracle (uses cgo): https://github.com/mattn/go-oci8Oracle (uses cgo): https://gopkg.in/rana/ora.v4Postgres (pure Go): https://github.com/jackc/pgx [*]Postgres (pure Go): https://github.com/lib/pq [*]Postgres (uses cgo): https://github.com/jbarham/gopgsqldriverPresto: https://github.com/prestodb/presto-go-clientQL: https://pkg.go.dev/modernc.org/qlSAP ASE (pure go): https://github.com/SAP/go-aseSAP ASE (uses cgo): https://github.com/SAP/cgo-aseSAP HANA (pure go): https://github.com/SAP/go-hdbSAP HANA (uses cgo): https://help.sap.com/viewer/0eec0d68141541d1b07893a39944924e/2.0.03/en-US/0ffbe86c9d9f44338441829c6bee15e6.htmlSQL over REST: https://github.com/adaptant-labs/go-sql-rest-driverSQLite (uses cgo): https://github.com/gwenn/gosqlite - Supports SQLite dynamic data typingSQLite (uses cgo): https://github.com/mattn/go-sqlite3 [*]SQLite (uses cgo): https://github.com/mxk/go-sqliteSQLite: (pure go): https://modernc.org/sqliteSQLite: (uses cgo): https://github.com/rsc/sqliteSingleStore: Use any MySQL driverSnowflake (pure Go): https://github.com/snowflakedb/gosnowflakeSybase ASE (pure go): https://github.com/thda/tdsSybase SQL Anywhere: https://github.com/a-palchikov/sqlagoTiDB: Use any MySQL driverVertica: https://github.com/vertica/vertica-sql-goVitess: https://pkg.go.dev/vitess.io/vitess/go/vt/vitessdriverYDB (pure go): https://github.com/ydb-platform/ydb-go-sdkYQL (Yahoo! Query Language): https://github.com/mattn/go-yql
标有[*]的驱动程序都包含在https://github.com/bradfitz/go-sql-test的兼容性测试套件中并通过了该测试套件。 标记为[**]的驱动程序通过兼容性测试套件但当前未包含在其中。
2、实现代码
2.1 sql工具类代码
例子使用了mysql驱动 封装的工具类sqldao.go
package daoimport (_ github.com/go-sql-driver/mysqlgithub.com/jmoiron/sqlx
)type SqlDao struct {db *sqlx.DBtx *sqlx.TxDriver stringDsn stringOpenConns intIdleConns 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 errordao.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 mainimport (fmtlogmydb/daostrconvtime
)/*
CREATE TABLE user (
id bigint(20) NOT NULL AUTO_INCREMENT,
name varchar(100) DEFAULT NULL,
age int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4
*/type User struct {Id int64 sql:idName string sql:nameAge 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:peng123tcp(127.0.0.1:3306)/gotest?charsetutf8mb4parseTimeTruelocAsia%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 Userdao.FindOne(user3, select * from user where name?, stu1)fmt.Printf(FindOne:%v\n, user3)var user4 []Userdao.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\sqlgo 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
