Mybatis Pagehelper Xugudb Adapter

MyBatis PageHelper 分页插件虚谷数据库适配指南

概述

本技能提供 MyBatis PageHelper 分页插件适配虚谷数据库（XuguDB）的完整配置指南。PageHelper 是一个 MyBatis 的物理分页插件，支持多种数据库，现在可以通过配置支持虚谷数据库。

适用场景：

• MyBatis 项目分页查询
• 列表数据分页展示
• 大数据量分页处理
• 复杂查询分页优化

核心特性：

• 物理分页，内存占用小
• 支持多种数据库
• 支持多种分页方式
• 支持排序和聚合查询
• 支持自定义方言
• 支持 RowBounds 分页
• 支持 PageHelper 自动 Count 查询

快速开始

1. 添加依赖

Maven 配置：

    
    
        org.mybatis
        mybatis
        3.5.13
    
    
    
    
        org.mybatis
        mybatis-spring
        2.1.1
    
    
    
    
        com.github.pagehelper
        pagehelper
        5.3.2
    
    
    
    
        com.xugu
        xugu-jdbc
        12.0.0
    
    
    
    
        org.mybatis.spring.boot
        mybatis-spring-boot-starter
        2.3.1
    
    
    
    
        com.github.pagehelper
        pagehelper-spring-boot-starter
        1.4.6
    

Gradle 配置：

dependencies {
    implementation 'org.mybatis:mybatis:3.5.13'
    implementation 'org.mybatis:mybatis-spring:2.1.1'
    implementation 'com.github.pagehelper:pagehelper:5.3.2'
    implementation 'com.xugu:xugu-jdbc:12.0.0'
    implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.3.1'
    implementation 'com.github.pagehelper:pagehelper-spring-boot-starter:1.4.6'
}

2. 配置 PageHelper

MyBatis 配置文件 mybatis-config.xml：

Spring Boot 配置 application.yml：

# PageHelper 配置
pagehelper:
  helper-dialect: xugudb
  reasonable: true
  support-methods-arguments: true
  params: count=countSql
  page-size-zero: false
  auto-runtime-dialect: true
  auto-dialect: true
  close-conn: true
  count-suffix: _COUNT

# MyBatis 配置
mybatis:
  config-location: classpath:mybatis-config.xml
  mapper-locations: classpath:mapper/**/*.xml
  type-aliases-package: com.example.entity
  configuration:
    map-underscore-to-camel-case: true
    cache-enabled: true
    lazy-loading-enabled: true
    aggressive-lazy-loading: false

3. 创建实体类

package com.example.entity;

import java.time.LocalDateTime;

public class User {
    private Long id;
    private String name;
    private String email;
    private String phone;
    private Integer status;
    private LocalDateTime createdAt;
    private LocalDateTime updatedAt;
    
    // Getters and Setters
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    
    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }
    
    public String getPhone() { return phone; }
    public void setPhone(String phone) { this.phone = phone; }
    
    public Integer getStatus() { return status; }
    public void setStatus(Integer status) { this.status = status; }
    
    public LocalDateTime getCreatedAt() { return createdAt; }
    public void setCreatedAt(LocalDateTime createdAt) { this.createdAt = createdAt; }
    
    public LocalDateTime getUpdatedAt() { return updatedAt; }
    public void setUpdatedAt(LocalDateTime updatedAt) { this.updatedAt = updatedAt; }
}

4. 创建 Mapper 接口

package com.example.mapper;

import com.example.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;

@Mapper
public interface UserMapper {
    
    // 查询所有用户
    @Select("SELECT * FROM users")
    List findAll();
    
    // 根据状态查询用户
    @Select("SELECT * FROM users WHERE status = #{status}")
    List findByStatus(@Param("status") Integer status);
    
    // 根据名称模糊查询
    @Select("SELECT * FROM users WHERE name LIKE CONCAT('%', #{name}, '%')")
    List findByNameLike(@Param("name") String name);
    
