ResultSet to Pagination

Question

How to convert a Resultset object to a paginated view in JSP?

For example, this is my query and result set:

pst = con.prepareStatement("select userName, job, place from contact"); rs = pst.executeQuery(); 

Answer 1

To get started, you need to add one or two additional query parameters to the JSP: firstrow and (optionally) rowcount . The number of rowcount can also be omitted and completely determined on the server side.

Then add a bunch of paging buttons to the JSP: the next button should instruct the Servlet to increase the firstrow value with the rowcount value. The previous button, obviously, should firstrow value firstrow by rowcount value. Remember to handle negative values ​​and overflows correctly! You can do this with SELECT count(id) .

Then fire a specific SQL query to get a list of results. However, the exact syntax of SQL depends on the database used. In MySQL and PostgreSQL, this is easy with the LIMIT and OFFSET clauses:

 private static final String SQL_SUBLIST = "SELECT id, username, job, place FROM" + " contact ORDER BY id LIMIT %d OFFSET %d"; public List<Contact> list(int firstrow, int rowcount) { String sql = String.format(SQL_SUBLIST, firstrow, rowcount); // Implement JDBC. return contacts; } 

In Oracle, you need a subquery with a rownum , which should look like this:

 private static final String SQL_SUBLIST = "SELECT id, username, job, place FROM" + " (SELECT id, username, job, place FROM contact ORDER BY id)" + " WHERE ROWNUM BETWEEN %d AND %d"; public List<Contact> list(int firstrow, int rowcount) { String sql = String.format(SQL_SUBLIST, firstrow, firstrow + rowcount); // Implement JDBC. return contacts; } 

In DB2, you need the OLAP function row_number() for this:

 private static final String SQL_SUBLIST = "SELECT id, username, job, place FROM" + " (SELECT row_number() OVER (ORDER BY id) AS row, id, username, job, place" + " FROM contact) AS temp WHERE row BETWEEN %d AND %d"; public List<Contact> list(int firstrow, int rowcount) { String sql = String.format(SQL_SUBLIST, firstrow, firstrow + rowcount); // Implement JDBC. return contacts; } 

I do not do MSSQL, but it is syntactically similar to DB2. Also see this topic .

Finally, just submit the sublist on the JSP page in the usual way using JSTL c:forEach .

 <table> <c:forEach items="${contacts}" var="contact"> <tr> <td>${contact.username}</td> <td>${contact.job}</td> <td>${contact.place}</td> </tr> </c:forEach> </table> <form action="yourservlet" method="post"> <input type="hidden" name="firstrow" value="${firstrow}"> <input type="hidden" name="rowcount" value="${rowcount}"> <input type="submit" name="page" value="next"> <input type="submit" name="page" value="previous"> </form> 

Note that some may suggest that you select SELECT entire table and keep the List<Contact> in the scope of the session and use List#subList() for List#subList() on the page. But it is far from efficient for memory with thousands of lines and several simultaneous users.

For those interested in a similar answer in the context of JSF / MySQL using the h:dataTable , this article may be useful. It also contains some useful language-independent math expressions that make it easy to work with Google-like pagination.

Answer 2

This Oracle example is incorrect.

Yes, the outer selects have good ROWNUM values, but these are still pseudo columns, so we can't use BETWEEN on it. We need one more choice.

The correct sql code is:

 SELECT c.* FROM (SELECT c.*, ROWNUM as rnum FROM (SELECT id, username, job, place FROM contact ORDER BY id) c) c WHERE c.rnum BETWEEN 5 AND 10 

---

Comrades, using a solid sql string and Statement SLOWOW class. Oracle needs to parse your SQL every time you execute it.

 //Slooow example Satement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("select * from my_table where id = 11"); 

Use the PreparedStatement and Binding options.

  //Faster example PreparedStatement ps = conn.getPrepareStatement("select * from my_table where id = ?"); ps.setInt(1, 11); 

And the fastest solution puts your sql in the oracle stored procedure and uses CallableStatement to call it.

 //Fastest example CallableStatement cs = conn.prepareCall("{? = call my_plsql_function(?)}"); cs.setInt(1, 11); 

Answer 3

Here are a few things you can do:

• Marshall result set to some list of objects / records
• Depending on the required page size, find out how many pages you will have based on a set of results.
• Check the query parameter for the desired page and offsets based on the number of elements displayed on the page. So, if you are on page 4 with 12 to display, your offset is 48.

• Determine the total number of pages based on the number of elements.

• Display your objects based on the offset you set (only display starts at position 48)

• Create your pagination with the number of pages depending on the total number of pages that you have defined.

=======

This is your basic approach. You can configure it as follows:

• Determining how to limit the page request (but this will not help you determine the page size)
• Unusual pagination methods
• etc..

Answer 4

this will not allow me to add a comment, so please do not consider this an answer. I just wanted clarification on the Piyush post above. Where do you have ai.add (sdata); and return ai, was it supposed to be "al" from the StudentList class?

Answer 5

Look at the value list template and apply it. This is usually the best way to deal with such things.

Answer 6

You can use displaytag for paigination or resultset, but u load some jar file from disattag file

first you create one servlet StudentList.java

 public class StudentList extends HttpServlet 

{public void service (HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

  ArrayList al=new ArrayList(); StudentDao stdo=new StudentDao(); // this is DAO Class (Data Acccess Object) try { al=stdo.getStudentList(); //getstudent list dao method } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } request.setAttribute("al",al); RequestDispatcher rd=request.getRequestDispatcher("StudentPaging.jsp"); rd.forward(request,response); } 

}

// dao method

 public ArrayList getStudentList() throws SQLException,Exception { ArrayList ai=new ArrayList(); Connection con=null; Statement st=null; ResultSet rs=null; Date dt=new Date(); SimpleDateFormat sdf=new SimpleDateFormat("dd/MM/yyyy"); StudentInformation sdata=null; con=MyConnection.creatConnection(); if(con!=null) { st=con.createStatement(); String select="select * from STUDENT"; System.out.println(select); rs=st.executeQuery(select); if(rs!=null) { while(rs.next()) { sdata=new StudentInformation(); sdata.setSid(rs.getString("SID")); sdata.setFirstName(rs.getString("FIRSTNAME")); sdata.setMiddleName(rs.getString("MIDDLENAME")); sdata.setLastName(rs.getString("LASTNAME")); dt=rs.getDate("SDATE"); sdata.setDateofbirth(sdf.format(dt)); sdata.setGender(rs.getString("GENDER")); sdata.setAddress(rs.getString("ADDRESS")); sdata.setHigestQulification(rs.getString("HIQULIFICATION")); sdata.setLanguageKnow(rs.getString("LANGUAGE")); sdata.setHobby(rs.getString("HOBBY")); sdata.setTermCondition(rs.getString("TERMCON")); ai.add(sdata); } } } return ai; }