1. Getting Started

go get github.com/jmoiron/sqlx
github.com/go-sql-driver/mysql

2. Handle Types

4 handle types:

2 cursor types:

3. Connecting to Database

var dsn = "root:123456@tcp(127.0.0.1:3306)/mydb?parseTime=true&&charset=utf8mb4"
var db *sqlx.DB
// 1. same as sql.Open()
db, err = sqlx.Open("mysql", dsn)
err = db.Ping()
// force a connection and test that is worked 
// 2. open and connect at the same time
db, err = sqlx.Connect("mysql", dsn)
// 3. same as 2, but panic on error
db = sqlx.MustConnect("mysql", dsn)

4. Querying

// 1. unchanged from database/sql
Exec(query string, args ...interface{}) (sql.Result, error)
Query(query string, args ...interface{}) (*sql.Rows, error)
QueryRow(query string, args ...interface{}) *sql.Row
// 2. extensions
MustExec(query string, args ...interface{}) sql.Result
Queryx(query string, args ...interface{}) (*sqlx.Rows, error)
QueryRowx(query string, args ...interface{}) *sqlx.Row
// 3. new semantics: 结构体struct与数据库schema绑定
Select(dest interface{}, query string, args ...interface{}) error
Get(dest interface{}, query string, args ...interface{}) error
// An error is returned if the result set is empty
// 4. sqlx.Row
type Rows struct {
*sql.Rows
unsafe bool
Mapper *reflectx.Mapper
// these fields cache memory use for a rows during iteration w/ structScan
started bool
fields
[][]int
values
[]interface{}
}
// 5. sql.Result
LastInsertId() (int64, error)
RowsAffected() (int64, error)

示例：

func querying(db *sqlx.DB) {
// 1. Exec & MustExec
schema := `CREATE TABLE IF NOT EXISTS person (
id INT(10) AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
age TINYINT,
address VARCHAR(100)
)`
db.MustExec(schema)
sqlStr := "insert into person(name, age) values(?, ?)"
db.MustExec(sqlStr, "jack", 21)
db.MustExec(sqlStr, "maxin", 30)
sqlStr = "insert into person(name, age, address) values(?, ?, ?)"
result, err := db.Exec(sqlStr, "lucy", 39, "London, UK")
if err != nil {
panic(err)
}
id, _ := result.LastInsertId()
fmt.Printf("last insert id is %dn", id)
// 2. Query & Queryx
sqlStr = "select * from person"
rows1, err := db.Query(sqlStr)
if err != nil {
panic(err)
}
for rows1.Next() {
var id int
var name string
var age uint8
var address sql.NullString
err = rows1.Scan(&id, &name, &age, &address)
if err != nil {
panic(err)
}
fmt.Printf("id: %d, name: %s, age: %d, address: %vn", id, name, age, address)
}
type person struct {
Id
int
Name
string
Age
uint8
Address sql.NullString
}
rows2, err := db.Queryx(sqlStr)
if err != nil {
panic(err)
}
for rows2.Next() {
var p person
rows2.Scan(&p)
fmt.Printf("%#vn", p)
}
// 3. Get & Select
var p person
var pp []person
err = db.Get(&p, "select * from person limit 1")
if err != nil {
panic(err)
}
fmt.Printf("%#vn", p)
err = db.Select(&pp, "select * from person where id > 2")
if err != nil {
panic(err)
}
fmt.Printf("%#vn", pp)
var count int
db.Get(&count, "select count(*) from person")
fmt.Println(count)
var names []string
db.Select(&names, "select name from person")
fmt.Println(names)
}

5. Transactions

// 1. sql.Tx
Begin() (*sql.Tx, error)
// 2. sqlx.Tx
Beginx() (*sqlx.Tx, error)
MustBegin() (*sql.Tx)

示例：

func transaction(db *sqlx.DB) {
tx := db.MustBegin()
defer func() {
if err := recover(); err != nil {
tx.Rollback()
}
}()
tx.MustExec("delete from person where id=4")
tx.MustExec("insert into person values(2, 'abc', 22, 'LA')")
tx.MustExec("insert into person values(100, 'abc', 22, 'LA')")
err := tx.Commit()
if err != nil {
panic(err)
}
}

6. Prepared Statements

func prepared(db *sqlx.DB) {
stmt, _ := db.Prepare("select * from person where id=?")
row := stmt.QueryRow(5)
var id int
var name string
var age uint8
var address sql.NullString
row.Scan(&id, &name, &age, &address)
fmt.Printf("id: %d, name: %s, age: %d, address: %vn", id, name, age, address)
stmtx, _ := db.Preparex("select * from person where id=?")
rowx := stmtx.QueryRowx(5)
var p person
rowx.Scan(&p)
fmt.Printf("%#vn", p)
}

7. Query Helpers

7.1 “In” Queries

func inQuery(db *sqlx.DB) {
ids := []int{1, 2, 3, 4, 5}
/*	// converting argument $1 type: unsupported type []int, a slice of int
rows, err := db.Query("select name from person where id in (?)", ids)
if err != nil {
panic(err)
}
for rows.Next() {
var name string
rows.Scan(&name)
fmt.Println(name)
}*/
// convert to (?, ?, ...)
query, args, err := sqlx.In("select name from person where id in (?)", ids)
if err != nil {
panic(err)
}
query = db.Rebind(query)
fmt.Println(query)
rows, err := db.Query(query, args...)
if err != nil {
panic(err)
}
for rows.Next() {
var name string
rows.Scan(&name)
fmt.Println(name)
}
}

7.2 Named Queries

NamedQuery(query string, arg interface{}) (*sqlx.Rows, error)
NamedExec(query string, arg interface{}) (sql.Result, error)
PrepareNamed(query string) (*NamedStmt, error)

示例：

func namedQuery(db *sqlx.DB) {
// named query with a struct
p := person{Name: "jack"}
rows, _ := db.NamedQuery("select count(*) from person where name=:name", p)
for rows.Next() {
var count int
rows.Scan(&count)
fmt.Println(count)
}
// named query with a map
m := map[string]interface{}{"address": "LA"}
stmt, _ := db.PrepareNamed("select * from person where address=:address limit 1")
row := stmt.QueryRowx(m)
row.Scan(&p)
fmt.Printf("%#vn", p)
}

8. Alternate Scan Types

func alternateScan(db *sqlx.DB) {
rows, _ := db.Queryx("select * from person")
for rows.Next() {
cols, _ := rows.SliceScan()
fmt.Println(cols)
}
rows, _ = db.Queryx("select * from person")
for rows.Next() {
cols := make(map[string]interface{})
rows.MapScan(cols)
fmt.Println(cols)
}
}

9. Connection Pool

DB.SetMaxIdleConns(n int)
DB.SetMaxOpenConns(n int)

参考：http://jmoiron.github.io/sqlx/

最后

以上就是天真音响为你收集整理的Golang 数据库 sqlx1. Getting Started2. Handle Types3. Connecting to Database4. Querying5. Transactions6. Prepared Statements7. Query Helpers8. Alternate Scan Types9. Connection Pool的全部内容，希望文章能够帮你解决Golang 数据库 sqlx1. Getting Started2. Handle Types3. Connecting to Database4. Querying5. Transactions6. Prepared Statements7. Query Helpers8. Alternate Scan Types9. Connection Pool所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。