    // 复杂查询
    @Select("SELECT * FROM users WHERE status = #{status} AND name LIKE CONCAT('%', #{name}, '%') ORDER BY created_at DESC")
    List findByStatusAndNameLike(@Param("status") Integer status, @Param("name") String name);
    
    // 聚合查询
    @Select("SELECT COUNT(*) FROM users WHERE status = #{status}")
    long countByStatus(@Param("status") Integer status);
}

5. 创建 Mapper XML 文件

UserMapper.xml：

    
    
    
        
        
        
        
        
        
        
    
    
    
    
        SELECT * FROM users
    
    
    
    
        SELECT * FROM users WHERE status = #{status}
    
    
    
    
        SELECT * FROM users WHERE name LIKE CONCAT('%', #{name}, '%')
    
    
    
    
        SELECT * FROM users 
        WHERE status = #{status} 
        AND name LIKE CONCAT('%', #{name}, '%') 
        ORDER BY created_at DESC
    
    
    
    
        SELECT COUNT(*) FROM users WHERE status = #{status}
    
    

分页查询

1. 基础分页查询

使用 PageHelper.startPage()：

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.example.mapper.UserMapper;
import com.example.entity.User;
import java.util.List;

public class UserService {
    
    private UserMapper userMapper;
    
    /**
     * 分页查询用户
     * @param pageNum 页码（从1开始）
     * @param pageSize 每页大小
     * @return 分页结果
     */
    public PageInfo getUsersByPage(int pageNum, int pageSize) {
        // 设置分页参数
        PageHelper.startPage(pageNum, pageSize);
        
        // 执行查询（会被自动分页）
        List users = userMapper.findAll();
        
        // 获取分页信息
        PageInfo pageInfo = new PageInfo<>(users);
        
        return pageInfo;
    }
    
    /**
     * 带条件的分页查询
     * @param status 状态
     * @param name 名称
     * @param pageNum 页码
     * @param pageSize 每页大小
     * @return 分页结果
     */
    public PageInfo getUsersByCondition(Integer status, String name, int pageNum, int pageSize) {
        // 设置分页参数
        PageHelper.startPage(pageNum, pageSize);
        
        // 执行查询
        List users = userMapper.findByStatusAndNameLike(status, name);
        
        // 获取分页信息
        PageInfo pageInfo = new PageInfo<>(users);
        
        return pageInfo;
    }
}

2. 使用 PageInfo 获取分页信息

public void printPageInfo(PageInfo pageInfo) {
    System.out.println("当前页: " + pageInfo.getPageNum());
    System.out.println("每页大小: " + pageInfo.getPageSize());
    System.out.println("总记录数: " + pageInfo.getTotal());
    System.out.println("总页数: " + pageInfo.getPages());
    System.out.println("是否有上一页: " + pageInfo.isHasPreviousPage());
    System.out.println("是否有下一页: " + pageInfo.isHasNextPage());
    System.out.println("上一页页码: " + pageInfo.getPrePage());
    System.out.println("下一页页码: " + pageInfo.getNextPage());
    System.out.println("是否第一页: " + pageInfo.isIsFirstPage());
    System.out.println("是否最后一页: " + pageInfo.isIsLastPage());
    System.out.navigatepageNums: " + java.util.Arrays.toString(pageInfo.getNavigatepageNums()));
    
    // 获取数据列表
    List users = pageInfo.getList();
    for (User user : users) {
        System.out.println("用户: " + user.getName());
    }
}

3. 使用 RowBounds 分页

import org.apache.ibatis.session.RowBounds;
import java.util.List;

public List getUsersByRowBounds(int offset, int limit) {
    RowBounds rowBounds = new RowBounds(offset, limit);
    return userMapper.findAllWithRowBounds(rowBounds);
}

4. 使用 Lambda 表达式

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import java.util.List;

public PageInfo getUsersByLambda(int pageNum, int pageSize) {
    // 使用 Lambda 表达式
    return PageHelper.startPage(pageNum, pageSize)
        .doSelectPageInfo(() -> userMapper.findAll());
}

// 或者使用更简洁的方式
public PageInfo getUsersByLambda2(int pageNum, int pageSize) {
    return PageHelper.startPage(pageNum, pageSize)
        .doSelectPageInfo(() -> userMapper.findByStatus(1));
}

高级配置

1. 自定义虚谷数据库方言

创建自定义方言类：

package com.example.dialect;

import com.github.pagehelper.dialect.helper.AbstractHelperDialect;
import org.apache.ibatis.cache.CacheKey;

public class XuguDBDialect extends AbstractHelperDialect {
    
    @Override
    public String getPageSql(String sql, Page page, CacheKey pageKey) {
        StringBuilder sqlBuilder = new StringBuilder(sql.length() + 14);
        sqlBuilder.append(sql);
        
        if (page.getStartRow() == 0) {
            sqlBuilder.append(" LIMIT ");
            sqlBuilder.append(page.getPageSize());
        } else {
            sqlBuilder.append(" LIMIT ");
            sqlBuilder.append(page.getStartRow());
            sqlBuilder.append(", ");
            sqlBuilder.append(page.getPageSize());
        }
        
        return sqlBuilder.toString();
    }
    
    @Override
    public Object processPageParameter(Mapped ms, Object parameterObject, RowBounds rowBounds, CacheKey pageKey) {
        pageKey.update(rowBounds.getOffset());
        pageKey.update(rowBounds.getLimit());
        return parameterObject;
    }
}

注册自定义方言：

2. 分页合理化配置

// 启用分页合理化
PageHelper.startPage(pageNum, pageSize, true);

// 或者在配置中设置
// reasonable=true 时，如果 pageNum  总页数，查询最后一页

3. 全局配置

Spring Boot 配置：

pagehelper:
  # 数据库方言
  helper-dialect: xugudb
  # 分页合理化
  reasonable: true
  # 是否支持接口参数来传递分页参数
  support-methods-arguments: true
  # 为了支持startPage(Object params)方法
  params: count=countSql
  # 当页数为0时是否查询全部
  page-size-zero: false
  # 是否自动识别数据库方言
  auto-runtime-dialect: true
  # 是否自动检测数据库类型
  auto-dialect: true
  # 关闭连接
  close-conn: true
  # count 查询后缀
  count-suffix: _COUNT

4. 多数据源配置

import com.github.pagehelper.PageInterceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class DataSourceConfig {
    
    @Bean
    public PageInterceptor pageInterceptor() {
        PageInterceptor pageInterceptor = new PageInterceptor();
        Properties properties = new Properties();
        properties.setProperty("helperDialect", "xugudb");
        properties.setProperty("reasonable", "true");
        properties.setProperty("supportMethodsArguments", "true");
        pageInterceptor.setProperties(properties);
        return pageInterceptor;
    }
    
    @Bean
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource, PageInterceptor pageInterceptor) throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setPlugins(pageInterceptor);
        return sessionFactory.getObject();
    }
}

