jmoiron/sqlx

# sqlx [](https://dl.circleci.com/status-badge/redirect/gh/jmoiron/sqlx/tree/master) [](https://coveralls.io/github/jmoiron/sqlx?branch=master) [](https://godoc.org/github.com/jmoiron/sqlx) [](https://raw.githubusercontent.com/jmoiron/sqlx/master/LICENSE) sqlx 是一个库，它为 go 的标准 `database/sql` 库提供了一组扩展。sqlx 版本的 `sql.DB`、`sql.TX`、`sql.Stmt` 等都保持底层接口不变，因此它们的接口是标准接口的超集。这使得将使用 database/sql 的现有代码库与 sqlx 集成变得相对轻松。 主要的新增功能有： * 将行编组到结构体（支持嵌入式结构体）、map 和 slice * 支持命名参数，包括 prepared statements * 使用 `Get` 和 `Select` 快速将查询结果转换为结构体/slice 除了 [godoc API 文档](http://godoc.org/github.com/jmoiron/sqlx) 之外， 还有一些[用户文档](http://jmoiron.github.io/sqlx/) 解释了如何将 `database/sql` 与 sqlx 结合使用。 ## 近期变更 1.3.0： * `sqlx.DB.Connx(context.Context) *sqlx.Conn` * `sqlx.BindDriver(driverName, bindType)` * 支持 `[]map[string]interface{}` 以进行“批量”插入 * 改进了 `sqlx.In` 的内存分配和性能 DB.Connx 返回一个 `sqlx.Conn`，它类似于 `sql.Conn`，并与 sqlx 对其他类型的封装保持一致。 `BindDriver` 允许用户控制 sqlx 将用于各种驱动程序的 bindvars，并在运行时添加新驱动程序。这在将驱动程序解析为绑定类型时会导致极其轻微的性能损耗（每次调用约 40ns），但它允许用户指定其驱动程序使用的绑定类型，即使 sqlx 尚未默认支持它。 ### 向后兼容性 任何新更改都必须保证与最近两个版本的 Go 兼容。不保证在此之前的版本的兼容性。 版本控制通过 Go modules 完成。破坏性变更（例如，移除已弃用的 API）将会提升主版本号。 ## 安装 ``` go get github.com/jmoiron/sqlx ``` ## 问题 行标题可能会出现歧义（`SELECT 1 AS a, 2 AS a`），并且 `Columns()` 的结果在以下查询中并没有提供完全限定的列名： ``` SELECT a.id, a.name, b.id, b.name FROM foos AS a JOIN foos AS b ON a.parent = b.id; ``` 这会导致作为目标的结构体或 map 产生歧义。请在你的查询中使用 `AS` 为列指定不同的名称，手动使用 `rows.Scan` 来扫描它们，或者使用 `SliceScan` 来获取结果的 slice。 ## 用法 以下示例展示了 sqlx 的一些常见用例。查看 [sqlx_test.go](https://github.com/jmoiron/sqlx/blob/master/sqlx_test.go) 以了解更多 用法。 ``` 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 // 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) // batch insert // batch insert with structs personStructs := []Person{ {FirstName: "Ardie", LastName: "Savea", Email: "asavea@ab.co.nz"}, {FirstName: "Sonny Bill", LastName: "Williams", Email: "sbw@ab.co.nz"}, {FirstName: "Ngani", LastName: "Laumape", Email: "nlaumape@ab.co.nz"}, } _, err = db.NamedExec(`INSERT INTO person (first_name, last_name, email) VALUES (:first_name, :last_name, :email)`, personStructs) // batch insert with maps personMaps := []map[string]interface{}{ {"first_name": "Ardie", "last_name": "Savea", "email": "asavea@ab.co.nz"}, {"first_name": "Sonny Bill", "last_name": "Williams", "email": "sbw@ab.co.nz"}, {"first_name": "Ngani", "last_name": "Laumape", "email": "nlaumape@ab.co.nz"}, } _, err = db.NamedExec(`INSERT INTO person (first_name, last_name, email) VALUES (:first_name, :last_name, :email)`, personMaps) } ```

标签：EVTX分析, Go, ORM, Ruby工具, 开发库, 数据库工具, 数据库驱动, 日志审计, 测试用例