docs/org.eclipse.jst.j2ee.doc.user/topics/cefinders.html - gerrit/jeetools/webtools.javaee - Git at Google

 <!DOCTYPE html
   PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 <html lang="en-us" xml:lang="en-us">
 <head>
 <link rel="stylesheet" type="text/css" href="../../org.eclipse.wst.doc.user/common.css" />
 <title>Finder methods for CMP entity beans</title>
 </head>
 <body id="cefinders"><a name="cefinders"><!-- --></a>

 <h1 class="topictitle1">Finder methods for CMP entity beans</h1>
 <div><p>This topic describes support for queries in EJB 2.x CMP entity
 beans. For EJB 1.1 CMP entity beans, WebSphere Application Server provides
 extensions for finder support.</p><p></p>
 <div class="skipspace"><h4 class="sectiontitle">Query support in EJB 2.x</h4><p>EJB 2.x provides a query
 syntax called EJB Query Language (EJB QL) for finder and select methods of
 CMP entity beans. Finder methods obtain one or more entity bean instances
 from a database, and are defined in the home interface. </p>
 <p>The <samp class="codeph">&lt;query&gt;</samp> element
 is used to define the query for the finder method in the deployment descriptor,
 for every finder method except findByPrimaryKey(key). Queries specified in
 the deployment descriptor are compiled into SQL during deployment. The syntax
 of the query is contained in the <samp class="codeph">&lt;ejb-ql&gt;</samp> element of the <samp class="codeph">&lt;query&gt;</samp> element:</p>
 <div class="p"> <pre>&lt;query&gt;
 &lt;query-method&gt;
 &lt;method-name&gt;&lt;/method-name&gt;
 &lt;method-params&gt;
 &lt;method-param&gt;&lt;/method-param&gt;
 &lt;/method-params&gt;
 &lt;/query-method&gt;
 &lt;result-type-mapping&gt;&lt;/result-type-mapping&gt;
 &lt;ejb-ql&gt;&lt;/ejb-ql&gt;
 &lt;/query&gt;</pre>
 </div>
 <p>See <a href="../topics/teaddejbqlquery.html">Adding finder methods to EJB 2.x beans using EJB QL</a> for more help.</p>
 <p>For details on EJB QL, go to the <a href="http://www-306.ibm.com/software/webservers/appserv/infocenter.html" target="_blank">WebSphere Application Server Information Center</a> and
 search for the keywords "EJB QL".</p>
 </div>
 <div class="skipspace"><h4 class="sectiontitle">Finder support in EJB 1.x</h4><div class="important"><span class="importanttitle">Important: </span>Finder
 support for EJB 1.1 CMP entity beans is a WebSphere Application Server extension
 to the EJB specification. The Finders section on the Bean page of the EJB
 deployment descriptor editor only displays for EJB 1.1 beans in projects that
 are targeted to WebSphere Application Server.</div>
 <p>There are three types
 of EJB custom finders that are currently supported and that can be used in
 combination:</p>
 <ul><li>SELECT</li>
 <li>WHERE</li>
 <li>Method</li>
 <li>OOQL - this is the only finder type that you can migrate to EJBQL for
 EJB 2.x</li>
 </ul>
 <p>For each finder method that is defined in the EJB home interface (other
 than findByPrimaryKey and those finder methods generated to support associations),
 one of the following query strings or declarations must be defined in the
 finder helper interface (in file <i>beanClassName</i>FinderHelper.java):</p>
 <ul><li>An SQL query string (for SELECT and WHERE custom finders)</li>
 <li>A method declaration (for method custom finders)</li>
 </ul>
 <p>Note that the return type java.util.Enumeration or java.util.Collection
 on the finder in the home interface indicates that this finder may return
 more than one bean. Using the remote interface as the return type indicates
 that a single bean is returned. This is true for all of the supported types
 of custom finders. The code generated into the persister handles this distinction.</p>
 <p>It
 is important to note that if you are working with existing EJB 1.0 JAR files,
 you can continue to define SQL query strings or method declarations in the
 helper finder interface. (The SQL query string is actually a field on the
 interface describing the full SELECT statement or just the WHERE clause.)
 However, for any new development work that requires you to work with EJB JAR
 files at a level higher than 1.0, it is required that you use an extension
 document rather than the finder helper interface to define your queries and
 method declarations. This is discussed later in this topic.</p>
 <p><span class="uicontrol">Maintaining
 SQL compatibility across different databases</span></p>
 <p>For SELECT,
 WHERE, and method custom finders, there may be situations where the finder
 methods access different databases. In this case, it is necessary to ensure
 that SQL compatibility is maintained across the different databases. For instance,
 it is possible that the SQL syntax used by each database is different. In
 these situations, use the SQL extensions defined by JDBC to resolve the database
 differences.</p>
 <p>For example, assume that you are developing a CMP entity
 bean that requires a finder method that involves a timestamp/date field. Also
 assume that this bean will be deployed to DB2<sup>®</sup> and Oracle databases. The problem is that
 the format of the timestamp/date fields in DB2 and Oracle are different, which causes
 difficulties in defining one WHERE clause for use with both DB2 and Oracle.
 The solution to this particular problem is to use the SQL Escape sequence.</p>
 <p>Additional
 information about SELECT, WHERE, and method custom finders is found in the
 following sections.</p>
 <p><span class="uicontrol">Note:</span> when working with custom
 finders, do not pass NULL.</p>
 <dl><dt class="bold">SELECT custom finders</dt>
 <dd><p>A SELECT custom finder is used to enter an entire select statement
 in the finder helper interface to define the SQL query.</p>
 <p>The use of SELECT
 custom finders is supported for compatibility with earlier releases. Using
 SELECT custom finders is discouraged in this and future releases.</p>
 </dd>
 <dt class="bold">WHERE custom finders</dt>
 <dd><p>A custom finder in which you enter only the filtering WHERE clause
 into the finder helper interface is called a WHERE custom finder. For example,
 if you have a VAPGarage CMP entity bean that is mapped to a database table
 with a column named CAPACITY, the finder helper interface would look like
 this:</p>
 <div class="p"> <pre>public interface VapGarageBeanFinderHelper {
    public final static String
       findCapacityGreaterThanWhereClause =
          "T1.CAPACITY &gt; ?";
 }</pre>
 </div>
 <p>Notice that any dependency on the shape of the results
 is removed from the string. Two dependencies still exist: </p>
 <ul><li>The name of the column (CAPACITY)</li>
 <li>The alias for the table (T1)</li>
 </ul>
 <p>The name of the column would change only if you took action to change
 it.</p>
 <p>The alias for the table will be the same from one generation to
 the next unless tables are added to or removed from the mapping. In single-table
 cases, this may not seem significant (the alias is always T1). When multiple
 tables are used, this is very important.</p>
 <p>Any table references in your
 handwritten SQL code must match the table aliases set up in the genericFindSqlString
 field. This is declared in the enterprise bean's generated persister.</p>
 <p>As
 in the SELECT custom form, the number of finder parameters must match the
 number of injection points (the <samp class="codeph">?</samp> characters) in the WHERE
 clause. Also, as in the SELECT form, the type of the parameter will be used
 to determine which java.sql.PreparedStatement set call will be used to inject
 each parameter. The parameter types must be compatible with the column types.
 If the type is an object type and the parameter is null, a setNull call will
 be used.</p>
 <p>For example, the home interface may contain the following method:</p>
 <div class="p"> <pre>public java.util.Enumeration findGreaterThan (int threshold) throws
    java.rmi.RemoteException, javax.ejb.FinderException;</pre>
 </div>
 <p>In
 this finder helper interface, the WHERE custom finder is one of the forms
 that you can provide. For example (line broken for publication):</p>
 <div class="p"> <pre>public static final String findGreaterThanWhereClause =
     "T1.VALUE &gt; ?";</pre>
 </div>
 <p>Note, however, that if you have an SQL
 statement that contains no WHERE clause, such as <samp class="codeph">SELECT * FROM MYTABLE</samp>,
 you should use a query string that always evaluates to <i>true</i>. For example:</p>
 <div class="p"> <pre>public static final String findALLWhereClause = "1 = 1";</pre>
 </div>
 </dd>
 <dt class="bold">Method custom finders</dt>
 <dd><p>A custom finder in which you enter a method signature into the finder
 helper interface is called a method custom finder. It is the most flexible
 type of custom finder, but it requires more work on your part. Using the same
 garage example as before, the finder helper interface would look like this:</p>
 <div class="p"> <pre>public interface VapGarageBeanFinderHelper {
   public java.sql.PreparedStatement findCapacityGreaterThan(int threshold)
     throws Exception;
 }</pre>
 </div>
 <p>Unlike the SELECT and WHERE forms, however, this is not
 enough for method custom finders. An implementation of this method is needed.
 You provide your implementation of the method in a class that follows these
 rules:</p>
 <ul><li>The name of the class is <i>beanClassName</i>FinderObject (VapGarageBeanFinderObject,
 in this example) and is in the same package as the bean class.</li>
 <li>The class must extend com.ibm.vap.finders.VapEJSJDBCFinderObject and must
 implement the bean's finder helper interface.</li>
 <li>Any table references in your handwritten SQL code must match the table
 aliases set up in the genericFindSqlString field. This is declared in the
 enterprise bean's generated persister.</li>
 </ul>
 <p>To finish off our example, the finder object would look like this.</p>
 <div class="p"> <pre>/**
 * Implementation class for methods in
 * VapGarageBeanFinderHelper.
 */
 public class VapGarageBeanFinderObject extends
    com.ibm.vap.finders.VapEJSJDBCFinderObject implements
    VapGarageBeanFinderHelper {

    public java.sql.PreparedStatement
    findCapacityGreaterThan(int threshold)
    throws Exception {

       PreparedStatement ps = null;
       int mergeCount = getMergedWhereCount();
       int columnCount = 1;
       ps = getMergedPreparedStatement("T1.CAPACITY &gt; ?");
       for (int i=0; i&lt;(columnCount*mergeCount); i=i+columnCount) {
           ps.setInt(i+1, threshold);
       }
       return ps;
    }
 }</pre>
 </div>
 <p>In the case of any method custom finder, the generated
 persister uses your implementation to create the PreparedStatement to be executed.
 The persister will execute the PreparedStatement and handle the results. The
 implementation needs help from the persister to make sure the result set for
 the query has the correct shape. The com.ibm.vap.finders.VapEJSJDBCFinderObject
 base class provides several important helper methods, some of which are shown
 in the above example. In the following table, the complete set of helper methods
 are listed and described:</p>

 <div class="skipspace"><table cellpadding="4" cellspacing="0" summary="" width="90%" frame="border" border="1" rules="all"><thead align="left"><tr><th valign="top" id="d0e191">Method</th>
 <th valign="top" id="d0e193">Description</th>
 </tr>
 </thead>
 <tbody><tr><td valign="top" headers="d0e191 ">getMergedPreparedStatement</td>
 <td valign="top" headers="d0e193 ">Takes a WHERE clause and returns a PreparedStatement with the WHERE
 clause merged into the appropriate places. The PreparedStatement will have
 the correct result set shape.</td>
 </tr>
 <tr><td valign="top" headers="d0e191 ">getMergedWhereCount</td>
 <td valign="top" headers="d0e193 ">Returns the number of times the WHERE clause is merged into the PreparedStatement.
 This is needed to know how many times to inject your query parameters into
 the PreparedStatement.</td>
 </tr>
 <tr><td valign="top" headers="d0e191 ">getPreparedStatement</td>
 <td valign="top" headers="d0e193 ">Takes a complete query string and returns a PreparedStatement. This
 can be used if for some reason you need to do your own WHERE clause merging.
 This should be a very rare case. The next two functions are provided to help
 in these extreme cases.</td>
 </tr>
 <tr><td valign="top" headers="d0e191 ">getGenericFindSqlString</td>
 <td valign="top" headers="d0e193 ">Returns the query string into which WHERE clauses are merged.</td>
 </tr>
 <tr><td valign="top" headers="d0e191 ">getGenericFindInsertPoints</td>
 <td valign="top" headers="d0e193 ">Returns an array of integers that defines the point or points in the
 getGenericFindSqlString returned query string at which the WHERE clause is
 merged. The first point in the array is the last point in the string. It is
 usually best to merge from the end of the query string since a merge at the
 end will not change the location of merges earlier in the string. The size
 of the array is the same as the value returned from getMergedWhereCount.</td>
 </tr>
 </tbody>
 </table>
 </div>
 <p>This is a rather simple case that can better be handled by a WHERE
 custom finder. More complex examples are possible that a WHERE custom finder
 simply cannot handle. For example, suppose you want a finder that takes a
 more complex object and injects it into multiple columns in a WHERE clause.
 You would end up with a finder method that looks like this:</p>
 <div class="p"> <pre>public java.sql.PreparedStatement
    findWithComplexObject(BigObject big) throws Exception {

    PreparedStatement ps = null;
    int mergeCount = getMergedWhereCount();
    int columnCount = 3;
    int anInt = big.getAnInt();
    String aString = big.getAString();
    String aLongAsString =
       com.ibm.vap.converters.VapStringToLongConverter.
       singleton().dataFrom(big.getLongObject());

    ps = getMergedPreparedStatement("(T1.ANINT &gt; ?) AND
         (T1.ASTRING = ?) AND (T2.ALONGSTR &lt;  ?)");
    for (int i=0; i&lt;(columnCount*mergeCount); i=i+columnCount) {
        ps.setInt(i+1, anInt);
        if (aString == null)
           ps.setNull(1, java.sql.Types.VARCHAR);
        else
           ps.setString(i+2, aString);
        if (aLongAsString == null)
           ps.setNull(1, java.sql.Types.VARCHAR);
        else
           ps.setString(i+3, aLongAsString);
    }
    return ps;
 }</pre>
 </div>
 <p>Even more complex examples are possible. For example,
 an object could be passed that contains the WHERE clause (or instructions
 on how to create it) in addition to the data. Or, there could be multiple
 parameters, each representing different conditions in the WHERE clause.</p>
 <p><b>Example:
 Complex method custom finder</b></p>
 <p>Although associations are not created
 with the EJB deployment tool, the following example is a logical representation
 of how a many-to-many association could be accomplished using a complex method
 custom finder. The example involves a many-to-many association between Product
 and Customer beans, using an intermediary bean (ProdCustLink) and two 1:m
 associations:</p>
 <p><br /><img src="../images/n5rpdcst.gif" alt="Graphical representation of a complex method custom finder and a many-to-many association between two beans." /><br /></p>
 <p>You can write method custom finders to span the
 relationship in either direction with just one method call. For this example,
 consider one direction only: a finder in Customer that retrieves all Customer
 instances that are associated with a given product key.</p>
 <p>Customer's home
 interface contains the appropriate method signature, as follows:</p>
 <div class="p"> <pre>java.util.Enumeration
    findCustomersByProduct(prod.cust.code.ProductKey inKey)
    throws java.rmi.RemoteException, javax.ejb.FinderException;</pre>
 </div>
 <p>Customer's
 finder helper interface contains the signature for the corresponding finder
 method:</p>
 <div class="p"> <pre>public java.sql.PreparedStatement
    findCustomersByProduct(prod.cust.code.ProductKey inKey)
    throws Exception;</pre>
 </div>
 <p>The finder object (CustomerBeanFinderObject)
 builds and caches the query string for the finder as well as implements the
 finder method.</p>
 <div class="p"> <pre>public class CustomerBeanFinderObject
    extends com.ibm.vap.finders.VapEJSJDBCFinderObject
    implements CustomerBeanFinderHelper {

    private String cachedFindCustomersByProductQueryString = null;
    .
    .
    .
 }</pre>
 </div>
 <p>Through lazy initialization in the finder object, the
 accessor method for the query-string field builds up the query string by first
 merging the WHERE condition into the query template and then adding a reference
 to the intermediate table into the FROM clause.</p>
 <p>The first half of the
 accessor method uses a genericFindInsertPoints array to locate and update
 each WHERE clause. Then, the second half of the method counts forward from
 the beginning of each FROM clause, inserts the reference to the intermediate
 table into the query string as needed, and updates the query-string field.</p>
 <div class="p"> <pre>protected String getFindCustomersByProductQueryString() {
    if (cachedFindCustomersByProductQueryString == null) {

    // Do the WHERE first
    // so that the genericFindInsertPoints are correct.
       int i;
       int[] genericFindInsertPoints = getGenericFindInsertPoints();
       StringBuffer sb = new StringBuffer(getGenericFindSqlString());
       for (i = 0; i &lt; genericFindInsertPoints.length; i++) {
          sb.insert(genericFindInsertPoints[i],
             "(T1.id = T2.Customer_id) AND (T2.Product_id = ?)");
       }

    // Make sure to update every FROM clause.
       String soFar = sb.toString();
       int fromOffset = soFar.indexOf(" FROM ");
       while (fromOffset != -1) {
          sb.insert((fromOffset+5)," ProdCustLink T2, ");
          soFar = sb.toString();
          fromOffset = soFar.indexOf(" FROM ", (fromOffset+5));
       }
       cachedFindCustomersByProductQueryString = sb.toString();
    }
    return cachedFindCustomersByProductQueryString;
 }</pre>
 </div>
 <p>After this method call, the query string looks something
 like the following:</p>
 <div class="p"> <pre>SELECT <i>&lt;columns&gt;</i> FROM ProdCustLink T2, CUSTOMER T1
     WHERE((T1.id = T2.Customer_id) AND (T2.Product_id = ?))</pre>
 </div>
 <p>Also
 in the finder object, the implemented finder uses the query string to create
 a PreparedStatement. Last but not least, the product ID value is added into
 each WHERE clause by using the superclass method getMergedWhereCount() in
 the iteration loop.</p>
 <div class="p"> <pre>public java.sql.PreparedStatement
    findCustomersByProduct(ProductKey inKey)
    throws java.lang.Exception {

    // Get the full query string and make a PreparedStatement.
    java.sql.PreparedStatement ps =
       getPreparedStatement(getFindCustomersByProductQueryString());

    // Inject the product id parameter into each merged WHERE clause.
    for (int i = 0; i &gt; getMergedWhereCount(); i++) {
       if (inKey != null)
          ps.setInt(i+1, inKey.id);
       else
 	 ps.setNull(i+1, 4);
    }

    return ps;
 }</pre>
 </div>
 </dd>
 </dl>
 </div>
 </div>

 <div><p><b class="reltaskshd">Related tasks</b><br />
 <a href="../topics/teaddejbqlquery.html" title="You can add &#34;find&#34; or &#34;ejbSelect&#34; finder methods to the home interfaces of EJB 2.x CMP beans. The finder methods use the EJB query language. A wizard guides you through the steps for adding new or existing finders to your bean.">Adding finder methods to EJB 2.x beans using EJB QL</a><br />
 </p>
 </div>		<br />
 		(C) Copyright IBM Corporation 2000, 2005. All Rights Reserved.
 </body>
 </html>
	<!DOCTYPE html
	PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
	<html lang="en-us" xml:lang="en-us">
	<head>
	<link rel="stylesheet" type="text/css" href="../../org.eclipse.wst.doc.user/common.css" />
	<title>Finder methods for CMP entity beans</title>
	</head>
	<body id="cefinders"><a name="cefinders"><!-- --></a>

	<h1 class="topictitle1">Finder methods for CMP entity beans</h1>
	<div><p>This topic describes support for queries in EJB 2.x CMP entity
	beans. For EJB 1.1 CMP entity beans, WebSphere Application Server provides
	extensions for finder support.</p><p></p>
	<div class="skipspace"><h4 class="sectiontitle">Query support in EJB 2.x</h4><p>EJB 2.x provides a query
	syntax called EJB Query Language (EJB QL) for finder and select methods of
	CMP entity beans. Finder methods obtain one or more entity bean instances
	from a database, and are defined in the home interface. </p>
	<p>The <samp class="codeph"><query></samp> element
	is used to define the query for the finder method in the deployment descriptor,
	for every finder method except findByPrimaryKey(key). Queries specified in
	the deployment descriptor are compiled into SQL during deployment. The syntax
	of the query is contained in the <samp class="codeph"><ejb-ql></samp> element of the <samp class="codeph"><query></samp> element:</p>
	<div class="p"> <pre><query>
	<query-method>
	<method-name></method-name>
	<method-params>
	<method-param></method-param>
	</method-params>
	</query-method>
	<result-type-mapping></result-type-mapping>
	<ejb-ql></ejb-ql>
	</query></pre>
	</div>
	<p>See <a href="../topics/teaddejbqlquery.html">Adding finder methods to EJB 2.x beans using EJB QL</a> for more help.</p>
	<p>For details on EJB QL, go to the <a href="http://www-306.ibm.com/software/webservers/appserv/infocenter.html" target="_blank">WebSphere Application Server Information Center</a> and
	search for the keywords "EJB QL".</p>
	</div>
	<div class="skipspace"><h4 class="sectiontitle">Finder support in EJB 1.x</h4><div class="important"><span class="importanttitle">Important: </span>Finder
	support for EJB 1.1 CMP entity beans is a WebSphere Application Server extension
	to the EJB specification. The Finders section on the Bean page of the EJB
	deployment descriptor editor only displays for EJB 1.1 beans in projects that
	are targeted to WebSphere Application Server.</div>
	<p>There are three types
	of EJB custom finders that are currently supported and that can be used in
	combination:</p>
	<ul><li>SELECT</li>
	<li>WHERE</li>
	<li>Method</li>
	<li>OOQL - this is the only finder type that you can migrate to EJBQL for
	EJB 2.x</li>
	</ul>
	<p>For each finder method that is defined in the EJB home interface (other
	than findByPrimaryKey and those finder methods generated to support associations),
	one of the following query strings or declarations must be defined in the
	finder helper interface (in file <i>beanClassName</i>FinderHelper.java):</p>
	<ul><li>An SQL query string (for SELECT and WHERE custom finders)</li>
	<li>A method declaration (for method custom finders)</li>
	</ul>
	<p>Note that the return type java.util.Enumeration or java.util.Collection
	on the finder in the home interface indicates that this finder may return
	more than one bean. Using the remote interface as the return type indicates
	that a single bean is returned. This is true for all of the supported types
	of custom finders. The code generated into the persister handles this distinction.</p>
	<p>It
	is important to note that if you are working with existing EJB 1.0 JAR files,
	you can continue to define SQL query strings or method declarations in the
	helper finder interface. (The SQL query string is actually a field on the
	interface describing the full SELECT statement or just the WHERE clause.)
	However, for any new development work that requires you to work with EJB JAR
	files at a level higher than 1.0, it is required that you use an extension
	document rather than the finder helper interface to define your queries and
	method declarations. This is discussed later in this topic.</p>
	<p><span class="uicontrol">Maintaining
	SQL compatibility across different databases</span></p>
	<p>For SELECT,
	WHERE, and method custom finders, there may be situations where the finder
	methods access different databases. In this case, it is necessary to ensure
	that SQL compatibility is maintained across the different databases. For instance,
	it is possible that the SQL syntax used by each database is different. In
	these situations, use the SQL extensions defined by JDBC to resolve the database
	differences.</p>
	<p>For example, assume that you are developing a CMP entity
	bean that requires a finder method that involves a timestamp/date field. Also
	assume that this bean will be deployed to DB2<sup>®</sup> and Oracle databases. The problem is that
	the format of the timestamp/date fields in DB2 and Oracle are different, which causes
	difficulties in defining one WHERE clause for use with both DB2 and Oracle.
	The solution to this particular problem is to use the SQL Escape sequence.</p>
	<p>Additional
	information about SELECT, WHERE, and method custom finders is found in the
	following sections.</p>
	<p><span class="uicontrol">Note:</span> when working with custom
	finders, do not pass NULL.</p>
	<dl><dt class="bold">SELECT custom finders</dt>
	<dd><p>A SELECT custom finder is used to enter an entire select statement
	in the finder helper interface to define the SQL query.</p>
	<p>The use of SELECT
	custom finders is supported for compatibility with earlier releases. Using
	SELECT custom finders is discouraged in this and future releases.</p>
	</dd>
	<dt class="bold">WHERE custom finders</dt>
	<dd><p>A custom finder in which you enter only the filtering WHERE clause
	into the finder helper interface is called a WHERE custom finder. For example,
	if you have a VAPGarage CMP entity bean that is mapped to a database table
	with a column named CAPACITY, the finder helper interface would look like
	this:</p>
	<div class="p"> <pre>public interface VapGarageBeanFinderHelper {
	public final static String
	findCapacityGreaterThanWhereClause =
	"T1.CAPACITY > ?";
	}</pre>
	</div>
	<p>Notice that any dependency on the shape of the results
	is removed from the string. Two dependencies still exist: </p>
	<ul><li>The name of the column (CAPACITY)</li>
	<li>The alias for the table (T1)</li>
	</ul>
	<p>The name of the column would change only if you took action to change
	it.</p>
	<p>The alias for the table will be the same from one generation to
	the next unless tables are added to or removed from the mapping. In single-table
	cases, this may not seem significant (the alias is always T1). When multiple
	tables are used, this is very important.</p>
	<p>Any table references in your
	handwritten SQL code must match the table aliases set up in the genericFindSqlString
	field. This is declared in the enterprise bean's generated persister.</p>
	<p>As
	in the SELECT custom form, the number of finder parameters must match the
	number of injection points (the <samp class="codeph">?</samp> characters) in the WHERE
	clause. Also, as in the SELECT form, the type of the parameter will be used
	to determine which java.sql.PreparedStatement set call will be used to inject
	each parameter. The parameter types must be compatible with the column types.
	If the type is an object type and the parameter is null, a setNull call will
	be used.</p>
	<p>For example, the home interface may contain the following method:</p>
	<div class="p"> <pre>public java.util.Enumeration findGreaterThan (int threshold) throws
	java.rmi.RemoteException, javax.ejb.FinderException;</pre>
	</div>
	<p>In
	this finder helper interface, the WHERE custom finder is one of the forms
	that you can provide. For example (line broken for publication):</p>
	<div class="p"> <pre>public static final String findGreaterThanWhereClause =
	"T1.VALUE > ?";</pre>
	</div>
	<p>Note, however, that if you have an SQL
	statement that contains no WHERE clause, such as <samp class="codeph">SELECT * FROM MYTABLE</samp>,
	you should use a query string that always evaluates to <i>true</i>. For example:</p>
	<div class="p"> <pre>public static final String findALLWhereClause = "1 = 1";</pre>
	</div>
	</dd>
	<dt class="bold">Method custom finders</dt>
	<dd><p>A custom finder in which you enter a method signature into the finder
	helper interface is called a method custom finder. It is the most flexible
	type of custom finder, but it requires more work on your part. Using the same
	garage example as before, the finder helper interface would look like this:</p>
	<div class="p"> <pre>public interface VapGarageBeanFinderHelper {
	public java.sql.PreparedStatement findCapacityGreaterThan(int threshold)
	throws Exception;
	}</pre>
	</div>
	<p>Unlike the SELECT and WHERE forms, however, this is not
	enough for method custom finders. An implementation of this method is needed.
	You provide your implementation of the method in a class that follows these
	rules:</p>
	<ul><li>The name of the class is <i>beanClassName</i>FinderObject (VapGarageBeanFinderObject,
	in this example) and is in the same package as the bean class.</li>
	<li>The class must extend com.ibm.vap.finders.VapEJSJDBCFinderObject and must
	implement the bean's finder helper interface.</li>
	<li>Any table references in your handwritten SQL code must match the table
	aliases set up in the genericFindSqlString field. This is declared in the
	enterprise bean's generated persister.</li>
	</ul>
	<p>To finish off our example, the finder object would look like this.</p>
	<div class="p"> <pre>/**
	* Implementation class for methods in
	* VapGarageBeanFinderHelper.
	*/
	public class VapGarageBeanFinderObject extends
	com.ibm.vap.finders.VapEJSJDBCFinderObject implements
	VapGarageBeanFinderHelper {

	public java.sql.PreparedStatement
	findCapacityGreaterThan(int threshold)
	throws Exception {

	PreparedStatement ps = null;
	int mergeCount = getMergedWhereCount();
	int columnCount = 1;
	ps = getMergedPreparedStatement("T1.CAPACITY > ?");
	for (int i=0; i<(columnCount*mergeCount); i=i+columnCount) {
	ps.setInt(i+1, threshold);
	}
	return ps;
	}
	}</pre>
	</div>
	<p>In the case of any method custom finder, the generated
	persister uses your implementation to create the PreparedStatement to be executed.
	The persister will execute the PreparedStatement and handle the results. The
	implementation needs help from the persister to make sure the result set for
	the query has the correct shape. The com.ibm.vap.finders.VapEJSJDBCFinderObject
	base class provides several important helper methods, some of which are shown
	in the above example. In the following table, the complete set of helper methods
	are listed and described:</p>

	<div class="skipspace"><table cellpadding="4" cellspacing="0" summary="" width="90%" frame="border" border="1" rules="all"><thead align="left"><tr><th valign="top" id="d0e191">Method</th>
	<th valign="top" id="d0e193">Description</th>
	</tr>
	</thead>
	<tbody><tr><td valign="top" headers="d0e191 ">getMergedPreparedStatement</td>
	<td valign="top" headers="d0e193 ">Takes a WHERE clause and returns a PreparedStatement with the WHERE
	clause merged into the appropriate places. The PreparedStatement will have
	the correct result set shape.</td>
	</tr>
	<tr><td valign="top" headers="d0e191 ">getMergedWhereCount</td>
	<td valign="top" headers="d0e193 ">Returns the number of times the WHERE clause is merged into the PreparedStatement.
	This is needed to know how many times to inject your query parameters into
	the PreparedStatement.</td>
	</tr>
	<tr><td valign="top" headers="d0e191 ">getPreparedStatement</td>
	<td valign="top" headers="d0e193 ">Takes a complete query string and returns a PreparedStatement. This
	can be used if for some reason you need to do your own WHERE clause merging.
	This should be a very rare case. The next two functions are provided to help
	in these extreme cases.</td>
	</tr>
	<tr><td valign="top" headers="d0e191 ">getGenericFindSqlString</td>
	<td valign="top" headers="d0e193 ">Returns the query string into which WHERE clauses are merged.</td>
	</tr>
	<tr><td valign="top" headers="d0e191 ">getGenericFindInsertPoints</td>
	<td valign="top" headers="d0e193 ">Returns an array of integers that defines the point or points in the
	getGenericFindSqlString returned query string at which the WHERE clause is
	merged. The first point in the array is the last point in the string. It is
	usually best to merge from the end of the query string since a merge at the
	end will not change the location of merges earlier in the string. The size
	of the array is the same as the value returned from getMergedWhereCount.</td>
	</tr>
	</tbody>
	</table>
	</div>
	<p>This is a rather simple case that can better be handled by a WHERE
	custom finder. More complex examples are possible that a WHERE custom finder
	simply cannot handle. For example, suppose you want a finder that takes a
	more complex object and injects it into multiple columns in a WHERE clause.
	You would end up with a finder method that looks like this:</p>
	<div class="p"> <pre>public java.sql.PreparedStatement
	findWithComplexObject(BigObject big) throws Exception {

	PreparedStatement ps = null;
	int mergeCount = getMergedWhereCount();
	int columnCount = 3;
	int anInt = big.getAnInt();
	String aString = big.getAString();
	String aLongAsString =
	com.ibm.vap.converters.VapStringToLongConverter.
	singleton().dataFrom(big.getLongObject());

	ps = getMergedPreparedStatement("(T1.ANINT > ?) AND
	(T1.ASTRING = ?) AND (T2.ALONGSTR < ?)");
	for (int i=0; i<(columnCount*mergeCount); i=i+columnCount) {
	ps.setInt(i+1, anInt);
	if (aString == null)
	ps.setNull(1, java.sql.Types.VARCHAR);
	else
	ps.setString(i+2, aString);
	if (aLongAsString == null)
	ps.setNull(1, java.sql.Types.VARCHAR);
	else
	ps.setString(i+3, aLongAsString);
	}
	return ps;
	}</pre>
	</div>
	<p>Even more complex examples are possible. For example,
	an object could be passed that contains the WHERE clause (or instructions
	on how to create it) in addition to the data. Or, there could be multiple
	parameters, each representing different conditions in the WHERE clause.</p>
	<p><b>Example:
	Complex method custom finder</b></p>
	<p>Although associations are not created
	with the EJB deployment tool, the following example is a logical representation
	of how a many-to-many association could be accomplished using a complex method
	custom finder. The example involves a many-to-many association between Product
	and Customer beans, using an intermediary bean (ProdCustLink) and two 1:m
	associations:</p>
	<p><br /><img src="../images/n5rpdcst.gif" alt="Graphical representation of a complex method custom finder and a many-to-many association between two beans." /><br /></p>
	<p>You can write method custom finders to span the
	relationship in either direction with just one method call. For this example,
	consider one direction only: a finder in Customer that retrieves all Customer
	instances that are associated with a given product key.</p>
	<p>Customer's home
	interface contains the appropriate method signature, as follows:</p>
	<div class="p"> <pre>java.util.Enumeration
	findCustomersByProduct(prod.cust.code.ProductKey inKey)
	throws java.rmi.RemoteException, javax.ejb.FinderException;</pre>
	</div>
	<p>Customer's
	finder helper interface contains the signature for the corresponding finder
	method:</p>
	<div class="p"> <pre>public java.sql.PreparedStatement
	findCustomersByProduct(prod.cust.code.ProductKey inKey)
	throws Exception;</pre>
	</div>
	<p>The finder object (CustomerBeanFinderObject)
	builds and caches the query string for the finder as well as implements the
	finder method.</p>
	<div class="p"> <pre>public class CustomerBeanFinderObject
	extends com.ibm.vap.finders.VapEJSJDBCFinderObject
	implements CustomerBeanFinderHelper {

	private String cachedFindCustomersByProductQueryString = null;
	.
	.
	.
	}</pre>
	</div>
	<p>Through lazy initialization in the finder object, the
	accessor method for the query-string field builds up the query string by first
	merging the WHERE condition into the query template and then adding a reference
	to the intermediate table into the FROM clause.</p>
	<p>The first half of the
	accessor method uses a genericFindInsertPoints array to locate and update
	each WHERE clause. Then, the second half of the method counts forward from
	the beginning of each FROM clause, inserts the reference to the intermediate
	table into the query string as needed, and updates the query-string field.</p>
	<div class="p"> <pre>protected String getFindCustomersByProductQueryString() {
	if (cachedFindCustomersByProductQueryString == null) {

	// Do the WHERE first
	// so that the genericFindInsertPoints are correct.
	int i;
	int[] genericFindInsertPoints = getGenericFindInsertPoints();
	StringBuffer sb = new StringBuffer(getGenericFindSqlString());
	for (i = 0; i < genericFindInsertPoints.length; i++) {
	sb.insert(genericFindInsertPoints[i],
	"(T1.id = T2.Customer_id) AND (T2.Product_id = ?)");
	}

	// Make sure to update every FROM clause.
	String soFar = sb.toString();
	int fromOffset = soFar.indexOf(" FROM ");
	while (fromOffset != -1) {
	sb.insert((fromOffset+5)," ProdCustLink T2, ");
	soFar = sb.toString();
	fromOffset = soFar.indexOf(" FROM ", (fromOffset+5));
	}
	cachedFindCustomersByProductQueryString = sb.toString();
	}
	return cachedFindCustomersByProductQueryString;
	}</pre>
	</div>
	<p>After this method call, the query string looks something
	like the following:</p>
	<div class="p"> <pre>SELECT <i><columns></i> FROM ProdCustLink T2, CUSTOMER T1
	WHERE((T1.id = T2.Customer_id) AND (T2.Product_id = ?))</pre>
	</div>
	<p>Also
	in the finder object, the implemented finder uses the query string to create
	a PreparedStatement. Last but not least, the product ID value is added into
	each WHERE clause by using the superclass method getMergedWhereCount() in
	the iteration loop.</p>
	<div class="p"> <pre>public java.sql.PreparedStatement
	findCustomersByProduct(ProductKey inKey)
	throws java.lang.Exception {

	// Get the full query string and make a PreparedStatement.
	java.sql.PreparedStatement ps =
	getPreparedStatement(getFindCustomersByProductQueryString());

	// Inject the product id parameter into each merged WHERE clause.
	for (int i = 0; i > getMergedWhereCount(); i++) {
	if (inKey != null)
	ps.setInt(i+1, inKey.id);
	else
	ps.setNull(i+1, 4);
	}

	return ps;
	}</pre>
	</div>
	</dd>
	</dl>
	</div>
	</div>

	<div><p><b class="reltaskshd">Related tasks</b><br />
	<a href="../topics/teaddejbqlquery.html" title="You can add "find" or "ejbSelect" finder methods to the home interfaces of EJB 2.x CMP beans. The finder methods use the EJB query language. A wizard guides you through the steps for adding new or existing finders to your bean.">Adding finder methods to EJB 2.x beans using EJB QL</a><br />
	</p>
	</div> <br />
	(C) Copyright IBM Corporation 2000, 2005. All Rights Reserved.
	</body>
	</html>