Pagination with Hibernate and MySQL

When you have a large of records in the table database, you need to paging it to avoid load all data records with slow speed. Someone’s mistake about pagination in Hibernate with MySQL by using MySQL query normally.

Example below is used to paging by MySQl Query. this query will select first record and limit select to 5 records:

SELECT * FROM table LIMIT 0,5

And in hibernate if we use this query by normal like below..

public List paging() {
    try {
       String queryString = "from table LIMIT 0,5";
       Query queryObject = getSession().createQuery(queryString);
       return queryObject.list();
    } catch (RuntimeException re) {
       throw re;
    }
}

It’s wrong. And it must be:

public List paging() {
    try {
       String queryString = "from table";
       Query queryObject = getSession().createQuery(queryString);
       queryObject.setFirstResult(0);
       queryObject.setMaxResults(5);
       return queryObject.list();
    } catch (RuntimeException re) {
       throw re;
    }
}

setFirstResult(…) is used to select the record number, and setMaxResult(…) is used to select number of records.
Dont be mistake about sql query and Query Object when hibernating with MySQL.

Posted in: Tutorials
Tags: Hibernate, Java, MySQL, Pagination