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);
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.