How to use JPA Native Query in Spring Boot applications

Hibernate and JPA can both execute native SQL statements against a Database. In this tutorial we will learn how to map SQL native queries in Spring Boot applications.

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 on the top of your Entity class. 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 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.

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("");
		};
	}

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"
  }
]

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

Exit mobile version