Recently, I worked on a Customer Relationship Management (CRM) application that enables the business to manage contact and relationship information for customers and prospects. A solution for persisting session state was discussed in my previous article, Persisting Session State: CRM Recent Customer List. This article describes the solution to another challenge posed by this CRM application: using EJBs to enable flexible customer queries.
One of the application requirements is customer search, enabling the user to enter one or more criteria to generate a filtered list of customers. This CRM application accesses the customer database via EJBs, to enforce transactional integrity and employ a layered architecture. The goal for this requirement is to enable flexible queries against the customer database within this EJB architecture. You can apply this approach to a wide range of problems involving dynamic EJB queries. The solution was developed using VisualAge® for JavaTM, Enterprise Edition, Version 3.5.3, and WebSphere® Application Server, Advanced Edition, Version 3.5.3.
WebSphere Advanced Edition supports three different techniques for EJB "custom" finders: select string, where clause, and method-based.
- select string custom finder
Lets you specify a static SQL SELECT string in the<beanClassName>FinderHelperinterface. This interface gives the EJB container "hints" on how to implement the query. While this approach is straightforward, it is not recommended for new WebSphere development. Here is an example of select clause specification:
public static final String findGreaterThanQueryString = "SELECT * FROM MYTABLE T1 WHERE T1.VALUE > ?"; - where clause custom finder
Requires only that you specify the WHERE clause of the SQL query string in the<beanClassName>FinderHelperinterface. This is the recommended approach for simple EJB queries, as in the following example:
public static final String findGreaterThanWhereClause = "T1.VALUE > ?"; - method custom finder
Enables you to dynamically construct a java.sql.PreparedStatement for container queries. You must create a class in the same package as the EJB deployed code and name it<beanClassName>FinderObject. The class must extend com.ibm.vap.finders.VapEJSJDBCFinderObject and must implement the<beanClassName>FinderHelperinterface. Then you can implement a method to generate the required PreparedStatement, using a method signature as follows:
public java.sql.PreparedStatement findGreaterThan(int threshold) throws Exception;
We can design a solution based on the method custom finder approach that is much easier to implement and maintain than either of the static finder approaches, which require many different static finders to cover all scenarios. This article describes how to take advantage of this method custom finder to assemble complex, dynamic queries against EJB data.
The method custom finder approach lets you build complex queries on the fly. Helper classes make statement construction easier. See Figure 1 below.
Figure 1.
The SearchCriteria class is just a bean that encapsulates the information required to construct a single SQL WHERE clause: field (column name), operator (<, =, >, etc.) and criteria (represented as an Object; String, Integer, Long, Date, and Timestamp currently supported). The SearchCriteriaHelper does the real work of taking one or more SearchCriteria, and constructing an appropriate PreparedStatement using the following two methods.
public String getWhereClause() {
StringBuffer where = new StringBuffer();
for (int i=0; i < searchCriteria.length; i++) {
SearchCriteria sc = searchCriteria[i];
if (i > 0) where.append(" AND ");
where.append(sc.getField()).append("
").append(sc.getOperator()).append(" ?");
}
return where.toString();
}
public void injectParams(PreparedStatement ps)
throws SQLException, SearchCriteriaException {
// inject parameters based on type
for (int i=0; i < searchCriteria.length; i++) {
SearchCriteria sc = searchCriteria[i];
Object o = sc.getCriteria();
if (o instanceof String) {
ps.setString(i+1, (String)sc.getCriteria());
} else if (o instanceof Integer) {
ps.setInt(i+1, ((Integer)o).intValue());
} else if (o instanceof Long) {
ps.setLong(i+1, ((Long)o).longValue());
} else if (o instanceof java.sql.Date) {
ps.setDate(i+1, ((java.sql.Date)o));
} else if (o instanceof java.sql.Timestamp) {
ps.setTimestamp(i+1, ((java.sql.Timestamp)o));
} else {
throw new SearchCriteriaException("Criteria
type not recognized.");
}
}
} |
Using these helper classes, we create a suitable FinderObject, and update the FinderHelper and Home interfaces to support searching with one or more SearchCriteria. See Figure 2 below.
Figure 2.
Now it is a simple matter to implement the FinderObject's findBySearchCriteria method.
public java.sql.PreparedStatement
findBySearchCriteria(SearchCriteria[] criteria)
throws Exception {
SearchCriteriaHelper helper =
new SearchCriteriaHelper(criteria);
PreparedStatement ps =
getMergedPreparedStatement(helper.getWhereClause());
helper.injectParams(ps);
return ps;
} |
A simplified but fully functional version of the CRM customer search design implementation is available for download. This includes the CustomerEJB and associated code, database schemas, and sample data.
It is easy to test your EJB Finder in VisualAge for Java using the EJB Test Client. The Test Client relies on Java reflection, and is a powerful tool for unit testing EJBs. The key to using it is to remember to right-click to invoke methods and construct objects. See Figure 3 below.
Figure 3.
Set up one of more search criteria by instantiating the proper objects, then invoke the finder and inspect the returned remote objects. JDBC exceptions will be returned for bogus field names and operators. For more information on using the EJB Test Client, see the VisualAge for Java online help documentation.
I hope that you have found these techniques useful additions to your bag of development tricks.
| Name | Size | Download method |
|---|---|---|
| finder.zip | 29 KB | FTP |HTTP |
Information about download methods
Tim Biernat has held various research and development positions at General Dynamics and Motorola, and has taught internationally for IBM and Learning Tree International. Tim was trained as an electrical engineer at Marquette University and the University of Texas, and his professional experience spans 17 years, including research in fault tolerant real-time avionics software, and development of many distributed object business systems. Currently, Tim is principal of softwareMentor.com, a distributed object technology consulting firm based in Milwaukee.
Comments (Undergoing maintenance)
Table of contents
Next steps from IBM
With the WebSphere Application Server you can benefit from the simplified EJB 3.0 development experience, new persistence model, and interceptors.
- Try: Download a free trial version of WebSphere Application Server Community Edition, the lightweight Java 5 application server with robust EJB 3.0 funtionality.
- Article: Learn how call patterns and components involved in making calls to EJB components deployed in the EJB container operate in WebSphere Application Server.
- Tutorial: This tutorials shows you how to create an enterprise J2EE application that implements both Plain Old Java Object (POJO) and EJB Web services endpoints.
- Buy: WebSphere Application Server
My developerWorks community
Tags
Use the slider bar to see more or fewer tags.
Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).
My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
