[FIXED] Hibernate pagination mechanism

Issue

I am trying to use Hibernate pagination for my query (PostgreSQL )

I set setFirstResult(0), setMaxResults(20) for my SQL query. My code is like below:

Session session = getSessionFactory().getCurrentSession();
session.beginTransaction();
Query query = session.createQuery("FROM Customers");
query.setFirstResult(0);
query.setMaxResults(20);
List<T> entities = query.list();
session.getTransaction().commit();

but when viewing the SQL Hibernate log, I still see the full SQL query:

Hibernate: select customer0_.id as id9_, customer0_.customer_name as dst2_9_, customer0_.addres as dst3_9_ from tbl_customers customer0_  

Why there is no LIMIT OFFSET in query of Hibernate pagination SQL log?

Does anyone know about Hibernate pagination mechanism?

I guess that Hibernate will select all data, put data into Resultset, and then paging in Resultset, right?

Solution

I am using in query and in hibernate call back. both are working as expected. Hibernate Query executes for results in between First and Max size given. Here Seems like you passed SQL not HQL to query. if yes it shouldn't work.

-- See my code here.

        Query query = this.getSession().createQuery("FROM QueryType");
        query.setFirstResult(0);
        query.setMaxResults(20);
        List toDelete = query.list();

and in log:

select * from ( select -- ALL column names. (dont want to share here.) from MY_TBL_NAME querytype0_ ) where rownum <= ?

Answered By - Chowdappa