Paginated lists made really easy (part 2 of 2

Paginated lists made really easy (part 2 of 2 – back-end)

by ricardoz on Oct.27, 2008, under Articles, JavaScript, Web related

In our first installment we reviewed the front-end part of developing a paginated list using AJAX and Java. Now we will dive into the back-end of our pagination mechanism.

The entry point for all client-server calls (via AJAX) is a facade class that provides one function for each feature we need to provide. This is the only class we need to expose via AJAX (ie in dwr.xml).

UI Facade

In our example we will call it CarsFacade, and in it we need to provide two simple functions: goToPage(int pageNumber) and sort(String attribute, int pageNumber)

goToPage receives a page number and returns the collection of cars to be displayed in that page, as well as the total number of cars in the system:

public Map<String, Object> goToPageUsers(int page){
  WebContext ctx = WebContextFactory.get();
  HttpServletRequest request = ctx.getHttpServletRequest();
 
  Map map = new HashMap();
 
  List list = CarsHandler.list(page, 'brand');
  int totalSize = CarsHandler.getTotalCarsQty();
 
  map.put("list", list);
  map.put("totalSize", totalSize);
 
  return map;
}

I’ve added the first two lines because usually you need to get something from the request or session context to validate your users identity and/or authorizations. And this is the way to get to the request object in a DWR exposed class.

The sort function receives an attribute name and a page number and returns the collection of cars to be displayed in that page, considering them sorted by the given attribute; as well as the total number of cars in the system, yea, i know same old same old:

public Map<String, Object> sort(String attribute, int page){
  WebContext ctx = WebContextFactory.get();
  HttpServletRequest request = ctx.getHttpServletRequest();
 
  Map map = new HashMap();
 
  List list = CarsHandler.list(page, attribute);
  int totalSize = CarsHandler.getTotalCarsQty();
 
  map.put("list", list);
  map.put("totalSize", totalSize);
 
  return map;
}

Now all we need to do is implement that nice CarsHandler class and we are good to go!

Data access layer

The data access layer (implemented via the CarsHandler class) will have the responsibility of returning the needed data, on the required order from the database to the Facade (and in turn to the front-end).

Our two guidelines for implementing it are:
* Return only the data needed by the front-end
* Try to delegate the selection of appropriate data to the lower data-access layer possible (ie. the database engine itself)

Given our requirements the signature of the CarsHandler class will be:

public class CarsHandler {
  public List list(int page, String sortBy){
    ...
  }
 
  public int getTotalCarsQty(){
    ...
  }
}

For all our examples we will assume a single database table named “cars” with three columns:

1
2
3

car_id INT
brand VARCHAR
plate_number VARCHAR

I will draft two options for implementation.

Direct SQL with Java paging and MySQL back-end

In this option we use a MySQL back-end to which we delegate sorting, but we handle paging on the Java side. For the sake of simplicity I’m implementing the data access directly on the Handler class, but I strongly suggest you use the DAO pattern to separate actual data access from systems core logic.

Our back-end will look something like this:

public class CarsHandler {
  private static final int ROWS_PER_PAGE = 5;
 
  public List list(int page, String sortBy) throws SQLException {
    // Somehow i get my database connection (driver manager, JNDI, whatever suits you best)
    Connection con = ....;
 
    Statement stm = con.createStatement();
 
    String sqlStatement = "SELECT * FROM cars ";           
    sqlStatement += " ORDER BY ";
    // Always escape parameters that come from the front-end before using the in SQL Sentences to prevent SQL injection attacks.
    // This nice method is in the Apache Commons Lang package.
    sqlStatement += StringEscapeUtils.escapeSql(sortBy);
 
    // We don't need more than page*ROWS_PER_PAGE records
    sqlStatment += " LIMIT ";
    sqlStatement += (page*ROWS_PER_PAGE);
 
    ResultSet rs = stm.executeQuery(sqlStatement);
 
    List result = new ArrayList();
 
    // We go through all results and add to the resulting list only those in the desired page
    for (int i=0; rs.next() && i<page*ROWS_PER_PAGE; i++){
      if (i>=(page-1)*ROWS_PER_PAGE){
        result.add(new Car(rs.getInt("car_id"), rs.getString("brand"), rs.getString("plate_number")));
      }
    }
 
    // Always remember to be nice and close your SQL objects
    rs.close();
    stm.close();
    con.close();
 
    return result;
  }
 
  public int getTotalCarsQty(){
    // Somehow i get my database connection (driver manager, JNDI, whatever suits you best)
    Connection con = ....;
 
    Statement stm = con.createStatement();
 
    String sqlStatement = "SELECT count(*) AS qty FROM cars";
 
    ResultSet rs = stm.executeQuery(sqlStatement);
 
    int result = 0;
 
    if (rs.next()){
      result = rs.getInt("qty");
    }
 
    // Always remember to be nice and close your SQL objects
    rs.close();
    stm.close();
    con.close();
 
    return result;
  }
}

And that’s about it. If you add filters to your list you will need to send them as parameters to both methods and add the required WHERE clauses to both SQL sentences.

Depending on the change rate of your database and the query load you might want to implement a memory cache for this scenario. One simple way is to use a hash map using the page+sort attribute+filters as key and the resulting list as value.

If you use a memory cache remember to refresh it upon data altering operations on the database.

iBatis sql-maps and MySQL

This scenario is basically the same as the previous one, but instead of using direct JDBC I’m using the iBatis sql-maps (http://ibatis.apache.org/) framework, I strongly recommend you check it out, it’s really easy to use and provides some very usefull features without sacrificing the power of writing the SQL sentences yourself.

In any case, you should read the basic iBatis documentation before using/understading this example.

First of all we need an iBatis mapping file:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="cars">
  <resultMap id="car-result" class="com.spartanjava.Car">
    <result property="carId" column="car_id"/>
    <result property="brand" column="brand"/>
    <result property="plateNumber" column="plate_number"/>
  </resultMap>
 
  <statement id="list" resultMap="car-result" parameterClass="map">
    SELECT *
    FROM cars
    ORDER BY $orderBy$
  </statement>
 
  <statement id="listCount" resultClass="int" parameterClass="map">
    SELECT COUNT(*)
    FROM cars
  </statement>
</sqlMap>

Then our handler will look similar to:

public class CarsHandler {
  private static final int ROWS_PER_PAGE = 5;
 
  public PaginatedList list(String sortBy){
    // Check out the iBatis documentation on how to get your instance of the SqlMapClient
    SqlMapClient sqlMap = ...;
 
    // Query parameters
    Map map = new HashMap();
    map.put("orderBy", sortBy);
 
    PaginatedList paginatedList = (PaginatedList) sqlMap.queryForPaginatedList("car.list", map, ROWS_PER_PAGE);
 
    return paginatedList;
  }
 
  public int getTotalCarsQty(){
    // Check out the iBatis documentation on how to get your instance of the SqlMapClient
    SqlMapClient sqlMap = ...;
 
 
    int qty = ((Integer) sqlMap.queryForObject("car.listCount")).intValue();
 
    return qty;   
  }
}

Note that we don’t need the page number on the list method. This is because the actual paging is handled by the PaginatedList class provided by iBatis. Check it’s API documentation on how to access a specific page and iterate through it’s elements, it’s really simple.

You can also use other options for data access, for example for high performing apps it’s nice to leverage paging to the database using a stored procedure.

Conclusion

Despite of the method you choose, try to keep memory footprint to a minimum, and leverage as much work as possible to the database engine. Avoid storing the full list in memory, and find a way to handle everything on a request based scope (no session storage!).

Bookmark it / share it:

:ajax, cache, data access, database, dwr, facade class, handling, ibatis, java, JavaScript, list, pagination, Performance, session context, simple, validation, web

RSS feed for this post (comments) · TrackBack URI

Categories
- Android
- Articles
- JavaScript
- Performance
- Security
- Tips
- Web related

Meta
- Log in
- Valid XHTML

Spartan Java