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)
Created May 4, 2012
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).
and
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.
Adding a Finder Object for Custom SQL Access (Extra Crispy)
This is an abbreviated method of adding a custom finder object that evolved from using the Original Recipe. It streamlines the creation of a
custom finder by abtracting certain repeated operations into a newly created subclass of the VapEJSJDBCFinderObject (which is a
superclass of all custom finders created for this project). The operations that are abstracted are getGenericDistinctQueryString,
getGenericQueryStringNoWhere and buildPreparedStatement (which basically tacks the query string onto the end of what is returned
by getGenericDistinctQueryString and then does a getPreparedStatement)
public String getGenericQueryStringNoWhere() {
String gqs = getGenericFindSqlString();
return gqs.substring(0,gqs.lastIndexOf("WHERE"));
}
public String getGenericDistinctQueryString() {
String q = getGenericQueryStringNoWhere();
return "SELECT DISTINCT" + q.substring(6);
}
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;
String findByMagicQS = ", 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 = buildPreparedStatement (findByMagicQS);
return ps;