Grails hql update

Comparable in your Book class:. The result of the above class is that the Book instances in the books collection of the Author class will be ordered by their release date. To keep objects in the order which they were added and to be able to reference them by index like an array you can define your collection type as a List :. In this case when you add new elements to the books collection the order is retained in a sequential list indexed from 0 so you can do:.

When using a List , elements must be added to the collection before being saved, otherwise Hibernate will throw an exception org. HibernateException : null index column for collection :. The static hasMany property defines the type of the elements within the Map. The keys for the map must be strings. To ensure uniqueness when adding an entry to a Set association Hibernate has to load the entire associations from the database. If you have a large numbers of entries in the association this can be costly in terms of performance.

The same behavior is required for List types, since Hibernate needs to load the entire association to maintain order. Therefore it is recommended that if you anticipate a large numbers of records in the association that you make the association bidirectional so that the link can be created on the inverse side.

For example consider the following code:. In this example the association link is being created by the child Book and hence it is not necessary to manipulate the collection directly resulting in fewer queries and more efficient code.

Given an Author with a large number of associated Book instances if you were to write code like the following you would see an impact on performance:. If you are using Grails, then Grails automatically binds a Hibernate session to the currently executing request.

This lets you use the save and delete methods as well as other GORM methods transparently. If you are not using Grails then you have to make sure that a session is bound to the current request. One way to to achieve that is with the withNewSession Closure method:. Another option is to bind a transaction using the withTransaction Closure method:. A useful feature of Hibernate over direct JDBC calls and even other frameworks is that when you call save or delete it does not necessarily perform any SQL operations at that point.

Hibernate batches up SQL statements and executes them as late as possible, often at the end of the request when flushing and closing the session. If you are using Grails this typically done for you automatically, which manages your Hibernate session. If you are using GORM outside of Grails then you may need to manually flush the session at the end of your operation.

Hibernate caches database updates where possible, only actually pushing the changes when it knows that a flush is required, or when a flush is triggered programmatically. One common case where Hibernate will flush cached updates is when performing queries since the cached information might be included in the query results. This can be a significant performance boost for applications that do a lot of database writes. Note that flushing is not the same as committing a transaction.

If your actions are performed in the context of a transaction, flushing will execute SQL updates but the database will save the changes in its transaction queue and only finalize the updates when the transaction commits.

An example of using the save method can be seen below:. This save will be not be pushed to the database immediately - it will be pushed when the next flush occurs. But there are occasions when you want to control when those statements are executed or, in Hibernate terminology, when the session is "flushed". To do so you can use the flush argument to the save method:. Note that in this case all pending SQL statements including previous saves, deletes, etc. This also lets you catch any exceptions, which is typically useful in highly concurrent scenarios involving optimistic locking :.

Another thing to bear in mind is that GORM validates a domain instance every time you save it. If that validation fails the domain instance will not be persisted to the database.

By default, save will simply return null in this case, but if you would prefer it to throw an exception you can use the failOnError argument:. You can even change the default behaviour with a setting in application. An example of the delete method can be seen below:. As with saves, Hibernate will use transactional write-behind to perform the delete; to perform the delete in-place you can use the flush argument:.

Using the flush argument lets you catch any errors that occur during a delete. A common error that may occur is if you violate a database constraint, although this is normally down to a programming or schema error. The following example shows how to catch a DataIntegrityViolationException that is thrown when you violate the database constraints:. In order to perform a batch delete there are a couple of ways to achieve that. One way is to use a Where Query :. It is critical that you understand how cascading updates and deletes work when using GORM.

The key part to remember is the belongsTo setting which controls which class "owns" a relationship. If you do not define belongsTo then no cascades will happen and you will have to manually save each object except in the case of the one-to-many, in which case saves will cascade automatically if a new instance is in a hasMany collection.

Conversely if I later delete the Airport all Flight instances associated with it will also be deleted:. The above examples are called transitive persistence and are controlled via the belongsTo and the cascade policy. If I were to remove belongsTo then the above cascading deletion code would not work. It looks simple, right? And it is. Just set the location property to a Location instance and you have linked an author to a location.

