Feeding your own data picker (part 2)
6 March 2008Now that we have all of the standard, reusable code for a selection list feeder servlet stuffed into an abstract base class, it will not take much additional code to extend that class to complete a specific implementation. Since we’ve been working with the Request for Approval lately, and we already have the database tables defined, I figured that RFAs were as good of a choice as any other for an example. To extend the base class for a standard JDBC/SQL implementation, all that is required is to implement the getQueryStatement() method to build the SQL to obtain the data, and provide values in the constructor for the following data fields:
- contextKey – the unique key to the cache of selected data
- dataSourceName – the name of the JDBC dataSource
- dataFields – the list of data fields to be returned by this servlet
Here is the complete Java class for the RequestForApprovalSelectorServlet:
package org.restafarian.approval.servlets;
import java.util.HashMap;
import java.util.Map;
import org.restafarian.core.servlets.SelectListServletBase;
/**
* <p>RequestForApproval data picker servlet.</p>
*/
public class RequestForApprovalSelectorServlet extends SelectListServletBase {
private static final long serialVersionUID = 1;
/**
* <p>Constructs a new <code>RequestForApprovalSelectorServlet</code>.</p>
*/
public RequestForApprovalSelectorServlet(){
super();
setContextKey("request.for.approval.selector.results.cache");
setDataSourceName("java:comp/env/jdbc/approval");
setDataFields(defineDataFields());
}
/**
* <p>Builds the list of fields to include.</p>
*
* @return the static list of fields to include
*/
private Map defineDataFields() {
Map fields = new HashMap();
fields.put("id", "id");
fields.put("type", "type");
fields.put("state", "state");
fields.put("payloadId", "payloadId");
fields.put("payloadURI", "payloadURI");
fields.put("description", "description");
fields.put("authorId", "authorId");
fields.put("authorURI", "authorURI");
fields.put("authorName", "authorName");
fields.put("dateTime", "dateTime");
return fields;
}
/**
* <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 String getQueryStatement(String startsWith, String
contains, String orderBy) {
StringBuffer buffer = new StringBuffer();
buffer.append(" select\n");
buffer.append(" id,\n");
buffer.append(" type,\n");
buffer.append(" state,\n");
buffer.append(" payloadId,\n");
buffer.append(" payloadURI,\n");
buffer.append(" description,\n");
buffer.append(" authorId,\n");
buffer.append(" authorURI,\n");
buffer.append(" authorName,\n");
buffer.append(" dateTime\n");
buffer.append(" from\n");
buffer.append(" requestforapproval\n");
buffer.append(" where\n");
if ("id".equalsIgnoreCase(orderBy)) {
// sort and select by id
if (contains != null && contains.length() > 0) {
buffer.append(" lower(id) like '%" + contains.toLowerCase() +
"%'\n");
} else {
buffer.append(" lower(id) like '" + startsWith.toLowerCase() +
"%' \n");
}
buffer.append(" order by\n");
buffer.append(" id\n");
} else if ("type".equalsIgnoreCase(orderBy)) {
// sort and select by type
if (contains != null && contains.length() > 0) {
buffer.append(" lower(type) like '%" + contains.toLowerCase() +
"%'\n");
} else {
buffer.append(" lower(type) like '" + startsWith.toLowerCase() +
"%'\n");
}
buffer.append(" order by\n");
buffer.append(" type, id\n");
} else if ("state".equalsIgnoreCase(orderBy)) {
// sort and select by state
if (contains != null && contains.length() > 0) {
buffer.append(" lower(state) like '%" + contains.toLowerCase() +
"%'\n");
} else {
buffer.append(" lower(state) like '" + startsWith.toLowerCase() +
"%'\n");
}
buffer.append(" order by\n");
buffer.append(" state, id\n");
} else if ("payloadId".equalsIgnoreCase(orderBy)) {
// sort and select by payloadId
if (contains != null && contains.length() > 0) {
buffer.append(" lower(payloadId) like '%" +
contains.toLowerCase() + "%'\n");
} else {
buffer.append(" lower(payloadId) like '" +
startsWith.toLowerCase() + "%'\n");
}
buffer.append(" order by\n");
buffer.append(" payloadId, id\n");
} else if ("payloadURI".equalsIgnoreCase(orderBy)) {
// sort and select by payloadURI
if (contains != null && contains.length() > 0) {
buffer.append(" lower(payloadURI) like '%" +
contains.toLowerCase() + "%'\n");
} else {
buffer.append(" lower(payloadURI) like '" +
startsWith.toLowerCase() + "%'\n");
}
buffer.append(" order by\n");
buffer.append(" payloadURI, id\n");
} else if ("description".equalsIgnoreCase(orderBy)) {
// sort and select by description
if (contains != null && contains.length() > 0) {
buffer.append(" lower(description) like '%" +
contains.toLowerCase() + "%'\n");
} else {
buffer.append(" lower(description) like '" +
startsWith.toLowerCase() + "%'\n");
}
buffer.append(" order by\n");
buffer.append(" description, id\n");
} else if ("authorId".equalsIgnoreCase(orderBy)) {
// sort and select by authorId
if (contains != null && contains.length() > 0) {
buffer.append(" lower(authorId) like '%" +
contains.toLowerCase() + "%'\n");
} else {
buffer.append(" lower(authorId) like '" +
startsWith.toLowerCase() + "%'\n");
}
buffer.append(" order by\n");
buffer.append(" authorId, id\n");
} else if ("authorURI".equalsIgnoreCase(orderBy)) {
// sort and select by authorURI
if (contains != null && contains.length() > 0) {
buffer.append(" lower(authorURI) like '%" +
contains.toLowerCase() + "%'\n");
} else {
buffer.append(" lower(authorURI) like '" +
startsWith.toLowerCase() + "%'\n");
}
buffer.append(" order by\n");
buffer.append(" authorURI, id\n");
} else if ("authorName".equalsIgnoreCase(orderBy)) {
// sort and select by authorName
if (contains != null && contains.length() > 0) {
buffer.append(" lower(authorName) like '%" +
contains.toLowerCase() + "%'\n");
} else {
buffer.append(" lower(authorName) like '" +
startsWith.toLowerCase() + "%'\n");
}
buffer.append(" order by\n");
buffer.append(" authorName, id\n");
} else if ("dateTime".equalsIgnoreCase(orderBy)) {
// sort and select by dateTime
if (contains != null && contains.length() > 0) {
buffer.append(" lower(dateTime) like '%" +
contains.toLowerCase() + "%'\n");
} else {
buffer.append(" lower(dateTime) like '" +
startsWith.toLowerCase() + "%'\n");
}
buffer.append(" order by\n");
buffer.append(" dateTime, id\n");
}
return buffer.toString();
}
}
I’ve already added both this and the abstract base class to the example .ear file that I have been distributing, but there are a couple of additional things that I would like to do before I release another version. Hopefuly, I can get all of that done before the start of next week.
Sorry, the comment form is closed at this time.
