Is there a way to fetch results from a Query with something like a (database) cursor?

[tfriess <tf...@de.ibm.com>]

Hello,
I'm looking for something like a database cursor in OpenJPA and was not able
to find something similar. (I used Google and also searched this mailing
list/ forum.) 
I have to use OpenJPA 1.2.2.

Using OpenJPAs Query class I can only get a complete list of entities from
the database at once (using the "getResultList()" method). If there are a
lots of entities persisted in the table this (a) could need a lots of time
and (b) loads all the entities into my memory.
What I'm looking for is something like an iterator/ cursor to the results of
my query where I can get an entity one by one with some kind of
"getNext()"-method which I then can process before I fetch the next entity
from my result. I want to avoid holding all the result entities at once in a
List in my memory.

I figured out that I'm able to get a reference to the java.sql.Connection
class via the OpenJPAPersistence.
With this instance I'm able to manually send a query to the database using
the JDBC mechanisms and get a java.sql.ResultSet as result. With this
ResultSet I can exactly do what I want - iterate over the results of my
query.
But the problem here is (a) I'm using OpenJPA internals and not only the JPA
API and (b) I was not able to figure out how to automatically map a
ResultSet to my real entity class. 

Is there a good solution for my problem?

Thank you very much!

Kind regards,
Tim


--
View this message in context: http://openjpa.208410.n2.nabble.com/Is-there-a-way-to-fetch-results-from-a-Query-with-something-like-a-database-cursor-tp6418962p6418962.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Is there a way to fetch results from a Query with something like a (database) cursor?

[Rick Curtis <cu...@gmail.com>]

Tim -

> So I assume this class will cover the "cursor handling" for me, right?
Correct.


> Does it make a difference for the DelegatingResultList class if I use its
iterator or can I also use the get(int index) method within a for loop
without performance/ memory penalties?
Honestly, it's been quite some time since I've used the LRS support.... so
you'll need to do some experimentation to make sure that iterator vs
get(index) work as expected.

HTH,
Rick

On Mon, May 30, 2011 at 8:57 AM, tfriess <tf...@de.ibm.com> wrote:

>
> Rick Curtis wrote:
> >
> > Tim -
> >
> > Take a look at Large Result Sets[1].
> >
> > [1]
> >
> http://openjpa.apache.org/builds/latest/docs/manual/manual.html#ref_guide_dbsetup_lrs
> >
> > Thanks,
> > Rick
> >
> > --
> > *Rick Curtis*
> >
>
> Hello Rick,
> thank you for your fast response and thanks for the hint!
>
> Using a Debugger I see that the List implementation returned by the Querys
> "getResultList()" method is a "DelegatingResultList". So I assume this
> class
> will cover the "cursor handling" for me, right?
> (I always thought getResultList would simply return an ArrayList or
> something like this.)
>
> Does it make a difference for the DelegatingResultList class if I use its
> iterator or can I also use the get(int index) method within a for loop
> without performance/ memory penalties?
> So can I use the following constructs without concerns?
> List theList = ...
> for(MyObject o : theList) {
> ...
> }
>
> or
>
> for(int i = 0; i != theList.size(); ++i) {
> ...
> }
>
> Thank you!
>


-- 
*Rick Curtis*

Re: Is there a way to fetch results from a Query with something like a (database) cursor?

[tfriess <tf...@de.ibm.com>]

Rick Curtis wrote:
> 
> Tim -
> 
> Take a look at Large Result Sets[1].
> 
> [1]
> http://openjpa.apache.org/builds/latest/docs/manual/manual.html#ref_guide_dbsetup_lrs
> 
> Thanks,
> Rick
> 
> -- 
> *Rick Curtis*
> 

Hello Rick,
thank you for your fast response and thanks for the hint!

Using a Debugger I see that the List implementation returned by the Querys
"getResultList()" method is a "DelegatingResultList". So I assume this class
will cover the "cursor handling" for me, right?
(I always thought getResultList would simply return an ArrayList or
something like this.)

Does it make a difference for the DelegatingResultList class if I use its
iterator or can I also use the get(int index) method within a for loop
without performance/ memory penalties?
So can I use the following constructs without concerns?
List theList = ...
for(MyObject o : theList) {
...
}

or

for(int i = 0; i != theList.size(); ++i) {
...
}

Thank you!


--
View this message in context: http://openjpa.208410.n2.nabble.com/Is-there-a-way-to-fetch-results-from-a-Query-with-something-like-a-database-cursor-tp6418962p6419243.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Is there a way to fetch results from a Query with something like a (database) cursor?

[Rick Curtis <cu...@gmail.com>]

Tim -

Take a look at Large Result Sets[1].

[1]
http://openjpa.apache.org/builds/latest/docs/manual/manual.html#ref_guide_dbsetup_lrs

Thanks,
Rick

On Mon, May 30, 2011 at 7:47 AM, tfriess <tf...@de.ibm.com> wrote:

> Hello,
> I'm looking for something like a database cursor in OpenJPA and was not
> able
> to find something similar. (I used Google and also searched this mailing
> list/ forum.)
> I have to use OpenJPA 1.2.2.
>
> Using OpenJPAs Query class I can only get a complete list of entities from
> the database at once (using the "getResultList()" method). If there are a
> lots of entities persisted in the table this (a) could need a lots of time
> and (b) loads all the entities into my memory.
> What I'm looking for is something like an iterator/ cursor to the results
> of
> my query where I can get an entity one by one with some kind of
> "getNext()"-method which I then can process before I fetch the next entity
> from my result. I want to avoid holding all the result entities at once in
> a
> List in my memory.
>
> I figured out that I'm able to get a reference to the java.sql.Connection
> class via the OpenJPAPersistence.
> With this instance I'm able to manually send a query to the database using
> the JDBC mechanisms and get a java.sql.ResultSet as result. With this
> ResultSet I can exactly do what I want - iterate over the results of my
> query.
> But the problem here is (a) I'm using OpenJPA internals and not only the
> JPA
> API and (b) I was not able to figure out how to automatically map a
> ResultSet to my real entity class.
>
> Is there a good solution for my problem?
>
> Thank you very much!
>
> Kind regards,
> Tim
>



-- 
*Rick Curtis*