Using native Queries with Hibernate and JPA

What are your options if your Hibernate/EJB application has to deal with a very complex and DB-optimized SQL query ? good interview question….
Hibernate has a built-in object oriented language for querying objects called HQL. This language is quite complete and efficient, so in theory you could translate every SQL query in its equivalent HQL ( and provide the necessary POJOs and configuration files).However the cost of translating very complex queries can be excessive (including the time spent to build all POJOs which are involved in the query). You might be very smart with HQL but it’s still very difficult that you can achieve the same performance of a Query that a DBA spent hours in order to optimize it.

This can be especially true if you are migrating an older JDBC application which has already a stable and tuned list of queries.

In these situations it is convenient to use the native SQL for creating Entity Queries.

The simplest way to run a native SQL Query is to use the createNativeQuery() method of the EntityManager interface, passing in the query string and the entity type that will be returned.

public List<Customer> findAllCustomersNative() {
        Query query = em.createNativeQuery("SELECT * from customer",Customer.class);
        List<Customer> customerList = query.getResultList();
        return customerList;
}

Native SQL can also be used for named queries by defining a @NamedNativeQuery annotation.

Let’s see an example of it:

package com.example.samplewebapp;


import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedNativeQueries;
import javax.persistence.NamedNativeQuery;
@Entity
@NamedNativeQueries({

    @NamedNativeQuery(
        name = "Person.findAllPersons",
        query =
            "SELECT * " +
            "FROM Person ", resultClass = Person.class
    ),

    @NamedNativeQuery(
        name = "Person.findPersonByName",
        query =
            "SELECT * " +
            "FROM Person p " +
            "WHERE p.name = ?", resultClass = Person.class)
    
})
public class Person {


    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    Long id;
    String name;
    String surname;


    // Getter and Setters omitted for brevity

}

In this example, we have defined two @NamedNativeQuery:

  • Person.findAllPersons: to return all Person Entities
  • Person.findPersonByName: to return all Person Entities filtered by name

Like JP QL named queries, the name of the query must be unique within the persistence unit. If the result type is an entity, the resultClass element may be used to indicate the entity class. If the result requires a SQL mapping, the resultSetMapping element may be used to specify the mapping name.

So it’s pretty obvious how to define a @NamedNativeQuery. Now let’s check how to run the @NamedNativeQuery. There are mainly two ways:

Using the EntityManager to run the @NamedNativeQuery

If you are using the EntityManager interface to extract your data, then you can simply reference the @NamedNativeQuery from within the “createNamedQuery” as you can see in this example:

@RestController
public class PersonController {
    @PersistenceContext
    EntityManager em;
    

    @RequestMapping("/findNames")
    public List<Person> findAll() {
        
        Query q = em.createNamedQuery("Person.findAllPersons");      
        return q.getResultList();
        
    }

    @RequestMapping("/findByName/{name}")
    public List<Person> findOne(@PathVariable String name) {
        Query q = em.createNamedQuery("Person.findPersonByName");      
        q.setParameter(1, name);
        return q.getResultList();

    }
}

You can test the above REST methods:

$ curl -s http://localhost:8080/findNames | jq
[
  {
    "id": 1,
    "name": "Jack",
    "surname": "Smith"
  },
  {
    "id": 2,
    "name": "Joe",
    "surname": "Black"
  },
  {
    "id": 3,
    "name": "Martin",
    "surname": "McFly"
  }
]


$ curl -s http://localhost:8080/findByName/Jack | jq
[
  {
    "id": 1,
    "name": "Jack",
    "surname": "Smith"
  }
]

Using Spring Data Repository interface

Spring Data JPA is able to bind automatically your NamedNativeQuery in your Repository definition. Doing that is extremely simple if you follow Spring Data’s naming convention. The name of your query has to start with the name of your entity class, followed by “.” and the name of your repository method.
In our example, the method Person.findAllPersons will be mapped with the findAllPersons method and the Person.findPersonByName will be mapped as findPersonByName(String name).

public interface PersonRepository extends CrudRepository<Person, Long> {

     List<Person> findAllPersons(); 
     List<Person> findPersonByName(String name);
     
}

When running the Main application class, we can reference our Repository interface and access data without the mediation of the EntityManager:

@SpringBootApplication
public class DemoApplication {
    private static final Logger log = LoggerFactory.getLogger(DemoApplication.class);

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }
    @Bean
    public CommandLineRunner demo(PersonRepository repository) {
        return (args) -> {
            // save a couple of persons
            repository.save(new Person("Jack","Smith"));
            repository.save(new Person("Joe","Black"));
            repository.save(new Person("Martin","McFly"));


            // fetch all persons
            log.info("Persons found with findAll():");
            log.info("-------------------------------");
            for (Person person : repository.findAllPersons()) {
                log.info(person.toString());
            }
            log.info("");



            // fetch persons by last name
            log.info("Person found with findByName('Jack'):");
            log.info("--------------------------------------------");
            repository.findPersonByName("Jack").forEach(smith -> {
                log.info(smith.toString());
            });

            log.info("");
        };
    }
}

We have just covered how to use the @NamedNativeQuery to execute native SQL statement from your Hibernate/JPA applications.
https://github.com/fmarchioni/masterspringboot/tree/master/jpa/native-query