Golang与MySQL Bootstrap实现分页链接的最佳实践

Golang与MySQL Bootstrap实现分页链接的最佳实践 如何实现Go语言与MySQL的分页链接？

<!DOCTYPE html>
<html lang="en">
<head>
  <title>Bootstrap</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
</head>
<body>

<div class="container">
  <h2>分页</h2>
  <p>.pagination 类提供了分页链接：</p>                  
  <ul class="pagination">
    <li><a href="#">1</a></li>
    <li><a href="#">2</a></li>
    <li><a href="#">3</a></li>
    <li><a href="#">4</a></li>
    <li><a href="#">5</a></li>
  </ul>
</div>

</body>
</html>

更多关于Golang与MySQL Bootstrap实现分页链接的最佳实践的实战教程也可以访问 https://www.itying.com/category-94-b0.html

1 回复

更多关于Golang与MySQL Bootstrap实现分页链接的最佳实践的实战系列教程也可以访问 https://www.itying.com/category-94-b0.html


在Go语言中实现MySQL分页并集成Bootstrap分页链接，需要结合后端分页逻辑和前端的Bootstrap分页组件。以下是完整的实现示例：

1. 后端Go代码（MySQL分页逻辑）

package main

import (
    "database/sql"
    "fmt"
    "html/template"
    "log"
    "net/http"
    "strconv"
    _ "github.com/go-sql-driver/mysql"
)

type User struct {
    ID   int
    Name string
}

type Pagination struct {
    CurrentPage int
    TotalPages  int
    HasPrev     bool
    HasNext     bool
    PrevPage    int
    NextPage    int
    Pages       []int
}

func main() {
    http.HandleFunc("/users", usersHandler)
    log.Fatal(http.ListenAndServe(":8080", nil))
}

func usersHandler(w http.ResponseWriter, r *http.Request) {
    // 连接MySQL数据库
    db, err := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/dbname")
    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }
    defer db.Close()

    // 获取当前页码，默认为第1页
    pageStr := r.URL.Query().Get("page")
    page, err := strconv.Atoi(pageStr)
    if err != nil || page < 1 {
        page = 1
    }

    // 每页显示数量
    pageSize := 10

    // 计算偏移量
    offset := (page - 1) * pageSize

    // 查询总记录数
    var totalRecords int
    err = db.QueryRow("SELECT COUNT(*) FROM users").Scan(&totalRecords)
    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }

    // 计算总页数
    totalPages := (totalRecords + pageSize - 1) / pageSize

    // 查询当前页数据
    rows, err := db.Query("SELECT id, name FROM users LIMIT ? OFFSET ?", pageSize, offset)
    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }
    defer rows.Close()

    var users []User
    for rows.Next() {
        var user User
        if err := rows.Scan(&user.ID, &user.Name); err != nil {
            http.Error(w, err.Error(), http.StatusInternalServerError)
            return
        }
        users = append(users, user)
    }

    // 生成分页信息
    pagination := generatePagination(page, totalPages)

    // 渲染模板
    tmpl, err := template.ParseFiles("users.html")
    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }

    data := struct {
        Users      []User
        Pagination Pagination
    }{
        Users:      users,
        Pagination: pagination,
    }

    tmpl.Execute(w, data)
}

func generatePagination(currentPage, totalPages int) Pagination {
    // 计算显示的页码范围（最多显示5个页码）
    startPage := currentPage - 2
    if startPage < 1 {
        startPage = 1
    }
    
    endPage := startPage + 4
    if endPage > totalPages {
        endPage = totalPages
        startPage = endPage - 4
        if startPage < 1 {
            startPage = 1
        }
    }

    // 生成页码列表
    var pages []int
    for i := startPage; i <= endPage; i++ {
        pages = append(pages, i)
    }

    return Pagination{
        CurrentPage: currentPage,
        TotalPages:  totalPages,
        HasPrev:     currentPage > 1,
        HasNext:     currentPage < totalPages,
        PrevPage:    currentPage - 1,
        NextPage:    currentPage + 1,
        Pages:       pages,
    }
}

