mysql实现分页查询
峰啊疯了 人气:01.前端接口调用
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.解析参数
//查询某个用户下所有的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) }
4.service实现
//查询一个用户所有的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实现
//查询用户下的所以信息 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数目:
查询语句加上参数。控制查到的页数。
返回的是总数量跟当前查询页。
特殊情况用不到数据库分页的,就需要用到内存分页了,下期小编给大家讲解内存分页。
加载全部内容