Detailed explanation of advanced operations of SQLX library in Golang

SELECT * FROM users WHERE level IN (?);

var levels = []int{4, 6, 7}rows, err := ("SELECT * FROM users WHERE level IN (?);", levels)

var levels = []int{4, 6, 7}query, args, err := ("SELECT * FROM users WHERE level IN (?);", levels) //  returns queries with the `?` bindvar, we can rebind it for our backendquery = (query)rows, err := (query, args...)

package main
​
import (
  "database/sql"
  "fmt"
  "strings"
  "time"
​
  _ "/go-sql-driver/mysql" // Anonymous import automatically execute init())
​
var db *
​
func initMySQL() (err error) {
  //DSN (Data Source Name)
  dsn := "root:12345678@tcp(127.0.0.1:3306)/sql_test"
  // Note: To initialize the global db object, do not declare a new db variable  db, err = ("mysql", dsn) // Only check the format and will not actually connect to the database  if err != nil {
    return err
  }
​
  // Ping Verify that the connection to the database is still active and establish the connection if necessary.  err = ()
  if err != nil {
    ("connect to db failed, err: %v\n", err)
    return err
  }
  // The value needs to be determined based on the specific business situation  ( * 10) // Set the maximum time to reuse the connection  ( * 5)  // Set the maximum time when the connection may be idle  (200)                 // Set the maximum number of open connections to the database  (10)                  // Set the maximum number of connections in the idle connection pool  return nil
}
​
type User struct {
  Name string `db:"name"`
  Age  int    `db:"age"`
}
​
// BatchInsertUsers Batch InsertUsersfunc BatchInsertUsers(users []*User) error {
  valueStrings := make([]string, 0, len(users))     // Placeholder slice  valueArgs := make([]interface{}, 0, len(users)*2) // Insert value slice​
  for _, u := range users {
    valueStrings = append(valueStrings, "(?, ?)")
    valueArgs = append(valueArgs, , ) // Placeholder and insert value correspond one by one  }
  // Splice complete SQL statements  // Sprintf is formatted according to the format specifier and returns the result string.  // Join joins elements of its first parameter to create a single string.  The delimited string sep is placed between elements of the result string.  stmt := ("INSERT INTO user (name, age) VALUES %s", (valueStrings, ","))
  // Exec executes the query without returning any rows.  Parameters are used for any placeholder parameters in the query.  result, err := (stmt, valueArgs...)
  if err != nil {
    ("Error inserting user into database: %v \n", err)
    return err
  }
  var rows_affected int64
  rows_affected, err = () // Returns the number of rows affected by update, insertion, or deletion.  Not every database or database driver supports this feature.  if err != nil {
    ("Return the number of rows affected by update, insertion, or deletion failed, err: %v\n", err)
    return err
  }
  ("Number of rows affected by update, insertion, or deletion: ", rows_affected)
  return nil
}
​
func main() {
  if err := initMySQL(); err != nil {
    ("connect to db failed, err: %v\n", err)
  }
  // Execute after checking the error, make sure db is not nil  // Close() is used to release resources related to database connections  // Close will close the database and prevent new queries from being started.  Close, and wait for all queries that have started processing on the server to complete.  defer ()
​
  ("connect to database success")
  // () Go to use database operations...​
  // Batch insertion of data  users := []*User{
    {Name: "Liu Bei", Age: 25},
    {Name: "Guan Yu", Age: 30},
    {Name: "Zhang Fei", Age: 28},
  }
  err := BatchInsertUsers(users)
  if err != nil {
    ("Failed to batch insert users: %v", err)
  }
}
​