2. 前端HTML模板（users.html）

<!DOCTYPE html>
<html lang="en">
<head>
    <title>用户列表</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
</head>
<body>

<div class="container">
    <h2>用户列表</h2>
    
    <table class="table table-striped">
        <thead>
            <tr>
                <th>ID</th>
                <th>姓名</th>
            </tr>
        </thead>
        <tbody>
            {{range .Users}}
            <tr>
                <td>{{.ID}}</td>
                <td>{{.Name}}</td>
            </tr>
            {{end}}
        </tbody>
    </table>

    <!-- Bootstrap分页组件 -->
    <nav aria-label="Page navigation">
        <ul class="pagination">
            {{if .Pagination.HasPrev}}
            <li>
                <a href="/users?page={{.Pagination.PrevPage}}" aria-label="Previous">
                    <span aria-hidden="true">&laquo;</span>
                </a>
            </li>
            {{else}}
            <li class="disabled">
                <span aria-hidden="true">&laquo;</span>
            </li>
            {{end}}

            {{range .Pagination.Pages}}
            {{if eq . $.Pagination.CurrentPage}}
            <li class="active"><span>{{.}}</span></li>
            {{else}}
            <li><a href="/users?page={{.}}">{{.}}</a></li>
            {{end}}
            {{end}}

            {{if .Pagination.HasNext}}
            <li>
                <a href="/users?page={{.Pagination.NextPage}}" aria-label="Next">
                    <span aria-hidden="true">&raquo;</span>
                </a>
            </li>
            {{else}}
            <li class="disabled">
                <span aria-hidden="true">&raquo;</span>
            </li>
            {{end}}
        </ul>
    </nav>
    
    <div class="text-center">
        <p>第 {{.Pagination.CurrentPage}} 页，共 {{.Pagination.TotalPages}} 页</p>
    </div>
</div>

</body>
</html>

3. 优化版本：使用结构体封装分页逻辑

// 分页结构体
type Paginator struct {
    Page       int
    PageSize   int
    TotalCount int
    TotalPages int
}

func NewPaginator(page, pageSize, totalCount int) *Paginator {
    totalPages := (totalCount + pageSize - 1) / pageSize
    if page < 1 {
        page = 1
    }
    if page > totalPages {
        page = totalPages
    }
    
    return &Paginator{
        Page:       page,
        PageSize:   pageSize,
        TotalCount: totalCount,
        TotalPages: totalPages,
    }
}

func (p *Paginator) Offset() int {
    return (p.Page - 1) * p.PageSize
}

func (p *Paginator) Limit() int {
    return p.PageSize
}

// 在查询中使用
func getUsersWithPagination(db *sql.DB, page, pageSize int) ([]User, *Paginator, error) {
    var totalCount int
    err := db.QueryRow("SELECT COUNT(*) FROM users").Scan(&totalCount)
    if err != nil {
        return nil, nil, err
    }

    paginator := NewPaginator(page, pageSize, totalCount)

    rows, err := db.Query(
        "SELECT id, name FROM users LIMIT ? OFFSET ?",
        paginator.Limit(),
        paginator.Offset(),
    )
    if err != nil {
        return nil, nil, err
    }
    defer rows.Close()

    var users []User
    for rows.Next() {
        var user User
        if err := rows.Scan(&user.ID, &user.Name); err != nil {
            return nil, nil, err
        }
        users = append(users, user)
    }

    return users, paginator, nil
}

这个实现方案包含了以下关键点：

  1. MySQL分页查询：使用LIMIT和OFFSET实现数据分页
  2. 分页计算：正确计算总页数和当前页的偏移量
  3. Bootstrap集成：使用Bootstrap的pagination组件样式
  4. 模板渲染：通过Go的html/template将分页数据动态渲染到HTML
  5. 分页逻辑封装：使用结构体封装分页参数和计算方法

访问http://localhost:8080/users?page=2即可查看第2页的数据，分页链接会自动根据当前页码和总页数生成相应的Bootstrap分页组件。

回到顶部