You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Tomáš Procházka <t....@centrum.cz> on 2010/01/17 00:54:17 UTC
read large result set in Ibatis3
Hi.
I need read large result set from DB (like 300 000 rows).
It's possible use this:
@Select({"SELECT * FROM send)
List<Send> getAllItems();
?
but instead of read all rows to List use server cursor and read only items which will be accessed by Iterator.
I found some old article about this problem like:
http://stackoverflow.com/questions/1344362/java-retrieving-large-amounts-of-data-from-a-db-using-ibatis
But I don't know, that this is for iBatis 3.0.
Exist same ResultHandler implementation that can do this?
--
Tomáš Procházka
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
R: read large result set in Ibatis3
Posted by Davide Rogora <dr...@unionefiduciaria.it>.
> Also, the JasperReports example mentioned earlier in the thread could be
part of a
> batch process for all we know.
> The point is: not every application is a web application.
You're right. We have no problem using iBatis on the web part of our
application because usually on the "interactive" side you have to read a
small amount of records for each query (and in this case the advantage of
automatic result mapping is amazing!).
But on the "batch" side often you HAVE to manipulate directly jdbc cursors
to avoid memory and performance issues so, in my opinion, it would be nice
if iBatis could have a way to use the power of dynamic queries, bypassing
only automatic result mapping by exposing directly the jdbc cursor.
Davide.
-----Messaggio originale-----
Da: Martin Ellis [mailto:ellis.m.a@gmail.com]
Inviato: giovedì 25 febbraio 2010 12.00
A: user-java@ibatis.apache.org
Oggetto: Re: read large result set in Ibatis3
On 25 February 2010 10:53, zkn <zk...@abv.bg> wrote:
> I use limit and offset exactly for this purpose. I think it's much
> better for the application and the database server to get the total
> count with one query and then just get the page you actually need and
> want to show to the user. You don't really need to show 10K rows on a
> single page to user, do you?
Giovanni is talking about web services, not web pages.
Also, the JasperReports example mentioned earlier in the thread could be
part of a batch process for all we know. The point is: not every
application is a web application.
Martin
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: read large result set in Ibatis3
Posted by Clinton Begin <cl...@gmail.com>.
A REALLY good example of a paginated web service is amazon's merchant
apis. I can't recall the exact name (AWS?), but I remember really
liking how they implemented a usable web service on top of what is
probably the largest product database on the web.
Clinton
On 2010-02-25, zkn <zk...@abv.bg> wrote:
> My bad, sorry.
>
> But again the same thing works for a web service. You get a "page" from db
> print it to the output stream then get the second page and continue
> printing... Wouldn't that work?
> For JasperReports I don't know because I've never used it and I don't know
> how it works. Sorry again if my suggestions was off-topic.
>
> On 25.02.2010, at 13:00, Martin Ellis wrote:
>
>> On 25 February 2010 10:53, zkn <zk...@abv.bg> wrote:
>>> I use limit and offset exactly for this purpose. I think it's much better
>>> for the application and the database server to get the total count with
>>> one query and then just get the page you actually need and want to show
>>> to the user. You don't really need to show 10K rows on a single page to
>>> user, do you?
>>
>> Giovanni is talking about web services, not web pages.
>>
>> Also, the JasperReports example mentioned earlier in the thread could
>> be part of a batch process for all we know. The point is: not every
>> application is a web application.
>>
>> Martin
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
>> For additional commands, e-mail: user-java-help@ibatis.apache.org
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>
--
Sent from my mobile device
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: read large result set in Ibatis3
Posted by zkn <zk...@abv.bg>.
My bad, sorry.
But again the same thing works for a web service. You get a "page" from db print it to the output stream then get the second page and continue printing... Wouldn't that work?
For JasperReports I don't know because I've never used it and I don't know how it works. Sorry again if my suggestions was off-topic.
On 25.02.2010, at 13:00, Martin Ellis wrote:
> On 25 February 2010 10:53, zkn <zk...@abv.bg> wrote:
>> I use limit and offset exactly for this purpose. I think it's much better for the application and the database server to get the total count with one query and then just get the page you actually need and want to show to the user. You don't really need to show 10K rows on a single page to user, do you?
>
> Giovanni is talking about web services, not web pages.
>
> Also, the JasperReports example mentioned earlier in the thread could
> be part of a batch process for all we know. The point is: not every
> application is a web application.
>
> Martin
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: read large result set in Ibatis3
Posted by Martin Ellis <el...@gmail.com>.
On 25 February 2010 10:53, zkn <zk...@abv.bg> wrote:
> I use limit and offset exactly for this purpose. I think it's much better for the application and the database server to get the total count with one query and then just get the page you actually need and want to show to the user. You don't really need to show 10K rows on a single page to user, do you?
Giovanni is talking about web services, not web pages.
Also, the JasperReports example mentioned earlier in the thread could
be part of a batch process for all we know. The point is: not every
application is a web application.
Martin
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: read large result set in Ibatis3
Posted by zkn <zk...@abv.bg>.
I use limit and offset exactly for this purpose. I think it's much better for the application and the database server to get the total count with one query and then just get the page you actually need and want to show to the user. You don't really need to show 10K rows on a single page to user, do you?
On 25.02.2010, at 12:49, Giovanni Cuccu wrote:
> hi,
> because sometimes you can't. Consider for example a web service
> that must return 10k objects, paging the results is really cumbersome
> (even a web service that returns 10k can be cumbersome but you can't
> control everything).
> ciao,
> Giovanni
>
>
> --------------------------------------------------------------------
> "You don't know the power of dark side" - Darth Vader
>
>
>
> On Thu, Feb 25, 2010 at 11:42 AM, zkn <zk...@abv.bg> wrote:
>> Why not use limit and offset and work on a small subset instead of loading a large result set? I believe this would be a better approach.
> .
>
>> On 17.01.2010, at 01:54, Tomáš Procházka wrote:
> .
>
>>>
>>> Hi.
>>>
>>> I need read large result set from DB (like 300 000 rows).
>>>
>>> It's possible use this:
>>>
>>> @Select({"SELECT * FROM send)
>>> List<Send> getAllItems();
>>>
>>> .
>>>
>>>
>>> but instead of read all rows to List use server cursor and read only items which will be accessed by Iterator.
>>>
>>> I found some old article about this problem like:
>>>
>>> http://stackoverflow.com/questions/1344362/java-retrieving-large-amounts-of-data-from-a-db-using-ibatis
>>>
>>> But I don't know, that this is for iBatis 3.0.
>>>
>>> Exist same ResultHandler implementation that can do this?
>>>
>>>
>>> --
>>> Tomáš Procházka
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
>>> For additional commands, e-mail: user-java-help@ibatis.apache.org
>>>
>>>
> .
>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
>> For additional commands, e-mail: user-java-help@ibatis.apache.org
> .
>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: read large result set in Ibatis3
Posted by Giovanni Cuccu <gi...@gmail.com>.
hi,
because sometimes you can't. Consider for example a web service
that must return 10k objects, paging the results is really cumbersome
(even a web service that returns 10k can be cumbersome but you can't
control everything).
ciao,
Giovanni
--------------------------------------------------------------------
"You don't know the power of dark side" - Darth Vader
On Thu, Feb 25, 2010 at 11:42 AM, zkn <zk...@abv.bg> wrote:
> Why not use limit and offset and work on a small subset instead of loading a large result set? I believe this would be a better approach.
>
> On 17.01.2010, at 01:54, Tomáš Procházka wrote:
>
>>
>> Hi.
>>
>> I need read large result set from DB (like 300 000 rows).
>>
>> It's possible use this:
>>
>> @Select({"SELECT * FROM send)
>> List<Send> getAllItems();
>>
>> .
>>
>>
>> but instead of read all rows to List use server cursor and read only items which will be accessed by Iterator.
>>
>> I found some old article about this problem like:
>>
>> http://stackoverflow.com/questions/1344362/java-retrieving-large-amounts-of-data-from-a-db-using-ibatis
>>
>> But I don't know, that this is for iBatis 3.0.
>>
>> Exist same ResultHandler implementation that can do this?
>>
>>
>> --
>> Tomáš Procházka
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
>> For additional commands, e-mail: user-java-help@ibatis.apache.org
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: read large result set in Ibatis3
Posted by zkn <zk...@abv.bg>.
Why not use limit and offset and work on a small subset instead of loading a large result set? I believe this would be a better approach.
On 17.01.2010, at 01:54, Tomáš Procházka wrote:
>
> Hi.
>
> I need read large result set from DB (like 300 000 rows).
>
> It's possible use this:
>
> @Select({"SELECT * FROM send)
> List<Send> getAllItems();
>
> .
>
>
> but instead of read all rows to List use server cursor and read only items which will be accessed by Iterator.
>
> I found some old article about this problem like:
>
> http://stackoverflow.com/questions/1344362/java-retrieving-large-amounts-of-data-from-a-db-using-ibatis
>
> But I don't know, that this is for iBatis 3.0.
>
> Exist same ResultHandler implementation that can do this?
>
>
> --
> Tomáš Procházka
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: read large result set in Ibatis3
Posted by Clinton Begin <cl...@gmail.com>.
That's correct. At this time, you have to call it through sqlSession.
I don't think it would be too hard to add mapper support. If it's really
important for you, then file a Jira ticket and I'll see what I can do.
Clinton
2010/1/17 Tomáš Procházka <t....@centrum.cz>
>
> Yes.
>
> And I found ResultHandler only in this method:
>
> void select
> (String statement, Object parameter, RowBounds rowBounds,
> ResultHandler handler)
>
> which is directly in SqlSession. No possibilities to use ResultHandler in
> mapper class.
>
> ______________________________________________________________
> > Od: "Clinton Begin" <cl...@gmail.com>
> > Komu: user-java@ibatis.apache.org
> > Datum: 17.01.2010 15:40
> > Předmět: Re: read large result set in Ibatis3
> >
> >Have you read the documentation?
> >
> >2010/1/17 Tomáš Procházka <t....@centrum.cz>
> >
> >>
> >> Thanks you for reply.
> >> I need read data only from one table (queue), so I have no accociation
> or
> >> collection mapping.
> >>
> >> But what I must do?
> >>
> >> Is this sufficient:
> >>
> >> @Select({"SELECT * FROM send)
> >> List<Send> getAllItems();
> >> ?
> >>
> >> Or I must do anything else, like use some special class instead of List?
> >>
> >> ______________________________________________________________
> >> > Od: "Clinton Begin" <cl...@gmail.com>
> >> > Komu: user-java@ibatis.apache.org
> >> > Datum: 17.01.2010 01:11
> >> > Předmět: Re: read large result set in Ibatis3
> >> >
> >> >Yes, you can use ResultHAndler in iBATIS 3. The key to know is that
> the
> >> >large result set will be fine if the query is as simple as you've
> >> >presented. However, if you use associations or collections mappings,
> then
> >> >you could run into memory issues.
> >> >
> >> >Cheers,
> >> >Clinton
> >> >
> >> >2010/1/16 Tomáš Procházka <t....@centrum.cz>
> >> >
> >> >>
> >> >> Hi.
> >> >>
> >> >> I need read large result set from DB (like 300 000 rows).
> >> >>
> >> >> It's possible use this:
> >> >>
> >> >> @Select({"SELECT * FROM send)
> >> >> List<Send> getAllItems();
> >> >>
> >> >> ?
> >> >>
> >> >> but instead of read all rows to List use server cursor and read only
> >> items
> >> >> which will be accessed by Iterator.
> >> >>
> >> >> I found some old article about this problem like:
> >> >>
> >> >>
> >> >>
> >>
> http://stackoverflow.com/questions/1344362/java-retrieving-large-amounts-of-data-from-a-db-using-ibatis
> >> >>
> >> >> But I don't know, that this is for iBatis 3.0.
> >> >>
> >> >> Exist same ResultHandler implementation that can do this?
> >> >>
> >> >>
> >> >> --
> >> >> Tomáš Procházka
> >> >>
> >> >> ---------------------------------------------------------------------
> >> >> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> >> >> For additional commands, e-mail: user-java-help@ibatis.apache.org
> >> >>
> >> >>
> >> >
> >> >
> >>
> >> ---------------------------------------------------------------------
> >> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> >> For additional commands, e-mail: user-java-help@ibatis.apache.org
> >>
> >>
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>
Re: read large result set in Ibatis3
Posted by Tomáš Procházka <t....@centrum.cz>.
Yes.
And I found ResultHandler only in this method:
void select
(String statement, Object parameter, RowBounds rowBounds,
ResultHandler handler)
which is directly in SqlSession. No possibilities to use ResultHandler in mapper class.
______________________________________________________________
> Od: "Clinton Begin" <cl...@gmail.com>
> Komu: user-java@ibatis.apache.org
> Datum: 17.01.2010 15:40
> Předmět: Re: read large result set in Ibatis3
>
>Have you read the documentation?
>
>2010/1/17 Tomáš Procházka <t....@centrum.cz>
>
>>
>> Thanks you for reply.
>> I need read data only from one table (queue), so I have no accociation or
>> collection mapping.
>>
>> But what I must do?
>>
>> Is this sufficient:
>>
>> @Select({"SELECT * FROM send)
>> List<Send> getAllItems();
>> ?
>>
>> Or I must do anything else, like use some special class instead of List?
>>
>> ______________________________________________________________
>> > Od: "Clinton Begin" <cl...@gmail.com>
>> > Komu: user-java@ibatis.apache.org
>> > Datum: 17.01.2010 01:11
>> > Předmět: Re: read large result set in Ibatis3
>> >
>> >Yes, you can use ResultHAndler in iBATIS 3. The key to know is that the
>> >large result set will be fine if the query is as simple as you've
>> >presented. However, if you use associations or collections mappings, then
>> >you could run into memory issues.
>> >
>> >Cheers,
>> >Clinton
>> >
>> >2010/1/16 Tomáš Procházka <t....@centrum.cz>
>> >
>> >>
>> >> Hi.
>> >>
>> >> I need read large result set from DB (like 300 000 rows).
>> >>
>> >> It's possible use this:
>> >>
>> >> @Select({"SELECT * FROM send)
>> >> List<Send> getAllItems();
>> >>
>> >> ?
>> >>
>> >> but instead of read all rows to List use server cursor and read only
>> items
>> >> which will be accessed by Iterator.
>> >>
>> >> I found some old article about this problem like:
>> >>
>> >>
>> >>
>> http://stackoverflow.com/questions/1344362/java-retrieving-large-amounts-of-data-from-a-db-using-ibatis
>> >>
>> >> But I don't know, that this is for iBatis 3.0.
>> >>
>> >> Exist same ResultHandler implementation that can do this?
>> >>
>> >>
>> >> --
>> >> Tomáš Procházka
>> >>
>> >> ---------------------------------------------------------------------
>> >> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
>> >> For additional commands, e-mail: user-java-help@ibatis.apache.org
>> >>
>> >>
>> >
>> >
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
>> For additional commands, e-mail: user-java-help@ibatis.apache.org
>>
>>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: read large result set in Ibatis3
Posted by Clinton Begin <cl...@gmail.com>.
Have you read the documentation?
2010/1/17 Tomáš Procházka <t....@centrum.cz>
>
> Thanks you for reply.
> I need read data only from one table (queue), so I have no accociation or
> collection mapping.
>
> But what I must do?
>
> Is this sufficient:
>
> @Select({"SELECT * FROM send)
> List<Send> getAllItems();
> ?
>
> Or I must do anything else, like use some special class instead of List?
>
> ______________________________________________________________
> > Od: "Clinton Begin" <cl...@gmail.com>
> > Komu: user-java@ibatis.apache.org
> > Datum: 17.01.2010 01:11
> > Předmět: Re: read large result set in Ibatis3
> >
> >Yes, you can use ResultHAndler in iBATIS 3. The key to know is that the
> >large result set will be fine if the query is as simple as you've
> >presented. However, if you use associations or collections mappings, then
> >you could run into memory issues.
> >
> >Cheers,
> >Clinton
> >
> >2010/1/16 Tomáš Procházka <t....@centrum.cz>
> >
> >>
> >> Hi.
> >>
> >> I need read large result set from DB (like 300 000 rows).
> >>
> >> It's possible use this:
> >>
> >> @Select({"SELECT * FROM send)
> >> List<Send> getAllItems();
> >>
> >> ?
> >>
> >> but instead of read all rows to List use server cursor and read only
> items
> >> which will be accessed by Iterator.
> >>
> >> I found some old article about this problem like:
> >>
> >>
> >>
> http://stackoverflow.com/questions/1344362/java-retrieving-large-amounts-of-data-from-a-db-using-ibatis
> >>
> >> But I don't know, that this is for iBatis 3.0.
> >>
> >> Exist same ResultHandler implementation that can do this?
> >>
> >>
> >> --
> >> Tomáš Procházka
> >>
> >> ---------------------------------------------------------------------
> >> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> >> For additional commands, e-mail: user-java-help@ibatis.apache.org
> >>
> >>
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>
Re: read large result set in Ibatis3
Posted by Tomáš Procházka <t....@centrum.cz>.
Thanks you for reply.
I need read data only from one table (queue), so I have no accociation or collection mapping.
But what I must do?
Is this sufficient:
@Select({"SELECT * FROM send)
List<Send> getAllItems();
?
Or I must do anything else, like use some special class instead of List?
______________________________________________________________
> Od: "Clinton Begin" <cl...@gmail.com>
> Komu: user-java@ibatis.apache.org
> Datum: 17.01.2010 01:11
> Předmět: Re: read large result set in Ibatis3
>
>Yes, you can use ResultHAndler in iBATIS 3. The key to know is that the
>large result set will be fine if the query is as simple as you've
>presented. However, if you use associations or collections mappings, then
>you could run into memory issues.
>
>Cheers,
>Clinton
>
>2010/1/16 Tomáš Procházka <t....@centrum.cz>
>
>>
>> Hi.
>>
>> I need read large result set from DB (like 300 000 rows).
>>
>> It's possible use this:
>>
>> @Select({"SELECT * FROM send)
>> List<Send> getAllItems();
>>
>> ?
>>
>> but instead of read all rows to List use server cursor and read only items
>> which will be accessed by Iterator.
>>
>> I found some old article about this problem like:
>>
>>
>> http://stackoverflow.com/questions/1344362/java-retrieving-large-amounts-of-data-from-a-db-using-ibatis
>>
>> But I don't know, that this is for iBatis 3.0.
>>
>> Exist same ResultHandler implementation that can do this?
>>
>>
>> --
>> Tomáš Procházka
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
>> For additional commands, e-mail: user-java-help@ibatis.apache.org
>>
>>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: read large result set in Ibatis3
Posted by Clinton Begin <cl...@gmail.com>.
Yes, you can use ResultHAndler in iBATIS 3. The key to know is that the
large result set will be fine if the query is as simple as you've
presented. However, if you use associations or collections mappings, then
you could run into memory issues.
Cheers,
Clinton
2010/1/16 Tomáš Procházka <t....@centrum.cz>
>
> Hi.
>
> I need read large result set from DB (like 300 000 rows).
>
> It's possible use this:
>
> @Select({"SELECT * FROM send)
> List<Send> getAllItems();
>
> ?
>
> but instead of read all rows to List use server cursor and read only items
> which will be accessed by Iterator.
>
> I found some old article about this problem like:
>
>
> http://stackoverflow.com/questions/1344362/java-retrieving-large-amounts-of-data-from-a-db-using-ibatis
>
> But I don't know, that this is for iBatis 3.0.
>
> Exist same ResultHandler implementation that can do this?
>
>
> --
> Tomáš Procházka
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>