In this post, I’d like to share with you how to use native SQL queries in a Spring Boot application that uses Spring Data JPA and Hibernate for repository layer. In details, you’ll learn:

• What is native query in Spring Data JPA, why and when using native queries
• Native query example for SELECT SQL statement
• Native query example for UPDATE SQL statement
• How to use pagination with native queries

If you’re new to Spring Data JPA, I recommend you follow this Spring Data JPA for beginner tutorial first, and then come back to this one.

 

1. What is Native Query?

You know, when using Spring Data JPA with an ORM framework like Hibernate, by default, the queries are of type JPQL (Java Persistence Query Language) – not relational SQL queries. Then at runtime, the ORM framework translates JPQL queries into SQL queries that are specific to the underlying database.

So, a native query is a SQL statement that is specific to a particular database (e.g. MySQL), which differs slightly from JPQL which is used by Spring Data JPA by default.

In most cases, you don’t need to use native queries in a Spring Boot application. Use native queries if JPQL does not work (you need to use native syntax of the underlying database), or when you want to tune performance (rarely).

2. Native Select Query Examples

Suppose that you have an entity class Product that maps with the products table in database. Here’s an example of a native query declared in the ProductRepository interface:

@Repository
public interface ProductRepository extends JpaRepository<Product, Integer> {

	@Query(value = "SELECT * FROM products WHERE price < :maxPrice", nativeQuery = true)
	public List<Product> getProductsWithMaxPrice(Integer maxPrice);
	
}

You see, to declare a native query, use the @Query annotation with the attribute nativeQuery = true. You can also use named parameters or indexed parameters in a native query just like with JPQL query. The method can also return a collection of the entity type, just like JPQL.

And the following native query example is used for full text search with MySQL database because the syntax SELECT … WHERE MATCH(…) AGAINST (…)is specific to MySQL:

public interface ProductRepository extends JpaRepository<Product, Integer> {
	
	@Query(
		value = "SELECT * FROM products WHERE MATCH(name, description) AGAINST (?1)", 
		nativeQuery = true)
	public List<Product> search(String keyword);
	
}

Note that you must use table name and column names in native queries because it’s regular SQL statements – you can’t use entity name and field names in native queries.

3. Native Update Query Examples

Here’s an example of a native query for SQL UPDATE statement:

@Repository
public interface ProductRepository extends JpaRepository<Product, Integer> {
	
	@Query(value = "UPDATE products SET price = price + :amount", nativeQuery = true)
	@Modifying
	public void updatePrice(Integer amount);
}

You see, it is required to use the @Modifying annotation for an update query.

4. Native Query with Pagination Examples

To use pagination for native queries, simply add a parameter of type Pageable in the query method. Here’s an example:

@Query(value = "SELECT * FROM products WHERE MATCH(name, description) AGAINST (?1)", 
		nativeQuery = true)
public Page<Product> search(String keyword, Pageable pageable);

Also, you need to change the return type from List<Entity> to Page<Entity> so the method will return a page of the paginated result.

You can also specify a count query that is used for pagination. Below is another example:

@Query(value = "SELECT * FROM products WHERE price < :maxPrice", 
	nativeQuery = true,
	countQuery = "SELECT COUNT(*) FROM products WHERE price < :maxPrice")
public Page<Product> getProductsWithMaxPrice(Integer maxPrice, Pageable pageable);

To learn more about pagination with Spring Data JPA, refer to this tutorial: Spring Data JPA Paging and Sorting Examples.

That’s a few examples of using native queries with Spring Data JPA. I recommend you watch the following video to see my coding in action:

 

Related Spring and Database Tutorials:

• Spring Data JPA EntityManager Examples (CRUD Operations)
• JPA EntityManager: Understand Differences between Persist and Merge
• Understand Spring Data JPA with Simple Example
• Spring Data JPA Custom Repository Example
• Spring MVC with JdbcTemplate Example
• How to configure Spring MVC JdbcTemplate with JNDI Data Source in Tomcat
• Spring and Hibernate Integration Tutorial (XML Configuration)
• Spring MVC + Spring Data JPA + Hibernate - CRUD Example