This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money

Writing in the front

I am just a beginner of Go language, this article is just a summary of what I often use. You are advised to go to GORM’s Chinese documentation for details. Of course, this post will be updated as well, documenting my CURD polishing process

Mind mapping

@TOC

1. Introduction

• A Go language development ORM library, isbyteWrote by a great man of China!! Domestic frame!
• Download:go get -u github.com/jinzhu/gorm

Process:

1. Import Gorm
2. Importing the Database driver
3. Defining the database model
4. Connecting to the database
5. Field migration mapping
6. Add, delete, change, search
7. Closing a database connection

2. Database driver

The database	drive	The import
mysql	github.com/go-sql-driver/mysql	github.com/jinzhu/gorm/dialects/mysql
postgresql	github.com/lib/pq	github.com/jinzhu/gorm/dialects/postgres
sqlite	github.com/mattn/go-sqlite3	github.com/jinzhu/gorm/dialects/sqlite
sqlserver		github.com/jinzhu/gorm/dialects/mssql

MySQL > connect to MySQL

Configuring the INI File

[mysql]
Db = mysql
DbHost = 127.0.0.1
DbPort = 3306
DbUser = root
DbPassWord = root
DbName = carsys
Copy the code

Reading configuration Files

	file, err := ini.Load("./conf/config.ini")
	iferr ! =nil {
		fmt.Println(Configuration file reading error, please check file path:, err)
	}
	LoadMysqlData(file)
Copy the code

Database path stitching

path := strings.Join([]string{DbUser, ":", DbPassWord, "@tcp(", DbHost, ":", DbPort, "/", DbName, "? charset=utf8&parseTime=true"}, "")  // Path stitching
model.Database(path)  	// Connect to the database
Copy the code

Connect to database, this is in the model package, so the following operations are model.db

var DB *gorm.DB  // Global database entry

func Database(connString string) {
	db, err := gorm.Open("mysql", connString)
	db.LogMode(true)
	iferr ! =nil {
		panic(err)
	}
	if gin.Mode() == "release" {
		db.LogMode(false)
	}
	db.SingularTable(true)         // The default is not to add the complex number s, otherwise it will add and s after each data, such as user to become users
	db.DB().SetMaxIdleConns(20)  	// Set the connection pool to idle
	db.DB().SetMaxOpenConns(100) 	// Set the maximum connection to open
	db.DB().SetConnMaxLifetime(time.Second * 30)
	DB = db
	migration() // Model migration maps to the database
}
Copy the code

4. Migrate (MySQL as example)

Automatically migrate the database to the latest version, most add columns and indexes, do not change the type and delete columns

4.1 Building database tables

The gorm.Model automatically adds create_time, update_time, delete_time.

Check the source code insidegorminmodel.

Then we can just define a struct model like this, ready to map to the database, without having to write an SQL statement. You can put a tag like gorm:”unique” to indicate that this is unique.

import "github.com/jinzhu/gorm"

type User struct {
	gorm.Model
	UserName       string `gorm:"unique"` 
	Email          string 
	PasswordDigest string
	Nickname       string `gorm:"unique"`
	Status         string
	Limit          int   // 0 Non-administrator 1 Administrator
	Type           int    // 0 indicates the user. 1 indicates the merchant
	Avatar         string `gorm:"size:1000"`
	Monery 		    int
}
Copy the code

4.2 Database model migration

Gorm provides an AutoMigrate method that makes mapping migration easy.

func migration(a) {
	Automatic migration mode
	DB.Set("gorm:table_options"."charset=utf8mb4").
		AutoMigrate(&User{})
}
Copy the code

4.3 the tags bound

4.3.1 one-to-many

Semantic scenario: The relationship between comments and posts. A post has many comments, but this comment can only be under the post.

Therefore, foreignkey binding can be carried out through foreignkey. Foreign keys to the Social model. The relationship id is social_id.

type Comment struct {
	gorm.Model
	Content    string
	ParentId   uint   	// Parent comment ID
	UserID     uint   	/ / user ID
	ReplyName  string 	// The name of the responder
	UserName   string
	UserAvatar string
	SocialId   uint  `gorm:"foreignkey:Social; association_jointable_foreignkey:social_id"` // Community post ID
	Children   []Comment `gorm:"foreignkey:ParentId"`
}
Copy the code

4.3.2 many-to-many

Semantic context: A user can have multiple friends and vice versa. So the user table itself is many-to-many.

