# Introduction This module provides you with several enhancements when interacting with the ColdFusion ORM via Hibernate. It provides you with virtual service layers, active record patterns, criteria and detached criteria queries, entity compositions, populations and so much more to make your ORM life easier! ## Versioning The ColdBox ORM Module is maintained under the [Semantic Versioning](http://semver.org) guidelines as much as possible.Releases will be numbered with the following format: ``` .. ``` And constructed with the following guidelines: * Breaking backward compatibility bumps the major (and resets the minor and patch) * New additions without breaking backward compatibility bumps the minor (and resets the patch) * Bug fixes and misc changes bumps the patch ## License Apache 2 License: ## Important Links * Code: * Issues: * Documentation: ## Professional Open Source ![Ortus Solutions, Corp](/files/-LA-UqN1BRXynZ7RUVO7) The ColdBox ORM Module is a professional open source software backed by [Ortus Solutions, Corp](http://www.ortussolutions.com/services) offering services like: * Custom Development * Professional Support & Mentoring * Training * Server Tuning * Security Hardening * Code Reviews * [Much More](http://www.ortussolutions.com/services) ### HONOR GOES TO GOD ABOVE ALL Because of His grace, this project exists. If you don't like this, then don't read it, it's not for you. > "Therefore being justified by **faith**, we have peace with God through our Lord Jesus Christ: By whom also we have access by **faith** into this **grace** wherein we stand, and rejoice in hope of the glory of God." Romans 5:5 # Overview ![class BaseORMService](https://github.com/ColdBox/cbox-cborm/wiki/BaseORMService.jpg) The *BaseORMService* is a core model CFC of the module that will provide you with a tremendous gammut of API methods to interact with ColdFusion ORM Entities. ## Concept The idea behind this support class is to provide a very good base or parent service layer that can interact with ColdFusion ORM via hibernate and entities inspired by [Spring's Hibernate Template](http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/html/classic-spring.html#classic-spring-hibernate) support. This means that you don't need to create a service layer CFC in order to work with ORM entities. It provides tons of methods for query executions, paging, transactions, session metadata, caching and much more. You can either use the class on its own or create more concrete service layers by inheriting from this class. ## Usage In order to get started with the base ORM service you need to know how to get access to it. You can do this via WireBox injection DSL or by the model's ID. ### WireBox DSL The module also registers a new WireBox DSL called `entityservice` which can produce virtual or base orm entity services that you can use to inject into your own event handlers or models. * `entityservice` - Inject a global ORM service * `entityservice:{entityName}` - Inject a Virtual entity service according to `entityName` ### Injection You can also request a Base ORM Service via the registered WireBox ID which is exactly the same as the `entityService` DSL: ```javascript // Inject inject name="ORMService" inject="BaseORMService@cborm"; // Retrieve wireBox.getInstance( "BaseORMService@cborm" ); getModel( "BaseORMService@cborm" ); ``` ### Implementation Once you have access to the injected base ORM service, you can use it in all of its glory. > **Important** Please check out the latest API Docs for the latest methods and functionality. ```javascript component{ inject name="ORMService" inject="entityService"; function saveUser( event, rc, prc ){ // retrieve and populate a new user object var user = populateModel( ORMService.new( "User" ) ); // save the entity using hibernate transactions ORMService.save( user ); setNextEvent( "user.list" ); } function list( event, rc, prc ){ //get a listing of all users with paging prc.users = ORMService.list( entityName= "User", sortOrder = "fname", offset = event.getValue("startrow",1), max = 20 ); event.setView( "user/list" ); } } function index( event, rc, prc ){ prc.data = ORMService.findAll( "Permission" ); } ``` Once you have a reference to the base ORM service then you can use any of its methods to interact with ORM entities. The drawback about leveraging the base ORM model is that you cannot add custom functions to it or tell it to work on a specific entity for all operations. It is a very simple API, but if you need more control then we can start using other approaches shown below. ## Virtual Services We also have a [virtual service layer](https://github.com/ortus/coldbox-orm-module/tree/b683c32113283ffccf7872703d638825e475e2bd/virtualentityservice/Virtual%20Entity%20Service.md) that can be mapped to specific entities and create entity driven service layers virtually. Meaning you don't have to be passing any entity names to the API methods to save you precious typing time. ## Concrete Services This is where you create your own CFC that inherits from our Base ORM Service model and either add or override methods. You can read more about it in our [Concrete Services Section](/v1.x-1/base-orm-service/concrete-services) # Installation ## Installation Just drop into your **modules** folder or use the box-cli to install `box install cborm` ### WARNING Unfortunately, due to the way that ORM is loaded by ColdFusion, if you are using the ORM EventHandler or ActiveEntity or any ColdBox Proxies that require ORM, you must create an Application Mapping in the `Application.cfc` like this: ```javascript this.mappings[ "/cborm" ] = COLDBOX_APP_ROOT_PATH & "modules/cborm"; ``` ### SYSTEM REQUIREMENTS ![](https://img.shields.io/badge/Lucee-v4.5+-red.svg?style=for-the-badge) ![](https://img.shields.io/badge/ColdFusion-v9.02+-green.svg?style=for-the-badge) * Lucee 4.5+ * ColdFusion 9.02+ ## WireBox DSL The module also registers a new WireBox DSL called `entityservice` which can produce virtual or base orm entity services: * `entityservice` - Inject a global ORM service * `entityservice:{entityName}` - Inject a Virtual entity service according to `entityName` ## Settings Here are the module settings you can place in your `ColdBox.cfc` under an `orm` structure: ```javascript orm = { injection = { enabled = true, include = "", exclude = "" } } ``` ## Validation We have also migrated the `UniqueValidator` from the **validation** module into our ORM module. It is mapped into wirebox as `UniqueValidator@cborm` so you can use in your constraints like so: ```javascript { fieldName : { validator: "UniqueValidator@cborm" } } ``` # Change Log ## 1.2.1 * Fixed box.json version number ## 1.2.0 * BaseORMService.merge doesn't seem to merge entities back into session #10 * Variable scoping in SQLHelper.cfc bug #9 * Update build process to leverage Travis * Updated `cbvalidation` to v1.1.0 * Build cleanup * Replaced `StringBuffer` with `StringBuilder` for performance ## 1.1.0 * Updated cbvalidation dependency * Prevent conditionals from being stripped from property names * Updated build for api docs and commandbox usage for dependencies * ORM Unique validation not working ## 1.0.2 * updates to all dependencies * production ignore lists ## 1.0.1 * CF11Compat - arrayContainsNoCase() Is not a function * Lucee support ## 1.0.0 * Create first module version # Base ORM Service ![class BaseORMService](https://github.com/ColdBox/cbox-cborm/wiki/BaseORMService.jpg) The *BaseORMService* is a core model CFC of the module that will provide you with a tremendous gammut of API methods to interact with ColdFusion ORM Entities. ## Concept The idea behind this support class is to provide a very good base or parent service layer that can interact with ColdFusion ORM via hibernate and entities inspired by [Spring's Hibernate Template](http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/html/classic-spring.html#classic-spring-hibernate) support. This means that you don't need to create a service layer CFC in order to work with ORM entities. It provides tons of methods for query executions, paging, transactions, session metadata, caching and much more. You can either use the class on its own or create more concrete service layers by inheriting from this class. ## Usage In order to get started with the base ORM service you need to know how to get access to it. You can do this via WireBox injection DSL or by the model's ID. ### WireBox DSL The module also registers a new WireBox DSL called `entityservice` which can produce virtual or base orm entity services that you can use to inject into your own event handlers or models. * `entityservice` - Inject a global ORM service * `entityservice:{entityName}` - Inject a Virtual entity service according to `entityName` ### Injection You can also request a Base ORM Service via the registered WireBox ID which is exactly the same as the `entityService` DSL: ```javascript // Inject inject name="ORMService" inject="BaseORMService@cborm"; // Retrieve wireBox.getInstance( "BaseORMService@cborm" ); getModel( "BaseORMService@cborm" ); ``` ### Implementation Once you have access to the injected base ORM service, you can use it in all of its glory. > **Important** Please check out the latest API Docs for the latest methods and functionality. ```javascript component{ inject name="ORMService" inject="entityService"; function saveUser( event, rc, prc ){ // retrieve and populate a new user object var user = populateModel( ORMService.new( "User" ) ); // save the entity using hibernate transactions ORMService.save( user ); setNextEvent( "user.list" ); } function list( event, rc, prc ){ //get a listing of all users with paging prc.users = ORMService.list( entityName= "User", sortOrder = "fname", offset = event.getValue("startrow",1), max = 20 ); event.setView( "user/list" ); } } function index( event, rc, prc ){ prc.data = ORMService.findAll( "Permission" ); } ``` Once you have a reference to the base ORM service then you can use any of its methods to interact with ORM entities. The drawback about leveraging the base ORM model is that you cannot add custom functions to it or tell it to work on a specific entity for all operations. It is a very simple API, but if you need more control then we can start using other approaches shown below. ## Virtual Services We also have a [virtual service layer](/v1.x-1/virtual-entity-service) that can be mapped to specific entities and create entity driven service layers virtually. Meaning you don't have to be passing any entity names to the API methods to save you precious typing time. ## Concrete Services This is where you create your own CFC that inherits from our Base ORM Service model and either add or override methods. You can read more about it in our [Concrete Services Section](/v1.x-1/base-orm-service/concrete-services) # Concrete Services ![](https://github.com/ColdBox/cbox-cborm/wiki/ConcreteORMServices.jpg) Let's say you are using the virtual services and base ORM service but you find that they do not complete your requirements, or you need some custom methods or change functionality. Then you will be building concrete services that inherit from the base or virtual entity services. This is the very purpose of these support classes as most of the time you will have custom requirements and your own style of coding. Here is a custom `AuthorService` we created: ```javascript /** * Service to handle author operations. */ component extends="cborm.models.VirtualEntityService" accessors="true" singleton{ // User hashing type property name="hashType"; AuthorService function init(){ // init it via virtual service layer super.init(entityName="bbAuthor", useQueryCaching=true); setHashType( "SHA-256" ); return this; } function search(criteria){ var params = {criteria="%#arguments.criteria#%"}; var r = executeQuery(query="from bbAuthor where firstName like :criteria OR lastName like :criteria OR email like :criteria",params=params,asQuery=false); return r; } function saveAuthor(author,passwordChange=false){ // hash password if new author if( !arguments.author.isLoaded() OR arguments.passwordChange ){ arguments.author.setPassword( hash(arguments.author.getPassword(), getHashType()) ); } // save the author save( author ); } boolean function usernameFound(required username){ var args = {"username" = arguments.username}; return ( countWhere(argumentCollection=args) GT 0 ); } } ``` Then you can just inject your concrete service in your handlers, or other models like any other normal model object. ```javascript component{ // Concrete ORM service layer property name="authorService" inject="security.AuthorService"; // Aliased property name="authorService" inject="id:AuthorService"; function index( event, rc, prc ){ // Get all authors or search if( len(event.getValue( "searchAuthor", "" )) ){ prc.authors = authorService.search( rc.searchAuthor ); prc.authorCount = arrayLen( prc.authors ); } else { prc.authors = authorService.list( sortOrder="lastName desc", asQuery=false ); prc.authorCount = authorService.count(); } // View event.setView("authors/index"); } } ``` # Service Properties There are a few properties you can instantiate a base service with or set them afterwards that affect operation. Below you can see a nice chart for them: | Property | Type | Required | Default | Description | | ------------------ | ------- | -------- | ------------------------- | ---------------------------------------------------------------------------------------------------------------------------------- | | `queryCacheRegion` | string | false | `ORMService.defaultCache` | The name of the secondary cache region to use when doing queries via this base service | | `useQueryCaching` | boolean | false | false | To enable the caching of queries used by this base service | | `eventHandling` | boolean | false | true | Announce interception events on *new()* operations and *save()* operations: *ORMPostNew, ORMPreSave, ORMPostSave* | | `useTransactions` | boolean | false | true | Enables ColdFusion safe transactions around all operations that either save, delete or update ORM entities | | `defaultAsQuery` | boolean | false | true | The bit that determines the default return value for list(), createCriteriaQuery() and executeQuery() as query or array of objects | So if I was to base off my services on top of this gem, I can do this: ```javascript component extends="cborm.models.BaseORMService"{ public UserService function init(){ super.init( useQueryCaching=true, eventHandling=false ); return this; } } ``` # Automatic Java Types Most of the Hibernate extensions like criteria builders and even some dynamic finders and counters will have to rely on the underlying Java type in order to work. You do this in ColdFusion by using the `javaCast()` function available to you. So if you are using a primary key that is an `Integer` you might have to do the following in order to match your variable to the underlying Java type: ```javascript criteria.eq( "id", javaCast( "int", arguments.id ) ); ``` If you do not type it, then ColdFusion assumes it is a string and passes a string to Hibernate which will throw an exception as it is supposed to be an integer. ## Auto Types We have created two methods available to you in the base orm service, virtual service, criteria builders, active entity, etc to help you with these translations by automatically casting the values for you: ```javascript // Coverts a value to the correct javaType for the property passed in The method returns the value in the proper Java Type any convertValueToJavaType( any propertyName, any value ) // Coverts an ID, list of ID's, or array of ID's values to the proper java type The method returns a coverted array of ID's any convertIDValueToJavaType( any id ) ``` So instead of casting it manually you can just let us do the work: ```javascript criteria.eq( "id", criteria.convertIDValueToJavaType( arguments.id ) ); ``` # Dynamic Finders- Counters The ORM module supports the concept of dynamic finders and counters for ColdFusion ORM entities. A dynamic finder/counter looks like a real method but it is a virtual method that is intercepted by via `onMissingMethod`. This is a great way for you to do finders and counters using a programmatic and visual representation of what HQL to run. This feature works on the Base ORM Service, Virtual Entity Services and also Active Entity services. The most semantic and clear representations occur in the Virtual Entity Service and Active Entity as you don't have to pass an entity name around. # Method Signatures We have three types of dynamic finders and counters: * `findBy` : Find ONE entity according to method signature, if more than one record is found an exception is thrown * `findAllBy` : Find ALL entities according to method signature * `countBy` : Give you a count of entities according to method signature Let's say you have the following entity: ```javascript component persistent="true" name="User" extends="cborm.models.ActiveEntity"{ property name="id" column="user_id" fieldType="id" generator="uuid"; property name="lastName"; property name="userName"; property name="password"; property name="lastLogin" ormtype="date"; } ``` Then we could do the following: ```javascript user = entityNew( "User" ).findByLastName( "Majano" ); users = entityNew( "User" ).findAllByLastNameLike( "Ma%" ); users = entityNew( "User" ).findAllByLastLoginBetween( "01/01/2010", "01/01/2012" ); users = entityNew( "User" ).findAllByLastLoginBetweeninGreaterThan( "01/01/2010" ); users = entityNew( "User" ).findAllByLastLoginGreaterThanAndLastNameLike( "01/01/2010", "jo%" ); count = entityNew( "User" ).countByLastLoginGreaterThan( "01/01/2010" ); count = entityNew( "User" ).countByLastLoginGreaterThanAndLastNameLike( "01/01/2010", "jo%" ); ``` You can also use the virtual entity service instead of active entity. ```javascript // Get a virtual entity service via DI, there are many ways to get a virtual entity service // Look at virtual entity service docs for retrieval property name="userService" inject="entityservice:userService"; user = userService.findByLastName( "Majano" ); users = userService.findAllByLastNameLike( "Ma%" ); users = userService.findAllByLastLoginBetween( "01/01/2010", "01/01/2012" ); users = userService.findAllByLastLoginGreaterThan( "01/01/2010" ); users = userService.findAllByLastLoginGreaterThanAndLastNameLike( "01/01/2010", "jo%" ); count = userService.countByLastLoginGreaterThan( "01/01/2010" ); count = userService.countByLastLoginGreaterThanAndLastNameLike( "01/01/2010", "jo%" ); ``` If you just use a vanilla Base ORM Service, then the first argument must be the `entityName`: ```javascript // Get a virtual entity service via DI, there are many ways to get a base entity service // Look at base entity service docs for retrieval property name="userService" inject="entityservice"; user = userService.findByLastName( "User", "Majano" ); ``` # Method Expressions A method expression is made up of the prefixes: `findBy, findAllBy, countBy` followed by the expression that combines a query upon one or more properties: ```javascript User.findBy{property}[Conditional=equal][Operator]?{property}[Conditional][Operator] User.findAllBy{property}[Conditional=equal][Operator]?{property}[Conditional][Operator] User.countBy{property}[Conditional=equal][Operator]?{property}[Conditional][Operator] ``` If a conditional keyword is not passed, we assume you want equality. Remember that! > **IMPORTANT** The ? means that you can concatenate the same pattern over and over again. ## Conditionals The available conditionals in ColdBox are: * `LessThanEquals` - Less than or equal to passed value * `LessThan` - Less than to passed value * `GreaterThanEquals` - Greater than or equal to passed value * `GreaterThan` - Greater than to passed value * `Like` - Equivalent to the SQL like expression * `NotEqual` - Not equal to the passed value * `isNull` - The property must be null * `isNotNull` - The property must not be null * `NotBetween` - The property value must not be between two values * `Between` - The property value must be between two values * `NotInList` - The property value must not be in the passed in simple list or array * `inList` - The property value must be in the passed in simple list or array ## Operators The only valid operators are: * `And` * `Or` # Query Options If you pass a structure as the last argument to your dynamic finder/counter call, we will consider that by convention to be your query options. ```javascript user = entityNew( "User" ).findByLastName( "Majano", { ignoreCase=true, timeout=20 } ); users = entityNew( "User" ).findAllByLastNameLike( "Ma%", { ignoreCase=false, max=20, offset=15 } ); ``` The valid query options are: * `ignorecase` : Ignores the case of sort order when you set it to true. Use this option only when you specify the sortorder parameter. * `maxResults` : Specifies the maximum number of objects to be retrieved. * `offset` : Specifies the start index of the resultset from where it has to start the retrieval. * `cacheable` : Whether the result of this query is to be cached in the secondary cache. Default is false. * `cachename` : Name of the cache in secondary cache. * `timeout` : Specifies the timeout value (in seconds) for the query # clear Clear the session removes all the entities that are loaded or created in the session. This clears the first level cache and removes the objects that are not yet saved to the database. ## Returns * This function returns *void* ## Examples ```javascript ormService.clear(); ``` # count Return the count of instances in the DB for the given entity name. You can also pass an optional where statement that can filter the count. Ex: count('User','age > 40 AND name="joe"'). You can even use named or positional parameters with this method: Ex: count('User','age > ? AND name = ?',\[40,"joe"]) ## Returns * This function returns *numeric* ## Arguments | Key | Type | Required | Default | Description | | ---------- | ------ | -------- | ---------- | ------------------------------ | | entityName | string | Yes | --- | | | where | string | No | | | | params | any | No | strucnew() | Named or positional parameters | ## Examples ```javascript // Get the count of instances for all books ormService.count("Book"); // Get the count for users with age above 40 and named Bob ormService.count("User","age > 40 AND name='Bob'"); // Get the count for users with passed in positional parameters ormService.count("User","age > ? AND name=?",[40,'Bob']); // Get the count for users with passed in named parameters ormService.count("Post","title like :title and year = :year",{title="coldbox",year="2007"}); ``` # countWhere Returns the count by passing name value pairs as arguments to this function. One mandatory argument is to pass the 'entityName'. The rest of the arguments are used in the where class using AND notation and parameterized. Ex: countWhere(entityName="User",age="20"); ## Returns * This function returns *numeric* ## Arguments | Key | Type | Required | Default | | ---------- | ------ | -------- | ------- | | entityName | string | Yes | --- | # createService Create a virtual service for a specfic entity. Basically a new service layer that inherits from the BaseORMService object but no need to pass in entity names, they are bound to the entity name passed here. ## Returns * This function returns *VirtualEntityService* ## Arguments | Key | Type | Required | Default | | ---------------- | ------- | -------- | ------------------- | | entityName | string | Yes | --- | | useQueryCaching | boolean | No | Same as BaseService | | queryCacheRegion | string | No | Same as BaseService | ## Examples ```javascript userService = ormService.createService("User"); userService = ormService.createService("User",true); userService = ormService.createService("User",true,"MyFunkyUserCache"); // Remember you can use virtual entity services by autowiring them in via our DSL component{ property name="userService" inject="entityService:User"; property name="postService" inject="entityService:Post"; } ``` # criteriaCount Update: Please use our updated [Hibernate Criteria Builder](/v1.x-1/coldbox-criteria-builder) objects instead so you can have much more control, granularity and coolness! Get the record count using hibernate projections and criterion for specific queries ## Returns * This function returns *numeric* ## Arguments | Key | Type | Required | Default | Description | | ---------- | ----- | -------- | ------- | ----------------------------------------------------------- | | entityName | any | Yes | --- | The entity to run count projections on | | criteria | array | No | \[] | The array of hibernate criterion to use for the projections | ## Examples ```javascript // The following is handler code which has been wired with a virtual entity service property name="authorService" inject="entityService:Author"; function showAuthors(event){ var rc = event.getCollection(); // Get the hibernate restrictions proxy object var restrictions = authorService.getRestrictions(); // build criteria var criteria = []; // Apply a "greater than" constraint to the named property ArrayAppend(criteria, restrictions.ge("firstName","M")); // Get the criteria query first prc.example1 = authorService.criteriaQuery(criteria=criteria, offset=(prc.boundaries.STARTROW-1), max=getSetting("PagingMaxRows"), sortOrder="firstName ASC"); // get the total records found via projections prc.foundcount = authorService.criteriaCount(criteria=criteria); } ``` # criteriaQuery Update: Please use our updated [Hibernate Criteria Builder](/v1.x-1/coldbox-criteria-builder) objects instead so you can have much more control, granularity and coolness! Do a hibernate criteria based query with projections. You must pass an array of criterion objects by using the Hibernate Restrictions object that can be retrieved from this service using getRestrictions(). The Criteria interface allows to create and execute object-oriented queries. It is powerful alternative to the HQL but has own limitations. Criteria Query is used mostly in case of multi criteria search screens, where HQL is not very effective. ## Returns * This function returns *any* which can be an array or query depending on passed arguments ## Arguments | Key | Type | Required | Default | Description | | ---------- | ------- | -------- | ------- | -------------------------------------- | | entityName | any | Yes | --- | The entity name to run the criteria on | | criteria | array | No | \[] | Array of criterion | | sortOrder | string | No | | | | offset | numeric | No | 0 | | | max | numeric | No | 0 | | | timeout | numeric | No | 0 | | | Ignore | boolean | No | false | | | asQuery | boolean | No | true | | ## Examples ```javascript // Assuming the following code has a dependency on a virtual entity service: property name="authorService" inject="entityService:Author"; var restrictions = authorService.getRestrictions(); var criteria = []; ArrayAppend(criteria, Restrictions.eq("firstName","Emily")); ArrayAppend(criteria, Restrictions.eq("firstName","Paul")); ArrayAppend(criteria, Restrictions.eq("firstName","Amy")); example1 = authorService.criteriaQuery([Restrictions.disjunction(criteria)]); var disjunction = ArrayNew(1); ArrayAppend(disjunction, Restrictions.eq("firstName","Emily")); ArrayAppend(disjunction, Restrictions.eq("firstName","Paul")); ArrayAppend(disjunction, Restrictions.eq("firstName","Amy")); var criteria = ArrayNew(1); ArrayAppend(criteria, Restrictions.gt("id",JavaCast("int",7))); ArrayAppend(criteria, Restrictions.disjunction(disjunction)); example2 = authorService.criteriaQuery(criteria); var criteria = ArrayNew(1); ArrayAppend(criteria, Restrictions.eq("firstName","Michael")); prc.example1 = authorService.criteriaQuery(criteria); var criteria = ArrayNew(1); ArrayAppend(criteria, Restrictions.ne("firstName","Michael")); prc.example2 = authorService.criteriaQuery(criteria); var criteria = ArrayNew(1); ArrayAppend(criteria, Restrictions.in("firstName",["Ian","Emily","Paul"])); prc.example3a = authorService.criteriaQuery(criteria); var criteria = ArrayNew(1); ArrayAppend(criteria, Restrictions.in("id",JavaCast("java.lang.Integer[]",[2,5,9]))); prc.example3b = authorService.criteriaQuery(criteria); var criteria = ArrayNew(1); ArrayAppend(criteria, Restrictions.like("lastName","M%")); prc.example4 = authorService.criteriaQuery(criteria); var criteria = ArrayNew(1); ArrayAppend(criteria, Restrictions.ilike("lastName","s%")); prc.example5 = authorService.criteriaQuery(criteria); var criteria = ArrayNew(1); ArrayAppend(criteria, Restrictions.isEmpty("books")); prc.example6 = authorService.criteriaQuery(criteria); var criteria = ArrayNew(1); ArrayAppend(criteria, Restrictions.isNotEmpty("books")); prc.example7 = authorService.criteriaQuery(criteria); var criteria = ArrayNew(1); ArrayAppend(criteria, Restrictions.isNull("bio")); prc.example8 = authorService.criteriaQuery(criteria); var criteria = ArrayNew(1); ArrayAppend(criteria, Restrictions.isNotNull("bio")); prc.example9 = authorService.criteriaQuery(criteria); var criteria = ArrayNew(1); ArrayAppend(criteria, Restrictions.between("lastName","A","M")); prc.example10a = authorService.criteriaQuery(criteria); var criteria = ArrayNew(1); ArrayAppend(criteria, Restrictions.between("id",JavaCast("int",3),JavaCast("int",7))); prc.example10b = authorService.criteriaQuery(criteria); var criteria = ArrayNew(1); ArrayAppend(criteria, Restrictions.gt("id",JavaCast("int",8))); prc.example11 = authorService.criteriaQuery(criteria); var criteria = ArrayNew(1); ArrayAppend(criteria, Restrictions.ge("id",JavaCast("int",13))); prc.example12 = authorService.criteriaQuery(criteria); ``` # delete Delete an entity using safe transactions. The entity argument can be a single entity or an array of entities. You can optionally flush the session also after committing. ## Returns * This function returns *void* ## Arguments | Key | type | Required | Default | Description | | ------------- | ------- | -------- | ------------- | ----------------------- | | entity | any | Yes | --- | | | flush | boolean | No | false | | | transactional | boolean | No | From Property | Use Transactions or not | ## Examples ```javascript var post = ormService.get(1); ormService.delete( post ); // Delete a flush immediately ormService.delete( post, true ); ``` # deleteAll Deletes all the entity records found in the database in a transaction safe matter and returns the number of records removed ## Returns * This function returns *numeric* ## Arguments | Key | Type | Required | Default | Description | | ------------- | ------- | -------- | ------------- | ----------------------- | | entityName | string | Yes | --- | The entity to purge | | flush | boolean | No | false | | | transactional | boolean | No | From Property | Use transactions or not | ## Examples ```javascript ormService.deleteAll("Tags"); ``` # deleteByID Delete using an entity name and an incoming id, you can also flush the session if needed. The ID can be a single ID or an array of ID's to batch delete using hibernate DLM style deletes. The function also returns the number of records deleted. ## Returns * This function returns *numeric* ## Arguments | Key | Type | Required | Default | Description | | ------------- | ------- | -------- | ------------- | ------------------------------- | | entityName | string | Yes | --- | The name of the entity to delte | | id | any | Yes | --- | A single ID or array of IDs | | flush | boolean | No | false | | | transactional | boolean | No | From Property | Use transactions not | ## Examples ```javascript // just delete count = ormService.deleteByID("User",1); // delete and flush count = ormService.deleteByID("User",4,true); // Delete several records, or at least try count = ormService.deleteByID("User",[1,2,3,4]); ``` # deleteByQuery Delete by using an HQL query and iterating via the results, it is not performing a delete query but it actually is a select query that should retrieve objects to remove ## Returns * This function returns *void* ## Arguments \| Key | Type | Required | Default | description | | query | string | Yes | --- | | | params | any | No | --- | | | max | numeric | No | 0 | | | offfset | numeric | No | 0 | | | flush | boolean | No | false | | | transactional | boolean | No | From Property | Use transactions or not | | datasource | string | false | | The datasource to use or use the default datasource | ## Examples ```javascript // delete all blog posts ormService.deleteByQuery("from Post"); // delete query with positional parameters ormService.deleteByQuery("from Post as b where b.author=? and b.isActive = :active",['Luis Majano',false]); // Use query options var query = "from User as u where u.isActive=false order by u.creationDate desc"; // first 20 stale inactive users ormService.deleteByQuery(query=query,max=20); // 20 posts starting from my 15th entry ormService.deleteByQuery(query=query,max=20,offset=15,flush=true); // examples with named parameters ormService.deleteByQuery("from Post as p where p.author=:author", {author='Luis Majano'}) ``` # deleteWhere Deletes entities by using name value pairs as arguments to this function. One mandatory argument is to pass the 'entityName'. The rest of the arguments are used in the where class using AND notation and parameterized. Ex: deleteWhere(entityName="User",age="4",isActive=true); ## Returns * This function returns *numeric* | Key | Type | Required | Default | Description | | ------------- | ------- | -------- | ------------- | -------------------- | | entityName | string | Yes | --- | | | transactional | boolean | No | From Property | Use transactions not | ## Examples ```javascript ormService.deleteWhere(entityName="User", isActive=true, age=10); ormService.deleteWhere(entityName="Account", id="40"); ormService.deleteWhere(entityName="Book", isReleased=true, author="Luis Majano"); ``` # evict Evict an entity from session, the id can be a string or structure for the primary key You can also pass in a collection name to evict from the collection ## Returns * This function returns *void* ## Arguments | | Key | Type | Required | Default | | -- | -------------- | ------ | -------- | ------- | | | entityName | string | Yes | --- | | | collectionName | string | No | --- | | id | any | No | --- | | ## Examples ```javascript ormService.evict(entityName="Account",account.getID()); ormService.evict(entityName="Account"); ormService.evict(entityName="Account", collectionName="MyAccounts"); ``` # evictEntity Evict entity objects from session. The argument can be one persistence entity or an array of entities ## Returns * This function returns *void* ## Arguments | Key | Type | Required | Default | Description | | ------ | ---- | -------- | ------- | ----------- | | entity | any | Yes | --- | | ## Examples ```javascript // evict one entity ORMService.evictEntity( entity ); // evict an array of entities entities = [ user1, user2 ]; ORMService.evictEntity( entities ); ``` # evictQueries Evict all queries in the default cache or the cache region that is passed in. ## Returns * This function returns *void* ## Arguments | Key | Type | Required | Default | Description | | ---------- | ------ | -------- | ------- | --------------------- | | cacheName | string | No | --- | | | datasource | string | No | --- | The datasource to use | ## Examples ```javascript // evict queries that are in the default hibernate cache ormService.evictQueries(); // evict queries for this service ormService.evictQueries( ormService.getQueryCacheRegion() ); // evict queries for my artists ormService.evictQueries( "MyArtits" ); ``` # executeQuery Allows the execution of HQL queries using several nice arguments and returns either an array of entities or a query as specified by the asQuery argument. The params filtering can be using named or positional. ## Returns * This function returns *any* ## Arguments | Key | Type | Required | Default | Description | | ---------- | ------- | -------- | --------------------- | ------------------------------------ | | query | string | Yes | --- | | | params | any | No | \[runtime expression] | | | offset | numeric | No | 0 | | | max | numeric | No | 0 | | | timeout | numeric | No | 0 | | | asQuery | boolean | No | true | | | unique | boolean | No | false | Return a unique result | | datasource | string | No | --- | Use a specific or default datasource | ## Examples ```javascript // simple query ormService.executeQuery( "select distinct a.accountID from Account a" ); // using with list of parameters ormService.executeQuery( "select distinct e.employeeID from Employee e where e.department = ? and e.created > ?", ['IS','01/01/2010'] ); // same query but with paging ormService.executeQuery( "select distinct e.employeeID from Employee e where e.department = ? and e.created > ?", ['IS','01/01/2010'],1,30); // same query but with named params and paging ormService.executeQuery( "select distinct e.employeeID from Employee e where e.department = :dep and e.created > :created", {dep='Accounting',created='01/01/2010'],10,20); // GET FUNKY!! ``` # exists Checks if the given entityName and id exists in the database ## Returns * This function returns *boolean* ## Arguments | Key | Type | Required | Default | Description | | ---------- | ---- | -------- | ------- | ----------- | | entityName | any | Yes | --- | | | id | any | Yes | --- | | ## Examples ```javascript if( ormService.exists("Account",123) ){ // do something } ``` # findAll Find all the entities for the specified query, named or positional arguments or by an example entity ## Returns * This function returns *array* ## Arguments | Key | Type | Required | Default | Description | | ---------- | ------- | -------- | --------------------- | ----------- | | query | string | No | --- | | | params | any | No | \[runtime expression] | | | offset | numeric | No | 0 | | | max | numeric | No | 0 | | | timeout | numeric | No | 0 | | | ignoreCase | boolean | No | false | | | example | any | No | --- | | ## Examples ```javascript // find all blog posts ormService.findAll("Post"); // with a positional parameters ormService.findAll("from Post as p where p.author=?",['Luis Majano']); // 10 posts from Luis Majano staring from 5th post ordered by release date ormService.findAll("from Post as p where p.author=? order by p.releaseDate",['Luis majano'],offset=5,max=10); // Using paging params var query = "from Post as p where p.author='Luis Majano' order by p.releaseDate" // first 20 posts ormService.findAll(query=query,max=20) // 20 posts starting from my 15th entry ormService.findAll(query=query,max=20,offset=15); // examples with named parameters ormService.findAll("from Post as p where p.author=:author", {author='Luis Majano'}) ormService.findAll("from Post as p where p.author=:author", {author='Luis Majano'}, max=20, offset=5); // query by example user = ormService.new(entityName="User",firstName="Luis"); ormService.findAll( example=user ); ``` # findAllWhere Find all entities according to criteria structure. Ex: findAllWhere(entityName="Category", {category="Training"}), findAllWhere(entityName="Users", {age=40,retired=true}); ## Returns * This function returns *array* ## Arguments | Key | Type | Required | Default | Description | | ---------- | ------ | -------- | ------- | ------------------------------------ | | entityName | string | Yes | --- | | | criteria | struct | Yes | --- | A structure of criteria to filter on | | sortOrder | string | false | --- | The sort ordering | ## Examples ```javascript posts = ormService.findAllWhere(entityName="Post", criteria={author="Luis Majano"}); users = ormService.findAllWhere(entityName="User", criteria={isActive=true}); artists = ormService.findAllWhere(entityName="Artist", criteria={isActive=true, artist="Monet"}); ``` # findByExample Find all/single entities by example ## Returns * This function returns *array* ## Arguments | Key | Type | Required | Default | Description | | ------- | ------- | -------- | ------- | -------------------------------------- | | example | any | Yes | --- | The entity sample | | unique | boolean | false | false | Return an array of sample data or none | ## Examples ```javascript currentUser = ormService.findByExample( session.user, true ); ``` # findit Finds and returns the first result for the given query or null if no entity was found. You can either use the query and params combination or send in an example entity to find. ## Returns * This function returns *any* ## Arguments | Key | Type | Required | Default | Description | | ------- | ------ | -------- | --------------------- | ----------- | | query | string | No | --- | | | params | any | No | \[runtime expression] | | | example | any | No | --- | | ## Examples ```javascript // My First Post ormService.findIt("from Post as p where p.author='Luis Majano'"); // With positional parameters ormService.findIt("from Post as p where p.author=?", ["Luis Majano"]); // with a named parameter (since 0.5) ormService.findIt("from Post as p where p.author=:author and p.isActive=:active", { author="Luis Majano",active=true} ); // By Example book = ormService.new(entityName="Book", author="Luis Majano"); ormService.findIt( example=book ); ``` # findWhere Find one entity (or null if not found) according to a criteria structure ex: findWhere(entityName="Category", {category="Training"}), findWhere(entityName="Users",{age=40,retired=false}); ## Returns * This function returns *any* ## Arguments | Key | Type | Required | Default | Description | | ---------- | ------ | -------- | ------- | ------------------------------------ | | entityName | string | Yes | --- | | | criteria | struct | Yes | --- | A structure of criteria to filter on | ## Examples ```javascript // Find a category according to the named value pairs I pass into this method var category = ormService.findWhere(entityName="Category", criteria={isActive=true, label="Training"}); var user = ormService.findWhere(entityName="User", criteria={isActive=true, username=rc.username,password=rc.password}); ``` # get Get an entity using a primary key, if the id is not found this method returns null. You can also pass an id = 0 and the service will return to you a new entity. ## Returns * This function returns *any* ## Arguments | Key | Type | Required | Default | Description | | ---------- | ------- | -------- | ------- | -------------------------------------------------------------------- | | entityName | string | Yes | --- | | | id | any | Yes | --- | | | returnNew | boolean | false | true | If id is 0 or empty and this is true, then a new entity is returned. | ## Examples ```javascript var account = ormService.get("Account",1); var account = ormService.get("Account",4); var newAccount = ormService.get("Account",0); ``` # getAll Retrieve all the instances from the passed in entity name using the id argument if specified. The id can be a list of IDs or an array of IDs or none to retrieve all. If the id is not found or returns null the array position will have an empty string in it in the specified order ## Returns * This function returns *array* of entities found ## Arguments | Key | Type | Required | Default | Description | | ---------- | ------ | -------- | ------- | ----------------------------- | | entityName | string | Yes | --- | | | id | any | No | --- | | | sortOrder | string | false | --- | The sort orering of the array | ## Examples ```javascript // Get all user entities users = ORMService.getAll(entityName="User", sortOrder="email desc"); // Get all the following users by id's users = ORMService.getAll("User","1,2,3"); // Get all the following users by id's as array users = ORMService.getAll("User",[1,2,3,4,5]); ``` # getEntityGivenName Returns the entity name from a given entity object via session lookup or if new object via metadata lookup ## Returns * This function returns *string* ## Arguments | Key | Type | Required | Default | Description | | ---------- | ---- | -------- | ------- | ----------- | | entityName | any | Yes | --- | | ## Examples ```javascript var name = ORMService.getEntityGivenName( entity ); ``` # getKey Returns the key (id field) of a given entity, either simple or composite keys. If the key is a simple pk then it will return a string, if it is a composite key then it returns an array ## Returns * This function returns *any* ## Arguments | Key | Type | Required | Default | Description | | ---------- | ------ | -------- | ------- | ----------- | | entityName | string | Yes | --- | | ## Examples ```javascript var pkField = ormService.getKey( "User" ); ``` # getPropertyNames Returns the persisted Property Names of the entity in array format ## Returns * This function returns *array* ## Arguments | Key | Type | Required | Default | Description | | ---------- | ------ | -------- | ------- | ----------- | | entityName | string | Yes | --- | | ## Examples ```javascript var properties = ormService.getPropertyNames("User"); ``` # getRestrictions Get our hibernate org.hibernate.criterion.Restrictions proxy object ## Returns * This function returns *coldbox.system.orm.hibernate.criterion.Restrictions* ## Examples ```javascript // Restrictions used with criteria queries var r = ormService.getRestrictions(); var users = ormService.newCriteria("User") .or( r.eq("lastName","majano"), r.gt("createDate", now()) ) .list(); ``` # getSessionStatistics Information about the first-level (session) cache for the current session ## Returns * This function returns *struct* ## Arguments | Key | Type | Required | Default | Description | | ---------- | ------ | -------- | ------- | -------------------------------------- | | datasource | string | false | --- | The default or specific datasource use | ## Examples ```javascript // Let's get the session statistics stats = ormService.getSessionStatistics; // Lets output it collection count: #stats.collectionCount#
collection keys: #stats.collectionKeys#
entity count: #stats.entityCount#
entity keys: #stats.entityKeys# ``` # getTableName Returns the table name of the passed in entity ## Returns * This function returns *string* ## Arguments | Key | Type | Required | Default | Description | | ---------- | ------ | -------- | ------- | ----------- | | entityName | string | Yes | --- | | ## Examples ```javascript var persistedTable = ormService.getTableName( "Category" ); ``` # isSessionDirty Checks if the session contains dirty objects that are awaiting persistence ## Returns * This function returns *boolean* ## Arguments | Key | Type | Required | Default | Description | | ---------- | ------ | -------- | ------- | ----------------------------------------- | | datasource | string | false | --- | The default or specific datasource to use | ## Examples ```javascript // Check if by this point we have a dirty session, then flush it if( ormService.isSessionDirty() ){ ORMFlush(); } ``` # list List all of the instances of the passed in entity class name. You can pass in several optional arguments like a struct of filtering criteria, a sortOrder string, offset, max, ignorecase, and timeout. Caching for the list is based on the *useQueryCaching* class property and the *cachename* property is based on the queryCacheRegion class property. ## Returns * This function returns *array* ## Arguments | Key | Type | Required | Default | Description | | ---------- | ------- | -------- | --------------------- | ----------- | | entityName | string | Yes | --- | | | criteria | struct | No | \[runtime expression] | | | sortOrder | string | No | | | | offset | numeric | No | 0 | | | max | numeric | No | 0 | | | timeout | numeric | No | 0 | | | ignoreCase | boolean | No | false | | | asQuery | boolean | No | true | | ## Examples ```javascript users = ormService.list(entityName="User",max=20,offset=10,asQuery=false); users = ormService.list(entityName="Art",timeout=10); users = ormService.list("User",{isActive=false},"lastName, firstName"); users = ormService.list("Comment",{postID=rc.postID},"createdDate desc"); ``` # merge Merge an entity or array of entities back into the session ## Returns * Same entity if one passed, array if an array of entities passed. ## Arguments | Key | Type | Required | Default | Description | | ------ | ---- | -------- | ------- | ----------- | | entity | any | Yes | --- | | ## Examples ```javascript // merge a single entity back ormService.merge( userEntity ); // merge an array of entities collection = [entity1,entity2,entity3]; ormService.merge( collection ); ``` # new Get a new entity object by entity name. You can also pass in a structure called properties that will be used to populate the new entity with or you can use optional named parameters to call setters within the new entity to have shorthand population. ## Returns * This function returns *any* ## Arguments | Key | Type | Required | Default | Description | | ---------- | ------ | -------- | ------- | --------------------------------------------------------------- | | entityName | any | true | --- | | | properties | struct | false | {} | A structure of name-value pairs to populate the new entity with | ## Examples ```javascript // return empty post entity var post = ormService.new("Post"); var user = ormService.new(entityName="User",properties={firstName="Luis", lastName="Majano", age="32", awesome=true}); var user = ormService.new("User",{fname="Luis",lname="Majano",cool=false,awesome=true}); ``` # newCriteria Get a brand new criteria builder object to do criteria queries with (See [ORM:CriteriaBuilder](/v1.x-1/coldbox-criteria-builder/getting-started)) ## Returns * This function returns *This function returns coldbox.system.orm.hibernate.CriteriaBuilder* ## Arguments | Key | Type | Required | Default | Description | | ---------------- | ------- | -------- | ---------------------- | -------------------------------------------------------------------------- | | entityName | string | true | --- | The entity name to bind the criteria query to | | useQueryCaching | boolean | false | false | Do automatic query caching for queries | | queryCacheRegion | string | false | criterias.{entityName} | The queryCacheRegion name property for all queries in this criteria object | ## Examples ```javascript var users = ORMService.newCriteria(entityName="User",useQueryCaching=true) .gt("age", javaCast("int", 30) ) .isTrue("isActive") .list(max=30,offset=10,sortOrder="lname"); ``` # populate Populate an entity with a structure of name-value pairs. Make sure the names of the properties match the keys in the structure. ## Returns * This function returns *void* ## Arguments | Key | Type | Required | Default | Description | | -------------------- | ------- | -------- | ------- | ---------------------------------------------------------------------------------------------------------------- | | target | any | Yes | --- | The entity to populate | | memento | struct | Yes | --- | The structure of name-value pairs to try to populate the entity with | | scope | string | No | | Use scope injection instead of setter injection, no need of setters, just tell us what scope to inject to | | trustedSetter | Boolean | No | false | Do not check if the setter exists, just call it, great for usage with onMissingMethod() and virtual properties | | include | string | No | | A list of keys to ONLY include in the population | | exclude | string | No | | A list of keys to exclude from the population | | nullEmptyInclude | string | No | | A list of keys to NULL when empty, specifically for ORM population. You can also specify "\*" for all fields | | nullEmptyExclude | string | No | | A list of keys to NOT NULL when empty, specifically for ORM population. You can also specify "\*" for all fields | | composeRelationships | boolean | No | true | When true, will automtically attempt to compose relationships from memento | > **INFO** With composeRelationships=true, you can populate one-to-many, many-to-one, many-to-many, and one-to-one relationships from property values in the memento. For 'many-to-one' and 'one-to-one' relationships, the value of the property in the memento should be a single value of the primary key of the target entity to be loaded. For 'one-to-many' and 'many-to-many' relationships, the value of the property in the memento should a comma-delimited list or array of the primary keys of the target entities to be loaded. ## Examples ```javascript var user = ormService.populate( ormService.new("User"), data ); // populate with includes only var user = ormService.populate( ormService.new("User"), data, "fname,lname,email" ); //populate with excludes var user = ormService.populate(target=ormService.new("User"),memento=data,exclude="id,setup,total" ); // populate with null values when value is empty string var user = ormService.populate(target=ormService.new("User"),memento=data,nullEmptyInclude="lastName,dateOfBirth" ); // populate many-to-one relationship var data = { firstName = "Luis", role = 1 // "role" is the name of the many-to-one relational property, and one is the key value }; var user = ormService.populate( target=ormService.new("User"), memento=data, composeRelationships=true ); // the role relationship will be composed, and the value will be set to the appropriate instance of the Role model // populate one-to-many relationship var data = { firstName = "Luis", favColors = "1,2,3" ( or [1,2,3] ) // favColors is the name of the one-to-many relational property, and 1, 2 and 3 are key values of favColor models }; var user = ormService.populate( target=ormService.new("User"), memento=data, composeRelationships=true ); // the favColors property will be set to an array of favColor entities // only compose some relationships var data = { firstName = "Luis", role = 1, favColors = [ 1, 3, 19 ] }; var user = ormService.populate( target=ormService.new("User"), memento=data, composeRelationships=true, exclude="favColors" ); // in this example, "role" will be composed, but "favColors" will be excluded ``` # populateFromJSON Populate an entity with a JSON structure packet. Make sure the names of the properties match the keys in the structure. ## Returns * This function returns *void* ## Arguments | Key | Type | Required | Default | Description | | ------------- | ------- | -------- | ------- | -------------------------------------------------------------------------------------------------------------- | | target | any | Yes | --- | The entity to populate | | JSONString | string | Yes | --- | The JSON packet to use for population | | scope | string | No | | Use scope injection instead of setter injection, no need of setters, just tell us what scope to inject to | | trustedSetter | Boolean | No | false | Do not check if the setter exists, just call it, great for usage with onMissingMethod() and virtual properties | | include | string | No | | A list of keys to ONLY include in the population | | exclude | string | No | | A list of keys to exclude from the population | ## Examples ```javascript var user = ormService.populateFromJSON( ormService.new("User"), jsonString ); ``` # populateFromXML Populate an entity with an XML packet. Make sure the names of the elements match the keys in the structure. ## Returns * This function returns *void* ## Arguments | Key | Type | Required | Default | Description | | ------------- | ------- | -------- | ------- | -------------------------------------------------------------------------------------------------------------- | | target | any | Yes | --- | The entity to populate | | xml | any | Yes | --- | The xml string or xml document object to populate with | | root | string | false | | The xml root node to start the population with, by default it uses the XMLRoot. | | scope | string | No | | Use scope injection instead of setter injection, no need of setters, just tell us what scope to inject to | | trustedSetter | Boolean | No | false | Do not check if the setter exists, just call it, great for usage with onMissingMethod() and virtual properties | | include | string | No | | A list of keys to ONLY include in the population | | exclude | string | No | | A list of keys to exclude from the population | ## Examples ```javascript var user = ormService.populateFromXML( ormService.new("User"), xml, "User"); ``` # populateFromQuery Populate an entity with a query object. Make sure the names of the columns match the keys in the object. ## Returns *void* * This function returns ## Arguments | Key | Type | Required | Default | Description | | ------------- | ------- | -------- | ------- | -------------------------------------------------------------------------------------------------------------- | | target | any | Yes | --- | The entity to populate | | qry | query | Yes | --- | The query to populate with | | rowNumber | numeric | false | 1 | The row to use to populate with. | | scope | string | No | --- | Use scope injection instead of setter injection, no need of setters, just tell us what scope to inject to | | trustedSetter | Boolean | No | false | Do not check if the setter exists, just call it, great for usage with onMissingMethod() and virtual properties | | include | string | No | --- | A list of columns to ONLY include in the population | | exclude | string | No | --- | A list of columns to exclude from the population | ## Examples ```javascript var user = ormService.populateFromQuery( ormService.new("User"), list("User",{id=4}) ); ``` # refresh Refresh the state of an entity or array of entities from the database ## Returns * This function returns *void* ## Arguments | Key | Type | Required | Default | Description | | ------ | ---- | -------- | ------- | ----------- | | entity | any | Yes | --- | | ## Examples ```javascript var user = storage.getVar("UserSession"); ormService.refresh( user ); var users = [user1,user2,user3]; ormService.refresh( users ); ``` # save Save an entity using hibernate transactions or not. You can optionally flush the session also. ## Returns * This function returns *void* ## Arguments | Key | Type | Required | Default | Description | | ------------- | ------- | -------- | ------- | ---------------------------------- | | entity | any | Yes | --- | | | forceInsert | boolean | No | false | | | flush | boolean | No | false | | | transactional | boolean | No | true | Use ColdFusion transactions or not | ## Examples ```javascript var user = ormService.new("User"); populateModel(user); ormService.save(user); // Save with immediate flush var user = ormService.new(entityName="User", lastName="Majano"); ormService.save(entity=user, flush=true); ``` # saveAll Saves an array of passed entities in specified order and transaction safe ## Returns * This function returns *void* ## Arguments | Key | Type | Required | Default | Description | | ------------- | ------- | -------- | ------- | ---------------------------------- | | entities | array | Yes | --- | The array of entities to persist | | forceInsert | boolean | No | false | | | flush | boolean | No | false | | | transactional | boolean | No | true | Use ColdFusion transactions or not | ## Examples ```javascript var user = ormService.new("User"); populateModel(user); var user2 = ormService.new("User"); populateModel(user); ormService.saveAll( [user1,user2] ); ``` # sessionContains Checks if the current session contains the passed in entity ## Returns * This function returns *boolean* ## Arguments | Key | Type | Required | Default | Description | | ------ | ---- | -------- | ------- | ----------- | | entity | any | Yes | --- | | ## Examples ```javascript function checkSomething( any User ){ // check if User is already in session if( NOT ormService.sessionContains( arguments.User ) ){ // Not in hibernate session, so merge it in. ormService.merge( arguments.User ); } } ``` # Active Entity This class allows you to simulate the Active Record pattern in your ORM entities by inheriting from our Active Entity class. This will make your ORM entities get all the functionality of our Virtual and Base ORM services so you can do finds, searches, listings, counts, execute queries, transaction safe deletes, saves, updates, criteria building, and even [validation](/v1.x-1/active-entity/validation) right from within your ORM Entity. The idea behind the Active Entity is to allow you to have a very nice abstraction to all the ColdFusion ORM capabilities (hibernate) and all of our ORM extensions like our ColdBox Criteria Builder. With Active Entity you will be able to: * Find entities using a variety of filters and conditions * ORM paging * Specify order, searches, criterias and grouping of orm listing and searches * Use DLM style hibernate operations for multiple entity deletion, saving, and updating * Check for existence of records * Check for counts using criterias * Use our extensive ColdBox Criteria Builder to build Object Oriented HQL queries * Validate your entity using our awesome [validation engine](https://github.com/ColdBox/cbox-validation/wiki) # Enabling Active Entity To work with Active Entity you must do a few things to tell ColdBox you want to use Active Entity and enable validation and injections: 1. Enable the ORM in your Application.cfc with event handling turned on, manage session and flush at request end as false. 2. Enable the orm configuration structure in your [ConfigurationCFC](http://wiki.coldbox.org/wiki/ConfigurationCFC.cfm) to allow for ColdBox to do entity injections # 1.Application.cfc The following are vanilla configurations for enabling the ORM in ColdFusion: ``` // Enable ORM this.ormEnabled = true; // ORM Datasource this.datasource = "contacts"; // ORM configuration settings this.ormSettings = { // Location of your entities, default is your convention model folder cfclocation = ["models"], // Choose if you want ORM to create the database for you or not? dbcreate = "none", // Log SQL or not logSQL = true, // Don't flush at end of requests, let Active Entity manage it for you flushAtRequestEnd = false, // Don't manage session, let Active Entity manage it for you autoManageSession = false, // Active ORM events eventHandling = true, // Use the ColdBox WireBox Handler for events eventHandler = "cborm.models.EventHandler" }; ``` # 2.ORM Config Settings Open your config/ColdBox.cfc and either uncomment or add the following settings: ```javascript // ORM services, injection, etc orm = { // entity injection injection = { // enable it enabled = true, // the include list for injection include = "", // the exclude list for injection exclude = "" } }; ``` # Building Please remember that you can use ANY method found in the Base ORM Service except that you will not pass an argument of entityName anymore as you have now binded to an ORM entity. You can also visit our API for all the methods. ``` component persistent="true" table="your_table" extends="cborm.models.ActiveEntity"{ } ``` The inheritance is what will make your Entity come alive with tons of useful methods! # Constructor Properties If you override the constructor in your ORM entity, then make sure that you call the super.init() with the optional arguments below: | | | | | | | ---------------- | ------- | ----- | -------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------- | | queryCacheRegion | string | false | *#entityName#.activeEntityCache* | The name of the secondary cache region to use when doing queries via this class | | useQueryCaching | boolean | false | false | The bit that tells the class to enable query caching, disabled by default | | useTransactions | boolean | false | true | The bit that enables automatic hibernate transactions on all save, saveAll, update, delete methods | | eventHandling | boolean | false | true | The bit that enables event handling via the ORM Event handler such as interceptions when new entities get created, saved, enabled by default. | | defaultAsQuery | boolean | false | true | The bit that determines the default return value for list(), createCriteriaQuery() and executeQuery() as query or array | ```javascript component persistent="true" table="your_table" extends="coldbox.system.orm.hibernate.ActiveEntity"{ function init(){ setCreatedDate( now() ); super.init(useQueryCaching=true); return this; } } ``` # Usage You just use entityNew() like you would with any ORM entity and then just call our cool methods: ```javascript // get a new User entity user = entityNew("User"); // Count all the users in the database usersFound = user.count(); // create a new user entity and pre-populate it with data coolUser = user.new( {firstName="Luis",lastName="Majano",Awesome=true} ); // save the user object coolUser.save(); // Retrieve a user with an ID of 123 luis = user.get("123"); ``` # Validation Our active entity object will also give you access to our validation engine by giving your ORM entities the following functions: ```javascript /** * Validate the ActiveEntity with the coded constraints -> this.constraints, or passed in shared or implicit constraints * The entity must have been populated with data before the validation * @fields.hint One or more fields to validate on, by default it validates all fields in the constraints. This can be a simple list or an array. * @constraints.hint An optional shared constraints name or an actual structure of constraints to validate on. * @locale.hint An optional locale to use for i18n messages * @excludeFields.hint An optional list of fields to exclude from the validation. */ boolean function isValid(string fields="*", any constraints="", string locale="", string excludeFields=""); /** * Get the validation results object. This will be an empty validation object if isValid() has not being called yet. */ coldbox.system.validation.result.IValidationResult function getValidationResults(); ``` > **Important** In order for validation to work, WireBox ORM entity injection must be enabled first! > > * ColdBox ORM Entity Injection > * WireBox Standalone ORM Entity Injection This makes it really easy for you to validate your ORM entities. Sample: ```javascript import coldbox.system.orm.hibernate.*; component persistent="true" extends="ActiveEntity"{ // Properties property name="firstName"; property name="lastName"; property name="email"; property name="username"; property name="password"; // Validation Constraints this.constraints = { "firstName" = {required=true}, "lastName" = {required=true}, "email" = {required=true,type="email"}, "username" = {required=true, size="5..10"}, "password" = {required=true, size="5..10"} }; } ``` Handlers Sample: ```javascript component{ property name="Messagebox" inject="id:messagebox@cbmessagebox"; function index(event,rc,prc){ prc.users = entityNew("User").list(sortOrder="lastName asc"); event.setView("users/list"); } function save(event,rc,prc){ event.paramValue("id",""); var user = populateModel( entityNew("User").get( rc.id ) ); if( user.isValid() { user.save(); flash.put("notice","User Saved!"); setNextEvent("users.index"); } else{ Messagebox.error(messageArray=user.getValidationResults().getAllErrors()); editor(event,rc,prc); } } function editor(event,rc,prc){ event.paramValue("id",""); prc.user = entityNew("User").get( rc.id ); event.setView("users/editor"); } function delete(event,rc,prc){ event.paramValue("id",""); entityNew("User").deleteById( rc.id ); flash.put("notice","User Removed!"); setNextEvent("users.index"); } } ``` Please refer to the ORM:BaseORMService for all the cool methods and functionality you can use with Active Entity. Like always, refer to the latest CFC Docs for methods and arguments. # Virtual Entity Service The virtual entity service is another support class that can help you create virtual service layers that are bounded to a specific ORM entity for convenience. This class inherits from our Base ORM Service and allows you to do everything the base class provides, except you do not need to specify to which entityName you are working with. You can also use this class as a base class and template out its methods to more concrete usages. The idea behind this virtual entity service layer is to allow you to have a very nice abstraction to all the CF ORM capabilities (hibernate) and promote best practices. ![](https://github.com/ColdBox/cbox-cborm/wiki/VirtualEntityService.jpg) # Virtual Entity Service Usage Please remember that you can use ANY method found in the [Base ORM Service](/v1.x-1/base-orm-service) except that you will not pass an argument of entityName anymore as you have now bounded to that specific entity. # Service Properties There are a few properties you can instantiate a base service with or set them afterwards that affect operation. Below you can see a nice chart for them: | Property | Type | Required | Default | Description | | ------------------ | ------- | -------- | ------------------------- | ---------------------------------------------------------------------------------------------------------------------------------- | | `queryCacheRegion` | string | false | `ORMService.defaultCache` | The name of the secondary cache region to use when doing queries via this base service | | `useQueryCaching` | boolean | false | false | To enable the caching of queries used by this base service | | `eventHandling` | boolean | false | true | Announce interception events on *new()* operations and *save()* operations: *ORMPostNew, ORMPreSave, ORMPostSave* | | `useTransactions` | boolean | false | true | Enables ColdFusion safe transactions around all operations that either save, delete or update ORM entities | | `defaultAsQuery` | boolean | false | true | The bit that determines the default return value for list(), createCriteriaQuery() and executeQuery() as query or array of objects | So if I was to base off my services on top of this gem, I can do this: ```javascript component extends="cborm.models.BaseORMService"{ public UserService function init(){ super.init( useQueryCaching=true, eventHandling=false ); return this; } } ``` # Virtual ORM Services The WireBox injection DSL has an injection namespace called entityService that can be used to wire in a Virtual Entity Service. You will use this DSL in conjunction with the name of the entity to manage. | Inject Content | Description | | ---------------------- | ------------------------------------------------------------------------------------------------------------- | | entityService:{entity} | Inject a VirtualEntityService object for usage as a service layer based off the name of the entity passed in. | ```javascript component{ // Virtual service layer based on the User entity property name="userService" inject="entityService:User"; } ``` Once a virtual entity service is bounded it will be injected wherever you define it. Then just use it! # Direct Instantiation You can also use the virtual entity service by directly instantiating the coldbox.system.orm.hibernate.VirtualEntityService, configuring it and using it: ```javascript import coldbox.system.orm.hibernate.* userService = new VirtualEntityService(entityName="User"); userService = new VirtualEntityService(entityName="User",useQueryCaching=true); usersFound = userService.count(); user = userService.new({firstName="Luis",lastName="Majano",Awesome=true}); userService.save( user ); user = userService.get("123"); var users = userService.newCriteria() .like("lastName", "%maj%") .isTrue("isActive") .list(sortOrder="lastName"); ``` # Concrete Virtual Services ![](https://raw.githubusercontent.com/wiki/coldbox-modules/cbox-cborm/ConcreteORMServices.jpg) Let's say you are using the virtual service but you find that they do not complete your requirements, or you need some custom methods or change functionality. Then you will be building concrete services that inherit from the virtual entity service. This is the very purpose of these support classes as most of the time you will have custom requirements and your own style of coding. Below is a sample templated service layer: ```javascript component extends="cborm.models.VirtualEntityService" singleton{ // DI property name="mailService" inject="coldbox:plugin:MailService"; property name="renderer" inject="coldbox:plugin:Renderer"; property name="settingService" inject="id:settingService@cb"; property name="CBHelper" inject="id:CBHelper@cb"; property name="log" inject="logbox:logger:{this}"; /** * Constructor */ public CommentService function init(){ super.init(entityName="cbComment",useQueryCaching="true"); return this; } /** * Get the total number of approved comments in the system */ numeric function getApprovedCommentCount(){ var args = { "isApproved" = true }; return countWhere(argumentCollection=args); } /** * Get the total number of unapproved comments in the system */ numeric function getUnApprovedCommentCount(){ var args = { "isApproved" = false }; return countWhere(argumentCollection=args); } /** * Comment listing for UI of approved comments, returns struct of results=[comments,count] * @contentID.hint The content ID to filter on * @contentType.hint The content type discriminator to filter on * @max.hint The maximum number of records to return, 0 means all * @offset.hint The offset in the paging, 0 means 0 * @sortOrder.hint Sort the comments asc or desc, by default it is desc */ function findApprovedComments(contentID,contentType,max=0,offset=0,sortOrder="desc"){ var results = {}; var c = newCriteria(); // only approved comments c.isTrue("isApproved"); // By Content? if( structKeyExists(arguments,"contentID") AND len(arguments.contentID) ){ c.eq("relatedContent.contentID",javaCast("int", arguments.contentID)); } // By Content Type Discriminator: class is a special hibernate deal if( structKeyExists(arguments,"contentType") AND len(arguments.contentType) ){ c.createCriteria("relatedContent") .isEq("class", arguments.contentType); } // run criteria query and projections count results.count = c.count(); results.comments = c.list(offset=arguments.offset,max=arguments.max,sortOrder="createdDate #arguments.sortOrder#",asQuery=false); return results; } } ``` # Methods Usages You can refer to the [ORM:BaseORMService](/v1.x-1/base-orm-service) methods without the entityName usage or refer to our [API Docs.](http://apidocs.coldbox.org/cbQuickDocs/search/index) # Related ORM Services # ColdBox ORM Event Handler By using the ColdBox ORM event handler as your base class for your application's event handler you will inherently get the ability to talk to your ColdBox application instance and even inject objects into your ColdFusion ORM entities using [WireBox](http://wiki.coldbox.org/wiki/WireBox.cfm). Not only that but we have also enabled the event handler to re-transmit the Hibernate Interceptions and announce them as ColdBox Interceptions. This way, you can intercept very easily the ColdBox way, create several chains, etc. The first thing you need to do is tell the CF Engine that you want to enable event handling and also which CFC will handle your global ORM entity events. Important: You can also use the [WireBox-EntityInjection](http://wiki.coldbox.org/wiki/WireBox-EntityInjection.cfm) approach which allows your event handler to be portable between ColdBox and Non-ColdBox applications and also allows you to NOT even create an event handler but just point it to the appropriate class. # Application.cfc settings ```javascript this.ormSettings = { cfclocation="model", dbcreate = "update", dialect = "MySQLwithInnoDB", logSQL = true, // Enable event handling eventhandling = true, // Set the event handler to use, which will be inside our application. eventhandler = "model.ORMEventHandler" }; ``` In this basic ORM configuration structure I have two lines that deal with ORM events: ```javascript // Enable event handling eventhandling = true, // Set the event handler to use, which will be inside our application. eventhandler = "model.ORMEventHandler" ``` This enables the internal Hibernate interceptors and then you map a global CFC that will handle them. Mine will be in my local application's model folder named ORMEventHandler. It has to be inside of my application's folder structure in order to be able to talk to the ColdBox application. This is extremely important, as this creates the bridge between the ORM and ColdBox. > **Imporant** The ORM Event Handler CFC MUST exist within the application in order for the bridge to work. # Event Handler CFC ![](https://raw.githubusercontent.com/wiki/coldbox-modules/cbox-cborm/ORMEventHandler.jpg) The ColdFusion documentation says that in order to create a global event handler that it must implement the *CFIDE.orm.IEventHandler* interface. So all you need to do is create the CFC and make it extend the core class that will provide you with all these capabilities: *cborm.models.EventHandler* ```javascript component extends="cborm.models.EventHandler"{ } ``` That's it! Just by doing this the CF ORM will call your CFC's event methods in this CFC and satisfy the interface. Of course you can override the methods, but always remember to fire off the parent class methods in order for the ColdBox interceptions to still work. You can then override each event method as needed. Below is a chart of methods you can override: | Method | Description | | -------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | | postNew(entity) | This method is called by the ColdBox Base ORM Service Layers after a new entity has been created via its new() method. | | preLoad(entity) | This method is called before the load operation or before the data is loaded from the database. | | postLoad(entity) | This method is called after the load operation is complete. | | preInsert(entity) | This method is called just before the object is inserted. | | postInsert(entity) | This method is called after the insert operation is complete. | | preUpdate(Struct oldData,entity) | This method is called just before the object is updated. A struct of old data is passed to this method to know the original state of the entity being updated. | | postUpdate(entity) | This method is called after the update operation is complete. | | preDelete(entity) | This method is called before the object is deleted. | | postDelete(entity) | This method is called after the delete operation is complete. | The base event handler CFC you inherit from has all the ColdBox interaction capabilities you will ever need. You can find out all its methods by referring to the API and looking at that class or by inspecting the ColdBox Proxy class, which is what is used for enabling ColdBox interactions: *coldbox.system.remote.ColdboxProxy* # Entity Injection Capabilities By enabling the global event handler, [WireBox](http://wiki.coldbox.org/wiki/WireBox.cfm) can also be enabled to listen to these events and use them to wire up dependencies in these ORM entities and thus provide dependency injection for ORM entities. The injection takes place during the postLoad() and postNew() event cycles. All you need to do is enable ORM injection via the [ConfigurationCFC](http://wiki.coldbox.org/wiki/ConfigurationCFC.cfm) > **Important** If you have to also override the event handler's *postLoad()*,*postNew()* method, you will then need to call the super class method so the injection procedures can take place: *super.postLoad(entity)* *super.postNew(entity)* The video below describes the entire process: ![](https://raw.githubusercontent.com/wiki/coldbox-modules/cbox-cborm/entityInjectionCapabilities.png) # ORM To ColdBox Interceptions ![](https://raw.githubusercontent.com/wiki/coldbox-modules/cbox-cborm/ORMEventHandlerBroadcast.jpg) We have also expanded the hibernate ORM events to bridge the gap to the ColdBox interceptors. So now all the hibernate interceptors will relay their events to ColdBox via interceptors. This means that each hibernate event, like *preLoad()* for example, will announce its ColdBox counterpart: *ORMPreLoad()*. Below are the new interception points the ORM Event Handler exposes with the appropriate interception data it announces: | Interception Point | Intercept Structure | Description | | ------------------ | ------------------- | --------------------------------- | | ORMPostNew | {entity} | Called via the postNew() event | | ORMPreLoad | {entity} | Called via the preLoad() event | | ORMPostLoad | {entity} | Called via the postLoad() event | | ORMPostDelete | {entity} | Called via the postDelete() event | | ORMPreDelete | {entity} | Called via the preDelete() event | | ORMPreUpdate | {entity,oldData} | Called via the preUpdate() event | | ORMPostUpdate | {entity} | Called via the postUpdate() event | | ORMPreInsert | {entity} | Called via the preInsert() event | | ORMPostInsert | {entity} | Called via the postInsert() event | With the exposure of these interception points to your ColdBox application, you can easily create decoupled executable chains of events that respond to ORM events. This really expands the ORM interceptor capabilities to a more decoupled way of listening to ORM events. You can even create different interceptors for different ORM entity classes that respond to the same events, extend the entities with AOP, change entities at runtime, and more; how cool is that. # ColdBox Criteria Builder The ColdBox Hibernate Criteria Builder is a powerful object that will help you build and execute [hibernate criteria queries](http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycriteria.html). [HQL](http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html/queryhql.html) 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. The ColdBox Criteria builder offers a powerful programmatic DSL builder for Hibernate Criteria queries. You can see below some of the Hibernate documentation on criteria queries. 1. Hibernate Criteria Queries: 2. Hibernate Criteria Interface: 3. Hibernate Restrictions: 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. > **Note** The best place to see all of the functionality of the Criteria Builder is to check out the latest [API Docs](http://apidocs.coldbox.org/). # Getting Started A criteria builder can be requested from our Base ORM services or a virtual service, which will bind itself automatically to the binded entity, by calling on their newCriteria() method. The corresponding class is: cborm.models.CriteriaBuilder 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 | useQueryCaching | false | false | To allow for query caching of *list()* operations | | queryCacheRegion | string | false | criteria.{entityName} | The name of the cache region to use | If you call newCriteria() from a virtual service layer, then you don't pass the entityName argument as it roots itself automatically. Examples ```javascript // Base ORM Service c = newCriteria( 'entityName' ); // Virtual c = newCriteria(); // Examples var results = c.like("firstName","Lui%") .maxResults( 50 ) .order("balance","desc") .and( c.restrictions.between( "balance", 200, 300), c.restrictions.eq("department", "development") ) .list(); // with pagination var results = c.like("firstName","Lui%") .order("balance","desc") .and( c.restrictions.between( "balance", 200, 300), c.restrictions.eq("department", "development") ) .list(max=50,offset=20); // more complex var results = c.in("name","luis,fred,joe") .OR( c.restrictions.isNull("age"), c.restrictions.eq("age",20) ) .list(); ``` Once you have an instance of the Criteria Builder class you can start adding restrictions, projections and configuration data for your query. All by concatenating methods in a nice programmatic DSL. Once all the restrictions, projections and/or configuration data are in place, you will execute the query/projections using our result methods. Please note that you can request as many new criteria builders as you like and each of them will execute different queries. So let's start with the restrictions. # Restrictions 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. The ColdBox criteria class offers almost all of the criterion methods found in the native hibernate Restrictions class () but if you need to add very explicit criterion directly you have access to the ColdBox Restrictions class which proxies calls to the native Hibernate class. You do this by either retrieving it from the Base/Virtual ORM services (getRestrictions()), create it manually, or the Criteria object itself has a public property called restrictions which you can use rather easily. We prefer the latter approach. Now, plese 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. ```javascript // From base ORM service var restrictions = getRestrictions() // Manually Created var restrictions = new coldbox.system.orm.hibernate.criterion.Restrictions(); // From Criteria Builder newCriteria().restrictions ``` Ok, now that the formalities have been explained let's build some criterias. # Criterias 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. So let's start with all the different supported criterion methods in the Criteria object, which are the most commonly used. If you need to use methods that are not in the Criteria object you will request them via the Restrictions object, which can proxy calls to the underlying Hibernate native Restrictions class (). | Method | Description | Example | | ---------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------- | | between(property,minValue,maxValue) | Where the property value is between two distinct values | *c.between("age",10,30);* | | conjunction(required array restrictionValues) | Group expressions together in a single conjunction (A and B and C...) and return the conjunction |

