Spring Data JPA: Write a query in Pure SQL in @Query of Repository

TL;DR

By specifying nativeQuery = true for @Query of Spring Data JPA, you can query by SQL instead of JPQL.

public interface PhotoRepository extends JpaRepository<Photo, Integer> {
  @Query(value = "SELECT * FROM PHOTO AS p WHERE p.USER_ID = 1", nativeQuery = true) // SQL
  Iterator<Photo> findPhotosByUserId(int userId);
}

@Repository

In Spring Data JPA, you can use Repository as DAO, that is, object that can access table, just by defining a method in Repository interface + DI with @ Autowired.

PhotoRepository.java


@Repository
public interface PhotoRepository extends JpaRepository<Photo, Integer> {
//PHOTO table is USER_If you have an ID column, the arguments userId and USER_Get records with matching IDs
  Iterator<Photo> findByUserId(int userId); 
}

PhotoService.java


@Service
public class PhotoService {

  @Autowired // DI:Instantiated
  private PhotoRepository repository;

  public Iterator<Photo> getPhotos(int userId) {
     return repository.findByUserId(userId);
  }

}

(For simplicity, we are using the Repository that inherits JpaRepository directly. Actually, the JpaRepository inheritance / Entity definition is separated from the domain layer where PhotoRepository is placed.)

@Repository indicates that it is a Repository component in Spring, and this annotation makes it DI target by @Autowired.

By the way, see below for the naming conventions of methods that can be defined in Repository. You can also do BETWEEN or ʻORDER_BY` normally. Spring Data JPA - Reference Documentation

@Query

If you add @Query to a method on the Repository, you can define the query directly as a string without defining the query from the method naming convention.

public interface PhotoRepository extends JpaRepository<Photo, Integer> {
  @Query(value = "select p from photo p where p.userId = 1") // JPQL
  Iterator<Photo> findPhotosByUserId(int userId);
}

However, the query language called JPQL can be defined by default, not SQL. Therefore, if you write SQL normally in the argument of @ Query, an error will occur.

Reference: 10.2. JPQL Language Reference

To execute in SQL, add nativeQuery = true as shown at the beginning. Now you can use SQL as usual.

public interface PhotoRepository extends JpaRepository<Photo, Integer> {
  @Query(value = "SELECT * FROM PHOTO AS p WHERE p.USER_ID = 1", nativeQuery = true) // SQL
  Iterator<Photo> findPhotosByUserId(int userId);
}

Recommended Posts

Spring Data JPA: Write a query in Pure SQL in @Query of Repository
Creating a common repository with Spring Data JPA
Check the behavior of getOne, findById, and query methods in Spring Boot + Spring Data JPA
Spring Data JPA SQL log output
[Spring Data JPA] Custom ID is assigned in a unique sequence at the time of registration.
Exists using Specification in Spring Data JPA
To write Response data directly in Spring
No property list found for type because it is a non-named query of Spring Data JPA
Sample code for search using QBE (Query by Example) of Spring Data JPA
Let's write a Qiita article in org-mode of Emacs !!
Until the use of Spring Data and JPA Part 2
Until the use of Spring Data and JPA Part 1
Make the where clause variable in Spring Data JPA
[Spring Data JPA] Can And condition be used in the automatically implemented method of delete?
Create a MySQL test environment (+ millions of test data) in 5 minutes
[spring] Let's use Spring Data JPA
Get a proxy instance of the component itself in Spring Boot
See the behavior of entity update with Spring Boot + Spring Data JPA
Let's write a test code for login function in Spring Boot
Write test code in Spring Boot
[How to install Spring Data Jpa]
[Rails] I want to send data of different models in a form