last edited bylmajano on 14-Apr-2013

History 

<< Back to Dashboard | << Extras Viewer

ColdBox Criteria Builder

( Virtual Entity Services | ORM Event Handling | Base ORM Service )

Overview

The ColdBox Hibernate Criteria Builder is a powerful object that will help you build and execute hibernate criteria queries. HQL is extremely powerful, but some developers prefer to build queries dynamically using an object-oriented API, rather than building query strings and concatenating them in strings or buffers. The ColdBox Criteria builder offers a powerful programmatic DSL builder for Hibernate Criteria queries. You can see below some of the Hibernate documentation on criteria queries.

1. Hibernate Criteria Queries: http://docs.jboss.org/hibernate/core/3.5/reference/en-US/html/querycriteria.html
2. Hibernate Criteria Interface: http://docs.jboss.org/hibernate/core/3.5/javadoc/org/hibernate/Criteria.html
3. Hibernate Restrictions: http://docs.jboss.org/hibernate/core/3.5/javadoc/org/hibernate/criterion/Restrictions.html

As you will soon discover, they are fantastic but doing it the java way is not that fun, so we took our lovely ColdFusion dynamic language funkyness and added some ColdBox magic to it.

Getting Started

A criteria builder can be requested from our Base ORM services or a virtual service, which will bind itself automatically to the binded entity, by calling on their newCriteria() method. The corresponding class is: coldbox.system.orm.hibernate.CriteriaBuilder

The arguments for the newCriteria() method are:

If you call newCriteria() from a virtual service layer, then you don't pass the entityName argument as it roots itself automatically.

Examples

// Base ORM Service
c = newCriteria( 'entityName' );
// Virtual
c = newCriteria();

// Examples
var results = c.like("firstName","Lui%")
     .maxResults( 50 )
     .order("balance","desc")
     .and( 
          c.restrictions.between( "balance", 200, 300),
          c.restrictions.eq("department", "development")
     )
     .list();

// with pagination
var results = c.like("firstName","Lui%")
     .order("balance","desc")
     .and( 
          c.restrictions.between( "balance", 200, 300),
          c.restrictions.eq("department", "development")
     )
     .list(max=50,offset=20);

// more complex
var results = c.in("name","luis,fred,joe")
     .OR( c.restrictions.isNull("age"), c.restrictions.eq("age",20) )
     .list();

Once you have an instance of the Criteria Builder class you can start adding restrictions, projections and configuration data for your query. All by concatenating methods in a nice programmatic DSL. Once all the restrictions, projections and/or configuration data are in place, you will execute the query/projections using our result methods. Please note that you can request as many new criteria builders as you like and each of them will execute different queries. So let's start with the restrictions.

Restrictions

