[JAVA] 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);
}