example of ResultSetHandler

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

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 

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: example of ResultSetHandler

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

On Thursday, March 22, 2018 at 6:05:15 PM UTC+1, Francesco Viscomi wrote:
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 

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: example of ResultSetHandler

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?


Il giorno giovedì 22 marzo 2018 22:50:20 UTC+1, Erwan Letessier ha scritto:
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

On Thursday, March 22, 2018 at 6:05:15 PM UTC+1, Francesco Viscomi wrote:
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 

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: example of ResultSetHandler

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.

On Fri, Mar 23, 2018, 15:12 Francesco Viscomi <[hidden email]> wrote:
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?


Il giorno giovedì 22 marzo 2018 22:50:20 UTC+1, Erwan Letessier ha scritto:
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

On Thursday, March 22, 2018 at 6:05:15 PM UTC+1, Francesco Viscomi wrote:
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 

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: example of ResultSetHandler

Francesco Viscomi
In reply to this post by Francesco Viscomi

Il gio 22 mar 2018, 18:05 Francesco Viscomi <[hidden email]> ha scritto:
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 

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.