Minor Description
In the code below - the regular criteria works fine for the plain select, but when I use it in the getCount method, it fails at runtime with a malformed SQL error. When I look at the SQL sent to MySQL, it shows the format of a prepared statement with and no replacements.
/**
- Get a List of the dogs that earned all of a list of titles in a given year
- @param titles array of titles to be searched.
- @param year four digit year to be searched
- @return a List of dogs that earned the titles
- @throws TorqueException if something goes wrong
*/
public static List<Dog> doFindDogsWithAllTitles( String[] titles, String year ) throws TorqueException { Criteria crit = getCriteriaDogsWithAllTitles( titles, year ); crit.addAscendingOrderByColumn( REG_NAME ); return doSelect( crit ); }
/**
- Get a count of the dogs that earned all of a list of titles in a given year
- @param titles array of titles to be searched.
- @param year four digit year to be searched
- @return number of dogs that earned the titles
- @throws TorqueException if something goes wrong
*/
public static int doFindCountWithAllTitles( String[] titles, String year ) throws TorqueException { //TODO: Remove Hack once issue fixed Criteria crit = getCriteriaDogsWithAllTitlesHack( titles, year ); SummaryHelper summary = new SummaryHelper(); summary.addAggregate( "count", new Count( DogPeer.DOG_ID ) ); List<ListOrderedMapCI> results = summary.summarize( crit ); return Integer.parseInt( results.get( 0 ).get( "count" ).toString() ); }
/**
- Create a criteria to search for the dogs that earned all of an array of titles in a given year
- @param titles array of titles to be searched.
- @param year four digit year to be searched
- @return the Criteria to be used for the request
*/
private static Criteria getCriteriaDogsWithAllTitles( String[] titles, String year ) {
String startDate = null, endDate = null;
try { int yr = Integer.parseInt( year )+1; //They year is valid if we get here. startDate = year+"-01-01"; endDate = Integer.toString( yr ) + "-01-01"; } catch( NumberFormatException nfe ) { //ignore }
Criteria crit = new Criteria();
int idx = 1;
for( String str : titles ) {
String alias = "t"+Integer.toString( idx );
crit.addAlias( alias, "title" );
crit.addJoin( DogPeer.DOG_ID, new ColumnImpl( alias + "." + TitlePeer.DOG_ID.getColumnName() ) );
crit.where( new ColumnImpl( alias + "." + TitlePeer.TITLE.getColumnName() ), str );
if( startDate != null ) { Column aliasDate = new ColumnImpl( alias + "." + TitlePeer.TITLE_DATE.getColumnName() ); crit.where( aliasDate, startDate, Criteria.GREATER_EQUAL ); crit.where( aliasDate, endDate, Criteria.LESS_THAN ); }
++idx;
}
return crit;
}
/**
* forces a non-prepared statement to get around SummaryHelper bug
* @param titles array of titles that the dog must have
* @param year year to be searched
* @return a Criteria object with the appropriate query
*/
private static Criteria getCriteriaDogsWithAllTitlesHack( String[] titles, String year ) {
String startDate = null, endDate = null;
try{ int yr = Integer.parseInt( year )+1; //They year is valid if we get here. startDate = year+"-01-01"; endDate = Integer.toString( yr ) + "-01-01"; }catch( NumberFormatException nfe )
{ //ignore }
Criteria crit = new Criteria();
int idx = 1;
for( String str : titles ) {
String alias = "t"+Integer.toString( idx );
crit.addAlias( alias, "title" );
crit.addJoin( DogPeer.DOG_ID, new ColumnImpl( alias + "." + TitlePeer.DOG_ID.getColumnName() ) );
crit.whereVerbatimSql( alias + "." + TitlePeer.TITLE.getColumnName() + "='" + str + "'", null );
if( startDate != null )
++idx;
}
return crit;
}