GORM basic operations

This is the 7th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.

An overview of

Fully functional ORM (Infinite Proximity)
(Has One, Has Many, Belongs To, Many To Many, polymorphism)
Hooks (before or after creating/saving/updating/deleting/finding)
preload
The transaction
Composite primary key
SQL generator
Automatic database migration
Custom Logs
Extensible, plug-ins can be written based on GORM callbacks
All features are covered by tests
Developer friendly

: : :

1 installation

go get -u github.com/jinzhu/gorm
Copy the code

2 Database Connection

More GORM database connection instructions

package main

import (
	"fmt"
	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/mysql"
)

var (
	// Define a global object db
	db *gorm.DB
	// Define database connection parameter values
	// User name to connect to the database
	userName string = "root"
	// Password to connect to the database
	password string = "admin"
	// Address to connect to the database
	ipAddress string = "127.0.0.1"
	// Port number to connect to the database
	port int = 3306
	// The name of the database to connect to
	dbName string = "go_test"
	// The encoding format of the connection database
	charset string = "utf8mb4"
	// To properly handle time. time, you need to add the parseTime parameter.
	parseTime string = "True"
	// Set the time position
	loc string = "Local"
)

func connectMysql(a) (err error) {
	/ / DSN = "root: admin @ TCP/go_test (127.0.0.1:3306)? charset=utf8mb4&parseTime=True&loc=Local"
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s? charset=%s&parseTime=%s&loc=%s", userName, password, ipAddress, port, dbName, charset, parseTime, loc)
	db, err = gorm.Open("mysql", dsn)
	iferr ! =nil {
		fmt.Println("Database connection error,err", err)
		return err
	}
	// Set the maximum number of idle connections
	db.DB().SetMaxIdleConns(10)
	// Set the maximum number of connections
	db.DB().SetMaxOpenConns(100)
	Struct (struct name); // Automatically generate database tables that indicate that the default is the plural form of struct names. For example: user - users
	// If you don't want the plural form, set:
	db.SingularTable(true)
	//defer db.Close()
	fmt.Println("Database connection successful!")
	return nil
}

func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return}}Copy the code

3 GORM model definition

3.1 Model concept description

::: TIP Model Concept ORM framework to operate the database requires a predefined model, the model can be understood as a data model, as a medium to operate the database.

The data read from the database is saved to a predefined model object, from which we can retrieve the desired data.
To insert data into the database, create a new model object first, and then save the desired data to the model object, and then save the model object to the database.

::: GorM model definition in Golang is implemented through struct, so we can use gorM library to implement mapping between struct types and mysql table data. Gorm is responsible for translating the read and write operations to the model into SQL statements, and gorM then converts the results returned by the database after executing SQL statements into the model objects we define.

3.2 GORM Model definition

Gorm model definition is mainly in struct type definition on the basis of the addition of field label description implementation, see a complete example below. Suppose you have a user table, the table structure is as follows

CREATE TABLE `user` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(255) NOT NULL,
   `age` int(11) DEFAULT NULL,
   `create_time` datetime NOT NULL.PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

The model is defined as follows

type User struct {
	Id  int   `gorm:"primary_key"` // Table field name: id, primary key
	Name string   `gorm:"not null"` // The model label name field cannot be empty
	Age  int     // Age field
    // Corresponds to create_time in the table and is not empty
	CreateTime  time.Time	`gorm:"column:create_time; not null"`
}
Copy the code

3.3 GORM Model Labels

From the above example, you can define the column name of a struct field with a tag definition syntax like GORm :”column:create_time”.

// tag definition section, multiple tag definitions can use semicolons (;) Space,
/ / such as ` gorm: "column: create_time; not null"`
`gorm`Tag syntax:'GORM :" Label definition "'
Copy the code

Common tags are as follows. For more tags, see the model definitions on the official website

The label	instructions	example
COLUMN	Specifies the column name	gorm:”column:create_time”
PRIMARY_KEY	Specify the primary key	gorm:”column:id; PRIMARY_KEY”
NOT NULL	Specifies the column as non-NULL	gorm:”not null”
–	Ignore the field	Gorm :”-” can ignore struct fields. Ignored fields do not participate in gorM read and write operations

4 Automatically create tables based on the GORM model

// Create an organization with the same structure as the database table
// Gorm uses tags to identify constraints in mysql
type User struct {
	Id  int   `gorm:"primary_key"`
	Name string   `gorm:"not null"`
	Age  int
	CreateTime  time.Time	`gorm:"column:create_time; not null"`
}
func main(a) {
       // See the second point above to connect to the database
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
	// Create the user table if there is no user in the database
	if! db.HasTable(&User{}) {CreateTable db.createtable; // CreateTable db.createtable
// You can also Set some additional table properties with db.set,
		if err := db.Set("gorm:table_options"."ENGINE=InnoDB DEFAULT CHARSET=utf8").CreateTable(&User{}).Error; err ! =nil {
			panic(err)
		}
	}
}
Copy the code