But see what happens when we run the following code:. An exception is thrown. As you can see from the code, we are setting the Author. Hence the instance is transient. The obvious fix is to make the Location instance persistent by saving it:. Another option is to alter the cascade policy for the association.

There are two ways to do that. One way is to define belongsTo on the Location class:. Note that this above syntax does not make the association bidirectional since no property is defined. A bidirectional example would be:. Alternatively if you prefer that the Location class has nothing to do with the Author class you can define the cascade policy in Author :. The above example will configure the cascade policy to cascade saves and updates, but not deletes.

In the case of a bidirectional one-to-many where the many side defines a belongsTo then the cascade strategy is set to "ALL" for the one side and "NONE" for the many side. What this means is that whenever an instance of A is saved or updated. So will any instances of B. And, critically, whenever any instance of A is deleted so will all the associated instances of B! Since the belongsTo is not defined, this means that saves and updates will be cascaded from A to B , however deletes will not cascade!

Only when you define belongsTo in B or alter the cascading strategy of A will deletes be cascaded. So exactly like the previous case of a undirectional One-to-Many, without belongsTo definition no delete operations will be cascaded, but crucially saves and updates will by default.

If you do not want saves and updates to cacade then you must alter the cascade policy of A :. You may be wondering why this association is a many-to-one and not a one-to-one. The reason is because it is possible to have multiple instances of B associated to the same instance of A. If you wish to define this association as a true one-to-one association a unique constraint is required:.

In this case the flights association will be loaded at the same time as its Airport instance, although a second query will be executed to fetch the collection.

You can also use fetch: 'join' instead of lazy: false , in which case GORM will only execute a single query to get the airports and their flights. This works well for single-ended associations, but you need to be careful with one-to-manys. At that point, you will likely end up with fewer results than you were expecting. The reason for this is quite technical but ultimately the problem arises from GORM using a left outer join. So, the recommendation is currently to use fetch: 'join' for single-ended associations and lazy: false for one-to-manys.

Be careful how and where you use eager loading because you could load your entire database into memory with too many eager associations. Rather than configuring join fetching as the default for an association, it may be better to alter the join strategy only for the queries that require it. This can be done using the fetch argument to most GORM methods:.

Or using the join method when using Where Queries or criteria:. Although eager fetching is appropriate for some cases, it is not always desirable. If you made everything eager you could quite possibly load your entire database into memory resulting in performance and memory problems.

An alternative to eager fetching is to use batch fetching. You can configure Hibernate to lazily fetch results in "batches". In this case, due to the batchSize argument, when you iterate over the flights association, Hibernate will fetch results in batches of With batch fetching you get 1 query to fetch the Airport and 3 queries to fetch each Flight in batches of In other words, batch fetching is an optimization of the lazy fetching strategy.

Batch fetching can also be configured at the class level as follows:. By default GORM classes are configured for optimistic locking. Optimistic locking is a feature of Hibernate which involves storing a version value in a special version column in the database that is incremented after each update. The version column gets read into a version property that contains the current versioned state of persistent instance which you can access:.

When you perform updates Hibernate will automatically check the version property against the version column in the database and if they differ will throw a StaleObjectException. This will roll back the transaction if one is active. This is useful as it allows a certain level of atomicity without resorting to pessimistic locking that has an inherit performance penalty. The downside is that you have to deal with this exception if you have highly concurrent writes.

This requires flushing the session:. The way you deal with the exception depends on the application. You could attempt a programmatic merge of the data or go back to the user and ask them to resolve the conflict. This has the implication that other read operations will be blocking until the lock is released. In GORM pessimistic locking is performed on an existing instance with the lock method:. GORM will automatically deal with releasing the lock for you once the transaction has been committed.

To get around this problem you can use the static lock id method that takes an id just like get id :. As well as the lock id method you can also obtain a pessimistic locking using queries. For example using a dynamic finder:. If you try to reload the instance using get id Hibernate will return the current modified instance from its Session cache.

