Gin + GORM 分页查询 — 通用分页封装

目标

实现一个可复用的 GORM 分页查询封装，支持排序、搜索、自定义条件，是毕设列表接口的标配。

完整代码

package main

import (
	"fmt"
	"math"
	"net/http"
	"strconv"

	"github.com/gin-gonic/gin"
	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
)

// ───── 数据模型 ─────
type Product struct {
	ID          uint    `gorm:"primaryKey" json:"id"`
	Name        string  `gorm:"size:100;index" json:"name"`
	Category    string  `gorm:"size:50;index" json:"category"`
	Price       float64 `json:"price"`
	Stock       int     `json:"stock"`
	Description string  `gorm:"type:text" json:"description"`
}

// ───── 通用分页结构 ─────
type Pagination struct {
	Page       int         `json:"page"`
	Limit      int         `json:"limit"`
	Total      int64       `json:"total"`
	TotalPages int         `json:"totalPages"`
	Data       interface{} `json:"data"`
}

type PageQuery struct {
	Page    int    `form:"page" binding:"omitempty,min=1"`
	Limit   int    `form:"limit" binding:"omitempty,min=1,max=100"`
	Sort    string `form:"sort"`    // 排序字段
	Order   string `form:"order"`   // asc/desc
	Keyword string `form:"keyword"` // 搜索关键词
}

// Paginate 通用分页查询函数
func Paginate(db *gorm.DB, query PageQuery, dest interface{}, searchFields ...string) (*Pagination, error) {
	// 默认值
	if query.Page == 0 {
		query.Page = 1
	}
	if query.Limit == 0 {
		query.Limit = 10
	}
	if query.Order == "" {
		query.Order = "desc"
	}

	q := db.Model(dest)

	// 关键词搜索（多字段 LIKE）
	if query.Keyword != "" {
		keyword := "%" + query.Keyword + "%"
		conditions := make([]string, len(searchFields))
		args := make([]interface{}, len(searchFields))
		for i, field := range searchFields {
			conditions[i] = fmt.Sprintf("%s LIKE ?", field)
			args[i] = keyword
		}
		if len(conditions) > 0 {
			q = q.Where(conditions[0], args[0])
			for i := 1; i < len(conditions); i++ {
				q = q.Or(conditions[i], args[i])
			}
		}
	}

	// 统计总数
	var total int64
	q.Count(&total)

	// 排序
	if query.Sort != "" {
		q = q.Order(fmt.Sprintf("%s %s", query.Sort, query.Order))
	} else {
		q = q.Order("id desc")
	}

	// 分页
	offset := (query.Page - 1) * query.Limit
	q.Limit(query.Limit).Offset(offset).Find(dest)

	return &Pagination{
		Page:       query.Page,
		Limit:      query.Limit,
		Total:      total,
		TotalPages: int(math.Ceil(float64(total) / float64(query.Limit))),
		Data:       dest,
	}, nil
}

// ───── 数据库初始化 ─────
var db *gorm.DB

func initDB() {
	var err error
	db, err = gorm.Open(sqlite.Open("shop.db"), &gorm.Config{})
	if err != nil {
		panic("数据库连接失败: " + err.Error())
	}
	db.AutoMigrate(&Product{})

	// 种子数据
	var count int64
	db.Model(&Product{}).Count(&count)
	if count == 0 {
		categories := []string{"电子产品", "图书", "文具", "运动器材", "生活用品"}
		for i := 1; i <= 50; i++ {
			db.Create(&Product{
				Name:        fmt.Sprintf("商品-%03d", i),
				Category:    categories[i%len(categories)],
				Price:       float64(i*10 + 5),
				Stock:       i * 3,
				Description: fmt.Sprintf("这是第 %d 个商品的描述", i),
			})
		}
		fmt.Println("✅ 已插入 50 条种子数据")
	}
}

// ───── Handler ─────
func listProducts(c *gin.Context) {
	var query PageQuery
	if err := c.ShouldBindQuery(&query); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
		return
	}

	var products []Product
	result, err := Paginate(db, query, &products, "name", "category", "description")
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
		return
	}

	c.JSON(http.StatusOK, result)
}

func main() {
	initDB()
	r := gin.Default()
	r.GET("/api/products", listProducts)
	r.Run(":8080")
}

测试

# 第1页，每页10条
curl "http://localhost:8080/api/products?page=1&limit=10"

# 搜索
curl "http://localhost:8080/api/products?keyword=电子产品"

# 按价格降序
curl "http://localhost:8080/api/products?sort=price&order=desc"

# 组合：搜索 + 分页 + 排序
curl "http://localhost:8080/api/products?keyword=商品&page=2&limit=5&sort=stock&order=asc"

响应示例

{
  "page": 1,
  "limit": 10,
  "total": 50,
  "totalPages": 5,
  "data": [
    {"id": 50, "name": "商品-050", "category": "生活用品", "price": 505, "stock": 150},
    ...
  ]
}

关键要点

• Paginate 函数可复用到任何模型
• searchFields 参数化搜索字段，防 SQL 注入
• 种子数据方便答辩时直接演示分页效果