5 preparation for CRUD use

The following common code for CRUD is the database connection and structure declaration, as shown below

import (
	"fmt"
	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/mysql"
	"time"
)
var (
	// Define a global object db
	db *gorm.DB
	// Define database connection parameter values
	// User name to connect to the database
	userName string = "root"
	// Password to connect to the database
	password string = "admin"
	// Address to connect to the database
	ipAddress string = "127.0.0.1"
	// Port number to connect to the database
	port int = 3306
	// The name of the database to connect to
	dbName string = "go_test"
	// The encoding format of the connection database
	charset string = "utf8mb4"
	// To properly handle time. time, you need to add the parseTime parameter.
	parseTime string = "True"
	// Set the time position
	loc string = "Local"
)

func connectMysql(a) (err error) {
	/ / DSN = "root: admin @ TCP/go_test (127.0.0.1:3306)? charset=utf8mb4&parseTime=True&loc=Local"
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s? charset=%s&parseTime=%s&loc=%s", userName, password, ipAddress, port, dbName, charset, parseTime, loc)
	db, err = gorm.Open("mysql", dsn)
	iferr ! =nil {
		fmt.Println("Database connection error,err", err)
		return err
	}
	// Set the maximum number of idle connections
	db.DB().SetMaxIdleConns(10)
	// Set the maximum number of connections
	db.DB().SetMaxOpenConns(100)
	Struct (struct name); // Automatically generate database tables that indicate that the default is the plural form of struct names. For example: user - users
	// If you don't want the plural form, set:
	db.SingularTable(true)
	//defer db.Close()
	fmt.Println("Database connection successful!")
	return nil
}

// Create an organization with the same structure as the database table
// Gorm uses tags to identify constraints in mysql
type User struct {
	Id         int    `gorm:"primary_key"`
	Name       string `gorm:"not null"`
	Age        int
	CreateTime time.Time `gorm:"column:create_time; not null"`
}
Copy the code

6 Inserting Data

Please refer to the official website for more usage of Insert Data

// Insert data
func (user *User) InsertUser(a) (err error) {
	// The Table() function is used if you do not specify a global Table name, or if the Table name is different from the structure name
	// You can specify the table name yourself in SQL. Here is an example in which this function can be removed. Db. The Create (user) can also be
	createDb := db.Table("user").Create(user)
	err = createDb.Error
	iferr ! =nil {
		fmt.Println("New data error,err", err)
		return err
	}
	return nil
}
// Main function call
func main(a) {
    // Connect to the database
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
    // Define the user structure that needs to be added
	user := User{Name: "Bill", Age: 16, CreateTime: time.Now()}
    // Call the insert operation on the user table
	err = user.InsertUser()
	iferr ! =nil {
		fmt.Println("New data error,err", err)
	}
	// All data of the corresponding structure, such as id, will be returned after the addition
	fmt.Println("Add data successfully, add user body primary key is", user.Id)
}
Copy the code

7 Updating Data

Please refer to the official website for more usage

7.1 Updating Modified fields

If you only want to Update specified fields, use Update or Updates

// Update the user table
func (user *User) UpdateUser(a)(err error) {
// Note that the Model method must be used in conjunction with the Update method. Note that db.update (user) does not Update data in this way
// Set the primary key of the Update in Model (if there is no where specified, the default Update key is id), and set the Update value in Update
// If no ID value is specified in the Model and no WHERE condition is specified, the full table will be updated
	update :=db.Model(&user).Update(user)
	err = update.Error
	iferr ! =nil {
		fmt.Println("Error updating data,err", err)
		return err
	}
	return nil
}
func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
	user := User{Id: 1, Name: "Daisy",Age:28}
	err = user.UpdateUser()
	iferr ! =nil {
		fmt.Println("Error updating data,err", err)
	}
	fmt.Println("Updated data", user)
}
Copy the code

Examples of other update operations are given

func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
	// This update only uses the WHERE condition without specifying an ID in the Model
	// Equivalent to update user set name='hello' wehre age=10
	db.Model(&User{}).Where("AGE = ?".10).Update("name"."hello")
	Update user set name='hello' wehre age=10 and name='helloWorld'
	db.Model(&User{}).Where("AGE = ?".18).Where("name=?"."helloWorld").Update("name"."hello")
	Update user set name='helloWorld', age=18
	db.Model(&User{}).Updates(map[string]interface{} {"name": "helloWorld"."age": 18})
Copy the code

7.2 Update All Fields

Db.save (&user) updates all fields, even if you don’t assign values. Db.model (&user).update (user)

func (user *User) SaveUser(a)(err error) {
	//Save updates all fields, even if you don't assign values
	Db.model (&user).update (user)
	update :=db.Save(&user)
	err = update.Error
	iferr ! =nil {
		fmt.Println("Error updating data,err", err)
		return err
	}
	return nil
}
func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
	user := User{Id: 1, Name: "Crayon Shin."}
	UPDATE user SET name=' id ', age=0, create_time='0000-00-00 00:00:00' WHERE id=1;
	user.SaveUser()
}
Copy the code

