JDBC使用游标实现分页查询的方法

介绍

JDBC是Java Database Connectivity的简称，是Java语言中用于访问关系型数据库的API，是Java程序员以及开发人员必须掌握的技能之一。本文将讲解如何使用JDBC实现分页查询。

步骤

1. 获取数据库连接

Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String url = "jdbc:mysql://localhost:3306/test";
String username = "root";
String password = "root";
try {
    // 注册驱动
    Class.forName("com.mysql.jdbc.Driver");
    // 获取连接
    conn = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
    e.printStackTrace();
} catch (SQLException e) {
    e.printStackTrace();
}

1. 创建Statement对象

try {
    stmt = conn.createStatement();
} catch (SQLException e) {
    e.printStackTrace();
}

1. 根据分页参数拼接SQL语句

int pageSize = 10;
int currentPage = 1;
int startRow = (currentPage - 1) * pageSize;
String sql = "SELECT * FROM table LIMIT " + startRow + "," + pageSize;

1. 执行查询

try {
    rs = stmt.executeQuery(sql);
} catch (SQLException e) {
    e.printStackTrace();
}

1. 遍历查询结果

try {
    while (rs.next()) {
        int id = rs.getInt("id");
        String name = rs.getString("name");
        String email = rs.getString("email");
        // 输出查询结果
        System.out.println("id=" + id + ", name=" + name + ", email=" + email);
    }
} catch (SQLException e) {
    e.printStackTrace();
}

示例1：分页查询第1页的数据

int pageSize = 10;
int currentPage = 1;
int startRow = (currentPage - 1) * pageSize;
String sql = "SELECT * FROM table LIMIT " + startRow + "," + pageSize;
try {
    rs = stmt.executeQuery(sql);
    while (rs.next()) {
        int id = rs.getInt("id");
        String name = rs.getString("name");
        String email = rs.getString("email");
        // 输出查询结果
        System.out.println("id=" + id + ", name=" + name + ", email=" + email);
    }
} catch (SQLException e) {
    e.printStackTrace();
}

示例2：分页查询第2页的数据

int pageSize = 10;
int currentPage = 2;
int startRow = (currentPage - 1) * pageSize;
String sql = "SELECT * FROM table LIMIT " + startRow + "," + pageSize;
try {
    rs = stmt.executeQuery(sql);
    while (rs.next()) {
        int id = rs.getInt("id");
        String name = rs.getString("name");
        String email = rs.getString("email");
        // 输出查询结果
        System.out.println("id=" + id + ", name=" + name + ", email=" + email);
    }
} catch (SQLException e) {
    e.printStackTrace();
}

总结

本文讲述了如何使用JDBC实现分页查询，包括获取数据库连接、创建Statement对象、拼接SQL语句、执行查询和遍历查询结果等步骤。希望这篇文章能够帮助到需要使用JDBC进行分页查询的开发人员。