How do I create a Custom Finder Object that selects a group of Entity Beans based on criteria that are in other (joined) tables? (i.e. Show me all Customers that have ordered aluminum widgets)

The problem - you want to find all objects that fulfill some criteria, but that criteria isn't all in one table. I.e., find all customers that have ordered aluminum widgets. This requires a join of (in most cases) four tables Customer -> Order -> OrderLine -> Product. Note: this only works when all of the attributes being selected come from the same base table. If they come from more than one base table, you need the other approach. (An alternate approach would be to instantiate all aluminum widgets and then follow associations, but this is expensive)

Adding a Finder Object for Custom SQL Access (Original Recipe)

This is used to accomplish more sophisticated queries than what the standard FinderHelper object will allow. Specifically, it is used to do complex joins across tables and select the objects that would result. The only (known) limitation is that all of the desired attributes to be returned from the select statement must be in the same table. The only attributes that can be in a different table are the ones being used for the restrict operation. (Note: if attributes from multiple tables are to be returned and used to populate the object, a new object must be constructed which is based on a view, and all of the methods of the view object must be marked as “read-only”).

1. In the NameHome, add the appropriate methods – i.e., findNameByMagic(args). This method will usually return a java.util.Enumeration. This method should declare that it throws RemoteException and FinderException.
    
2. In the NameBeanFinderHelper class, add a method of the same name that takes the same argument(s). Note: this method should return java.sql.PreparedStatement. This method should declare that it throws Exception (or something lower in the Exception hierarchy).
    
3. Generate the (deployed) code for the Name EJB Group.
    
4. In the EJB tab of the Workspace, create the NameBeanFinderObject class. This class must extend com.ibm.vap.finders.VapEJSJDBCFinderObject and implement NameBeanFinderHelper. Note: the FinderObject will not show up in the list of classes (even if you expand to the “long” list), so to see it, you will need to go to the Projects or Packages page.
    
5. To the FinderObject class, add two (almost certainly needed) methods –

   public String getGenericQueryStringNoWhere() { 
    String gqs = getGenericFindSqlString(); 
    return gqs.substring(0,gqs.lastIndexOf("WHERE")); 
   } 
   

   and

   public String getGenericDistinctQueryString() { 
    String q = getGenericQueryStringNoWhere(); 
    return "SELECT DISTINCT" + q.substring(6); 
   } 
   

   The first method fetches the generic query string from the Persister object and strips off the “where” clause. The second one adds the word “distinct” to the query string (in the appropriate place, of course) so that duplicate rows/objects are not returned.
    

6. Implement the method findNameByMagic(args)

   String qs = getGenericDistinctQueryString(); 
   String findByMagicQS = qs + ", SCHEMA.TABLE2 T2, SCHEMA.TABLE3 T3, SCHEMA.TABLE4 T4 WHERE (T4.COL1 = '" + 
   arg + "') AND (T4.JOINCOL = T3. JOINCOL1) AND (T3. JOINCOL2 = T2. JOINCOL2) AND (T2. JOINCOL3 = T1.
   JOINCOL3)"; 
   java.sql.PreparedStatement ps = getPreparedStatement (findByMagicQS); 
   return ps; 
   

7. Generate Name again. When generation is complete, the FinderObject should show up in the middle pane of the EJB tab (if the “long” view is shown).