New Columns

Probably the first things you’ll notice are the auto-generated primary key ID and the version. The ID is always used as the reference for the primary key; you can access it off the domain object at any time. The ID ties the particular detached domain object to the persisted one. Hibernate also uses a version field for transactional integrity and to support optimistic locking. This works much like ActiveRecord in Rails, which allows you to have the primary key and version created for you; unfortunately, the downside is that you cannot overwrite the primary key by hand.

Naming

Next, notice that the table name and column names are assigned. The names that were in CamelCase before are now switched to snake_case (lowercased and separated by an underscore).

Foreign Keys

In most databases, foreign keys or foreign key indexes are created between the User and Category tables. GORM sets up the foreign key by taking the class that has the belongsTo reserved word—in this case, the Category class has belongsTo = User—and saving the user_id column in the category table to the database. The user_id column is the concatenation of the belongsTo table name with _id.

Data Type

For the most part, data types are derived from the types you set in the domain itself. So Strings become VARCHARs, Dates become DATETIMEs, and so on. Of course, the exact type is dependent on the actual database you’re using and the constraints applied to the property.

Setting Default Values

Many applications have a default value to store in the database if you don’t select one for them. Setting a default value is easy in GORM; simply set the value as you would to a normal domain object. In general, a status usually has a starting value. In the Collab-Todo application, your value will not be "Completed" from the get-go, so start the status at Started. If the user wants to change this upon creation, he can. Listing 6-2 showcases the updated change.

Listing 6-2.  The Todo with a Default Value for Status

class Todo {

    static belongsTo = [User, Category]

    User owner
    Category category
    String name
    String note
    Date createdDate
    Date dueDate
    Date completedDate
    String priority
    String status = "Started"

    ...
}

Large Object Types

Before we move on to relationships, let’s discuss the treatment of large object types. Large objects are generally binary large objects (BLOBs) and character large objects (CLOBs) that get persisted to the database. Although we’re storing objects to the database as an example and will be using it to highlight features in Chapter 8 on downloading and uploading files, we don’t necessarily suggest using a database to store files. Files can be very large, and unless you need to version those files, it is unwise to waste the database as a storage place. Instead, use the file system to keep files. Remember that the point of a file system is to store files.

Of course, there are legitimate reasons to store files in the database, so if you want to store BLOBs, simply set the datatype to byte[]. Note that we’ve added an item to Todo called associatedFile. When you update the database in MySQL, you will notice that the object type created is TINYBLOB. In addition, setting the maxSize on the constraints produces a longtext column in the database when using MySQL.

Players Involved

As you saw in Chapter 4, creating relationships is quite easy. However, we’ll provide an example for those who need a refresher. You’ll need to modify only two pieces of code in your classes. You’ll use the following two keywords:

• hasMany
• belongsTo

The next section shows examples of these in action.

class User {
    ...
    Address address
}
class Address {
    User user
}

class User {
    ...
    static hasMany = [buddyLists: BuddyList]
}
class BuddyList {
    static belongsTo = User
}

class BuddyList {
    static hasMany = [members: BuddyListMember]
    ...
}

class BuddyListMember {
    static belongsTo = BuddyList
}

Managing Relationships

Adding to the relationships is quite easy and makes use of Groovy dynamic syntax. Listing 6-8 shows how to add and remove members from BuddyList.

Listing 6-8.  Adding BuddyList to and Removing BuddyList from BuddyListMember

BuddyList myList
myList.addToMembers(someMember)
myList.removeFromMembers(someMember)

class Todo {
    static hasMany = [keywords: Keyword]
}
class Keyword {
    static belongsTo = Todo
    static hasMany = [todos: Todo]
}

Adjusting the Mappings

GORM allows you to adjust names of the tables and columns on the database itself in a DSL way without the need for extra configuration files, as Hibernate requires. To demonstrate, we’ll customize the Todo domain. By the end, you’ll see those changes reflected in the domain.

To make these adjustments, you need to add a static mapping closure to the domain object. The additional code simply adds to those static mappings. Check out the adjustment to Todo in Listing 6-10.

Listing 6-10.  Todo with the Mapping Closure Defined

class Todo {
    ...
    static mapping = {
        // insert mappings here
    }
}

Now you can start adding to the static mappings, slowly growing the mapping for added functionality.

table 'todo_tbl'

columns {
            name column:'name_str'
            note column:'note_str'
        }

id generator:'hilo', params:[table:'hi_value',column:'next_value',max_lo:100]

id composite:[create 'name','dueDate']

static mapping = {
     version false
}

class User {
    static mapping = {
        columns {
            address lazy:false
        }
    }
}

