This article has participated in the activity of “New person creation Ceremony”, and started the road of digging gold creation together.

1 the demand

Paging is a very common requirement in front and back separated projects, and recently Gin was used to refactor the SpringBoot project with Gorm as the integrated ORM framework. However, Golang ecology is more flexible (low EQ: crude) compared to Java, and many things need to be packaged by themselves. I wrote a blog to record MD5 encapsulation (Golang encapsulation with salt and encryption times MD5 function – Nuggets (juejin. Cn)). Online check a circle of Gorm paging scheme feel are not their own needs, so intend to use Gorm package a similar Mybatis-Plus paging scheme, can achieve similar “generic” query effect. Due to the use of Golang’s reflection mechanism, the image is more profound so it is recorded here. The method described in section 4 already works, with a bit of redundant code. The method proposed in section 6 uses reflection to solve redundancy, but reflection has performance problems. If redundancy is not a concern, use the encapsulation method in section 4

2 Project Structure

The Demo structure is as follows. We set up a small Gin project to perform paging queries on the country and city tables in MySQL 8’s built-in World database. Database is the initialization of the database, general paging query logic of the bottom package; The structure corresponding to city and country tables and the conditional query structure are recorded in the model. Service is the specific business query logic; There are only two routes in main.go, one is paging to query the route of the city, the other is paging to query the route of the country. See GitHub for the full code

| - gorm_page | | - database | | - mysql. Go / / initialization and paging underlying encapsulation | | -- model. Go / / pageResponse structure | | - model | | -- city. Go / / City table corresponding structure | | - country. Go / / country table corresponding structure | | -- page. Go / / paging conditions | | - service | | -- city. Go | | - country. Go | | -- go. Mod | | -- go. Sum | | -- main. GoCopy the code

Three structures

The city structure contains the city name, country code, region and province, and population, while the country structure contains the code, country name, continent and region, and founding time. The pageInfo structure specifies the current page and page size of the query and is nested in the corresponding city and country structures. The search criteria for city can be country code and region and province. The search criteria for country can be continent, region, and founding time. The Page structure is returned to the front end to record the total number of records, total number of pages, and so on.

type PageInfo struct {
	CurrentPage int64 `json:"currentPage"`
	PageSize    int64 `json:"pageSize"`
} // Paging conditions

type City struct {
	ID          int    `json:"id"`
	Name        string `json:"name"`
	CountryCode string `json:"countryCode"`
	District    string `json:"district"`
	Population  int    `json:"population"`
} // city table structure

type CityQueryInfo struct {
	PageInfo
	CountryCode string `json:"countryCode"`
	District    string `json:"district"`
} // city query condition

type Country struct {
	Code      string `json:"code"`
	Name      string `json:"name"`
	Continent string `json:"continent"`
	Region    string `json:"region"`
	IndepYear int    `json:"indepYear"`
} // country table structure

type CountryQueryInfo struct {
	PageInfo
	Continent string `json:"continent"`
	Region    string `json:"region"`
	IndepYear int    `json:"indepYear"`
} // country Query conditions

type Page struct {
	CurrentPage int64       `json:"currentPage"`
	PageSize    int64       `json:"pageSize"`
	Total       int64       `json:"total"` // Total number of records
	Pages       int64       `json:"pages"` / / the total number of pages
	Data        interface{} `json:"data"` // The actual list data
} // Paging response is returned to the front end
Copy the code

4 Redundant Gorm paging encapsulation exists

4.1 Gorm official paging example

Gorm officially provides a simple paging mount, as shown below. However, our business is relatively complex in the actual use process. For example, we hope to obtain total pages and total records. Such a simple package cannot meet our needs.

// GorM official paging instance
func Paginate(r *http.Request) func(db *gorm.DB) *gorm.DB {
	return func (db *gorm.DB) *gorm.DB {
		page, _ := strconv.Atoi(r.Query("page"))
		if page == 0 {
			page = 1
		}	
		pageSize, _ := strconv.Atoi(r.Query("page_size"))
		switch {
		case pageSize > 100:
			pageSize = 100
		case pageSize <= 0:
			pageSize = 10
		}
	
		offset := (page - 1) * pageSize
		return db.Offset(offset).Limit(pageSize)
		}
}
db.Scopes(Paginate(r)).Find(&users)
db.Scopes(Paginate(r)).Find(&articles)
Copy the code

4.2 Encapsulate additional paging information

To try to encapsulate the extra paging information yourself, start by writing CountAll and SelectList functions in cityModel to query the total number of records and list data. And extend Paginate, can get pages, total and other information, and limit the boundaries.

package model

import (
	"gorm_page/database"

	"gorm.io/gorm"
)

