我要评论1、背景
在提供列表接口时一般要用到分页，对于存储在某些数据库中的数据进行分页起来非常的方便，下文给出一个通过gorm进行分页并通过http返回数据的例子。
2、go库下载
gorm官方文档地址：https://gorm.io/zh_cn/docs/，库下载如下：
go get -u gorm.io/gorm
本文使用的数据库是mysql，因此要下载mysql驱动：
go get -u gorm.io/driver/mysql
这里使用gin框架来提供查询列表的接口想，库下载如下：
go get -u github.com/gin-gonic/gin
3、初始化数据
【1】建表
建一个用来测试分页的表，sql如下：
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 '测试分页表';
【2】插入数据
插入30条number和create_time字段不同的数据，sql如下：
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】查看数据
查看创建出来的30条数据：
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、代码示例
【1】gorm结构体定义
可以根据在线sql转go结构体来实现，转换之后如下：
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 time.time `gorm:"column:create_time;type:datetime;default:current_timestamp" json:"create_time"`
updatetime time.time `gorm:"column:update_time;type:datetime;default:current_timestamp" json:"update_time"`
}
func (m *testpage) tablename() string {
return "test_page"
}
【2】分页结构体定义
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"` //总页数
totalcount int `json:"total_count"` //总条数
page int `json:"page"` //当前页
pagesize int `json:"page_size"` //当前页数据量
prevpage bool `json:"prev_page"` //是否存在上页
nextpage bool `json:"next_page"` //是否存在下页
}
//要测试的分页结构
type testpageresp struct {
pageresult
list []models.testpage `json:"list"`
}
【3】封装分页方法
package page
import (
"math"
"gotest/gorm_demo/types"
)
// getoffsetandlimit
//
// @description: 获取查数据库的偏移量和限制数
// @param page 当前页
// @param pagesize 业数据量
// @return offset 数据库偏移
// @return limit 数据库限制量
func getoffsetandlimit(page, pagesize int) (offset, limit int) {
limit = pagesize
if limit <= 0 {
limit = 4
}
if page > 1 {
offset = (page - 1) * limit
}
return
}
// getpageresult
//
// @description: 获取分页结果
// @param pg 当前页
// @param pagesize 页数据量
// @param totalcount 根据此数量进行分页
// @return types.pageresult 分页结构
func getpageresult(pg, pagesize, totalcount int) types.pageresult {
res := types.pageresult{}
res.totalcount = totalcount
res.pagesize = pagesize
res.page = pg
if pagesize > 0 && totalcount > 0 {
res.totalpage = int(math.ceil(float64(totalcount) / float64(pagesize)))
}
if res.page < res.totalpage {
res.nextpage = true
}
if res.page > 1 {
res.prevpage = true
}
return res
}
【4】查询列表接口
使用gin框架启动一个http服务，并注册一条路由用于查询列表接口：
r := gin.new()
r.get("/page/list", getpagelisthandler)
if err := r.run(":8888"); err != nil {
panic(err)
}
查询列表接口回调函数实现如下：
func getpagelisthandler(c *gin.context) {
var req types.pagereq
if err := c.shouldbind(&req); err != nil {
logger.error("invalid req", zap.error(err))
c.string(500, "invalid req")
return
}
logger.info("get page list req", zap.any("req", req))
db := conn.getxxxdbconn()
resp := types.testpageresp{}
resp.list = make([]models.testpage, 0)
//获取所有数量
var count int64
if err := db.model(&models.testpage{}).count(&count).error; err != nil {
logger.error("get count error", zap.error(err))
c.string(500, "get count error")
return
}
//计算分页数据
resp.pageresult = page.getpageresult(req.page, req.pagesize, int(count))
//计算偏移量
offset, limit := page.getoffsetandlimit(req.page, req.pagesize)
//数据库查询
if err := db.model(&models.testpage{}).order("create_time desc").offset(offset).limit(limit).find(&resp.list).error; err != nil {
logger.error("get list error", zap.error(err))
c.string(500, "get list error")
return
}
//返回数据
c.json(http.statusok, resp)
}
【5】启动http服务
控制台输出如下：
[gin-debug] [warning] running in "debug" mode. switch to "release" mode in production. - using env: export gin_mode=release - using code: gin.setmode(gin.releasemode) [gin-debug] get /page/list --> main.getpagelisthandler (1 handlers) [gin-debug] [warning] you trusted all proxies, this is not safe. we recommend you to set a value. please check https://pkg.go.dev/github.com/gin-gonic/gin#readme-don-t-trust-all-proxies for details. [gin-debug] listening and serving http on :8888
这里使用的是gin框架的debug模式，生产环境中不要用gin框架的debug模式，会影响程序的qps。
【6】调用获取列表接口
使用curl命令来调用，获取第2页，页数据量为6条的数据，控制台输出如下：
[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, //总共有5页
"total_count": 30, //根据30条数据进行分页
"page": 2, //当前页为第2页
"page_size": 6, //每一页的大小为6
"prev_page": true, //上一页存在
"next_page": true, //下一页存在
"list": [ //根据创建时间倒序的列表
{
"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"
}
]
}
此时gin程序控制台输出：
[gin-debug] [warning] running in "debug" mode. switch to "release" mode in production.
- using env: export gin_mode=release
- using code: gin.setmode(gin.releasemode)
[gin-debug] get /page/list --> main.getpagelisthandler (1 handlers)
[gin-debug] [warning] you trusted all proxies, this is not safe. we recommend you to set a value.
please check https://pkg.go.dev/github.com/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/main.go:131] | get page list req | {"req": {"page":2,"pagesize":6}} //分页请求数据
[2024-10-21 17:23:13.437] | info | goroutine:34 | [conn/mysql.go:74] | conn mysql success
2024/10/21 17:23:13 d:/gotest/gorm_demo/main.go:139
[0.640ms] [rows:1] select count(*) from `test_page` //查询所有数量的sql
2024/10/21 17:23:13 d:/gotest/gorm_demo/main.go:152
[0.325ms] [rows:6] select * from `test_page` order by create_time desc limit 6 offset 6 //根据分页请求计算出的偏移位置和限制量sql
上面打印了gorm库中的日志信息，可以通过自定义zap对象来实现gorm的logger对象来自定义gorm输出日志。
5、总结
分页的方法有很多，上面只是给出了其中一种，根据自己项目的业务场景选择一种最喜欢的方法就行。
到此这篇关于golang使用gorm实现分页功能的示例代码的文章就介绍到这了,更多相关golang gorm实现分页内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论