Golang Accessing SQL like Database (ii)--SQL package main data structure and method

This is a creation in Article, where the information may have evolved or changed.

Next, the main implementation of SQL package is Database/sql, and the more important data structures in SQL are:

• Type DB
• Type Row
• Type Rows
• Type Stmt
• Type Tx

Sql. Register

Sql. The register interface is used to register the database driver. A third-party-developed database requires this interface to be called in Init to complete the registration of this driver.

The interface declaration is:

func Register(name string, driver driver.Driver)

If register is called two times to register the same name as driver, or driver for Nil,register will be panic.

Function Open

func Open(dirverName, dataSourceName string)(*DB error)

The Open function opens a specified database based on the DB drivername and driver-specific description information in the arguments, and the driver-specific description information typically contains database name and connection information. The datasourcename formats supported by different driver may be different.

Open is generally just a validation parameter and does not create a connection for database. If you want to verify that the connection specified by DataSourceName is valid, you need to call the ping function to test.

Open returns a pointer to the DB type. DB maintains its own idle connection pool, which can be safely used by multiple gorutines concurrently.

Type DB

DB is the main data structure in Database/sql, and defines various basic operation methods for database.

DB is a database handle that represents a connection pool of 0 or more potential connections. It can be safely used by multiple goroutine concurrently.

The SQL package automatically creates and frees connections, and it maintains a free pool for idle connections. If the database has the concept of per-connection state, each status can only be reliably observed in one thing. Once DB. Begin is called, and it returns the Tx bound to a unique connection. If a Commit or Rollback is invoked on a thing, the transaction's connection will be rolled back to the idle connection pool of the db. The size of the pool can be set by Setmaxidleconns.

type DB struct {    driver driver.Driver    dsn string    numClosed uint64    mu sync.Mutex    freeConn []*driverConn    numOpen int    openerCh Chan struct{}    closed bool    dep map[finalCloser]depSet    lastPut     map[*driverConn]string    maxIdle     int    maxOpen     int    maxLifetime time.Duration    cleanerCh   chan struct{}}

Func (*DB) Begin

func (db *DB)Begin() (*Tx, error)

Begin starts a transaction, and the transaction sees that the isolation level has a driver decision.

Func (*DB) Close

func (db *DB)Close() error

Close is used to close the database and free all open resources. Shutting down a database is a rare operation because database processing is a long-term existence and can be shared across multiple goroutine.

Func (*DB) Driver

func (db *DB)Driver() driver.Driver

The driver function returns the underlying driver information for the database.

Func (*DB) Exec

func (db *DB)Exec(query string, args ...interface{}) (Result, error)

The query that exec executes does not return any rows.

type Result is a summary of the execution of an SQL statement. Result contains two methods: Lastinsertid Returns the shaping value produced by the database executing a command. Usually the "Auto increment" field when a new row is inserted. rowsaffected Returns the line number affected by the update, insert, or delete operation. Not all database or database drivers support both of these characteristics.

type Result interface {    LastInsertId() (int64, error)    RowsAffected()(int64, error)}

Func (*DB) Ping

func (db *DB) Ping() error

The ping function is used to verify that the connection to the database is still in the alive state. If the link is a non-alive state, the connection is established.

Func (*DB) Prepare

func (db *DB) Prepare(query string) (*Stmt, error)

The Prepare function prepares a statement for later queries and executions. Multiple queries and executions can be executed concurrently through the statement returned by prepare. when statement is no longer used, the caller must call statement's Close method to close statement.

Func (*DB) Query

func (db *DB) Query(query string, args ...interface{})(*Rows, error)

The query function executes a query to the database and returns a pointer to the execution result rows, which can be iterated through all the results as described in the rows provided in the following article.

Func (*DB) Queryrow

func (db *DB) QueryRow(query string, args ...interface{}) *Row

The qureyrow is used for database query operations that are returned at most one row. So Queryrow always returns a non-nil operation. When the actual result returned by the error query or query is empty, only the scan method of the row struct body (object) is invoked.

Example:

id := 123var username stringerr := db.QueryRow("SELECT username FROM users WHERE id=?", id).Scan(&username)switch {case err == sql.ErrNoRows:    log.Printf("No user with that ID.")case err != nil:    log.Fatal(err)default:    fmt.Printf("Username is %s\n", username)}

Func (*DB) setconnmaxlifetime

func (db *DB)SetConnMaxLifetime(d time.Duration)

The setconnmaxlifetime is used to set the maximum time interval at which a connection can be reused. If it times out, the connection is closed before it is reused. If D <= 0, the connection will be permanently retained.

Func (*DB) Setmaxidleconns

func (db *DB)SetMaxIdleConns(n int)

Setmaxidleconns set the maximum number of connections for the idle connection pool. If the value of Maxopenconns is > 0, but less than the Maxidleconns set here, Maxidleconns will automatically drop to the same limit as Maxopenconns.

If <= 0, no idle connections are retained.

Func (*DB) Setmaxopenconns

func (db *DB)SetMaxOpenConns(n int)

The Setmaxopenconns is used to set the maximum number of connections that database can open.

If <= 0, there is no connection limit. And the default value is 0 (unlimited)

Func (*DB) Stats

func (db *DB)Stats() DBStats

The stats function returns statistics about the database.

type DBStats struct {    OpenConnections int}

Type Row

type Row struct{}

ROW data structure has only one method scan

func (r *Row)Scan(dst ...interface{})

Scan copies the columns in the matching row to the values pointed to by Dest. If there are multiple rows of matches, only the first row is returned and the extra rows are discarded. If there is no row match, the **errnorows is returned. The number of values in DST must be the same as the number of rows returned.

