I just committed a very nice update for those of you who love Criteria Queries in hibernate. I have create a new CriteriaBuilder object that brings a nice programmatic DSL to work with criteria queries, projections, grouping, associations, result transformations and much more. It is inspired by GORM and it looks outstanding (At least that’s what I believe). It is already on the development branch and the following documentation will be going online soon. Here it is for those of you who want to start playing with it.
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.
The arguments for the newCriteria() method are:
- entityName - The name of the entity to bind the criteria or root the criteria query under
- useQueryCaching:boolean[false] - To allow for query caching of list() operations
-
queryCacheRegion:string[criteria.{entityName}] - The name of the cache region to use
If you call newCriteria() from the virtual service layer, then you don’t pass the entity name as it roots itself automatically.
// 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.
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, create it manually, or the Criteria object itself has a public property called “RESTRICTIONS” which you can use rather easily.
// From base ORM service
var restrictions = getRestrictions()
// From Criteria
newCriteria().RESTRICTIONS
// Manually Created
var restrictions = new coldbox.system.orm.hibernate.criterion.Restrictions();
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).
-
between(property,minValue,maxValue)
-
eq(property, value) or isEq(property,value)
-
eqProperty(property, otherProperty)
-
gt(property, value) or isGT(property, value)
-
gtProperty(property,otherProperty)
-
ge(property,value) or isGTE
-
geProperty(property, otherProperty)
-
idEQ(required any propertyValue)
-
ilike(required string property, required string propertyValue)
-
isIn(required string property, required any propertyValue) or in(required string property, required any propertyValue)
-
isEmpty(required string property)
-
isNotEmpty(required string property)
-
isNull(required string property)
-
isNotNull(required string property)
-
islt(required string property, required any propertyValue) or lt(required string property, required any propertyValue)
-
ltProperty(required string property, required string otherProperty)
-
isle(required string property, required any propertyValue) or le(required string property, required any propertyValue)
-
leProperty(required string property, required string otherProperty)
-
like(required string property, required string propertyValue)
-
ne(required string property, required any propertyValue)
-
neProperty(required string property, required any otherProperty)
-
sizeEq(required string property, required any propertyValue)
-
sizeGT(required string property, required any propertyValue)
-
sizeGE(required string property, required any propertyValue)
-
sizeLE(required string property, required any propertyValue)
-
sizeLE(required string property, required any propertyValue)
-
sizeNE(required string property, required any propertyValue)
-
sqlRestriction(required string sql)
-
conjunction(required array restrictionValues)
-
and(Criterion, Criterion, …)
-
or(Criterion, Criterion, ….)
-
disjunction(required array restrictionValues)
-
not(required any criterion) or isNot()
You can also use the add() method to add a manual restriction or array of restrictions
c.add( c.restrictions.eq(“name”,“luis”) )
But as you can see from the code, the facade methods are much nicer.
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(max, offset, timeout, sortOrder, ignoreCase, asQuery=false) Execute the criterias and give you the results.
- get() - to retrieve one result only.
- count() - Does a projection on the given criteria query and gives you the row count only, great for pagination totals or running counts.
// 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(numeric timeout)
-
readOnly(boolean readOnly)
-
firstResult()
-
fetchSize(numeric fetchSize)
-
cache(cache, cacheRegion=[] )
-
cacheRegion(cacheRegion)
-
order(property,sortDir,ignoreCase)
Associations
You can also navigate associations by nesting the criterias using the createCriteria(“association”) method and then concatenating the properties of the association.
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:
var c = newCriteria(“User”);
var users = c.like('name",“lui%”)
.createAlias(“admins”,“a”)
.eq(“a.name”,“Vero”)
.list();
Specifying Join Types
Criteria associations also allow you to chose the join type, which defaults to an inner join. The available join types are:
-
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. 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.
- 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 results = c.like(“firstName”,“Lui%”)
.and(
c.restrictions.between( “balance”, 200, 300),
c.restrictions.eq(“department”, “development”)
)
.resultTransformer( criteria.DISTINCT_ROOT_ENTITY )
.list();
Projections
Our criteria builder also supports the notion of projections. 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. Below are the available projections you can use:
-
avg
-
count
-
countDistinct
-
distinct
-
groupProperty
-
max
-
min
-
property
-
sum
-
rowCount
-
id
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 a 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. The alias can then be used with any restriction the criteria builder can use.
Argument Value: propertyName:alias or a list of propertyName:alias or an array of values:
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();