Original author, public number [programmer reading], welcome to pay attention to the public number, reprint the article please indicate the source oh.
It should be said that database programming is the basic functional module provided by any programming language, whether it is supported by the programming language built-in, or through external libraries; Of course, different programming languages provide different database programming apis, and need to support a variety of databases, such as MySQL, SQLServer,Postgres and other commonly used databases.
Leaving aside other programming languages, in this article we’ll talk about database programming in Go and learn how to use the standard library provided by Go to write generic database code.
Database connection and driver
The database/SQL and database/SQL/driver
Standard library Database/SQL is the database operation abstraction layer of Go language. The specific database operation logic is implemented by different third-party packages, and the standard library Database/SQL/Driver provides the standard specifications for the implementation of these third-party packages.
Therefore, Go language database programming, generally only need to import the standard library database/ SQL package, this package provides all the necessary structures, functions and methods to operate a database, the following import package statement:
import database/sql
Copy the codeThe advantage of the Go language is that when you migrate from one database to another (such as SQL Server to MySQL), you only need to change the driver package.
Database driver package supported by Go
If you want to connect to the MySQL database, how do you implement such a package? In fact, Go language standard library Database/SQL /driver defines all interfaces to implement third-party driver packages, we only import the implementation of database/ SQL /driver interface driver packages.
Here is a list of database drivers that support Golang:
Github.com/golang/go/w…
Install third-party driver packages
Take the MySQL database driver package as an example:
$ go get -u github.com/go-sql-driver/mysql
Copy the codeImporting the Driver Package
import database/sql
import _ "github.com/go-sql-driver/mysql"
Copy the codeSQL DB structure
The SQL. DB structure is a database operation object encapsulated in the SQL/DATABASE package, which contains the basic methods of operating the database.
DSN
The DSN format varies from database to database, depending on the implementation of the database driver. The DSN format for go-SQL-driver/SQL is as follows:
//[username [: password]@][protocol (database server address)]]/ database name? Parameter list [username[:password]@][protocol[(address)]]/ dbName [? Param1 = Value1 &...¶mN=valueN]Copy the codeInitialize the SQL DB
There are two functions in the database/ SQL package that initialize and return a * SQL. DB structure object:
//driverName indicates the driverName, DSN func Open(driverName, dataSourceName string) (*DB, error) func OpenDB(c driver.Connector) *DBCopy the codeHere’s how to use these two functions:
In general, we use the Open() function to initialize and return an instance of the * SQL.db structure. Using the Open() function, we simply pass in the driver name and the corresponding DSN. It is easy to use, and it is very common.
import "database/sql"
import _ "github.com/go-sql-driver/mysql"// Notice the _ func in frontopen(){
const DRIVER = "mysql"
var DSN = "root:123456@tcp(localhost:3306)/test? charset=utf8&parseTime=True&loc=Local"
var err error
db, err = sql.Open(DRIVER, DSN)
iferr ! = nil { panic(err) }iferr = db.Ping(); err ! = nil{ panic(err) } }Copy the codeThe OpenDB() function relies on the driver package to implement the Connector interface of the SQL /database/driver package. This method is not recommended and is not generic.
import "database/sql"
import "github.com/go-sql-driver/mysql"// Notice the absence of _ funcopenConnector() {
Connector, err := mysql.NewConnector(&mysql.Config{
User: "root",
Passwd: "123456",
Net: "tcp",
Addr: "localhost:3306",
DBName: "test",
AllowNativePasswords:true,
Collation:"utf8_general_ci",
ParseTime:true,
Loc:time.Local,
})
iferr ! = nil { panic(err) } db = sql.OpenDB(Connector)iferr = db.Ping(); err ! = nil{ panic(err) } }Copy the codeInitialize a *sql.DB pointer structure using the method previously defined:
Var db *sql. db // initialize '*sql. db' func in init methodinit(){open() // or openConnector()}Copy the codeSql. DB is a structure encapsulated in the SQL /database package, but does not represent a database connection object. In fact, we can think of SQL. DB as a simple database connection pool.
Func (db * db) SetMaxIdleConns(n int) SetMaxIdleConns(n int) SetMaxIdleConns(n int) SetMaxOpenConns(n int) SetMaxOpenConns(n int) SetMaxOpenConns(n int) SetMaxOpenConns(n int) SetMaxOpenConns(n int) SetMaxOpenConns(n int) The default value is 0 func (db * db) SetConnMaxLifetime(d time.duration)// Set the connection timeout periodCopy the codeCode demo
Db. SetMaxIdleConns(0)// If this parameter is set to 0, db.SetConnMaxLifetime(time.Second * 5)// Times out in 5 secondsCopy the codeBasic Database operations
SQL > create table users (CURD); SQL > create table users (CURD);
CREATE TABLE users(
id INT NOT NULL AUTO_INCREMENT COMMENT 'ID',
username VARCHAR(32) NOT NULL COMMENT 'Username',
moeny INT DEFAULT 0 COMMENT 'Account balance',
PRIMARY KEY(id)
);
INSERT INTO users VALUES(1,'Ming', 1000); INSERT INTO users VALUES(2,'little red', 2000); INSERT INTO users VALUES(3,'xiao gang', 1400);Copy the codeThe query
Query is the most basic function of database operation. In Go, you can use the Query() or QueryContext() methods in SQL.db. These two methods are defined as follows:
func (db *DB) Query(query string, args ... interface{}) (*Rows, error) func (db *DB) QueryContext(ctx context.Context, query string, args ... interface{}) (*Rows, error)Copy the codeThe Query() and QueryContext() methods return a sql.rows structure that represents a Query result set. The definition of sql.rows and the methods it contains are as follows:
typeRows struct {//contains filtered or unfiltered fields} func (rs *Rows) Close() error ColumnTypes() ([]*ColumnType, error) func (rs *Rows) Columns() ([]string, Func (rs *Rows) Err() error func (rs *Rows) Next() bool Func (rs *Rows) NextResultSet() bool func (rs *Rows) Scan(dest... Interface {}) error // Scan the structureCopy the codeUse the Next() and Scan methods of SQl.rows, but iterate over the returned result set. Here is sample code:
func query() {
selectText := "SELECT * FROM users WHERE id = ?"
rows, _ := db.Query(selectText, 2)
defer rows.Close()
for rows.Next() {
var (
id int
username string
money int
)
_ = rows.Scan(&id, &username,&money)
fmt.Println(id, username,money)
}
}
Copy the codeYou can also use the QueryRow() or QueryRowContext() methods in SQL.db, which are defined as follows:
func (db *DB) QueryRow(query string, args ... interface{}) *Row func (db *DB) QueryRowContext(ctx context.Context, query string, args ... interface{}) *RowCopy the codeQueryRow and QueryRowContext return a SQL. Row structure that represents a Row in the table. Sql. Row is defined as follows. As you can see, the SQL. Row structure has only one Scan() method that scans the data in the SQL. Row structure.
typeRow struct{ } func (r *Row) Scan(dest ... interface{}) errorCopy the codeCode demo
func queryRow(){
selectText := "SELECT * FROM users WHERE id = ?"
row := db.QueryRow(selectText, 2)
var (
id int
username string
money int
)
_ = row.Scan(&id, &username,&money)
fmt.Println(id, username,money)
}
Copy the codeAlternatively, a SQL.stmt structure ‘can be returned using the Prepare() or PrepareContext() methods in SQL.db.
Note: The sqL.stmt structure sends the SQL statement defined in Prepare() or PrepareContext() to the database for execution, sends the parameters required in the SQL statement to the database, and returns the result.
func (db *DB) Prepare(query string) (*Stmt, error)
func (db *DB) PrepareContext(ctx context.Context, query string) (*Stmt, error)
Copy the codeSql.stmt submits methods used with SQl.db to query and return result sets, as shown in the following example:
func queryStmt(){
stmt,err := db.Prepare("SELECT * FROM users WHERE id = ?")
iferr ! = nil{return
}
defer stmt.Close()
rows,err := stmt.Query(2)
defer rows.Close()
for rows.Next() {
var (
id int
username string
money int
)
_ = rows.Scan(&id, &username,&money)
fmt.Println(id, username,money)
}
}
Copy the codeadd
To add database records, you can use the Exec() or ExecContext() methods in SQL.db, which are defined as follows:
func (db *DB) Exec(query string, args ... interface{}) (Result, error) func (db *DB) ExecContext(ctx context.Context, query string, args ... interface{}) (Result, error)Copy the codeCode examples:
func insert(){
insertText := "INSERT INTO users values(? ,? ,?) "
rs,err := db.Exec(insertText,4,"juejin", 1000).iferr ! = nil{ fmt.Println(err)return
}
ifid,_ := rs.LastInsertId(); id > 0 { fmt.Println("Insert successful"} /* Insert successfullyifn,_ := rs.RowsAffected(); n > 0 { fmt.Println("Insert successful") /}} *Copy the codeThe first return value from the Exec() or ExecContext() method is a type that implements the sqL. Result interface, which is defined as follows:
Note that the LastInsertId() method returns the increment ID only if an INSERT statement is used and the table has an increment ID, otherwise it returns 0.
typeResult interface {LastInsertId() (int64, error) This function returns RowsAffected() (int64, error)// the number of rows in the affected table}Copy the codeWe can use the LastInsertId() method or RowsAffected() in SQL.result to determine whether the SQL statement was executed successfully.
In addition to using the Exec() and ExecContext() methods in SQL.db, you can also use Prepare() or PrepareContext() to return the SQL.stmt structure and write data to the table using the Exec() method in SQL.stmt.
Example of writing data to a table using SQl.stmt:
func insertStmt(){
stmt,err := db.Prepare("INSERT INTO users VALUES(? ,? ,?) ")
defer stmt.Close()
iferr ! = nil{return
}
rs,err := stmt.Exec(5,"juejin", 1000).if id,_ := rs.LastInsertId(); id > 0 {
fmt.Println("Insert successful")}}Copy the codeNote that executing SQL using Exec() or ExecContext() in SQL.stmt is equally appropriate for update and delete statements, which will not be demonstrated below.
update
As with adding data to a table, you can use the Exec() or ExecContext() methods of sql.db. However, when you UPDATE data with a database UPDATE statement, you can only use the RowsAffected() method in the sql.result structure to determine the number of RowsAffected. Then check whether the execution is successful.
func update() {
updateText := "UPDATE users SET username = ? WHERE id = ?"
rs,err := db.Exec(updateText,"database", 2)iferr ! = nil{ fmt.Println(err)return
}
ifn,_ := rs.RowsAffected(); n > 0 { fmt.Println("Update successful")}}Copy the codedelete
Using the DELETE statement to DELETE a data table record is the same as the update statement above, as shown in the following example:
func del() {
delText := "DELETE FROM users WHERE id = ?"
rs,err := db.Exec(delText,1)
iferr ! = nil{ fmt.Println(err)return
}
fmt.Println(rs.RowsAffected())
}
Copy the codeThe transaction
In the previous example, we did not enable transactions. If transactions are not enabled, each SQL statement submitted is treated as a transaction by default. If multiple statements are executed together and one of them is executed incorrectly, the previously executed SQL statement cannot be rolled back.
For some strict business logic (such as order payment, user transfer, etc.), you should submit multiple SQL statements in the same transaction to avoid the situation that the transaction cannot be rolled back due to execution errors.
Open the transaction
How do I start a new transaction? You can use the Begin() or BeginTx() methods in the SQL.db structure, which are defined as follows:
func (db *DB) Begin() (*Tx, error)
func (db *DB) BeginTx(ctx context.Context, opts *TxOptions) (*Tx, error)
Copy the codeThe second argument to the BeginTx() method is TxOptions, which is defined as follows:
type TxOptions struct {
// Isolation is the transaction isolation level.
// If zero, the driver or database's default level is used. Isolation IsolationLevel ReadOnly bool }Copy the codeThe Isolation field of TxOptions is used to define the Isolation level of the transaction. Its type is IsolationLevel, and the value range of Ioslation can be the following constants:
const (
LevelDefault IsolationLevel = iota
LevelReadUncommitted
LevelReadCommitted
LevelWriteCommitted
LevelRepeatableRead
LevelSnapshot
LevelSerializable
LevelLinearizable
)
Copy the codeThe Begin() and BeginTxt() methods return a sqL.tx structure. Operations on the database with SQL.tx are committed in the same transaction.
tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: Sql.levelserializable}) //Begin actually calls BeginTx(), db.begintx (context.background (), nil) tx, err := db.begin ()Copy the codeBasic operations supported by SQL.Tx
Here are the basic operations in the SQL.tx structure, used in the same way as the examples we demonstrated earlier
func (tx *Tx) Exec(query string, args ... interface{}) (Result, error) func (tx *Tx) ExecContext(ctx context.Context, query string, args ... interface{}) (Result, error) func (tx *Tx) Query(query string, args ... interface{}) (*Rows, error) func (tx *Tx) QueryContext(ctx context.Context, query string, args ... interface{}) (*Rows, error) func (tx *Tx) QueryRow(query string, args ... interface{}) *Row func (tx *Tx) QueryRowContext(ctx context.Context, query string, args ... interface{}) *RowCopy the codeTransaction commit
Commit(); Rollback(); Rollback(); Rollback();
func (tx *Tx) Commit() error
func (tx *Tx) Rollback() error
Copy the codeprecompiled
Stmt() and StmtContext() in the SQl.tx structure can encapsulate SQL.stmt as a transactionally-enabled SQL.stmt structure and return it. These two methods are defined as follows:
func (tx *Tx) Stmt(stmt *Stmt) *Stmt
func (tx *Tx) StmtContext(ctx context.Context, stmt *Stmt) *Stmt
Copy the codeUsing the Prepare() and PrepareContext() methods in SQl.tx, you can directly return a struct of SQL.stmt that supports transactions
func (tx *Tx) Prepare(query string) (*Stmt, error)
func (tx *Tx) PrepareContext(ctx context.Context, query string) (*Stmt, error)
Copy the codeThe sample
/ / modify functxUpdate(){
tx,_ := db.Begin()
rs,err := tx.Exec("UPDATE users SET username = ? WHERE id = ?"."sssss", 2)iferr ! = nil{ panic(err) } err = tx.Commit()iferr ! = nil{ panic(err) }ifn,_ := rs.RowsAffected(); n > 0{ fmt.Println("Success"// Use Stmt to modify functxStmt(){
tx,err := db.Begin()
iferr ! = nil{ panic(err) } stmt,err := db.Prepare("UPDATE users SET username = ? WHERE id = ?")
stmtTx := tx.Stmt(stmt)
defer stmtTx.Close()
rs,_ := stmtTx.Exec("test",2)
_ = tx.Commit()
ifn,_ := rs.RowsAffected(); n > 0{ fmt.Println("Success")}}Copy the codeRelated ORM frameworks
Previously, we introduced the Go language’s native support for database programming. However, more conveniently, we can directly use some open source Object Relational Mapping (ORM) framework, which can encapsulate the underlying SQL statements and directly Map them to Struct,Map and other data types. Save us directly write SQL statement work, very simple and convenient.
Here are a few commonly used ORM frameworks:
GORM
GORM is a very complete ORM framework, in addition to the basic increase in query support, also support association contains one, contains more, belongs to, many-to-many tables, also can write hook callbacks before or after create/save/update/delete/find, and also support transactions.
GORM currently supports database-driven MySQL, SQLite3, SQL Server, and Postgres.
Xorm
Xorm is a simple and powerful ORM framework, its function also GORM is similar, but the support database driven more than GORM, support for MySQL, SQL Server, SQLite3, Postgres, MyMysql, Tidb, Oracle database driven, etc.
Beego ORM
Beego ORM is a Web framework developed by Chinese Beego in a module, although it is a module of Beego, but can be used independently, but at present Beego ORM only supports MySQL, SQLite3,Postgres and other database drivers.
In addition to the three ORM frameworks we introduced above, there are many good ORM frameworks, you can have a look.
summary
Go language abstracts the operation of the database and encapsulates it in SQL /database package, providing a unified API for us to operate different databases, which is very practical. In this article, we explained the SQL/database package. The SQL DB, SQL, Rows, and SQL. Stmt, SQL. The use of structures such as Tx, believe that through the examples above, you also must be able to master the language database operation method.
Your attention is the biggest encouragement on my writing road!