7.3 Updating selected Fields

If you Omit anything and you don’t want to update it, Omit anything and Omit it. When your update parameters are structs, you only want to update certain fields using Select. Examples are as follows:

func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
	// Define the structure to be updated
	user := User{Id: 1, Name: "Crayon Shin.",Age:222,CreateTime:time.Now()}
	// The name field will not be modified during the update
    UPDATE user SET age=12,create_time= NOW() WHERE id=1;
	db.Model(&user).Omit("name").Update(&user)
	// Change only the name field
    UPDATE user SET name=' UPDATE user 'WHERE id=1;
	db.Model(&user).Select("name").Update(&user)
}
Copy the code

7.4 No Hooks updates

The above update operation automatically runs the BeforeUpdate,AfterUpdate methods of model, Update UpdatedAt timestamp in update to save its Associations, if you don’t want to call these methods, you can use UpdateColumn, UpdateColumns. For example, the following

func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
	// Define the structure to be updated
	user := User{Id: 1}
	// Update a single attribute, similar to 'Update'
    SQL UPDATE user SET name='hello' WHERE id = 1;
	db.Model(&user).UpdateColumn("name"."hello")
	// Update multiple properties, similar to 'Updates'
    SQL UPDATE user SET name='hello', age=18 WHERE id =1;
	db.Model(&user).UpdateColumns(User{Name: "hello", Age: 18})}Copy the code

7.5 Batch Update

Hooks do not run during batch updates

func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}

	UPDATE users SET name='hello', age=9000 WHERE id IN (1, 2);
	db.Table("user").Where("id IN (?) "And []int{1.2}).Updates(map[string]interface{} {"name": "hello"."age": 9000})

	Map [string]interface{}
	// UPDATE users SET name='hello', age=18;
	db.Model(User{}).Updates(User{Name: "hello", Age: 18})

	// Use 'RowsAffected' to get the total number of updates
	updateTotal := db.Model(User{}).Updates(User{Name: "hello", Age: 18}).RowsAffected
	fmt.Println("The number of records updated is",updateTotal)
}
Copy the code

7.6 Update using SQL Expressions

Gorm provides Expr functions to set expressions

func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
	// Define the structure to be updated
	user := User{Id: 1}
	// Calculate the age value before performing the update operation
	//UPDATE user SET age = age * '2' + '100' WHERE id = 1;
	db.Model(&user).Update("age", gorm.Expr("age * ? +?".2.100))

	//UPDATE user SET age = age * '2' + '100' WHERE id = 1;
	db.Model(&user).Updates(map[string]interface{} {"age": gorm.Expr("age * ? +?".2.100)})

	//UPDATE user SET age = age - 10 WHERE id = 1;
	db.Model(&user).UpdateColumn("age", gorm.Expr("age - ?".10))

	//UPDATE user SET age = age - 10 WHERE id = 1 and age>12;
	db.Model(&user).Where("age > 12").UpdateColumn("age", gorm.Expr("age - ?".10))}Copy the code

8 Deleting Data

8.1 Deleting Records

::: DANGER Warning When deleting records, please make sure that the primary key field has a value,GORM will delete records through the primary key, if the primary key is empty,GORM will delete all records of the model. : : :

func (user *User) DeleteUser(a) (err error) {
	//Save updates all fields, even if you don't assign values
	Db.model (&user).update (user)
	deleteUser := db.Delete(&user)
	err = deleteUser.Error
	iferr ! =nil {
		fmt.Println("Error deleting data,err", err)
		return err
	}
	return nil
}
func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
	// Define the structure to be deleted. The primary key field must have a value
	user := User{Id: 1}
	//delete from user where id=1;
	user.DeleteUser()
}
Copy the code

8.2 Batch Deleting

Delete all matched records

func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}

	// DELETE from user where name LIKE "% f % F %";
	db.Where("name LIKE ?"."% Fu Xiaolin %").Delete(User{})

	//DELETE from user where name LIKE "% f % F %";
	db.Delete(User{}, "name LIKE ?"."% Fu Xiaolin %")}Copy the code

8.3 Physical Deletion

func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
	// Define the structure to be deleted. The primary key field must have a value
	user := User{Id: 1}
	// The Unscoped method can physically delete records
	//DELETE FROM user WHERE id=1;
	db.Unscoped().Delete(&user)
}
Copy the code

9 Querying Data

::: tip GorM query description

gormQuerying data essentially provides a set of functions to help us quickly concatenatesqlStatement, minimize writingsqlStatement workload.
gormWe generally save the query results to the structure(struct)Variable, so you need to define the structure type based on the data you want to query before performing the query operation.
gormThe library is coroutine safe,gormProvides functions that can be safely executed concurrently across multiple coroutines.

: : :

9.1 Query Methods

For more usage, refer to the query method on the official website. The following is an example