So GORM provides some methods to retrieve the original values that Hibernate caches when it loads the instance which it uses for dirty checking. You can use the isDirty method to check if any field has been modified:. You can use the getDirtyPropertyNames method to retrieve the names of modified fields; this may be empty but will not be null:. You can use the getPersistentValue fieldName method to retrieve the value of a modified field:.

Depending on the complexity of the query you have the following options in order of flexibility and power:. Use the list method to obtain all instances of a given class:. The list method supports arguments to perform pagination:. The second basic form of retrieval is by database identifier using the get id method:. You can also obtain a list of instances for a set of identifiers using getAll :. GORM supports the concept of dynamic finders.

Instead, a method is auto-magically generated using code synthesis at runtime, based on the properties of a given class. Take for example the Book class:. The Book class has properties such as title , releaseDate and author.

The basic form is:. The tokens marked with a? Each comparator changes the nature of the query. In the above example the first query is equivalent to equality whilst the latter, due to the Like comparator, is equivalent to a SQL like expression. InRange - Between the from and to values of a Groovy Range. Notice that the last three require different numbers of method arguments compared to the rest, as demonstrated in the following example:.

You can combine as many criteria as you like, but they must all be combined with And or all Or. If you need to combine And and Or or if the number of criteria creates a very long method name, just convert the query to a Criteria or HQL query. In this case if the Author instance is not null we use it in a query to obtain all the Book instances for the given Author. The same pagination and sorting parameters available on the list method can also be used with dynamic finders by supplying a map as the final parameter:.

The where method builds on the support for Detached Criteria by providing an enhanced, compile-time checked query DSL for common queries. The where method is more flexible than dynamic finders, less verbose than criteria and provides a powerful mechanism to compose queries.

The closure should define the logical criteria in regular Groovy syntax, for example:. The returned object is a DetachedCriteria instance, which means it is not associated with any particular database connection or session. This means you can use the where method to define common queries at the class level:.

Query execution is lazy and only happens upon usage of the DetachedCriteria instance. If you want to execute a where-style query immediately there are variations of the findAll and find methods to accomplish this:. Each Groovy operator maps onto a regular criteria method.

The following table provides a map of Groovy operators to methods:. The Groovy regex matching operators map onto like and ilike queries unless the expression on the right hand side is a Pattern object, in which case they map onto an rlike query:. A between criteria query can be done by combining the in keyword with a range:. Finally, you can do isNull and isNotNull style queries by using null with regular comparison operators:. Since the return value of the where method is a DetachedCriteria instance you can compose new queries from the original query:.

Note that you cannot pass a closure defined as a variable into the where method unless it has been explicitly cast to a DetachedCriteria instance. In other words the following will produce an error:. As you can see the closure definition is cast using the Groovy as keyword to a DetachedCriteria instance targeted at the Person class.

If you use a property name on both the left hand and right side of a comparison expression then the appropriate property comparison criteria is automatically used:.

The following table described how each comparison operator maps onto each criteria property comparison method:. Associations can be queried by using the dot operator to specify the property name of the association to be queried:. You can group multiple criterion inside a closure method call where the name of the method matches the association name:.

The following table shows which operator maps onto which criteria method for each size comparison:. If you define a query for an association an alias is automatically generated for the query. For example the following query:. These generated aliases are fine for most cases, but are not useful if you want to later sort or use a projection on the results.

For example the following query will fail:. If you plan to sort the results then an explicit alias should be used and these can be defined by simply declaring a variable in the where query:. By assigning the name of an association to a local variable it will automatically become an alias usable within the query itself and also for the purposes of sorting or projecting the results.

It is possible to execute subqueries within where queries. For example to find all the people older than the average age the following query can be used:.

You can apply additional criteria to any subquery by using the of method and passing in a closure containing the criteria:. Since the property subquery returns multiple results, the criterion used compares all results. For example the following query will find all people younger than people with the surname "Simpson":.

And support for aliases cross query references using simple variable declarations has been added to where queries:. There are several functions available to you within the context of a query. These are summarized in the table below:. Since each where method call returns a DetachedCriteria instance, you can use where queries to execute batch operations such as batch updates and deletes.

