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

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: