Feeding your own data picker (part 1)
3 March 2008Once you configure your data picker, you need to provide it with data in order for it to work. For the demo page, I used a PHP-based data source, but that was actually a first for me. For regular use, I always create my data provider using Java. One of the nice things about utilizing a standard convention for things is that just about all of the code for something is virtually the same. In the case of our Ajax data pickers, almost all of the Java code necessary to present the desired data in XML format, including data acquisition and caching, can be consolidated into a single abstract class:
package org.restafarian.core.servlets;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
/**
* <p>This abstract class is the base code for select list servlets.</p>
*/
public abstract class SelectListServletBase extends RestServletBase {
private String contextKey = "";
private String dataSourceName = "";
private String defaultStartsWith = "%";
private String defaultContains = "";
private String defaultOrderBy = "id";
private Map dataFields = new HashMap();
/**
* <p>The Servlet "doGet()" method.</p>
*
* @param req the <code>HttpServletRequest</code> object
* @param res the <code>HttpServletResponse</code> object
* @throws ServletException
* @throws IOException
*/
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
StringBuffer buffer = new StringBuffer();
buffer.append("<?xml version=\"1.0\"?>\n");
buffer.append("<options size=\"");
List options = getOptions(req);
if (options != null) {
buffer.append(options.size());
buffer.append("\">\n");
buffer.append(formatOptions(req, options));
} else {
buffer.append("0\">\n");
}
buffer.append("</options>\n");
PrintWriter pw = res.getWriter();
pw.println(buffer);
}
/**
* <p>The Servlet "doPost()" method.</p>
*
* @param req the <code>HttpServletRequest</code> object
* @param res the <code>HttpServletResponse</code> object
* @throws ServletException
* @throws IOException
*/
public void doPost(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
res.sendError(405, "Method Not Allowed. Use the \"GET\" method
for this URL");
}
/**
* <p>The Servlet "doPut()" method.</p>
*
* @param req the <code>HttpServletRequest</code> object
* @param res the <code>HttpServletResponse</code> object
* @throws ServletException
* @throws IOException
*/
public void doPut(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
res.sendError(405, "Method Not Allowed. Use the \"GET\" method
for this URL");
}
/**
* <p>The Servlet "doDelete()" method.</p>
*
* @param req the <code>HttpServletRequest</code> object
* @param res the <code>HttpServletResponse</code> object
* @throws ServletException
* @throws IOException
*/
public void doDelete(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
res.sendError(405, "Method Not Allowed. Use the \"GET\" method
for this URL");
}
/**
* <p>This method formats the options.</p>
*
* @param req the <code>HttpServletRequest</code> object
* @param options the <code>List</code> of options
* @return the formatted options
*/
protected String formatOptions(HttpServletRequest req, List options) {
StringBuffer buffer = new StringBuffer();
int startIndex = 0;
int count = 0;
String start = req.getParameter("start");
if (start != null && start.length() > 0) {
startIndex = Integer.parseInt(start.trim());
}
String countString = req.getParameter("count");
if (countString != null && countString.length() > 0) {
count = Integer.parseInt(countString.trim());
}
if (count < 1) {
count = 10;
}
if (startIndex > 0) {
startIndex = startIndex - 1;
}
int endIndex = startIndex + count;
if (endIndex > options.size()) {
endIndex = options.size();
}
for (int x=startIndex; x<endIndex; x++) {
int index = x + 1;
Map thisItem = (Map) options.get(x);
buffer.append(" <option index=\"");
buffer.append(index);
buffer.append("\">\n");
Iterator i = dataFields.keySet().iterator();
while (i.hasNext()) {
String fieldName = (String) i.next();
buffer.append(" <field name=\"");
buffer.append(fieldName);
buffer.append("\">");
if (thisItem.get(fieldName) != null) {
buffer.append(thisItem.get(fieldName));
}
buffer.append("</field>\n");
}
buffer.append(" </option>\n");
}
return buffer.toString();
}
/**
* <p>This method retrieves the requested options from the session.</p>
*
* @param req the <code>HttpServletRequest</code> object
* @return the options
*/
protected List getOptions(HttpServletRequest req) {
List options = new ArrayList();
Date rightNow = new Date();
String startsWith = req.getParameter("startsWith");
String contains = req.getParameter("contains");
String orderBy = req.getParameter("orderBy");
if (startsWith == null || startsWith.length() < 1) {
startsWith = defaultStartsWith;
}
if (contains == null || contains.length() < 1) {
contains = defaultContains;
}
if (orderBy == null || orderBy.length() < 1) {
orderBy = defaultOrderBy;
}
String cacheKey = startsWith + ":" + contains + ":" + orderBy +
getCacheKeyQualifier(req);
Map queryCache = getQueryCache(req, contextKey);
if (queryCache.containsKey(cacheKey)) {
Map results = (Map) queryCache.get(cacheKey);
options = (List) results.get("options");
results.put("lastAccessed", rightNow);
Integer accessCount = (Integer) results.get("accessCount");
results.put("accessCount", new Integer(accessCount.intValue() + 1));
} else {
options = fetchOptions(startsWith, contains, orderBy);
if (options != null && options.size() > 0) {
Map results = new HashMap();
results.put("creationDate", rightNow);
results.put("lastAccessed", rightNow);
results.put("accessCount", new Integer(1));
results.put("options", options);
queryCache.put(cacheKey, results);
}
}
return options;
}
/**
* <p>This method returns any additional qualifiers for the key
* to the query cache.</p>
*
* @param req the <code>HttpServletRequest</code> object
* @return the cache key qualifier
*/
protected String getCacheKeyQualifier(HttpServletRequest req) {
return "";
}
/**
* <p>This method obtains the requested options from the data source.</p>
*
* @param startsWith the "starts with" query parameter
* @param contains the "contains" query parameter
* @param orderBy the sort order
* @return the options
*/
protected List fetchOptions(String startsWith, String contains, String orderBy) {
List options = new ArrayList();
DataSource dataSource = getDataSource();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String qs = getQueryStatement(startsWith, contains, orderBy);
Map items = new TreeMap();
try {
conn = dataSource.getConnection();
conn.setAutoCommit(false);
stmt = conn.createStatement();
rs = stmt.executeQuery(qs);
while (rs.next()) {
Map thisItem = new HashMap();
Iterator i = dataFields.keySet().iterator();
while (i.hasNext()) {
String fieldName = (String) i.next();
String tableField = (String) dataFields.get(fieldName);
thisItem.put(fieldName, filter(rs.getString(tableField)));
}
if (orderBy == null || orderBy.length() < 1) {
items.put(thisItem.get("id"), thisItem);
} else {
options.add(thisItem);
}
}
} catch (SQLException e) {
System.out.println("SQL statement: " + qs);
System.out.println("SQL error: " + e.toString() + "; " + e.getMessage());
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException sqle) {
System.out.println("SQL error: " + sqle.toString() + "; " +
sqle.getMessage());
sqle.printStackTrace();
}
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException sqle) {
System.out.println("SQL error: " + sqle.toString() + "; " +
sqle.getMessage());
sqle.printStackTrace();
}
stmt = null;
}
if (conn != null) {
try {
conn.commit();
conn.close();
} catch (SQLException sqle) {
System.out.println("SQL error: " + sqle.toString() + "; " +
sqle.getMessage());
sqle.printStackTrace();
}
conn = null;
}
}
if (orderBy == null || orderBy.length() < 1) {
if (!items.isEmpty()) {
Iterator i = items.keySet().iterator();
while (i.hasNext()) {
String key = i.next().toString();
options.add(items.get(key));
}
}
}
return options;
}
/**
* <p>This method creates the SQL statement.</p>
*
* @param startsWith the "starts with" query parameter
* @param contains the "contains" query parameter
* @param orderBy the sort order
* @return the SQL statement
*/
protected abstract String getQueryStatement(String startsWith,
String contains, String orderBy);
/**
* <p>Fetch the query cache from the servlet context, or creates a new one.</p>
*
* @param req the <code>HttpServletRequest</code> object
* @param contextKey the servlet context key
*/
protected static Map getQueryCache(HttpServletRequest req, String contextKey) {
Map queryCache = (Map) req.getSession().getServletContext().getAttribute(contextKey);
// if no cache, then make one
if (queryCache == null) {
queryCache = new HashMap();
queryCache.put("_lastCacheReviewDateTime", new Date());
req.getSession().getServletContext().setAttribute(contextKey, queryCache);
}
Date lastCacheReviewDate = (Date) queryCache.get("_lastCacheReviewDateTime");
if (lastCacheReviewDate.before(getReviewCompareDate())) {
reviewQueryCache(queryCache);
}
return queryCache;
}
/**
* <p>Review the query cache and remove expired items.</p>
*/
protected static void reviewQueryCache(Map queryCache) {
Date cutoffDate = getReviewCompareDate();
List toBeRemoved = new ArrayList();
Iterator i = queryCache.keySet().iterator();
while (i.hasNext()) {
String key = (String) i.next();
if (!"_lastCacheReviewDateTime".equals(key)) {
Map results = (Map) queryCache.get(key);
Date creationDate = (Date) results.get("creationDate");
if (creationDate.before(cutoffDate)) {
toBeRemoved.add(key);
}
}
}
i = toBeRemoved.iterator();
while (i.hasNext()) {
queryCache.remove(i.next());
}
queryCache.put("_lastCacheReviewDateTime", new Date());
}
/**
* <p>Fetch the query cache from the servlet context, or creates a new one.</p>
*/
protected static Date getReviewCompareDate() {
Calendar calendar = new GregorianCalendar();
calendar.setTime(new Date());
calendar.add(Calendar.DATE, -1);
return calendar.getTime();
}
/**
* <p>This method is used to look up the <code>DataSource</code>
* by name.</p>
*
* @return <code>dataSource</code> - the <code>DataSource</code>
*/
protected DataSource getDataSource() {
DataSource dataSource = null;
try {
Context ctx = new InitialContext(new Hashtable());
dataSource = (DataSource) ctx.lookup(dataSourceName);
} catch (Throwable t) {
t.printStackTrace();
}
return dataSource;
}
/**
* @return Returns the contextKey.
*/
protected String getContextKey() {
return contextKey;
}
/**
* @param contextKey The contextKey to set.
*/
protected void setContextKey(String contextKey) {
this.contextKey = contextKey;
}
/**
* @return Returns the dataFields.
*/
protected Map getDataFields() {
return dataFields;
}
/**
* @param dataFields The dataFields to set.
*/
protected void setDataFields(Map dataFields) {
this.dataFields = dataFields;
}
/**
* @return Returns the dataSourceName.
*/
protected String getDataSourceName() {
return dataSourceName;
}
/**
* @param dataSourceName The dataSourceName to set.
*/
protected void setDataSourceName(String dataSourceName) {
this.dataSourceName = dataSourceName;
}
/**
* @return Returns the defaultContains.
*/
protected String getDefaultContains() {
return defaultContains;
}
/**
* @param defaultContains The defaultContains to set.
*/
protected void setDefaultContains(String defaultContains) {
this.defaultContains = defaultContains;
}
/**
* @return Returns the defaultOrderBy.
*/
protected String getDefaultOrderBy() {
return defaultOrderBy;
}
/**
* @param defaultOrderBy The defaultOrderBy to set.
*/
protected void setDefaultOrderBy(String defaultOrderBy) {
this.defaultOrderBy = defaultOrderBy;
}
/**
* @return Returns the defaultStartsWith.
*/
protected String getDefaultStartsWith() {
return defaultStartsWith;
}
/**
* @param defaultStartsWith The defaultStartsWith to set.
*/
protected void setDefaultStartsWith(String defaultStartsWith) {
this.defaultStartsWith = defaultStartsWith;
}
}
For a standard pick list backed up by data stored on a JDBC datasource, the implementing class simply needs to provide a few data values in the constructor, and then override the getQueryStatement method with code to utilize the passed startsWith, contains, and orderBy values to create the appropriate SQL. For pick lists backed up by other forms of data, such as LDAP, Lotus Domino, Web Services, or even plain text files such as in our Country list example, you can override the entire fetchOptions method to replace the JDBC/SQL code with code appropriate for your alternative data source.
Next time we’ll use our RFA database to put together an SQL example, just to show how simple a standard SQL implementation can be.
Sorry, the comment form is closed at this time.
