help  namespaces  categories  help  rss feeds

ORM:CriteriaBuilder

<< Back to Dashboard | << Extras Viewer

Contents

ColdBox Criteria Builder

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

Covers up to version 3.7.0

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.

Note: The best place to see all of the functionality of the Criteria Builder is to check out the latest API Docs.

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:

Argument Type Required Default Description
entityName string true --- The name of the entity to bind this criteria builder with, the initial pivot.
useQueryCaching boolean false false To allow for query caching of list() operations
queryCacheRegion string false criteria.{entityName} The name of the cache region to use

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).

Method Description Example
between(property,minValue,maxValue) Where the property value is between two distinct values
c.between("age",10,30);
conjunction(required array restrictionValues) Group expressions together in a single conjunction (A and B and C...) and return the conjunction
c.conjunction( [ 
     c.restrictions.between("balance",100,200), 
     c.restrictions.lt("salary",20000) ] );
disjunction(required array restrictionValues) Group expressions together in a single disjunction (A or B or C...)
c.disjunction( [ 
     c.restrictions.between("balance",100,200), 
     c.restrictions.lt("salary",20000) ] );
eqProperty(property, otherProperty) Where one property must equal another
c.eqProperty("createDate","modifyDate");
eq(property, value) or isEq(property,value) Where a property equals a particular value, you can also use eq()
c.eq("age",30);
gt(property, value) or isGT(property, value) Where a property is greater than a particular value, you can also use gt()
c.gt("publishedDate", now() );
gtProperty(property,otherProperty) Where a one property must be greater than another
c.gtProperty("balance","overdraft");
ge(property,value) or isGE Where a property is greater than or equal to a particular value, you can also use ge()
c.ge("age",18);
geProperty(property, otherProperty) Where a one property must be greater than or equal to another
c.geProperty("balance","overdraft");
idEQ(required any propertyValue) Where an objects id equals the specified value
c.idEq( 4 );
ilike(required string property, required string propertyValue) A case-insensitive 'like' expression
c.ilike("lastName", "maj%");
isIn(required string property, required any propertyValue) or in(required string property, required any propertyValue) Where a property is contained within the specified list of values, the property value can be a collection (struct) or array or list, you can also use in()
c.isIn( "id", [1,2,3,4] );
isEmpty(required string property) Where a collection property is empty
c.isEmpty("childPages");
isNotEmpty(required string property) Where a collection property is not empty
c.isNotEmpty("childPages");
isFalse(required string property) Where a collection property is false
c.isFalse("isPublished");
isNull(required string property) Where a property is null
c.isNull("passwordProtection");
isNotNull(required string property) Where a property is NOT null
c.isNotNull("publishedDate");
islt(required string property, required any propertyValue) or lt() Where a property is less than a particular value, you can also use lt()
c.isLT("age", 40 );
ltProperty(required string property, required string otherProperty) Where a one property must be less than another
c.ltProperty("sum", "balance");
isle(required string property, required any propertyValue) or le() Where a property is less than or equal a particular value, you can also use le()
c.isLE("age", 30);
leProperty(required string property, required string otherProperty) Where a one property must be less than or equal to another
c.LeProperty("balance","balance2");
like(required string property, required string propertyValue) Equivalent to SQL like expression
c.like("content", "%search%");
ne(required string property, required any propertyValue) Where a property does not equal a particular value
c.ne("isPublished", true);
neProperty(required string property, required any otherProperty) Where one property does not equal another
c.neProperty("password","passwordHash");
sizeEq(required string property, required any propertyValue) Where a collection property's size equals a particular value
c.sizeEq("comments",30);
sizeGT(required string property, required any propertyValue) Where a collection property's size is greater than a particular value
c.sizeGT("children",5);
sizeGE(required string property, required any propertyValue) Where a collection property's size is greater than or equal to a particular value
c.sizeGE("children", 10);
sizeLT(required string property, required any propertyValue) Where a collection property's size is less than a particular value
c.sizeLT("childPages", 25 );
sizeLE(required string property, required any propertyValue) Where a collection property's size is less than or equal a particular value
c.sizeLE("childPages", 25 );;
sizeNE(required string property, required any propertyValue) Where a collection property's size is not equal to a particular value
c.sizeNE("childPages",0);
sqlRestriction(required string sql) Use arbitrary SQL to modify the resultset
c.sqlRestriction("char_length( lastName ) = 10");
and(Criterion, Criterion, ...) Return the conjuction of N expressions as arguments
c.and( c.restrictions.eq("name","luis"), c.restrictions.gt("age",30) );
or(Criterion, Criterion, ….) Return the disjunction of N expressions as arguments
c.or( c.restrictions.eq("name","luis"), c.restrictions.eq("name", "joe") );
not(required any criterion) or isNot() Return the negation of an expression
c.isNot( c.restrictions.eg("age", 30) );
isTrue(required string property) Returns if the property is true
c.isTrue("isPublished");