For example, the following query will update all people with the surname "Simpson" to have the surname "Bloggs":. Criteria is an advanced way to query that uses a Groovy builder to construct potentially complex queries. It is a much better approach than building up query strings using a StringBuilder. Criteria can be used either with the createCriteria or withCriteria closure methods. This criteria will select up to 10 Account objects in a List matching the following criteria:. Associations can be queried by having a node that matches the property name.

For example say the Account class had many Transaction objects:. We can query this association by using the property name transactions as a builder node:. The above code will find all the Account instances that have performed transactions within the last 10 days.

You can also nest such association queries within logical blocks:. Here we find all accounts that have either performed transactions in the last 10 days OR have been recently created in the last 10 days. Projections may be used to customise the results. Define a "projections" node within the criteria builder tree to use projections. There are equivalent methods within the projections node to the methods found in the Hibernate Projections class:. When multiple fields are specified in the projection, a List of values will be returned.

A single value will be returned otherwise. For this to work, all projections must have aliases defined, otherwise the corresponding map entry will not be built. We can also transform the result into an object of our choosing via the Transformers. Each alias must have a corresponding property or explicit setter on the bean otherwise an exception will be thrown. The first argument to the sqlProjection method is the SQL which defines the projections.

The second argument is a list of Strings which represent column aliases corresponding to the projected values expressed in the SQL.

The third argument is a list of org. Type instances which correspond to the projected values expressed in the SQL. The API supports all org. Each of the inner lists contains the 2 projected values for each Box , perimeter and area.

That query would return a single result with the value of 84 as the total area of all of the Box instances. The second argument represents the group by clause that should be part of the query.

That string may be single column name or a comma separated list of column names. The third argument is a list of Strings which represent column aliases corresponding to the projected values expressed in the SQL. The fourth argument is a list of org. The query above is projecting the combined heights of boxes grouped by width and would return results that look like this:. Each of the inner lists contains 2 values.

The first value is a box width and the second value is the sum of the heights of all of the boxes which have that width. A result iterator that allows moving around within the results by arbitrary increments. This allows full access to all the properties in this class. However, this can also be achieved using a criteria query:. Instead, use the select fetch mode:. Although this approach triggers a second query to get the flights association, you will get reliable results - even with the maxResults option.

An important point to bear in mind is that if you include associations in the query constraints, those associations will automatically be eagerly loaded.

For example, in this query:. Returns a unique result set i. The criteria has to be formed in a way that it only queries one row. This method is not to be confused with a limit to just the first row. If subqueries or associations are used one may end up with the same row multiple times in the result set.

This technique requires that each criteria must refer to the same domain class i. January 9, at am. February 10, at pm. Burt says:. May 9, at am. Maarten van Schouwen says:. August 30, at am. September 10, at pm. Leave a Reply Click here to cancel reply. One more approach for diagnosing spring-security-core login errors ». And of course this works for normal domain classes too, not just self-referential ones. Alternatively you could leverage cascade behaviour.

The cascade behaviour takes cares of deleting every orphan Review. Thus, invoking. A one-to-many relationship is when one class, example Author , has many instances of another class, example Book.

In this case we have a unidirectional one-to-many. GORM will, by default, map this kind of relationship with a join table. GORM will automatically inject a property of type java. Set into the domain class based on the hasMany setting. This can be used to iterate over the collection:. The default cascading behaviour is to cascade saves and updates, but not deletes unless a belongsTo is also specified:.

If you have two properties of the same type on the many side of a one-to-many you have to use mappedBy to specify which the collection is mapped:. This is also true if you have multiple collections that map to different properties on the many side:.

GORM supports many-to-many relationships by defining a hasMany on both sides of the relationship and having a belongsTo on the owned side of the relationship:. GORM maps a many-to-many using a join table at the database level. The owning side of the relationship, in this case Author , takes responsibility for persisting the relationship and is the only side that can cascade saves across.

This is the expected behaviour as, just like Hibernate, only one side of a many-to-many can take responsibility for managing the relationship. As well as associations between different domain classes, GORM also supports mapping of basic collection types. For example, the following class creates a nicknames association that is a Set of String instances:.

GORM will map an association like the above using a join table. You can alter various aspects of how the join table is mapped using the joinTable argument:. As well as associations , GORM supports the notion of composition. In this case instead of mapping classes onto separate tables a class can be "embedded" within the current table.

In the above example we have a parent Content class and then various child classes with more specific behaviour. At the database level GORM by default uses table-per-hierarchy mapping with a discriminator column called class so the parent class Content and its subclasses BlogEntry , Book etc.

Table-per-hierarchy mapping has a down side in that you cannot have non-nullable properties with inheritance mapping. However, excessive use of inheritance and table-per-subclass can result in poor query performance due to the use of outer join queries. The upshot of inheritance is that you get the ability to polymorphically query. For example using the list method on the Content super class will return all subclasses of Content :. By default when you define a relationship with GORM it is a java.

Set which is an unordered collection that cannot contain duplicates. In other words when you have:. The books property that GORM injects is a java. Sets guarantee uniqueness but not order, which may not be what you want.

To have custom ordering you configure the Set as a SortedSet :. In this case a java. SortedSet implementation is used which means you must implement java.

Comparable in your Book class:. The result of the above class is that the Book instances in the books collection of the Author class will be ordered by their release date.

To keep objects in the order which they were added and to be able to reference them by index like an array you can define your collection type as a List :. In this case when you add new elements to the books collection the order is retained in a sequential list indexed from 0 so you can do:.

When using a List , elements must be added to the collection before being saved, otherwise Hibernate will throw an exception org. HibernateException : null index column for collection :.

The static hasMany property defines the type of the elements within the Map. The keys for the map must be strings.

To ensure uniqueness when adding an entry to a Set association Hibernate has to load the entire associations from the database. If you have a large numbers of entries in the association this can be costly in terms of performance. The same behavior is required for List types, since Hibernate needs to load the entire association to maintain order.

Therefore it is recommended that if you anticipate a large numbers of records in the association that you make the association bidirectional so that the link can be created on the inverse side.

For example consider the following code:. In this example the association link is being created by the child Book and hence it is not necessary to manipulate the collection directly resulting in fewer queries and more efficient code.

Given an Author with a large number of associated Book instances if you were to write code like the following you would see an impact on performance:. If you are using Grails, then Grails automatically binds a Hibernate session to the currently executing request. This lets you use the save and delete methods as well as other GORM methods transparently.

If you are not using Grails then you have to make sure that a session is bound to the current request. One way to to achieve that is with the withNewSession Closure method:. Another option is to bind a transaction using the withTransaction Closure method:.

A useful feature of Hibernate over direct JDBC calls and even other frameworks is that when you call save or delete it does not necessarily perform any SQL operations at that point. Hibernate batches up SQL statements and executes them as late as possible, often at the end of the request when flushing and closing the session.

If you are using Grails this typically done for you automatically, which manages your Hibernate session. If you are using GORM outside of Grails then you may need to manually flush the session at the end of your operation. Hibernate caches database updates where possible, only actually pushing the changes when it knows that a flush is required, or when a flush is triggered programmatically. One common case where Hibernate will flush cached updates is when performing queries since the cached information might be included in the query results.

This can be a significant performance boost for applications that do a lot of database writes. Note that flushing is not the same as committing a transaction. If your actions are performed in the context of a transaction, flushing will execute SQL updates but the database will save the changes in its transaction queue and only finalize the updates when the transaction commits.

An example of using the save method can be seen below:. This save will be not be pushed to the database immediately - it will be pushed when the next flush occurs. But there are occasions when you want to control when those statements are executed or, in Hibernate terminology, when the session is "flushed". To do so you can use the flush argument to the save method:.

Note that in this case all pending SQL statements including previous saves, deletes, etc. This also lets you catch any exceptions, which is typically useful in highly concurrent scenarios involving optimistic locking :. Another thing to bear in mind is that GORM validates a domain instance every time you save it. If that validation fails the domain instance will not be persisted to the database.

