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
*/functionsql( 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
Plain values.
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 valuesc.sql( "id = ?", [ 123 ] );c.sql( "userName = ? and firstName like ?", [ "joe","%joe%"] );// strong typed valuesc.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.