Scan converts columns read from database to the normal type of go listed below and the special type provided by SQL package.

• *string
• *[]byte
• *int, *int8, *int16, *int32, *int64
• *uint, *uint8, *uint16, *uint32, *uint64
• *bool
• *float32, *float64
• *interface{}
• *rawbytes

The simplest case is: if the source data read from the database is Interger,bool or string type, and the type is identified with T, then the target is exactly the pointer to T *t , then scan simply assigns the data to the target variable by pointer.

Scan also allows lossless conversion between sting and numeric type data. Scan directly retrieves the numeric type data string (stringify) from the database as String. Conversely, if you convert a string to a numeric type, you need to check for overflow and so on. It is also necessary to consider the accuracy of the conversion between different types of numeric. * One exception is: If you get the float64 numeric string as a string, you may lose some of the information. So normally, the value of the float type is converted to the float64 type.

If the target data type is *[]byte, scan copies the source data read from the database to the location of the destination address. Copy copies are owned by the caller, can be modified, and can be held indefinitely. Use type *rawbytes to avoid copy operations.

If the parameter is of type *interface{}, scan will directly copy the data returned from the drive without making any conversions.

If the source type is time . time, can be converted to *time by scan . Time, *interface{}, *string, or *[]byte type. When converted to *string or *[]byte, it will be used to time . Format3339nano

The source type is booland can be converted by scan to *bool, *interface{}, *string, *[]byte, or *rawbytes

If you want to scan the source data to the *bool type, you need the metadata to be true,false,1,0 or you can be StrConv. Parsebool parsing a string

Type Rows

The rows struct is similar to type Row,row, which is the query result that returns a single line, and rows says multiple rows of query results.

type Rows struct {}

Func (*rows) Close

func (rs *Rows)Close() error

Close will close a rows to prevent the rows from still being enumerated. If the return result of Next is false, rows will be automatically closed, and closing will still satisfy the error checking of the results. Close is idempotent and does not affect the error checking of the results.

Func (*rows) Columns

func (rs *Rows)Columns()([]string, error)

Column returns a list of the names of the columns. If rows is already close, an error is returned.

Func (*rows) ERR

func （rs *Rows）Err() error

If there is error,err in the enumeration process, error will be returned. Err can be called after a display or an implicit close rows.

Func (*rows) Next

func (rs *Rows)Next() bool

Next prepares the next result for the scan function and returns trueif there is a result. falseis returned when there is no next line or an error occurred during the preparation process, and err is able to differentiate between the two errors.

Each time you call scan to get results, you need to first execute next.

Func (*rows) Scan

Scan of rows with Row, reference func (*row) scan

Type Stmt

stmt is a ready state. STMT provides some methods to prepare the state for execution. stmt can be safely executed by multiple goroutine concurrently.

type Stmt struct {}

STMT provides the following methods:

func (s *stmt)Close() errfunc (s *stmt)Exec(args ...interface{}）(Result, error)func (s *stmt)Query(args ...interface{}) (*Rows, error)func (s *stmt)QueryRow(args ...interface{}) (*Row, error)

Stmt has a call to func (*db) Prepare returns, so the following two methods are equivalent:

stmt，err := db.Prepare(querystring)res, err := stmt.Exec(...)

is equivalent to:

res, err := db.Exec(querystring, ...)

Extrapolate, the other methods are the same.

Tyte Tx

type Tx struct{}

TX is a database transaction in progress. Once the database transaction must end with commit or rollback. After a commit or rollback is called, an error Errtxdone is returned if the transaction fails.

So the transaction begins with the func (*db) begin () return transaction *tx, ending with the commit or rollback of TX. It is important to note that the prepare method that has the TX return statement is also just a commit or rollback close, not the Close method of stmt.

Func (*TX) Prepare

func (tx \*Tx)Prepare(query string) (\*stmt, error)

A prepare,prepare similar to DB creates a prepared statement for the current transaction. When a commit or rollback of a transaction is called, the statement will not be valid.

Func (*TX) Commit

func (tx *Tx)Commit() error

Commit commits the current transaction.

Func (*TX) Exec

func (tx *Tx)Exec(query string, args ...interface{}) (Result, error)

EXEC executes a query that does not return results, such as insert,update.

Func (*TX) Query

func (tx *Tx)Query(query string, args ...interface{}) (*Rows, error)

Query with the previous DB and stmt

Func (*TX) Queryrow

func (tx *Tx)QueryRow(query string, args ...interface{}) *Row

Query with DB and stmt

Func (*TX) Rollback

func (tx *Tx)Rollback() error

Rollback discards the operation of the current transaction.

Func (*TX) Stmt (Stmt *stmt) *stmt

stmt returns a specific stmt for the current transaction, based on an already existing stmt.

Such as:

updateMoney, err := db.Prepare("UPDATE balance SET money=money+? WHERE id=?")...tx, err := db.Begin()...res, err := tx.Stmt(updateMoney).Exec(123.45, 98293203)

The above example obtains the same statement for the current transaction TX through TX.STMT based on the statement Updatemoney. The new special statement will expire after the commit or rollback of TX has been executed.

Reference links

Https://golang.org/src/database/sql/doc.txt
Https://godoc.org/database/sql
https://golang.org/src/ database/sql/
HTTPS://GODOC.ORG/GITHUB.COM/LIB/PQ
HTTPS://GITHUB.COM/LIB/PQ
https://godoc.org/ GITHUB.COM/LIB/PQ
http://jmoiron.github.io/sqlx/
Https://github.com/jmoiron/sqlx/blob/master/sqlx.go
Https://github.com/golang/go/wiki/SQLInterface
Https://github.com/golang/go/wiki/SQLDrivers