Shulou(Shulou.com)05/31 Report--

Most people do not understand the knowledge points of this article "golang how to connect the sqlx library", so the editor summarizes the following contents, detailed contents, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "golang how to connect the sqlx library" article.

Guidelines for using the sqlx Library

In a project, we may usually use database/sql to connect to the MySQL database. With the help of the example of using sqlx to insert data in bulk, this article introduces the sqlx.In and DB.NamedExec methods that you may have ignored in sqlx.

Sqlx introduction

In a project, we may usually use database/sql to connect to the MySQL database. Sqlx can be thought of as a superset of the Go language's built-in database/sql, which provides a set of extensions based on the excellent built-in database/sql. In addition to Get (dest interface {},...), which is often used to query, these extensions Error and Select (dest interface {},...) There are many other powerful features besides error.

To install sqlxgo get github.com/jmoiron/sqlx, basically use the connection database var db * sqlx.DBfunc initDB () (err error) {dsn: = "user:password@tcp (127.0.0.1 sqlx.DBfunc initDB 3306) / sql_test?charset=utf8mb4&parseTime=True" / / you can also use MustConnect if the connection is not successful, 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} query

The sample code for querying single-row data is as follows:

/ / query single data example func queryRowDemo () {sqlStr: = "select id, name, age from user where id=?" Var u user err: = db.Get (& u, sqlStr, 1) if err! = nil {fmt.Printf ("get failed, err:%v\ n", err) return} fmt.Printf ("id:%d name:%s age:%d\ n", u.ID, u.Name, u.Age)}

The sample code for querying multiple rows of data is as follows:

/ / query multiple data examples func queryMultiRowDemo () {sqlStr: = "select id, name, age from user where id >?" Var users [] user err: = db.Select (& users, sqlStr, 0) if err! = nil {fmt.Printf ("query failed, err:%v\ n", err) return} fmt.Printf ("users:%#v\ n", users)} insert, update and delete

The exec method in sqlx is basically the same as the exec usage in native sql:

/ / insert func insertRowDemo () {sqlStr: = "insert into user (name, age) values (?,?)" Ret, err: = db.Exec (sqlStr, "Little Prince of Shahe", 19) if err! = nil {fmt.Printf ("insert failed, err:%v\ n", err) return} theID, err: = ret.LastInsertId () / / id if err of newly inserted data! = nil {fmt.Printf ("get lastinsert ID failed, err:%v\ n") Err) return} fmt.Printf ("insert success, the id is% d.\ n", theID)} / / Update data func updateRowDemo () {sqlStr: = "update user set age=? Where id =? " Ret, err: = db.Exec (sqlStr, 39,6) if err! = nil {fmt.Printf ("update failed, err:%v\ n", err) return} n, err: = ret.RowsAffected () / / number of rows affected by the operation if err! = nil {fmt.Printf ("get RowsAffected failed, err:%v\ n") Err) return} fmt.Printf ("update success, affected rows:%d\ n", n)} / / Delete data func deleteRowDemo () {sqlStr: = "delete from user where id =?" Ret, err: = db.Exec (sqlStr, 6) if err! = nil {fmt.Printf ("delete failed, err:%v\ n", err) return} n, err: = ret.RowsAffected () / / number of rows affected by the operation if err! = nil {fmt.Printf ("get RowsAffected failed, err:%v\ n") Err) return} fmt.Printf ("delete success, affected rows:%d\ n", n)} NamedExec

The DB.NamedExec method is used to bind a SQL statement to a field with the same name in the structure or map.

Func insertUserDemo () (err error) {sqlStr: = "INSERT INTO user (name,age) VALUES (: name,:age)" _, err = db.NamedExec (sqlStr, map [string] interface {} {"name": "seven meters", "age": 28,}) return} NamedQuery

In the same way as DB.NamedExec, queries are supported.