By default, save will simply return null in this case, but if you would prefer it to throw an exception you can use the failOnError argument:. You can even change the default behaviour with a setting in application. An example of the delete method can be seen below:. As with saves, Hibernate will use transactional write-behind to perform the delete; to perform the delete in-place you can use the flush argument:. Using the flush argument lets you catch any errors that occur during a delete.

A common error that may occur is if you violate a database constraint, although this is normally down to a programming or schema error. The following example shows how to catch a DataIntegrityViolationException that is thrown when you violate the database constraints:. In order to perform a batch delete there are a couple of ways to achieve that. One way is to use a Where Query :.

It is critical that you understand how cascading updates and deletes work when using GORM. The key part to remember is the belongsTo setting which controls which class "owns" a relationship. If you do not define belongsTo then no cascades will happen and you will have to manually save each object except in the case of the one-to-many, in which case saves will cascade automatically if a new instance is in a hasMany collection.

Conversely if I later delete the Airport all Flight instances associated with it will also be deleted:. The above examples are called transitive persistence and are controlled via the belongsTo and the cascade policy. If I were to remove belongsTo then the above cascading deletion code would not work. It looks simple, right?

And it is. Just set the location property to a Location instance and you have linked an author to a location.

But see what happens when we run the following code:. An exception is thrown. As you can see from the code, we are setting the Author. Hence the instance is transient. The obvious fix is to make the Location instance persistent by saving it:. Another option is to alter the cascade policy for the association. There are two ways to do that. One way is to define belongsTo on the Location class:. Note that this above syntax does not make the association bidirectional since no property is defined.

A bidirectional example would be:. Alternatively if you prefer that the Location class has nothing to do with the Author class you can define the cascade policy in Author :. The above example will configure the cascade policy to cascade saves and updates, but not deletes. In the case of a bidirectional one-to-many where the many side defines a belongsTo then the cascade strategy is set to "ALL" for the one side and "NONE" for the many side. What this means is that whenever an instance of A is saved or updated.

So will any instances of B. And, critically, whenever any instance of A is deleted so will all the associated instances of B! Since the belongsTo is not defined, this means that saves and updates will be cascaded from A to B , however deletes will not cascade!

Only when you define belongsTo in B or alter the cascading strategy of A will deletes be cascaded. So exactly like the previous case of a undirectional One-to-Many, without belongsTo definition no delete operations will be cascaded, but crucially saves and updates will by default.

If you do not want saves and updates to cacade then you must alter the cascade policy of A :. You may be wondering why this association is a many-to-one and not a one-to-one. The reason is because it is possible to have multiple instances of B associated to the same instance of A.

If you wish to define this association as a true one-to-one association a unique constraint is required:. In this case the flights association will be loaded at the same time as its Airport instance, although a second query will be executed to fetch the collection. You can also use fetch: 'join' instead of lazy: false , in which case GORM will only execute a single query to get the airports and their flights.

This works well for single-ended associations, but you need to be careful with one-to-manys. At that point, you will likely end up with fewer results than you were expecting. The reason for this is quite technical but ultimately the problem arises from GORM using a left outer join. So, the recommendation is currently to use fetch: 'join' for single-ended associations and lazy: false for one-to-manys.

Be careful how and where you use eager loading because you could load your entire database into memory with too many eager associations. Rather than configuring join fetching as the default for an association, it may be better to alter the join strategy only for the queries that require it. This can be done using the fetch argument to most GORM methods:. Or using the join method when using Where Queries or criteria:. Although eager fetching is appropriate for some cases, it is not always desirable.

If you made everything eager you could quite possibly load your entire database into memory resulting in performance and memory problems. An alternative to eager fetching is to use batch fetching. You can configure Hibernate to lazily fetch results in "batches". In this case, due to the batchSize argument, when you iterate over the flights association, Hibernate will fetch results in batches of With batch fetching you get 1 query to fetch the Airport and 3 queries to fetch each Flight in batches of In other words, batch fetching is an optimization of the lazy fetching strategy.

