Bulk reading of data in hibernate using scrollable result set

Question

I was reading a blog regarding bulk fetching with hibernate http://java.dzone.com/articles/bulk-fetching-hibernate.
In this, ScrollableResults is used as a solution. Here still we need to evict objects from session.
I don't understand how using ScrollableResults(or scroll()) is different from using list().

In other words, how the below statements differ in terms of performance

 List<Employee> empList = session.createCriteria(Employee.class).list();
 ScrollableResults sc = session.createCriteria(Employee.class).scroll();

Please let me know.

ScrollableResults works like ScrollableResultSet of Jdbc. It fetches data one by one. — bpjoshi, Oct 27 '17 at 12:27

score 2 · Answer 1 · answered Dec 09 '14 at 05:57

The above code seems to be missing some settings.

Query query = session.createQuery(query);
query.setReadOnly(true);
// MIN_VALUE gives hint to JDBC driver to stream results
query.setFetchSize(Integer.MIN_VALUE);
ScrollableResults results = query.scroll(ScrollMode.FORWARD_ONLY);
// iterate over results
while (results.next()) {
    Object row = results.get();
    // process row then release reference
    // you may need to flush() as well
}
results.close();

Follow this link for more details and explanation.

score 1 · Answer 2 · answered Dec 08 '14 at 23:37

I referred Hibernate API documentation to understand the difference between list() and scroll(). ScrollableResults is like a cursor . An important feature of ScrollableResults is that it allows accessing ith object in the current row of results, without initializing any other results in the row through get(int i) function. It also allows moving back and forth the result set using next() and previous() function.

Example :

  ScrollableResults sc = session.createQuery("select e.employeeName,e.employeeDept FROM Employee e").scroll(ScrollMode.SCROLL_INSENSITIVE);
      while(sc.next()) {
         String empName = (String)sc.get(0);
         System.out.println(empName);
         String empdept = (String)sc.get(1);
         System.out.println(empdept);
      }

The output of above programm will values of employeeName and employeeDept .

Now suppose you want to get the last record of the resultSet. With list() you would need to iterate the whole result. With ScrollableResults, you can use last() function.

Note that for ScrollableResults sc = session.createCriteria(Employee.class).scroll(); should be iterated as

 while(sc.next()) {
     Employee emp = (Employee)sc.get(0);
     System.out.println(emp.getname);
 }

Thanks Gaurav..one thing..on executing both these statements i can see sql statements being fired..doesn't that mean both will load all the rows in the table in memory — Anand, Dec 09 '14 at 06:21

Bulk reading of data in hibernate using scrollable result set

2 Answers2

Linked