本文目录导读：
我来为你详细讲解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();
}
}
}
分页性能优化建议
- 避免大数据量偏移：对于深分页，使用子查询优化
- 添加索引：在排序字段上加索引
- 使用覆盖索引：减少回表查询
- 限制查询字段：只查询需要的列
-- 深分页优化示例
SELECT * FROM users
WHERE id > (
SELECT id FROM users
ORDER BY id
LIMIT 10000, 1
)
LIMIT 20;
掌握了这些分页实现,你就能够在各种Java项目中灵活应用数据库分页功能了。
