sqlx is a library which provides a set of extensions on go's standard database/sql
library.
sqlx support some db: mysql, postgresql, oracle, sqlite ...
https://github.com/jmoiron/sqlx
示例代码:
该库目前只对查询进行了深度封装,对于更新和插入封装较少。
新建表
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package main import ( "database/sql" _ "github.com/go-sql-driver/mysql" "github.com/jmoiron/sqlx" ) var schema = `Create table person2( first_name varchar(20), last_name varchar(20), email varchar(20)); ` var schema2 = `Create table place( country varchar(20), city varchar(20) NULL, telcode int(20)); ` type Person struct { FirstName string `db:"first_name"` LastName string `db:"last_name"` Email string } type Place struct { Country string city sql.NullString TelCode int } func main() { dsn := "root:123456@tcp(172.16.65.200:3306)/golang" db, err := sqlx.Connect("mysql", dsn) if err != nil { panic(err) } result, err := db.Exec(schema2) if err != nil { panic(err) } _, err = result.RowsAffected() if err != nil { panic(err) } }
单行查询使用 sqlx.Get(),多行查询使用 sqlx.Select()
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package main import ( "database/sql" _ "github.com/go-sql-driver/mysql" "fmt" "log" "github.com/jmoiron/sqlx" ) type User struct { Id int `db:"id"` Name string `db:"name"` Age int `db:"age"` } // 单行查询,如果查询到多个结果,只返回第一行,查询不到结果就ErrNoRows错误。 func QueryRow(db *sqlx.DB) { var user User err := db.Get(&user, "select id, name, age from user where id=?", 1) if err == sql.ErrNoRows { log.Printf("not found data of the id:%d", 1) } if err != nil { panic(err) } fmt.Printf("user: %#v\n", user) } // 多行查询, 查询不到任何记录也不会报错。 func Query(db *sqlx.DB) { var users []*User err := db.Select(&users, "select id, name, age from user") if err != nil { panic(err) } if err == sql.ErrNoRows { log.Printf("not found data") return } for _, user := range users { fmt.Println(user.Id, user.Name) } } func main() { dsn := "root:123456@tcp(172.16.65.200:3306)/golang" db, err := sqlx.Connect("mysql", dsn) if err != nil { panic(err) } err = db.Ping() if err != nil { panic(err) } fmt.Printf("connect to db success\n") QueryRow(db) Query(db) }
更新和插入使用sqlx.Exec()
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package main import ( _ "github.com/go-sql-driver/mysql" "fmt" "github.com/jmoiron/sqlx" ) func Update(db *sqlx.DB) { name := "Miles" age := 88 id := 3 result, err := db.Exec("update user set name=?, age=? where id=?", name, age, id) if err != nil { panic(err) } // RowsAffected returns the number of rows affected by an // update, insert, or delete. rowsAffected, err := result.RowsAffected() if err != nil { panic(err) } fmt.Printf("update id:%d, affect rows:%d\n", id, rowsAffected) } func Insert(db *sqlx.DB) { name := "Lucy" age := 18 result, err := db.Exec("insert into user(name, age) values (?,?)", name, age) if err != nil { panic(err) } id, err := result.LastInsertId() if err != nil { panic(err) } affected, err := result.RowsAffected() if err != nil { panic(err) } fmt.Printf("last insert id:%d affect rows:%d\n", id, affected) } func main() { dsn := "root:123456@tcp(172.16.65.200:3306)/golang" db, err := sqlx.Connect("mysql", dsn) if err != nil { panic(err) } err = db.Ping() if err != nil { panic(err) } fmt.Println("connect to db success!!!") Update(db) Insert(db) }
预处理,直接使用原生的sql.db,没有进行过任何封装
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package main import ( _ "github.com/go-sql-driver/mysql" "fmt" "github.com/jmoiron/sqlx" ) type User2 struct { Id int `db:"id"` Name string `db:"name"` Age int `db:"age"` } // 预处理是为了提高查询性能; // 实现的原理是先将查询语句发送给Mysql数据库做预解析; // 然后再将需要查询的条件数据发送给Mysql数据库进行执行; // 这种原理类似于GO语言和Python语言执行效率的对比; // Go语言是需要先编译的,Python是一边执行一边编译。 func PrepareQuery(db *sqlx.DB, id int) { stmt, err := db.Prepare("select id, name, age from user where id>?") if err != nil { panic(err) } rows, err := stmt.Query(id) if err != nil { panic(err) } defer stmt.Close() defer rows.Close() for rows.Next(){ var user User2 err := rows.Scan(&user.Id, &user.Name, &user.Age) if err != nil { panic(err) } fmt.Printf("user: %#v\n", user) } } func main() { dsn := "root:123456@tcp(172.16.65.200:3306)/golang" db, err := sqlx.Connect("mysql", dsn) if err != nil { panic(err) } defer db.Close() PrepareQuery(db, 1) }
原则性操作
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package main import ( _ "github.com/go-sql-driver/mysql" "fmt" "github.com/jmoiron/sqlx" ) func Transaction(db *sqlx.DB) { // 开启事务 tx, err := db.Begin() if err != nil { panic(err) } result, err := tx.Exec("insert into user(name, age)values(?,?)", "Jack", 98) if err != nil { // 失败回滚 tx.Rollback() panic(err) } fmt.Println("result", result) exec, err := tx.Exec("update user set name=?, age=? where id=?", "Jack", 98, 1) if err != nil { // 失败回滚 tx.Rollback() panic(err) } fmt.Println("exec", exec) // 提交事务 err = tx.Commit() if err != nil { // 失败回滚 tx.Rollback() panic(err) } } func main() { dsn := "root:123456@tcp(172.16.65.200:3306)/golang" db, err := sqlx.Connect("mysql", dsn) if err != nil { panic(err) } err = db.Ping() if err != nil { panic(err) } Transaction(db) }
Named
// Named queries can use structs, so if you have an existing struct (i.e. person := &Person{}) that you have populated, you can pass it in as &person tx.NamedExec("INSERT INTO person (first_name, last_name, email) VALUES (:first_name, :last_name, :email)", &Person{"Jane", "Citizen", "jane.citzen@example.com"}) tx.Commit()
更多用法
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package main import ( "database/sql" "fmt" "log" _ "github.com/lib/pq" "github.com/jmoiron/sqlx" ) var schema = ` CREATE TABLE person ( first_name text, last_name text, email text ); CREATE TABLE place ( country text, city text NULL, telcode integer )` type Person struct { FirstName string `db:"first_name"` LastName string `db:"last_name"` Email string } type Place struct { Country string City sql.NullString TelCode int } func main() { // this Pings the database trying to connect, panics on error // use sqlx.Open() for sql.Open() semantics db, err := sqlx.Connect("postgres", "user=foo dbname=bar sslmode=disable") if err != nil { log.Fatalln(err) } // exec the schema or fail; multi-statement Exec behavior varies between // database drivers; pq will exec them all, sqlite3 won't, ymmv db.MustExec(schema) tx := db.MustBegin() tx.MustExec("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "Jason", "Moiron", "jmoiron@jmoiron.net") tx.MustExec("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "John", "Doe", "johndoeDNE@gmail.net") tx.MustExec("INSERT INTO place (country, city, telcode) VALUES ($1, $2, $3)", "United States", "New York", "1") tx.MustExec("INSERT INTO place (country, telcode) VALUES ($1, $2)", "Hong Kong", "852") tx.MustExec("INSERT INTO place (country, telcode) VALUES ($1, $2)", "Singapore", "65") // Named queries can use structs, so if you have an existing struct (i.e. person := &Person{}) that you have populated, you can pass it in as &person tx.NamedExec("INSERT INTO person (first_name, last_name, email) VALUES (:first_name, :last_name, :email)", &Person{"Jane", "Citizen", "jane.citzen@example.com"}) tx.Commit() // Query the database, storing results in a []Person (wrapped in []interface{}) people := []Person{} db.Select(&people, "SELECT * FROM person ORDER BY first_name ASC") jason, john := people[0], people[1] fmt.Printf("%#v\n%#v", jason, john) // Person{FirstName:"Jason", LastName:"Moiron", Email:"jmoiron@jmoiron.net"} // Person{FirstName:"John", LastName:"Doe", Email:"johndoeDNE@gmail.net"} // You can also get a single result, a la QueryRow jason = Person{} err = db.Get(&jason, "SELECT * FROM person WHERE first_name=$1", "Jason") fmt.Printf("%#v\n", jason) // Person{FirstName:"Jason", LastName:"Moiron", Email:"jmoiron@jmoiron.net"} // if you have null fields and use SELECT *, you must use sql.Null* in your struct places := []Place{} err = db.Select(&places, "SELECT * FROM place ORDER BY telcode ASC") if err != nil { fmt.Println(err) return } usa, singsing, honkers := places[0], places[1], places[2] fmt.Printf("%#v\n%#v\n%#v\n", usa, singsing, honkers) // Place{Country:"United States", City:sql.NullString{String:"New York", Valid:true}, TelCode:1} // Place{Country:"Singapore", City:sql.NullString{String:"", Valid:false}, TelCode:65} // Place{Country:"Hong Kong", City:sql.NullString{String:"", Valid:false}, TelCode:852} // Loop through rows using only one struct place := Place{} rows, err := db.Queryx("SELECT * FROM place") for rows.Next() { err := rows.StructScan(&place) if err != nil { log.Fatalln(err) } fmt.Printf("%#v\n", place) } // Place{Country:"United States", City:sql.NullString{String:"New York", Valid:true}, TelCode:1} // Place{Country:"Hong Kong", City:sql.NullString{String:"", Valid:false}, TelCode:852} // Place{Country:"Singapore", City:sql.NullString{String:"", Valid:false}, TelCode:65} // Named queries, using `:name` as the bindvar. Automatic bindvar support // which takes into account the dbtype based on the driverName on sqlx.Open/Connect _, err = db.NamedExec(`INSERT INTO person (first_name,last_name,email) VALUES (:first,:last,:email)`, map[string]interface{}{ "first": "Bin", "last": "Smuth", "email": "bensmith@allblacks.nz", }) // Selects Mr. Smith from the database rows, err = db.NamedQuery(`SELECT * FROM person WHERE first_name=:fn`, map[string]interface{}{"fn": "Bin"}) // Named queries can also use structs. Their bind names follow the same rules // as the name -> db mapping, so struct fields are lowercased and the `db` tag // is taken into consideration. rows, err = db.NamedQuery(`SELECT * FROM person WHERE first_name=:first_name`, jason) }