大量的表读取可能会导致我们的应用程序内存不足。它们还会给数据库增加额外的负载，并且需要更多的带宽来执行。读取大型表时推荐的方法是使用分页查询。本质上，我们读取数据的子集（页面），处理数据，然后移动到下一页。
在本文中，我们将讨论并实现使用JDBC进行分页的不同策略。
首先，我们需要在pom.xml文件中根据我们的数据库添加适当的 JDBC 依赖项，以便我们可以连接到我们的数据库。例如，如果我们的数据库是PostgreSQL，我们需要添加PostgreSQL依赖：
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.6.0</version>
</dependency>
|
其次，我们需要一个大型数据集来进行分页查询。让我们创建一个员工表并向其中插入一百万条记录：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;
}
|
使用 JDBC 分页
我们的数据集包含大约 1M 条记录，同时查询所有记录不仅会给数据库带来压力，还会给带宽带来压力，因为在给定时刻需要传输更多数据。此外，它还给我们的内存应用程序空间带来压力，因为 RAM 中需要容纳更多数据。在读取大型数据集时，始终建议按页或批量读取和处理。
JDBC 不提供开箱即用的方法来读取页面，但是我们可以自己实现一些方法。我们将讨论并实施两种这样的方法。
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();
}
|
查询结果是单页数据。为了在分页中读取整个表，我们迭代每个页面，处理每个页面的记录，然后移动到下一页。2.使用带 LIMIT 的排序键
我们还可以利用带有LIMIT 的排序键来批量读取结果。例如，在我们的员工表中，我们有一个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设置的上限。测试
让我们通过编写简单的单元测试来测试我们的逻辑。为了进行测试，我们将设置一个数据库并向表中插入 1M 条记录。我们为每个测试类运行一次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 ()方法首先创建一个员工表并插入 1M 员工的示例记录：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 ，它们将帮助我们定义循环的迭代窗口。然后，我们断言每个页面的最后读取记录和总页面大小。结论
在本文中，我们讨论了批量读取大型数据集，而不是在一个查询中读取所有数据集。我们讨论并实施了两种方法以及验证工作的单元测试。
对于大型数据集， LIMIT和OFFSET方法可能会变得效率低下，因为它们读取由OFFSET位置定义的所有行并跳过，而排序键方法则高效，因为它仅使用也已索引的排序键查询相关数据。