Native SQL

JPQL covers most needs, but sometimes you need SQL itself: a function specific to your database, a window function, a hand-tuned report query. Native queries let you send any SQL through the same EntityManager, with parameters and within the current transaction. The price is portability: the query is written for one particular database.

Loading entities

The examples use the Seed data from the lesson on lazy loading.

// src/main/java/com/example/blog/Main.java
List<Post> posts = em.createNativeQuery(
                "select * from Post where views > :min order by views desc", Post.class)
        .setParameter("min", 50)
        .getResultList();
posts.forEach(p -> IO.println(p.getTitle()));

Hibernate: select * from Post where views > ? order by views desc
Hello, Hibernate
Records in Java 25

The query uses real table and column names: Post, views, author_id. With the entity class as the second argument, Hibernate turns each row into a managed Post. Named parameters like :min work here too. The method returns a raw List, so the compiler warns about an unchecked conversion.

Rows as arrays

When the result is not an entity, ask for Object[] — one array per row:

// src/main/java/com/example/blog/Main.java
List<Object[]> rows = em.createNativeQuery("""
                select a.name, count(p.id)
                from Author a left join Post p on p.author_id = a.id
                group by a.name
                order by a.name
                """, Object[].class)
        .getResultList();
rows.forEach(r -> IO.println(Arrays.toString(r)));

[Anna, 1]
[Maria, 1]
[Oleg, 1]

The element types depend on the JDBC driver: here the name is a String and the count a Long.

Rows into a record

Hibernate can also call the constructor of a record whose components match the columns:

// src/main/java/com/example/blog/AuthorPosts.java
package com.example.blog;
 
public record AuthorPosts(String name, long posts) {
}

// src/main/java/com/example/blog/Main.java
List<AuthorPosts> stats = em.createNativeQuery("""
                select a.name, count(p.id)
                from Author a left join Post p on p.author_id = a.id
                group by a.name
                order by a.name
                """, AuthorPosts.class)
        .getResultList();
stats.forEach(IO::println);

AuthorPosts[name=Anna, posts=1]
AuthorPosts[name=Maria, posts=1]
AuthorPosts[name=Oleg, posts=1]

The columns are matched by position: the first to name, the second to posts.

When to choose what

Task	Tool
Ordinary selection and filtering	JPQL
Filters assembled at run time	Criteria API
Database-specific features, complex reports	Native SQL

Native queries also bypass the persistence context, like bulk JPQL updates: an update through SQL does not change entities already loaded.

Common mistake

When a native query loads entities, it must return every column the entity maps. If you select only a few:

// src/main/java/com/example/blog/Main.java
em.createNativeQuery("select id, title from Post where views > :min order by views desc", Post.class)

Exception in thread "main" org.hibernate.exception.SQLGrammarException: Unable to find column position by name: author_id [Column "AUTHOR_ID" not found [42122-252]] [n/a]

To load a part of the columns, use Object[] or a record instead of the entity class.

Thanks for reading!