org.hibernate.Query .iterate() VS .getResultList()

Question

I am using org.hibernate.Query Api to query for result. But i ran into strange problem. This is suppose my query select DISTINCT abc FROM ABC abc where ORDER BY abc.name ASC.

I have tested this with Oracle database.

let say i have code as below

public static List executeQuery(EntityManager em, Query query) {
        org.hibernate.Query hibernateQuery = query.unwrap(org.hibernate.Query.class);

        // this piece of code is used to fetch data from cache based on id
        if(EntityCacheable){

        Iterator<Entity> it = hibernateQuery.iterate();
        return "";
        }else{

        //This finnaly return the result
            return query.getResultList();
        }
    }

Now the problem is for the above query, hibernateQuery.iterate() generates query like below

select abc.id FROM ABC abc where ORDER BY abc.name ASC, which is not valid. Since what you have in order by clause should be there in select clause also.

But query.getResultList() generates something as below,

select abc.id, abc.name FROM ABC abc where ORDER BY abc.name ASC -- so i am safe here

I can make changes in my query and proceed further, but is this an issue in Hibernate API or what.

Answer 1

This does not seems to be an issue with Hibernate API, infact this the desired behaviour.

Query.iterate():

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.

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
}

Query.getResultList(): 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);
}