Operation meaning	Golang sample	Equivalent SQL
Query the first record based on the primary key	db.First(&user)	SELECT * FROM user ORDER BY id LIMIT 1;
Get a random record	db.Take(&user)	SELECT * FROM user LIMIT 1;
Queries the last record by primary key	db.Last(&user)	SELECT * FROM user ORDER BY id DESC LIMIT 1;
Query all records	db.Find(&users)	SELECT * FROM user
To query a specified record, run the following command`The primary key is an integer`When available	db.First(&user, 10)	SELECT * FROM user WHERE id = 10;

func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
	// Define the structure variable to receive query results
	user := User{}

	// define a struct slice that receives the results of a query for all tables
	var users []User
	SELECT * FROM user ORDER BY id LIMIT 1;
	db.First(&user)

	SELECT * FROM user LIMIT 1;
	db.Take(&user)

	SELECT * FROM user ORDER BY id DESC LIMIT 1;
	db.Last(&user)

	SELECT * FROM user
	db.Find(&users)

	// Query a specified record (only available if the primary key is an integer)
	// SELECT * FROM user WHERE id = 3;
	db.First(&user, 3)

	fmt.Println("The single data queried is",user)
	db.Find(&users, )
	for _, v := range users {
		fmt.Println(v)
	}
}
Copy the code

9.2 Query Error Handling

The db.Error attribute is used to judge whether the query result is wrong. If the Error attribute is not equal to nil, it means that an Error has occurred. The following

iferr := db.Take(&user).Error; err ! =nil {
    fmt.Println("Query failed", err)
}
Copy the code

9.2.1 Error Exceptions

Gorm will also treat it as an error if the data cannot be queried. If the data cannot be queried, the above example will print record not found

The gorM library provides the following two ways to check if an Error is not found

9.2.2 gorm. IsRecordNotFoundError

err := db.Take(&user).Error
if gorm.IsRecordNotFoundError(err) {
    fmt.Println("Data not available")}else iferr ! =nil {
// If err does not equal record not found and nil, the SQL execution failed.
	fmt.Println("Query failed", err)
}
Copy the code

9.2.3 db. RecordNotFound

// check the result of the query
if db.Take(&user).RecordNotFound {
    fmt.Println("Data not available")}else iferr ! =nil {
 // If err does not equal record not found and nil, the SQL execution failed.
 	fmt.Println("Query failed", err)
 }
Copy the code

9.3 Where Condition Query

None of the above examples specify a WHERE condition. Here’s how to set a where condition, using the db. where function. The function is described as follows:

 db.Where(query interface{}, args ...interface{})
Copy the code

Parameter Description:

Parameter names	instructions
query	SQL statement where clause where clause uses question mark (?) Instead of the parameter value, it means that the parameter is bound by the args parameter
args	The where clause binds more than one parameter

func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
	// Define the structure variable to receive query results
	user := User{}
	// define a struct slice that receives the results of a query for all tables
	var users []User

	// Get the first matching data
	// SELECT * FROM user WHERE name = 'hello' limit 1;
	db.Where("name = ?"."hello").First(&user)

	// Get all matched data. The receiver is a slice of the corresponding type []User
	// SELECT * FROM user WHERE name = 'hello';
	db.Where("name = ?"."hello").Find(&users)

	// SELECT * FROM user WHERE name <> 'hello';
	db.Where("name <> ?"."hello").Find(&users)

	// Concatenate IN conditional SQL statements
	// SELECT * FROM user WHERE name in ('hello','helloWorld');
	db.Where("name IN (?) "And []string{"hello"."helloWorld"}).Find(&users)

	// splice LIKE fuzzy query
	// SELECT * FROM users WHERE name LIKE '%hel%';
	db.Where("name LIKE ?"."%hel%").Find(&users)

	// AND conditional splice
	// SELECT * FROM user WHERE name = 'hello' AND age >= 22;
	db.Where("name = ? AND age >= ?"."hello"."22").Find(&users)

	/ / Time conditions
	// SELECT * FROM users WHERE create_time > '2020-01-01 00:00:00';
	db.Where("create_time < ?", time.Now()).Find(&users)

	// BETWEEN
	// SELECT * FROM user WHERE age BETWEEN 10 AND 20;
	db.Where("age BETWEEN ? AND ?".10.20).Find(&users)

    Struct Struct SELECT * FROM user WHERE name = "xiaoxin" AND age = 20 LIMIT 1;
    db.Where(&User{Name: "xiaoxin", Age: 20}).First(&user)

    SELECT * FROM user WHERE name = "jinzhu" AND age = 20;
    db.Where(map[string]interface{} {"name": "xiaoxin"."age": 20}).Find(&users)

    SELECT * FROM user WHERE id IN (20, 21, 22);
    db.Where([]int64{20.21.22}).Find(&users)
}
Copy the code

Tip: When querying through a structure,GORMWill only be queried by non-zero value fields, which means if your field value is0, ",false or any other zero valueWill not be used to build query criteria. For example,

