Hi
You may do this with XML or annotations as you like.
The mapping of the cursor will look like:
#{targetName, jdbcType=CURSOR, javaType=java.sql.ResultSet, resultMap=CercarResultMap, mode=OUT}
"Cercar" being a java type to map the resultSet columns.
when you execute fMapper.cercaR(paramsMap);
paramsMap will eventually contain an entry "targetName" => List<Cercar> that will contain all the results (millions?), and only when result Set is fetched to the end, you can iterate over the list to do what you have to do.
From Mybatis latest release (3.4.6 / March 12th) on, you can pass a ResultHandler when calling a procedure having an out cursor, so the call would be:
fMapper.cercaR(paramsMap, resultHandler);
with resultHandler = new ResultHandler<Cercar>() that you have to implement to do directly what you want to with results (e.g: transform, write in a stream, whatever )
For each resultset row, Mybatis maps a Cercar that is passed to the resultHandler.
"targetName" becomes a dummy property since there is no more target to save the list, ... or one is known by the resutlHandler.
You save some time and memory.
Another axis to improve speed: as you work with Oracle, note that the driver's default fetch size is 10, then application suffers significant overhead because a lot of I/O round trips to the DB.
The value shall be increased:
either in the statement:
of globally
<setting name="defaultFetchSize" value="500"/>
The value depends on the needs, but I made a test once, here was an order of magnitude, fetching same query with different fetchSize:
- fetchSize=1 => 13000 ms
- fetchSize=10 => 5300 ms
- fetchSize=100 => 3800 ms
- fetchSize=300 => 3700 ms
- fetchSize=500 => 3650 ms
- fetchSize=1000 => 3600 ms
cheers
Erwan