1. 概述
大数据表读取可能会导致我们的应用耗尽内存，并给数据库带来额外负载，执行时需要更大的带宽。推荐的策略是在读取大表时使用分页查询。基本上，我们一次处理数据的一部分（一页），然后移动到下一页。
在这篇文章中，我们将讨论并实现使用JDBC的分页策略。
2. 准备工作
首先，我们需要在pom.xml文件中添加适当的JDBC依赖，以便连接到我们的数据库。例如，如果我们的数据库是PostgreSQL，我们需要添加PostgreSQL依赖：
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.6.0</version>
</dependency>
其次，我们需要一个大型数据集来进行分页查询。让我们创建一个employees表，并向其中插入一百万条记录：
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
salary DECIMAL(10, 2)
);
INSERT INTO employees (first_name, last_name, salary)
SELECT
'FirstName' || series_number,
'LastName' || series_number,
(random() * 100000)::DECIMAL(10, 2) -- Adjust the range as needed
FROM generate_series(1, 1000000) as series_number;
最后，在示例应用程序内部创建一个连接对象，并用我们的数据库连接进行配置：
Connection connect() throws SQLException {
Connection connection = DriverManager.getConnection(url, user, password);
if (connection != null) {
System.out.println("Connected to database");
}
return connection;
}
3. 使用JDBC进行分页
我们的数据集包含大约100万条记录，一次性查询所有数据不仅会给数据库带来压力，还会因为需要传输更多数据而增加带宽压力。此外，它还会对内存中的应用程序空间造成压力，因为需要在RAM中容纳更多的数据。当处理大容量数据时，始终建议按页面或批次读取和处理。
JDBC并没有提供现成的方法来按页面读取，但我们可以通过自己实现一些方法。我们将讨论并实现两种这样的方法。
3.1. 使用LIMIT和OFFSET
我们可以使用*LIMIT和OFFSET与查询语句一起返回定义的结果数量。LIMIT子句获取我们想要返回的行数，而OFFSET*子句则跳过查询结果中定义的行数。然后，我们可以通过控制OFFSET位置来分页查询。
以下逻辑中，我们定义了LIMIT为pageSize，offset为读取记录的起始位置：
ResultSet readPageWithLimitAndOffset(Connection connection, int offset, int pageSize) throws SQLException {
String sql = """
SELECT * FROM employees
LIMIT ? OFFSET ?
""";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, pageSize);
preparedStatement.setInt(2, offset);
return preparedStatement.executeQuery();
}
查询结果是一组数据。为了按页面读取整个表，我们需要遍历每一页，处理每一页的记录，然后移到下一页。
3.2. 使用排序键与LIMIT
我们还可以利用排序键与LIMIT按批次读取结果。例如，在我们的employees表中，有一个名为ID的自动递增列，并为其设置了索引。我们将使用这个ID列作为页面的下限，而LIMIT将帮助我们设置页面的上限：
ResultSet readPageWithSortedKeys(Connection connection, int lastFetchedId, int pageSize) throws SQLException {
String sql = """
SELECT * FROM employees
WHERE id > ? LIMIT ?
""";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, lastFetchedId);
preparedStatement.setInt(2, pageSize);
return preparedStatement.executeQuery();
}
如上所示，我们在逻辑中将lastFetchedId作为页面的下限，pageSize是我们使用LIMIT设置的上限。
4. 测试
让我们通过编写简单的单元测试来测试我们的逻辑。为了测试，我们将设置一个数据库，并向表中插入100万条记录。我们在每个测试类中运行setup()和tearDown()方法，用于设置测试数据和清理：
@BeforeAll
public static void setup() throws Exception {
connection = connect(JDBC_URL, USERNAME, PASSWORD);
populateDB();
}
@AfterAll
public static void tearDown() throws SQLException {
destroyDB();
}
populateDB()方法首先创建employees表，并插入100万个员工的样例记录：
private static void populateDB() throws SQLException {
String createTable = """
CREATE TABLE EMPLOYEES (
id SERIAL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
salary DECIMAL(10, 2)
);
""";
PreparedStatement preparedStatement = connection.prepareStatement(createTable);
preparedStatement.execute();
String load = """
INSERT INTO EMPLOYEES (first_name, last_name, salary)
VALUES(?,?,?)
""";
IntStream.rangeClosed(1,1_000_000).forEach(i-> {
PreparedStatement preparedStatement1 = null;
try {
preparedStatement1 = connection.prepareStatement(load);
preparedStatement1.setString(1,"firstname"+i);
preparedStatement1.setString(2,"lastname"+i);
preparedStatement1.setDouble(3, 100_000+(1_000_000-100_000)+Math.random());
preparedStatement1.execute();
} catch (SQLException e) {
throw new RuntimeException(e);
}
});
}
我们的tearDown()方法会销毁employees表：
private static void destroyDB() throws SQLException {
String destroy = """
DROP table EMPLOYEES;
""";
connection
.prepareStatement(destroy)
.execute();
}
设置好测试数据后，我们可以编写一个简单的单元测试，验证LIMIT和OFFSET方法的分页大小：
@Test
void givenDBPopulated_WhenReadPageWithLimitAndOffset_ThenReturnsPaginatedResult() throws SQLException {
int offset = 0;
int pageSize = 100_000;
int totalPages = 0;
while (true) {
ResultSet resultSet = PaginationLogic.readPageWithLimitAndOffset(connection, offset, pageSize);
if (!resultSet.next()) {
break;
}
List<String> resultPage = new ArrayList<>();
do {
resultPage.add(resultSet.getString("first_name"));
} while (resultSet.next());
assertEquals("firstname" + (resultPage.size() * (totalPages + 1)), resultPage.get(resultPage.size() - 1));
offset += pageSize;
totalPages++;
}
assertEquals(10, totalPages);
}
如上所示，我们还循环遍历所有数据库记录，按页面读取，对每一页的最后一条记录进行验证。
同样，我们可以编写另一个测试，用于使用排序键（如ID列）进行分页：
@Test
void givenDBPopulated_WhenReadPageWithSortedKeys_ThenReturnsPaginatedResult() throws SQLException {
PreparedStatement preparedStatement = connection.prepareStatement("SELECT min(id) as min_id, max(id) as max_id FROM employees");
ResultSet resultSet = preparedStatement.executeQuery();
resultSet.next();
int minId = resultSet.getInt("min_id");
int maxId = resultSet.getInt("max_id");
int lastFetchedId = 0; // assign lastFetchedId to minId
int pageSize = 100_000;
int totalPages = 0;
while ((lastFetchedId + pageSize) <= maxId) {
resultSet = PaginationLogic.readPageWithSortedKeys(connection, lastFetchedId, pageSize);
if (!resultSet.next()) {
break;
}
List<String> resultPage = new ArrayList<>();
do {
resultPage.add(resultSet.getString("first_name"));
lastFetchedId = resultSet.getInt("id");
} while (resultSet.next());
assertEquals("firstname" + (resultPage.size() * (totalPages + 1)), resultPage.get(resultPage.size() - 1));
totalPages++;
}
assertEquals(10, totalPages);
}
如上所述，我们按顺序遍历整个表，一次读取一页数据。我们找到minId和maxId，它们将帮助我们定义循环的迭代窗口。然后，我们对每一页的最后一条记录进行断言，以及总页数。
5. 总结
在这篇文章中，我们讨论了如何以批次而不是一次性读取大量数据。我们讨论并实现了两种方法，并编写了单元测试来验证其工作原理。
对于大型数据集，LIMIT和OFFSET方法可能效率不高，因为它们会读取所有行，然后根据OFFSET位置跳过，而使用排序键的方法更有效，因为它仅使用已排序且索引的键查询相关数据。
如往常一样，示例代码可以在GitHub上找到。