GORM Query Patterns: Dynamic Finders vs Criteria vs Where Queries in Grails 4–6

The problem: too many ways to query

Grails developers often reach for Book.findByAuthorAndTitleLike(...) because it reads like English. But that convenience hides runtime method resolution, unpredictable SQL, and a deprecation path. Meanwhile, the Criteria API and the newer where queries offer type safety and explicit fetch control. Choosing the right style affects query performance, maintainability, and how easily you can verify the generated SQL.

Dynamic finders: convenient but opaque

Dynamic finders are resolved via methodMissing at runtime. They translate to Hibernate criteria under the hood, but you cannot see the SQL until you enable logging. Chaining associations (e.g., Book.findByAuthorNameAndPublisherName) often produces separate selects for each relation — the classic N+1 problem. Since GORM 7 (Grails 5+), dynamic finders are deprecated for removal; new code should avoid them.

Criteria API: explicit control and fetch modes

The Criteria DSL (createCriteria().list { ... }) is type‑safe and supports projections, aliases, and fetchMode. Using fetchMode('author', FetchMode.JOIN) eagerly loads the association in a single query, eliminating N+1 selects. You can also set default fetch behavior in the domain mapping block (static mapping = { author fetch: 'join' }), but per‑query overrides are safer when multiple collections are involved to avoid Cartesian products.

Where queries: compile‑time checked middle ground

Introduced in GORM 7, where queries (Book.where { author.name == 'King' && title =~ '%Shining%' }.list()) are parsed at compile time in Groovy 3+. They map cleanly to criteria, support the same fetch options, and give you IDE feedback on property names. They are the recommended replacement for dynamic finders in Grails 5+ applications.

Worked example: fetching books with authors

Assume a simple domain model:

class Author { String name; static hasMany = [books: Book] }
class Book { String title; Author author; static belongsTo = [author: Author] }

We want all books by author "King" with the author eagerly loaded. Three styles:

Style	Code	Expected SQL behavior
Dynamic finder	Book.findByAuthorName('King')	Two selects: one for books, one for each author (N+1)
Where query	Book.where { author.name == 'King' }.list()	Single select with inner join (if fetch not specified, may still lazy‑load)
Criteria with fetchMode	Book.createCriteria().list { createAlias('author','a'); eq('a.name','King'); fetchMode('author', FetchMode.JOIN) }	Single select with explicit join, no lazy load

To verify, enable SQL logging in application.yml:

logging:
  sql: debug
hibernate:
  generate_statistics: true

Run the queries inside a @Transactional(readOnly = true) service method and inspect the Hibernate statistics:

def sessionFactory
@Transactional(readOnly = true)
void compare() {
  def stats = sessionFactory.statistics
  stats.clear()
  Book.findByAuthorName('King')
  println "Dynamic finder loads: ${stats.entityLoadCount}"
  stats.clear()
  Book.where { author.name == 'King' }.list()
  println "Where query loads: ${stats.entityLoadCount}"
  stats.clear()
  Book.createCriteria().list { createAlias('author','a'); eq('a.name','King'); fetchMode('author', FetchMode.JOIN) }
  println "Criteria loads: ${stats.entityLoadCount}"
}

Expected checks: entityLoadCount should be 1 for the criteria version (book + author in one row), higher for the others. Risk: enabling generate_statistics in production adds overhead; keep it for development only.

Trade‑off: joining multiple collections

If you add fetchMode('tags', FetchMode.JOIN) alongside the author join, Hibernate produces a Cartesian product (book × author × tags). The row count explodes. Instead, use @BatchSize(size: 20) on the tags collection or run a separate query for tags. This limitation applies to both criteria and where queries.

Pagination: offset vs keyset

Both styles support max/offset (criteria uses firstResult). For large offsets (e.g., page 500 of 10,000 rows), offset pagination scans and discards rows. Keyset pagination (where { id > lastSeenId }) uses an index seek and is faster, but requires manual query construction and a stable sort key. Example keyset with where query:

Book.where { id > lastId && author.name == 'King' }.list(max: 20, sort: 'id')

Always run pagination queries in a read‑only transaction to avoid dirty‑checking overhead on large result sets.

How to verify in a fresh project

1. Create a Grails 6 app: grails create-app demo && cd demo
2. Add domain classes: grails create-domain-class Book and grails create-domain-class Author
3. Configure the one‑to‑many, enable SQL logging as above.
4. Run ./gradlew bootRun and hit a test endpoint that executes the three query styles.
5. Check ./gradlew dependencies | grep gorm to confirm gorm-hibernate5 matches the Grails BOM.

These steps require write access to the project directory and a JDK 11+ installation. No database setup is needed if you use the default H2 in‑memory database.

Actionable closing

Stop writing new dynamic finders. Migrate existing ones to where queries for compile‑time safety, and use the Criteria API when you need explicit fetch modes or complex projections. Enable SQL logging and Hibernate statistics in every development environment — they are the only reliable way to see what GORM actually sends to the database. For pagination beyond a few hundred rows, implement keyset pagination and wrap the query in a read‑only transaction.