Thursday, December 14, 2006

Paging in JSP with Hibernate

In the past, I had a few posts on how to implement pagination using displaytag(1, 2). That solution is feasible only with small result sets, the reason being that we will have the entire result set in memory (also called cache based paging). If the result set is large, then having the entire result set in memory will not be feasible. With large result sets, you cannot afford to have them in memory. In such case, you have to fetch a chunk of data at a time (query based paging). The down side of using query based paging, is that there will be multiple calls to the database for multiple page requests. In this post, I will describe how to implement simple query based caching solution, using Hibernate and a simple JSP. Time permitting, I will soon post a hybrid of cache based and query based paging example. Here is the code for implementing simple paging using a JSP and Hibernate:
  1. Download the latest version of hibernate from hibernate.org, and include all the required jars in your classpath.
  2. Hibernate configuration
    <?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/orcl</property>
    <property name="connection.username">scott</property>
    <property name="connection.password">tiger</property>
    <property name="dialect">org.hibernate.dialect.Oracle9Dialect</property>
    <property name="hibernate.current_session_context_class">thread</property>
    <mapping resource="beans/Employee.hbm.xml" />
    </session-factory>
    </hibernate-configuration>
    hibernate.cfg.xml
  3. The Employee bean class to hold the data
    public class Employee {
    public long empId;
    public String empName;
    public String empJob;
    public long 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 long getEmpSal() {
    return empSal;
    }
    public void setEmpSal(long empSal) {
    this.empSal = empSal;
    }
    }
    Employee.java
  4. The Employee Mapping file: This listing of the Data Access Object uses the setMaxResults, and setFirstResult method of the Query object to extract the appropriate set of results for each page.
    <?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="beans.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="long"/>
    </class>
    </hibernate-mapping>
    Employee.hbm.xml
  5. The Data Access Object
    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;
    }
    }
    DAO.java
  6. The 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" />

    <link rel="stylesheet" type="text/css" href="css/screen.css" />
    <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", data.DAO.getData(pageNumber));
    System.out.println(((java.util.List)session.getAttribute("EmpList")).size());
    String myUrl = "pagingEmp.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 colspan="2"></td>
    <td colspan="2"><a href="${pageScope.myUrl}">nextPage</a></td>
    </tr>
    </table>
    </jsp:root>
    pagingEmp.jsp

    This JSP uses the DAO class to retrieve the Employee information from the database. The page number is passed as a parameter to the DAO. Notice that I did not implement the "previous" page, but it is similar to next. I assumed that we do not know the number of results for this example.

