Tuesday, February 3, 2009

Pagination with Hibernate

Pagination:

Displaying a huge amount of data in a single page was not good, to avoid this will use Pagination concept. It means dividing huge data into smaller parts and showing each smaller one as a page representation. In Technical, huge data is nothing but Resultset, Resultset has many no of rows, by taking some no of rows and displaying in jsp page as a different pages. For this, generally follows approach as the entire Resultset put in a session or any memory and performing presentation logic to display few rows by page by page. Instead of this, will implement Pagination based on query using Hibernate. Each time the query will fetch required data as per the pagination.

Implementation in Eclipse:

Pre requisites:

1. Hibernate3. (download hiberante3 jar file from www.hibernate.org)

2. Any RDBMS (Here Oracle9i/10g jar file ojdbc14.jar)

3. jstl jar file

3. java1.5 or above

Step1:

Creating a sample table named Emp with 4 columns.

CREATE TABLE "BABU"."EMP"

(    "EMPNO" LONG,

    "ENAME" VARCHAR2(40 BYTE),

    "JOB" VARCHAR2(40 BYTE),

    "SAL" FLOAT(126)

) ;

Entering sample data: 11 rows

"EMPNO"    "ENAME"    "JOB"    "SAL"

"1"    "ram"    "se"    "40"

"2"    "babu"    "tl"    "80"

"3"    "cheekuri"    "pl"    "120"

"4"    "bob"    "ret"    "343"

"5"    "fgh"    "t"    "45"

"6"    "gfh"    "tr"    "45"

"7"    "martin"    "PM"    "200"

"8"    "luther4"    "MD"    "500"

"9"    "kelvin"    "CEO"    "1000"

"10"    "Mike"    "SM"    "100"

"11"    "Eric"    "NE"    "70"

Step2: Create a project(Eg.HibernatePaging) in eclipse using new project (dynamic web project)

Step3 : Place the jars(hibernate3.jar,ojdbc14.jar,jstl.jar) in lib folder or configure in project build path.

Step4: Here creating a package com.paging.classes,with in creating a hibernate mapping file to represent Emp Table.

Employee.hbm.xml

<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC

"-//Hibernate/Hibernate Mapping DTD 3.0//EN"

"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

<class name="com.paging.classes.Employee" table="Emp">

<id name="empId" column="EMPNO" type="long">

<generator class="native"/>

</id>

<property name="empName" column="ENAME" />

<property name="empJob" column="JOB" />

<property name="empSal" column="SAL" type="float"/>

</class>

</hibernate-mapping>

 

Step5: Creating a bean class to hold the data.

create a class as like this

 

package com.paging.classes;

 

public class Employee {

public long empId;

public String empName;

public String empJob;

public float empSal;

public long getEmpId() {

return empId;

}

public void setEmpId(long empId) {

this.empId = empId;

}

public String getEmpJob() {

return empJob;

}

public void setEmpJob(String empJob) {

this.empJob = empJob;

}

public String getEmpName() {

return empName;

}

public void setEmpName(String empName) {

this.empName = empName;

}

public float getEmpSal() {

return empSal;

}

public void setEmpSal(float empSal) {

this.empSal = empSal;

}

}

Step6: Write a hibernate.cfg.xml file in src folder.

<?xml version='1.0' encoding='utf-8'?>

<!DOCTYPE hibernate-configuration PUBLIC

"-//Hibernate/Hibernate Configuration DTD 3.0//EN"

"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

<session-factory>

<property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>

<property name="connection.url">jdbc:oracle:thin:@localhost:1521/XE</property>

<property name="connection.username">babu</property>

<property name="connection.password">babu</property>

<property name="dialect">org.hibernate.dialect. Oracle9Dialect</property>

<property name="hibernate.current_session_context_class">thread</property>

<mapping resource="com/paging/classes/Employee.hbm.xml" />

</session-factory>

</hibernate-configuration>

 

Step 7:

Creating a Data Access Object(DAO)

Here DAO class names as DAO.java'

package com.paging.classes;

 

import java.util.Iterator;

import java.util.List;

 

import org.hibernate.Query;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

import org.hibernate.cfg.Configuration;

 

 

public class DAO {

private static int pageSize = 3;

public static List getData(int pageNumber) {

SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();

Session session = sessionFactory.getCurrentSession();

List result = null;

try {

session.beginTransaction();

Query query = session.createQuery("from Employee");

query = query.setFirstResult(pageSize * (pageNumber - 1));

query.setMaxResults(pageSize);

 

result = query.list();

 

 

session.getTransaction().commit();

} catch (Exception e) {

e.printStackTrace();

}

return result;

}

}

 

Pagination needs to specify bounds upon result set (the maximum number of rows want to retrieve and / or the first row want to retrieve) you should use methods of the Query interface:

Query q = sess.createQuery("from TableName");

q.setFirstResult(No of rows);

q.setMaxResults(maximum no of results per page i.e pagesize);

List cats = q.list();

In the above getData method ,passing page number as argument ,to fetch the data of that page and returning as List.

 

 

Step8:

