preface

This is the sixth day of my participation in the Text challenge, hello, I’m the composer Kind of Sun. Last time we talked about the unified encapsulation of Response. This one takes you and shows you how to write a user list interface with pagination, and by the way, connect gin to mysql and GORM

1. Introduction

(1). Gorm is the most popular ORM framework gorM Chinese document in GO

Orm framework helps developers improve efficiency, encapsulation OF SQL, developers do not have to write SQL directly, there are a lot of syntax sugar, but we note that SQL is very important, we have to understand SQL more and more, in order to achieve the use of ORM

(2). Let’s analyze the GetUserList interface. Its functions:

  1. Return to user list
  2. Paging function

Next, refer to the content returned by the following interface:

{
    "code": 200."data": {
        "total": 2."userlist": [{"address": ""."birthday": "2021-06-02"."desc": ""."gender": ""."head_url": ""."id": 3."mobile": "13999189292"."nick_name": "peiyahui2"."role": 1
            },
            {
                "address": ""."birthday": "2021-06-02"."desc": ""."gender": ""."head_url": ""."id": 4."mobile": "13999189293"."nick_name": "peiyahui3"."role": 1}},"msg": "Successfully obtained user list"
}

Copy the code

2. Install gorm

IO /driver/mysql // Install the mysql driver go get gorm. IO /gormCopy the code

3. Define the struct of the table

Write it in models/user.go

package models
import "time"
type User struct {
	ID       uint      `json:"id" gorm:"primaryKey"`
	Password string    `json:"password"`
	NickName string    `json:"nick_name"`
	HeadUrl  string    `json:"head_url"`
	Birthday *time.Time `json:"birthday" gorm:"type:date"`
	Address  string    `json:"address"`
	Desc     string    `json:"desc"`
	Gender   string    `json:"gender"`
	Role     int       `json:"role"`
	Mobile   string    `json:"mobile"`
}

// Set the table name of User to 'profiles'
func (User) TableName(a) string {
	return "user"
}
Copy the code

4. Write the InitMysql function to initialize the mysql driver

(1) write in initialize/mysql

package initialize

import (
	"fmt"
	"go_gin/global"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)
 
func InitMysqlDB(a) {
	mysqlInfo := global.Settings.Mysqlinfo
	// See https://github.com/go-sql-driver/mysql#dsn-data-source-name for details
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s? charset=utf8mb4&parseTime=True&loc=Local",
		mysqlInfo.Name, mysqlInfo.Password, mysqlInfo.Host,
		mysqlInfo.Port, mysqlInfo.DBName)
	db, _ := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	global.DB = db
}

Copy the code

Several attention points: 1. Global. Settings. Mysqlinfo inside information about 9 processing yaml configuration letter about 2. Make sure that the mysql address, password, username, and port number are not incorrectly written. No Spaces in yaml values!! 4. Global. DB is added to globalvar. go

    //globalVar.go
    DB    *gorm.DB
Copy the code

(2). The test

Introduced in main.go

Initialize mysql
initialize.InitMysqlDB()
Copy the code

And start the project, if there is no error, the database driver is already linked

4. Create the user table

(1). The mysql navicat link

(2). Download the SQL file and import it into the database

5. Write the DAO layer and operate the database

Write the GetUserListDao function in dao/user.go

package dao

import (
	"go_gin/global"
	"go_gin/models"
)

var users []models.User

// GetUserList gets the user list (page number,page_size number of data per page)
func GetUserListDao(page int, page_size int) (intAnd []interface{}) {
	// Paging user list data
	userList := make([]interface{}, 0.len(users))
	// Calculate the offset
	offset := (page - 1) * page_size
	// Query all users
	result := global.DB.Offset(offset).Limit(page_size).Find(&users)
	// If no data is found
	if result.RowsAffected == 0{
		return 0, userList
	}
	// Get the total number of users
	total := len(users)
	// Query data
	result.Offset(offset).Limit(page_size).Find(&users)
	//
	for _, useSingle := range users {
		birthday := ""
		if useSingle.Birthday == nil {
			birthday = ""
		}else {
			// Set the initial value of the unset birthday
			birthday = useSingle.Birthday.Format("2006-01-02")
		}
		userItemMap := map[string]interface{} {"id":        useSingle.ID,
			"password":  useSingle.Password,
			"nick_name": useSingle.NickName,
			"head_url":  useSingle.HeadUrl,
			"birthday":  birthday,
			"address":   useSingle.Address,
			"desc":      useSingle.Desc,
			"gender":    useSingle.Gender,
			"role":      useSingle.Role,
			"mobile":    useSingle.Mobile,
		}
		userList = append(userList, userItemMap)
	}
	return total, userList
}

Copy the code

SQL > select * from user; SQL > select * from user

6. Write the controller layer GetUserList function

Controller layer is to complete the integration of services, return user data write GetUserList function in controller/user

// GetUserList gets the user list
func GetUserList(c *gin.Context) {
	// Get parameters
	UserListForm := forms.UserListForm{}
	iferr := c.ShouldBind(&UserListForm); err ! =nil {
		utils.HandleValidatorError(c, err)
		return
	}
	// Get data
	total, userlist := dao.GetUserListDao(UserListForm.Page, UserListForm.PageSize)
	/ / determine
	if (total + len(userlist)) == 0 {
		Response.Err(c, 400.400."No data was obtained".map[string]interface{} {"total":    total,
			"userlist": userlist,
		})
		return
	}
	Response.Success(c, 200."Obtaining user list succeeded".map[string]interface{} {"total":    total,
		"userlist": userlist,
	})
}

Copy the code

And finally — testing

(1). Add a route to router/user

    UserRouter.POST("list", controller.GetUserList)
Copy the code

(2). Use the postman open http://192.168.0.125:8022/v1/user/list

Return the data successfully, indicating that you have learned this chapter ~

If you found any of these articles useful, please give them a thumbs up and leave a comment