Java数据库分页案例怎么实操

本文目录导读：

1. MySQL分页实现
2. Oracle分页实现
3. 高级分页：带条件查询
4. 通用分页工具类
5. 分页性能优化建议

我来为你详细讲解Java数据库分页的实操案例,覆盖MySQL、Oracle两种主流数据库：

MySQL分页实现

MySQL使用LIMIT关键字实现分页，语法简单：

SELECT * FROM table_name 
LIMIT offset, pageSize;
-- 或
SELECT * FROM table_name 
LIMIT pageSize OFFSET offset;

基础分页查询

public class PaginationExample {
    // 分页查询方法
    public List<User> getUsersByPage(int pageNum, int pageSize) {
        String sql = "SELECT * FROM users " +
                     "LIMIT ?, ?";
        int offset = (pageNum - 1) * pageSize; // 计算偏移量
        try (Connection conn = DBUtils.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, offset);
            ps.setInt(2, pageSize);
            ResultSet rs = ps.executeQuery();
            List<User> userList = new ArrayList<>();
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setAge(rs.getInt("age"));
                userList.add(user);
            }
            return userList;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    // 获取总记录数
    public int getTotalCount() {
        String sql = "SELECT COUNT(*) FROM users";
        try (Connection conn = DBUtils.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                return rs.getInt(1);
            }
            return 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }
}

完整分页工具类

