使用 JDBC 分頁
一、簡介
大量的表讀取可能會導致我們的應用程式記憶體不足。它們還會給資料庫增加額外的負載，並且需要更多的頻寬來執行。讀取大型表時建議的方法是使用分頁查詢。本質上，我們讀取數據的子集（頁面），處理數據，然後移動到下一頁。
在本文中，我們將討論並實作使用 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 分頁
我們的資料集包含大約 1M 筆記錄，同時查詢所有記錄不僅會給資料庫帶來壓力，還會對頻寬帶來壓力，因為在給定時刻需要傳輸更多資料。此外，它也給我們的記憶體應用程式空間帶來壓力，因為 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. 測試
讓我們透過編寫簡單的單元測試來測試我們的邏輯。為了進行測試，我們將設定一個資料庫並向表中插入 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()方法首先建立一個employees表並插入 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 ，它們將幫助我們定義循環的迭代視窗。然後，我們斷言每個頁面的最後讀取記錄和總頁面大小。
5. 結論
在本文中，我們討論了批量讀取大型資料集，而不是在一個查詢中讀取所有資料集。我們討論並實作了兩種方法以及驗證工作的單元測試。
對於大型資料集， LIMIT和OFFSET方法可能會變得效率低下，因為它們讀取所有由OFFSET位置定義的行並跳過，而排序鍵方法則有效，因為它僅使用也已索引的排序鍵查詢相關數據。
與往常一樣，範例程式碼可以在 GitHub 上取得。