How to use native SQL in Hibernate and JPA

Hibernate JPA and SQL queries

The goal of Hibernate and JPA is to hide the complexity of SQL and relational databases from the software developer.

However there are times when a developer must combine Hibernate, JPA and SQL queries within their code. The Jakarta Persistence API provides two standard ways to pull records from a database using native SQL, Hibernate and JPA:

1. Native SQL queries that return an object array.
2. Native SQL queries that map records to JPA entities.

Hibernate and SQL together

Hibernate and JPA can execute any valid SQL statement. By default, the data returned is simply a list of object arrays.

• Each record returned is an element in the list.
• Each field is accessed through its array index.

A native SQL query that selects all records from the Player database table looks like this:

String sql = "SELECT * FROM PLAYER";
Query query = em.createNativeQuery(sql);
List<Object[]> players = query.getResultList();

players.forEach((player)-> {
  System.out.print(player[1]);
  System.out.println(player[2]);
});

Indexed based object access

As you can see, there is no database to entity mapping in this JPA and SQL example. Hibernate and JPA simply returns the data as structured objects in a generic array.

In this example, indexes 1 and 2 were used.

Let’s assume the Player table in the database was structured as follows:

Due to zero-based counting, indexes 1 and 2 map to the second and third columns in the Player table, which are the handle and the emailAddress.

When the code builds and runs, the output is as follows:

rocky [email protected]
mario [email protected]
sk8trgrl avril@example

Hibernate, JPA, SQL and entity mapping

The JPA Query object is smart enough to map fields returned from a native SQL query to the fields in the corresponding entity if you provide the name of the class to the createNativeQuery method.

The benefit of this approach is that the code can access data through setters and getters rather than depend on the index the required field uses in the database. The approach is more user-friendly and less brittle.

String sql = "SELECT * FROM PLAYER";
Query query = em.createNativeQuery(sql, Player.class);
List<Player> players = query.getResultList();

players.forEach((p) -> {
  System.out.print(p.getEmailAddress());
  System.out.println(p.getHandle());
});

The result of this JPA, SQL and Hibernate example that uses entity mapping is the same as the native SQL query above.

rocky [email protected]
mario [email protected]
sk8trgrl avril@example

Native queries allow Hibernate and JPA to send raw SQL through their JDBC connections.

JPA’s NamedNativeQuery for SQL statements

Code can get messy when it is cluttered with too many SQL statements. It’s a best practice to keep frequently used SQL statements in the definition of the class that uses them, rather than litter them throughout the code.

JPA’s NamedNativeQuery annotation allows you to define raw SQL queries and the class they map to in an annotation on the entity to which they pertain.

@NamedNativeQuery(name = "selectPlayerEntities", 
                   query = "SELECT * FROM Player p", 
                    resultClass = Player.class)

public class Player { ... }

The query Hibernate, JPA and SQL code becomes much cleaner as a result:

Query query = em.createNamedQuery("selectPlayerEntities");
List<Player> players = query.getResultList();

Applications that use the Jakarta Persistence API shouldn’t embed too much raw SQL in their code.

However, when you need an SQL query for performance reasons or to access the database in a way that doesn’t fit nicely with existing Hibernate and JPA mappings, native queries are always a way to gain access to the required persistence data.

The Player class

The full code for the Player class used in this example is below:

public class Player { 
  @Id @GeneratedValue private Long id; 
  @Column(name="handle", unique=true) 
  private String loginName; 
  @Basic private String emailAddress; 
  public String getEmailAddress() { return emailAddress;} 
  public void setEmailAddress(String emailAddress) { 
    this.emailAddress = emailAddress; 
  } 
  public String getPassword() {return password;} 
  public void setPassword(String password) { 
    this.password = password; 
  } 
  public String getLoginName() {return loginName;} 
  public void setLoginName(String loginName) { 
    this.loginName = loginName; 
  } 
}

Learn JDBC fast!