Using tags is many2many

type User struct {
	gorm.Model
	Relations 	   []User `gorm:"many2many:relation; association_jointable_foreignkey:relation_id"`
	UserName       string
	Email          string 	//`gorm:"unique"`
	Avatar         string 	`gorm:"size:1000"`
	Phone 		   string
	BanTime 	   int
	OpenID 		   string 	`gorm:"unique"`
}
Copy the code

Such a many-to-many association creates one more table to store the relationship between the two.

---

5. Add

5.1 the one-to-one

The create function creates it.

category := model.Category{
		CategoryName: service.CategoryName,
		EnglishName:service.EnglishName,
}
err := model.DB.Create(&category).Error
Copy the code

5.2 a one-to-many

Semantic context: Here is a comment table, and socialId is a post, one post for multiple comments, in the same way that one-to-one is created

	var comment model.Comment
	comment = model.Comment{
		UserID:    user.ID,
		Content:   service.Content,
		ParentId:  service.ParentId,
		UserName:  user.UserName,
		UserAvatar:  user.Avatar,
		SocialId: service.SocialID,
	}
	err := model.DB.Create(&comment).Error
Copy the code

5.3 many-to-many

Semantic environment: user tables, many-to-many relationships between users

type User struct {
	gorm.Model
	Relations 	   []User `gorm:"many2many:relation; association_jointable_foreignkey:relation_id"`
	UserName       string
	Email          string 	//`gorm:"unique"`
	Avatar         string 	`gorm:"size:1000"`
	Phone 		   string
	BanTime 	   int
	OpenID 		   string 	`gorm:"unique"`
}
Copy the code

Select two elements in the user table. Then do the associative binding.

	var user model.User
	var friend model.User
	model.DB.Model(&friend).Where(`id = ? `,id).First(&friend)  			// The follower
	model.DB.Model(&user).Where(`id = ? `,userId).First(&user)			/ / followers
	err := model.DB.Model(&user).Association(`Relations`).Append([]model.User{friend}).Error
Copy the code

This statement associates the user with the friend and stores it in the Relations table.

---

6. Delete

6.1 the one-to-one

Delete in GORM is a soft delete, that is, although it is deleted, but not completely deleted, but retained in the data, but the query does not query the deleted statement.

• Single record deletion

Let’s find this piece of data

	var social model.Society
	code := e.Success
	err := model.DB.First(&social, id).Error // Find social by id.
Copy the code

Then delete it

	err = model.DB.Delete(&social).Error
Copy the code

• Batch delete

 db.Where("user_name like ? "."%Fan%").Delete(User{})
Copy the code

• Permanent delete: delete the database directly, not soft delete

db.Unscoped().Where("name Like ?"."% 6%").Delete(User{})
Copy the code

6.2 a one-to-many

One on one is a special one on many.

Delete and one-to-one are actually the same operation.

	var social model.Society
	model.DB.First(&social, id)  // Find this thread
	model.DB.Delete(&social)   // Delete it
Copy the code

6.3 many-to-many

Find the relationship between the two and delete the association. It will delete the relationship between the two from the Relations table.

	var user model.User
	var friend []model.User
	model.DB.Model(&friend).Where(`id = ? `,id).First(&friend)  			// The follower
	model.DB.Model(&user).Where(`id = ? `,userId).First(&user)	/ / followers
	err := model.DB.Model(&user).Association(`Relations`).Delete(friend).Error
Copy the code

---

7. Change

7.1 the one-to-one

• Save

All fields are saved, even if the field is zero

social := model.Society{
		CategoryID:    service.CategoryID,
		Title:         service.Title,
		Content:       service.Content,
		Picture:       service.Picture,
		CategoryName:  service.CategoryName,
	}
err := model.DB.Save(&social).Error
Copy the code

• Update

When updating a single column using Update, conditions need to be specified, otherwise the ErrMissingWhereClause error is returned.

model.DB.Model(model.User{}).Where("open_id=?", openid).Update("phone", phone)
Copy the code

• Updates

Batch update, only the specified field will be updated, but if the value is null, it will not update the null value.

Only specified fields are updated

model.DB.Table("user").Where("user_name = ?"."FanOne").
Updates(map[string]interface{} {"phone":"10001"."email":"[email protected]"})
Copy the code

Update only fields with changed and non-zero values

model.DB.Model(&User{}).Where("user_name = ?"."FanOne").
Updates(User{Phone:"10001",Email:"[email protected]"})
Copy the code