class Todo {
static mapping = {
        columns {
            name index:'Name_Idx, Name_Create_Date_Idx'
            createDate index:'Name_Create_Date_Idx'
        }
    }
}

class Admin extends User {
    String department
}

static mapping = {
    tablePerHierarchy true
}

static mapping = {
    tablePerHierarchy false           // table-per-subclass
}

Turning on the Cache

One of the big pluses with Hibernate is its ability to use second-level caching, which stores data associated with the domain class to the Hibernate cache. The advantage to this is that if you retrieve the data, it will pull it from the cache instead of making a call to the database. This is a great mechanism for retrieving data that is accessed often and is rarely changed. To configure the second­level cache, you have to follow a few steps. First, update the DataSource.groovy file, then update the domain object in question.

Configuring the cache in the data source is easy. Add the code from Listing 6-14 into your DataSource.groovy file.

Listing 6-14.  The Entry to Initialize the Hibernate Second­Level Cache

hibernate {
    cache.use_second_level_cache=true
    cache.use_query_cache=true
    cache.provider_class='org.hibernate.cache.EhCacheProvider'
}

This tells Hibernate that you want to use second-level caching and that the provider you’ll use is EhCacheProvider. You can swap this line out with another provider if you like; this just happens to be the de facto Hibernate cache.

Next, initialize the cache for the individual domain objects with which you want to associate it. Initialize it for Todo by inserting the cache entry into the mapping constraints. Add the following line to your mapping:

static mapping = {
    ...
    cache true
}

When using true, it signals that the object should be stored in the cache and it will configure a “read-write” cache that includes both lazy and non-lazy properties. However, you can further adjust the cache settings to be read-only, transactional, or non-lazy. To do this, you use a similar entry to the previous one, but with more specifics:

cache usage:'read-only', include:'non-lazy'  // or cache usage: 'transactional'

Besides configuring the cache at the class level, you can also configure it for embedded classes at the domain level. The configuration is virtually identical to what you did previously, except you specify the column that will be cached, as shown in Listing 6-15.

Listing 6-15.  Configuring the Cache for the Address on the User

class User {
    static mapping = {
                address cache:true
    }

Transient Properties

In JPA and GORM, all the properties on your domain (or entity) objects are persisted to the database by default. This can be disadvantageous when you want to have properties on your domains that either are amalgamations of other properties or simply don’t need to be persisted. Luckily, in GORM, you can easily mark properties to not be persisted.

In GORM and JPA, properties that you don’t want to persist are called transient properties. In JPA, you mark each property with the @Transient annotation. In GORM, you create a transient mapping of all these properties.

For the example, use the User table. Inside User, you want to have a confirmPassword property, so you can make sure the user typed in the correct password. Obviously, you don’t need the password persisted twice, so you mark it as transient. In Listing 6-16, you can see that you add confirmPassword to the transients mapping.

Listing 6-16.  User Object with the Transient confirmPassword

class User {
    static transients = [ "confirmPassword" ]

