golang库sqlx的快速使用

该文章的所有例子是通过mysql写的

连接数据库

package main

import (
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
)

var DB *sqlx.DB

func main() {
    db, err := sqlx.Open("mysql","root:123456@tcp(192.168.188.106:3306)/test_db")
    if err != nil {
        fmt.Println("open mysql err",err)
        panic(err)
    }
    fmt.Println("open mysql success")
    DB = db
    err = db.Ping()
    if err != nil {
        fmt.Println("connect mysql error:",err)
        panic(err)
    }
    fmt.Println("test connect mysql success")
    defer db.Close()
}

这里需要知道的是DB实例对象并不是连接对象，而是数据库的抽象，它在内部维护了一个连接池，并在首次需要连接时尝试进行连接 通常我们在项目初始化数据库连接的时候都是需要确定数据库是否连接成功，我们可以通过DB的ping方法进行测试，下面是ping方法的官方说明：

Ping verifies a connection to the database is still alive, establishing a connection if necessary.

当然我们可以通过sqlx.Connect 直接创建连接对象，Connnect方法会在创建DB对象，并执行ping来测试数据库的连接

package main

import (
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
)



func main() {
    db, err := sqlx.Connect("mysql","root:123456@tcp(192.168.188.106:3306)/test_db")
    if err != nil {
        fmt.Println("connect mysql err",err)
        panic(err)
    }
    defer db.Close()
    fmt.Println("connnect mysql success")
}

sqlx中常用方法

1. Exec(…) (sql.Result, error)
2. Query(…) (*sql.Rows, error) - u
3. QueryRow(…) *sql.Row
4. MustExec() sql.Result
5. Queryx(…) (*sqlx.Rows, error)
6. QueryRowx(…) *sqlx.Row
7. Get(dest interface{}, …) error
8. Select(dest interface{}, …) error

Exec

Exec 和 MustExec从连接池获取连接，并在服务器上执行提供的sql

package main

import (
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
)



func main() {
    db, err := sqlx.Connect("mysql","root:123456@tcp(192.168.188.106:3306)/test_db")
    if err != nil {
        fmt.Println("connect mysql err",err)
        panic(err)
    }
    defer db.Close()
    fmt.Println("connnect mysql success:",db)

    // 创建表
    schema := `CREATE TABLE place (
            country text,
            city text NULL,
            telcode integer);`
    result , err := db.Exec(schema)
    if err != nil {
        fmt.Println(" create table error:",err)
        panic(err)
    }
    fmt.Println(result)
    fmt.Println("create table success")
    // 插入数据
    cityState := `INSERT INTO place (country, telcode) VALUES (?, ?)`
    countryCity := `INSERT INTO place (country, city, telcode) VALUES (?, ?, ?)`
    db.MustExec(cityState, "Hong Kong", 852)
    db.MustExec(cityState, "Singapore", 65)
    db.MustExec(countryCity, "South Africa", "Johannesburg", 27)
}

Exec 返回的是sql.Result, error

MustExec 只返回sql.Result，但是如果执行sql错误则会panic

sql.Result包含LastInsertId和RowsAffected两个方法

bindvars

在sqlx中sql语句中用的占位符用的是：？

bindvars有关的一个常见误解是将它们用于插值。 它们仅用于参数化，不允许更改SQL语句的结构。 例如，使用bindvars尝试对列或表名进行参数化将不起作用

// doesn't work
db.Query("SELECT * FROM ?", "mytable")
 
// also doesn't work
db.Query("SELECT ?, ? FROM people", "name", "location")

Query

Query是使用数据库/ sql运行查询并返回行结果的主要方式。 查询返回一个sql.Rows对象和一个error：

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
)