Retrieving the data in jsp page, here jsp named as paging.jsp

 

<jsp:root version="1.2" xmlns:jsp="http://java.sun.com/JSP/Page"

xmlns:c="urn:jsptld:http://java.sun.com/jsp/jstl/core">

<jsp:directive.page contentType="text/html; charset=UTF-8" />

 

<jsp:scriptlet>

int pageNumber=1;

if(request.getParameter("page") != null) {

session.setAttribute("page", request.getParameter("page"));

pageNumber = Integer.parseInt(request.getParameter("page"));

} else {

session.setAttribute("page", "1");

}

String nextPage = (pageNumber +1) + "";

session.setAttribute( "EmpList", com.paging.classes.DAO.getData(pageNumber));

System.out.println(((java.util.List)session.getAttribute("EmpList")).size());

String myUrl = "paging.jsp?page=" + nextPage;

System.out.println(myUrl);

 

pageContext.setAttribute("myUrl", myUrl);

</jsp:scriptlet>

<h2 align="center">Emp Table with Display tag</h2>

<jsp:useBean id="EmpList" scope="session" type="java.util.List"></jsp:useBean>

<table>

<tr>

<th>Employee Id</th>

<th>Name</th>

<th>Job</th>

<th>Salary</th>

</tr>

<c:forEach items="${EmpList}" var="emp" begin="0" end="10">

<tr>

<td><c:out value="${emp.empId}"></c:out></td>

<td><c:out value="${emp.empName}"></c:out></td>

<td><c:out value="${emp.empJob}"></c:out></td>

<td><c:out value="${emp.empSal}"></c:out></td>

</tr>

</c:forEach>

 

<tr>

 

<td ><a href="${pageScope.myUrl}">nextPage</a></td> <td ><a href="paging.jsp?page=1">1</a></td><td><a href="paging.jsp?page=2">2</a></td>

</tr>

</table>

</jsp:root>

By accessing DAO class getData method and fetching result set placing in session corresponding page data instead of entire resultset.

session.setAttribute( "EmpList", com.paging.classes.DAO.getData(pageNumber));

Using jstl tags ,populating data on jsp page.

Here anchor tags 1 ,2 were hardcoded for passing pagenumber 1 &2 to paging.jsp page .

Nextpage link dynamically passing page number to paging.jsp .As like this you can implement Previous page.

 

Output will be:

 

 

 

Approach to write Hibernate XML Config file:

 

The XML configuration file is by default expected to be in the root o your CLASSPATH. Here is an example:

As you can see, the advantage of this approach is the externalization of the mapping file names to configuration. The hibernate.cfg.xml is also more convenient once you have to tune the Hibernate cache. Note that is your choice to use either hibernate.properties or hibernate.cfg.xml, both are equivalent, except for the above mentioned benefits of using the XML syntax.

Property: hibernate.dialect

The classname of a Hibernate org.hibernate.dialect.Dialect which allows Hibernate to generate SQL optimized for a particular relational database. eg. full.classname.of.Dialect

In most cases Hibernate will actually be able to chose the correct org.hibernate.dialect.Dialect implementation to use based on the JDBC metadata returned by the JDBC driver

SQL Dialects :You should always set the hibernate.dialect property to the correct org.hibernate.dialect.Dialect subclass for your database. If you specify a dialect, Hibernate will use sensible defaults for some of the other properties listed above, saving you the effort of specifying them manually.

 Hibernate SQL Dialects (hibernate.dialect)

RDBMS

Dialect

DB2

org.hibernate.dialect.DB2Dialect

DB2 AS/400

org.hibernate.dialect.DB2400Dialect

DB2 OS390

org.hibernate.dialect.DB2390Dialect

PostgreSQL

org.hibernate.dialect.PostgreSQLDialect

MySQL

org.hibernate.dialect.MySQLDialect

MySQL with InnoDB

org.hibernate.dialect.MySQLInnoDBDialect

MySQL with MyISAM

org.hibernate.dialect.MySQLMyISAMDialect

Oracle (any version)

org.hibernate.dialect.OracleDialect

Oracle 9i/10g

org.hibernate.dialect.Oracle9Dialect

Sybase

org.hibernate.dialect.SybaseDialect

Sybase Anywhere

org.hibernate.dialect.SybaseAnywhereDialect

Microsoft SQL Server

org.hibernate.dialect.SQLServerDialect

SAP DB

org.hibernate.dialect.SAPDBDialect

Informix

org.hibernate.dialect.InformixDialect

HypersonicSQL

org.hibernate.dialect.HSQLDialect

Ingres

org.hibernate.dialect.IngresDialect

Progress

org.hibernate.dialect.ProgressDialect

Mckoi SQL

org.hibernate.dialect.MckoiDialect

Interbase

org.hibernate.dialect.InterbaseDialect

Pointbase

org.hibernate.dialect.PointbaseDialect

FrontBase

org.hibernate.dialect.FrontbaseDialect

Firebird

org.hibernate.dialect.FirebirdDialect

Let me know if you have any queries on this.

Thanks,

Cheekuri.

 

No comments:

Post a Comment