Spring Data JPA Native SQL Query

In this tutorial on Spring Data JPA, I am going to share with you how to use the @Query annotation to run custom SQL queries.

Spring Data JPA provides us with many ready to use query methods which allow us to perform basic CRUD(Create, Read, Update and Delete) operations but sometimes, even though we have this ready to use Query Methods we still need to run a custom SQL Query. Let’s see how to do it. 

Run Native SQL Queries with Spring Data JPA @Query Annotation 

Let’s assume we have a database table called Users and we need to select all records from this database table that have a field email_verification_status set to true. The email_verification_status token is set to true when the user has successfully verified their email address. 

Here is an example of Users Repository interface which has a query method with @Query annotation:

package com.appsdeveloperblog.app.ws.io.repository;
 
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;

import com.appsdeveloperblog.app.ws.io.entity.UserEntity;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;

@Repository
public interface UserRepository extends PagingAndSortingRepository<UserEntity, Long>{

  @Query(value="select * from Users u where u.EMAIL_VERIFICATION_STATUS = 'true'", nativeQuery = true)
  Page<UserEntity> findAllUsersWithConfirmedEmailAddress( Pageable pageableRequest );

}

and if you do not use pagination to break search results into smaller pages but rather want to get an entire list of users returned in one single collection, then here is another example:

package com.appsdeveloperblog.app.ws.io.repository;
import org.springframework.stereotype.Repository;
import java.util.Collection;
import com.appsdeveloperblog.app.ws.io.entity.UserEntity;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

@Repository
public interface UserRepository extends CrudRepository<UserEntity, Long>{

  @Query(value="select * from Users u where u.EMAIL_VERIFICATION_STATUS = 'true'", nativeQuery = true)
  Collection<UserEntity> findAllUsersWithConfirmedEmailAddress();
}

Custom SQL Query with JPQL

The same above select query could be written using the Java Persistence Query Language(JPQL). 

@Query(”SELECT * FROM Users u WHERE u.EMAIL_VERIFICATION_STATUS = 'true' ”)

Native Query with Indexed Parameters

In the examples above the email_cerification_status value was hardcoded into SQL Query. But what if we need to pass the value as a method parameter? Below is an example if the same Spring Data JPA Native query which selects all users with email verification status set to true but the value of email verification status is passed as an Indexed Query Parameter: 

@Query(value=”SELECT * FROM Users u WHERE u.EMAIL_VERIFICATION_STATUS = ?1 ”,  nativeQuery = true)
Collection<UserEntity> findAllUsersWithVerifiedEmailAddress(boolean emailVerificationStatus);

We can also pass more than one Index Query Parameter. 

@Query(value=”SELECT * FROM Users u WHERE u.EMAIL_VERIFICATION_STATUS = ?1  and u.platform=?2”,  nativeQuery = true)
Collection<UserEntity> findAllUsersWithVerifiedEmailAddress(boolean emailVerificationStatus, String mobileDevicePlatform);

Indexed Query Parameters – JPQL example

Index Query Parameters can also be used with Java Persistence Query Language. The below code snippet demonstrates how to use indexed query parameters with JPQL: 

@Query(”SELECT * FROM Users u WHERE u.EMAIL_VERIFICATION_STATUS = ?1  and u.platform=?2”)
Collection<UserEntity> findAllUsersWithVerifiedEmailAddress(boolean emailVerificationStatus, String mobileDevicePlatform);

Named Parameters Example with Native Query 

There is one more way to pass parameters to a native query and it is to use Named Parameters.  To pass a parameter to a native query we will need to use @Param annotation and make sure that the name used in the @Param annotation matches the variable name used in the native query. Have a look at the example below: 

@Query(value=”SELECT * FROM Users u WHERE u.EMAIL_VERIFICATION_STATUS = :emailVerificationStatus ”,  nativeQuery = true)
Collection<UserEntity> findAllUsersWithVerifiedEmailAddress(@Param("emailVerificationStatus") boolean emailVerificationStatus);

Note that the value in @Param annotation emailVerificationStatus matches the variable name used in SQL query emailVerificationStatus. Also note the use of colon “:” before the variable name in the SQL query. 

The UPDATE SQL Query

Additionally, to selecting records from a database table you can use @Query annotation to perform an update SQL query. In the case of the update SQL query, there is one more annotation that needs to be used and it is @Modifying. Below is an example of how you can use the @Modifying annotation together with @Query annotation to perform the UPDATE SQL Query.

    @Modifying
    @Query(value = "update Users u set u.EMAIL_VERIFICATION_STATUS = ?1 where u.USER_ID = ?2",
            nativeQuery = true)
void setEmailVerificationStatus(boolean status, String userId);

The DELETE SQL Query

Just like in the example above, you can use @Modifying annotation together with @Query annotation to perform SQL DELETE query and delete the record from a database table. Below is an example of how you can run the DELETE SQL query using the Spring Data JPA Native Query:

    @Modifying
    @Query(value = "delete from Users u where u.USER_ID = ?1",
            nativeQuery = true)
void deleteUser(String userId);

I hope this short tutorial on how to create custom SQL queries with Spring Data JPA was helpful to you. 

If you are interested in learning more about Spring Data JPA, please have a look at the list of video courses below or search this website by a keyword: JPA. You might also want to check how Spring Data JPA is used when Building RESTful Web Services with Spring MVC and Spring Boot.