Led & Sustained by

G2one Logo

Developed with

Intellij

Powered by

Spring

GORM - Querying

Domain Class Querying

Querying with dynamic methods

There are several ways to query for domain class instances, one of them being via Grails dynamic methods. For more details see DomainClass Dynamic Methods:

def results = Book.findByTitle("The Stand")

results =
   Book
    .findByTitleLike("Harry Pot%")
results =
  Book
    .findByReleaseDateBetween( firstDate, secondDate )
results =
  Book
    .findByReleaseDateGreaterThan( someDate )
results =
  Book
   .findByTitleLikeOrReleaseDateLessThan( "%Something%", someDate )

// find by relationship
results = Book.findAllByAuthor( Author.get(1) )

// and to affect some sorting
results =
  Book
   .findAllByAuthor( Author.get(1), [sort:'title',order:'asc'] )

Querying by example

Just pass an example of the domain object you would like to find to the find() method.

def b = Book.find( new Book(title:'The Shining') )

Querying with a criteria builder

For more advanced queries or querying across objects graphs you can use Criteria (for a full reference see the section on Builders):

def c = Book.createCriteria()
def results = c {
     like("author.name", "Stephen%")
     between("releaseDate", firstDate, secondDate )
}

Querying with HQL queries

Otherwise, as Grails uses Hibernate internally you can use an HQL query:

def results =
      Book.findAll("from Book as b where b.title like 'Lord of the%'")

Or with positional parameters:

def results =
      Book.findAll("from Book as b where b.title like ?", ["The Shi%"])

Or with named parameters:

def results =
      Book.findAll("from Book as b where b.title like :search or b.author like :search", [search:"The Shi%"])

You cannot combine named parameters with positional parameters.

Be careful with Groovy multi-line strings: if they contain any newlines, then the query will not work. So for example this:

def results = Book.findAll("""
from Book as b,
     Author as a
where b.author = a and a.surname = ?""", ['Smith'])

will not work. You have to use the line-continuation character:

def results = Book.findAll("""\
from Book as b, \
     Author as a \
where b.author = a and a.surname = ?""", ['Smith'])

Note The space after "Author as a" is important, otherwise the string would look like "...Author as awhere b.author...".

Querying with Eager Fetching

Sometimes is desireable to query objects that relate to each other without lazy=true , so SQL is optimized and there is no n+1 SQL SELECT queries. To do that you can:

1) Add LEFT JOIN FETCH to your HQL query

2) You can also try with Criteria Builder, with something like this:

import org.hibernate.FetchMode as FM
// ......

def c2 = Task.createCriteria()
def tasks = c2.list{
     eq("assignee.id", task.assignee.id)
     maxResults(new Integer(params.max))
     firstResult(new Integer(params.offset))
     fetchMode('assignee', FM.EAGER)
    fetchMode('project', FM.EAGER)
    order('priority', 'asc')
}

3) You can use hibernate .hbm files to mapp relations using lazy=false.

</