Spring Data JPA Native SQL Query

This tutorial will teach you how to use the @Query annotation to execute custom SQL queries.

Spring Data JPA provides many query methods that are ready to use, allowing us to perform basic CRUD(Create, Read, Update and Delete) operations. However, there may be situations where we need to execute a custom SQL query even though we have these ready-to-use query methods. This tutorial will show you how to do it.

Native SQL query vs JPQL in Spring Data JPA

Native SQL queries allow you to write SQL directly, whereas JPQL is an object-oriented query language that is designed to work with Java entities. JPQL provides a higher level of abstraction and can help simplify queries, but may not always be able to fully utilize the power of SQL. Using native SQL queries can provide more flexibility in terms of performance optimization and complex queries, but may also require more manual mapping of results to Java objects. Ultimately, the choice between using native SQL queries and JPQL depends on the specific requirements and constraints of your application.

Run Native SQL Queries with Spring Data JPA @Query Annotation 

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

Below is an example of the Users Repository interface that has a query method with a @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 JpaRepository<UserEntity, Long>{

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

}

If you do not want to use pagination to break search results into smaller pages and instead prefer to retrieve the entire list of users in a single collection, 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 above SELECT query could be written using the Java Persistence Query Language (JPQL).

@Query(”SELECT u FROM UserEntity u WHERE u.emailVerificationStatus = 'true' ”)

Native Query with Indexed Parameters

In the examples above, the email_verification_status value was hardcoded into the SQL Query. But what if we need to pass the value as a method parameter?

Below is an example of the same Spring Data JPA Native query that 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);

If you want to learn how to execute an UPDATE query using Spring Data JPA Native SQL, you can refer to the following tutorial “Spring Data JPA Native UPDATE SQL Query“.

Indexed Query Parameters – JPQL example

Indexed query parameters can be used with the Java Persistence Query Language (JPQL). The following code snippet demonstrates how to use indexed query parameters with JPQL:

@Query(”SELECT u FROM UserEntity u WHERE u.emailVerificationStatus = ?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 the @Param annotation and ensure 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);

Please keep in mind that the value specified in the @Param annotation for emailVerificationStatus should match the variable name used in the SQL query (which is also emailVerificationStatus in this case). Additionally, remember to use a colon (:) before the variable name in the SQL query.

Named Parameters Video Tutorial

The UPDATE SQL Query

In addition to selecting records from a database table, you can also use the @Query annotation to execute an update SQL query. However, when executing an update SQL query, you need to use one more annotation: @Modifying.

Here is an example of how to use the @Modifying and @Query annotations to perform an 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

As shown in the previous example, you can use the @Modifying annotation along with the @Query annotation to execute a SQL DELETE query and remove a record from a database table. Below is an illustration of how you can execute 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);

Frequently asked questions

  • How can I test my Native SQL Queries in Spring Data JPA?
    To test your Native SQL Queries in Spring Data JPA, you can use the same testing framework that you would use for any other unit tests in your application. You can create a test class that uses the same configuration and setup as your regular application, and then write test methods that execute your Native SQL Queries and verify the results. You can use tools like JUnit or TestNG to run your tests and verify that your queries are working correctly.
  • Can I map the results of a Native SQL Query to a custom Java object?
    Yes, you can map the results of a Native SQL Query to a custom Java object using Spring Data JPA’s projection feature. Projections allow you to define a custom interface or class that specifies which columns from the result set should be mapped to which properties in your Java object. You can then use this projection interface or class in your Native SQL Query to map the results to your custom object. This can be useful if you only need to retrieve a subset of columns from your table or if you want to map the results to a non-entity class.
  • How do I handle errors or exceptions when executing Native SQL Queries in Spring Data JPA?
    When executing Native SQL Queries in Spring Data JPA, you should be aware of the potential for SQL injection attacks and other security risks. You should also be prepared to handle any errors or exceptions that might occur during query execution. Spring Data JPA provides several ways to handle errors, including catching exceptions that are thrown by the JPA provider, using the @Transactional annotation to automatically roll back transactions on exceptions, and implementing custom exception handlers to handle specific error conditions. You can also use logging and monitoring tools to track and diagnose errors that occur during query execution.

Conclusion

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

If you are interested in learning more about Spring Data JPA, please 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.

Leave a Reply

Your email address will not be published. Required fields are marked *