Go 数据库 sqlx

1. Getting Started

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

2. Handle Types

4 handle types:

sqlx	database/sql
sqlx.DB	sql.DB
sqlx.Tx	sql.Tx
sqlx.Stmt	sql.Stmt
sqlx.NamedStmt

2 cursor types:

sqlx	database/sql	from
sqlx.Rows	sql.Rows	Queryx
sqlx.Row	sql.Row	QueryRowx

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 %d\n", 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: %v\n", 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("%#v\n", 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("%#v\n", p)

	err = db.Select(&pp, "select * from person where id > 2")
	if err != nil {
		panic(err)
	}
	fmt.Printf("%#v\n", 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: %v\n", id, name, age, address)

	stmtx, _ := db.Preparex("select * from person where id=?")
	rowx := stmtx.QueryRowx(5)

	var p person
	rowx.Scan(&p)
	fmt.Printf("%#v\n", 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("%#v\n", 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)