7.2 a one-to-many

Update fields directly, same as above.

7.3 many-to-many

Since many-to-many is associated according to relationships, updating fields is irrelevant to relationships, ditto.

---

Check 8.

• FirstIt’s finding the first one
• LastIt’s finding the last one
• FindIs to find it all

8.1 the one-to-one

Semantic environment: user table

type User struct {
	gorm.Model
	Relations 	   []User `gorm:"many2many:relation; association_jointable_foreignkey:relation_id"`
	UserName       string
	Email          string 	//`gorm:"unique"`
	Avatar         string 	`gorm:"size:1000"`
	Phone 		      string
	BanTime 	      int
	OpenID 		   string 	`gorm:"unique"`
}
Copy the code

8.1.1 Basic Query

• Query the first one

var u1 User
db.First(&u1)
fmt.Println(u1)
Copy the code

• Query the last one

var u2 User
db.Last(&u2)
fmt.Println(u2)
Copy the code

• Press the primary key

var u3 User
db.First(&u3,2) // If not specified, the default primary key is ID
fmt.Println(u3)
Copy the code

• Query one item by condition

var u4 User
db.First(&u4,"user_name=?"."FanOne") // If not specified, the default primary key is ID
fmt.Println(u4)
Copy the code

• Get all data

var u5 []User
db.Find(&u5) // Query all data
fmt.Println(u5)
Copy the code

8.1.2 Where conditions

• A single condition

var u1 User
db.Where("user_name = ?"."FanOne").First(&u1)
Copy the code

• Nested query

var u1s []User
db.Where("user_name=?"."fanone").Where("phone=?"."10086").Find(&u1s)
Copy the code

• Many conditions

var u2 User
db.Where("user_name = ? AND phone = ?"."FanOne"."10086").First(&u2)
Copy the code

• Fuzzy query remember to add%

var u3 User
db.Where("user_name LIKE ?"."%Fan%").First(&u3)  // return the first user_name that is close to Fan
var u3s []User
db.Where("user_name LIKE ?"."%Fan%").Find(&u3)   // Return multiple user_name data similar to Fan
Copy the code

• Range queries

var u4 []User
db.Where("id > ?"."FanOne"."10").Find(&u2) // select id>10
Copy the code

• An array

var u5 []User
db.Where("user_name in (?) "And []string{"Fan"."One"}).Find(&u5)
Copy the code

• Structural form

var u6 User
db.Where(&User{UserName:"FanOne"}).First(&u6)
Copy the code

• The map form

var u7 User
db.Where(map[string]interface{} {"user_name":"fan"."phone":"10086"}).First(&u7)
Copy the code

8.1.3 Not condition

• Query the UserName! = FanOne data, return list

var u1 []User
db.Not(&User{UserName:"FanOne"}).Find(&u1)
Copy the code

• Query the UserName! = FanOne data, return the first data

var u2 User
db.Not("user_name"."FanOne").First(&u2)
Copy the code

• Query the UserName! = fan data, return the first data

var u3 User
db.Not("user_name = ?"."fan").First(&u2)
Copy the code

• Select * from string where UserName is not in the list

var u4 User
db.Not("user_name"And []string{"fan"."one"}).First(&u4)
Copy the code

• Select * from string where UserName is not in the list

var u5 []User
db.Not("user_name in (?) "And []string{"fan"."one"}).Find(&u5)
Copy the code

8.1.4 Selecting a Query

• Query only the data in user_name column

var u1 []User
db.Select("user_name").Find(&u1)
Copy the code

• Query only the data of user_name and phone in string array format

var u1 []User
db.Select([]string{"user_name"."phone"}).Find(&u1)
Copy the code

• Query only the data of user_name and phone in string array format

var u1 []User
db.Select("user_name , phone").Find(&u1)
Copy the code

8.1.5 sorting

The default sort is ascending

var u1s,u2s []User
db.Order("name desc").Find(&u1s)  // In descending order by name
db.Order("name asc".true).Find(&u1s)  // In ascending order by name
Copy the code

8.1.6 paging

Limit Page size Offset Specifies the number of pages

var us []User
db.Order("id asc").Offset(5).Limit(3).Find(&us)
Copy the code

8.1.7 number

var count int
db.Model(&User{}).Count(&count)
Copy the code

var count int
db.Model(&User{}).Where("user_name Like ?"."% 6%").Count(&count)
Copy the code

