1. Introduction
xorm is one of the commonly used orm in the go language, used to operate databases.
Main Features:
- Supports automatic generation of xorm structures based on database
- Supports flexible mapping between Struct and database tables, and supports automatic synchronization
- Support transactions
- Supports the use of functions and structures such as Id, In, Where, Limit, Join, Having, Table, Sql, Cols, etc. as conditions
- Support query result set export function of csv, tsv, xml, json, xlsx, yaml, and html
2. Install and use xorm, pay attention to closing the connection!
import (
_ "/go-sql-driver/mysql"
"/xorm"
)Then we can construct a new method to initialize the orm client.
In the following code, we will return a function and a () function for the user to call. The reason is that some, such as scripts, etc.
When the execution is completed, the mysql connection needs to be closed to save the number of connections.
If not closed, in the case of high concurrency,There are many connections, which will blow up mysql!
type MysqlClient
func NewMysqlClient() (MysqlClient, func(), error) {
// Create engine object engine, err := ("mysql", "user:pwd@tcp(ip:port)/dbname?charset=utf8")
if err != nil {
("init engine fail! err:%+v", err)
}
//Connection pool configuration (30) // Maximum db connection (10) // Maximum number of db connection idles (30 * ) // The number of idle connections exceeds the time
// Log related configuration (true) // Print log ().SetLevel(log.LOG_DEBUG) // Print log level ({}) // Set log output (console, log files, system logs, etc.)
// Test connectivity if err = (); err != nil {
("ping to db fail! err:%+v", err)
}
return engine, func() {
()
}, err
}About the pit of connection number and ()
Engine can be closed manually, but in general, it does not need to be closed, and it will be closed automatically when the program exits.
However, if each connection has a new client, the http service will not exit. If there is high concurrency at this time, countless mysql connections will be added to blow up mysql.
In this NewMysqlClient function, we create a global variable MysqlClient, which shares a connection globally.
Common methods introduction
(30) Create a connection pool
(true) Print sql statements on the console
().SetLevel(log.LOG_DEBUG) Print debugging and above information on the console
({}) Write logs to files, or special log collection system.
3. MySQL production struct
Use the official reverse tool/xorm/reverseFor example, we have a table now
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `status` smallint(6) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB
We use reverse -f sql_reverse.yaml to generate struct
The code of sql_reverse.yaml is as follows:
# Step 1: Install go get /reverse# Step 2: Modify the tables of conn_str and include_tables in the above file# Step 3: Execute reverse -f sql_reverse.yaml
kind: reverse
name: mydb
source:
database: mysql
conn_str: 'user:pasword@tcp(localhost:3306)/test?charset=utf8mb4'
targets:
- type: codes
language: golang
include_tables: # The structure of the table that needs to be generated - user //The indication we want to produce multiple_files: true
output_dir: ./internal/domain/flow/repository/po
template: |
package po
{{$ilen := len .Imports}}
{{if gt $ilen 0}}
import (
{{range .Imports}}"{{.}}"{{end}}
)
{{end}}
{{range .Tables}}
type {{TableMapper .Name}} struct {
{{$table := .}}
{{range .ColumnsSeq}}{{$col := $ .}} {{ColumnMapper $}} {{Type $col}} `{{Tag $table $col}}`
{{end}}
}
func (m *{{TableMapper .Name}}) TableName() string {
return "{{$}}"
}
{{end}}
template_path: ./template/ # template path for code file, it has higher perior than template field on languageGenerate the corresponding go code as
type User struct {
Id int64 `json:"id" xorm:"'id' int(11) not null pk autoincr "`
Name string `json:"name" xorm:"'name' varchar(45) null "`
Status int64 `json:"status" xorm:"'status' smallint(3) null "`
}
func (m *User) TableName() string {
return "user"
}4. Commonly used xorm operation examples-insert data
4.1 insert Add 1 piece of data
Add an object.
engine, _, _ := NewMysqlClient()
u := User{
Name: "hisheng",
Status: 0,
}
affected, err := (&u)
(affected, err)4.2 insert adds multiple data
For multiple pieces of data, or the Insert method, the parameter is slice.
func TestInsertUsers(t *) {
engine, _, _ := NewMysqlClient()
us := []User{{Name: "hi", Status: 1}, {Name: "sheng", Status: 1}}
affected, err := (&us)
(affected, err)
}When inserting multiple data, the official suggests that we have less than 150 data and 150 extra data, it is easy to fail to insert.
4.3 insert adds data from different tables
user := new(User) = "myname" ... questions := make([]Question, 1) questions[0].Content = "whywhywhwy?" affected, err := (user, &questions)
5. Commonly used xorm operation examples--delete a row
5.1 Delete by id
user := new(User) affected, err := (id).Delete(user)
5.2 Delete by where condition
user := new(User)
affected, err := ("id = ?",1).Delete(user)5.3 Soft deletion, implement soft deletion through a certain field, such as status=0
This is updated
u := User{
Status: 0,
}
affected, err := ("id = ?",1).MustCols("status").Update(&u)
(affected, err)Note that here, a MustCols is added because xorm has values automatically updated by default, while status and default values are the same. The default values will be ignored and not updated. To update at this time, a MustCols field needs to be added.
6. Common xorm operation examples-update update operation
6.1 Pass the field to be updated with the parameters of the structure
func TestUpdate(t *) {
engine, _, _ := NewMysqlClient()
u := User{
Name: "1111",
}
affected, err := (1).Update(&u)
}The parameter that will update the field. The Name field will be updated here.
6.2 Use map as parameter to pass the field to be updated
Take map as the field to be updated, the delivery method, the format of map is map[string]interface{}
func TestUpdateMap(t *) {
engine, _, _ := NewMysqlClient()
u := map[string]interface{}{"name": "111"}
affected, err := (1).Update(u)
}6.3 The specified field is updated. If the unspecified field is not updated, the specified field will be updated even if it is 0.
We specify the value in the structure that needs to be updated by adding the Cols function.
affected, err := (id).Cols("age").Update(&user)6.4 Specify the field to be updated, and the unspecified updated based on whether the value is a value.
Sometimes you want to specify that certain fields must be updated, and other fields are automatically judged based on the value, and you can use MustCols to combine Update to use.
affected, err := (id).MustCols("age").Update(&user)7. Commonly used xorm operation examples--query a single line
Use the Get method to query a single piece of data. When calling the Get method, a pointer to the corresponding structure needs to be passed. At the same time, the non-empty field in the structure will automatically become the query condition and the previous method conditions are combined together to query.
7.1 Obtain a single piece of data according to the primary key Id:
user := new(User) has, err := (id).Get(user)
7.2 Obtain a single piece of data according to where:
user := new(User)
has, err := ("name=?", "xlw").Get(user)7.3 Obtain a single piece of data based on non-empty data in the user organization:
user := &User{Id:1}
has, err := (user)8. Commonly used xorm operation examples-query multiple data
Use the Find method to query multiple pieces of data. The first parameter of the Find method is a slice pointer or a Map pointer, which is the result returned after the query. The second parameter is optional, which is the pointer to the query condition struct.
8.1 The result is a structure slice:
everyone := make([]User, 0) err := (&everyone)
8.2 The return result is structure map:
users := make(map[int64]User) err := (&users)
9. Common operation examples of xorm-FindAndCount pagination
FindAndCount returns the result as the structure slice and the total number:
everyone := make([]User, 0) count,err := (1,20).FindAndCount(&everyone)
10. Commonly used operations examples of xorm-transaction operations
func TestTransaction(t *) {
engine, _, _ := NewMysqlClient()
session := ()
defer ()
if err := (); err != nil {
(err)
return
}
if _, err := (&User{Name: "11", Status: 0}); err != nil {
(err)
_ = ()
return
}
_ = ()
}The above is a detailed explanation of how Go uses xorm to read mysql. For more information about Go's xorm to read mysql, please pay attention to my other related articles!