Implementing Streaming Reads with MyBatis to Export Large Datasets

Background: The company needed to export reports from an old system, but when the data volume exceeded ten thousand rows the export failed due to memory spikes caused by the default JDBC fetch mode.

Analysis showed the legacy system used the default "fetch all at once" mode. The solution was to switch to a streaming read approach to reduce memory consumption.

JDBC three read modes:

All at once (default): retrieve the entire result set in one go.

Streaming: retrieve rows one by one.

Cursor: retrieve multiple rows per round.

MyBatis uses the first mode by default.

Development environment:

JDK 1.8, IntelliJ IDEA 2018, MyBatis 3, Spring MVC, Spring 4.

Implementation steps

The author adopts a layered architecture (controller → service → DAO) and modifies the service layer to accept a ResultHandler<> callback, while the DAO method returns void. The mapper XML is configured with resultSetType="FORWARD_ONLY" and fetchSize="-2147483648" to enable streaming.

In the service layer, add a ResultHandler<> parameter to the DAO call.

The DAO interface returns void because the result is processed via the callback.

In the mapper, set resultSetType="FORWARD_ONLY" and fetchSize="-2147483648" and define a resultMap for the callback.

Even though the DAO returns void, the mapper still needs a resultMap because the callback processes the rows internally.

Example code

Controller layer:

@RequestMapping("/export")
public void export(Vo vo, String props, HttpServletResponse response) {
    // ...
    list = ossVipCustomService.selectForwardOnly(vo, Order.build());
    // ...
}

Service layer (key part):

public List<Bo> selectForwardOnly(Vo vo, Order order) {
    final List<Bo> list = new ArrayList<>();
    mapper.selectForwardOnly(vo, order, new ResultHandler<Bo>() {
        @Override
        public void handleResult(ResultContext<? extends Bo> resultContext) {
            // callback processing logic
            list.add(resultContext.getResultObject());
        }
    });
    return list;
}

DAO layer (key part):

/**
 * Stream read data
 * @param vo query object
 * @param order sorting
 * @param handler callback handler
 */
void selectForwardOnly(@Param("record") Vo vo, @Param("order") Order order, ResultHandler<Bo> handler);

Mapper XML (key part):

<select id="selectForwardOnly"
        parameterType="com.*.Vo" resultMap="GetListBo"
        resultSetType="FORWARD_ONLY" fetchSize="-2147483648">
    SELECT * FROM customer
</select>

The author notes that the DAO should not return a value; the processing is done via the callback, which avoids returning large result sets to the service layer.

Personal reflections

The author spent considerable time researching why the DAO interface should not return a value and consulted senior colleagues. Adjustments to MySQL connection parameters and MyBatis settings were attempted but did not resolve the issue.