Native SQL Queries

When to Use Native SQL

Native SQL is the right tool when a query uses features specific to one database, or when a complex report is easier to write directly in SQL. Hibernate sends the SQL as written, and it can map the result to entities or to plain values.

The cost is portability. The query is tied to one database, and the SQL uses table and column names, not entity and field names.

List<Student> students = session
        .createNativeQuery(
            "select * from students where full_name like :pattern", Student.class)
        .setParameter("pattern", "A%")
        .getResultList();

List<Object[]> rows = session
        .createNativeQuery(
            "select status, count(*) from students group by status", Object[].class)
        .getResultList();

Code Explanation:

• The first query returns managed Student entities, so the columns selected must match the entity mapping.
• Named parameters work the same way as in HQL.
• The second query returns each row as an Object[], one element per selected column.
• Hibernate may not know which tables a native query reads, so pending changes might not be flushed first. Call flush() before the query when fresh data matters.