c.conjunction( \[
c.restrictions.between("balance",100,200),
c.restrictions.lt("salary",20000) ] );

| | disjunction(required array restrictionValues) | Group expressions together in a single disjunction (A or B or C...) |

c.disjunction( \[
c.restrictions.between("balance",100,200),
c.restrictions.lt("salary",20000) ] );

| | eqProperty(property, otherProperty) | Where one property must equal another | *c.eqProperty("createDate","modifyDate");* | | eq(property, value) or isEq(property,value) | Where a property equals a particular value, you can also use eq() | *c.eq("age",30);* | | gt(property, value) or isGT(property, value) | Where a property is greater than a particular value, you can also use gt() | *c.gt("publishedDate", now() );* | | gtProperty(property,otherProperty) | Where a one property must be greater than another | *c.gtProperty("balance","overdraft");* | | ge(property,value) or isGE | Where a property is greater than or equal to a particular value, you can also use ge() | *c.ge("age",18);* | | geProperty(property, otherProperty) | Where a one property must be greater than or equal to another | *c.geProperty("balance","overdraft");* | | idEQ(required any propertyValue) | Where an objects id equals the specified value | *c.idEq( 4 );* | | ilike(required string property, required string propertyValue) | A case-insensitive 'like' expression | *c.ilike("lastName", "maj%");* | | isIn(required string property, required any propertyValue) or in(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() | *c.isIn( "id", \[1,2,3,4] );* | | isEmpty(required string property) | Where a collection property is empty | *c.isEmpty("childPages");* | | isNotEmpty(required string property) | Where a collection property is not empty | *c.isNotEmpty("childPages");* | | isFalse(required string property) | Where a collection property is false | *c.isFalse("isPublished");* | | isNull(required string property) | Where a property is null | *c.isNull("passwordProtection");* | | isNotNull(required string property) | Where a property is NOT null | *c.isNotNull("publishedDate");* | | islt(required string property, required any propertyValue) or lt() | Where a property is less than a particular value, you can also use lt() | *c.isLT("age", 40 );* | | ltProperty(required string property, required string otherProperty) | Where a one property must be less than another | *c.ltProperty("sum", "balance");* | | isle(required string property, required any propertyValue) or le() | Where a property is less than or equal a particular value, you can also use le() | *c.isLE("age", 30);* | | leProperty(required string property, required string otherProperty) | Where a one property must be less than or equal to another | *c.LeProperty("balance","balance2");* | | like(required string property, required string propertyValue) | Equivalent to SQL like expression | *c.like("content", "%search%");* | | ne(required string property, required any propertyValue) | Where a property does not equal a particular value | *c.ne("isPublished", true);* | | neProperty(required string property, required any otherProperty) | Where one property does not equal another | *c.neProperty("password","passwordHash");* | | sizeEq(required string property, required any propertyValue) | Where a collection property's size equals a particular value | *c.sizeEq("comments",30);* | | sizeGT(required string property, required any propertyValue) | Where a collection property's size is greater than a particular value | *c.sizeGT("children",5);* | | sizeGE(required string property, required any propertyValue) | Where a collection property's size is greater than or equal to a particular value | *c.sizeGE("children", 10);* | | sizeLT(required string property, required any propertyValue) | Where a collection property's size is less than a particular value | *c.sizeLT("childPages", 25 );* | | sizeLE(required string property, required any propertyValue) | Where a collection property's size is less than or equal a particular value | *c.sizeLE("childPages", 25 );;* | | sizeNE(required string property, required any propertyValue) | Where a collection property's size is not equal to a particular value | *c.sizeNE("childPages",0);* | | sqlRestriction(required string sql) | Use arbitrary SQL to modify the resultset | *c.sqlRestriction("char\_length( lastName ) = 10");* | | and(Criterion, Criterion, ...) | Return the conjuction of N expressions as arguments | *c.and( c.restrictions.eq("name","luis"), c.restrictions.gt("age",30) );* | | or(Criterion, Criterion, ….) | Return the disjunction of N expressions as arguments | c.or( c.restrictions.eq("name","luis"), c.restrictions.eq("name", "joe") ) | | not(required any criterion) or isNot() | Return the negation of an expression | *c.isNot( c.restrictions.eg("age", 30) );* | | isTrue(required string property) | Returns if the property is true | *c.isTrue("isPublished");* | > **Note** In some cases (*isEq()*, *isIn()*, etc), you may receive data type mismatch errors. These can be resolved by using [JavaCast](http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7fbe.html) on your criteria value. ```javascript c.isEq("userID", JavaCast( "int", 3 )); ``` You can also use the add() method to add a manual restriction or array of restrictions to the criteria you are building. ```javascript c.add( c.restrictions.eq("name","luis") ) ``` But as you can see from the code, the facade methods are much nicer. # Converting Values to Java Types By default you will need to do some javaCasting() on the values in order for the criteria builder to work correctly on some values. Remember that ColdFusion is a typeless language and Java is not. However, we have added to convenience methods for you so you can just pass in values without caring about casting: * convertIDValueToJavaType(id) * convertValueToJavaType(propertyName, value) You can also find these methods in the Base ORM services and Virtual Entity Services. ```javascript c.convertIDValueToJavaType( id = 123 ); c.convertIDValueToJavaType( id = ["1","2","3"] ); c.convertValueToJavaType(propertyName="id", value=arguments.testUserID) ``` # Configuration Modifiers The following methods alters the behavior of the executed query: | Method | Description | Example | | ---------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------- | | timeout(numeric timeout) | Set a timeout for the underlying JDBC query in milliseconds. | *timeout( 5000 )* | | readOnly(boolean readOnly) | Set the read-only/modifiable mode for entities and proxies loaded by this Criteria, defaults to readOnly=true | *readOnly(true)* | | firstResult() | Specifies the offset for the results. A value of 0 will return all records up to the maximum specified. | *firstResult(11)* | | maxResults(numeric maxResults) | Set a limit upon the number of objects to be retrieved. | *maxResults(25)* | | fetchSize(numeric fetchSize) | Set's the fetch size of the underlying JDBC query | *fetchSize(50)* | | cache(cache, cacheRegion= ) | Tells Hibernate whether to cache the query or not (if the query cache is enabled), and optionally choose a cache region | *cache(true), cache(true,'my.cache')* | | cacheRegion(cacheRegion) | Tells Hibernate the cache region to store the query under | cacheRegion('my.cool.cache') | | order(property,sortDir='asc',ignoreCase=false) | Specifies both the sort property (the first argument, the sort order (either 'asc' or 'desc'), and if it should ignore cases or not | *order('lastName','asc',false)* | ```javascript 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); ``` # Results 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. | Method | Description | Example | | ---------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------- | | list(max, offset, timeout, sortOrder, ignoreCase, asQuery=false) | Execute the criterias and give you the results. | *list(), list(max=50,offset=51,timeout=3000,ignoreCase=true)* | | get() | Retrieve one result only. | *get()* | | count() | Does a projection on the given criteria query and gives you the row count only, great for pagination totals or running counts. Note, count() can't be called on a criteria after list() has been executed. | *count()* | ```javascript // Get var results = c.idEq( 4 ).get(); // Listing var results = c.like("name", "lui%") .list(); // Count via projections of all users that start with the letter 'L' var count = c.ilike("name","L%").count(); ``` > **Note** You can call *count()* and *list()* on the same criteria, but due to the internal workings of Hibernate, you must call *count()* first, then *list()*. # Associations You can also navigate associations by nesting the criterias using the createCriteria("association\_name") method and then concatenating the properties of the association to query upon. You will basically be switching the pivot point of the query. ```javascript var c = newCriteria("User"); var users = c.like("name","lui%") .createCriteria("admins") .like("name","fra%") .list(); ``` ```javascript var c = newCriteria("User"); var users = c.like("name","lui%") .withAdmins().like("name","fra%") .list(); ``` You can also use a hibernate property approach which aliases the association much how HQL approaches it by using the createAlias("associationName","alias") method: ```javascript var c = newCriteria("User"); var users = c.like("name","lui%") .createAlias("admins","a") .eq("a.name","Vero") .list(); ``` Let's see the method signatures for these guys: ```javascript createCriteria(required string associationName,numeric joinType) createAlias(required string associationName, required string alias, numeric joinType) with{AssociationName}( joinType ) ``` # Result Transformers Our criteria builder also supports the notion of projections (). A projection is used to change the nature of the results, much how a result transformer does. However, there are several projection types you can use which are great for doing counts, distinct counts, max values, sums, averages and much more. This is great when you do paging as obviously you do not want to execute two queries: one for the pagination and another for the total reuslts count. Below are the available projections you can use: | Transform | Description | Example | | --------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | avg | The name of the property to avg or a list or array of property names | *withProjections(avg="salary")* | | count | The name of the property to count or a list or array of property names | *withProjections(count="comments")* | | countDistinct | The name of the property to count distinct or a list or array of property names | *withProjections(countDistinct="email")* | | 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 | *withProjections(distinct="email")* | | groupProperty | The name of the property to group by or a list or array of property names | *withProjections(groupproperty="lastName")* | | max | The name of the property to max or a list or array of property names | *withProjections(max="lastLogin")* | | min | The name of the property to min or a list or array of property names | *withProjections(min="cid")* | | property | The name of the property to do a projected value on or a list or array of property names | *withProjections(property="firstname")* | | sum | The name of the property to sum or a list or array of property names | *withProjections(sum="balance")* | | rowCount | Do a row count on the criteria | *withProjections(rowcount=1)* | | id | Return the projected identifier value | *withProjections(id=1)* | | sqlProjection | Return projected value for sql fragment. Can accept a single config {sql,alias,property}, or an array of multiple configs. |

withProjections(sqlProjection={sql="SELECT count( ) from blog where Year < 2006 and Author={alias}.Author",
alias="BlogPosts",
property="Author" })\*

| | sqlGroupProjection | Return projected value for sql fragment with grouping. Can accept a single config( sql,alias,property,group}, or an array of multiple configs. |

withProjections(sqlGroupProjection={sql="SELECT count( ) from blog where Year < 2006 and Author={alias}.Author",
alias="BlogPosts",
property="Author",
group="Author" })\*

| | detachedSQLProjection | Creates a sqlProjection() based on Detached Criteria Builder | See [Detached Criteria Builder](https://github.com/ColdBox/cbox-cborm/wiki/ORM-Detached-Criteria-Builder) | You will use them by passing them to the *withProjections()* method as arguments that match the projection type. The value of the argument 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. 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. ```javascript Ex: avg="balance", avg="balance:myBalance", avg="balance, total", avg=["balance","total"] ``` > **INFO** If the :alias is not used, then the alias becomes the property name. ```javascript // 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(); ``` # Detached Criteria Builder You can now use the [ORM:DetachedCriteriaBuilder](https://github.com/ColdBox/cbox-cborm/wiki/ORM-Detached-Criteria-Builder) to create programmatically create criteria and projection subqueries. To create an instance of Detached Criteria Builder, simply call the *createSubcriteria()* method on your existing criteria. The arguments for *createSubcriteria()* are: | Argument | Type | Required | Default | Description | | ---------- | ------ | -------- | ------- | ------------------------------------------------------------------- | | entityName | string | true | --- | The name of the entity to bind this detached criteria builder with. | | alias | string | true | --- | The alias to use | Once the Detached Criteria Builder is defined, you can add projections, criterias, and associations, just like you would with Criteria Builder. Examples ```javascript // criteria builder c = newCriteria(); // detached criteria builder c.createSubcriteria( 'Car', 'CarSub' ); ``` See the documentation for [ORM:DetachedCriteriaBuilder](https://github.com/ColdBox/cbox-cborm/wiki/ORM-Detached-Criteria-Builder) for more information. # ColdBox Detached Criteria Builder If you use ORM in your ColdBox apps, you are hopefully already taking full advantage of [Criteria Builder](/v1.x-1/coldbox-criteria-builder), ColdBox’s powerful, programmatic DSL builder for Hibernate Criteria queries (and if you’re not using it, you should!). With the new Detached Criteria Builder, you can expand the power and flexibility of your criteria queries with support for criteria and projection subqueries, all while using the same intuitive patterns of Criteria Builder. No fuss, just more flexibility and control for your criteria queries! > "Some applications need to create criteria queries in "detached mode", where the Hibernate session is not available. It also allows you to express subqueries." Hibernate Docs For more information about Detached Criteria and Subqueries, check out the following Hibernate documentation: 1. Hibernate Docs: 2. Hibernate DetachedCriteria: 3. Hibernate Subqueries: > **INFO** The best place to see all of the functionality of the Detached Criteria Builder is to check out the latest [API Docs](/v1.x-1/coldbox-detached-criteria-builder). # Getting Started Whether you want to create a criteria subquery, or a projection subquery, you'll first need to get a new instance of the Detached Criteria Builder class. Since all of the subqueries we're creating are being added to our main criteria query either as a criteria or a projection, we can get the Detached Criteria Builder like so: ```javascript // Get a reference to a criteria builder from an ORM base or virtual service c = ormservice.newCriteria(); // detached criteria builder c.createSubcriteria( entityName='Car', alias='CarSub' ); ``` The arguments for the *createSubcriteria()* method are: | Argument | Type | Required | Default | Description | | ---------- | ------ | -------- | ------- | ------------------------------------------------------------------- | | entityName | string | true | --- | The name of the entity to bind this detached criteria builder with. | | alias | string | true | --- | The alias to use for the entity | Once the Detached Criteria Builder is defined, you can add projections, criterias, and associations, just like you would with a normal Criteria Builder. Examples ```javascript c.newCriteria( entityName=‘Car’ ); // all-in-one criteria subquery c.add( c.createSubcriteria( ‘Car’, ‘CarSub’ ) .withProjections( property=’CarID’ ) .isEq( ‘Make’, ‘Ford’ ) .propertyIn( ‘CarID’ ) ).list(); // detached criteria builder separately, then add as criteria var dc = c.createSubcriteria( ‘Car’, ‘CarSub’ ) .withProjections( property=’CarID’ ) .isEq( ‘Make’, ‘Ford’ ); // add criteria subquery c.add( dc.propertyIn( ‘CarID’ ) ); ``` # Projections When using Detached Criteria Builder for criteria or projection subqueries, you must use a projection. If you think about it from a SQL perspective, this makes sense. After all, we need our subquery to return a specific result (property values, a count, etc.) which will be compared to a property value on the root table (in the case of a criteria subquery), or which will be returned as a valid column value (in the case of a projection subquery). All of the projections available for Criteria Builder are also available for Detached Criteria Builder. Examples ```javascript c = newCriteria(); c.add( c.createSubcriteria( ‘Car’, ‘CarSub’ ) // result of subquery will be CarIDs .withProjections( property=’CarID’ ) .isEq( ‘Make’, ‘Ford’ ) .propertyIn( ‘CarID’ ) ).list(); ``` Be careful when using projections and adding criterias to your query. Hibernate does not work well with projections and alias definitions, so if you want to add a criteria you must use `this.yourPropertyName` to tell Hibernate not to use the alias and build a correct SQL. ```javascript var c = newCriteria(); oReviews = c.withProjections( property="status,ID,rating,isComplete" ); c.isTrue( "this.isComplete" ); c.gt( "this.rating", JavaCast( "float", 0 ) ); oReviews = c.resultTransformer( c.ALIAS_TO_ENTITY_MAP ).list(); ``` # Subqueries If you are using Detached Criteria Builder for a criteria subquery, you will also need to use one of the methods from the ColdBox subqueries class. This is what will ultimately bind the subquery result to the root entity. The most important thing to remember is that this subquery is what needs to be added as a criterion to your criteria query. So whether you are doing a simple subquery or building up a complex detached criteria, the result of one of the methods below should be what is added as a criterion to the criteria query (see example below). Examples ```javascript // wrong way...will fail because the subquery method “propertyIn()” is not what is added c.add( c.createSubcriteria( ‘Car’, ‘CarSub’ ) .withProjections( property=’CarID’ ) .propertyIn( ‘CarID’ ) .isEq( ‘Make’, ‘Ford’ ) ).list(); // right way...since propertyIn() is last in the chain, it’s value will be what is ultimately added as a criteria c.add( c.createSubcriteria( ‘Car’, ‘CarSub’ ) .withProjections( property=’CarID’ ) .isEq( ‘Make’, ‘Ford’ ) .propertyIn( ‘CarID’ ) ).list(); // right way--split up dc = c.createSubcriteria( ‘Car’, ‘CarSub’ ) .withProjections( property=’CarID’ ) .isEq( ‘Make’, ‘Ford’ ); c.add( dc.propertyIn( ‘CarID’ ) ).list(); ``` | Method | Description | | ---------------------------------------- | ------------------------------------------------------------------------------------------------------------- | | detachedSQLProjection | A single or array of DetachedCriteriaBuilders which will return the projected value | | subEq(value) | Where the result of the subquery is eq to a specific value | | subEqAll(value) | Where all values in the subquery result are equal to a specific value | | subGe(value) | Where values in the subquery result are greater than or equal to a specific value | | subGeAll(value) | Where all values in the subquery result are equal to a specific value | | subGeSome(value) | Where some values in the subquery result are greater than a specific value | | subGt(value) | Where values in the subquery result are greater than a specific value | | subGtAll(required string value) | Where all values in the subquery result are greater than a specific value | | subGtSome(required string value) | Where some values in the subquery result are greater than a specific value | | subIn(required string value) | Where values in the subquery result are contained within the specified list of values | | subLe(required string value) | Where values in the subquery result are less than or equal to a specific value | | subLeAll(required string value) | Where all values in the subquery result are less than or equal to a specific value | | subLeSome(required string value) | Where some values in the subquery result are less than or equal to a specific value | | subLt(required string value) | Where values in the subquery result are less than a specific value | | subLtAll(required string value) | Where all values in the subquery result are less than a specific value | | subLtSome(required string value) | Where some values in the subquery result are less than a specific value | | subNe(required string value) | Where values in the subquery result are not equal to a specific value | | subNotIn(required string value) | Where values in the subquery result are not contained within the specified list of values | | exists | Where the subquery returns some result | | notExists | Where the subquery does not return a result | | propertyEq(required string property) | Where values in the subquery result are equal to the the specified property value | | propertyEqAll(required string property) | Where all values in the subquery result are equal to the the specified property value | | propertyGe(required string property) | Where values in the subquery result are greater than or equal to the the specified property value | | propertyGeAll(required string property) | Where all values in the subquery result are greater than or equal to the the specified property value | | propertyGeSome(required string property) | Where some values in the subquery result are greater than or equal to the the specified property value | | propertyGt(required string property) | Where values in the subquery result are greater than the the specified property value | | propertyGtAll(required string property) | Where all values in the subquery result are greater than the the specified property value | | propertyGtSome(required string property) | Where some values in the subquery result are greater than the the specified property value | | propertyIn(required string property) | Where values in the subquery result are contained in within the list of values for the specified property | | propertyLe(required string property) | Where values in the subquery result are less than or equal to the the specified property value | | propertyLeAll(required string property) | Where all values in the subquery result are less than or equal to the the specified property value | | propertyLeSome(required string property) | Where some values in the subquery result are less than or equal to the the specified property value | | propertyLt(required string property) | Where values in the subquery result are less than the the specified property value | | propertyLtAll(required string property) | Where all values in the subquery result are less than the the specified property value | | propertyLtSome(required string property) | Where some values in the subquery result are less than the the specified property value | | propertyNe(required string property) | Where values in the subquery result are not equal to the the specified property value | | propertyNotIn(required string property) | Where values in the subquery result are not contained in within the list of values for the specified property | # DetachedSQLProjection() Besides using it for creating criteria subqueries, Detached Criteria Builder can also be used in conjunction with the new detachedSQLProjection() method to return a projected result based on a subquery. The detachedSQLProjection() method can be called just like any other Criteria Builder projection. | Transform | Description | | --------------------- | ----------------------------------------------------------------------------------- | | detachedSQLProtection | A single or array of DetachedCriteriaBuilders which will return the projected value | Examples ```javascript c = newCriteria(); c.withProjections( detachedSQLProjection= c.createSubcriteria( "Car", "Car2" ) .withProjections( count="Car2.Year" ) .isLT( "Year", javaCast( "int", 2006 ) ) .isEQ( "CarID", "{alias}.CarID" ), groupProperty="Make" ).list(); ``` > **INFO** If you need to use a property from the root entity in one of your criterias, simply prepend the property name with {alias}. **MORE** otice how a subquery method was not used in this example of the Detached Criteria Builder. # Criterias All of the same criterias defined in Criteria Builder can be utilized in a Detached Criteria Builder as well. Easy, huh? Here they go again... 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. So let's start with all the different supported criterion methods in the Criteria object, which are the most commonly used. If you need to use methods that are not in the Criteria object you will request them via the Restrictions object, which can proxy calls to the underlying Hibernate native Restrictions class (). | Method | Description | Example | | ---------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------- | | between(property,minValue,maxValue) | Where the property value is between two distinct values | *c.between("age",10,30);* | | conjunction(required array restrictionValues) | Group expressions together in a single conjunction (A and B and C...) and return the conjunction |

c.conjunction( \[
c.restrictions.between("balance",100,200),
c.restrictions.lt("salary",20000) ] );

| | disjunction(required array restrictionValues) | Group expressions together in a single disjunction (A or B or C...) |

c.disjunction( \[
c.restrictions.between("balance",100,200),
c.restrictions.lt("salary",20000) ] );

| | eqProperty(property, otherProperty) | Where one property must equal another | *c.eqProperty("createDate","modifyDate");* | | eq(property, value) or isEq(property,value) | Where a property equals a particular value, you can also use eq() | *c.eq("age",30);* | | gt(property, value) or isGT(property, value) | Where a property is greater than a particular value, you can also use gt() | *c.gt("publishedDate", now() );* | | gtProperty(property,otherProperty) | Where a one property must be greater than another | *c.gtProperty("balance","overdraft");* | | ge(property,value) or isGE | Where a property is greater than or equal to a particular value, you can also use ge() | *c.ge("age",18);* | | geProperty(property, otherProperty) | Where a one property must be greater than or equal to another | *c.geProperty("balance","overdraft");* | | idEQ(required any propertyValue) | Where an objects id equals the specified value | *c.idEq( 4 );* | | ilike(required string property, required string propertyValue) | A case-insensitive 'like' expression | *c.ilike("lastName", "maj%");* | | isIn(required string property, required any propertyValue) or in(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() | *c.isIn( "id", \[1,2,3,4] );* | | isEmpty(required string property) | Where a collection property is empty | *c.isEmpty("childPages");* | | isNotEmpty(required string property) | Where a collection property is not empty | *c.isNotEmpty("childPages");* | | isFalse(required string property) | Where a collection property is false | *c.isFalse("isPublished");* | | isNull(required string property) | Where a property is null | *c.isNull("passwordProtection");* | | isNotNull(required string property) | Where a property is NOT null | *c.isNotNull("publishedDate");* | | islt(required string property, required any propertyValue) or lt() | Where a property is less than a particular value, you can also use lt() | *c.isLT("age", 40 );* | | ltProperty(required string property, required string otherProperty) | Where a one property must be less than another | *c.ltProperty("sum", "balance");* | | isle(required string property, required any propertyValue) or le() | Where a property is less than or equal a particular value, you can also use le() | *c.isLE("age", 30);* | | leProperty(required string property, required string otherProperty) | Where a one property must be less than or equal to another | *c.LeProperty("balance","balance2");* | | like(required string property, required string propertyValue) | Equivalent to SQL like expression | *c.like("content", "%search%");* | | ne(required string property, required any propertyValue) | Where a property does not equal a particular value | *c.ne("isPublished", true);* | | neProperty(required string property, required any otherProperty) | Where one property does not equal another | *c.neProperty("password","passwordHash");* | | sizeEq(required string property, required any propertyValue) | Where a collection property's size equals a particular value | *c.sizeEq("comments",30);* | | sizeGT(required string property, required any propertyValue) | Where a collection property's size is greater than a particular value | *c.sizeGT("children",5);* | | sizeGE(required string property, required any propertyValue) | Where a collection property's size is greater than or equal to a particular value | *c.sizeGE("children", 10);* | | sizeLT(required string property, required any propertyValue) | Where a collection property's size is less than a particular value | *c.sizeLT("childPages", 25 );* | | sizeLE(required string property, required any propertyValue) | Where a collection property's size is less than or equal a particular value | *c.sizeLE("childPages", 25 );;* | | sizeNE(required string property, required any propertyValue) | Where a collection property's size is not equal to a particular value | *c.sizeNE("childPages",0);* | | sqlRestriction(required string sql) | Use arbitrary SQL to modify the resultset | *c.sqlRestriction("char\_length( lastName ) = 10");* | | and(Criterion, Criterion, ...) | Return the conjuction of N expressions as arguments | *c.and( c.restrictions.eq("name","luis"), c.restrictions.gt("age",30) );* | | or(Criterion, Criterion, ….) | Return the disjunction of N expressions as arguments | c.or( c.restrictions.eq("name","luis"), c.restrictions.eq("name", "joe") ) | | not(required any criterion) or isNot() | Return the negation of an expression | *c.isNot( c.restrictions.eg("age", 30) );* | | isTrue(required string property) | Returns if the property is true | *c.isTrue("isPublished");* | > **Note** In some cases (*isEq()*, *isIn()*, etc), you may receive data type mismatch errors. These can be resolved by using [JavaCast](http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7fbe.html) on your criteria value. ```javascript c.isEq("userID", JavaCast( "int", 3 )); ``` You can also use the add() method to add a manual restriction or array of restrictions to the criteria you are building. ```javascript c.add( c.restrictions.eq("name","luis") ) ``` But as you can see from the code, the facade methods are much nicer. # Associations Yes, you can also create associations in Detached Criteria Builder, just like you would with Criteria Builder. Go on, make some uber-complicated queries! Here we go!... You can also navigate associations by nesting the criterias using the createCriteria("association\_name") method and then concatenating the properties of the association to query upon. You will basically be switching the pivot point of the query. ```javascript var c = newCriteria("User"); var users = c.like("name","lui%") .createCriteria("admins") .like("name","fra%") .list(); ``` ```javascript var c = newCriteria("User"); var users = c.like("name","lui%") .withAdmins().like("name","fra%") .list(); ``` You can also use a hibernate property approach which aliases the association much how HQL approaches it by using the createAlias("associationName","alias") method: ```javascript var c = newCriteria("User"); var users = c.like("name","lui%") .createAlias("admins","a") .eq("a.name","Vero") .list(); ``` Let's see the method signatures for these guys: ```javascript createCriteria(required string associationName,numeric joinType) createAlias(required string associationName, required string alias, numeric joinType) with{AssociationName}( joinType ) ``` # Help! I'm Not Getting the Result I expected! Since we’re not writing SQL, it can sometimes be frustrating to uncover why results from Criteria Builder and Detached Criteria Builder don’t match up with what you’re expecting. An easy way to debug in these scenarios is to enable SQL logging and actually look at the query which is ultimately executed after Hibernate does it magic. For a good guide on setting up SQL logging for ColdFusion ORM, check out Once SQL logging is setup, you’ll be able to instantly see the SQL which is executed to deliver the result you’re getting from your criteria queries. You can then run these queries independently (such as in SQL Server Management Studio), identify where the issues are, and tweak your criteria query until it’s perfect. # Introduction The `cborm` module is a module that will enhance your experience when working with the ColdFusion ORM powered by [Hibernate](https://hibernate.org/). It will not only enhance it with dynamic goodness but give you a fluent and human approach to working with Hibernate. ![](/files/-Lk6EEIAf2sEwSd4UHs9) ### Some Features * **Service Layers** with all the methods you could probably think off to help you get started in any project * **Virtual service layers** so you can create virtual services for any entity in your application * Automatic RESTFul resources handler, focus on your domain objects and business logic, not the boilerplate of REST * `ActiveEntity` our implementation of Active Record for ORM * Fluent queries via Hibernate's criteria and detached criteria queries with some Dynamic CFML goodness * Automatic transaction demarcation for save and delete operations * Dynamic finders and counters for expressive and fluent shorthand SQL * Automatic Java casting * Entity population from json, structs, xml, and queryies including building up their relationships * Entity validation via [cbValidation](https://forgebox.io/view/cbvalidation) * Includes the [Mementifier project](https://www.forgebox.io/view/mementifier) to produce memento states from any entity, great for producing JSON * Ability for finders and queries to be returned as Java streams using our [cbStreams](https://www.forgebox.io/view/cbstreams) project. ```javascript # A quick preview of some functionality var book = new Book().findByTitle( "My Awesome Book" ); var book = new Book().getOrFail( 2 ); new Book().getOrFail( 4 ).delete(); new Book().deleteWhere( isActive:false, isPublished:false ); property name="userService" inject="entityService:User"; return userService.list(); return userService.list( asStream=true ); var count = userService.countWhere( age:20, isActive:true ); var users = userService.findAllByLastLoginBetween( "01/01/2019", "05/01/2019" ); 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(); ``` **In other words, it makes using an ORM not SUCK!** ## Versioning The ColdBox ORM Module is maintained under the [Semantic Versioning](http://semver.org) guidelines as much as possible.Releases will be numbered with the following format: ``` .. ``` And constructed with the following guidelines: * Breaking backward compatibility bumps the major (and resets the minor and patch) * New additions without breaking backward compatibility bumps the minor (and resets the patch) * Bug fixes and misc changes bumps the patch ## License Apache 2 License: ## Important Links * Code: * Issues: ## Professional Open Source ![Ortus Solutions, Corp](/files/-LA-UqN1BRXynZ7RUVO7) The ColdBox ORM Module is a professional open source software backed by [Ortus Solutions, Corp](http://www.ortussolutions.com/services) offering services like: * Custom Development * Professional Support & Mentoring * Training * Server Tuning * Security Hardening * Code Reviews * [Much More](http://www.ortussolutions.com/services) ### HONOR GOES TO GOD ABOVE ALL Because of His grace, this project exists. If you don't like this, then don't read it, it's not for you. > "Therefore being justified by **faith**, we have peace with God through our Lord Jesus Christ: By whom also we have access by **faith** into this **grace** wherein we stand, and rejoice in hope of the glory of God." Romans 5:5 # Release History In this section you will find the release notes for each version we release under this major version. If you are looking for the release notes of previous major versions use the version switcher at the top left of this documentation book. Here is a breakdown of our major version releases. ## Version 2.0 A complete rewrite of the module to support a more modern and fluent approach to working with Hibernate/ColdFusion ORM. In this release we had to support 3 versions of Hibernate: 3 (Lucee), 4 (ACF 2016) and 5 (ACF 2018), which in itself proved to be a gargantuan task. We also focused on bringing more functional programming aspects to working with collections of entities and even introduced **cbStreams** as part of the cborm module. This gives you the ability to produce streams out of any method that produces a collection of entities. We also focused on converting the state of an object graph to a raw ColdFusion data struct as we live in the world of APIs. We include the **mementifier** module which allows every single entity to have a `getMemento()` method that will convert itself and its relationships to raw CF data constructs so you can take that state and either marshall it to another format (json,xml,excel) or audit the state. ## Version 1.0 The first version of the cbORM series that focused on expanding the native ColdFusion ORM methods and exposing much more Hibernate functionality to the CFML world. # What's New With 2.5.0 ## Release Notes * `Features` : Introduction of the automatic resource handler for ORM Entities based on ColdBox's 6 resources and RestHandler * `Improvement` : Natively allow for nested transactions and savepoints by not doing preemptive transaction commits when using transactions. * `Bug` : Fix on `getOrFail()` where if the id was 0, it would still return an empty object. * `Task` : Added formatting via cfformat You can read more about the [RESTFul Resources here](/v2.x-2/orm-events/automatic-rest-crud): # What's New With 2.4.0 ## Release Notes * `Feature` : Upgraded to `cbValidation` 2.0.0 * `Feature` : Updated the unique validator to match 2.0.0 standards * `Feature` : Upgraded to `mementifier` 2.0.0 # What's New With 2.3.0 ## Release Notes * `improvement` : In `executeQuery()` Determine if we are in a UPDATE, INSERT or DELETE, if we do, just return the results instead of a stream or query as the result is always numeric, the rows that were altered. * `bug` : Fixed `asStream` typo on `executeQuery()` * `bug` : Missing ACF2016 compat on tests # What's New With 2.2.1 ## Release Notes * `bug` : virtual entity service still had `entity` required for casting methods # What's New With 2.2.0 This release not only has some bug fixes but several new features that pack a punch. ## Major Features ### Criteria Query Fluent If Statements - `when()` How many times have you been dealing with if statements in order to add some restrictions into your criteria object? Many, this was the only way before, not anymore. So instead of doing something like the following: ```javascript c = newCriteria(); if( isBoolean( arguments.isPublished ) ){ c.isEq( "isPublished", isPublished ); // Published eq true evaluate other params if( isPublished ){ c.isLt( "publishedDate", now() ) .$or( c.restrictions.isNull( "expireDate" ), c.restrictions.isGT( "expireDate", now() ) ) .isEq( "passwordProtection","" ); } } if( !isNull( arguments.showInSearch ) ){ c.isEq( "showInSearch", showInSearch ); } return c.list(); ``` This looks like normal code, but we can do a more functional approach by introducing the `when()` function: ```javascript /** * @test The boolean evaluation * @target The closure to execute if test is true */ when( boolean test, function target ) ``` This function takes in as the first argument a **boolean** value, if the value is **true**, then the target closure will be called for you and the criteria will be passed via the arguments scope: ```javascript newCriteria() .when( isBoolean( arguments.isPublished ), function( c ){ // Published bit c.isEq( "isPublished", isPublished ) .when( isPublished, function( c ){ c.isLt( "publishedDate", now() ) .$or( c.restrictions.isNull( "expireDate" ), c.restrictions.isGT( "expireDate", now() ) ) .isEq( "passwordProtection","" ); } ) } ) .when( !isNull( arguments.showInSearch ), function( criteria ){ c.isEq( "showInSearch", showInSearch ); } ) .list() ``` This construct will help you create more fluent designs when building criteria queries, enjoy! ### PeekaBoo! - `peek()` We have also enhanced the criteria queries with a peek() function which allows you to peek in the current position of the criteria build up. This allows you to debug or inspect the SQL/HQL inside the criteria at that point in time. You can use it for sending debug data or logging, or auditing. ```javascript /** * @target the closure to execute, the criteria is passed as an argument */ peek( target ) ``` Enjoy your peekaboo function! ```javascript newCriteria() .when( isBoolean( arguments.isPublished ), function( c ){ // Published bit c.isEq( "isPublished", isPublished ) .when( isPublished, function( c ){ c.isLt( "publishedDate", now() ) .$or( c.restrictions.isNull( "expireDate" ), c.restrictions.isGT( "expireDate", now() ) ) .isEq( "passwordProtection","" ); } ) } ) .peek( function( c ){ systemOutput( "HQL after publish: #criteria.getSql()# "); } ) .when( !isNull( arguments.showInSearch ), function( criteria ){ c.isEq( "showInSearch", showInSearch ); } ) .peek( function( c ){ systemOutput( "HQL before listing: #criteria.getSql()# "); } ) .list() ``` ### ValidateOrFail() We have added a new function on the ActiveEntity object to assist with validations. The `validateOrFail()` function will allow you to validate the entity and if it validates it just returns the instance of the entity for a nice fluent design. However, if the validation fails, it throws a `ValidationException` and the errors are passed to the exception object via the `extendedInfo` key. You can then deal with the exception as needed. ```javascript getInstance( "User" ) .populate( rc ) .validateOrFail() .save(); ``` ## Release Notes * `Features`: New function for criteria query `when( boolean, target )` that you can use to build functional criterias without the use of if statements. * `Feature`: Missing `nullValue()` is BaseBuilder class * `Feature`: Added new criteria query `peek( closure )` function to allow for peeking into the building process. Pass in your closure that receives the criteria and interact with it. * `Feature`: Added a `validateOrFail()` to the active entity, which if the validation fails it will throw an exception or return back to you the same entity validated now. * `Improvement`: Better documentation for `deleteById()` since it does bulk deletion, which does not do any type of cascading. * `Improvement`: `isValid()` in active entity missing `includeFields` argument * `Improvement`: Timeout hints for criteria builder * `Improvement`: Updated exception type for criteria builder `get()` * `Bug`: ACF2016 issues with elvis operator. * `Bug`: `getOrFail()` had an invalid throw statement --- [Next Page](/llms-full.txt/1)