    String firstName
    String lastName
    String userName
    String password
    String email
    String confirmPassword
}

As you can see, you still add the confirmPassword to the normal domain, but you also add it to a list of strings called transients, marking it as transient.

GORM Events

Often in normal domain and DAO architectures, you could have base save/update/delete methods. However, if you ever want to add a universal concept for a particular domain, such as updating timestamps or writing to a log, you could overwrite the method, add your own custom needs, and then call the parent method. However, GORM and some of these other more modern frameworks eliminate the need for the DAO classes and allow you to access the domain class directly. However, this can pose a problem, because now the individual developers are responsible for updating fields (such as a last-modified date) that always need to be run when updating, and writing to the log file. Yuck; this could lead to disaster.

Luckily, GORM has an answer to this problem. It gives you the ability to define these types of transitions at the domain level. Todo has lastModifiedDate and createDate. In GORM, you can add two methods to the domain that are automatically called before inserting and before updating the database. Obviously, the beforeInsert method is called before insertion, and beforeUpdate is called before updating. Listing 6-17 shows the code you need to add to Todo in order to have your dates modified at insertion and creation.

Listing 6-17.  Code for Adding the Automatic Events for Todo

def beforeInsert = {
   createDate =newDate()
    lastModifiedDate = new Date()
}
def beforeUpdate = {
   lastModifiedDate =newDate()
}

Now whenever a save or update is called, these two methods are called regardless from where the save or update is called from. In addition, a beforeDelete event is called before a deletion.

Do you detect a pattern here? Having create and modify dates is a fairly standard pattern. In order to have this same functionality on more than one class, you would have to add those seven lines of code plus the two lines to define the domains in each of your domain classes.

Luckily, as you may have guessed, GORM has a way around this. If you have lastUpdated and dateCreated named properties, you can configure GORM to automatically update them by adding the following line in the mapping:

static mapping = {
        ...
        autoTimestamp false
}

Note that the properties have to be named lastUpdated and dateCreated. In addition, lastUpdated only gets updated when the domain is actually updated and not on a creation like before.

Finally, there is one other way to adjust items, and that is on the actual loading of the item, or rather after the item has been loaded and all the properties have been set. Listing 6-18 contains a method that displays a message upon loading the domain object.

Listing 6-18.  Method Called upon Loading the Domain

def onLoad = {
    print 'We have loaded the item'
}

Database Model

Before moving onto validation, let’s see how all the updates to the domain model affect a MySQL database.

In Figure 6-3, you can see the output of the database structure after being created by GORM.

Using Built-In Constraints

You can add many different constraints to your domain object, including everything from null checking to credit-card validation. In our application, we will use quite a few of these validations, although there’s no way for us to use them all. Table 6-2, however, shows all the possible constraints provided by GORM.

Of course, it is impossible for the Grails team to predict all the possible constraints needed by an application. Using pre-created constraints is the easy part. In the next section, we’ll show you how to create your own customized constraints using closures.

Creating Custom Constraints

Let’s take another look at that Todo object and what other constraints you need to define for it. Let’s examine the startDate property and see what you need to constrain on it. For starters, you don’t want to allow users to use start dates in the past. The purpose of this application is to create tasks that start now or in the future; in theory, this is something that is probably created at the beginning and never changed. Nothing in the built-in constraints shown in Table 6-2 does what you need, so you need to create your first custom constraint. You need to allow the constraint to be null, and if the constraint is filled in, you need to make sure the date doesn’t occur in the past.

To define custom constraints in GORM, mark the custom constraint with a validator. Take a look at this in action in Listing 6-20.

Listing 6-20.  Applying a Simple Custom Validator to Todo

static constraints = {
    ...
    startDate(nullable:true,
        validator: {
            if (it?.compareTo(new Date()) < 0 ) {
                return false
            }
            return true
     })
}

As you can see, this starts off like normal validations with nullable:true, but then it adds custom validation. All custom validators start with the word validator, followed by a closure. Inside the closure, you need to return true if the validation passes, and false if the validation doesn’t pass. Of course, you cannot create a decent validation with a random closure that has no information. However, that is why you have access to the property, demarked with it, inside the validator closure. The it represents the item that is being validated. For our validation, we are going to check that the start date doesn’t occur before the current date. As you can see, it’s pretty easy to create a custom validation, but you can make it even more advanced.

For this next example of functionality, let’s take a look at the completeDate field. It makes sense that completeDate has to occur after startDate. To create a custom validator to show this, you need to have access to the startDate object. No problem. Check out its implementation in Listing 6-21.

Listing 6-21.  Applying aMore Complex Validator to Todo

static constraints = {
    ...
COMPLETEDATE(NULLABLE:TRUE,
        validator: {
            val, obj ->
                if (val != null) {
                    return val.after(obj.createDate)
                }
                return true
        })
}

As you can see, this looks similar to the validator you defined in Listing 6-20, with one small change: you also pass in the variables val, obj ->. val is the variable that is passed in; the previous example, it represented the value of startDate. In addition, you’re now also passing in obj, which is the domain object you’re using—in this case, the Todo object. This allows you to compare completeDate directly with that domain’s createDate.

This gives you a fairly dynamic way of creating validations. In fact, you can even create queries inside the validator, allowing for some slick validations. Let’s take a look at the Keyword domain, which is one of the constraints. To reduce redundancy, you want to put a constraint on the name, because you don’t want to have any names that are also used as names for the description. You can easily make the name a unique field, but you also want to make sure that no description is the same as the name. If there is, you’re probably creating a name twice or not properly defining the other one. Listing 6-22 shows how to find all the instances of description to make sure it doesn’t have the same text as the name property.

Listing 6-22.  A Custom Constraint That Performs aQuery

class Keyword {
    ...
    String name
    String description
    static constraints = {
        name(validator: {
            if (Keyword.findAllByDescription(it).size() > 0) {
                return false
            }
            return true
        })
    }
}

GORM’s Dynamic Queries

As you just saw, creating dynamic CRUD queries is fairly easy. However, you’re only able to do a simple retrieval based on the primary key (the ID). While this is necessary to most applications, you obviously need to do more than that. In the example application, you’ll need to retrieve not only lists, but also lists of people and ever more specific queries.

In the upcoming sections, we’ll go over multiple types of queries, ranging from fairly simple single finds to lists, criteria queries, and HQL queries. The criteria queries will make use of Groovy the most by allowing you to create a DSL of the query you want to create. This makes for some wonderfully easy queries to create. The downside is, unlike with the HQL queries, the criteria queries are limited to querying off only one class.

We’ll start by showing you how to grow some single result-set queries, and then we’ll go over how they work. We’ll list the options for creating the queries, and finally, we’ll show you how to create the query lists.

Todo.count()

Todo.countByPriority('1')

Todo.findByPriorityAndStatus("2", "3")

Todo.findWhere([ "priority": "1", status: "2"])

Todo.findAllByName("Our First Web App")
Todo.findAllByPriorityOrStatus("2", "4")

Todo.findAllWhere([ "priority": "1", status: "2"])

Todo.getAll(1,3,5)
Todo.getAll([1,3,5])

Todo.list()

Todo.listOrderByName()

Todo.list(max: 10, offset: 20, sort: "priority", order "desc")

HQL Queries

The previous methods for query creation allowed you to use powerful DSLs to create simple user queries. However, using these queries is like eating sushi—an hour later, you’re hungry for more. These GORM dynamic queries could not perform anything too complex, such as ranges, and more importantly, they can only query off themselves.

In many applications, you not only need to query other tables to get the data, but you often want bits and pieces of the data back—for example, a few columns from table A mixed with a few columns from table B. With HQL queries, you can perform this task easily.

Once again, if you’re familiar with Hibernate, this will be second nature to you. However, if you’re new to Hibernate, understanding HQL is simply realizing that you’re creating a query based on what the domain says as opposed to what is actually in the database (like in an SQL query).

In the “GORM’s Dynamic Queries” section, we went over two sets of queries: returning one result set and returning multiple result sets. With HQL queries, you have the same scenario, plus a more general query mechanism with executeQuery:

• find
• findAll
• executeQuery

1. Todo.find("From Todo as t order by t.priority asc")
2. Todo.find("From Todo as t
                       where t.name = ?
                       and t.priority = ?
                       order by t.priority asc", ["Test", "2"])
3. Todo.find("From Todo as t
                       where t.name = :name
                       and t.priority = :priority
                       order by t.priority asc", [priority :"2", name : "Test"])
4. def todo = new Todo()
    todo.name = "Test"
    todo = Todo.find(todo)

Todo.findAll("From Todo t", max: 10, offset: 20, sort: "priority", order "desc")

Todo.findAll("From Todo t where t.priority = ?",
                        ["1"], max: 10, offset: 20, sort: "priority", order "desc")

Todo.executeQuery("select t.name from Todo t where t.priority = ? ", "1")

Hibernate’s Criteria Queries

If you’ve ever worked with Hibernate, you’re probably familiar with Hibernate’s Criteria API. Perhaps you tried to do some of the concepts of projections and so on, but got confused. Sometimes the simplest criteria query seems overly complex. For those of you not familiar with the Criteria API, it’s a Hibernate API designed to provide elegance to creating dynamic queries. You might be wondering why this is necessary. Well, let’s think back to the HQL queries we previously created.

What if you want to create a dynamic query? Doing so would require multiple dynamic where clauses, which would require you to do multiple string concatenations and a bunch of if-then-else statements. Yuck! That just gets messy fast, and lends itself to easy runtime SQL errors—and that’s never a good thing. Using the Criteria object allows you to abstract away creating the query and make it in a readable DSL way.

As we said, creating the Criteria queries in pure Hibernate is a bit of a pain; however, with the Groovy language, GORM has created some fairly smooth ways for creating these queries. In this section, we’ll go over how to create advanced Criteria queries. First, we’ll show a small example that demonstrates the difference between creating a query with Criteria and creating a query with HQL.

Our first example is a relatively simple problem that you could have with any web application—even ours. Take Todo—what if you want to search based on the note, the description, or another field? This requires you to create a dynamic query. You need to store the possible values in the map where the key is the name of the field and where the value is the value.

We’ll build this in a few steps to make this as easy as possible. First, we’ll create the base test methods for TodoTest. Next, we’ll show you the implementation of this logic in HQL. Finally, we’ll show you how to do this the proper way in Criteria.

You’ll create a query that can take in this map and do multiple ands of it on the key/value pair. Listing 6-38 shows the parameters to pass through to the query runners.

Listing 6-38.  The Entry Test Case

void testFindingTodosWithHQL() {
    def params = [ name: '%Second%', status: '4' ]
    def todos = executeHQLQuery( params )
    assert todos[0].name == "Our Second Web App"
}

void testFindingTodosWithCriteria() {
    def params = [ name: '%Second%', status: '4' ]
    def todos = executeCriteriaQuery( params )
    assert todos[0].name == "Our Second Web App"
}

These tests are relatively simple; they look on the Todo list for a name with the word "Second" in it and a status of 4. With our sample data, this should only return one record with the name "Our Second Web App". This is the easy part. Now let’s take a look at how to implement this for an HQL query. Listing 6-39 defines executeHQLQuery.

Listing 6-39.  The Dynamic HQL Query

List executeHQLQuery(def params) {

    def hqlQuery = "Select t From Todo t "

    if (params.size() > 0) {
        def first = true
        params.each { key, value ->
        if (first) {
            hqlQuery += ' where '
        } else {
            hqlQuery += ' and '
        }
        first = false
        hqlQuery += " t.${key} like :${key} "
        }
        }
        return Todo.executeQuery(hqlQuery, params)
    }

We won’t try to claim that this is the only way of creating the necessary query, but it is one of the ways. The solution contains multiple steps:

1. Create the select—in this case, Select t From Todo t.
2. Check whether there are any parameters. This is necessary because you don’t want a where clause if there are no parameters.
3. Add where or and depending on whether it’s the first or subsequent property you’re selecting.
4. Add the comparison. The key is the name of the property, and the value is the value to compare against. You see the word key twice, because the second instance will be replaced by a prepared statement when executeQuery is called.
5. Execute the query, pass in the supplied parameters, and voilà.

If you look at that code and explanation, you’ll see that it’s not a pretty way of performing that query. Luckily, Hibernate has an easier solution: the Criteria query. With this, you can write dynamic queries without ever having to write any SQL or HQL code. And with Groovy, this gets even easier, because you get to use builders to create your Criteria query instead. Listing 6-40 defines the method you’ll use for creating the Criteria query.

Listing 6-40.  The Criteria Query

List executeCriteriaQuery(def params) {
        def todos = Todo.createCriteria().list {
            and {
                params.each {key, value ->
                    like(key, value)
              }
            }
        }
    }

Not only does this look better, but it’s much easier to read as well. Here’s the breakdown for this one:

1. Create a dynamic query on the Todo domain.
2. Use and to define a closure that then allows you to iterate through a list of expressions.
3. Set like with a name/value pair without any formatting.

As you can see, this is much easier than creating a dynamic HQL query. Once you’re familiar with creating Criteria queries in your average Java code, you’ll see that the ability to use the Groovy builder with closures is cleaner and more robust. This will become even more apparent when we increase the complexity of our Criteria examples throughout the book.

Detached Criteria

Detached Criteria, commonly used to create common reusable criteria queries, are not associated with any given database session/connection and are constructed using a grails.gorm.DetachedCriteria class that accepts a domain class as the only argument to its constructor:

import grails.gorm.*
...
def criteria = new DetachedCriteria(User)

You can execute criteria queries or where queries (discussed in the next section)on a detached criteria instance to build up the appropriate query. To build a normal criteria query, you can use the build method as illustrated in Listing 6-41.

Listing 6-41.  Building the Criteria

def criteria = new DetachedCriteria(User).build {
  eq 'lastName', ''Smith'
}

You can also execute dynamic finders on DetachedCriteria just like on domain classes, as illustrated in Listing 6-42.

Listing 6-42.  Executing Dynamic Finders on Detached Criteria

def criteria = new DetachedCriteria(User).build {
    eq 'lastName', 'Smith'
}
def bart = criteria.findByFirstName("John")

Where Queries

The where queries provide compile-time checked query DSL for common queries. The object returned by the where query is a Detached criteria instance, which makes it possible to define common queries at the class level, or execute batch operations such as batch updates and deletes.

The where method accepts a closure that defines the logical criteria, as illustrated in 6-43.

Listing 6-43.  Using a where Query

def query = User.where {
   firstName == "John"
}
Person vishal = query.find()

Listing 6-44 illustrates using the where method to define queries at the class level.

Listing 6-44.  Using the where Method to Define Queries at the Class Level

class User{
    static smiths = where {
         lastName == "Smith"
    }
    ...
}
...
User.smiths.each {
    println it.firstname
}

Listing 6-45 illustrates using the where method to execute batch operations.

Listing 6-45.  Using the where Method to Execute Batch Operations

def query = Person.where {
    lastName == 'Simpson'
}
int total = query.deleteAll()