Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Whether you want to create a criteria subquery, or a projection subquery, you'll first need to get a new instance of the Detached Criteria Builder class. Since all of the subqueries we're creating are being added to our main criteria query either as a criteria or a projection, we can get the Detached Criteria Builder like so:
The arguments for the createSubcriteria() method are:
Once the Detached Criteria Builder is defined, you can add projections, criterias, and associations, just like you would with a normal Criteria Builder.
Examples
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 for the entity
When using Detached Criteria Builder for criteria or projection subqueries, you must use a projection. If you think about it from a SQL perspective, this makes sense. After all, we need our subquery to return a specific result (property values, a count, etc.) which will be compared to a property value on the root table (in the case of a criteria subquery), or which will be returned as a valid column value (in the case of a projection subquery).
All of the projections available for Criteria Builder are also available for Detached Criteria Builder.
Examples
Be careful when using projections and adding criterias to your query. Hibernate does not work well with projections and alias definitions, so if you want to add a criteria you must use this.yourPropertyName to tell Hibernate not to use the alias and build a correct SQL.
If you use ORM in your ColdBox apps, you are hopefully already taking full advantage of Criteria Builder, ColdBox’s powerful, programmatic DSL builder for Hibernate Criteria queries (and if you’re not using it, you should!). With the new Detached Criteria Builder, you can expand the power and flexibility of your criteria queries with support for criteria and projection subqueries, all while using the same intuitive patterns of Criteria Builder. No fuss, just more flexibility and control for your criteria queries!
"Some applications need to create criteria queries in "detached mode", where the Hibernate session is not available. It also allows you to express subqueries." Hibernate Docs
For more information about Detached Criteria and Subqueries, check out the following Hibernate documentation:
Hibernate DetachedCriteria: http://docs.jboss.org/hibernate/orm/3.5/api/org/hibernate/criterion/DetachedCriteria.html
Hibernate Subqueries: http://docs.jboss.org/hibernate/orm/3.5/api/org/hibernate/criterion/Subqueries.html
INFO The best place to see all of the functionality of the Detached Criteria Builder is to check out the latest API Docs.
Since we’re not writing SQL, it can sometimes be frustrating to uncover why results from Criteria Builder and Detached Criteria Builder don’t match up with what you’re expecting.
An easy way to debug in these scenarios is to enable SQL logging and actually look at the query which is ultimately executed after Hibernate does it magic.
For a good guide on setting up SQL logging for ColdFusion ORM, check out
Once SQL logging is setup, you’ll be able to instantly see the SQL which is executed to deliver the result you’re getting from your criteria queries. You can then run these queries independently (such as in SQL Server Management Studio), identify where the issues are, and tweak your criteria query until it’s perfect.
Besides using it for creating criteria subqueries, Detached Criteria Builder can also be used in conjunction with the new detachedSQLProjection() method to return a projected result based on a subquery. The detachedSQLProjection() method can be called just like any other Criteria Builder projection.
Examples
INFO If you need to use a property from the root entity in one of your criterias, simply prepend the property name with {alias}. MORE otice how a subquery method was not used in this example of the Detached Criteria Builder.
Yes, you can also create associations in Detached Criteria Builder, just like you would with Criteria Builder. Go on, make some uber-complicated queries!
Here we go!...
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:
Transform | Description |
detachedSQLProtection | A single or array of DetachedCriteriaBuilders which will return the projected value |
If you are using Detached Criteria Builder for a criteria subquery, you will also need to use one of the methods from the ColdBox subqueries class. This is what will ultimately bind the subquery result to the root entity.
The most important thing to remember is that this subquery is what needs to be added as a criterion to your criteria query. So whether you are doing a simple subquery or building up a complex detached criteria, the result of one of the methods below should be what is added as a criterion to the criteria query (see example below).
Examples
All of the same criterias defined in Criteria Builder can be utilized in a Detached Criteria Builder as well. Easy, huh?
Here they go again...
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
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");
Method
Description
detachedSQLProjection
A single or array of DetachedCriteriaBuilders which will return the projected value
subEq(value)
Where the result of the subquery is eq to a specific value
subEqAll(value)
Where all values in the subquery result are equal to a specific value
subGe(value)
Where values in the subquery result are greater than or equal to a specific value
subGeAll(value)
Where all values in the subquery result are equal to a specific value
subGeSome(value)
Where some values in the subquery result are greater than a specific value
subGt(value)
Where values in the subquery result are greater than a specific value
subGtAll(required string value)
Where all values in the subquery result are greater than a specific value
subGtSome(required string value)
Where some values in the subquery result are greater than a specific value
subIn(required string value)
Where values in the subquery result are contained within the specified list of values
subLe(required string value)
Where values in the subquery result are less than or equal to a specific value
subLeAll(required string value)
Where all values in the subquery result are less than or equal to a specific value
subLeSome(required string value)
Where some values in the subquery result are less than or equal to a specific value
subLt(required string value)
Where values in the subquery result are less than a specific value
subLtAll(required string value)
Where all values in the subquery result are less than a specific value
subLtSome(required string value)
Where some values in the subquery result are less than a specific value
subNe(required string value)
Where values in the subquery result are not equal to a specific value
subNotIn(required string value)
Where values in the subquery result are not contained within the specified list of values
exists
Where the subquery returns some result
notExists
Where the subquery does not return a result
propertyEq(required string property)
Where values in the subquery result are equal to the the specified property value
propertyEqAll(required string property)
Where all values in the subquery result are equal to the the specified property value
propertyGe(required string property)
Where values in the subquery result are greater than or equal to the the specified property value
propertyGeAll(required string property)
Where all values in the subquery result are greater than or equal to the the specified property value
propertyGeSome(required string property)
Where some values in the subquery result are greater than or equal to the the specified property value
propertyGt(required string property)
Where values in the subquery result are greater than the the specified property value
propertyGtAll(required string property)
Where all values in the subquery result are greater than the the specified property value
propertyGtSome(required string property)
Where some values in the subquery result are greater than the the specified property value
propertyIn(required string property)
Where values in the subquery result are contained in within the list of values for the specified property
propertyLe(required string property)
Where values in the subquery result are less than or equal to the the specified property value
propertyLeAll(required string property)
Where all values in the subquery result are less than or equal to the the specified property value
propertyLeSome(required string property)
Where some values in the subquery result are less than or equal to the the specified property value
propertyLt(required string property)
Where values in the subquery result are less than the the specified property value
propertyLtAll(required string property)
Where all values in the subquery result are less than the the specified property value
propertyLtSome(required string property)
Where some values in the subquery result are less than the the specified property value
propertyNe(required string property)
Where values in the subquery result are not equal to the the specified property value
propertyNotIn(required string property)
Where values in the subquery result are not contained in within the list of values for the specified property