Note on Pre‑reading Rows with MyBatis Cursor
MyBatis's Cursor is a feature that can be used to prevent Out of Memory when you want to read all rows of a large amount of data in batch processing. Compared with ResultHandler, which is used for a similar purpose, a Cursor can be treated as an Iterator, making the code simpler.
Suppose we have the following Mapper definition.
package com.example;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.cursor.Cursor;
@Mapper
public interface DepositWithdrawalMapper {
@Select("SELECT BRANCH_NAME, CUSTOMER_ID, TRANSACTION_TYPE, AMOUNT, TRANSACTION_DATE FROM DEPOSIT_WITHDRAWAL ORDER BY TRANSACTION_DATE, BRANCH_NAME")
Cursor<DepositWithdrawal> findAll();
}
Cursor extends Iterable, so you can loop over it with a for‑each statement.
try (Cursor<DepositWithdrawal> cursor = this.depositWithdrawalMapper.findAll()) {
cursor.forEach(System.out::println);
}
You can also obtain an Iterator via the iterator() method.
try (Cursor<DepositWithdrawal> cursor = this.depositWithdrawalMapper.findAll()) {
Iterator<DepositWithdrawal> iterator = cursor.iterator();
while (iterator.hasNext()) {
DepositWithdrawal data = iterator.next();
System.out.println(data);
}
}
In batch processing, you sometimes need to look at the next row's data to decide how to process the current one. For example, when performing aggregation when a sorted field changes. It would be ideal if this could be done entirely in SQL, but when complex aggregation logic is involved, it must be implemented on the Java side.
Since Iterator does not have a method for peeking, a typical implementation of lookahead involves preparing a variable for the lookahead data, reading the next row with the Iterator obtained from iterator(), and storing it in the variable. This works when there is only one Cursor, but when you need to handle multiple Cursors for aggregation, the code for state management becomes complex and a breeding ground for bugs.
Therefore, by using Guava's PeekingIterator, a peek method is added to the Iterator, simplifying the code.
Below is an example code that retrieves deposit/withdrawal data sorted by date and branch name, and uses Cursor and PeekingIterator to aggregate and display the count and total amount of deposits and withdrawals for each date and branch.
Note
The batch processing example and test data were taken from here.
package com.example;
import com.google.common.collect.Iterators;
import com.google.common.collect.PeekingIterator;
import java.math.BigInteger;
import java.text.DecimalFormat;
import java.time.LocalDate;
import java.util.Objects;
import org.apache.ibatis.cursor.Cursor;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;
@Component
public class Demo implements CommandLineRunner {
private final DepositWithdrawalMapper depositWithdrawalMapper;
public Demo(DepositWithdrawalMapper depositWithdrawalMapper) {
this.depositWithdrawalMapper = depositWithdrawalMapper;
}
@Override
@Transactional
public void run(String... args) throws Exception {
try (Cursor<DepositWithdrawal> cursor = this.depositWithdrawalMapper.findAll()) {
PeekingIterator<DepositWithdrawal> iterator = Iterators.peekingIterator(cursor.iterator());
int depositCount = 0;
int withdrawalCount = 0;
BigInteger totalDeposit = BigInteger.ZERO;
BigInteger totalWithdrawal = BigInteger.ZERO;
System.out.println("+-----------------+--------------+---------------+-----------------+----------------+-------------------+");
System.out.println("| Transaction Date| Branch Name | Deposit Count | Withdrawal Count| Total Deposit | Total Withdrawal |");
System.out.println("+-----------------+--------------+---------------+-----------------+----------------+-------------------+");
DecimalFormat formatter = new DecimalFormat("#,###");
while (iterator.hasNext()) {
DepositWithdrawal data = iterator.next();
Assert.notNull(data, "'data' must not be null");
if (data.isWithdrawal()) {
withdrawalCount++;
totalWithdrawal = totalWithdrawal.add(data.amount());
}
else if (data.isDeposit()) {
depositCount++;
totalDeposit = totalDeposit.add(data.amount());
}
DepositWithdrawal nextData = iterator.hasNext() ? iterator.peek() : null;
boolean isBreak = isDifferentTransactionDate(data, nextData) || isDifferentBranchName(data, nextData);
if (isBreak) {
System.out.printf("| %-15s | %-12s | %13s | %15s | %14s | %17s |%n", data.transactionDate(),
data.branchName(), formatter.format(depositCount), formatter.format(withdrawalCount),
formatter.format(totalDeposit), formatter.format(totalWithdrawal));
depositCount = 0;
withdrawalCount = 0;
totalDeposit = BigInteger.ZERO;
totalWithdrawal = BigInteger.ZERO;
}
}
System.out.println("+-----------------+--------------+---------------+-----------------+----------------+-------------------+");
}
}
boolean isDifferentTransactionDate(DepositWithdrawal data1, DepositWithdrawal data2) {
LocalDate date1 = data1 != null ? data1.transactionDate() : null;
LocalDate date2 = data2 != null ? data2.transactionDate() : null;
return !Objects.equals(date1, date2);
}
boolean isDifferentBranchName(DepositWithdrawal data1, DepositWithdrawal data2) {
String s1 = data1 != null ? data1.branchName() : null;
String s2 = data2 != null ? data2.branchName() : null;
return !Objects.equals(s1, s2);
}
}
The following DDL and
CREATE TABLE DEPOSIT_WITHDRAWAL (
BRANCH_NAME VARCHAR(8),
CUSTOMER_ID VARCHAR(4),
TRANSACTION_TYPE VARCHAR(1),
AMOUNT BIGINT,
TRANSACTION_DATE DATE
);
Prepare the following data.
INSERT INTO DEPOSIT_WITHDRAWAL (BRANCH_NAME, CUSTOMER_ID, TRANSACTION_TYPE, AMOUNT,
TRANSACTION_DATE)
VALUES ('Saitama', '319', '1', 891240, '2011-10-01'),
('Saitama', '173', '1', 420216, '2011-10-01'),
('Chiba', '476', '0', 826811, '2011-10-01'),
('Chiba', '692', '1', 19301, '2011-10-01'),
('Tokyo', '160', '1', 220363, '2011-10-01'),
('Tokyo', '874', '0', 762613, '2011-10-01'),
('Tokyo', '88', '1', 441986, '2011-10-01'),
('Tokyo', '972', '0', 951090, '2011-10-01'),
('Tokyo', '521', '0', 277999, '2011-10-01'),
('Tokyo', '177', '0', 651350, '2011-10-01'),
('Saitama', '555', '1', 453222, '2011-10-02'),
('Chiba', '892', '1', 449405, '2011-10-02'),
('Chiba', '887', '0', 314118, '2011-10-02'),
('Chiba', '336', '1', 805071, '2011-10-02'),
('Tokyo', '850', '0', 748174, '2011-10-02'),
('Tokyo', '286', '1', 462774, '2011-10-02'),
('Tokyo', '260', '0', 606862, '2011-10-02'),
('Tokyo', '756', '0', 15399, '2011-10-02'),
('Tokyo', '770', '1', 802144, '2011-10-02'),
('Tokyo', '491', '0', 89243, '2011-10-02'),
('Saitama', '386', '1', 661562, '2011-10-03'),
('Saitama', '749', '0', 235999, '2011-10-03'),
('Saitama', '180', '0', 613690, '2011-10-03'),
('Saitama', '709', '1', 241846, '2011-10-03'),
('Chiba', '192', '1', 536581, '2011-10-03'),
('Chiba', '688', '1', 860915, '2011-10-03'),
('Tokyo', '930', '0', 374102, '2011-10-03'),
('Tokyo', '745', '0', 958636, '2011-10-03'),
('Tokyo', '241', '0', 68636, '2011-10-03'),
('Tokyo', '803', '0', 937094, '2011-10-03'),
('Saitama', '843', '1', 808066, '2011-10-04'),
('Saitama', '31', '0', 107638, '2011-10-04'),
('Saitama', '592', '1', 627890, '2011-10-04'),
('Saitama', '616', '1', 334311, '2011-10-04'),
('Saitama', '584', '0', 366661, '2011-10-04'),
('Chiba', '23', '0', 915191, '2011-10-04'),
('Chiba', '760', '0', 89511, '2011-10-04'),
('Chiba', '440', '0', 804531, '2011-10-04'),
('Chiba', '51', '1', 978903, '2011-10-04'),
('Tokyo', '56', '0', 763763, '2011-10-04'),
('Saitama', '560', '1', 763545, '2011-10-05'),
('Saitama', '27', '0', 203102, '2011-10-05'),
('Saitama', '565', '1', 285055, '2011-10-05'),
('Chiba', '510', '1', 112097, '2011-10-05'),
('Chiba', '4', '1', 871444, '2011-10-05'),
('Chiba', '554', '1', 614683, '2011-10-05'),
('Chiba', '877', '1', 529944, '2011-10-05'),
('Tokyo', '431', '0', 391908, '2011-10-05'),
('Tokyo', '191', '1', 789341, '2011-10-05'),
('Tokyo', '521', '1', 209756, '2011-10-05');
When you run the code, the count and total amount of deposits and withdrawals are aggregated and displayed by date and branch as follows.
+-----------------+--------------+---------------+-----------------+----------------+-------------------+
| Transaction Date| Branch Name | Deposit Count | Withdrawal Count| Total Deposit | Total Withdrawal |
+-----------------+--------------+---------------+-----------------+----------------+-------------------+
| 2011-10-01 | Chiba | 1 | 1 | 19,301 | 826,811 |
| 2011-10-01 | Saitama | 2 | 0 | 1,311,456 | 0 |
| 2011-10-01 | Tokyo | 2 | 4 | 662,349 | 2,643,052 |
| 2011-10-02 | Chiba | 2 | 1 | 1,254,476 | 314,118 |
| 2011-10-02 | Saitama | 1 | 0 | 453,222 | 0 |
| 2011-10-02 | Tokyo | 2 | 4 | 1,264,918 | 1,459,678 |
| 2011-10-03 | Chiba | 2 | 0 | 1,397,496 | 0 |
| 2011-10-03 | Saitama | 2 | 2 | 903,408 | 849,689 |
| 2011-10-03 | Tokyo | 0 | 4 | 0 | 2,338,468 |
| 2011-10-04 | Chiba | 1 | 3 | 978,903 | 1,809,233 |
| 2011-10-04 | Saitama | 3 | 2 | 1,770,267 | 474,299 |
| 2011-10-04 | Tokyo | 0 | 1 | 0 | 763,763 |
| 2011-10-05 | Chiba | 4 | 0 | 2,128,168 | 0 |
| 2011-10-05 | Saitama | 2 | 1 | 1,048,600 | 203,102 |
| 2011-10-05 | Tokyo | 2 | 1 | 999,097 | 391,908 |
+-----------------+--------------+---------------+-----------------+----------------+-------------------+
That concludes the memo on pre‑reading rows with MyBatis Cursor. Using PeekingIterator allowed for a simple implementation.