查询优化

1. 避免全表扫描

问题：分页查询时，如果 ORDER BY 字段没有索引，会导致全表扫描。

解决方案：

-- 为排序字段创建索引
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_users_status ON users(status);

-- 使用覆盖索引
CREATE INDEX idx_users_covering ON users(status, created_at, name, email);

2. 优化 COUNT 查询

问题：复杂的 COUNT 查询可能很慢。

解决方案：

// 使用简化的 COUNT 查询
public long countUsers(Integer status) {
    // 使用 @Select 注解的简化查询
    return userMapper.countByStatus(status);
}

// 或者使用缓存
@Cacheable(value = "userCount", key = "#status")
public long countUsersWithCache(Integer status) {
    return userMapper.countByStatus(status);
}

3. 使用子查询优化

问题：大数据量分页时，LIMIT offset, limit 性能差。

解决方案：

    SELECT u.* FROM users u
    INNER JOIN (
        SELECT id FROM users 
        WHERE status = #{status}
        ORDER BY created_at DESC
        LIMIT #{offset}, #{limit}
    ) t ON u.id = t.id

4. 使用游标分页

问题：传统分页在深页时性能差。

解决方案：

// 使用游标分页（基于上一页最后一条记录）
public PageInfo getUsersByCursor(Long lastId, int pageSize) {
    PageHelper.startPage(1, pageSize);
    
    List users = userMapper.findUsersAfterId(lastId);
    
    return new PageInfo<>(users);
}