Batch fetching can also be configured at the class level as follows:. By default GORM classes are configured for optimistic locking. Optimistic locking is a feature of Hibernate which involves storing a version value in a special version column in the database that is incremented after each update. The version column gets read into a version property that contains the current versioned state of persistent instance which you can access:. When you perform updates Hibernate will automatically check the version property against the version column in the database and if they differ will throw a StaleObjectException.

This will roll back the transaction if one is active. This is useful as it allows a certain level of atomicity without resorting to pessimistic locking that has an inherit performance penalty.

The downside is that you have to deal with this exception if you have highly concurrent writes. This requires flushing the session:. The way you deal with the exception depends on the application. You could attempt a programmatic merge of the data or go back to the user and ask them to resolve the conflict. This has the implication that other read operations will be blocking until the lock is released. In GORM pessimistic locking is performed on an existing instance with the lock method:.

GORM will automatically deal with releasing the lock for you once the transaction has been committed. To get around this problem you can use the static lock id method that takes an id just like get id :. As well as the lock id method you can also obtain a pessimistic locking using queries.

For example using a dynamic finder:. If you try to reload the instance using get id Hibernate will return the current modified instance from its Session cache. So GORM provides some methods to retrieve the original values that Hibernate caches when it loads the instance which it uses for dirty checking.

You can use the isDirty method to check if any field has been modified:. Dirty checking uses the equals method to determine if a property has changed. In the case of associations, it is important to recognize that if the association is a proxy, comparing properties on the domain that are not related to the identifier will initialize the proxy, causing another database query. If the association does not define equals method, then the default Groovy behavior of verifying the instances are the same will be used.

Because proxies are not the same instance as an instance loaded from the database, which can cause confusing behavior. It is recommended to implement the equals method if you need to check the dirtiness of an association.

You can use the getDirtyPropertyNames method to retrieve the names of modified fields; this may be empty but will not be null:. You can use the getPersistentValue fieldName method to retrieve the value of a modified field:. Depending on the complexity of the query you have the following options in order of flexibility and power:.

Use the list method to obtain all instances of a given class:. The list method supports arguments to perform pagination:. The second basic form of retrieval is by database identifier using the get id method:. You can also obtain a list of instances for a set of identifiers using getAll :. GORM supports the concept of dynamic finders. Instead, a method is auto-magically generated using code synthesis at runtime, based on the properties of a given class.

Take for example the Book class:. The Book class has properties such as title , releaseDate and author. The basic form is:. The tokens marked with a? Each comparator changes the nature of the query. In the above example the first query is equivalent to equality whilst the latter, due to the Like comparator, is equivalent to a SQL like expression. InRange - Between the from and to values of a Groovy Range. Notice that the last three require different numbers of method arguments compared to the rest, as demonstrated in the following example:.

You can combine as many criteria as you like, but they must all be combined with And or all Or. If you need to combine And and Or or if the number of criteria creates a very long method name, just convert the query to a Criteria or HQL query. In this case if the Author instance is not null we use it in a query to obtain all the Book instances for the given Author. The same pagination and sorting parameters available on the list method can also be used with dynamic finders by supplying a map as the final parameter:.

The where method builds on the support for Detached Criteria by providing an enhanced, compile-time checked query DSL for common queries.

The where method is more flexible than dynamic finders, less verbose than criteria and provides a powerful mechanism to compose queries. The closure should define the logical criteria in regular Groovy syntax, for example:. The returned object is a DetachedCriteria instance, which means it is not associated with any particular database connection or session.

This means you can use the where method to define common queries at the class level:. Query execution is lazy and only happens upon usage of the DetachedCriteria instance. If you want to execute a where-style query immediately there are variations of the findAll and find methods to accomplish this:.

Each Groovy operator maps onto a regular criteria method. The following table provides a map of Groovy operators to methods:. The Groovy regex matching operators map onto like and ilike queries unless the expression on the right hand side is a Pattern object, in which case they map onto an rlike query:. A between criteria query can be done by combining the in keyword with a range:. Finally, you can do isNull and isNotNull style queries by using null with regular comparison operators:.

