traversing through result

[gerch]

Hi

i have been looking for quite some while now for a solution to this problem.

I have a table in my oracle database which has 2.000.000 (yes 2 million rows) and i need to process them one after the other

we are using ejb 3.0 and if just try

public List<Person> getList()
 {
 Query q = em.createQuery("from Person");

 return q.getResultList();
 }

i get an out of memory error after 5 min of trying to load all the people :)

how can i traverse through the result set

something like:

 public void process(){
 // does not acutally work (JUST AN EXAMPLE)
 ResultSet s = em.createQuery("from Person");

 while (s.hasNext()){
 // so something
 }
 }

Thank you very much

[mazz]

Yours is a typical use-case for paging:

http://java.sun.com/javaee/5/docs/api/javax/persistence/Query.html

setFirstResults:

http://java.sun.com/javaee/5/docs/api/javax/persistence/Query.html#setFirstResult(int)

setMaxResults:

http://java.sun.com/javaee/5/docs/api/javax/persistence/Query.html#setMaxResults(int)

[gerch]

ok i get the part of perhaps saying:

for (int i = 0; i < maxSize; i = i+ 10){

 q.setFirstResult(i);
 q.setMaxResults(10);
}

but how do i find out the max rows ? do i need to do a count statement before that ?

[ragavgomatam]

setMaxResults(10);

here denotes how may rows you would like to handle at a time. Usually this is harcoded, got nothing to do with number of rows in table