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

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 Hibernate Criteria Queries.

The ColdBox Hibernate Criteria Builder is a powerful object that will help you build and execute hibernate criteria queries in a fluent and dynamic manner. 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. 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.

userService
    .newCriteria()
    .eq( "name", "luis" )
    .isTrue( "isActive" )
    .getOrFail();

userService
    .newCriteria()
    .isTrue( "isActive" )
    .joinTo( "role" )
        .eq( "name", "admin" )
    .asStream()
    .list();

userService
    .newCriteria()
    .withProjections( property="id,fname:firstName,lname:lastName,age" )
    .isTrue( "isActive" )
    .joinTo( "role" )
        .eq( "name", "admin" )
    .asStruct()
    .list();

Resources

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

1. http://docs.jboss.org/hibernate/core/3.5/reference/en-US/html/querycriteria.html

2. http://docs.jboss.org/hibernate/core/3.5/javadoc/org/hibernate/Criteria.html

3. http://docs.jboss.org/hibernate/core/3.5/javadoc/org/hibernate/criterion/Restrictions.html

4. https://www.baeldung.com/hibernate-criteria-queries

5. https://howtodoinjava.com/hibernate/hibernate-criteria-queries-tutorial/

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

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:

// orm service
ormService.newCriteria( "User" );

// virtual service
productService.newCriteria();

// active entity
getInstance( "User" ).newCriteria();

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.

We provide you with tons of available restrictions and if none of those match what you need, you can even use a-la-carte SQL restrictions, in which you can just use SQL even with parameters. You can also do OR statements or embedded ANDs, etc.

productService
    .newCriteria()
    .ge( "price", 30 )
    .count();
    
userService
    .newCriteria()
    .isTrue( "isActive" )
    .notIsNull( "lastLogin" )
    .orderBy( "lastLogin desc" )
    .firstResult()
    .get();


// A-la-carte SQL restrictions 
var userStream = userService
    .newCriteria()
    .sql( "userName = ? and firstName like ? and lastLogin >= ?", [
    	{ value : "joe", type : "string" },
    	{ value : "%joe%", type : "string" }
        { value : incomingDate, type : "timestamp" }
    ] )
    .list( asStream = true );
    

var c = newCriteria();
var results = c.like("firstName","Lui%") // restriction
     .maxResults( 50 ) // modifier
     .order("balance","desc") // modifier
     // AND restrictions
     .and( 
          c.restrictions.between( "balance", 200, 300),
          c.restrictions.eq("department", "development")
     )
     // Retrieve a list
     .list();

Associations

You can also use your restrictions on the associated entity data. This is achieved via the association methods section.

Query Modifiers

You can also add modifiers for the execution of the query. This can be sorting, timeouts, join types and so much more.

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

var results = c.like("firstName","Lui%") // restriction
     .firstResult( 25 ) // modifier
     .maxResults( 50 ) // modifier
     .order( "balance", "desc" ) // modifier
     .timeout( 
     // AND restrictions
     .and( 
          c.restrictions.between( "balance", 200, 300),
          c.restrictions.eq("department", "development")
     )
     // Retrieve a list
     .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

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() - Convenience method to return a single instance that matches the built up criterias query, or null if the query returns no results.

• getOrFail() - 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

productService
    .newCriteria()
    .ge( "price", 30 )
    .count();
    
userService
    .newCriteria()
    .isTrue( "isActive" )
    .notIsNull( "lastLogin" )
    .orderBy( "lastLogin desc" )
    .firstResult()
    .get();

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() - Returns the SQL string that will be prepared for the criteria object at the time of request

• 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

var sql = userService
    .newCriteria()
    .sql( "userName = ? and firstName like ? and lastLogin >= ?", [
    	{ value : "joe", type : "string" },
    	{ value : "%joe%", type : "string" }
        { value : incomingDate, type : "timestamp" }
    ] )
    .getSqlLog();
    
// You can also use peek()
var results = userService
    .newCriteria()
    .sql( "userName = ? and firstName like ? and lastLogin >= ?", [
    	{ value : "joe", type : "string" },
    	{ value : "%joe%", type : "string" }
        { value : incomingDate, type : "timestamp" }
    ] )
    .peek( function( c ){
        log.debug( "sql log: #c.getSqlLog()#" );
    });
    .list();

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.

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

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

If there isn't one defined in the CFML equivalent then don't worry, just call it like is 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 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.

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.

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

Fluent If Statements - when()

There comes times where you need some 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:

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.

Hibernate also supports the ability to work with projections and 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 or a projection of the data. This is similar to how you use to select data in a cfquery call.

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.

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:

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.

Examples

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.