// SELECT * FROM users WHERE name = "xiaoxin";
db.Where(&User{Name: "xiaoxin", Age: 0}).Find(&users)
Copy the code

You can use Pointers or implementations Scanner/ValuerInterfaces to avoid this problem

// Use Pointers
type User struct {
    Id         int    `gorm:"primary_key"`
	Name       string `gorm:"not null"`
	Age        *int
	CreateTime time.Time `gorm:"column:create_time; not null"`
}

/ / using Scanner/Valuer
type User struct {
    Id         int    `gorm:"primary_key"`
  	Name       string `gorm:"not null"`
  	Age        sql.NullInt64  NullInt64 implements the Scanner/Valuer interface
  	CreateTime time.Time `gorm:"column:create_time; not null"`
}
Copy the code

9.4 Not condition

This function is similar to Where, for example

func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
	// Define the structure variable to receive query results
	user := User{}
	// define a struct slice that receives the results of a query for all tables
	var users []User

	// SELECT * FROM user WHERE name <> "xiaoxin" LIMIT 1;
	db.Not("name"."xiaoxin").First(&user)

	SQL: SELECT * FROM user WHERE name Not In ("hello", "hello2");
	db.Not("name"And []string{"hello"."hello2"}).Find(&users)

	// Not In Slice of primary key
	// SELECT * FROM user WHERE id NOT IN (1,2,3) LIMIT 1;
	db.Not([]int64{1.2.3}).First(&user)

	// SELECT * FROM user LIMIT 1;
	db.Not([]int64{}).First(&user)

	// SELECT * FROM user WHERE NOT(name = 1) LIMIT 1;
	db.Not("name = ?"."Zhang").First(&user)

	// Struct Struct SELECT * FROM user WHERE name <> LIMIT 1;
	db.Not(User{Name: "Zhang"}).First(&user)
	
	fmt.Println(user)
	fmt.Println(users)
}
Copy the code

9.5 the Or conditions

func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
	// define a struct slice that receives the results of a query for all tables
	var users []User

	// SELECT * FROM user WHERE name = 'js_' OR name = 'js_ ';
	db.Where("name = ?"."Zhang").Or("name = ?"."Bill").Find(&users)

	Struct Struct Struct Struct Struct Struct Struct Struct Struct Struct Struct Struct Struct Struct Struct Struct Struct;
	db.Where("Name = 'name '").Or(User{Name: "Bill"}).Find(&users)

	// Map type SELECT * FROM user WHERE name = 'zhang3' OR name = 'wang4 ';
	db.Where("Name = 'name '").Or(map[string]interface{} {"name": "Bill"}).Find(&users)

	fmt.Println(users)
}
Copy the code

The select clause 9.6

Select, which specifies the fields you want to retrieve from the database. The default is all fields. For example, the following

func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
	// define a struct slice that receives the results of a query for all tables
	var users []User

	// Query the specified fields, other fields are zero for each type
	// SELECT name, age FROM user;
	db.Select("name, age").Find(&users)

	// Query the specified field and add the WHERE condition
	// SELECT name, age FROM user WHERE age=12;
	db.Select("name, age").Find(&users,"age=?".12)

	// SELECT name, create_time FROM user;
	db.Select([]string{"name"."create_time"}).Find(&users)

	SELECT COALESCE(age,'42') FROM user;
	db.Table("user").Select("COALESCE(age,?) ".42).Rows()
}
Copy the code

9.7 the order sorting

Set the sort statement,order by clause. Specifies the order in which records are retrieved from the database. Setting the second parameter, reorder, to true, overrides the sorting condition defined earlier.

func (s *DB) Order(value interface{}, reorder ...bool) *DB {
	returns.clone().search.Order(value, reorder...) .db }Copy the code

func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
	// Define the structure variable to receive query results
	// define a struct slice that receives the results of a query for all tables
	var users []User
	var users1 []User
	var users2 []User

	// SELECT * FROM user ORDER BY age desc;
	db.Order("age desc").Find(&users)

	SELECT * FROM user ORDER BY age desc, name;
	db.Order("age desc").Order("name").Find(&users)

	// override sort
	//// SELECT * FROM user ORDER BY age desc; (users1)
	//// SELECT * FROM user ORDER BY age; (users2)
	db.Order("age desc").Find(&users1).Order("age".true).Find(&users2)
	//[{1 220-02-05 16:37:58 +0800 CST} {2 220-02-05 16:37:58 +0800 CST} {2 220-02-05 16:37:58 +0800 CST} {2 220-02-05 16:37:58 +0800 CST CST} {1 220-02-05 16:37:58 +0800 CST}]
	fmt.Println(users1)
	[{1 2020-02-05 16:37:58 +0800 CST} {2 2020-02-05 16:37:58 +0800 CST} {4 Zhao 6 56 2020-02-05 16:37:58 +0800 CST} {5 Zhao 6 56 2020-02-05 16:37:58 +0800 CST CST} {5 520-02-05 16:37:58 +0800 CST}]
	fmt.Println(users2)
}
Copy the code

9.8 Limit & Count

Limit specifies the maximum number of records to retrieve from the database. Count Total number of records that the model can obtain.

func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
	// define a struct slice that receives the results of a query for all tables
	var users []User
	var users1 []User
	var users2 []User
	var count int 

	// SELECT * FROM user LIMIT 3;
	db.Limit(3).Find(&users)

	// -1 cancels the Limit condition
	// SELECT * FROM user LIMIT 10; (users1)
	// SELECT * FROM user; (users2)
	db.Limit(10).Find(&users1).Limit(- 1).Find(&users2)

	// SELECT * from user WHERE name = 'js_' OR name = 'js_ '; (users)
	// SELECT count(*) FROM user WHERE count(*) = 1; (count)
	db.Where("name = ?"."Zhang").Or("name = ?"."Bill").Find(&users).Count(&count)

	// SELECT count(*) FROM user WHERE count(*) = 1; (count)
	db.Model(&User{}).Where("name = ?"."Zhang").Count(&count)

	SELECT count(*) FROM user;
	db.Table("user").Count(&count)

	SELECT count(distinct(name)) FROM user; (count)
	db.Table("user").Select("count(distinct(name))").Count(&count)
}
Copy the code

9.9 Group & Having

::: tip Be careful

GroupFunctions must be collocatedSelectFunction together
The following examplescansimilarFindBoth are used to execute a query and then assign the query result to a struct variablescanThe table name is not extracted from the structure variable passed in.
The following example redefines a structure to hold the results, but it has no bindinguserTable, so it can only be used herescanQuery function.

: : :

func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
	// Set a Result structure type to hold query results
	type Result struct {
		Date  time.Time
		Total int
	}
	// SELECT date(create_time) as date, sum(age) as total FROM user GROUP BY date(create_time)
	rows, err := db.Table("user").Select("date(create_time) as date, sum(age) as total").Group("date(create_time)").Rows()
	for rows.Next() {
		var result Result
		err := rows.Scan(&result.Date,&result.Total)
		iferr ! =nil{
			fmt.Println("Err scan fail,",err)
			return
		}
		fmt.Println(result)
		//{2020-02-01 00:00:00 +0800 CST 24}
		//{2020-02-05 00:00:00 +0800 CST 146}
	}

	//SELECT date(create_time) as date, sum(age) as total FROM user GROUP BY date(create_time) HAVING (sum(age)>100)
	rows, err = db.Table("user").Select("date(create_time) as date, sum(age) as total").Group("date(create_time)").Having("sum(age) > ?".100).Rows()
	for rows.Next() {
		/ /... For details, see lines 14 to 24
	}
	// The following two ways are similar
	// Scan, like Find, is used to execute a query and then assign the query result to a structure variable. The difference is that scan does not extract table names from the passed structure variable.
	// Here we have redefined a structure to hold the results, but this structure is not bound to the user table, so we can only use the scan query function.
	var results []Result
	//SELECT date(create_time) as date, sum(age) as total FROM user GROUP BY date(create_time) HAVING (sum(age)>100)
	db.Model(User{}).Select("date(create_time) as date, sum(age) as total").Group("date(create_time)").Having("sum(age) > ?".100).Scan(&results)
	fmt.Println(results) //[{2020-02-01 00:00:00 +0800 CST 24}]

	db.Table("user").Select("date(create_time) as date, sum(age) as total").Group("date(create_time)").Having("sum(age) > ?".100).Scan(&results)
	fmt.Println(results) //[{2020-02-01 00:00:00 +0800 CST 24}]
}
Copy the code

9.10 connect the join

Joins, specifying connection conditions

func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
	type Result struct {
		Name  string
		Email string
	}

	//SELECT users.name, emails.email FROM users LEFT JOIN emails on emails.user_id = users.id
	rows, err := db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Rows()
	for rows.Next() {
		/ /...
	}
	var results []Result
	//SELECT users.name, emails.email FROM users LEFT JOIN emails on emails.user_id = users.id
	db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&results)

	var user User
	// Multiple connections and parameters
	db.Joins("JOIN emails ON emails.user_id = users.id AND emails.email = ?"."[email protected]").Joins("JOIN credit_cards ON credit_cards.user_id = users.id").Where("credit_cards.number = ?"."411111111111").Find(&user)
}
Copy the code

9.11 Executing SQL Statements directly

For complex queries, such as multi-table join queries, we can simply write and execute SQL statements.

Gorm sets SQL statements using db.Raw and performs queries using Scan

func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
	type Result struct {
		Name  string
		Age   int
		Total int
	}
	var result []Result
	db.Table("user").Select("name, age").Where("name = ?"."Daisy").Scan(&result)

	/ / the native SQL
	db.Raw("SELECT name, age FROM user WHERE name = ?"."Daisy").Scan(&result)
	sqlStr := "SELECT name , COUNT(*) AS total FROM user WHERE create_time>? GROUP BY name HAVING(total >0)"

	// Because the SQL statement uses a question mark (?) As a binding parameter, so you need to pass a binding parameter (Raw second parameter).
	// The Raw function supports binding multiple arguments
	db.Raw(sqlStr, "The 2019-01-01 00:00:00").Scan(&result)
	fmt.Println(result)
}
Copy the code

