SQL restrictions will allow you to add ad-hoc SQL to the criteria you are building. Simple enough, but we have made great strides to make this look easy for a developer but behind the scenes we take each sql you pass and compile it so we can abstract all the native Java types for you. This means that you can use it in a similar manner to executeQuery() in CFML.

Method Signature

The method you will use for this restriction is sql()

/**
 * Use arbitrary SQL to modify the resultset
 *
 * @sql The sql to execute, it can contain parameters via positional `?` placeholders
 * @params This is an array of value definitions which need to be a struct of { value: , type: } or if the value is a simple value, we will try to infer it's type
 */
function sql( required string sql, array params=[] )

Arguments

The method takes in two arguments:

• sql - The ad-hoc query to execute. You can use positional parameters via the ? placeholder.

• params - An array of parameters to bind the SQL with, can be simple values or a struct of a values and a supported type or a combination of both.

SQL Params

The parameters you bind the SQL with can be of two types

1. Plain values.

2. Typed values

If you use plain values, then we will INFER the type from it, which is not as accurate as using a typed value. A typed value is a struct with the value and a valid type.

c.sql( "isActive = true" );

// simple values
c.sql( "id = ?", [ 123 ] );
c.sql( "userName = ? and firstName like ?", [ "joe", "%joe%"] );

// strong typed values
c.sql( "id = ?", [ { value:123, type=c.TYPES.integer } ] );
c.sql( "isActive = ?", [ { value:true, type=c.TYPES.boolean } ] );
c.sql( "userName = ? and firstName like ?", [
    { value : "joe", type : "string" },
    { value : "%joe%", type : "string" }
] );

Valid Types

Below you can see the TYPES struct available in the criteria builder object which map the CFML types to the native Hibernate Types.

this.TYPES = {
    "string"      : "StringType",
    "clob"        : "ClobType",
    "text"        : "TextType",
    "char"        : "ChareacterType",
    "boolean"     : "BooleanType",
    "yesno"       : "YesNoType",
    "truefalse"   : "TrueFalseType",
    "byte"        : "ByteType",
    "short"       : "ShortType",
    "integer"     : "IntegerType",
    "long"        : "LongType",
    "float"       : "FloatType",
    "double"      : "DoubleType",
    "bigInteger"  : "BigIntegerType",
    "bigDecimal"  : "BigDecimalType",
    "timestamp"   : "TimestampType",
    "time"        : "TimeType",
    "date"        : "DateType",
    "calendar"    : "CalendarType",
    "currency"    : "CurrencyType",
    "locale"      : "LocaleType",
    "timezone"    : "TimeZoneType",
    "url"         : "UrlType",
    "class"       : "ClassType",
    "blob"        : "BlobType",
    "binary"      : "BinaryType",
    "uuid"        : "UUIDCharType",
    "serializable": "SerializableType"
};

Inferred Types

The inferred types we infer are the following and in the following order.

1. Binary

2. Boolean

3. Time

4. Date

5. uuid

6. float

7. numeric

8. url

9. string

10. text

Query Modifiers

The following methods alters the behavior of the executed query, some can be a life saver, so check them all out.

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

newCriteria()
	 .eq( "this", value )
	 .peek( function(criteria){
	 	systemOutput( "CurrentSQL: #criteria.getSQLLog()#" )
	 })
	 .when( !isNull( arguments.published ), function( c ){
		c.eq( "isPublished", published )
	 })
	 .list();

Result Modifiers

You can also tell Hibernate to transform the results to other formats for you once you retrieve them.

• asDistinct() - Applies a result transformer of DISTINCT_ROOT_ENTITY

• asStruct() - Applies a result transformer of ALIAS_TO_ENTITY_MAP so you get an array of structs instead of array of objects

• asStream() - Get the results as a CBstream

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.

So the idea is the you request a query, add all kinds of restrictions and modifiers and then you request the results from it.

