SQLX Instructions for use

SQLX User Guide

Reference Link: http://jmoiron.github.io/sqlx/
SQLX is a Go language pack that adds a lot of extensions to the built-in Database/sql package, simplifying the writing of database operation codes

Resources

If you are unfamiliar with the SQL usage of the Go language, you can learn from the following websites:
http://go-database-sql.org/

If you are unfamiliar with Golang language, you can learn from the following websites:
Https://blog.csdn.net/wdy_yx
Since the Database/sql interface is a subset of SQLX, all usage of database/sql in the current document is also used for SQLX

Begin

Installing the SQLX Drive

go get github.com/jmoiron/sqlx

This article accesses SQLite data

go get github.com/mattn/go-sqlite3

Handle Types

SQLX design and Database/sql use the same way. Contains 4 main handle types:

• Sqlx. DB: And SQL. DB similar to that of the database
• Sqlx. Tx: And SQL. Tx similar, indicating transacion
• Sqlx. STMT: And SQL. The stmt is similar, indicating prepared statement.
• Sqlx. NAMEDSTMT: Represents prepared statement (supports named parameters)

All handler types provide compatibility with database/sql, meaning that it is used when calling SQLX. Db. When you query, you can replace it directly with SQL. Db. Query. This makes it easy for SQLX to be added to existing database projects.

In addition, SQLX has two cursor types:

• Sqlx. Rows and SQL. Rows similar to Queryx returned.
• Sqlx. Row and SQL. Similar to row, QUERYROWX returns.

Connecting to a database

A DB instance is not a link, but an abstraction represents a database. This is why the creation of a DB does not return errors and panic. It maintains a connection pool internally and attempts to connect when a connection is required. You can create a sqlx.db through open or create a new sqlx.db from an existing sql.db by newdb

var db *sqlx.DB// exactly the same as the built-indb = sqlx.Open("sqlite3",":memory:")// from a pre-existing sql.DB; note the required driverNamedb = sqlx.NewDb(sql.Open("sqlite3",":memory:"),"sqlite3")// force a connection and test that it workederr = db.Ping()

In some environments, you may need to open a DB connection at the same time. You can call connect, this function opens a new DB and tries to ping. The Mustconnect function is panic when a link error occurs.

var err error// open and connect at the same time:db, err = sqlx.Connect("sqlite3", ":memory:")// open and connect at the same time, panicing on errordb = sqlx.MustConnect("sqlite3",":memory:")

Querying 101

The handle types in SQLX implements the same basic operational syntax for database queries.

• Exec (...) (SQL. RESULT,ERROR) has not changed compared with Database/sql
• Query (...) (*sql. Rows, error) has not changed compared to Database/sql
• Queryrow (...) *sql. Row and Database/sql have not changed compared

Extensions to built-in syntax

• Mustexec () SQL. Result-exec, but panic or error
• Queryx (...) (*SQLX. Rows, error)-Query, but return an SQLX. Rows
• QueryRows (...) *sqlx. Row-queryrow, but return to an SQLX. Row

And here's the new syntax.

• Get (dest interface{},...) error
• Select (dest interface{},...) error
  The use of these methods is described in more detail below

Exec

EXEC and Mustexec get a connection from the connection pool and then just want the corresponding query action. For Ad-hoc query execution drivers that are not supported, a prepared statement is created behind the action execution.
This connection is returned to the connection pool before the result is returned.

schema := `CREATE TABLE place (    country text,    city text NuLL,    telcode integer);`// execte a query on the serverresult, err := db.Exec(schema)// or, you can use MustExec, which panics on errorcityState := `INSERT INTO place (country, city, 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", "Johannesbury", 27)

Result in the above code has two possible data lastinsertd () or rowsaffected (), depending on the driver
In MySQL code, performing an insert operation on a table containing Auth-increment key will get Latinsertid (), which will be returned in PostgreSQL only in the row cursor that uses the returning statement.

Bindvars