Since the return value of the where method is a DetachedCriteria instance you can compose new queries from the original query:. Note that you cannot pass a closure defined as a variable into the where method unless it has been explicitly cast to a DetachedCriteria instance. In other words the following will produce an error:. As you can see the closure definition is cast using the Groovy as keyword to a DetachedCriteria instance targeted at the Person class.

If you use a property name on both the left hand and right side of a comparison expression then the appropriate property comparison criteria is automatically used:. The following table described how each comparison operator maps onto each criteria property comparison method:. Associations can be queried by using the dot operator to specify the property name of the association to be queried:. You can group multiple criterion inside a closure method call where the name of the method matches the association name:.

The following table shows which operator maps onto which criteria method for each size comparison:. If you define a query for an association an alias is automatically generated for the query. For example the following query:. These generated aliases are fine for most cases, but are not useful if you want to later sort or use a projection on the results.

For example the following query will fail:. If you plan to sort the results then an explicit alias should be used and these can be defined by simply declaring a variable in the where query:.

By assigning the name of an association to a local variable it will automatically become an alias usable within the query itself and also for the purposes of sorting or projecting the results. It is possible to execute subqueries within where queries. For example to find all the people older than the average age the following query can be used:.

You can apply additional criteria to any subquery by using the of method and passing in a closure containing the criteria:. Since the property subquery returns multiple results, the criterion used compares all results. For example the following query will find all people younger than people with the surname "Simpson":. And support for aliases cross query references using simple variable declarations has been added to where queries:.

There are several functions available to you within the context of a query. These are summarized in the table below:. Since each where method call returns a DetachedCriteria instance, you can use where queries to execute batch operations such as batch updates and deletes. For example, the following query will update all people with the surname "Simpson" to have the surname "Bloggs":. Criteria is an advanced way to query that uses a Groovy builder to construct potentially complex queries.

It is a much better approach than building up query strings using a StringBuilder. Criteria can be used either with the createCriteria or withCriteria closure methods. This criteria will select up to 10 Account objects in a List matching the following criteria:. Associations can be queried by having a node that matches the property name. For example say the Account class had many Transaction objects:. We can query this association by using the property name transactions as a builder node:.

The above code will find all the Account instances that have performed transactions within the last 10 days. You can also nest such association queries within logical blocks:. Here we find all accounts that have either performed transactions in the last 10 days OR have been recently created in the last 10 days. Projections may be used to customise the results.

Define a "projections" node within the criteria builder tree to use projections. There are equivalent methods within the projections node to the methods found in the Hibernate Projections class:. When multiple fields are specified in the projection, a List of values will be returned. A single value will be returned otherwise. For this to work, all projections must have aliases defined, otherwise the corresponding map entry will not be built. We can also transform the result into an object of our choosing via the Transformers.

Each alias must have a corresponding property or explicit setter on the bean otherwise an exception will be thrown. The first argument to the sqlProjection method is the SQL which defines the projections. The second argument is a list of Strings which represent column aliases corresponding to the projected values expressed in the SQL.

The third argument is a list of org. Type instances which correspond to the projected values expressed in the SQL. The API supports all org. Each of the inner lists contains the 2 projected values for each Box , perimeter and area.

That query would return a single result with the value of 84 as the total area of all of the Box instances. The second argument represents the group by clause that should be part of the query. That string may be single column name or a comma separated list of column names. The third argument is a list of Strings which represent column aliases corresponding to the projected values expressed in the SQL. The fourth argument is a list of org. The query above is projecting the combined heights of boxes grouped by width and would return results that look like this:.

Each of the inner lists contains 2 values. The first value is a box width and the second value is the sum of the heights of all of the boxes which have that width. A result iterator that allows moving around within the results by arbitrary increments. This allows full access to all the properties in this class.

Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Stack Gives Back Safety in numbers: crowdsourcing data on nefarious IP addresses. Featured on Meta.