func main() {
    db, err := sqlx.Connect("mysql","root:123456@tcp(192.168.188.106:3306)/test_db")
    if err != nil {
        fmt.Println("connect mysql err",err)
        panic(err)
    }
    defer db.Close()
    fmt.Println("connnect mysql success:",db)

    rows, err := db.Query("SELECT country, city, telcode FROM place")
    for rows.Next() {
        var country string
        // note that city can be NULL, so we use the NullString type
        var city    sql.NullString
        var telcode int
        err = rows.Scan(&country, &city, &telcode)
        if err == nil {
            fmt.Printf("country:%v----city:%v-----telcode:%v\n",country,city,telcode)
        }
    }

}

应将rows看做是数据库的cursor而不是具体的结果列表，通过Next() 进行迭代是当查询结果比较多的时候，节省内存的好方法，并且只能scan一次

如果不遍历整个rows的结果，需要确保调用rows.Close() 将连接返回到连接池

Query 返回的错误是在准备执行或执行时可能发生的任何错误，包括了从连接池中获取连接时发生的错误。当然这里的错误通常都是由于SQL语法造成错误

Query 所使用的数据库连接将保持活动状态，知道通过Next迭代将所有row用尽，或者调用rows.Close() 释放该行

将数据库查询结果scan扫描到结构体中，需要用到rows.StructScan,代码例子：

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
)

func main() {
    db, err := sqlx.Connect("mysql","root:123456@tcp(192.168.188.106:3306)/test_db")
    if err != nil {
        fmt.Println("connect mysql err",err)
        panic(err)
    }
    defer db.Close()
    fmt.Println("connnect mysql success:",db)
    type Place struct {
        Country       string
        City          sql.NullString
        TelephoneCode int `db:"telcode"`
    }
    rows, err := db.Queryx("SELECT * FROM place")
    for rows.Next() {
        var p Place
        err = rows.StructScan(&p)
        fmt.Printf("%#v\n",p)
    }

}

这里切记，结构体字段必须大写

QueryRow

package main

import (
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
)

func main() {
    db, err := sqlx.Connect("mysql","root:123456@tcp(192.168.188.106:3306)/test_db")
    if err != nil {
        fmt.Println("connect mysql err",err)
        panic(err)
    }
    defer db.Close()
    fmt.Println("connnect mysql success")
    row := db.QueryRow("SELECT telcode FROM place WHERE telcode=?", 852)
    var telcode int
    err = row.Scan(&telcode)
    if err != nil {
        panic(err)
    }
    fmt.Println(telcode)

}

与Query不同，QueryRow返回的Row类型结果没有错误，从而可以安全地将Scan链接到返回值。 如果执行查询时出错，则Scan将返回该错误。 如果没有行，则Scan返回sql.ErrNoRows。 如果扫描本身失败（例如由于类型不匹配），则也会返回该错误。

Get And Select

Get和Select是一个非常省时的扩展。它们把query和非常灵活的scan语法结合起来。为了更加清晰的介绍它们，我们先讨论下什么是scannalbe

• a value is scannable if it is not a struct, eg string, int
• a value is scannable if it implements sql.Scanner
• a value is scannable if it is a struct with no exported fields (eg. time.Time)

Get和Select对scannable的类型使用rows.scan，对non-scannable的类型使用rows.StructScan。Get用来获取单个结果然后Scan，Select用来获取结果切片。

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
)

func main() {
    db, err := sqlx.Connect("mysql","root:123456@tcp(192.168.188.106:3306)/test_db")
    if err != nil {
        fmt.Println("connect mysql err",err)
        panic(err)
    }
    defer db.Close()
    fmt.Println("connnect mysql success")
    type Place struct {
        Country       string
        City          sql.NullString
        TelephoneCode int `db:"telcode"`
    }
    p := Place{}
    pp := []Place{}
    err = db.Get(&p, "SELECT * FROM place LIMIT 1")
    if err != nil {
        fmt.Println(err)
    } else {
        fmt.Println(p)
    }
    err = db.Select(&pp, "SELECT * FROM place WHERE telcode > ?", 50)
    if err != nil {
        fmt.Println(err)
    } else {
        fmt.Println(pp)
    }
    var id int
    err = db.Get(&id, "SELECT count(*) FROM place")
    if err != nil {
        fmt.Println(err)
    } else {
        fmt.Println(id)
    }
    var names []string
    err = db.Select(&names, "SELECT COUNTRY FROM place LIMIT 10")
    if err != nil {
        fmt.Println(err)
    } else {
        fmt.Println(names)
    }

}