Note: In some cases (isEq(), isIn(), etc), you may receive data type mismatch errors. These can be resolved by using JavaCast on your criteria value.

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.

Method Description Example
list(max, offset, timeout, sortOrder, ignoreCase, asQuery=false) Execute the criterias and give you the results.
list(), list(max=50,offset=51,timeout=3000,ignoreCase=true)
get() Retrieve one result only.
get()
count() Does a projection on the given criteria query and gives you the row count only, great for pagination totals or running counts. Note, count() can't be called on a criteria after list() has been executed.
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();

Note: You can call count() and list() on the same criteria, but due to the internal workings of Hibernate, you must call count() first, then list().

Configuration Modifiers

The following methods alters the behavior of the executed query:

Method Description Example
timeout(numeric timeout) Set a timeout for the underlying JDBC query in milliseconds.
timeout( 5000 )
readOnly(boolean readOnly) Set the read-only/modifiable mode for entities and proxies loaded by this Criteria, defaults to readOnly=true
readOnly(true)
firstResult() Specifies the offset for the results. A value of 0 will return all records up to the maximum specified.
firstResult(11)
maxResults(numeric maxResults) Set a limit upon the number of objects to be retrieved.
maxResults(25)
fetchSize(numeric fetchSize) Set's the fetch size of the underlying JDBC query
fetchSize(50)
cache(cache, cacheRegion= ) Tells Hibernate whether to cache the query or not (if the query cache is enabled), and optionally choose a cache region
cache(true), cache(true,'my.cache')
cacheRegion(cacheRegion) Tells Hibernate the cache region to store the query under
cacheRegion('my.cool.cache')
order(property,sortDir='asc',ignoreCase=false) Specifies both the sort property (the first argument, the sort order (either 'asc' or 'desc'), and if it should ignore cases or not
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:

Join Type Description
FULL_JOIN Specifies joining to an entity based on a full join.
INNER_JOIN Specifies joining to an entity based on an inner join.
LEFT_JOIN Specifies joining to an entity based on a left outer join.
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?)

Transform Description
ALIAS_TO_ENTITY_MAP Each row of results is a Map from alias to entity instance
DISTINCT_ROOT_ENTITY Each row of results is a distinct instance of the root entity
PROJECTION This result transformer is selected implicitly by calling setProjection()
ROOT_ENTITY Each row of results is an instance of the root entity

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:

Transform Description Example
avg The name of the property to avg or a list or array of property names
withProjections(avg="salary")
count The name of the property to count or a list or array of property names
withProjections(count="comments")
countDistinct The name of the property to count distinct or a list or array of property names
withProjections(countDistinct="email")
distinct The name of the property to do a distinct on, this can be a single property name a list or an array of property names
withProjections(distinct="email")
groupProperty The name of the property to group by or a list or array of property names
withProjections(groupproperty="lastName")
max The name of the property to max or a list or array of property names
withProjections(max="lastLogin")
min The name of the property to min or a list or array of property names
withProjections(min="cid")
property The name of the property to do a projected value on or a list or array of property names
withProjections(property="firstname")
sum The name of the property to sum or a list or array of property names
withProjections(sum="balance")
rowCount Do a row count on the criteria
withProjections(rowcount=1)
id Return the projected identifier value
withProjections(id=1)
sqlProjection Return projected value for sql fragment. Can accept a single config {sql,alias,property}, or an array of multiple configs.
withProjections(sqlProjection={sql="SELECT count( * ) from blog where Year < 2006 and Author={alias}.Author",
alias="BlogPosts", 
property="Author" })
sqlGroupProjection Return projected value for sql fragment with grouping. Can accept a single config( sql,alias,property,group}, or an array of multiple configs.
withProjections(sqlGroupProjection={sql="SELECT count( * ) from blog where Year < 2006 and Author={alias}.Author", 
alias="BlogPosts", 
property="Author", 
group="Author" })
detachedSQLProjection Creates a sqlProjection() based on Detached Criteria Builder See Detached Criteria Builder

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"]

Note: If the :alias is not used, then the alias becomes the property name.

// 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 ORM:DetachedCriteriaBuilder 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:

Argument Type Required Default Description
entityName string true --- The name of the entity to bind this detached criteria builder with.
alias string true -- The alias to use

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 ORM:DetachedCriteriaBuilder for more information.

Related ORM Services

 
Download in other Formats:
markup Markup | html HTML | word Word

comments Comments (1)

dlduplantis@herffjones.com's Gravatar

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.

ColdBox Books