A guide to mapping databases in Grails with GORM

Performing queries with domain objects

There are many ways to perform queries using domain objects, most of which will be familiar to J2EE or hibernate developers. GORM provides methods to list, find and get by convention on every domain object. Below we take a look at each in more detail.

How to get a single item by its primary key (ID)

To get a single item by its ID, we just use the static get method, which is provided automatically on the domain class. The example below would retrieve book with id 1

Listing all items in a domain object

A common case for domain objects is to provide a paged list of all records. In order to do this we need to provide an offset and limit to the list command, GORM handles this by taking a map of parameters, of which some common ones are listed below

• max the maximum number of results to return
• offset the offset to position the cursor in the results
• sort a field by which the results should be sorted
• order either asc or desc for ascending or descending.

An example that retrieves the first 10 rows of Books sorted by revision descending.

def list = Book.list(max:10, offset:0, sort: 'revision', order: 'desc');

An alternative way to specify which column to order by is by convention, one can simply use listOrderBy<property> for any property on the domain object. See the example:

def books = Book.listOrderByRevision(max:10)

Using finders to narrow down results

GORM also provides a few different ways to find data using a query; here we will discuss the most common ways to do this. Firstly, one can use findAll to find a collection of domain objects and find to retrieve a single item by query. Both methods take a hibernate HQL statement and an optional list of parameters as per list. Here's an example of each method:

// find first 10 books with title like grails 
Book.findAll("from Book where title ilike 'grails'", [max: 10, offset: 0] )
// find first book with title exactly grails.
Book.find("from Book where title = 'grails'")

There's also another way to do this, that uses Groovy's dynamic method support. Domain objects provide a version of findBy and findAllBy that are dynamically built, they can include property names and several conditions. We will not cover all the cases, but here's how it works in practice.

findAllBy<PropertyName>[<Conditions><PropertyName>]

Example conditions:

• And the two clauses are combined with and
• Or the two clauses are combined with or
• IsNull added after property, to check for null
• IsNotNull added after property, to ensure not null
• Like added after property to check using LIKE
• Ilike added after property to check using ILIKE

..and lastly a couple of examples:

// find first 10 by title like grails
Book.findAllByTitleIlike('%grails%', [max:10, offset:0])
// find first book with title grails.
Book.findByTitle("grails");

Further reading

We have got you up and running with GORM, there's a lot more to learn about it, and many extra features that we have not covered. However, you should now be in a good position to get started with GORM then read the reference manuals on the grails website when you need to know more.