public class PageResult<T> {
    private int pageNum;      // 当前页码
    private int pageSize;     // 每页条数
    private int totalPages;   // 总页数
    private long totalCount;  // 总记录数
    private List<T> list;     // 当前页数据
    // getter/setter方法
    public int getPageNum() { return pageNum; }
    public void setPageNum(int pageNum) { this.pageNum = pageNum; }
    public int getPageSize() { return pageSize; }
    public void setPageSize(int pageSize) { this.pageSize = pageSize; }
    public int getTotalPages() { 
        // 计算总页数
        return (int) Math.ceil((double) totalCount / pageSize);
    }
    public long getTotalCount() { return totalCount; }
    public void setTotalCount(long totalCount) { this.totalCount = totalCount; }
    public List<T> getList() { return list; }
    public void setList(List<T> list) { this.list = list; }
    // 是否有上一页
    public boolean isHasPrevious() {
        return pageNum > 1;
    }
    // 是否有下一页
    public boolean isHasNext() {
        return pageNum < getTotalPages();
    }
}
// 分页服务实现
public class UserService {
    public PageResult<User> findUsersByPage(int pageNum, int pageSize) {
        PageResult<User> pageResult = new PageResult<>();
        try {
            // 1. 获取总记录数
            int totalCount = getTotalCount();
            pageResult.setTotalCount(totalCount);
            // 2. 查询当前页数据
            List<User> userList = getUsersByPage(pageNum, pageSize);
            pageResult.setList(userList);
            // 3. 设置分页参数
            pageResult.setPageNum(pageNum);
            pageResult.setPageSize(pageSize);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return pageResult;
    }
}

Oracle分页实现

Oracle使用ROWNUM实现分页，需要三层嵌套：

SELECT * FROM (
    SELECT t.*, ROWNUM AS rn FROM (
        SELECT * FROM users ORDER BY id
    ) t WHERE ROWNUM <= ?
) WHERE rn > ?

Oracle分页代码

public class OraclePaginationExample {
    // Oracle分页查询
    public List<User> getUsersByPageOracle(int pageNum, int pageSize) {
        String sql = "SELECT * FROM (" +
                     "    SELECT t.*, ROWNUM AS rn FROM (" +
                     "        SELECT * FROM users ORDER BY id" +
                     "    ) t WHERE ROWNUM <= ?" +
                     ") WHERE rn > ?";
        int end = pageNum * pageSize;          // 结束行号
        int start = (pageNum - 1) * pageSize;  // 开始行号
        try (Connection conn = DBUtils.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, end);
            ps.setInt(2, start);
            ResultSet rs = ps.executeQuery();
            List<User> userList = new ArrayList<>();
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setAge(rs.getInt("age"));
                userList.add(user);
            }
            return userList;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
}

高级分页：带条件查询

public class AdvancedPagination {
    // 带条件的分页查询
    public PageResult<User> findUsersWithCondition(
            String name, int age, int pageNum, int pageSize) {
        StringBuilder countSql = new StringBuilder("SELECT COUNT(*) FROM users WHERE 1=1 ");
        StringBuilder querySql = new StringBuilder("SELECT * FROM users WHERE 1=1 ");
        List<Object> params = new ArrayList<>();
        // 动态添加查询条件
        if (name != null && !name.trim().isEmpty()) {
            countSql.append(" AND name LIKE ?");
            querySql.append(" AND name LIKE ?");
            params.add("%" + name + "%");
        }
        if (age > 0) {
            countSql.append(" AND age = ?");
            querySql.append(" AND age = ?");
            params.add(age);
        }
        // 添加排序和分页
        querySql.append(" ORDER BY id DESC LIMIT ?, ?");
        int offset = (pageNum - 1) * pageSize;
        params.add(offset);
        params.add(pageSize);
        PageResult<User> pageResult = new PageResult<>();
        try (Connection conn = DBUtils.getConnection()) {
            // 查询总记录数
            try (PreparedStatement countPs = conn.prepareStatement(countSql.toString())) {
                for (int i = 0; i < params.size() - 2; i++) {
                    countPs.setObject(i + 1, params.get(i));
                }
                ResultSet countRs = countPs.executeQuery();
                if (countRs.next()) {
                    pageResult.setTotalCount(countRs.getInt(1));
                }
            }
            // 查询当前页数据
            try (PreparedStatement queryPs = conn.prepareStatement(querySql.toString())) {
                for (int i = 0; i < params.size(); i++) {
                    queryPs.setObject(i + 1, params.get(i));
                }
                ResultSet rs = queryPs.executeQuery();
                List<User> userList = new ArrayList<>();
                while (rs.next()) {
                    User user = new User();
                    user.setId(rs.getInt("id"));
                    user.setName(rs.getString("name"));
                    user.setAge(rs.getInt("age"));
                    userList.add(user);
                }
                pageResult.setList(userList);
            }
            pageResult.setPageNum(pageNum);
            pageResult.setPageSize(pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pageResult;
    }
}

通用分页工具类

public class JdbcPaginationUtil {
    // 通用分页方法（MySQL）
    public static <T> PageResult<T> paginate(
            Connection conn,
            String baseSql,
            Object[] params,
            int pageNum,
            int pageSize,
            RowMapper<T> rowMapper) throws SQLException {
        PageResult<T> pageResult = new PageResult<>();
        // 构建计数SQL
        String countSql = "SELECT COUNT(*) FROM (" + baseSql + ") t";
        // 构建分页SQL
        String pageSql = baseSql + " LIMIT ?, ?";
        // 查询总数
        try (PreparedStatement countPs = conn.prepareStatement(countSql)) {
            for (int i = 0; i < params.length; i++) {
                countPs.setObject(i + 1, params[i]);
            }
            ResultSet countRs = countPs.executeQuery();
            if (countRs.next()) {
                pageResult.setTotalCount(countRs.getInt(1));
            }
        }
        // 查询数据
        try (PreparedStatement pagePs = conn.prepareStatement(pageSql)) {
            int offset = (pageNum - 1) * pageSize;
            // 设置原有参数
            for (int i = 0; i < params.length; i++) {
                pagePs.setObject(i + 1, params[i]);
            }
            // 设置分页参数
            pagePs.setInt(params.length + 1, offset);
            pagePs.setInt(params.length + 2, pageSize);
            ResultSet rs = pagePs.executeQuery();
            List<T> list = new ArrayList<>();
            while (rs.next()) {
                list.add(rowMapper.mapRow(rs));
            }
            pageResult.setList(list);
        }
        pageResult.setPageNum(pageNum);
        pageResult.setPageSize(pageSize);
        return pageResult;
    }
    // RowMapper接口
    public interface RowMapper<T> {
        T mapRow(ResultSet rs) throws SQLException;
    }
}
// 使用示例
class TestPagination {
    public static void main(String[] args) {
        try (Connection conn = DBUtils.getConnection()) {
            // 定义查询SQL
            String sql = "SELECT * FROM users WHERE age > ?";
            Object[] params = {18};
            // 执行分页查询
            PageResult<User> result = JdbcPaginationUtil.paginate(
                conn,
                sql,
                params,
                1,      // 第1页
                10,     // 每页10条
                rs -> {  // RowMapper实现
                    User user = new User();
                    user.setId(rs.getInt("id"));
                    user.setName(rs.getString("name"));
                    return user;
                }
            );
            // 使用分页结果
            System.out.println("总记录数：" + result.getTotalCount());
            System.out.println("总页数：" + result.getTotalPages());
            System.out.println("当前页数据：" + result.getList());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

分页性能优化建议

1. 避免大数据量偏移：对于深分页，使用子查询优化
2. 添加索引：在排序字段上加索引
3. 使用覆盖索引：减少回表查询
4. 限制查询字段：只查询需要的列

-- 深分页优化示例
SELECT * FROM users 
WHERE id > (
    SELECT id FROM users 
    ORDER BY id 
    LIMIT 10000, 1
)
LIMIT 20;

掌握了这些分页实现,你就能够在各种Java项目中灵活应用数据库分页功能了。