10 SQL Generator

10.1 Executing native SQL

Chaining operations with other methods are not supported when executing native SQL. For more usage, please refer to the SQL Generator website

func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}

	Db.exec ()
	db.Exec("DROP TABLE user;")
	db.Exec("UPDATE user SET create_time=? WHERE id IN (?) ", time.Now(), []int64{11.22.33})

	// Scan
	type Result struct {
		Name string
		Age  int
	}

	// Query using db.raw ()
	var result[] Result
	db.Raw("SELECT name, age FROM user WHERE id > ?".3).Scan(&result)
	fmt.Println(result)
}
Copy the code

10.2 SQL. Row and SQL. Rows

Row or sql.Rows to obtain the query result

func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
	var user User
	row := db.Table("user").Where("name = ?"."Zhang").Select("name, age").Row() // (*sql.Row)
	row.Scan(&user.Name, &user.Age)

	rows, err := db.Model(&User{}).Where("name = ?"."Zhang").Select("name, age").Rows() // (*sql.Rows, error)
	defer rows.Close()
	for rows.Next() {
		var u User
		rows.Scan(&u.Name, &u.Age)
		fmt.Println(u.Name,u.Age)
	}

	/ / the native SQL
	rows, err = db.Raw("select name, age from user where name = ?"."Zhang").Rows() // (*sql.Rows, error)
	defer rows.Close()
	for rows.Next() {
		var u User
		rows.Scan(&u.Name, &u.Age)
		fmt.Println(u.Name,u.Age)
	}
}
Copy the code

10.3 Scanning SQl. Rows into Model

func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
	rows, err := db.Model(&User{}).Where("name = ?"."Zhang").Select("name, age").Rows() // (*sql.Rows, error)
	defer rows.Close()

	for rows.Next() {
		var user User
		// ScanRows scans a row to user
		db.ScanRows(rows, &user)
		// do something
		fmt.Println(user.Name,user.Age)
	}
}
Copy the code

11 Transaction Processing

By default, GORM encapsulates individual CREATE, UPDATE, and DELETE operations within a transaction to ensure data integrity.

If you want to treat multiple CREATE, UPDATE, and DELETE operations as one atomic operation,Transaction is used to do this.

Transactions are only supported in MySQL for databases or tables that use the Innodb database engine

11.1 Transaction Process Description

func CreateUsers(db *gorm.DB) error {
  return db.Transaction(func(tx *gorm.DB) error {
    // Do some database operations in a transaction ('tx' should be used here, not 'db')
    if err := tx.Create(&User{Name: "zhangsan"}).Error; err ! =nil {
      // If any err is returned, the entire transaction will rollback
      return err
    }

    if err := tx.Create(&User{Name: "lisi"}).Error; err ! =nil {
      return err
    }

    // Return nil commit transaction
    return nil})}Copy the code

11.2 Transactions (Manual Control)

// Start the transaction
tx := db.Begin()

// Do some database operations in a transaction ('tx' should be used here, not 'db')
tx.Create(...)

// ...

// Manually invoke transaction Rollback() if there is an error
tx.Rollback()

// Call the transaction Commit() manually if there is no error
tx.Commit()
Copy the code

11.3 Transaction Example

package main

import (
	"fmt"
	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/mysql"
	"time"
)

var (
	// Define a global object db
	db *gorm.DB
	// Define database connection parameter values
	// User name to connect to the database
	userName string = "root"
	// Password to connect to the database
	password string = "admin"
	// Address to connect to the database
	ipAddress string = "127.0.0.1"
	// Port number to connect to the database
	port int = 3306
	// The name of the database to connect to
	dbName string = "go_test"
	// The encoding format of the connection database
	charset string = "utf8mb4"
	// To properly handle time. time, you need to add the parseTime parameter.
	parseTime string = "True"
	// Set the time position
	loc string = "Local"
)

func connectMysql(a) (err error) {
	/ / DSN = "root: admin @ TCP/go_test (127.0.0.1:3306)? charset=utf8mb4&parseTime=True&loc=Local"
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s? charset=%s&parseTime=%s&loc=%s", userName, password, ipAddress, port, dbName, charset, parseTime, loc)
	db, err = gorm.Open("mysql", dsn)
	iferr ! =nil {
		fmt.Println("Database connection error,err", err)
		return err
	}
	// Set the maximum number of idle connections
	db.DB().SetMaxIdleConns(10)
	// Set the maximum number of connections
	db.DB().SetMaxOpenConns(100)
	Struct (struct name); // Automatically generate database tables that indicate that the default is the plural form of struct names. For example: user - users
	// If you don't want the plural form, set:
	db.SingularTable(true)
	//defer db.Close()
	fmt.Println("Database connection successful!")
	return nil
}

