Go 語言之 SQLX 高級操作 sqlx.In
sqlx.In 介紹
sqlx is a package for Go which provides a set of extensions on top of the excellent built-in database/sql package.
Illustrated guide to SQLX：http://jmoiron.github.io/sqlx/
sqlx：https://github.com/jmoiron/sqlx
"In" Queries
Because database/sql does not inspect your query and it passes your arguments directly to the driver, it makes dealing with queries with IN clauses difficult:
SELECT * FROM users WHERE level IN (?);
When this gets prepared as a statement on the backend, the bindvar ? will only correspond to a single argument, but what is often desired is for that to be a variable number of arguments depending on the length of some slice, eg:
var levels = []int{4, 6, 7}rows, err := db.Query("SELECT * FROM users WHERE level IN (?);", levels)
This pattern is possible by first processing the query with sqlx.In:
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 backendquery = db.Rebind(query)rows, err := db.Query(query, args...)
What sqlx.In does is expand any bindvars in the query passed to it that correspond to a slice in the arguments to the length of that slice, and then append those slice elements to a new arglist. It does this with the ? bindvar only; you can use db.Rebind to get a query suitable for your backend.
普通批量插入資料，不使用 sqlx.In
package main
import (
"database/sql"
"fmt"
"strings"
"time"
_ "github.com/go-sql-driver/mysql" // 匿名匯入 自動執行 init()
)
var db *sql.DB
func initMySQL() (err error) {
//DSN (Data Source Name)
dsn := "root:12345678@tcp(127.0.0.1:3306)/sql_test"
// 注意：要初始化全域的 db 物件，不要新宣告一個 db 變數
db, err = sql.Open("mysql", dsn) // 只對格式進行校驗，并不會真正連接資料庫
if err != nil {
return err
}
// Ping 驗證與資料庫的連接是否仍處于活動狀態，并在必要時建立連接，
err = db.Ping()
if err != nil {
fmt.Printf("connect to db failed, err: %v\n", err)
return err
}
// 數值需要根據業務具體情況來確定
db.SetConnMaxLifetime(time.Second * 10) // 設定可以重用連接的最長時間
db.SetConnMaxIdleTime(time.Second * 5) // 設定連接可能處于空閑狀態的最長時間
db.SetMaxOpenConns(200) // 設定與資料庫的最大打開連接數
db.SetMaxIdleConns(10) // 設定空閑連接池中的最大連接數
return nil
}
type User struct {
Name string `db:"name"`
Age int `db:"age"`
}
// BatchInsertUsers 批量插入資料
func BatchInsertUsers(users []*User) error {
valueStrings := make([]string, 0, len(users)) // 占位符 slice
valueArgs := make([]interface{}, 0, len(users)*2) // 插入值 slice
for _, u := range users {
valueStrings = append(valueStrings, "(?, ?)")
valueArgs = append(valueArgs, u.Name, u.Age) // 占位符與插入值 一一對應
}
// 拼接完整的SQL陳述句
// Sprintf根據格式說明符進行格式化，并回傳結果字串，
// Join將其第一個引數的元素連接起來以創建單個字串，分隔字串sep放置在結果字串的元素之間，
stmt := fmt.Sprintf("INSERT INTO user (name, age) VALUES %s", strings.Join(valueStrings, ","))
// Exec執行查詢而不回傳任何行，引數用于查詢中的任何占位符引數，
result, err := db.Exec(stmt, valueArgs...)
if err != nil {
fmt.Printf("Error inserting user into database: %v \n", err)
return err
}
var rows_affected int64
rows_affected, err = result.RowsAffected() // 回傳受更新、插入或洗掉影響的行數，并非每個資料庫或資料庫驅動程式都支持此功能，
if err != nil {
fmt.Printf("回傳受更新、插入或洗掉影響的行數 failed, err: %v\n", err)
return err
}
fmt.Println("受更新、插入或洗掉影響的行數: ", rows_affected)
return nil
}
func main() {
if err := initMySQL(); err != nil {
fmt.Printf("connect to db failed, err: %v\n", err)
}
// 檢查完錯誤之后執行，確保 db 不為 nil
// Close() 用來釋放資料庫連接相關的資源
// Close 將關閉資料庫并阻止啟動新查詢，關閉，然后等待服務器上已開始處理的所有查詢完成，
defer db.Close()
fmt.Println("connect to database success")
// db.xx() 去使用資料庫操作...
// 批量插入資料
users := []*User{
{Name: "劉備", Age: 25},
{Name: "關羽", Age: 30},
{Name: "張飛", Age: 28},
}
err := BatchInsertUsers(users)
if err != nil {
fmt.Printf("Failed to batch insert users: %v", err)
}
}
運行
Code/go/mysql_demo via ?? v1.20.3 via ?? base
? go run main.go
connect to database success
受更新、插入或洗掉影響的行數: 3
Code/go/mysql_demo via ?? v1.20.3 via ?? base
?
SQL 查詢結果
mysql> select * from user; # 插入之前
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 小喬 | 12 |
| 2 | 小喬 | 22 |
| 5 | 昭君 | 18 |
| 6 | 黛玉 | 16 |
| 8 | 李煜 | 26 |
| 9 | Alice | 25 |
| 10 | Bob | 30 |
| 11 | Carol | 28 |
| 12 | Alice1 | 25 |
| 13 | Bob1 | 30 |
| 14 | Carol1 | 28 |
+----+--------+------+
11 rows in set (0.00 sec)
mysql> select * from user; # 插入之后
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 小喬 | 12 |
| 2 | 小喬 | 22 |
| 5 | 昭君 | 18 |
| 6 | 黛玉 | 16 |
| 8 | 李煜 | 26 |
| 9 | Alice | 25 |
| 10 | Bob | 30 |
| 11 | Carol | 28 |
| 12 | Alice1 | 25 |
| 13 | Bob1 | 30 |
| 14 | Carol1 | 28 |
| 15 | 劉備 | 25 |
| 16 | 關羽 | 30 |
| 17 | 張飛 | 28 |
+----+--------+------+
14 rows in set (0.01 sec)
使用 sqlx.In 批量插入
package main
import (
"database/sql/driver"
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
var db *sqlx.DB
func initDB() (err error) {
dsn := "root:12345678@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"
// 連接到資料庫并使用ping進行驗證，
// 也可以使用 MustConnect MustConnect連接到資料庫，并在出現錯誤時恐慌 panic，
db, err = sqlx.Connect("mysql", dsn)
if err != nil {
fmt.Printf("connect DB failed, err:%v\n", err)
return
}
db.SetMaxOpenConns(20) // 設定資料庫的最大打開連接數，
db.SetMaxIdleConns(10) // 設定空閑連接池中的最大連接數，
return
}
type user struct {
ID int `db:"id"`
Age int `db:"age"`
Name string `db:"name"`
}
func (u user) Value() (driver.Value, error) {
return []interface{}{u.Name, u.Age}, nil
}
// BatchInsertUsersSqlxIn 使用sqlx.In幫我們拼接陳述句和引數, 注意傳入的引數是[]interface{}
func BatchInsertUsersSqlxIn(users []interface{}) error {
// In展開args中的切片值，回傳修改后的查詢字串和一個可以由資料庫執行的新的arg串列，
// “查詢”應該使用“?”“bindVar，回傳值使用' ?“bindVar，
query, args, _ := sqlx.In(
"INSERT INTO user (name, age) VALUES (?), (?), (?)",
users..., // 如果arg實作了 driver.Valuer, sqlx.In 會通過呼叫 Value()來展開它
)
fmt.Println("query sql string: ", query) // 查看生成的querystring
fmt.Println("args: ", args) // 查看生成的args
// Exec執行查詢而不回傳任何行，引數用于查詢中的任何占位符引數，
result, err := db.Exec(query, args...)
var rows_affected int64
rows_affected, err = result.RowsAffected() // 回傳受更新、插入或洗掉影響的行數，并非每個資料庫或資料庫驅動程式都支持此功能，
if err != nil {
fmt.Printf("回傳受更新、插入或洗掉影響的行數 failed, err: %v\n", err)
return err
}
fmt.Println("受更新、插入或洗掉影響的行數: ", rows_affected)
return nil
}
func main() {
if err := initDB(); err != nil {
fmt.Printf("init DB failed, err:%v\n", err)
return
}
fmt.Println("init DB succeeded")
// 批量插入
u1 := user{Name: "李白", Age: 16}
u2 := user{Name: "杜甫", Age: 42}
u3 := user{Name: "王維", Age: 29}
users := []interface{}{u1, u2, u3}
_ = BatchInsertUsersSqlxIn(users)
}
運行
Code/go/sqlx_demo via ?? v1.20.3 via ?? base
? go run main.go
init DB succeeded
query sql string: INSERT INTO user (name, age) VALUES (?, ?), (?, ?), (?, ?)
args: [李白 16 杜甫 42 王維 29]
受更新、插入或洗掉影響的行數: 3
Code/go/sqlx_demo via ?? v1.20.3 via ?? base
SQL 查詢結果
mysql> select * from user;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 小喬 | 12 |
| 2 | 小喬 | 22 |
| 5 | 昭君 | 18 |
| 6 | 黛玉 | 16 |
| 8 | 李煜 | 26 |
| 9 | Alice | 25 |
| 10 | Bob | 30 |
| 11 | Carol | 28 |
| 12 | Alice1 | 25 |
| 13 | Bob1 | 30 |
| 14 | Carol1 | 28 |
| 15 | 劉備 | 25 |
| 16 | 關羽 | 30 |
| 17 | 張飛 | 28 |
+----+--------+------+
14 rows in set (0.01 sec)
mysql> select * from user; # 插入之后
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 小喬 | 12 |
| 2 | 小喬 | 22 |
| 5 | 昭君 | 18 |
| 6 | 黛玉 | 16 |
| 8 | 李煜 | 26 |
| 9 | Alice | 25 |
| 10 | Bob | 30 |
| 11 | Carol | 28 |
| 12 | Alice1 | 25 |
| 13 | Bob1 | 30 |
| 14 | Carol1 | 28 |
| 15 | 劉備 | 25 |
| 16 | 關羽 | 30 |
| 17 | 張飛 | 28 |
| 18 | 李白 | 16 |
| 19 | 杜甫 | 42 |
| 20 | 王維 | 29 |
+----+--------+------+
17 rows in set (0.00 sec)
mysql>
使用 NamedExec 批量插入
package main
import (
"database/sql/driver"
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
var db *sqlx.DB
func initDB() (err error) {
dsn := "root:12345678@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"
// 連接到資料庫并使用ping進行驗證，
// 也可以使用 MustConnect MustConnect連接到資料庫，并在出現錯誤時恐慌 panic，
db, err = sqlx.Connect("mysql", dsn)
if err != nil {
fmt.Printf("connect DB failed, err:%v\n", err)
return
}
db.SetMaxOpenConns(20) // 設定資料庫的最大打開連接數，
db.SetMaxIdleConns(10) // 設定空閑連接池中的最大連接數，
return
}
type user struct {
ID int `db:"id"`
Age int `db:"age"`
Name string `db:"name"`
}
func (u user) Value() (driver.Value, error) {
return []interface{}{u.Name, u.Age}, nil
}
// BatchInsertUsersNamedExec NamedExec 批量插入
func BatchInsertUsersNamedExec(users []*user) error {
// 任何命名的占位符引數都將被arg中的欄位替換，
result, err := db.NamedExec("INSERT INTO user (name, age) VALUES (:name, :age)", users)
var rows_affected int64
rows_affected, err = result.RowsAffected() // 回傳受更新、插入或洗掉影響的行數，并非每個資料庫或資料庫驅動程式都支持此功能，
if err != nil {
fmt.Printf("回傳受更新、插入或洗掉影響的行數 failed, err: %v\n", err)
return err
}
fmt.Println("BatchInsertUsersNamedExec 受插入影響的行數: ", rows_affected)
return nil
}
func main() {
if err := initDB(); err != nil {
fmt.Printf("init DB failed, err:%v\n", err)
return
}
fmt.Println("init DB succeeded")
// 批量插入
u1 := user{Name: "褒姒", Age: 16}
u2 := user{Name: "貂蟬", Age: 42}
u3 := user{Name: "飛燕", Age: 29}
// NamedExec
users := []*user{&u1, &u2, &u3}
_ = BatchInsertUsersNamedExec(users)
}
運行
Code/go/sqlx_demo via ?? v1.20.3 via ?? base
? go run main.go
init DB succeeded
BatchInsertUsersNamedExec 受插入影響的行數: 3
Code/go/sqlx_demo via ?? v1.20.3 via ?? base
?
SQL 查詢結果
mysql> select * from user;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 小喬 | 12 |
| 2 | 小喬 | 22 |
| 5 | 昭君 | 18 |
| 6 | 黛玉 | 16 |
| 8 | 李煜 | 26 |
| 9 | Alice | 25 |
| 10 | Bob | 30 |
| 11 | Carol | 28 |
| 12 | Alice1 | 25 |
| 13 | Bob1 | 30 |
| 14 | Carol1 | 28 |
| 15 | 劉備 | 25 |
| 16 | 關羽 | 30 |
| 17 | 張飛 | 28 |
| 18 | 李白 | 16 |
| 19 | 杜甫 | 42 |
| 20 | 王維 | 29 |
+----+--------+------+
17 rows in set (0.00 sec)
mysql> select * from user; # 插入之后
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 小喬 | 12 |
| 2 | 小喬 | 22 |
| 5 | 昭君 | 18 |
| 6 | 黛玉 | 16 |
| 8 | 李煜 | 26 |
| 9 | Alice | 25 |
| 10 | Bob | 30 |
| 11 | Carol | 28 |
| 12 | Alice1 | 25 |
| 13 | Bob1 | 30 |
| 14 | Carol1 | 28 |
| 15 | 劉備 | 25 |
| 16 | 關羽 | 30 |
| 17 | 張飛 | 28 |
| 18 | 李白 | 16 |
| 19 | 杜甫 | 42 |
| 20 | 王維 | 29 |
| 21 | 褒姒 | 16 |
| 22 | 貂蟬 | 42 |
| 23 | 飛燕 | 29 |
+----+--------+------+
20 rows in set (0.00 sec)
mysql>
sqlx 高級操作之 IN 查詢
查詢 ID 在指定集合中的資料
package main
import (
"database/sql/driver"
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
var db *sqlx.DB
func initDB() (err error) {
dsn := "root:12345678@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"
// 連接到資料庫并使用ping進行驗證，
// 也可以使用 MustConnect MustConnect連接到資料庫，并在出現錯誤時恐慌 panic，
db, err = sqlx.Connect("mysql", dsn)
if err != nil {
fmt.Printf("connect DB failed, err:%v\n", err)
return
}
db.SetMaxOpenConns(20) // 設定資料庫的最大打開連接數，
db.SetMaxIdleConns(10) // 設定空閑連接池中的最大連接數，
return
}
type user struct {
ID int `db:"id"`
Age int `db:"age"`
Name string `db:"name"`
}
// QueryByIDs 查詢 ID 在指定集合中的資料
func QueryByIDs(ids []int) (users []user, err error) {
// In 展開args中的切片值，回傳修改后的查詢字串和一個可以由資料庫執行的新的arg串列，
// “查詢”應該使用“?”“bindVar，回傳值使用' ?“bindVar
query, args, err := sqlx.In("SELECT name, age FROM user WHERE id IN (?)", ids)
if err != nil {
return nil, err
}
// Rebind 將查詢從 QUESTION 轉換為DB驅動程式的 bindvar 型別，
query = db.Rebind(query)
// Select 使用此資料庫，任何占位符引數都將被提供的引數替換，
err = db.Select(&users, query, args...)
if err != nil {
return nil, err
}
return users, nil
}
func main() {
if err := initDB(); err != nil {
fmt.Printf("init DB failed, err:%v\n", err)
return
}
fmt.Println("init DB succeeded")
// IN 查詢
users, err := QueryByIDs([]int{1, 15, 21, 2}) // 默認按照主鍵順序排列
if err != nil {
fmt.Printf("query error: %v\n", err)
return
}
fmt.Printf("query successful result users %v\n", users)
for _, user := range users {
fmt.Printf("user: %#v\n", user)
}
}
運行
Code/go/sqlx_demo via ?? v1.20.3 via ?? base
? go run main.go
init DB succeeded
query successful result users [{0 12 小喬} {0 22 小喬} {0 25 劉備} {0 16 褒姒}]
user: main.user{ID:0, Age:12, Name:"小喬"}
user: main.user{ID:0, Age:22, Name:"小喬"}
user: main.user{ID:0, Age:25, Name:"劉備"}
user: main.user{ID:0, Age:16, Name:"褒姒"}
Code/go/sqlx_demo via ?? v1.20.3 via ?? base
?
SQL 查詢結果
mysql> select * from user;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 小喬 | 12 |
| 2 | 小喬 | 22 |
| 5 | 昭君 | 18 |
| 6 | 黛玉 | 16 |
| 8 | 李煜 | 26 |
| 9 | Alice | 25 |
| 10 | Bob | 30 |
| 11 | Carol | 28 |
| 12 | Alice1 | 25 |
| 13 | Bob1 | 30 |
| 14 | Carol1 | 28 |
| 15 | 劉備 | 25 |
| 16 | 關羽 | 30 |
| 17 | 張飛 | 28 |
| 18 | 李白 | 16 |
| 19 | 杜甫 | 42 |
| 20 | 王維 | 29 |
| 21 | 褒姒 | 16 |
| 22 | 貂蟬 | 42 |
| 23 | 飛燕 | 29 |
+----+--------+------+
20 rows in set (0.00 sec)
mysql>
- 查詢結果默認按照主鍵順序排列
- 自定義查詢結果順序
- 使用代碼排序
- 使用 MySQL 排序 FIND_IN_SET
sqlx 高級操作之 FIND_IN_SET
package main
import (
"database/sql/driver"
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
"strings"
)
var db *sqlx.DB
func initDB() (err error) {
dsn := "root:12345678@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"
// 連接到資料庫并使用ping進行驗證，
// 也可以使用 MustConnect MustConnect連接到資料庫，并在出現錯誤時恐慌 panic，
db, err = sqlx.Connect("mysql", dsn)
if err != nil {
fmt.Printf("connect DB failed, err:%v\n", err)
return
}
db.SetMaxOpenConns(20) // 設定資料庫的最大打開連接數，
db.SetMaxIdleConns(10) // 設定空閑連接池中的最大連接數，
return
}
type user struct {
ID int `db:"id"`
Age int `db:"age"`
Name string `db:"name"`
}
// QueryByIDs 查詢 ID 在指定集合中的資料
func QueryByIDs(ids []int) (users []user, err error) {
// In 展開args中的切片值，回傳修改后的查詢字串和一個可以由資料庫執行的新的arg串列，
// “查詢”應該使用“?”“bindVar，回傳值使用' ?“bindVar
query, args, err := sqlx.In("SELECT name, age FROM user WHERE id IN (?)", ids)
if err != nil {
return nil, err
}
// Rebind 將查詢從 QUESTION 轉換為DB驅動程式的 bindvar 型別，
query = db.Rebind(query)
// Select 使用此資料庫，任何占位符引數都將被提供的引數替換，
err = db.Select(&users, query, args...)
if err != nil {
return nil, err
}
return users, nil
}
// QueryAndOrderByIDs 根據 ID 在指定集合中和指定順序查詢
func QueryAndOrderByIDs(ids []int) (users []user, err error) {
// 創建一個字串切片，大小為ids的長度
strIDs := make([]string, 0, len(ids))
// 將ids轉換為字串型別
for _, id := range ids {
// Sprintf根據格式說明符進行格式化，并回傳結果字串，
strIDs = append(strIDs, fmt.Sprintf("%d", id))
}
// In展開args中的切片值，回傳修改后的查詢字串和一個可以由資料庫執行的新的arg串列，“查詢”應該使用“?”“bindVar，回傳值使用' ?“bindVar，
query, args, err := sqlx.In("SELECT name, age FROM user WHERE id IN (?) ORDER BY FIND_IN_SET(id, ?)", ids, strings.Join(strIDs, ","))
if err != nil {
return
}
// Rebind 將查詢從QUESTION轉換為DB驅動程式的bindvar型別，
query = db.Rebind(query)
// 執行查詢 Select 使用此資料庫，任何占位符引數都將被提供的引數替換，
err = db.Select(&users, query, args...)
return
}
func main() {
if err := initDB(); err != nil {
fmt.Printf("init DB failed, err:%v\n", err)
return
}
fmt.Println("init DB succeeded")
// IN 查詢
users, err := QueryByIDs([]int{1, 15, 21, 2})
if err != nil {
fmt.Printf("query error: %v\n", err)
return
}
fmt.Printf("query successful result users %v\n", users)
for _, user := range users {
fmt.Printf("user: %#v\n", user)
}
fmt.Println("**************")
// FIND_IN_SET
users, err = QueryAndOrderByIDs([]int{1, 15, 21, 2})
if err != nil {
fmt.Printf("query error: %v\n", err)
return
}
fmt.Printf("query successful result users %v\n", users)
for _, user := range users {
fmt.Printf("user: %#v\n", user)
}
}
運行
Code/go/sqlx_demo via ?? v1.20.3 via ?? base
? go run main.go
init DB succeeded
query successful result users [{0 12 小喬} {0 22 小喬} {0 25 劉備} {0 16 褒姒}]
user: main.user{ID:0, Age:12, Name:"小喬"}
user: main.user{ID:0, Age:22, Name:"小喬"}
user: main.user{ID:0, Age:25, Name:"劉備"}
user: main.user{ID:0, Age:16, Name:"褒姒"}
**************
query successful result users [{0 12 小喬} {0 25 劉備} {0 16 褒姒} {0 22 小喬}]
user: main.user{ID:0, Age:12, Name:"小喬"} # FIND_IN_SET 按照指定順序查詢
user: main.user{ID:0, Age:25, Name:"劉備"}
user: main.user{ID:0, Age:16, Name:"褒姒"}
user: main.user{ID:0, Age:22, Name:"小喬"}
Code/go/sqlx_demo via ?? v1.20.3 via ?? base
?
注意：開發中，使用代碼排序還是使用 SQL FIND_IN_SET 查詢排序，需要根據開發實際情況來使用，
官方示例
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", "[email protected]")
tx.MustExec("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "John", "Doe", "[email protected]")
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", "[email protected]"})
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:"[email protected]"}
// Person{FirstName:"John", LastName:"Doe", Email:"[email protected]"}
// 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:"[email protected]"}
// 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": "[email protected]",
})
// 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: "[email protected]"},
{FirstName: "Sonny Bill", LastName: "Williams", Email: "[email protected]"},
{FirstName: "Ngani", LastName: "Laumape", Email: "[email protected]"},
}
_, 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": "[email protected]"},
{"first_name": "Sonny Bill", "last_name": "Williams", "email": "[email protected]"},
{"first_name": "Ngani", "last_name": "Laumape", "email": "[email protected]"},
}
_, err = db.NamedExec(`INSERT INTO person (first_name, last_name, email)
VALUES (:first_name, :last_name, :email)`, personMaps)
}
更多示例請參考官方檔案：https://github.com/jmoiron/sqlx/blob/master/sqlx_test.go
本文來自博客園，作者：尋月隱君，轉載請注明原文鏈接：https://www.cnblogs.com/QiaoPengjun/p/17481004.html
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/555181.html
標籤：其他
上一篇：Scala簡介和安裝
下一篇：返回列表