Spring Data JPA Native Queries?

Spring Data JPA Native Queries  are SQL queries written in the native SQL syntax of the underlying database. Spring Data JPA provides a convenient way to use native SQL queries with its @Query annotation, which allows you to write custom SQL queries and map their results to your domain objects.

Table of Content :

While Spring Data JPA provides a set of predefined methods for common database operations, there are times when you need to write more complex queries that cannot be expressed using the predefined methods. In such cases, you can use native SQL queries to perform the required database operations.

Native queries can be used to perform all types of database operations such as SELECT, INSERT, UPDATE, and DELETE. However, using native queries requires you to write database-specific SQL syntax, which may not be portable across different database vendors.

In Spring Data JPA, you can use native SQL queries by setting the nativeQuery attribute to true in the @Query annotation. Additionally, for UPDATE and DELETE queries, you need to use the @Modifying annotation in addition to the @Query annotation.

How to use Spring Data JPA Native Queries - Maven Dependencies

To use Spring Data JPA Native Queries, you need to include the following dependencies in your pom.xml file: 


 


<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
  <scope>runtime</scope>
</dependency>

The first dependency is for Spring Data JPA, which provides the core functionality for working with databases in Spring applications. The second dependency is for the H2 database, which is a lightweight, in-memory database that can be used for testing and development purposes.

You may also need to include the appropriate database driver dependency for the database you are using. For example, if you are using MySQL, you need to include the following dependency: 


 

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.26</version>
</dependency>

Replace mysql-connector-java with the appropriate artifact ID for your database driver, and 8.0.26 with the version of the driver you want to use.

Note that you may need to configure additional properties, such as the database URL, username, and password, in your application.properties or application.yml file, depending on the database you are using.

Spring Data JPA Native Queries - steps

To use Spring Data JPA Native Queries, you need to follow these steps:

1. Create a repository interface that extends the JpaRepository interface or one of its sub-interfaces, such as CrudRepository or PagingAndSortingRepository. 


 

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
  // your custom methods here
}

2. Write your custom SQL query in the @Query annotation, and set the nativeQuery attribute to true. 


 
  @Query(value = "SELECT * FROM users WHERE age > :age", nativeQuery = true)
List<User> getUsersByAge(@Param("age") int age);

3. (Optional) Use parameter binding to pass parameters to the query. This can be done using the @Param annotation. 


 
  @Query(value = "SELECT * FROM users WHERE name = :name", nativeQuery = true)
List<User> getUsersByName(@Param("name") String name);

4. (Optional) Use the @Modifying annotation in addition to the @Query annotation for UPDATE and DELETE queries.


 
  @Modifying
@Query(value = "UPDATE users SET name = :name WHERE id = :id", nativeQuery = true)
void updateUserName(@Param("id") long id, @Param("name") String name);

5. Use the repository interface in your service or controller class to access the database using the custom native query methods. 


 
@Service
public class UserService {

  @Autowired
  private UserRepository userRepository;

  public List<User> getUsersByAge(int age) {
    return userRepository.getUsersByAge(age);
  }

  public void updateUserName(long id, String name) {
    userRepository.updateUserName(id, name);
  }

}

Basic SELECT query: 


 
@Repository
public interface UserRepository extends JpaRepository<User, Long> {

  @Query(value = "SELECT * FROM users", nativeQuery = true)
  List<User> getAllUsers();

}

SELECT query with parameters: 


 
  @Repository
public interface UserRepository extends JpaRepository<User, Long> {

  @Query(value = "SELECT * FROM users WHERE age > :age", nativeQuery = true)
  List<User> getUsersByAge(@Param("age") int age);

}

UPDATE query: 


 

  @Repository
public interface UserRepository extends JpaRepository<User, Long> {

  @Modifying
  @Query(value = "UPDATE users SET name = :name WHERE id = :id", nativeQuery = true)
  void updateUserName(@Param("id") long id, @Param("name") String name);

}

DELETE query: 


 

  @Repository
public interface UserRepository extends JpaRepository<User, Long> {

  @Modifying
  @Query(value = "DELETE FROM users WHERE id = :id", nativeQuery = true)
  void deleteUserById(@Param("id") long id);

}

Limitations of Spring Data JPA Native Queries

While Spring Data JPA Native Queries can be useful for writing custom SQL queries in your application, they also have some limitations that you should be aware of:

  • Database-specific syntax: Native queries are written using the native SQL syntax of the underlying database, which may not be portable across different database vendors. This means that if you switch to a different database in the future, you may need to rewrite your native queries to use the syntax of the new database.
  • Lack of type safety: Native queries do not provide type safety like JPQL queries do. This means that you need to be careful to ensure that the data types of the returned values match the expected types in your code.
  • Difficulty in mapping results to domain objects: Unlike JPQL queries, which automatically map query results to domain objects, native queries require you to manually map query results to domain objects using a result set mapper. This can be tedious and error-prone, especially for complex queries that return many columns.
  • Increased risk of SQL injection: Since native queries use raw SQL, they are more susceptible to SQL injection attacks if you do not properly sanitize input parameters.
  • Limited support for pagination: Native queries may not support pagination as efficiently as JPQL queries, especially for large result sets. This can lead to performance issues if you are working with large datasets.

In general, it is recommended to use JPQL queries whenever possible, as they provide type safety, support for object-oriented concepts, and portability across different databases. However, if you need to write custom SQL queries that cannot be expressed using JPQL, native queries can be a useful tool. Just be aware of their limitations and use them with caution.

Conclusion

That's it! You can now use your custom native query methods to access the database in your application. Note that Spring Data JPA also provides other ways to customize queries, such as using method names, criteria queries, or query by example. However, native queries are a useful tool when you need to write complex queries that cannot be expressed using the predefined methods.

Note: In order to use native queries with Spring Data JPA, you need to set the nativeQuery attribute to true in the @Query annotation. Additionally, for UPDATE and DELETE queries, you need to use the @Modifying annotation in addition to the @Query annotation.