Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 39 ยท HQL, Criteria and Native Queries

Native SQL, Named Queries and Projections

Run native SQL from Hibernate, map results to entities and records, declare named queries, and page efficiently with offset and keyset pagination - with runnable H2 stand-ins.

HQL covers most needs, but sometimes you need the database itself: a window function with a vendor hint, a recursive query, a full-text search operator, or a hand-tuned report. For those cases Hibernate lets you send native SQL and still map the result to entities or DTOs. This lesson also covers named queries (validated at startup) and the two ways to page through large result sets.

Hibernate itself is not available here, so its code is read-only. The runnable programs use JDBC and H2 to demonstrate the same ideas: mapping rows to records, a registry of named queries, and offset versus keyset paging.

Native queries

Java
// to an entity: select every column the entity needs
List<Book> books = session.createNativeQuery(
        "select * from books where unit_price < :max", Book.class)
    .setParameter("max", new BigDecimal("20"))
    .getResultList();

// to scalars or tuples
List<Object[]> rows = session.createNativeQuery(
        "select author_id, count(*) from books group by author_id", Object[].class)
    .getResultList();

// to a record (Hibernate 6+): column labels must match component names
record Stat(long authorId, long total) {}
List<Stat> stats = session.createNativeQuery(
        "select author_id as authorId, count(*) as total from books group by author_id", Stat.class)
    .getResultList();

Rules to remember:

  • A native query bypasses HQL's portability: if you switch databases, you rewrite it.
  • When the result type is an entity, the SQL must return all columns of its table (including the id and discriminator, if any), or you get "column not found" errors.
  • Native queries still take named parameters, and Hibernate binds them safely.
  • Hibernate does not know which tables a native query reads. Before the query runs it flushes the whole session, unless you tell it which tables are involved with addSynchronizedEntityClass(Book.class). The same applies to cache invalidation for native updates: use addSynchronizedQuerySpace so cached results of the touched entities are evicted.

Native DML works with executeUpdate(), and like bulk HQL it skips the persistence context.

Mapping to many shapes

Hibernate offers several mapping tools for native SQL:

Tool Use it when
createNativeQuery(sql, Entity.class) all entity columns are returned
createNativeQuery(sql, Record.class) the result is a flat DTO
@SqlResultSetMapping + @ConstructorResult you need one reusable mapping with many columns, or a mix of entity and scalars
.setTupleTransformer(...) / Tuple.class custom assembly of results
@NamedNativeQuery the SQL should be declared once and checked early

Example with an explicit mapping:

Java
@SqlResultSetMapping(name = "BookSummaryMapping",
    classes = @ConstructorResult(targetClass = BookSummary.class, columns = {
        @ColumnResult(name = "title", type = String.class),
        @ColumnResult(name = "unit_price", type = BigDecimal.class)}))
@NamedNativeQuery(name = "Book.summaries",
    query = "select title, unit_price from books order by title",
    resultSetMapping = "BookSummaryMapping")
@Entity
class Book { /* ... */ }

List<BookSummary> list = session.createNamedQuery("Book.summaries", BookSummary.class).getResultList();

The next program maps a result set into a record with a tiny reusable function, the same job @ConstructorResult does, and then does a native DML statement and asks how many rows it touched.

Java Mapping native results to records

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Named queries

A named query is declared once (HQL or native) and referenced by name. Hibernate parses and validates all of them at startup, so a typo fails at boot rather than on a rarely used page:

Java
@Entity
@NamedQuery(name = "Book.byAuthor",
            query = "from Book b where b.author.id = :authorId order by b.title")
class Book { /* ... */ }

List<Book> books = session.createNamedQuery("Book.byAuthor", Book.class)
    .setParameter("authorId", 7L)
    .getResultList();

In Spring Data JPA, @Query on a repository method serves the same purpose. Keep names qualified (Book.byAuthor) because they share one global namespace. Since Hibernate 6, typed query methods on a @Repository-style interface (the @HQL/@SQL/@Find annotations with the Hibernate Processor) check the query at compile time.

Pagination: offset versus keyset

setFirstResult(n).setMaxResults(m) is the standard way to page, and Hibernate writes the right syntax per database. But offset paging gets slower with every page: the database must read and discard n rows. It can also skip or repeat rows when data changes between requests.

Keyset paging (also called the seek method) remembers the last row seen and asks for the rows after it, using an index:

Java
List<Book> next = session.createSelectionQuery("""
        from Book b
        where (b.title, b.id) > (:lastTitle, :lastId)
        order by b.title, b.id
        """, Book.class)
    .setParameter("lastTitle", lastTitle)
    .setParameter("lastId", lastId)
    .setMaxResults(20)
    .getResultList();

The sort must be deterministic (add the id as a tiebreaker), and the client sends back the last key instead of a page number. The price is that you cannot jump to page 57; for feeds and "load more" lists, that is fine. Spring Data offers Window and ScrollPosition for this.

Java Offset versus keyset paging

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

The last line shows the weakness of offset paging: after a new row appears at the front, page 2 repeats two items the user already saw. The keyset query would be unaffected.

When to choose native SQL

Prefer HQL. Choose native SQL when you need database-specific features (JSON operators, full-text search, MERGE, recursive CTEs on older Hibernate versions), or when you inherit a tuned query from a DBA. Keep native SQL in one place, test it against the real database (see testing), and always return explicit column lists instead of select * when you map to DTOs.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What must a native query that maps to an entity return?

    Hibernate builds the entity from the result set, so every mapped column (and the discriminator, if any) must be present.
  2. 2. What is the main benefit of named queries?

    Hibernate parses named queries at boot, so mistakes fail early instead of at the first call.
  3. 3. Why does offset paging get slower for deep pages?

    To return rows 100000-100020 the database still scans past the first 100000 rows.
  4. 4. Which ordering requirement does keyset paging have?

    The 'rows after this key' comparison only works if every row has a unique, stable position in the sort.

Summary

  • Native queries map to entities, records, tuples or @SqlResultSetMapping, but are not portable and bypass HQL's checks.
  • Tell Hibernate which tables a native query touches (query spaces) so flushing and cache invalidation stay correct.
  • Named queries are validated at startup.
  • Offset paging is simple but slow and unstable on deep pages; keyset paging scales for feeds.

Next: HQL Functions, CASE Expressions and Bulk Operations