type User struct { Id int64`db:"id"` Age int64`db:"age"` FirstName string`db:"firstName"` LastName string`db:"lastName"` }
funcQueryRowById(id int64) (user User, err error) { err = DB.Get(&user, "select * from user where id = ?", id) if err != nil { return } return }
查询多行记录
funcSelectMData() ([]*User, error) { var list []*User err := DB.Select(&list, "select * from user order by id desc") if err != nil { return list, err } return list, err }
sqlx的Exec执行sql
Exec and MustExec get a connection from the connection pool and executes the provided query on the server. For drivers that do not support ad-hoc query execution, a prepared statement may be created behind the scenes to be executed. The connection is returned to the pool before the result is returned.
funcExecSQL() { schema := `CREATE TABLE place ( country text, city text NULL, telcode integer);` result, err := DB.Exec(schema) if err != nil { return } fmt.Println(result) // or, you can use MustExec, which panics on error 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) }
sqlx绑定数据
MySQL uses the ? variant shown above
PostgreSQL uses an enumerated $1, $2, etc bindvar syntax
SQLite accepts both ? and $1 syntax
Oracle uses a :name syntax
sqlx查询–Query
Query is the primary way to run queries with database/sql that return row results. Query returns an sql.Rows object and an error:
Query的使用
funcQueryDemo() { // 查询数据库 rows, err := DB.Query("select * from user") if err != nil { return } // 迭代器取数据 for rows.Next() { var id int64 var firstNam string var lastname string var age int64 rows.Scan(&id, &firstNam, &lastname, &age) fmt.Printf("id:%d, firstname:%s, lastname:%s, age:%d\n", id, firstNam, lastname, age) } }
Queryx使用
The sqlx extension Queryx behaves exactly as Query does, but returns an sqlx.Rows, which has extended scanning behaviors:
funcQueryxDemo() { rows, err := DB.Queryx("select * from user") if err != nil { return } for rows.Next() { var u User err := rows.StructScan(&u) if err != nil { return } fmt.Printf("id:%d, firstname:%s, lastname:%s, age:%d\n", u.Id, u.FirstName, u.LastName, u.Age) } }
sqlx的Transactions
To use transactions, you must create a transaction handle with DB.Begin(). Code like this will not work:
// this will not work if connection pool > 1 db.MustExec("BEGIN;") db.MustExec(...) db.MustExec("COMMIT;")
stmt, err := db.Prepare(`SELECT * FROM place WHERE telcode=?`) row = stmt.QueryRow(65) tx, err := db.Begin() txStmt, err := tx.Prepare(`SELECT * FROM place WHERE telcode=?`) row = txStmt.QueryRow(852)
// Preparex stmt, err := db.Preparex(`SELECT * FROM place WHERE telcode=?`) var p Place err = stmt.Get(&p, 852)
sqlx–Query Helpers
In Queries
SELECT * FROM users WHERE level IN (?);
var levels = []int{4, 6, 7} rows, err := db.Query("SELECT * FROM users WHERE level IN (?);", levels)
var levels = []int{4, 6, 7} query, args, err := sqlx.In("SELECT * FROM users WHERE level IN (?);", levels) // sqlx.In returns queries with the `?` bindvar, we can rebind it for our backend query = db.Rebind(query) rows, err := db.Query(query, args...)
Named Queries
// named query with a struct p := Place{Country: "South Africa"} rows, err := db.NamedQuery(`SELECT * FROM place WHERE country=:country`, p) // named query with a map m := map[string]interface{}{"city": "Johannesburg"} result, err := db.NamedExec(`SELECT * FROM place WHERE city=:city`, m)
p := Place{TelephoneCode: 50} pp := []Place{} // select all telcodes > 50 nstmt, err := db.PrepareNamed(`SELECT * FROM place WHERE telcode > :telcode`) err = nstmt.Select(&pp, p)
arg := map[string]interface{}{ "published": true, "authors": []{8, 19, 32, 44}, } query, args, err := sqlx.Named("SELECT * FROM articles WHERE published=:published AND author_id IN (:authors)", arg) query, args, err := sqlx.In(query, args...) query = db.Rebind(query) db.Query(query, args...)
// if our db schema uses ALLCAPS columns, we can use normal fields db.MapperFunc(strings.ToUpper) // suppose a library uses lowercase columns, we can create a copy copy := sqlx.NewDb(db.DB, db.DriverName()) copy.MapperFunc(strings.ToLower)
每个sqlx. db使用sqlx/reflectx包的映射器来实现底层映射，并将活动映射器公开为sqlx. db .Mapper。您可以通过直接设置来进一步定制DB上的映射:
import"github.com/jmoiron/sqlx/reflectx" // Create a new mapper which will use the struct field tag "json" instead of "db" db.Mapper = reflectx.NewMapperFunc("json", strings.ToLower)
sqlx– Connection Pool
Statement preparation and query execution require a connection, and the DB object will manage a pool of them so that it can be safely used for concurrent querying. There are two ways to control the size of the connection pool as of Go 1.2:
funcmain() { fmt.Println("hello squirrel") _ = initMysql() sql, args, err := sq.Select("*"). From("user"). Where(sq.Eq{"id": 2}).ToSql() fmt.Printf("sql: %s, args: %v, err: %v\n", sql, args[0], err) // select * from user where id = 2 var id int64 var firstNam string var lastname string var age int64
