example of ResultSetHandler

[Francesco Viscomi]

Hi all,

I'm new to myBatis; I need to implement a way of having my own mapper using ResultSetHandler;

The straight way to call a procedure to pl/sql is:

In java class i have

    

    SqlSession session = getSession();) 

    Dao fMapper = session.getMapper(Dao.class);

so after popolate the map i call

fMapper .cercaR(paramsMap);

and in the paramsMap i've got the result set,

Besides, the cercaR is mapped in the configuration file as 

<select id="cercaR" parameterType="map" statementType="CALLABLE" >

I want a way to performe this call in a better way (i mean the result set is very big, so i need to speed up the time to get the result set). 

Is there a way to improve it? mybe using ResultSetHandler

thanks 

[Erwan Letessier]

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:

fetchSize="500"

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

[Francesco Viscomi]

I'm using 3.3.0; so i cannot use 

fMapper.cercaR(paramsMap, resultHandler);

or I'm wrong?

my new handler is declared 

public class CashSetHandler  implements ResultSetHandler {

}

how i can bind that instead of using the default handler?

[Erwan Letessier]

Indeed you cannot.

You need 3.4.6

I also needed to use custom result handler with procedure out cursor. It has been added after I requested it. In prior versions the resultHandler is just ignored.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Francesco Viscomi]

--
You received this message because you are subscribed to a topic in the Google Groups "mybatis-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mybatis-user/lS7AUxCdkr0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to mybatis-user...@googlegroups.com.