Modern Native SQL Queries with Hibernate and JPA

Native SQL with Hibernate and Jakarta Persistence

JPQL and Hibernate Query Language are usually preferable when a query can be expressed in terms of entities. However, native SQL remains useful for database-specific features, complex reporting queries, optimized SQL, common table expressions and existing SQL that an application must reuse.

Jakarta Persistence exposes native SQL through EntityManager.createNativeQuery(...). Hibernate executes that SQL through the JDBC connection associated with the persistence context.

Return scalar values with a native query

If a native query selects individual columns rather than a mapped entity, the result can be consumed as rows of values.

var sql = """
    select handle, email_address
    from player
    order by id
    """;

@SuppressWarnings("unchecked")
var players = (java.util.List<Object[]>)
    entityManager.createNativeQuery(sql).getResultList();

for (var player : players) {
    IO.println(player[0] + " " + player[1]);
}

Notice that the query names its columns instead of using SELECT *. Explicit columns make native queries easier to understand and less sensitive to unrelated schema changes.

Map native SQL directly to a JPA entity

When the SQL returns the columns required by an entity mapping, pass the entity class to createNativeQuery. Hibernate then materializes managed entity instances instead of returning Object[] rows.

var sql = """
    select id, handle, email_address
    from player
    order by id
    """;

var query = entityManager.createNativeQuery(sql, Player.class);

@SuppressWarnings("unchecked")
var players = (java.util.List<Player>) query.getResultList();

players.forEach(player ->
    IO.println(player.loginName() + " " + player.emailAddress())
);

Always bind native-query parameters

Do not construct SQL by concatenating user input. Native queries support parameters, which keep values separate from the SQL statement and avoid SQL injection vulnerabilities.

var sql = """
    select id, handle, email_address
    from player
    where handle = :handle
    """;

var player = entityManager
    .createNativeQuery(sql, Player.class)
    .setParameter("handle", "rocky")
    .getSingleResult();

IO.println(player);

NamedNativeQuery example

For a native query that is reused throughout an application, a named native query gives the SQL a stable application-level name.

import jakarta.persistence.*;

@Entity
@Table(name = "player")
@NamedNativeQuery(
    name = "Player.findAllNative",
    query = """
        select id, handle, email_address
        from player
        order by id
        """,
    resultClass = Player.class
)
public class Player {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "handle", nullable = false, unique = true)
    private String loginName;

    @Column(name = "email_address", nullable = false)
    private String emailAddress;

    protected Player() {
    }

    public Player(String loginName, String emailAddress) {
        this.loginName = loginName;
        this.emailAddress = emailAddress;
    }

    public String loginName() {
        return loginName;
    }

    public String emailAddress() {
        return emailAddress;
    }

    @Override
    public String toString() {
        return loginName + " <" + emailAddress + ">";
    }
}

The named query can then be executed through the EntityManager:

var query = entityManager.createNamedQuery(
    "Player.findAllNative",
    Player.class
);

var players = query.getResultList();
players.forEach(IO::println);

Hibernate NativeQuery

Jakarta Persistence is sufficient for many native SQL use cases. When an application needs Hibernate-specific capabilities, the underlying Hibernate Session can be obtained and Hibernate’s NativeQuery API used directly.

import org.hibernate.Session;

var session = entityManager.unwrap(Session.class);

var players = session
    .createNativeQuery("""
        select id, handle, email_address
        from player
        where email_address like :domain
        """, Player.class)
    .setParameter("domain", "%@example.com")
    .getResultList();

players.forEach(IO::println);

When should you use native SQL?

Native SQL is appropriate when it provides a capability or performance characteristic that JPQL, Criteria queries or normal entity navigation cannot express cleanly. The tradeoff is portability: database-specific SQL can couple the application to a particular database dialect.

Keep native queries focused, bind all external values as parameters, select only the columns you need, and map results to entities or explicit result mappings whenever that makes the code clearer.

Cameron McKenzie is an AWS Certified AI Practitioner, Machine Learning Engineer, Solutions Architect and author of many popular books in the software development and Cloud Computing space. His growing YouTube channel training devs in Java, Spring, AI and ML has well over 30,000 subscribers.