// Mapper 方法
@Select("SELECT * FROM users WHERE id > #{lastId} ORDER BY id ASC LIMIT #{limit}")
List findUsersAfterId(@Param("lastId") Long lastId);

性能测试

1. 测试脚本

创建性能测试类：

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;

@SpringBootTest
public class PageHelperPerformanceTest {
    
    @Autowired
    private UserMapper userMapper;
    
    @Test
    public void testPaginationPerformance() {
        int[] pageSizes = {10, 20, 50, 100};
        int[] pageNumbers = {1, 10, 100, 1000};
        
        for (int pageSize : pageSizes) {
            for (int pageNum : pageNumbers) {
                long startTime = System.currentTimeMillis();
                
                PageHelper.startPage(pageNum, pageSize);
                List users = userMapper.findAll();
                PageInfo pageInfo = new PageInfo<>(users);
                
                long endTime = System.currentTimeMillis();
                long duration = endTime - startTime;
                
                System.out.printf("页码: %d, 每页大小: %d, 耗时: %d ms, 总记录数: %d%n",
                    pageNum, pageSize, duration, pageInfo.getTotal());
            }
        }
    }
    
    @Test
    public void testCountPerformance() {
        long startTime = System.currentTimeMillis();
        
        long count = userMapper.countByStatus(1);
        
        long endTime = System.currentTimeMillis();
        long duration = endTime - startTime;
        
        System.out.printf("COUNT 查询耗时: %d ms, 结果: %d%n", duration, count);
    }
}

2. 性能监控

添加性能监控：

import com.github.pagehelper.PageInterceptor;
import org.apache.ibatis.plugin.Interceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MyBatisConfig {
    
    @Bean
    public Interceptor pageInterceptor() {
        PageInterceptor pageInterceptor = new PageInterceptor();
        Properties properties = new Properties();
        properties.setProperty("helperDialect", "xugudb");
        properties.setProperty("reasonable", "true");
        properties.setProperty("supportMethodsArguments", "true");
        
        // 启用性能监控
        properties.setProperty("countSuffix", "_COUNT");
        properties.setProperty("autoRuntimeDialect", "true");
        
        pageInterceptor.setProperties(properties);
        return pageInterceptor;
    }
}

集成测试

1. 单元测试

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.mockito.InjectMocks;
import org.mockito.Mock;
import org.mockito.junit.jupiter.MockitoExtension;
import java.util.Arrays;
import java.util.List;

@ExtendWith(MockitoExtension.class)
public class UserServiceTest {
    
    @Mock
    private UserMapper userMapper;
    
    @InjectMocks
    private UserService userService;
    
    private List mockUsers;
    
    @BeforeEach
    void setUp() {
        mockUsers = Arrays.asList(
            createUser(1L, "张三", "zhangsan@example.com"),
            createUser(2L, "李四", "lisi@example.com"),
            createUser(3L, "王五", "wangwu@example.com")
        );
    }
    
    @Test
    void testGetUsersByPage() {
        // 模拟 Mapper 返回
        when(userMapper.findAll()).thenReturn(mockUsers);
        
        // 执行分页查询
        PageInfo pageInfo = userService.getUsersByPage(1, 10);
        
        // 验证结果
        assertNotNull(pageInfo);
        assertEquals(3, pageInfo.getList().size());
        assertEquals(1, pageInfo.getPageNum());
        assertEquals

…

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [kourou25](https://github.com/kourou25)
- **Source:** [kourou25/xugudb-dev-skills](https://github.com/kourou25/xugudb-dev-skills)
- **License:** Apache-2.0

Install and usage instructions live in the source repository linked above.