count( propertyName = "' )

Get the record count using hibernate projections for the given criterias. You can optionally pass in the name of the property to do the count on, else it doesn * by default.

get( properties="" )

Convenience method to return a single instance that matches the built up criterias, or null if the query returns no results. It can also throw the following exception: NonUniqueResultException - if there is more than one matching result. It can also take in a property list in the properties argument so instead of giving you a full ORM entity object, it will give you a struct of those properties.

getOrFail( properties="" )

Convenience method to return a single instance that matches the built up criterias, or throw an exception (EntityNotFound) if the query returns no results. It can also throw the following exception: NonUniqueResultException - if there is more than one matching result. It can also take in a property list in the properties argument so instead of giving you a full ORM entity object, it will give you a struct of those properties.

list( ... )

Execute the criteria queries you have defined and return the results, you can pass optional parameters to manipulate the way the results are sent back to you. Let's look at the method signature for this awesome method:

As you can see from the signature above, the first two arguments (offset, max) are used for pagination. So you can easily paginate the result set. The timeout argument can be used if the query is expected to be heavy duty, we want to make sure we timeout the execution (throws exception). The ignorecase is only used for sorting orders.

You can navigate associations in criteria queries in several ways:

• Dot notation for many-to-one relationships ONLY

• Helper methods to create inner criterias or joins: createCriteria(), joinTo()

Dot Notation Navigation

This type of navigation is the easiest but ONLY works with many-to-one associations. Let's say you have a User entity with a Role and the Role has the following properties: id, name, slug and you want to get all users that have the role slug of admin and are active. Then you could do this:

Joins

You can also use the joinTo() method, which previously was called createAlias() to create joins to related associations. Let's check out the method signature first:

The arguments can be further explained below:

• associationName : This is the name of the property on the target entity that is the association

• alias : This is the alias to assign it so you can reference it later in the criterions following it

• joinType : By default it is an inner join. The available joins are: INNER_JOIN, FULL_JOIN, LEFT_JOIN

• withClause : This is the criterion (so it's a restriction) to be added to the join condition, basically the ON clause.

Inner Criterias

The last journey to query on associations is to pivot the root entity of the criteria to an association. This means that you will create a new criteria object based on the previous criteria, but now the target entity is the one you assign. PHEW! That's a mouthful. Basically, it's a nice way to traverse into the join and stay in that entity.

This is accomplished via the createCriteria() method or the nice dynamic alias: with{entity}() method.

The arguments can be further explained below:

• associationName : This is the name of the property on the target entity that is the association

• alias : This is the alias to assign it so you can reference it later in the criterions following it

• joinType : By default it is an inner join. The available joins are: INNER_JOIN, FULL_JOIN, LEFT_JOIN

• withClause : This is the criterion (so it's a restriction) to be added to the join condition, basically the ON clause.

We have created several methods available to you in the criteria builders to help you with casting ColdFusion values to Java values so you can use them in Criteria Queries. Most of the time the engines can do this for us but not always, so we would recommend to just use these convenience methods when needed.

BaseBuilder Casting Methods

nullValue()

Produce a null value that can be used anywhere you like!

autoCast( propertyName, value )

This method allows you to cast any value to the appropriate type in Java for the property passed in.

idCast( id )

This method allows you to cast the identifier value to the appropriate type in Java.

Example

So instead of casting it manually you can just let us do the work by calling these methods from any of the services.

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 criteria to match against or in SQL terms, your WHERE statements.

The ColdBox criteria class offers most of the criterion methods found in the native hibernate Restrictions class:

If one isn't defined in the CFML equivalent, just call it as it appears in the Javadocs and we will proxy the call to the native Hibernate class for you.

Direct Reference

You can get a direct reference to the Restrictions class via the Base/Virtual ORM services (getRestrictions()), or the Criteria object itself has a public property called restrictions which you can use rather easily. We prefer the latter approach. Now, please 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.

Building Restrictions

To build our criteria queries we can 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. 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.

between( property, minValue, maxValue )

Where the property value is between two distinct values

conjunction( required array restrictionValues )

Group expressions together in a single conjunction (A and B and C...) and return the conjunction

disjunction( required array restrictionValues )

Group expressions together in a single disjunction (A or B or C...)

isEQ( property, value )

Where a property equals a particular value, you can also use eq()

isGT( property, value )

Where a property is greater than a particular value, you can also use gt()

gtProperty( property,otherProperty )

Where a one property must be greater than another

isGE( property,value )

Where a property is greater than or equal to a particular value, you can also use ge()

geProperty( property, otherProperty )

Where one property must be greater than or equal to another

idEQ( required any propertyValue )

Where an object's id equals the specified value

iLike( required string property, required string propertyValue )

A case-insensitive 'like' expression

isIn( 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()

isEmpty( required string property )

Where a collection property is empty

isNotEmpty( required string property )

Where a collection property is not empty

isFalse( required string property )

Where a collection property is false

isNull( required string property )

Where a property is null

isNotNull( required string property )

Where a property is NOT null

isLT( required string property, required any propertyValue )

Where a property is less than a particular value, you can also use lt()

ltProperty( required string property, required string otherProperty )

Where a one property must be less than another

isLE( required string property, required any propertyValue )

Where a property is less than or equal a particular value, you can also use le()

leProperty( required string property, required string otherProperty )

Where a one property must be less than or equal to another

like( required string property, required string propertyValue )

Equivalent to SQL like expression

ne( required string property, required any propertyValue )

Where a property does not equal a particular value

neProperty( required string property, required any otherProperty )

Where one property does not equal another

sizeEq( required string property, required any propertyValue )

Where a collection property's size equals a particular value

sizeGT( required string property, required any propertyValue )

Where a collection property's size is greater than a particular value

sizeGE( required string property, required any propertyValue )

Where a collection property's size is greater than or equal to a particular value

sizeLT( required string property, required any propertyValue )

Where a collection property's size is less than a particular value

sizeLE( required string property, required any propertyValue )

Where a collection property's size is less than or equal a particular value

sizeNE( required string property, required any propertyValue )

Where a collection property's size is not equal to a particular value

sql( required sql, params )

Use arbitrary SQL to modify the resultset

and( Criterion, Criterion, ... )

Return the conjuction of N expressions as arguments

or( Criterion, Criterion, …. )

Return the disjunction of N expressions as arguments

isNot( required any criterion )

Return the negation of an expression. You can also use not()

isTrue( required string property )

Returns if the property is true

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.

Negation

Every restriction method you see above or in the docs can also be negated very easily by just prefixing the method with a not .

when()

There are times where you need if statements in order to add criterias based on incoming input. That's ok, but we can do better by adding a when( test, target ) function that will evaluate the test argument or expression. If it evaluates to true then the target closure is called for you with the criteria object so you can do your criterias:

Hibernate provides several ways to retrieve data from the database. We have seen the normal entity loading operations in our basic CRUD and we have seen several HQL and SQL query methods as well. The last one is the .

The ColdBox Hibernate Criteria Builder is a powerful object that will help you build and execute in a fluent and dynamic manner. 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. This is error prone, syntax crazy and sometimes untestable.

The ColdBox Criteria Builders offers a powerful programmatic DSL builder for Hibernate Criteria queries. It focuses on a criteria object that you will build up to represent the query to execute. The cool thing is that you can even retrieve the exact HQL or even SQL the criteria query will be executing. You can get the explain plans, provide query hints and much more. In our experience, criteria queries will make your life much easier when doing complicated queries.

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.

Resources

You can see below some of the Hibernate documentation on criteria queries.

A criteria builder object can be requested from our Base ORM services or a virtual service or an ActiveEntity, which will bind itself automatically to the requested entity, by calling on the newCriteria() method. The corresponding class is: cborm.models.CriteriaBuilder

Criteria Object - newCriteria()

The arguments for the newCriteria() method are:

Restrictions

This criteria object will then be used to add restrictions to build up the exact query you want. Restrictions are basically your where statements in SQL and they build on each other via ANDs by default. For example, only retrieve products with a price over $30 or give me only active users.

Associations

Query Modifiers

• cache() - Enable caching of this query result, provided query caching is enabled for the underlying session factory.

• cacheRegion() - Set the name of the cache region to use for query result caching.

• comment() - Add a comment to the generated SQL.

• fetchSize() - Set a fetch size for the underlying JDBC query.

• firstResult() - Set the first result to be retrieved or the offset integer

• maxResults() - Set a limit upon the number of objects to be retrieved.

• order() - Add an ordering to the result set, you can add as many as you like

• queryHint() - Add a DB query hint to the SQL. These differ from JPA's QueryHint, which is specific to the JPA implementation and ignores DB vendor-specific hints. Instead, these are intended solely for the vendor-specific hints, such as Oracle's optimizers. Multiple query hints are supported; the Dialect will determine concatenation and placement.

• readOnly() - Set the read-only/modifiable mode for entities and proxies loaded by this Criteria, defaults to readOnly=true

• timeout() - Set a timeout for the underlying JDBC query in milliseconds.

Result Modifiers

You can also tell Hibernate to transform the results to other formats for you once you retrieve them.

• asDistinct() - Applies a result transformer of DISTINCT_ROOT_ENTITY

• asStruct() - Applies a result transformer of ALIAS_TO_ENTITY_MAP so you get an array of structs instead of array of objects

• asStream() - Get the results as a CBstream

Getting Results

Now that the criteria builder object has all the restrictions and modifiers attached when can execute the SQL. Please note that you can store a criteria builder object if you wanted to. It is lazy evaluated, it just represents your SQL. It will only execute when you need it to execute via the following finalizer methods:

• list() - Execute the criteria queries you have defined and return the results as an array of objects

• get( [properties] ) - Convenience method to return a single instance that matches the built up criterias query, or null if the query returns no results.

• getOrFail( [properties] ) - Convenience method to return a single instance that matches the built up criterias query, or throws an exception if the query returns no results

• count() - Get the record count using hibernate projections for the given criterias

Logging

There are several methods available to you in the criteria objects to give you the actual SQL or HQL to execute, even with bindings. These are a true life-saver.

• logSQL( label ) - Allows for one-off sql logging at any point in the process of building up CriteriaBuilder; will log the SQL state at the time of the call

• getSQL( returnExecutableSql = false, formatSql ) - Returns the SQL string that will be prepared for the criteria object at the time of request. If you set returnExecutableSql to true , the SQL returned will include the parameters populated.

• getPositionalSQLParameters() - Returns a formatted array of parameter value and types

• getSqlLog() - Retrieves the SQL Log

• startSqlLog() - Triggers CriteriaBuilder to start internally logging the state of SQL at each iterative build

• stopSqlLog() - Stop the internal logging.

• logSql() - Allows for one-off sql logging at any point in the process of building up CriteriaBuilder; will log the SQL state at the time of the call

• canLogSql() - Returns whether or not CriteriaBuilder is currently configured to log SQL

• peek( function/closure ) - Used to peek into the criteria builder process. You pass in a closure/lambda that receives the criteria. You can then use it to peek into the sql or more.

Hibernate also supports the ability to work with sql projections and sql aggregates. Instead of treating the results as an array of objects or a stream of objects you can return the result as a row with columns of data.

var statusReport = c
    .withProjections( count: "isActive:authors", groupProperty: "isActive" )
    .asStruct()
    .list();

This is great for API driven applications as you DON"T have to retrieve the entire object graphs, you can decide which columns to bring back and return an array of structs with lightening speed by just using the asStruct() modifier.

There are several projection types you can use which are great for doing counts, distinct counts, max values, sums, averages and much more.

withProjections()

The method in the criteria builder that will allow you to add projections is called withProjections(). You will then use it's arguments to tell Hibernate what projection or aggregates to compile into the query. Here is the method signature:

/**
 * Setup projections for this criteria query, you can pass one or as many projection arguments as you like.
 * The majority of the arguments take in the property name to do the projection on, which will also use that as the alias for the column
 * or you can pass an alias after the property name separated by a : Ex: projections(avg="balance:avgBalance")
 * The alias on the projected value can be referred to in restrictions or orderings.
 * Please also note that the resulting array locations are done in alphabetical order of the arguments.
 *
 * @avg The name of the property to avg or a list or array of property names
 * @count The name of the property to count or a list or array of property names
 * @countDistinct The name of the property to count distinct or a list or array of property names
 * @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
 * @groupProperty The name of the property to group by or a list or array of property names
 * @id The projected identifier value
 * @max The name of the property to max or a list or array of property names
 * @min The name of the property to min or a list or array of property names
 * @property The name of the property to do a projected value on or a list or array of property names
 * @rowCount Do a row count on the criteria
 * @sum The name of the property to sum or a list or array of property names
 * @sqlProjection Do a projection based on arbitrary SQL string
 * @sqlGroupProjection Do a projection based on arbitrary SQL string, with grouping
 * @detachedSQLProjection Do a projection based on a DetachedCriteria builder config
 */
any function withProjections(
	string avg,
	string count,
	string countDistinct,
	any distinct,
	string groupProperty,
	boolean id,
	string max,
	string min,
	string property,
	boolean rowCount,
	string sum,
	any sqlProjection,
	any sqlGroupProjection,
	any detachedSQLProjection
){

Arguments

Below are the available projections you can use from this method

The value of the arguments 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.

Aliases

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", // balance as myBalance
avg="balance, total", 
avg=["balance","total"]

Examples

// 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();
     
var results = c
     .withProjections( property="id,name,username" )
     .isTrue( "isActive" )
     .asStruct()
     .list();

Projections

Here is a detail overview of each projection type.

avg

The name of the property to average or a list or array of property names to average on

// projection
withProjections( avg = "salary" )
// Produces
select avg( salary ) from User

// projection with alias
withProjections( avg = "salary:totalSalary" )
// Produces
select avg( salary ) as totalSalary from User

// Multiple projections
withProjections( avg = "salary,balance" )
// Produces
select avg( salary ), avg( balance ) from User

count

The name of the property to count on or a list or array of property names to count on

// projection
withProjections( count = "id" )
// Produces
select count( id ) from User

// projection with alias
isTrue( "isActive" )
    .withProjections( count = "id:TotalUsers" )
// Produces
select count( id ) as totalUsers from User
where isActive = true

countDistinct

Get a distinct count on a property or list of properties

// projection
withProjections( distinctCount = "city:cities" )
// Produces
SELECT COUNT( DISTINCT city ) as cities
FROM User;

distinct

Distinct is used to return only distinct (different) values from a property or list of properties

// projection
withProjections( distinct = "city" )
// Produces
SELECT DISTINCT city
FROM User;

groupProperty

Which properties to group in the SQL statement.

// project users by country
withProjections( property : "country", count : "id" , groupProperty : "country" )

// Produces
SELECT COUNT(id), Country
FROM User
GROUP BY Country;

id

Return an array of IDs of the current projected entity. Let's say we want all the user id's that have never logged in to the system. So we can use that to send them mails.

isNull( "lastLogin" )
    .withProjections( id : true )
    .list();

// Produces
SELECT id
FROM User
WHERE lastLogin is null

max

Return a max() sql projection on a a property or list of properties

// projection
withProjections( max = "salary" )
// Produces
select max( salary ) from User

min

Return a min() sql projection on a a property or list of properties

// projection
withProjections( min = "salary" )
// Produces
select min( salary ) from User

property

Return or a list of properties in the projection. Basically pick and choose in the select statement. The cool thing is that this can be a property or a full relationship, which can give you an array of properties and objects.

You can navigate relationships using the dot notation.

// projection
withProjections( property = "id,fname,lname,email" )
// Produces
select id, fname, lname, email from User

// Get all site core settings in the database
newCriteria()
.isFalse( "isDeleted" )
.isTrue( "isCore" )
.joinTo( "site", "site" )
.withProjections( property: "name,site.slug:siteSlug" )
.asStruct()
.list( sortOrder = "site.slug,name" );

// Produces
SELECT name, site.slug as siteSlug
FROM setting, site
WHERE setting.isDelete = false AND
setting.isCore = true AND
site.siteId = setting.FK_siteID
order by site.slug, setting.name

rowcount

Run a count( * ) on the projection alongside other projections.

r = criteria
.withProjections(
	min      = "lastLogin",
	rowCount = true,
	max      = "lastLogin"
)
.peek( function( c ){
	debug( c.getSql( true, true ) );
} )
.list();

// Produces
select
    max(this.lastLogin) as y0_,
    min(this.lastLogin) as y1_,
    count(*) as y2_ // this is due to the rowcount
from
    users this_

sum

Run a sql sum() on a one or many properties

// projection
withProjections( sum = "salary:payroll" )
// Produces
select sum( salary ) as payroll from User

sqlProjection/sqlGroupProjection

Do a projection based on arbitrary SQL and SQL grouping strings. The value can be a single struct or an array of structs with the following pattern:

• sql - The raw sql to execute

• alias - The aliases to apply

• property - The property projected on

• group - What to group on if needed

.withProjections(
	groupProperty = "catid",
	sqlProjection = [
		{
			sql      : "count( category_id )",
			alias    : "count",
			property : "catid"
		}
	],
	sqlGroupProjection = [
		{
			sql      : "year( modifydate )",
			group    : "year( modifydate )",
			alias    : "modifiedDate",
			property : "id"
		},
		{
			sql      : "dateDiff('2021-12-31 23:59:59','2021-12-30')",
			group    : "dateDiff('2021-12-31 23:59:59','2021-12-30')",
			alias    : "someDateDiff",
			property : "id"
		}
	]
)
.asStruct()
.peek( function( c ){
	debug( c.getSql( true, true ) );
} )
.list();

This will produce the following SQL

select
    this.category_id as y0_,
    count(category_id) as count,
    year(modifydate) as modifiedDate,
    dateDiff('2021-12-31 23:59:59', '2021-12-30') as someDateDiff
from
    categories this_
group by
    this.category_id,
    year(modifydate),
    dateDiff('2021-12-31 23:59:59', '2021-12-30')