difference between query.list and query.iterate

Question

What exactly is the difference between using Query.list() and Query.iterator()?

Is there is any performance enhancement in using either. I mean that is any of them implementing lazy loading ?

Or is Query.iterator() ultimately same as query.list().iterate()

Also why is there no Criteria.iterator() only Criteria.list()

Answer 1

Query.list(): Executes 1 SQL query and loads the entire data. Even if the records are present in cache a fresh SQL query is executed to load the records from the database.

List<Employee> list1 = session.createQuery("from Employee").list(); // SELECT *FROM EMP
for (Employee e : list1) {
    System.out.println(e);
}
List<Employee> list2 = session.createQuery("from Employee").list(); // SELECT *FROM EMP
for (Employee e : list2) {
    System.out.println(e);
}

Query.iterate(): Executes 1+N SQL queries. The first query only returns the identifier of all the records and when the returned iterator is iterated then each time a separate SQL query is executed that contains a WHERE clause like “WHERE id=N”. If the records are present in cache then the first query is executed and the rest N queries are not executed and records are obtained from cache.

Iterator<Employee> iterator1 = session.createQuery("from Employee").iterate(); // SELECT EMP_ID FROM EMP
while(iterator1.hasNext()) {
    System.out.println(iterator1.next()); // SELECT * FROM EMP WHERE EMP_ID=?
}
Iterator<Employee> iterator2 = session.createQuery("from Employee").iterate(); // SELECT EMP_ID FROM EMP
while (iterator2.hasNext()) {
    System.out.println(iterator2.next()); // From cache, no SQL
}

Answer 2

If instances are already in the session (primary-level cache) or second-level cache iterate() will give better performance.

If they are not already cached, iterate() will be slower than list() and might require many database hits for a simple query.

Answer 3

The javadoc says:

Return the query results as an Iterator. If the query contains multiple results pre row, the results are returned in an instance of Object[].

Entities returned as results are initialized on demand. The first SQL query returns identifiers only.

(emphasis mine)

Answer 4

+----------------------------------------------+-----------------------------------------------+
|                    list()                    |                   iterate()                   |
+----------------------------------------------+-----------------------------------------------+
| Return type is List                          | Return type is Iterate                        |
| All records loads at single database request | For each record, one database hit is made     |
| This is faster if cache is not available     | This is very slower if cache is not available |
| Eager loading                                | Lazy loading                                  |
+----------------------------------------------+-----------------------------------------------+  

For list():

Query query = session.createQuery("from Employee");
List list = query.list(); // SELECT * FROM EMP
Iterator iterator = list.iterator();
while(iterator.hasNext()){
}  

For iterate():

Query query = session.createQuery("from Employee");
Iterator iterator = query.iterate(); // SELECT * FROM EMP
while(iterator.hasNext()){
    // SELECT * FROM EMP WHERE EMP_ID=?
}