package main
​
import (
  "database/sql/driver"
  "fmt"
  _ "/go-sql-driver/mysql"
  "/jmoiron/sqlx"
)
​
var db *
​
func initDB() (err error) {
  dsn := "root:12345678@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"
  // Connect to the database and use ping to verify.  // You can also use MustConnect to connect to the database and panic when an error occurs.  db, err = ("mysql", dsn)
  if err != nil {
    ("connect DB failed, err:%v\n", err)
    return
  }
  (20) // Set the maximum number of open connections for the database.  (10) // Set the maximum number of connections in the idle connection pool.  return
}
​
type user struct {
  ID   int    `db:"id"`
  Age  int    `db:"age"`
  Name string `db:"name"`
}
​
func (u user) Value() (, error) {
  return []interface{}{, }, nil
}
​
// BatchInsertUsersSqlxIn uses the statement and parameters to help us splice statements and parameters, note that the passed parameter is []interface{}func BatchInsertUsersSqlxIn(users []interface{}) error {
  // In expand the slice value in args, return the modified query string and a new arg list that can be executed by the database.  // "Query" should use "?" "bindVar. Return value uses '?"bindVar.  query, args, _ := (
    "INSERT INTO user (name, age) VALUES (?), (?), (?)",
    users..., // If arg is implemented, it will be expanded by calling Value()  )
  ("query sql string: ", query) // View the generated querystring  ("args: ", args)              // View generated args  // Exec executes the query without returning any rows.  Parameters are used for any placeholder parameters in the query.  result, err := (query, args...)
  var rows_affected int64
  rows_affected, err = () // Returns the number of rows affected by update, insertion, or deletion.  Not every database or database driver supports this feature.  if err != nil {
    ("Return the number of rows affected by update, insertion, or deletion failed, err: %v\n", err)
    return err
  }
  ("Number of rows affected by update, insertion, or deletion: ", rows_affected)
  return nil
}
​
​
func main() {
  if err := initDB(); err != nil {
    ("init DB failed, err:%v\n", err)
    return
  }
  ("init DB succeeded")
  // Batch insertion  u1 := user{Name: "Li Bai", Age: 16}
  u2 := user{Name: "Du Fu", Age: 42}
  u3 := user{Name: "Wang Wei", Age: 29}
  users := []interface{}{u1, u2, u3}
  _ = BatchInsertUsersSqlxIn(users)
}
​

package main
​
import (
  "database/sql/driver"
  "fmt"
  _ "/go-sql-driver/mysql"
  "/jmoiron/sqlx"
)
​
var db *
​
func initDB() (err error) {
  dsn := "root:12345678@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"
  // Connect to the database and use ping to verify.  // You can also use MustConnect to connect to the database and panic when an error occurs.  db, err = ("mysql", dsn)
  if err != nil {
    ("connect DB failed, err:%v\n", err)
    return
  }
  (20) // Set the maximum number of open connections for the database.  (10) // Set the maximum number of connections in the idle connection pool.  return
}
​
type user struct {
  ID   int    `db:"id"`
  Age  int    `db:"age"`
  Name string `db:"name"`
}
​
func (u user) Value() (, error) {
  return []interface{}{, }, nil
}
​
​
// BatchInsertUsersNamedExec NamedExec Batch Insertfunc BatchInsertUsersNamedExec(users []*user) error {
  // Any named placeholder parameter will be replaced by fields in arg.  result, err := ("INSERT INTO user (name, age) VALUES (:name, :age)", users)
  var rows_affected int64
  rows_affected, err = () // Returns the number of rows affected by update, insertion, or deletion.  Not every database or database driver supports this feature.  if err != nil {
    ("Return the number of rows affected by update, insertion, or deletion failed, err: %v\n", err)
    return err
  }
  ("BatchInsertUsersNamedExec Number of rows affected by insertion: ", rows_affected)
  return nil
}
​
func main() {
  if err := initDB(); err != nil {
    ("init DB failed, err:%v\n", err)
    return
  }
  ("init DB succeeded")
  // Batch insertion  u1 := user{Name: "Baosi", Age: 16}
  u2 := user{Name: "Diao Chan", Age: 42}
  u3 := user{Name: "Flying Swallow", Age: 29}
  // NamedExec
  users := []*user{&u1, &u2, &u3}
  _ = BatchInsertUsersNamedExec(users)
}
​