// Create an organization with the same structure as the database table
// Gorm uses tags to identify constraints in mysql
type User struct {
	Id         int    `gorm:"primary_key"`
	Name       string `gorm:"not null"`
	Age        int
	CreateTime time.Time `gorm:"column:create_time; not null"`
}

//CreateUsers Batch insert user data
// If an error is reported, the previous operation is rolled back
func CreateUsers(a) error {
	// Note that once the transaction starts, you should use tx as the database handle
	tx := db.Begin()
	defer func(a) {
		if r := recover(a); r ! =nil {
			tx.Rollback()
		}
	}()

	iferr := tx.Error; err ! =nil {
		return err
	}

	if err := tx.Create(&User{Name: "zhangsan",Age:17}).Error; err ! =nil {
		tx.Rollback()
		return err
	}

	if err := tx.Create(&User{Name: "lisi",Age:38}).Error; err ! =nil {
		tx.Rollback()
		return err
	}

	return tx.Commit().Error
}
func main(a) {
	err := connectMysql()
	iferr ! =nil {
		fmt.Println("Failed to initialize database,err", err)
		return
	}
	err = CreateUsers()
	iferr ! =nil {
		fmt.Println("Transaction operation failed,err", err)
		return}}Copy the code

12 Frequently Asked Questions

12.1 GORM time format Problem

Gorm time format with time zone is not suitable for our usual use of 2019-01-01 12:22:22 format, we need to rewrite some methods of timeTime, the code is as follows

package models

import (
	"database/sql/driver"
	"errors"
	"fmt"
	"strings"
	"time"
)

//BaseModel Infrastructure information
type BaseModel struct {
	CreateTime MyTime 'gorm:"comment:' create time '; type:timestamp;" ; json:"createTime"`
	UpdateTime MyTime 'gorm:"comment:' update time '; type:timestamp;" ; json:"updateTime"`
	Remark     string ` gorm: "the comment: 'remarks'"; json:"remark"`
}
//MyTime Specifies the time
type MyTime time.Time

func (t *MyTime) UnmarshalJSON(data []byte) error {
	if string(data) == "null" {
		return nil
	}
	var err error
	// The time string received by the front-end
	str := string(data)
	// Remove unwanted end of received STR"
	timeStr := strings.Trim(str, "\" ")
	t1, err := time.Parse("The 2006-01-02 15:04:05", timeStr)
	*t = MyTime(t1)
	return err
}

func (t MyTime) MarshalJSON(a) ([]byte, error) {
	formatted := fmt.Sprintf("\"%v\"", time.Time(t).Format("The 2006-01-02 15:04:05"))
	return []byte(formatted), nil
}

func (t MyTime) Value(a) (driver.Value, error) {
	// MyTime is converted to time. time
	tTime := time.Time(t)
	return tTime.Format("The 2006-01-02 15:04:05"), nil
}

func (t *MyTime) Scan(v interface{}) error {
	switch vt := v.(type) {
	case time.Time:
		// The string is converted to time. time
		*t = MyTime(vt)
	default:
		return errors.New("Type handling error")}return nil
}

func (t *MyTime) String(a) string {
	return fmt.Sprintf("hhh:%s", time.Time(*t).String())
}
Copy the code

13 GORM for more information

GORM Chinese document

An overview of

1 installation

2 Database Connection

3 GORM model definition

3.1 Model concept description

3.2 GORM Model definition

3.3 GORM Model Labels

4 Automatically create tables based on the GORM model

5 preparation for CRUD use

6 Inserting Data

7 Updating Data

7.1 Updating Modified fields

7.2 Update All Fields

7.3 Updating selected Fields

7.4 No Hooks updates

7.5 Batch Update

7.6 Update using SQL Expressions

8 Deleting Data

8.1 Deleting Records

8.2 Batch Deleting

8.3 Physical Deletion

9 Querying Data

9.1 Query Methods

9.2 Query Error Handling

9.2.1 Error Exceptions

9.2.2 gorm. IsRecordNotFoundError

9.2.3 db. RecordNotFound

9.3 Where Condition Query

9.4 Not condition

9.5 the Or conditions

The select clause 9.6

9.7 the order sorting

9.8 Limit & Count

9.9 Group & Having

9.10 connect the join

9.11 Executing SQL Statements directly

10 SQL Generator

10.1 Executing native SQL

10.2 SQL. Row and SQL. Rows

10.3 Scanning SQl. Rows into Model

11 Transaction Processing

11.1 Transaction Process Description

11.2 Transactions (Manual Control)

11.3 Transaction Example

12 Frequently Asked Questions

12.1 GORM time format Problem

13 GORM for more information

Related Posts

Java using Aviator expression learning record (28)

Java background programmer to Android “3” integration of Tencent Yunyun Live broadcast push stream end, accept the display end, background server end and pit mining records

Concurrent programming Voliatile