Previously, I outlined a way in which you can pretty easily extend ColdBox’s excellent CriteriaBuilder to support Hibernate Criteria SQL projections. While this certainly worked, you may remember I complained–whined, really–about how hard-codey the solution was. That is, in order to really use the SQL projections, you more or less have to build out SQL strings manually each and every time you use it. String concatenation, ugh!

Not content to leave it alone, I continued to explore ways to make this a bit better; I think I’ve struck on a significantly better way.

On the Road to a Solution

What I’ve created is what I call a “Detached SQL Projection”. In essence, it leverages the DetachedCriteria which I wrote about previously. Once the DetachedCriteria is sufficiently built up, the “projection” more or less grabs the SQL that *would be* generated for the DetachedCriteria. However, instead of adding it as a subquery to the main criteria object, this faux-projection uses the pre-generated SQL and creates a real SQL projection.

Here’s some code (from my extension of the withProjections() method):

// detachedSQLProjection
if( structKeyExists( arguments, "detachedSQLProjection" ) ) {
   var propertyTypes = [];
   var sqlargs = arguments.detachedSQLProjection;
   var metaData = orm.getSessionFactory(orm.getEntityDatasource(this.getentityName())).getClassMetaData(this.getentityName());
   // get session
   var session = orm.getSession().getActualSession();
   // get session factory
   var factory = session.getSessionFactory();
   // get executable criteriaImplementation for detached criteria object
   var criteriaImpl = sqlargs.sql.getNativeCriteria().getExecutableCriteria( session );
   // get implementors for the criteria implementation
   var implementors = factory.getImplementors( criteriaImpl.getEntityOrClassName() );
   // create new criteria query translator; we'll use this to build up the query string
   var translator = createObject( "java", "org.hibernate.loader.criteria.CriteriaQueryTranslator" ).init(
                factory,
                criteriaImpl,
                implementors[ 1 ],
                criteriaImpl.getAlias()
            );
   var sqlstring = "(";
   // get select string
   sqlstring = sqlstring & "select #translator.getSelect()# ";     
   // build from string...use root alias of translator
   sqlstring = sqlstring & "from #implementors[1]# #translator.getRootSQLAlias()# ";
   // if there's a where condition...
   if( translator.getWhereCondition() != "" ) {
      // get parameter values and types
      var parameterValues = translator.getQueryParameters().getPositionalParameterValues();
      var parameterTypes = translator.getQueryParameters().getPositionalParameterTypes();
      // append straight where condition string
      sqlstring = sqlstring & "where #translator.getWhereCondition()#";
      // loop over parameters; need to replace those pesky "?" with the real values
      for( var pv=1; pv<=arrayLen( parameterValues ); pv++ ) {
          // check the type of the parameter
          var pvTyped = parameterTypes[ pv ].getName()=="string" ? "'#parameterValues[ pv ]#'" : parameterValues[ pv ];
          // get position of parameter placeholder
          paramPos = findNoCase( "?", sqlstring );
          // if parameter is a string type and is preceeded by "like", need to add like evaluators
          if( parameterTypes[ pv ].getName()=="string" && mid( sqlstring, paramPos-5, 4 )=="like" ) {
             pvTyped = reReplaceNoCase( pvTyped, "^'", "'%", "one" );
             pvTyped = reReplaceNoCase( pvTyped, "'$", "%'", "one" );
          }
          // if comparing to root alias, remove quotations
          if( pvTyped contains "{alias}" ) {
             pvTyped = replaceNoCase( pvTyped, "'", "", "all" );
          }
          sqlstring = reReplaceNoCase( sqlstring, "\?", pvTyped, "one" );
      }
   }
   // group by string
   if( translator.getGroupBy() != "" ) {
      sqlstring = sqlstring & " group by #translator.getGroupBy()# ";
   }
   // order by string
   if( translator.getOrderBy() != "" ) {
      sqlstring = sqlstring & " order by #translator.getOrderBy()# ";
   }
   // add user-defined alias to end of the entire sql statement
   sqlstring = sqlstring & ") as #sqlargs.alias#";
   // retrieve correct type for each specified property so list() doesn't bork
   for( var prop in listToArray( sqlargs.property ) ) {
      arrayAppend( propertyTypes, metaData.getPropertyType( prop ) );
   }
   // add to projection list
   projectionList.add( this.PROJECTIONS.sqlProjection( sqlstring, listToArray( sqlargs.alias ), propertyTypes ), sqlargs.alias );
}

And here’s how I use it:

function BetterSQLProjections() {
   var c  = newCriteria();
   var sc = newSubCriteria( "Car", "Car2" ); // detachedCriteria
   sc.withProjections( count="CarID")
      .add( c.restrictions.isLT( "Year", javaCast( "int", 2012 ) ) )
      .add( c.restrictions.eq( "Make", "{alias}.Make" ) );
   var sql = {
      sql = sc, // detachedCriteria object
      alias = "TotalPre2012",
      property="Year"
   };
   c.withProjections( groupProperty="Make", detachedSQLProjection=sql );
   var results = {
      data = c.list()
   };
   results.count = arrayLen( results.data );
}

As you can see, I’ve thrown together a pretty rough and unfinished version (for example, no support yet for joins…) of a SQL string builder. So I’m still building strings 🙁 However, the huge benefit here is that the string building is now abstracted as a projection. Therefore, instead of having to build strings each and every time I want (or need) to use a SQL projection, I can simply build out a DetachedCriteria using the API (hooray), and simply pass the full DetachedCriteria object to this new projection. It will then do the dirty work of SQL-building and other nonsense, relieving my day-to-day code from being littered with nasty string concatenations.

Conclusion

It’s still not perfect, but I’m quite a bit happier with this approach. It leverages the API more effectively, IMO, and moves away from having to manually build SQL strings, which is awesome. Thoughts?