type City struct {
	ID          int    `json:"id"`
	Name        string `json:"name"`
	CountryCode string `json:"countryCode"`
	District    string `json:"district"`
	Population  int    `json:"population"`
}
// The wrapper contains the query criteria, which the service passes
func (c *City) CountAll(wrapper map[string]interface{}) int64 {
	var total int64
	database.DB.Model(&City{}).Where(wrapper).Count(&total)
	return total
}

func (c *City) SelectList(p *database.Page, wrapper map[string]interface{}) error {
	list := []City{}
	iferr := database.DB.Model(&City{}).Scopes(Paginate(p)).Where(wrapper).Find(&list).Error; err ! =nil {
		return err
	}
	p.Data = list
	return nil
}

func Paginate(page *database.Page) func(db *gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		if page.CurrentPage <= 0 {
			page.CurrentPage = 0
		} // If the current page is less than 0, set it to 0
		switch {
		case page.PageSize > 100:
			page.PageSize = 100
		case page.PageSize <= 0:
			page.PageSize = 10
		} // Limit the size
		page.Pages = page.Total / page.PageSize
		ifpage.Total%page.PageSize ! =0 {
			page.Pages++
		} // Count the total pages
		p := page.CurrentPage
		if page.CurrentPage > page.Pages {
			p = page.Pages
		} // If the current page is greater than the total number of pages, use the total number of pages
		size := page.PageSize
		offset := int((p - 1) * size)
		return db.Offset(offset).Limit(int(size)) // Set limit and offset}}Copy the code

4.3 Call paging in Service

The query criteria are first extracted from CityQueryInfo into the Wrapper, and then CountAll of cityModel is called to get the total number of records, which is returned if 0 (saving time). Then call SelelctList to save the actual list Data to the Data field of the Page.

package service

import (
	"gorm_page/database"
	"gorm_page/model"
)

type CityService struct{}

var cityModel model.City

func (c *CityService) SelectPageList(p *database.Page, queryVo model.CityQueryInfo) error {
	p.CurrentPage = queryVo.CurrentPage
	p.PageSize = queryVo.PageSize
	wrapper := make(map[string]interface{}, 0)
	ifqueryVo.CountryCode ! ="" {
		wrapper["CountryCode"] = queryVo.CountryCode
	}
	ifqueryVo.District ! ="" {
		wrapper["District"] = queryVo.District
	}
	p.Total = cityModel.CountAll(wrapper)
	if p.Total == 0 {
		return nil // If the total number of records is 0, the Limit query is not executed
	}
	return cityModel.SelectList(p, wrapper)
}
Copy the code

4.4 Redundant code

The above encapsulation method works, but it is not perfect, and there is a lot of redundant code when there are many objects that need to be queried in pages. For example, if I were to wrap a country paging query as described above, I would get the following redundant code. As you can see, the only different parts of CountAll and SelectList are types. If there are 10 objects that need to be paging queried, the two functions are repeated 10 times. So is there a way to simplify and just write countAll and SelectList once?

// Redundant code in model/city.go
func (c *City) CountAll(wrapper map[string]interface{}) int64 {
	var total int64
	database.DB.Model(&City{}).Where(wrapper).Count(&total)
	return total
}
func (c *City) SelectList(p *database.Page, wrapper map[string]interface{}) error {
	list := []City{}
	iferr := database.DB.Model(&City{}).Scopes(Paginate(p)).Where(wrapper).Find(&list).Error; err ! =nil {
		return err
	}
	p.Data = list
	return nil
}

// Redundant code in model/country.go
func (c *Country) CountAll(wrapper map[string]interface{}) int64 {
	var total int64
	database.DB.Model(&Country{}).Where(wrapper).Count(&total)
	return total
}
func (c *Country) SelectList(p *database.Page, wrapper map[string]interface{}) error {
	list := []Country{}
	iferr := database.DB.Model(&Country{}).Scopes(Paginate(p)).Where(wrapper).Find(&list).Error; err ! =nil {
		return err
	}
	p.Data = list
	return nil
}
Copy the code

5 Paging in Mybatis-Plus

Before simplifying Gorm’s paging encapsulation, let’s take a look at paging in Spring. The ORM framework most commonly used in Spring is Mybatis, whose paging function requires the use of additional paging plug-ins. The basic principle is to use interceptors to intercept SQL statements and add Limit and Offset conditions. Mybatis-Plus integrates the paging plug-in, which is very simple to use, just need to create the corresponding Page object and QueryWrapper object, pass to the Mapper layer selectPage method. SelectPage is inherited from BaseMapper and is transparent to UserMapper and CityMapper. After selectPage is executed, currentPage, Total, Pages, and list of records are saved in the Page object.

// Query user pages
public Page<User> selectPageList(UserQueryVo queryVo) {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.eq("career", queryVo.getCareer());
    queryWrapper.orderByDesc("update_time");
    Page<User> page = new Page<>(queryVo.getCurrentPage(), queryVo.getPageSize());
    return userMapper.selectPage(page, queryWrapper);
}
// Query city paging
public Page<City> selectPageList(CityQueryVo queryVo) {
    QueryWrapper<City> queryWrapper = new QueryWrapper<>();
    queryWrapper.eq("country", queryVo.getCountry()); // Find the city by country
    queryWrapper.orderByDesc("update_time");
    Page<User> page = new Page<>(queryVo.getCurrentPage(), queryVo.getPageSize());
    return cityMapper.selectPage(page, queryWrapper);
}
Copy the code

As you can see from the code above, the reuse of paging code in Mybatis-Plus leverages Java’s generics mechanism, while Golang won’t support generics until 1.18 is released. So is there any way to achieve generics in Golang right now? The answer is reflex

Use reflection to simplify paging encapsulation

A closer look at the redundant code in 4.4 showed that the arguments in db.model () and db.find () were type-specific. The first solution that came to mind was to use interface{} to abstract the code. The interface{} array cannot be passed to Find because it needs to determine the type of the list element.

func SelectPage(p *database.Page, wrapper map[string]interface{}, model interface{}) error {
	database.DB.Model(&model).Where(wrapper).Count(&p.Total)
	if p.Total == 0 {
		p.Data = []interface{} {}return nil
	}
	list := []interface{}{}
	err := database.DB.Model(&model).Scopes(Paginate(p)).Where(wrapper).Find(&list).Error
        iferr ! =nil {
		return err
	}
	p.Data = list
	return nil
}
Copy the code

The Find function uses reflection to get the type of the element in the array, so we use reflection to get the specific type of the parameter from the model parameter before passing the parameter, and then use reflection to create the array of the corresponding type.

func SelectPage(page *Page, wrapper map[string]interface{}, model interface{}) (e error) {
	e = nil
	DB.Model(&model).Where(wrapper).Count(&page.Total)
	if page.Total == 0 {
		page.Data = []interface{} {}return
	}
	// reflection gets the type
	t := reflect.TypeOf(model)
	// Create an array of the corresponding type by reflection
	list := reflect.Zero(reflect.SliceOf(t)).Interface()
	e = DB.Model(&model).Where(wrapper).Scopes(Paginate(page)).Find(&list).Error
	ife ! =nil {
		return
	}
	page.Data = list
	return
}
Copy the code

Reflection creates arrays of the corresponding type as follows:

list := reflect.MakeSlice(reflect.SliceOf(t), 0.0).Interface()
Copy the code

Calling the paging wrapper function, you can see that all that remains in the City and Country struct methods is the same line calling SelelctPage, with the third argument specifying the query struct.

// Paging is invoked in model/city.go
func (c *City) SelectPageList(p *database.Page, wrapper map[string]interface{}) error {
	err := database.SelectPage(p, wrapper, City{}) // specify the query for City
	return err
}

// Paging is called in model/country.go
func (c *Country) SelectPageList(p *database.Page, wrapper map[string]interface{}) error {
	err := database.SelectPage(p, wrapper, Country{}) // Specify the query Country
	return err
}
Copy the code

7 Test Run

The complete project code is shown in my Github example. Enter Go run main. Go to run the Demo program. It can be seen that there are pages, total and other information in Response, and the list data in data is normal, indicating that the SelectPage function encapsulated at the bottom can query different objects through reflection.

8 summarizes

Golang is more flexible than the Java architecture, and a lot of things need to be wrapped by hand. This is just a quick demonstration of using reflection for paging, but reflection has some performance issues. In addition, the query conditions in Demo use map transfer, so that only equal query conditions can be transmitted. DB can be used to transfer like and greater than or less than conditions:

query := database.DB.Model(&Country{}).Where(wrapper)
query = query.Where("IndepYear > ?".1949) // Set greater than the condition
database.SelectPage(p, query, Country{})
// Change the SelectPage function to the following
func SelectPage(page *Page, query *gorm.DB, model interface{}) (e error) {
        e = nil
	query.Count(&page.Total)
	if page.Total == 0 {
		page.Data = []interface{} {}return
	}
	// reflection gets the type
	t := reflect.TypeOf(model)
	// Create an array of the corresponding type by reflection
	list := reflect.Zero(reflect.SliceOf(t)).Interface()
	e = query.Scopes(Paginate(page)).Find(&list).Error
	ife ! =nil {
		return
	}
	page.Data = list
	return
}
Copy the code