In the code? Placeholders, called Bindvars, are important, and you can always use them to send data to a database that can be used to organize SQL injection attacks.

Database/sql does not perform any validation on the query statement, and what is sent to the server is what is passed in.
Unless driver implements a specific interface, query is ready before the database executes. The bindvars of different databases is not the same.

• MySQL use?
• PostgreSQL use and so on
• SQLite use? or $
• Oracle Use: Name

Other databases may not be the same. You can use SQLX. Db. Rebind (String) string function is used? syntax to get a query statement that is appropriate to execute on the current database

The common misconception about binddvars is that they are used to interpolate values. They are only used for parameterization and do not allow changes to the legitimate interface of SQL statements. For example, the following usage will be an error.

// doesn‘t workdb.Query("SELECT * FROM ?","mytable")// also doesn‘t workdb.Query("SELECT ?,? FROM people","name","location")

Query

Query is the primary method for executing queries in Database/sql, which returns a row result. Query returns a Sql.rows object and an Error object

// fetch all places from the dbrows, err := db.Query("SELECT country,city, telcode FROM place")// iterate over each rowfor rows.Next() {    var country string    // note that city can be NULL, so we use the NullString type    var telcode int    err = rows.Scan(&country,&city,&telcode)}

Rows should be used as a cursor instead of a series of results. Although the methods for database-driven caching are different,
The pass red Next () iteration gets a column of results each time, and in the case of very large query results, can effectively limit the use of memory,
Scan () uses reflect to map the results of each column of SQL to the data type of the go language such as String, []byte, and so on. If you do not traverse the full part of the rows result,
Be sure to remember to call rows before returning the connection to the connection pool. Close ().

The error returned by query may have occurred while the server was preparing the query, or it might have been issued when the query was executed. For example, you might get a bad level of connectivity from the connection pool (although the database tries to discover or create a working connection 10 times).
In general, errors are mainly caused by incorrect SQL statements, similar errors, incorrect domain names or table names, and so on.

In most cases, rows.scan () copies the data obtained from the drive, regardless of how the cache is driven. Special type of SQL. Rawbytes can be used to get a zero-copy slice byte from the data returned by the driver. This value is not valid the next time you call next, because the memory it points to has been driven to rewrite the other data.

Query uses connection after all rows have been traversed through next () or rows are called. Released after Close ().
Queryx is similar to query behavior, but returns a SQLX. Rows object, which supports extended scan behavior.

type Place struct {    Country string    City sql.NullString    TelephoneCode int `db:"telcode`}rows, err := db.Queryx("SELECT * FROM place")for rows.Next() {    var p Place    err = rows.StructScan(&p)}

Sqlx. The main extension of ROWX is Structscan, which can automatically scan the results to the domain in the corresponding structure (Fileld).
Note that the domain (field) in the struct must be exportable (exported) so that the SQLX can write the value into the struct.
As shown in the preceding code, you can use the DB struct tag to specify that the struct field maps to a specific column name in the database, or in db. Mapperfunc () to specify the default mappings.
DB defaults to the filed name of the struct to perform the strings. After lower, match the column names of the database. For more detailed information on Structscan,slicescan,mapscan, see the later section advanced scanning.

Queryrow

Queryrow gets a column of data from the database server. It gets a connection from the connection pool. It then executes query, returning a row object that has a self-internal rows object.

row := db.QueryRow("SELECT * FROM place WHERE telcode=?",852)var telcode interr = row.Scan(&telcode)

Unlike query, Queryrow returns only a row type, does not return an error, and if an error occurs during the execution of the query, it is returned via scan, and SQL is returned if the query result is empty. Errnorows.
If the scan itself is faulted, the error is also returned by scan.

The connection used by Queryrow when result is returned is closed, which means that Sql.rawbyes is not available when using Queryrow because driver uses SQL. Rawbytes references memory and may also be invalid after connection is reclaimed.

