Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
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.
Ok, now that the formalities have been explained let's build some criterias.
The following methods alters the behavior of the executed query:
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.
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().
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).
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.
You can also use the add() method to add a manual restriction or array of restrictions to the criteria you are building.
But as you can see from the code, the facade methods are much nicer.
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)
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()
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");
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.
Hibernate Criteria Queries: http://docs.jboss.org/hibernate/core/3.5/reference/en-US/html/querycriteria.html
Hibernate Criteria Interface: http://docs.jboss.org/hibernate/core/3.5/javadoc/org/hibernate/Criteria.html
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.
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.
You can also use a hibernate property approach which aliases the association much how HQL approaches it by using the createAlias("associationName","alias") method:
Let's see the method signatures for these guys:
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.
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:
Once the Detached Criteria Builder is defined, you can add projections, criterias, and associations, just like you would with Criteria Builder.
Examples
See the documentation for ORM:DetachedCriteriaBuilder for more information.
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:
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.
INFO If the :alias is not used, then the alias becomes the property name.
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: cborm.models.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
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.
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
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
Argument
Type
Required
Default
Description
entityName
string
true
---
The name of the entity to bind this criteria builder with, the initial pivot.
useQueryCaching
useQueryCaching
false
false
To allow for query caching of list() operations
queryCacheRegion
string
false
criteria.{entityName}
The name of the cache region to use