Func namedQuery () {sqlStr: = "SELECT * FROM user WHERE name=:name" / / use map as a naming query rows, err: = db.NamedQuery (sqlStr, map [string] interface {} {"name": "seven meters"}) if err! = nil {fmt.Printf ("db.NamedQuery failed, err:%v\ n") Err) return} defer rows.Close () for rows.Next () {var u user err: = rows.StructScan (& u) if err! = nil {fmt.Printf ("scan failed, err:%v\ n" Err) continue} fmt.Printf ("user:%#v\ n", u)} u: = user {Name: "7m",} / / use structure to name the query Map rows according to the db tag of the structure field, err = db.NamedQuery (sqlStr, u) if err! = nil {fmt.Printf ("db.NamedQuery failed, err:%v\ n") Err) return} defer rows.Close () for rows.Next () {var u user err: = rows.StructScan (& u) if err! = nil {fmt.Printf ("scan failed, err:%v\ n" Err) continue} fmt.Printf ("user:%#v\ n", u)}} transaction operation

For transaction operations, we can use the db.Beginx () and tx.Exec () methods provided in sqlx. The sample code is as follows:

Func transactionDemo2 () (err error) {tx, err: = db.Beginx () / / Open transaction if err! = nil {fmt.Printf ("begin trans failed, err:%v\ n", err) return err} defer func () {if p: = recover () P! = nil {tx.Rollback () panic (p) / / re-throw panic after Rollback} else if err! = nil {fmt.Println ("rollback") tx.Rollback () / / err is non-nil Don't change it} else {err = tx.Commit () / / err is nil; if Commit returns error update err fmt.Println ("commit")}} () sqlStr1: = "Update user set age=20 where id=?" Rs, err: = tx.Exec (sqlStr1, 1) if errands = nil {return err} n, err: = rs.RowsAffected () if errata = nil {return err} if n! = 1 {return errors.New ("exec sqlStr1 failed")} sqlStr2: = "Update user set age=50 where itemized?" Rs, err = tx.Exec (sqlStr2, 5) if erratic roomnil {return err} n, err = rs.RowsAffected () if erratic roomnil {return err} if n! = 1 {return errors.New ("exec sqlStr1 failed")} return err} sqlx.In

Sqlx.In is a very convenient function provided by sqlx.

Sample table structure for bulk insertion of sqlx.In

To facilitate the demonstration of inserting data, create a user table with the following structure:

CREATE TABLE `user` (`id` BIGINT (20) NOT NULL AUTO_INCREMENT, `name` VARCHAR (20) DEFAULT'', `age` INT (11) DEFAULT '0users, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4; structure

Define a user structure whose fields match the columns of the user table in the database through tag.

Type User struct {Name string `db: "name" `Age int `db: "age" `} bindvars (binding variable)

Query placeholder? Internally, it is called bindvars (query placeholder), which is very important. You should always use them to send values to the database because they prevent SQL injection attacks. Database/sql does not attempt any validation of the query text; it is sent to the server as is along with the encoded parameters. Unless the driver implements a special interface, the query is prepared on the server before execution. So bindvars is database-specific:

Used in MySQL?

PostgreSQL uses enumerated bindvar syntax such as $1, $2, etc.

In SQLite? And $1 syntax are supported.

Syntax for: name used in Oracle

A common misconception of bindvars is that they are used to insert values in sql statements. They are actually only used for parameterization and are not allowed to change the structure of SQL statements. For example, trying to parameterize a column or table name using bindvars will not work:

/ /? Cannot be used to insert a table name (as a placeholder for a table name in a SQL statement) db.Query ("SELECT * FROM?", "mytable") /? Nor can it be used to insert column names (as placeholders for column names in SQL statements) db.Query ("SELECT?,? FROM people", "name", "location") splicing statements to achieve batch insertion

Stupid, but easy to understand. That is, splicing as many User as there are.

/ / BatchInsertUsers constructs a batch insert statement func BatchInsertUsers (users [] * User) error {/ / store (?) Slice valueStrings: = make ([] string, 0, len (users)) / / slice valueArgs for storing values: = make ([] interface {}, 0, len (users) * 2) / / traversing users to prepare relevant data for _, u: = range users {/ the number of placeholders here corresponds to the number of inserted values valueStrings = append (valueStrings, "(?) ?) ") ValueArgs = append (valueArgs, u.Name) valueArgs = append (valueArgs, u.Age)} / / self-stitching specific statements to be executed stmt: = fmt.Sprintf ("INSERT INTO user (name, age) VALUES% s", strings.Join (valueStrings, ",") _, err: = DB.Exec (stmt, valueArgs...) Return err} batch insertion using sqlx.In

The premise is that our structure needs to implement the driver.Valuer interface:

Func (u User) Value () (driver.Value, error) {return [] interface {} {u.Name, u.Age}, nil}

The batch insertion code using sqlx.In is as follows:

/ / BatchInsertUsers2 uses sqlx.In to help us concatenate statements and parameters. Note that the parameters passed in are [] interface {} func BatchInsertUsers2 (users [] interface {}) error {query, args, _: = sqlx.In ("INSERT INTO user (name, age) VALUES (?), (?), (?)", users..., / / if arg implements driver.Valuer Sqlx.In will expand it by calling Value () fmt.Println (query) / / View the generated querystring fmt.Println (args) / / View the generated args _, err: = DB.Exec (query, args...) Return err} batch insertion using NamedExec

Note: this function needs version 1.3.1 or above, and there is still a problem with version 1.3.1. There can be no spaces and; in the sql statement. For more information, please see issues/690.

The code for batch insertion using NamedExec is as follows:

/ / BatchInsertUsers3 uses NamedExec to insert func BatchInsertUsers3 (users [] * User) error {_, err: = DB.NamedExec ("INSERT INTO user (name, age) VALUES (: name,: age)", users) return err} in batch

Try the combination of the above three methods:

Func main () {err: = initDB () if err! = nil {panic (err)} defer DB.Close () U1: = User {Name: "seven meters", Age: 18} U2: = User {Name: "q1mi", Age: 28} U3: = User {Name: "Little Prince" Age: 38} / / method 1 users: = [] * User {& U1, & U2, & U3} err = BatchInsertUsers (users) if err! = nil {fmt.Printf ("BatchInsertUsers failed, err:%v\ n", err)} / / method 2 users2: = [] interface {} {U1, U2 U3} err = BatchInsertUsers2 (users2) if err! = nil {fmt.Printf ("BatchInsertUsers2 failed, err:%v\ n", err)} / / method 3 users3: = [] * User {& U1, & U2, & U3} err = BatchInsertUsers3 (users3) if err! = nil {fmt.Printf ("BatchInsertUsers3 failed, err:%v\ n") Query example for err)}} sqlx.In

An additional usage for sqlx.In is added here to implement In query and FIND_IN_SET functions in sqlx query statements. That is, the realization of SELECT * FROM user WHERE id in (3 FROM user WHERE id in 2), and SELECT * FROM user WHERE id in (3 recorder 2) ORDER BY FIND_IN_SET (id,'3 recollection 2 1').

In query

Query the data of id in a given id collection.

/ / QueryByIDs dynamically populates id query, args, err: = sqlx.In ("SELECT name, age FROM user WHERE id IN (?), ids) if err! = nil {return} / / sqlx.In returns the query statement with `?` bindvar based on the given ID query func QueryByIDs (ids [] int) (users [] User, err error) {/ / fill in id query, args, err: = sqlx.In (" SELECT name, age FROM user WHERE id IN (?) ", ids) We use Rebind () to rebind it query = DB.Rebind (query) err = DB.Select (& users, query, args...) Return} in query and FIND_IN_SET function

Queries the data of id in a given id collection and maintains the order of the given id collection.

/ / QueryAndOrderByIDs dynamically populates id strIDs in the specified id query and maintenance order func QueryAndOrderByIDs (ids [] int) (users [] User, err error) {/ / id strIDs: = make ([] string, 0, len (ids)) for _, id: = range ids {strIDs = append (strIDs, fmt.Sprintf ("% d", id))} 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} / / sqlx.In returns the query statement with `? `bindvar. We use Rebind () to rebind it query = DB.Rebind (query) err = DB.Select (& users, query, args...) Return}

Of course, in this example you can also use the IN query first, and then sort the query results by a given ids through the code.

The above is about the content of this article on "how to connect golang to sqlx library". I believe we all have a certain understanding. I hope the content shared by the editor will be helpful to you. If you want to know more about the relevant knowledge, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.