Getting Started
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:
Argument
Type
Required
Default
Description
entityName
string
true
---
The name of the entity to bind this criteria builder with, the initial pivot.
useQueryCaching
boolean
false
false
To allow for query caching of list() operations
queryCacheRegion
string
false
criteria.{entityName}
The name of the cache region to use
datasource
string
false
System Default
The datasource to bind the criteria query on, defaults to the one in this ORM service
If you call newCriteria() from a virtual service layer or Active Entity, then you don't pass the entityName argument as it roots itself automatically.
1
// orm service
2
ormService.newCriteria( "User" );
3
4
// virtual service
5
productService.newCriteria();
6
7
// active entity
8
getInstance( "User" ).newCriteria();
Copied!

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.
1
productService
2
.newCriteria()
3
.ge( "price", 30 )
4
.count();
5
6
userService
7
.newCriteria()
8
.isTrue( "isActive" )
9
.notIsNull( "lastLogin" )
10
.orderBy( "lastLogin desc" )
11
.firstResult()
12
.get();
13
14
15
// A-la-carte SQL restrictions
16
var userStream = userService
17
.newCriteria()
18
.sql( "userName = ? and firstName like ? and lastLogin >= ?", [
19
{ value : "joe", type : "string" },
20
{ value : "%joe%", type : "string" }
21
{ value : incomingDate, type : "timestamp" }
22
] )
23
.list( asStream = true );
24
25
26
var c = newCriteria();
27
var results = c.like("firstName","Lui%") // restriction
28
.maxResults( 50 ) // modifier
29
.order("balance","desc") // modifier
30
// AND restrictions
31
.and(
32
c.restrictions.between( "balance", 200, 300),
33
c.restrictions.eq("department", "development")
34
)
35
// Retrieve a list
36
.list();
Copied!
Tip: Every restriction can also be negated by using the not prefix before each method: notEq(), notIn(), notIsNull()

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.
1
var results = c.like("firstName","Lui%") // restriction
2
.firstResult( 25 ) // modifier
3
.maxResults( 50 ) // modifier
4
.order( "balance", "desc" ) // modifier
5
.timeout(
6
// AND restrictions
7
.and(
8
c.restrictions.between( "balance", 200, 300),
9
c.restrictions.eq("department", "development")
10
)
11
// Retrieve a list
12
.list();
Copied!

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
1
productService
2
.newCriteria()
3
.ge( "price", 30 )
4
.count();
5
6
userService
7
.newCriteria()
8
.isTrue( "isActive" )
9
.notIsNull( "lastLogin" )
10
.orderBy( "lastLogin desc" )
11
.firstResult()
12
.get();
Copied!

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, formatSql ) - 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
1
var sql = userService
2
.newCriteria()
3
.sql( "userName = ? and firstName like ? and lastLogin >= ?", [
4
{ value : "joe", type : "string" },
5
{ value : "%joe%", type : "string" }
6
{ value : incomingDate, type : "timestamp" }
7
] )
8
.getSqlLog();
9
10
// You can also use peek()
11
var results = userService
12
.newCriteria()
13
.sql( "userName = ? and firstName like ? and lastLogin >= ?", [
14
{ value : "joe", type : "string" },
15
{ value : "%joe%", type : "string" }
16
{ value : incomingDate, type : "timestamp" }
17
] )
18
.peek( function( c ){
19
log.debug( "sql log: #c.getSqlLog()#" );
20
});
21
.list();
Copied!
Last modified 7mo ago