Hibernate - Select and Pagination in HQL

Ref:- http://www.dineshonjava.com/p/select-and-pagination-in-hql.html#.VnIiBfkrLIU

Pagination of search results is a common requirement for any application.

Out of performance reasons it is recommended to restrict the number of returned objects per query. In fact is a very common use case anyway that the user navigates from one page to an other. The way to define pagination is exactly the way you would define pagination in a plain HQL or Criteria query.

Using the createQuery() method of a Session object that returns a Query object.

First, instantiate the Session object using the openSession() method of SessionFactory.

Then, invoke the createQuery() method on the resulting object.

Query q = session.createQuery("...");
q.setFirstResult(start);
q.setMaxResults(length);

Student.java

view plainprint?

1. package com.sdnext.hibernate.tutorial.dto;  
2.   
3. import java.io.Serializable;  
4.   
5. import javax.persistence.Column;  
6. import javax.persistence.Entity;  
7. import javax.persistence.GeneratedValue;  
8. import javax.persistence.GenerationType;  
9. import javax.persistence.Id;  
10. import javax.persistence.Table;  
11.   
12. @Entity  
13. @Table(name="STUDENT")  
14. public class Student implements Serializable   
15. {  
16.  /** 
17.   * serialVersionUID 
18.   */  
19.  private static final long serialVersionUID = 8633415090390966715L;  
20.  @Id  
21.  @Column(name="ID")  
22.  @GeneratedValue(strategy=GenerationType.AUTO)  
23.  private int id;  
24.  @Column(name="STUDENT_NAME")  
25.  private String studentName;  
26.  @Column(name="ROLL_NUMBER")  
27.  private int rollNumber;  
28.  @Column(name="COURSE")  
29.  private String course;  
30.  public int getId() {  
31.   return id;  
32.  }  
33.  public void setId(int id) {  
34.   this.id = id;  
35.  }  
36.  public String getStudentName() {  
37.   return studentName;  
38.  }  
39.  public void setStudentName(String studentName) {  
40.   this.studentName = studentName;  
41.  }  
42.  public int getRollNumber() {  
43.   return rollNumber;  
44.  }  
45.  public void setRollNumber(int rollNumber) {  
46.   this.rollNumber = rollNumber;  
47.  }  
48.  public String getCourse() {  
49.   return course;  
50.  }  
51.  public void setCourse(String course) {  
52.   this.course = course;  
53.  }  
54.  public String toString()  
55.  {  
56.   return "ROLL Number: "+rollNumber+"| Name: "+studentName+"| Course: "+course;  
57.  }  
58. }  

hibernate.cfg.xml

view plainprint?

1. <hibernate-configuration>   
2.  <session-factory>   
3.   <!-- Database connection settings -->  
4.    <property name="connection.driver_class">com.mysql.jdbc.Driver</property>   
5.    <property name="connection.url">jdbc:mysql://localhost:3306/hibernateDB2</property>   
6.    <property name="connection.username">root</property>   
7.    <property name="connection.password">root</property>   
8.   
9.   <!-- JDBC connection pool (use the built-in) -->  
10.    <property name="connection.pool_size">1</property>   
11.   
12.   <!-- SQL dialect -->  
13.    <property name="dialect">org.hibernate.dialect.MySQLDialect</property>   
14.   
15.   <!-- Enable Hibernate's automatic session context management -->  
16.     <property name="current_session_context_class">thread</property>   
17.     
18.   <!-- Disable the second-level cache -->  
19.    <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>   
20.   
21.   <!-- Echo all executed SQL to stdout -->  
22.    <property name="show_sql">true</property>   
23.     
24.   <!-- Drop and re-create the database schema on startup -->  
25.    <property name="hbm2ddl.auto">update</property>   
26.      
27.      
28.    <mapping class="com.sdnext.hibernate.tutorial.dto.Student">  
29.         
30.   </mapping></session-factory>   
31.  </hibernate-configuration>  

HibernateTestDemo.java

view plainprint?

1. package com.sdnext.hibernate.tutorial;  
2.   
3. import java.util.List;  
4.   
5. import org.hibernate.Query;  
6. import org.hibernate.Session;  
7. import org.hibernate.SessionFactory;  
8. import org.hibernate.cfg.AnnotationConfiguration;  
9.   
10. import com.sdnext.hibernate.tutorial.dto.Student;  
11.   
12. public class HibernateTestDemo {  
13.  /** 
14.   * @param args 
15.   */  
16.  public static void main(String[] args)   
17.  {  
18.   SessionFactory sessionFactory = new AnnotationConfiguration().configure().buildSessionFactory();  
19.   Session session = sessionFactory.openSession();  
20.   session.beginTransaction();  
21.     
22.   String SQL_QUERY = "FROM Student student";  
23.   Query query = session.createQuery(SQL_QUERY);  
24.   query.setFirstResult(1);//set first result start value  
25.   query.setMaxResults(5);//number of result to be display  
26.     
27.   List<student> students = query.list();  
28.   for(Student student : students)  
29.   {  
30.    System.out.println(student);  
31.   }  
32.   session.getTransaction().commit();  
33.   session.close();  
34.  }  
35. }  
36. </student>  

Output:

log4j:WARN No appenders could be found for logger (org.hibernate.cfg.annotations.Version).

log4j:WARN Please initialize the log4j system properly.

Hibernate: select student0_.ID as ID0_, student0_.COURSE as COURSE0_, student0_.ROLL_NUMBER as ROLL3_0_, student0_.STUDENT_NAME as STUDENT4_0_ from STUDENT student0_ limit ?, ?
ROLL Number: 2| Name: Sweety Rajput| Course: PGDCP
ROLL Number: 3| Name: Adesh Rajput| Course: MA
ROLL Number: 4| Name: DEV| Course: MA
ROLL Number: 5| Name: RAJ| Course: BA
ROLL Number: 6| Name: Pradeep| Course: BA