Hibernate Criteria Queries are your friend

I was working on a project where I needed to do a search feature, I then remembered seeing this in the ColdBox docs… Wasn’t sure exactly what it was, but it looked cool, so I gave it a try, and wow is it powerful.

Normally, I would write out some long SQL/HQL with joins, sub selects, counts anything to further expand a search and at the same time keeping performance high while interweaving if statements inside of the query itself.  But with a Hibernate Criteria Query it was a breeze, and the end result looks clean and it’s easy to read (Aside from having to sometimes JavaCast some variables).

If your using ColdBox, Luis has made it very easy to use this by proxying out the native classes while providing the projections, paging, sorting & all that good stuff.  You should really give these a try, but please check out the latest dev version of the BaseORMService thats updated to support criteria of associated objects.

Otherwise, you can still do this in CF just by starting on these two.

Restrictions = CreateObject(“java”,”org.hibernate.criterion.Restrictions”);
Criteria = ORMGetSession().createCriteria( ‘some_entity_name’ );

Luis was kind enough to provide some example code, shown below.

/**
* entry search returns struct with keys [entries,count]
*/
struct function search(search="",isPublished,category,author,max=0,offset=0){
    var results = {};
    // get Hibernate Restrictions class
    var restrictions = getRestrictions();
    // criteria queries
    var criteria = [];
    // isPublished filter
    if( structKeyExists(arguments,"isPublished") AND arguments.isPublished NEQ "any"){
        arrayAppend(criteria, restrictions.eq("isPublished",javaCast("boolean",arguments.isPublished)) );
    }

    // Author Filter
    if( structKeyExists(arguments,"author") AND arguments.author NEQ "all"){
        arrayAppend(criteria, restrictions.eq("author.authorID",javaCast("int",arguments.author)) );
    }

    // Search Criteria
    if( len(arguments.search) ){
        // like disjunctions
        var orCriteria = [];
        arrayAppend(orCriteria,restrictions.like("title","%#arguments.search#%"));
        arrayAppend(orCriteria,restrictions.like("content","%#arguments.search#%"));
        // append disjunction to main criteria
        arrayAppend( criteria, restrictions.disjunction( orCriteria ) );
    }

    // Category Filter
    if( structKeyExists(arguments,"category") AND arguments.category NEQ "all"){
        // Uncategorized?
        if( arguments.category eq "none" ){
            arrayAppend(criteria, restrictions.isEmpty("categories") );
        }
        // With categories
        else {
            // create association criteria, by passing a simple value the method will inflate.
            arrayAppend(criteria, "categories");
            // add the association criteria to the main search
            arrayAppend(criteria, restrictions.in("categories.categoryID",JavaCast("java.lang.Integer[]",[arguments.category])));
        }
    }

    // run criteria query and projections count
    results.entries = criteriaQuery(criteria=criteria,offset=arguments.offset,max=arguments.max,sortOrder="publishedDate DESC",asQuery=false);
    results.count = criteriaCount(criteria=criteria);
    return results;
}

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

WordPress Appliance - Powered by TurnKey Linux