package main
​
import (
  "database/sql/driver"
  "fmt"
  _ "/go-sql-driver/mysql"
  "/jmoiron/sqlx"
)
​
var db *
​
func initDB() (err error) {
  dsn := "root:12345678@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"
  // Connect to the database and use ping to verify.  // You can also use MustConnect to connect to the database and panic when an error occurs.  db, err = ("mysql", dsn)
  if err != nil {
    ("connect DB failed, err:%v\n", err)
    return
  }
  (20) // Set the maximum number of open connections for the database.  (10) // Set the maximum number of connections in the idle connection pool.  return
}
​
type user struct {
  ID   int    `db:"id"`
  Age  int    `db:"age"`
  Name string `db:"name"`
}
​
// QueryByIDs Query ID Data in the specified collectionfunc QueryByIDs(ids []int) (users []user, err error) {
  // In Expand the slice value in args, return the modified query string and a new arg list that can be executed by the database.  // "Query" should use "?" "bindVar. Return value uses '?"bindVar  query, args, err := ("SELECT name, age FROM user WHERE id IN (?)", ids)
  if err != nil {
    return nil, err
  }
  // Rebind converts query from QUESTION to bindvar type of DB driver.  query = (query)
  // Select Use this database.  Any placeholder parameter will be replaced by the provided parameter.  err = (&users, query, args...)
  if err != nil {
    return nil, err
  }
  return users, nil
}
​
func main() {
  if err := initDB(); err != nil {
    ("init DB failed, err:%v\n", err)
    return
  }
  ("init DB succeeded")
  // IN query  users, err := QueryByIDs([]int{1, 15, 21, 2})  // By default, sorted in primary key order  if err != nil {
    ("query error: %v\n", err)
    return
  }
  ("query successful result users %v\n", users)
  for _, user := range users {
    ("user: %#v\n", user)
  }
}
​

package main
​
import (
  "database/sql/driver"
  "fmt"
  _ "/go-sql-driver/mysql"
  "/jmoiron/sqlx"
  "strings"
)
​
var db *
​
func initDB() (err error) {
  dsn := "root:12345678@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"
  // Connect to the database and use ping to verify.  // You can also use MustConnect to connect to the database and panic when an error occurs.  db, err = ("mysql", dsn)
  if err != nil {
    ("connect DB failed, err:%v\n", err)
    return
  }
  (20) // Set the maximum number of open connections for the database.  (10) // Set the maximum number of connections in the idle connection pool.  return
}
​
type user struct {
  ID   int    `db:"id"`
  Age  int    `db:"age"`
  Name string `db:"name"`
}
​
// QueryByIDs Query ID Data in the specified collectionfunc QueryByIDs(ids []int) (users []user, err error) {
  // In Expand the slice value in args, return the modified query string and a new arg list that can be executed by the database.  // "Query" should use "?" "bindVar. Return value uses '?"bindVar  query, args, err := ("SELECT name, age FROM user WHERE id IN (?)", ids)
  if err != nil {
    return nil, err
  }
  // Rebind converts query from QUESTION to bindvar type of DB driver.  query = (query)
  // Select Use this database.  Any placeholder parameter will be replaced by the provided parameter.  err = (&users, query, args...)
  if err != nil {
    return nil, err
  }
  return users, nil
}
​
// QueryAndOrderByIDs Query in the specified collection and in the specified order according to the IDfunc QueryAndOrderByIDs(ids []int) (users []user, err error) {
  // Create a string slice with the size of ids  strIDs := make([]string, 0, len(ids))
  // Convert ids to string type  for _, id := range ids {
    // Sprintf is formatted according to the format specifier and returns the result string.    strIDs = append(strIDs, ("%d", id))
  }
  // In expand the slice value in args, return the modified query string and a new arg list that can be executed by the database.  "Query" should use "?" "bindVar. Return value uses '?"bindVar.  query, args, err := ("SELECT name, age FROM user WHERE id IN (?) ORDER BY FIND_IN_SET(id, ?)", ids, (strIDs, ","))
  if err != nil {
    return
  }
​
  // Rebind converts query from QUESTION to bindvar type of DB driver.  query = (query)
  // Execute query Select Use this database.  Any placeholder parameter will be replaced by the provided parameter.  err = (&users, query, args...)
  return
}
​
func main() {
  if err := initDB(); err != nil {
    ("init DB failed, err:%v\n", err)
    return
  }
  ("init DB succeeded")
  // IN query  users, err := QueryByIDs([]int{1, 15, 21, 2})
  if err != nil {
    ("query error: %v\n", err)
    return
  }
  ("query successful result users %v\n", users)
  for _, user := range users {
    ("user: %#v\n", user)
  }
​
  ("**************")
  // FIND_IN_SET
  users, err = QueryAndOrderByIDs([]int{1, 15, 21, 2})
  if err != nil {
    ("query error: %v\n", err)
    return
  }
  ("query successful result users %v\n", users)
  for _, user := range users {
    ("user: %#v\n", user)
  }
}
​