The ColdBox restrictions class allows you to create criterions upon certain properties, associations and even SQL for your ORM entities. This is the meat and potatoes of criteria queries, where you build a set of criterion to match against. The ColdBox criteria class offers almost all of the criterion methods found in the native hibernate Restrictions class (http://docs.jboss.org/hibernate/core/3.5/javadoc/org/hibernate/criterion/Restrictions.html) but if you need to add very explicit criterion directly you have access to the ColdBox Restrictions class which proxies calls to the native Hibernate class. You do this by either retrieving it from the Base/Virtual ORM services (getRestrictions()), create it manually, or the Criteria object itself has a public property called restrictions which you can use rather easily. We prefer the latter approach. Now, plese understand that the ColdBox Criteria Builder masks all this complexity and in very rare cases will you be going to our restrictions class directly. Most of the time you will just be happily concatenating methods on the Criteria Builder.

// From base ORM service
var restrictions = getRestrictions()

// Manually Created
var restrictions = new coldbox.system.orm.hibernate.criterion.Restrictions();

// From Criteria Builder
newCriteria().restrictions

Ok, now that the formalities have been explained let's build some criterias.

Criterias

To build our criteria queries we will mostly use the methods in the criteria object or go directly to the restrictions object for very explicit criterions as explained above. We will also go to the restrictions object when we do conjunctions and disjunctions, which are fancy words for AND's, OR's and NOT's. So to build criterias we will be calling these criterion methods and concatenate them in order to form a nice DSL language that describes what we will retrieve. Once we have added all the criteria then we can use several other concatenated methods to set executions options and then finally retrieve our results or do projections on our results.

So let's start with all the different supported criterion methods in the Criteria object, which are the most commonly used. If you need to use methods that are not in the Criteria object you will request them via the Restrictions object, which can proxy calls to the underlying Hibernate native Restrictions class (http://docs.jboss.org/hibernate/core/3.5/javadoc/org/hibernate/criterion/Restrictions.html).

c.between("age",10,30);

c.conjunction( [ 
     c.restrictions.between("balance",100,200), 
     c.restrictions.lt("salary",20000) ] );

c.disjunction( [ 
     c.restrictions.between("balance",100,200), 
     c.restrictions.lt("salary",20000) ] );

c.eqProperty("createDate","modifyDate");

c.eq("age",30);

c.gt("publishedDate", now() );

c.gtProperty("balance","overdraft");

c.ge("age","18");

c.geProperty("balance","overdraft");

c.idEq( 4 );

c.ilike("lastName", "maj%");

c.isIn( "id", [1,2,3,4] );

c.isEmpty("childPages");

c.isNotEmpty("childPages");

c.isFalse("isPublished");

c.isNull("passwordProtection");

c.isNotNull("publishedDate");

c.isLT("age", 40 );

c.ltProperty("sum", "balance");

c.isLE("age", 30);

c.LeProperty("balance","balance2");

c.like("content", "%search%");

c.ne("isPublished", true);

c.neProperty("password","passwordHash");

c.sizeEq("comments",30);

c.sizeGT("children",5);

c.sizeGE("children", 10);

c.sizeLT("childPages", 25 );

c.sizeLE("childPages", 25 );;

c.sizeNE("childPages",0);

c.sqlRestriction("char_length( lastName ) = 10");

c.and( c.restrictions.eq("name","luis"), c.restrictions.gt("age",30) );

c.or( c.restrictions.eq("name","luis"), c.restrictions.eq("name", "joe") );

c.isNot( c.restrictions.eg("age", 30) );

c.isTrue("isPublished");

c.isEq("userID", JavaCast( "int", 3 ));

You can also use the add() method to add a manual restriction or array of restrictions to the criteria you are building.

c.add( c.restrictions.eq("name","luis") )

But as you can see from the code, the facade methods are much nicer.

Converting Values to Java Types

By default you will need to do some javaCasting() on the values in order for the criteria builder to work correctly on some values. Remember that ColdFusion is a typeless language and Java is not. However, we have added to convenience methods for you so you can just pass in values without caring about casting:

• convertIDValueToJavaType(id)
• convertValueToJavaType(propertyName, value)

You can also find these methods in the Base ORM services and Virtual Entity Services.

c.convertIDValueToJavaType( id = 123 );

c.convertIDValueToJavaType( id = ["1","2","3"] );

c.convertValueToJavaType(propertyName="id", value=arguments.testUserID)

Results

Once you have concatenated criterias together, you can execute the query via the execution methods. Please remember that these methods return the results, so they must be executed last.

list(), list(max=50,offest=51,timeout=3000,ignoreCase=true)

get()

count()

// Get
var results = c.idEq( 4 ).get();

// Listing
var results = c.like("name", "lui%")
     .list();

// Count via projections of all users that start with the letter 'L'
var count = c.ilike("name","L%").count();

Configuration Modifiers

The following methods alters the behavior of the executed query:

timeout( 5000 )

readOnly(true)

firstResult(11)

maxResults(25)

fetchSize(50)

cache(true), cache(true,'my.cache')

cacheRegion('my.cool.cache')

order('lastName','asc',false)

c.timeout( 5000 )
c.readOnly(true)
c.firstResult(20).maxResults(50).fetchSize(10).cacheRegsion('my.awesome.region')
c.cache(true,'my.region')
c.order('lastName','desc',true);   

Associations

You can also navigate associations by nesting the criterias using the createCriteria("association_name") method and then concatenating the properties of the association to query upon. You will basically be switching the pivot point of the query.

var c = newCriteria("User");
var users = c.like('name","lui%")
     .createCriteria("admins")
          .like("name","fra%")
     .list();

We also have an alternative dynamic approach which uses a method signature of with{AssociationName} which looks nicer and reads better.

var c = newCriteria("User");
var users = c.like('name","lui%")
     .withAdmins().like("name","fra%")
     .list();

You can also use a hibernate property approach which aliases the association much how HQL approaches it by using the createAlias("associationName","alias") method:

var c = newCriteria("User");
var users = c.like('name","lui%")
     .createAlias("admins","a")
     .eq("a.name","Vero")
     .list();

Let's see the method signatures for these guys:

createCriteria(required string associationName,numeric joinType)
createAlias(required string associationName, required string alias, numeric joinType)
with{AssociationName}( joinType )

Specifying Join Types

Criteria associations also allow you to chose the join type, which defaults to an inner join. The available join types which can be sent to both the createCriteria(), createAlias(), and with{AssociationName}() methods are:

var c = newCriteria("User");
var users = c.like('name","lui%")
     .createAlias("admins","a", criteria.LEFT_JOIN )
     .eq("a.name","Vero")
     .list();
var c = newCriteria("User");
var users = c.like("name","lui%")
     .withAdmins( criteria.LEFT_JOIN ).like("name","fra%")
     .list();

Result Transformers

Hibernate offers the ability to apply transformations to the results via its org.hibernate.transform.ResultTransform interface (http://docs.jboss.org/hibernate/core/3.5/javadoc/org/hibernate/transform/class-use/ResultTransformer.html). There are several included with Hibernate or you can build your own in java. Below are the transformers included with Hibernate that you can use by calling our resultTransformer() class and passing a reference to them, which we have nicely scoped for you in the Criteria Builder as public properties (Nice huh?)

These transformer classes are already referenced for you as public properties of the Criteria Builder class: criteria.{ResultTransformerName}

var c = newCriteria();
var results = c.like("firstName","Lui%")
     .and( 
          c.restrictions.between( "balance", 200, 300),
          c.restrictions.eq("department", "development")
     )
     .resultTransformer( c.DISTINCT_ROOT_ENTITY )
     .list();

Projections

Our criteria builder also supports the notion of projections (http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycriteria.html#querycriteria-projection). A projection is used to change the nature of the results, much how a result transformer does. However, there are several projection types you can use which are great for doing counts, distinct counts, max values, sums, averages and much more. This is great when you do paging as obviously you do not want to execute two queries: one for the pagination and another for the total reuslts count. Below are the available projections you can use:

withProjections(avg="salary")

withProjections(count="comments")

withProjections(countDistinct="email")

withProjections(distinct="email")

withProjections(groupproperty="lastName")

withProjections(max="lastLogin")

withProjections(min="cid")

withProjections(property="firstname")

withProjections(sum="balance")

withProjections(rowcount=1)

withProjections(id=1)

withProjections(sqlProjection={sql="SELECT count( * ) from blog where Year < 2006 and Author={alias}.Author",
alias="BlogPosts", 
property="Author" })

withProjections(sqlGroupProjection={sql="SELECT count( * ) from blog where Year < 2006 and Author={alias}.Author", 
alias="BlogPosts", 
property="Author", 
group="Author" })

You will use them by passing them to the withProjections() method as arguments that match the projection type. The value of the argument is one, a list or an array of property names to run the projection on, with the exception of id and rowcount which take a boolean true. Also, you can pass in a string separated with a : to denote an alias on the property when doing the SQL. The alias can then be used with any restriction the criteria builder can use.

Ex: avg="balance", avg="balance:myBalance", avg="balance, total", avg=["balance","total"]

// Using native approach for one projection only
var results = c.like("firstName","Lui%")
     .and( 
          c.restrictions.between( "balance", 200, 300),
          c.restrictions.eq("department", "development")
     )
     .setProjection( c.projections.rowCount() )
     .get();

// Using the withProjections() method, which enables you to do more than 1 projection
var results = c.like("firstName","Lui%")
     .and( 
          c.restrictions.between( "balance", 200, 300),
          c.restrictions.eq("department", "development")
     )
     .withProjections(rowCount=1)
     .get();

var results = c.like("firstName","Lui%")
     .and( 
          c.restrictions.between( "balance", 200, 5000),
          c.restrictions.eq("department", "development")
     )
     .withProjections(avg="balance,total",max="total:maxTotal")
     .gt("maxTotal",500)
     .list();

Detached Criteria Builder

You can now use the [Extras:Criteria Builder Detached Criteria Builder] to create programmatically create criteria and projection subqueries.

To create an instance of Detached Criteria Builder, simply call the createSubcriteria() method on your existing criteria.

The arguments for createSubcriteria() are:

Once the Detached Criteria Builder is defined, you can add projections, criterias, and associations, just like you would with Criteria Builder.

Examples

// criteria builder
c = newCriteria();
// detached criteria builder
c.createSubcriteria( 'Car', 'CarSub' );   

See the documentation for [Extras:Criteria Builder Detached Criteria Builder] for more information.

Related ORM Services

• Base ORM Services
• Virtual ORM Services
• ORM Event Handling
• Criteria Builder
• Detached Criteria Builder
• WireBox Entity Injection

Download in other Formats:
Markup | HTML | Word

Comments (1)

Add Comment

Dave DuPlantis said

at 01:53:30 PM 13-Jul-2012

I was not able to get resultTransformer to work using criteria.DISTINCT_ROOT_ENTITY as in the example above; however, using either newCriteria().DISTINCT_ROOT_ENTITY or c.DISTINCT_ROOT_ENTITY worked for me.