Get和Select在执行完查询后就会关闭Rows，并且在执行阶段遇到任何问题都会返回错误。由于它们内部使用的StructScan，所以 下文中advanced scanning section讲的特征也适用与Get和Select。

Select可以提高编码效率，但是要注意Select和Queryx是有很大不同的，因为Select会把整个结果一次放入内存。如果查询结果没有限制特定的大小，那么最好使用Query/StructScan迭代方法。

Transactions

为了使用transactions，必须使用DB.Begin()来创建

tx, err := db.Begin()
err = tx.Exec(...)
err = tx.Commit()

由于transaction是一个connection状态，所以Tx对象必须绑定和控制单个connection。一个Tx会在整个生命周期中保存一个connection，然后在调用commit或Rollback()的时候释放掉。你在调用这几个函数的时候必须十分小心，否则connection会一直被占用直到被垃圾回收。

由于在一个transaction中只能有一个connection，所以每次只能执行一条语句。在执行另外的query操作之前，cursor对象Row*和Rows必须被Scanned或Closed。如果在数据库给你返回数据的时候你尝试向数据库发送数据，这个操作可能会中断connection。

Prepared Statements

对于大部分的数据库来说，当一个query执行的时候，在数据库内部statements其实已经准备好了。然后你可以通过sqlx.DB.Prepare()准备statements，便于后面在别的地方使用

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
)

func main() {
    db, err := sqlx.Connect("mysql","root:123456@tcp(192.168.188.106:3306)/test_db")
    if err != nil {
        fmt.Println("connect mysql err",err)
        panic(err)
    }
    defer db.Close()
    fmt.Println("connnect mysql success")

    stmt, err := db.Prepare(`SELECT * FROM place WHERE telcode=?`)
    if err != nil {
        panic(err)
    }
    row := stmt.QueryRow(65)
    var country string
    // note that city can be NULL, so we use the NullString type
    var city    sql.NullString
    var telcode int
    err = row.Scan(&country, &city, &telcode)
    if err == nil {
        fmt.Printf("country:%v----city:%v-----telcode:%v\n",country,city,telcode)
    }


}

Prepare实际上在数据库上执行preparation操作，所以它需要一个connection和它的connection state。

database/sql把这部分进行了抽象，自动在新的connection上创建statement，这样开发者就能通过stmt对象在多个connection上并发执行操作。

Preparex()返回一个sqlx.Stmt对象，包含sqlx.DB和sqlx.Tx所有的handle 扩展（方法）。

Query Helpers

**“In” Queries **

因为database / sql不会检查你的查询，而是将您的参数直接传递给驱动程序，所以使用IN子句处理查询变得很困难：

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
)

func main() {
    db, err := sqlx.Connect("mysql","root:123456@tcp(192.168.188.106:3306)/test_db")
    if err != nil {
        fmt.Println("connect mysql err",err)
        panic(err)
    }
    defer db.Close()
    fmt.Println("connnect mysql success")
    var telcodes = []int{852,65,27}
    query, args, err := sqlx.In("select * from place where telcode in (?)",telcodes)
    fmt.Println(query)
    fmt.Println(args)
    query = db.Rebind(query)
    rows,err := db.Queryx(query,args...)
    if err != nil {
        panic(err)
    }
    type Place struct {
        Country       string
        City          sql.NullString
        TelephoneCode int `db:"telcode"`
    }
    for rows.Next() {
        var p Place
        err = rows.StructScan(&p)
        if err != nil {
            fmt.Println(err)
            continue
        }
        fmt.Println(p)
    }
}

我们通过sqlx.In得到的sql语句其实就是：select * from place where telcode in (?, ?, ?) 而args得到的就是[852 65 27] ，然后就可以通过db.Queryx(query,args)进行对应的查询