package main
​
import (
    "database/sql"
    "fmt"
    "log"
    _ "/lib/pq"
    "/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    
    TelCode int
}
​
func main() {
    // this Pings the database trying to connect
    // use () for () semantics
    db, err := ("postgres", "user=foo dbname=bar sslmode=disable")
    if err != nil {
        (err)
    }
​
    // exec the schema or fail; multi-statement Exec behavior varies between
    // database drivers;  pq will exec them all, sqlite3 won't, ymmv
    (schema)
    tx := ()
    ("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "Jason", "Moiron", "jmoiron@")
    ("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "John", "Doe", "johndoeDNE@")
    ("INSERT INTO place (country, city, telcode) VALUES ($1, $2, $3)", "United States", "New York", "1")
    ("INSERT INTO place (country, telcode) VALUES ($1, $2)", "*", "852")
    ("INSERT INTO place (country, telcode) VALUES ($1, $2)", "Singapore", "65")
    // Named queries can use structs, so if you have an existing struct (. person := &Person{}) that you have populated, you can pass it in as &person
    ("INSERT INTO person (first_name, last_name, email) VALUES (:first_name, :last_name, :email)", &Person{"Jane", "Citizen", "@"})
    ()
​
    // Query the database, storing results in a []Person (wrapped in []interface{})
    people := []Person{}
    (&people, "SELECT * FROM person ORDER BY first_name ASC")
    jason, john := people[0], people[1]
​
    ("%#v\n%#v", jason, john)
    // Person{FirstName:"Jason", LastName:"Moiron", Email:"jmoiron@"}
    // Person{FirstName:"John", LastName:"Doe", Email:"johndoeDNE@"}
​
    // You can also get a single result, a la QueryRow
    jason = Person{}
    err = (&jason, "SELECT * FROM person WHERE first_name=$1", "Jason")
    ("%#v\n", jason)
    // Person{FirstName:"Jason", LastName:"Moiron", Email:"jmoiron@"}
​
    // if you have null fields and use SELECT *, you must use * in your struct
    places := []Place{}
    err = (&places, "SELECT * FROM place ORDER BY telcode ASC")
    if err != nil {
        (err)
        return
    }
    usa, singsing, honkers := places[0], places[1], places[2]
    ("%#v\n%#v\n%#v\n", usa, singsing, honkers)
    // Place{Country:"United States", City:{String:"New York", Valid:true}, TelCode:1}
    // Place{Country:"Singapore", City:{String:"", Valid:false}, TelCode:65}
    // Place{Country:"*", City:{String:"", Valid:false}, TelCode:852}
​
    // Loop through rows using only one struct
    place := Place{}
    rows, err := ("SELECT * FROM place")
    for () {
        err := (&place)
        if err != nil {
            (err)
        } 
        ("%#v\n", place)
    }
    // Place{Country:"United States", City:{String:"New York", Valid:true}, TelCode:1}
    // Place{Country:"*", City:{String:"", Valid:false}, TelCode:852}
    // Place{Country:"Singapore", City:{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 /Connect
    _, err = (`INSERT INTO person (first_name,last_name,email) VALUES (:first,:last,:email)`, 
        map[string]interface{}{
            "first": "Bin",
            "last": "Smuth",
            "email": "bensmith@",
    })
​
    // Selects Mr. Smith from the database
    rows, err = (`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 = (`SELECT * FROM person WHERE first_name=:first_name`, jason)
    // batch insert
    // batch insert with structs
    personStructs := []Person{
        {FirstName: "Ardie", LastName: "Savea", Email: "asavea@"},
        {FirstName: "Sonny Bill", LastName: "Williams", Email: "sbw@"},
        {FirstName: "Ngani", LastName: "Laumape", Email: "nlaumape@"},
    }
​
    _, err = (`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@"},
        {"first_name": "Sonny Bill", "last_name": "Williams", "email": "sbw@"},
        {"first_name": "Ngani", "last_name": "Laumape", "email": "nlaumape@"},
    }
​
    _, err = (`INSERT INTO person (first_name, last_name, email)
        VALUES (:first_name, :last_name, :email)`, personMaps)
}