1. Background
When providing a list interface, paging is generally used. It is very convenient to paginate the data stored in some databases. Here is a passgormPaginate and passhttpReturns an example of data.
2. Go library download
gormOfficial document address:/zh_CN/docs/, the library download is as follows:
go get -u /gorm
The database used in this article ismysql, so download itmysqldrive:
go get -u /driver/mysql
Here we use the gin framework to provide the interface for query list, and the library download is as follows:
go get -u /gin-gonic/gin
3. Initialize the data
【1】Create a table
Create a table to test the page.sqlas follows:
CREATE TABLE test_page
(
id INT AUTO_INCREMENT PRIMARY KEY,
number INT NOT NULL,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT 'Test paging table';
【2】Insert data
Insert 30 piecesnumberandcreate_timeData with different fields,sqlas follows:
INSERT INTO test_page (number, create_time)
VALUES (1, NOW() + INTERVAL 0 SECOND),
(2, NOW() + INTERVAL 1 SECOND),
(3, NOW() + INTERVAL 2 SECOND),
(4, NOW() + INTERVAL 3 SECOND),
(5, NOW() + INTERVAL 4 SECOND),
(6, NOW() + INTERVAL 5 SECOND),
(7, NOW() + INTERVAL 6 SECOND),
(8, NOW() + INTERVAL 7 SECOND),
(9, NOW() + INTERVAL 8 SECOND),
(10, NOW() + INTERVAL 9 SECOND),
(11, NOW() + INTERVAL 10 SECOND),
(12, NOW() + INTERVAL 11 SECOND),
(13, NOW() + INTERVAL 12 SECOND),
(14, NOW() + INTERVAL 13 SECOND),
(15, NOW() + INTERVAL 14 SECOND),
(16, NOW() + INTERVAL 15 SECOND),
(17, NOW() + INTERVAL 16 SECOND),
(18, NOW() + INTERVAL 17 SECOND),
(19, NOW() + INTERVAL 18 SECOND),
(20, NOW() + INTERVAL 19 SECOND),
(21, NOW() + INTERVAL 20 SECOND),
(22, NOW() + INTERVAL 21 SECOND),
(23, NOW() + INTERVAL 22 SECOND),
(24, NOW() + INTERVAL 23 SECOND),
(25, NOW() + INTERVAL 24 SECOND),
(26, NOW() + INTERVAL 25 SECOND),
(27, NOW() + INTERVAL 26 SECOND),
(28, NOW() + INTERVAL 27 SECOND),
(29, NOW() + INTERVAL 28 SECOND),
(30, NOW() + INTERVAL 29 SECOND);
【3】View data
Check out the 30 pieces of data created:
mysql [xxx]> select * from test_page; +----+--------+---------------------+---------------------+ | id | number | create_time | update_time | +----+--------+---------------------+---------------------+ | 1 | 1 | 2024-10-21 07:11:19 | 2024-10-21 07:11:19 | | 2 | 2 | 2024-10-21 07:11:20 | 2024-10-21 07:11:19 | | 3 | 3 | 2024-10-21 07:11:21 | 2024-10-21 07:11:19 | | 4 | 4 | 2024-10-21 07:11:22 | 2024-10-21 07:11:19 | | 5 | 5 | 2024-10-21 07:11:23 | 2024-10-21 07:11:19 | | 6 | 6 | 2024-10-21 07:11:24 | 2024-10-21 07:11:19 | | 7 | 7 | 2024-10-21 07:11:25 | 2024-10-21 07:11:19 | | 8 | 8 | 2024-10-21 07:11:26 | 2024-10-21 07:11:19 | | 9 | 9 | 2024-10-21 07:11:27 | 2024-10-21 07:11:19 | | 10 | 10 | 2024-10-21 07:11:28 | 2024-10-21 07:11:19 | | 11 | 11 | 2024-10-21 07:11:29 | 2024-10-21 07:11:19 | | 12 | 12 | 2024-10-21 07:11:30 | 2024-10-21 07:11:19 | | 13 | 13 | 2024-10-21 07:11:31 | 2024-10-21 07:11:19 | | 14 | 14 | 2024-10-21 07:11:32 | 2024-10-21 07:11:19 | | 15 | 15 | 2024-10-21 07:11:33 | 2024-10-21 07:11:19 | | 16 | 16 | 2024-10-21 07:11:34 | 2024-10-21 07:11:19 | | 17 | 17 | 2024-10-21 07:11:35 | 2024-10-21 07:11:19 | | 18 | 18 | 2024-10-21 07:11:36 | 2024-10-21 07:11:19 | | 19 | 19 | 2024-10-21 07:11:37 | 2024-10-21 07:11:19 | | 20 | 20 | 2024-10-21 07:11:38 | 2024-10-21 07:11:19 | | 21 | 21 | 2024-10-21 07:11:39 | 2024-10-21 07:11:19 | | 22 | 22 | 2024-10-21 07:11:40 | 2024-10-21 07:11:19 | | 23 | 23 | 2024-10-21 07:11:41 | 2024-10-21 07:11:19 | | 24 | 24 | 2024-10-21 07:11:42 | 2024-10-21 07:11:19 | | 25 | 25 | 2024-10-21 07:11:43 | 2024-10-21 07:11:19 | | 26 | 26 | 2024-10-21 07:11:44 | 2024-10-21 07:11:19 | | 27 | 27 | 2024-10-21 07:11:45 | 2024-10-21 07:11:19 | | 28 | 28 | 2024-10-21 07:11:46 | 2024-10-21 07:11:19 | | 29 | 29 | 2024-10-21 07:11:47 | 2024-10-21 07:11:19 | | 30 | 30 | 2024-10-21 07:11:48 | 2024-10-21 07:11:19 | +----+--------+---------------------+---------------------+
4. Code examples
【1】Gorm structure definition
Can be based onOnline SQL to Go structureTo implement it, after the conversion is as follows:
package models
import (
"time"
)
type TestPage struct {
Id int `gorm:"column:id;type:int(11);AUTO_INCREMENT;primary_key" json:"id"`
Number int `gorm:"column:number;type:int(11);NOT NULL" json:"number"`
CreateTime `gorm:"column:create_time;type:datetime;default:CURRENT_TIMESTAMP" json:"create_time"`
UpdateTime `gorm:"column:update_time;type:datetime;default:CURRENT_TIMESTAMP" json:"update_time"`
}
func (m *TestPage) TableName() string {
return "test_page"
}
【2】Pagination structure definition
package types
import "GoTest/gorm_demo/models"
type PageReq struct {
Page int `form:"page,default=1"`
PageSize int `form:"page_size,default=4"`
}
type PageResult struct {
TotalPage int `json:"total_page"` //Total page count TotalCount int `json:"total_count"` //Total number of items Page int `json:"page"` //Current page PageSize int `json:"page_size"` //The current page data volume PrevPage bool `json:"prev_page"` //Is the previous page existed NextPage bool `json:"next_page"` //Is the next page exist?}
//The pagination structure to be testedtype TestPageResp struct {
PageResult
List [] `json:"list"`
}
【3】Packaging and paging method
package page
import (
"math"
"GoTest/gorm_demo/types"
)
// GetOffsetAndLimit
//
// @Description: Get the offset and limit of the database// @param page Current page// @param pageSize Industry data volume// @return offset database offset// @return limit database limitfunc GetOffsetAndLimit(page, pageSize int) (offset, limit int) {
limit = pageSize
if limit <= 0 {
limit = 4
}
if page > 1 {
offset = (page - 1) * limit
}
return
}
// GetPageResult
//
// @Description: Get pagination results// @param pg Current page// @param pageSize page data volume// @param totalCount Pagination based on this quantity// @return Pagination structurefunc GetPageResult(pg, pageSize, totalCount int) {
res := {}
= totalCount
= pageSize
= pg
if pageSize > 0 && totalCount > 0 {
= int((float64(totalCount) / float64(pageSize)))
}
if < {
= true
}
if > 1 {
= true
}
return res
}
【4】Query list interface
Use the gin framework to start an http service and register a route to query the list interface:
r := ()
("/page/list", GetPageListHandler)
if err := (":8888"); err != nil {
panic(err)
}
The query list interface callback function is implemented as follows:
func GetPageListHandler(c *) {
var req
if err := (&req); err != nil {
("invalid req", (err))
(500, "invalid req")
return
}
("get page list req", ("req", req))
db := ()
resp := {}
= make([], 0)
//Get all quantities var count int64
if err := (&{}).Count(&count).Error; err != nil {
("get count error", (err))
(500, "get count error")
return
}
// Calculate the paged data = (, , int(count))
// Calculate the offset offset, limit := (, )
//Database query if err := (&{}).Order("create_time DESC").Offset(offset).Limit(limit).Find(&).Error; err != nil {
("get list error", (err))
(500, "get list error")
return
}
//Return data (, resp)
}
【5】Start http service
The console output is as follows:
[GIN-debug] [WARNING] Running in "debug" mode. Switch to "release" mode in production. - using env: export GIN_MODE=release - using code: () [GIN-debug] GET /page/list --> (1 handlers) [GIN-debug] [WARNING] You trusted all proxies, this is NOT safe. We recommend you to set a value. Please check //gin-gonic/gin#readme-don-t-trust-all-proxies for details. [GIN-debug] Listening and serving HTTP on :8888
The one used here isginFrameworkdebugMode, do not use it in production environmentginFrameworkdebugmode will affect the programqps。
【6】Call the list interface
usecurlThe command is called to obtain the data with 6 pieces of page data. The console output is as follows:
[xxx@xxx ~]# curl -v -X GET --location "http://127.0.0.1:8888/page/list?page=2&page_size=6"
* About to connect() to 127.0.0.1 port 8888 (#0)
* Trying 127.0.0.1...
* Connected to 127.0.0.1 (127.0.0.1) port 8888 (#0)
> GET /page/list?page=2&page_size=6 HTTP/1.1
> User-Agent: curl/7.29.0
> Host: 127.0.0.1:8888
> Accept: */*
>
< HTTP/1.1 200 OK
< Content-Type: application/json; charset=utf-8
< Date: Mon, 21 Oct 2024 09:23:13 GMT
< Content-Length: 735
<
* Connection #0 to host 127.0.0.1 left intact
{
"total_page": 5, //There are 5 pages in total "total_count": 30, //Paginate based on 30 pieces of data "page": 2, //The current page is page 2 "page_size": 6, //The size of each page is 6 "prev_page": true, //The previous page exists "next_page": true, //The next page exists "list": [ //A list of reverse order according to creation time {
"id": 24,
"number": 24,
"create_time": "2024-10-21T07:11:42+08:00",
"update_time": "2024-10-21T07:11:19+08:00"
},
{
"id": 23,
"number": 23,
"create_time": "2024-10-21T07:11:41+08:00",
"update_time": "2024-10-21T07:11:19+08:00"
},
{
"id": 22,
"number": 22,
"create_time": "2024-10-21T07:11:40+08:00",
"update_time": "2024-10-21T07:11:19+08:00"
},
{
"id": 21,
"number": 21,
"create_time": "2024-10-21T07:11:39+08:00",
"update_time": "2024-10-21T07:11:19+08:00"
},
{
"id": 20,
"number": 20,
"create_time": "2024-10-21T07:11:38+08:00",
"update_time": "2024-10-21T07:11:19+08:00"
},
{
"id": 19,
"number": 19,
"create_time": "2024-10-21T07:11:37+08:00",
"update_time": "2024-10-21T07:11:19+08:00"
}
]
}
at this timeginProgram console output:
[GIN-debug] [WARNING] Running in "debug" mode. Switch to "release" mode in production.
- using env: export GIN_MODE=release
- using code: ()
[GIN-debug] GET /page/list --> (1 handlers)
[GIN-debug] [WARNING] You trusted all proxies, this is NOT safe. We recommend you to set a value.
Please check //gin-gonic/gin#readme-don-t-trust-all-proxies for details.
[GIN-debug] Listening and serving HTTP on :8888
[2024-10-21 17:23:13.435] | INFO | Goroutine:34 | [gorm_demo/:131] | get page list req | {"req": {"Page":2,"PageSize":6}} //Pagination request data[2024-10-21 17:23:13.437] | INFO | Goroutine:34 | [conn/:74] | conn mysql success
2024/10/21 17:23:13 D:/GoTest/gorm_demo/:139
[0.640ms] [rows:1] SELECT count(*) FROM `test_page` //Query all number of sql
2024/10/21 17:23:13 D:/GoTest/gorm_demo/:152
[0.325ms] [rows:6] SELECT * FROM `test_page` ORDER BY create_time DESC LIMIT 6 OFFSET 6 //The offset position and limit amount calculated based on the paging request sqlPrinted abovegormLog information in the library can be customizedzapObject to implementgormofloggerObject customizationgormOutput log.
5. Summary
There are many ways to paginate. The above is just one of them. Just choose your favorite method based on the business scenario of your project.
This is the article about this example code for Golang using gorm to implement the paging function. For more related content on Golang gorm to implement paging, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!