How to Create a Remote Paging Listview Using GWT-Ext
Creating a remote paging listview isn’t particularly difficult but getting to the point of knowing how all the pieces fit together can be tricky first time round. This post describes how I created a very simple remote paging listview that retrieves each page of data from an Oracle database table. As well as describing the listview I’ve also included the code for the server side that actually fetches and returns each page of data to the client.
Deploying GWT-Ext
If you’ve not used GWT-Ext before then here’s how to include it in your GWT application. First you’ll need to download GWT-Ext itself. At the time of writing 2.0.4 is the latest release, you can get it here. The real workhorse behind GWT-Ext is the Javascript library ExtJS. GWT-Ext 2.0.4 requires ExtJS 2.0.2 which you can download from here.
If you don’t already have a GWT application then GWT’s The Basics guide is a good place to start.
With your application up and running here are the steps required to deploy GWT-Ext within it.
- Create the directory
js/extunder your application’spublicfolder and copy the following files and directories from ExtJS into it,adapter resources ext-all.js ext-all-debug.js ext-core.js ext-core-debug.js - Edit your module file and add these lines to it,
<inherits name='com.gwtext.GwtExt'/> <stylesheet src="js/ext/resources/css/ext-all.css"/> <script src="js/ext/adapter/ext/ext-base.js" /> <script src="js/ext/ext-all.js" /> - The final step it to add the GWT-Ext jar, gwtext.jar, to your application’s classpath.
The Client Side Code
GWT-Ext refers to a listview as a Grid so to avoid confusion I’ll use that term throughout the rest of this post. I used the term listview up to this point because it’s probably a more generally accepted description.
Before going into the code it’s worth spending a little time understanding the various objects that are needed to support the Grid object. The first thing worth pointing out is that GWT-Ext doesn’t use standard GWT Remote Procedure Calls to fetch the data needed in the Grid. What you do instead is provide it with a HTTP URL from which to retrieve the data. To do this you use a DataProxy object. There are three types of DataProxy, the HttpProxy, the MemoryProxy and the ScriptTagProxy. The MemoryProxy is just a static in memory data source and the ScriptTagProxy is the same as HttpProxy except that it retrieves data from a site other than the one where your Grid is hosted. So what format should the data coming back from this URL be in? Two formats are supported, JSON and XML. In order to understand the structure of this JSON or XML we need to use a RecordDef. This object contains FieldDef objects each of which describe a particular field in the data feed. Once you’ve described you data structure using a RecordDef you create either a JsonReader or an XmlReader and give it a reference to your RecordDef. Finally, to tie all these objects together you create a Store. The Store is the object you present to the Grid. It uses the DataProxy to retrieve the data and and the Reader to parse it.
With the Store defined we can turn our attention to the Grid. To define what columns the Grid should have and where the data for each column should come from we use a ColumnModel object. This object contains a number of ColumnConfig objects, one for each column. A ColumnConfig contains the column title, the name of the record in the RecordDef to populate the column with, it’s width, weather it’s sortable and how the data should be rendered. To create the Grid itself we use the GridPanel object. You can link it to the Store and ColumnModel using either the constructor or the setter methods.
The final point to note is that you’ll need to add an onRender event handler to the Grid so that the first page of data is loaded when the Grid is displayed.
Bringing all that together here’s the class I ended up with,
package com._17od.gwtexamples.client;
import java.util.Date;
import com.google.gwt.core.client.EntryPoint;
import com.google.gwt.user.client.ui.RootPanel;
import com.gwtext.client.core.SortDir;
import com.gwtext.client.data.DateFieldDef;
import com.gwtext.client.data.FieldDef;
import com.gwtext.client.data.HttpProxy;
import com.gwtext.client.data.IntegerFieldDef;
import com.gwtext.client.data.JsonReader;
import com.gwtext.client.data.Record;
import com.gwtext.client.data.RecordDef;
import com.gwtext.client.data.Store;
import com.gwtext.client.data.StringFieldDef;
import com.gwtext.client.util.DateUtil;
import com.gwtext.client.widgets.Component;
import com.gwtext.client.widgets.PagingToolbar;
import com.gwtext.client.widgets.Panel;
import com.gwtext.client.widgets.event.PanelListenerAdapter;
import com.gwtext.client.widgets.grid.CellMetadata;
import com.gwtext.client.widgets.grid.ColumnConfig;
import com.gwtext.client.widgets.grid.ColumnModel;
import com.gwtext.client.widgets.grid.GridPanel;
import com.gwtext.client.widgets.grid.GridView;
import com.gwtext.client.widgets.grid.Renderer;
import com.gwtext.client.widgets.grid.RowSelectionModel;
public class PersonGrid implements EntryPoint {
public void onModuleLoad() {
Panel panel = new Panel();
panel.setBorder(false);
panel.setPaddings(15);
HttpProxy dataProxy = new HttpProxy("http://localhost:8888/persons");
final RecordDef recordDef = new RecordDef(new FieldDef[]{
new StringFieldDef("class"),
new DateFieldDef("dateOfBirth", "Y-m-d"),
new StringFieldDef("firstname"),
new IntegerFieldDef("id"),
new StringFieldDef("lastname"),
});
JsonReader reader = new JsonReader(recordDef);
reader.setRoot("persons");
reader.setTotalProperty("totalPerons");
reader.setId("id");
final Store store = new Store(dataProxy, reader, true);
store.setDefaultSort("id", SortDir.ASC);
ColumnConfig firstNameColumn = new ColumnConfig("First Name", "firstname", 45, true);
ColumnConfig lastNameColumn = new ColumnConfig("Last Name", "lastname", 45, true);
ColumnConfig dateOfBirthColumn = new ColumnConfig("Date of Birth", "dateOfBirth", 45, true, dateRender);
ColumnModel columnModel = new ColumnModel(new ColumnConfig[] {
firstNameColumn,
lastNameColumn,
dateOfBirthColumn});
columnModel.setDefaultSortable(true);
GridPanel grid = new GridPanel();
grid.setWidth(700);
grid.setHeight(300);
grid.setTitle("People");
grid.setStore(store);
grid.setColumnModel(columnModel);
grid.setTrackMouseOver(true);
grid.setLoadMask(true);
grid.setSelectionModel(new RowSelectionModel());
grid.setStripeRows(true);
grid.setIconCls("grid-icon");
grid.setEnableColumnResize(true);
GridView view = new GridView();
view.setForceFit(true);
grid.setView(view);
PagingToolbar pagingToolbar = new PagingToolbar(store);
pagingToolbar.setPageSize(15);
pagingToolbar.setDisplayInfo(true);
grid.setBottomToolbar(pagingToolbar);
grid.addListener(new PanelListenerAdapter() {
public void onRender(Component component) {
store.load(0, 15);
}
});
panel.add(grid);
RootPanel.get().add(panel);
}
private Renderer dateRender = new Renderer() {
public String render(Object value, CellMetadata cellMetadata, Record record, int rowIndex, int colNum, Store store) {
return DateUtil.format((Date) value, "d-m-Y");
}
};
}
Where Does the Data Come From?
The code below is the servlet I developed to return each page of data to the Grid. It retrieves the data from an Oracle database table called “person”. It’s not particularly complicated but it does demonstrate some challenges that are worth pointing out.
package com._17od.gwtexamples.servlets;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Enumeration;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import oracle.jdbc.pool.OracleDataSource;
import org.json.JSONArray;
import org.json.JSONObject;
public class GetPersonsServlet extends HttpServlet {
private Connection connection;
public void doGet(HttpServletRequest req, HttpServletResponse res) throws IOException {
doPost(req, res);
}
public void doPost(HttpServletRequest req, HttpServletResponse res) throws IOException {
// Display the parameters we're passed in for debugging purposes
printParameters(req);
// Get the limit and sort parameters off the request
int start = req.getParameter("start") == null ? 1 : Integer.parseInt(req.getParameter("start"));
int numberToReturn = req.getParameter("limit") == null ? 10 : Integer.parseInt(req.getParameter("limit"));
String sortBy = req.getParameter("sort") == null ? "id" : req.getParameter("sort");
String sortOrder = req.getParameter("dir") == null ? "asc" : req.getParameter("dir");
// Create the SQL query used to retrieve the persons
String sql = createMainSQLQuery(sortBy, sortOrder);
ArrayList persons = null;
try {
// Get a connection to the Oracle database and put it on the object so that it's easily accessible
connection = getConnection();
// Execute the query to return the exact records requested
persons = queryForPersonsUsingLimits(sql, start, numberToReturn);
// Convert the list of persons into a JSON string
JSONObject jsonDataToReturn = new JSONObject();
JSONArray jsonPersons = new JSONArray(persons, true);
jsonDataToReturn.put("totalPerons", getTotalNumberOfPersons(sql));
jsonDataToReturn.put("persons", jsonPersons);
// Write everything back to the requestor
res.getWriter().print(jsonDataToReturn.toString(1));
} catch (Exception e) {
e.printStackTrace();
throw new IOException(e);
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
throw new IOException(e);
}
}
}
}
/**
* Create the query used to retrieve the persons from the database.
* @param sortBy
* @param sortOrder
* @return
*/
private String createMainSQLQuery(String sortBy, String sortOrder) {
StringBuffer sql = new StringBuffer("SELECT id, firstname, lastname, dateofbirth FROM person");
sql.append(" ORDER BY ");
sql.append(sortBy);
sql.append(" ");
sql.append(sortOrder);
return sql.toString();
}
/**
* Wrap the query passed in with some extra SQL that pulls out the exact
* page of persons requested, i.e. starting at record number 10 return 20
* records.
* It's vital that the query given in 'sql' has an 'order by' clause.
* If it didn't then the persons could be returned in a random order
* making it impossible to order them into pages.
* @param sql
* @param start
* @param numberToReturn
* @return
* @throws SQLException
*/
private ArrayList queryForPersonsUsingLimits(String sql, int start, int numberToReturn) throws SQLException {
StringBuffer sqlBuffer = new StringBuffer("SELECT * FROM (SELECT a.*, ROWNUM rnum from (");
sqlBuffer.append(sql);
sqlBuffer.append(") a WHERE ROWNUM <= ");
sqlBuffer.append(start + numberToReturn);
sqlBuffer.append(") WHERE rnum > ");
sqlBuffer.append(start);
Statement statement = null;
ResultSet rs = null;
ArrayList<person> persons = new ArrayList<person>();
try {
statement = connection.createStatement();
rs = statement.executeQuery(sqlBuffer.toString());
while (rs.next()) {
Person person = new Person();
person.setId(rs.getInt("id"));
person.setFirstname(rs.getString("firstname"));
person.setLastname(rs.getString("lastname"));
person.setDateOfBirth(rs.getDate("dateofbirth"));
persons.add(person);
}
} finally {
if (rs != null) {
rs.close();
}
if (statement != null) {
statement.close();
}
}
return persons;
}
/**
* Return the total numbner of records that the given query will return.
* @param sql
* @return
* @throws SQLException
*/
private int getTotalNumberOfPersons(String sql) throws SQLException {
int count = 0;
StringBuffer sqlBuffer = new StringBuffer("SELECT COUNT(*) count FROM (");
sqlBuffer.append(sql);
sqlBuffer.append(")");
Statement statement = null;
ResultSet rs = null;
try {
statement = connection.createStatement();
rs = statement.executeQuery(sqlBuffer.toString());
while (rs.next()) {
count = rs.getInt("count");
}
} finally {
if (rs != null) {
rs.close();
}
if (statement != null) {
statement.close();
}
}
return count;
}
/**
* Return a database connection from the connection poll that's stored on the servlet/application context
* @return
* @throws SQLException
*/
private Connection getConnection() throws SQLException {
OracleDataSource ods = (OracleDataSource) getServletContext().getAttribute("CONNECTION_POOL");
if (ods == null) {
ods = createConnectionPool();
getServletContext().setAttribute("CONNECTION_POOL", ods);
}
return ods.getConnection();
}
/**
* Create a database connection pool
* @return
* @throws SQLException
*/
private OracleDataSource createConnectionPool() throws SQLException {
String username = "replace with db username";
String password = "replace with db password";
String url = "jdbc:oracle:thin:@localhost:1521:XE";
OracleDataSource ods = new OracleDataSource();
ods.setURL(url);
ods.setUser(username);
ods.setPassword(password);
ods.setConnectionCachingEnabled(true);
return ods;
}
private void printParameters(HttpServletRequest req) {
Enumeration parameterNames = req.getParameterNames();
System.out.println("\nRequest Parameters:");
while (parameterNames.hasMoreElements()) {
String parameterName = (String) parameterNames.nextElement();
System.out.println(" " + parameterName + "=" + req.getParameter(parameterName));
}
}
}
The first point of interest in doPost() is the code that takes the ordering and limit parameters off the request. This servlet is requested when the grid is first drawn or when the user clicks on the “Next Page” or “Previous Page” buttons. For the paging to work the data needs to be ordered. The sort parameter indicates what column to sort the data by. It uses the name you give the column in the RecordDef when creating the Grid. The dir parameter says what direction the ordering should be in, ascending or descending. The start parameter is the number of the first record to return and limit is the number of records to return.
Now that we know what the user is asking for it’s time to build the SQL query that will fetch that data. The method createMainSQLQuery() creates a query string to select the required columns and order the ResultSet.
In the next section we call getConnection(). This method (and createConnectionPool()) demonstrate how to create an Oracle connection pool using OracleDataSource and it’s method setConnectionCachingEnabled().
The method queryForPersonsUsingLimits() is where we actually execute the query. In the first section the query is wrapped in some SQL that fetches only the rows we want starting at row start and returning only numberToReturn records. I picked this up from this Ask Tom article. It’s well worth a read if you’re interested in how the ROWNUM pseudocolumn works.
The next section takes the query ResultSet, creates a Person object for each row and puts each on an ArrayList.
Back in doPost() the ArrayList of Persons is serialized to JSON text using the JSON classes freely availabe here. The method getTotalNumberOfPersons() is called to retrieve the total number of rows the query would return if we weren’t limiting the results. The reason we need this is because the Grid will have a message at the bottom saying like “Displaying 1 to 20 of 100”. The getTotalNumberOfPersons() method reuses the SQL we created earlier and simply wraps it in a SELECT COUNT(*).
Finally we write the JSON text to the output stream which is sent back to the browser.
Deploying the servlet into a web application is simply a matter of adding servlet and servlet-mapping entries to the application’s web.xml. For testing purposed I chose to use the web application created by GWT. By default this application is located in the \<project dir>\tomcat\webapps\ROOT\. Here are the entries I added…
<servlet>
<servlet-name>GetPersonsServlet</servlet-name>
<servlet-class>com._17od.GetPersonsServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>GetPersonsServlet</servlet-name>
<url-pattern>/persons</url-pattern>
</servlet-mapping>
Here’s a sample of the JSON returned from the URL http://localhost:8888/persons?start=2&limit=3.
{
"persons": [
{
"class": "class com._17od.servlets.Person",
"dateOfBirth": "1901-01-17",
"firstname": "Laurel",
"id": 3,
"lastname": "Davis"
},
{
"class": "class com._17od.servlets.Person",
"dateOfBirth": "1892-05-16",
"firstname": "Ted",
"id": 4,
"lastname": "Schoenberger"
},
{
"class": "class com._17od.servlets.Person",
"dateOfBirth": "2001-05-19",
"firstname": "Rebecca",
"id": 5,
"lastname": "Taylor"
}
],
"totalPerons": 20
}
The JSON implementation I’m using includes the name of the class being serialized, hence the class attribute.
Since I’ve included all the other code here’s the Person class,
package com._17od.gwtexamples.servlets;
import java.util.Date;
public class Person {
private int id;
private String firstname;
private String lastname;
private Date dateOfBirth;
public Date getDateOfBirth() {
return dateOfBirth;
}
public void setDateOfBirth(Date dateOfBirth) {
this.dateOfBirth = dateOfBirth;
}
public String getFirstname() {
return firstname;
}
public void setFirstname(String firstname) {
this.firstname = firstname;
}
public int getId() {
return id;
}
public void setId(int personId) {
this.id = personId;
}
public String getLastname() {
return lastname;
}
public void setLastname(String lastname) {
this.lastname = lastname;
}
}
To finish off here’s a screenshot of the Grid,