QUERYROWX returns a sqlx.row instead of a sql.row, which implements the same scan method as rows, as well as the advanced scan method as follows: (More advanced scan method, Senior scanning section)

var p Placeerr := db.QueryRows("SELECT city, telcode FROM place LIMIT 1").StructScan(&p)

Get and Select

Get and select are a very time-saving extension. They combine query with a very flexible scan syntax. To introduce them more clearly, let's discuss what is Scannalbe:

a value is scannable if it is not a struct, eg string,inta value is scannable if it implements sql.Scannera value is scannable if it is a struct with no exported fields (eg time.Time)

Get and select Use Rows.scan for the type of scannable, and rows for non-scannable types. Structscan. Get is used to get a single result and then scan,select to get the result slice.

p := Place{}pp := []Place{}// this will pull the first place directly into perr = db.Get(&p,"SELECT * FROM place WHERE telcode > ?", 50)// they work with regular types as wellvar id interr = db.Get(&id,"SELECT count(*) FROM place")// fetch at most 10 place namesvar names []stringerr = db.Select(&names,"SELECT name FROM place LIMIT 10")

Get and select Close rows after executing the query, and any problems encountered during the execution phase will return an error. Because of the structscan they use internally, the following
The features spoken in advanced scanning sections also apply to get and select.

Select can improve the coding path, but note that select and Queryx are very different, because select puts the entire result into memory at once. If the query results are not limited to a specific size, it is best to use the Query/structscan iterative approach.

Transactions

In order to use transactions, you must use Db.begin () to create the following code is wrong:

db.MustExec("BEGIN;")db.MustExec(...)db.MustExec("COMMIT;")

The EXEC and other query statements request a connection to the DB, return to the connection pool after execution, and do not guarantee that the connection is the one used at the Begin execution, so the correct practice is to use Db.begin:

tx, err := db.BEGINerr = tx.Exec(...)err = tx.Commit()

In addition to the begin, DB can also return SQLX using Extended BeginX () and Mustbegin (). Tx:

tx := db.MustBegin()tx.MustExec(...)err = tx.Commit()

Sqlx. TX has all the Haandle extensions owned by Sqlx.db.
Because transaction is a connection state, the TX object must bind and control a single connection. A TX saves a connection throughout its lifecycle and then releases it when it calls commit or rollback (). You must be very careful when calling these functions, otherwise the connections will always be occupied until garbage collection.
Because there can be only one connection in a transaction, only one statement can be executed at a time. The cursor object row* and rows must be scanned or closed before additional query operations are performed. If you try to send data to the database when the database returns you data, this operation may interrupt connection.
Finally, the TX object simply executes a BEGIN statement and binds a connection, which does not actually perform any operations on the server. The real behavior of transaction consists of locking and isolation, which are different on different databases.

Prepared statements

For most databases, when a query executes, the statement inside the database is actually ready. Then you can pass the SQLX. Db. Prepare () Prepares the statements for later use in other places.

stmt, err := db.Prepare(`SELECT * FROM place WHERE telcode = ?`)row = stmt.QueryRow(65)tx, err := db.Gegin()txStmt, err := tx.Prepare(`SELECT * FROM place WHERE telcode = ?`)row = txStmt.QueryRow(852)

Prepare actually performs the preparation operation on the database, so it needs a connection and its connection state.
Database/sql abstracts This part and automatically creates statement on the new connection, so that developers can perform operations concurrently on multiple connection through stmt objects.
Preparex () returns a SQLX. The stmt object that contains the SQLX. DB and Sqlx.tx all handle extensions (methods).

Sql. The TX object contains a stmt () method that returns a statement that is specific to the transaction in the existing statement.
Sqlx. TX also contains a STMTX () method, from an existing SQL. Create a transaction-specific sqlx.stmt in Stmt or sqlx.stmt.

Query Helpers

"In" Queries
Because Database/sql does not parse your query statements and then pass parameters directly to driver, for in

SQLX Instructions for use