Now we will look at how to limit the number of rows using hibernate.
Here's a scenario to easily understand what we are about to do.
Let's consider that we have four rows in an employee table, and a SELECT * FROM employee SQL query returns all four records. However, if we want only the second and third records, we can use the SELECT * FROM employee LIMIT 1, 2 SQL statement.
Let's take a look at how to achieve such a condition in hibernate:
Enter the following code to paginate using a criteria:
SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
Session session = sessionFactory.openSession();
Criteria criteria = session.createCriteria(Employee.class);
criteria.setFirstResult(1); // represent LIMIT 1,* in MySQL
criteria.setMaxResults(2);// represent LIMIT *,2 in MySQL
List<Employee> employees = criteria.list();
for (Employee employee : employees) {
System.out.println(employee.toString());
}
session.close();
HibernateUtil.shutdown();The output will be as follows:
Hibernate: select this_.id as id0_1_, this_.department as department0_1_, this_.firstName as firstName0_1_, this_.salary as salary0_1_, department2_.id as id1_0_, department2_.deptName as deptName1_0_ from employee this_ left outer join department department2_ on this_.department=department2_.id limit ?, ? Employee id: 2 first name: aarush salary: 35000.0 department: developement Employee id: 3 first name: varsha salary: 30000.0 department: UI/UX
You can use pagination and restrictions together as well.
Here, two methods play a main role: the first is setFirstResult() and the second is setMaxResult(). The setFirstResult() method is used to set a start limit, and setMaxResult() is used to set the maximum limit. Hibernate adds the database-dependent clause; for example, hibernate adds the LIMIT clause here as the current database is MySQL.