var count int
db.Table("users").Where("user_name Like ?"."% 6%").Count(&count)
Copy the code

8.1.8 native SQL

When we want to do more complex queries, we can use this native SQL to do the queries. Perform and query operations

	query := "SELECT * FROM `work_time` WHERE `work_time`.`deleted_at` IS NULL AND month = "+ ` "`+ data.Month +` "`

	ifdata.Search! ="" {
		query += ` AND (( mask LIKE "`+"%"+data.Search +"%"+ `" ) OR ( number LIKE "`+"%"+data.Search+"%"+ ` ` "))
	}
	ifdata.DepartmentName! ="" {
		query += " AND department_name LIKE " + ` "`+"%" +data.DepartmentName+"%" + ` "`
	}
	ifdata.WorkType! ="" {
		query += " AND work_type LIKE " + ` "`+"%" +data.WorkType+"%" + ` "`
	}
	ifdata.EmployeeStatus! ="" {
		query += " AND employee_status LIKE " + ` "`+"%" +data.EmployeeStatus+"%" + ` "`
	}
	ifdata.EmployeeType! ="" {
		query += " AND employee_type LIKE " + ` "`+"%" +data.EmployeeType+"%" + ` "`
	}
	ifdata.Status! ="" {
		query += " AND status LIKE " + ` "`+"%" +data.Status+"%" + ` "`
	}
	if err := db.Offset((data.PageNum - 1) * data.PageSize).Limit(data.PageSize).Raw(query).Scan(&workTimeList).Error; err ! =nil {
		code = e.LEVEL_ERROR
		return serializer.Response{
			Level: code,
			Data:  e.GetMsg(code),
			Error: err.Error(),
		}
	}
Copy the code

8.2 a one-to-many

Semantic scene Comment is a Comment table, and Social is a post table. A post corresponds to multiple comments, so it is associated at multiple ends

type Comment struct {
	gorm.Model
	Content    string
	ParentId   uint   	// Parent comment ID
	UserID     uint   	/ / user ID
	ReplyName  string 	// The name of the responder
	UserName   string
	UserAvatar string
	SocialId   uint  `gorm:"ForeignKey:Social; AssociationForeignKey:social_id"` // Community post ID
	Children   []Comment `gorm:"ForeignKey:Comment; AssociationForeignKey:comment_id"`
}
Copy the code

• Look up a single useRelatedassociated

	var comment model.Comment 
	model.DB.Model(&comment).
	Related(&comment.SocialId , "social_id").Find(&comment)
Copy the code

• Check multiple usePreloadpreload

	var comment []model.Comment
	model.DB.Model(&comment).Preload("Children").Find(&comment)
Copy the code

8.3 many-to-many

Semantic scenarios the user is many-to-many to himself. It’s like a friend. A person can have more than one friend, and one friend can include other friends.

type User struct {
	gorm.Model
	Relations 	    []User `gorm:"many2many:relation; association_jointable_foreignkey:relation_id"`
	UserName       string
	Email          string 	//`gorm:"unique"`
	Avatar         string 	`gorm:"size:1000"`
	Phone 		   string
	BanTime 	   int
	OpenID 		   string 	`gorm:"unique"`
}
Copy the code

Many-to-many queries in the user table can be syndicated through this Association to query data information associated with it.

	var user model.User
	var friendsList []model.User
	model.DB.Table("user").Where("id = ?",id).First(&user)
	err := model.DB.Model(&user).Association("Relations").
	Limit(service.PageSize).Offset((service.PageNum- 1)*service.PageSize).
	Find(&friendsList).Error
Copy the code

---

9. Wrong summaries

Error 1: Error 1103: Incorrect table name

I don’t know which table it is

	var employees []model.Employee
	var count int
	db := model.DB
Copy the code

to

	var employees []model.Employee
	var count int
	db := model.DB.Model(model.Employee{})
Copy the code

Error 2: SQL: no rows in result set

Note: the Count() query must be after the WHERE condition, limit,offset before pagination!

SQL: No rows in result set SQL: No rows in result set

The value of count cannot be too early. Otherwise, the queried total number is the total number of all data, which is not conditionally filtered

	db.Limit(data.PageSize).Offset((data.PageNum- 1)*data.PageSize).Find(&employees).Count(&count)
Copy the code

to

	db.Count(&count).Limit(data.PageSize).Offset((data.PageNum- 1)*data.PageSize).Find(&employees)
Copy the code