golang通過mysql語句實現(xiàn)分頁查詢

更新時間：2022年03月25日 10:04:10 作者：峰啊瘋了

這篇文章主要介紹了golang通過mysql語句實現(xiàn)分頁查詢，文章內(nèi)容介紹詳細，具有一定的參考價值，需要的小伙伴可以參考一下，希望對你的學習有所幫助

1.前端接口調(diào)用

#yyds干貨盤點#golang實現(xiàn)通過mysql語句實現(xiàn)分頁查詢_數(shù)據(jù)庫分頁

2.register訪問入口

//查詢一個用戶下所有的subnet
ws.Route(ws.GET("/subnets").
To(sc.ListSubnet).
Doc("List subnets authorized to the login user.").
Param(ws.QueryParameter(query.ParameterPage, "page").Required(false).DataFormat("page=%d").DefaultValue("page=1")).
Param(ws.QueryParameter(query.ParameterLimit, "limit").Required(false)).
Returns(http.StatusOK, api.StatusOK, api.ListResult{}))

3.解析參數(shù)

//查詢某個用戶下所有的subnet信息
func (v *SubController) ListSubnet(request *restful.Request, response *restful.Response) {
username := request.Attribute(filters.UserName).(string)

subnetService := &service.SubnetService{}
query := query.ParseQueryParameter(request)
result, err := subnetService.ListSubnet(query, v.Db, username)
if err != nil {
api.HandleError(response, request, err)
return
}
response.WriteEntity(result)
}

#yyds干貨盤點#golang實現(xiàn)通過mysql語句實現(xiàn)分頁查詢_分頁_02

4.service實現(xiàn)

//查詢一個用戶所有的subnet信息
func (ss *SubnetService) ListSubnet(query *query.Query, db *sql.DB, userName string) (*api.ListResult, error) {
sm := mapper.NewSubnetMapper(db)
sb, err := sm.SearchAllByUserName(query, userName)
return sb, err
}

5.mapper實現(xiàn)

//查詢用戶下的所以信息
func (s *SubnetMapper) SearchAllByUserName(query *query.Query, userName string) (*api.ListResult, error) {

totalRow, err := s.Db.Query("SELECT COUNT(*) from t_subnet ,t_vpc where t_vpc.id = t_subnet.vpcid and username = ?", userName)
if err != nil {
klog.Error("query orders count error", err)
return nil, err
}
total := 0
for totalRow.Next() {
err := totalRow.Scan(
&total,
)
if err != nil {
klog.Error("query orders count error", err)
continue
}
}
totalRow.Close()

rows, err := s.Db.Query(SearchAllByUserNameSql, userName, query.Pagination.Limit, query.Pagination.Offset)
defer func() {
if rows != nil {
rows.Close()
}
}()
if err != nil {
klog.Error("query subnet error", err)
return nil, err
}
items := make([]interface{}, 0)
for rows.Next() {
ss := new(vpc.SubnetItem)
err = rows.Scan(&ss.VpcName, &ss.VpcNetwork, &ss.SubnetName, &ss.SubnetNetwork, &ss.CreateTime)
items = append(items, *ss)
}
return &api.ListResult{
TotalItems: total,
Items: items,
}, nil
}

可以看到第一步查出了所有count數(shù)目：

#yyds干貨盤點#golang實現(xiàn)通過mysql語句實現(xiàn)分頁查詢_數(shù)據(jù)庫分頁_03