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

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 2

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
}