16 comments:

  1. You wrote: "Time permitting, I will soon post a hybrid of cache based and query based paging example."

    I would love to see this article. Perhaps, even if you don't get time to write the code, you could post some text explaining the design behind your solution?

    Chris.

    ReplyDelete
  2. I was actually looking at this article today, and thinking if I can come up with any better idea paging in J2EE

    ReplyDelete
  3. Not sure if you have seen this http://blog.hibernate.org/cgi-bin/blosxom.cgi/2004/08/14#pagination
    Hibernate blog explaining how to implement pagination using hibernate.
    I think one question still remains is how do we display the total number of results as well.

    ReplyDelete
  4. Once we implemented paging in Hibernate the same way, but we have found that using the setFirstResult function is not very fast on large tables. This is actually good for the first few pages. The database system scans the whole table from the beginning to the place you want to display. It is faster to have one column with sequential row numbers and an index on it. In some cases, if records are added only at the end or beginning - this is possible.

    --
    our consulting company

    ReplyDelete
  5. To Piotr: actually, this depends on implementation of JDBC driver. Oracle drivers do exactly as you said. I don't know if PG or MySQL drivers have the same behaviour. I believe MySQL works because it has a native "first/max" support (if you can do that with SQL, the driver should do the same).

    ReplyDelete
  6. actually i am interested in Jsp code which uses hibernate to store dta & retrieve...can u help me by posting related code.

    Thanks in advance!
    Sanjay Yadav

    ReplyDelete
  7. i have gone through your paging..it is really usefull...

    ReplyDelete
  8. Hey Abhi, i thought ur article was excellent and very informative!

    ReplyDelete
  9. Abhi - Thanks for posting this it was very helpful, but unfortunately I still can't get it to work on my end. I'm getting the following exception:
    org.apache.jasper.JasperException: /common/pagingEmp.jsp(1,121) Could not add one or more tag libraries.

    Can you please specify which taglibs you are using? and how can I implement them in my web app? (I'm using Tomcat v6.

    Thanks for your help!

    ReplyDelete
  10. Abhi - I got it to work now!! please ignore my previous post. But I still have a favor to ask, can you please walk me through the logic to implement "previous page"?

    Thanks,
    Tamer

    ReplyDelete
  11. Hi,I am new to JSP and Hibernate. Can you please show an example on how to insert data into DB from JSP and how to view it from JSP.

    ReplyDelete
  12. Hi abhi

    i am new in hibernate,i want pagination in my web application so i go through your Paging in JSP with Hibernate.but i did not get this code
    session.setAttribute( "EmpList", data.DAO.getData(pageNumber));
    particularly where data is coming.and
    also i am trying package name instead
    of data,then it giving error as
    java.lang.NumberFormatException: For input string: "empId".

    please help me in this topic.
    thanks
    Biswanath

    ReplyDelete
  13. I got it . please ignore my previous post

    ReplyDelete
  14. hi abhi,
    i am parveen
    i have been used your program but when i compile the jsp i got an error
    org.apache.jasper.JasperException: /pagingEmp.jsp(2,58) Could not add one or more tag libraries
    what type of library i need please tell me. i need it very much. help me . i am waitng for ur response

    ReplyDelete
  15. Hi Abhi,
    Iam working on an app wic is quite similar to this post....
    I am tryin to export data to CSV format...
    Using flex as UI, I choose the component(thro a combo box) whose data has to be exported, and then send it to the Action class on the server. I retrieve wat ever data is to be retrieved, from a session bean and send it to a JSP file where a format data to presented as a downloadable file.

    When i access the action class thro the URL, i get the download pop-up.
    But when i click the export button in Flex, there is no response.

    Plz find few code snippets and let me know what im missing:

    Flex:

    public function ExportData():void{

    exportData.send({id:DCSelect1.selectedItem.@id.toString(),
    type:"ABC", from:dateFrom.text, dateto:dateTo.text});

    }

    public function fileDownload(event:ResultEvent):void {
    return;
    }


    HTTPService: id="exportData" showBusyCursor="true" concurrency="last"
    url="http://111.22.66.111:8080/EPDD/exportData.action"
    result="fileDownload(event)" fault="exportError(event)"




    JSP:

    page language="java" contentType="application/csv; charset=ISO-8859-1"
    import ----------------
    -------------------------
    pageEncoding="ISO-8859-1"



    response.setContentType("application/csv");
    response.setHeader("Content-Disposition","attachment;filename=
    Report.csv");

    System.out.println("---- Getting the Data to Download ----");
    out.print("Date");
    out.print(",");
    out.print("value");
    out.print("\n");

    in a loop{
    out.print(date);
    out.print(",");
    out.print(value);
    out.print("\n");
    }

    //Finally
    out.flush()





    I guess the connection btw JSP and Flex is missing, because it works fine when i access the action class through URL.
    Should the FileDownload() function
    in Flex have anything to recieve the proper content from the JSP file???

    ReplyDelete
  16. Hello Tamer..
    I am facing the same issue...
    "org.apache.jasper.JasperException: /login.jsp(2,56) Could not add one or more tag libraries"

    can u tell me how did u solved it???

    Abhi- What may be the reason??

    ReplyDelete

Popular Posts