How to use JPA Native Query in Spring Boot applications

by

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.

Native Queries in a nutshell

Firstly, 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;
}

You can also leverage native SQL 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, you can use the SqlResultSetMapping element to specify the mapping name.

Example:

SqlResultSetMapping(
        name = "CustomerMapping",
        entities = @EntityResult(
                entityClass = Customer.class,
                fields = {
                    @FieldResult(name = "id", column = "id"),
                    @FieldResult(name = "firstName", column = "firstName"),
                    @FieldResult(name = "lastName", column = "lastName"),
                    @FieldResult(name = "address", column = "address")}))

Having defined the “CustomerMapping” as SqlResultSetMapping, you can now provide the name of the mapping instead of the entity class as a parameter to the createNativeQuery(String sqlString, String resultSetMapping) method:

List<Customer> results = this.em.createNativeQuery("SELECT a.id as id, a.firstName, a.lastName, a.address FROM Customer c", "CustomerMapping").getResultList();

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 or the EntityManager interface.

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

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

CrudRepository vs JpaRepository interfaces

In this example, we are using CrudRepository as basic Spring Data JPA interface. Another option is to use JpaRepository. JpaRepository extends CrudRepository and provides additional methods for more advanced data access operations. For example:

Here are some key differences between CrudRepository and JpaRepository:

  1. JpaRepository allows to create a query from a method name. It uses naming conventions for query generation, based on the method name, whereas the CrudRepository interface does not provide this feature.
  2. JpaRepository provides additional query methods like findOne(), findAll(), findAllById(), findAll(Sort sort), findAll(Pageable pageable) and count() etc. which are not available in CrudRepository.
  3. JpaRepository has extra functionality like deleteInBatch(), flush(), saveAndFlush(), getOne(), and findById(id) etc. that are not available in CrudRepository.

Conclusion

In conclusion, JPA Native Query is a powerful feature that allows developers to write and execute SQL queries in Spring Boot applications. By leveraging this feature, developers can take advantage of the performance benefits of native queries while still utilizing the JPA API.

Source code: https://github.com/fmarchioni/masterspringboot/tree/master/jpa/native-query

Found the article helpful? if so please follow us on Socials
Twitter Icon       Facebook Icon       LinkedIn Icon       Mastodon Icon