@Query

Spring Data JPA의 Query Methods는 간단하게 메서드의 이름을 바탕으로 조회하는 데이터의 조건을 지정할 수 있어서 매우 간편하다.

그러나, 이름이 길어진다는 점, 조금 복잡한 조건, 집계함수 등을 활용하기 어렵다는 단점이 있다.

=> 직접 실행할 SQL을 작성하기 위해서 @Query 어노테이션을 활용할 수 있다.

 

ex) ERD

 

1. @Query

: 어노테이션으로 MyBatis의 Mapper Annotation처럼 SQL을 직접 작성할 수 있게 해주는 기능. 

1) JPQL과 SQL

- JPQL (Java Persistence Query Language)라는 언어를 기본으로 사용. (엔티티와 속성을 바탕으로 동작하는 언어)

- 또는 그냥 SQL을 전달할 수 있다 -> nativeQuery = true로 설정.

- 표현법이 다르다.

- SQL을 사용해서 데이터 조회 시에 조회된 데이터의 컬럼과 엔티티가 잘 일치해야 정상적으로 동작한다.

ex) 

	// JPQL
	@Query("SELECT l from Lecture l where l.startTime<11")
    List<Lecture> findLecturesBeforeLunch();

	// Native SQL
    @Query(value = "select * from lecture where start_time<11",
            nativeQuery = true)
    List<Lecture> findLecturesBeforeLunchNative();

// Controller
    @GetMapping("test-query")
    public String testQuery() {
        List<Lecture> jpql = lectureRepository.findLecturesBeforeLunch();
        List<Lecture> nativeSql = lectureRepository.findLecturesBeforeLunchNative();

        for (Lecture l : jpql) {
            log.info("{}:  {}", l.getName(), l.getStartTime());
        }
        log.info("==================================");
        for (Lecture l : nativeSql) {
            log.info("{}:  {}", l.getName(), l.getStartTime());
        }
        
        //ver2. Stream API 사용도 가능하다.
        lectureRepository.findLecturesBeforeLunch().forEach(lecture ->
                log.info("{}: {}", lecture.getName(), lecture.getStartTime()));
        lectureRepository.findLecturesBeforeLunchNative().forEach(lecture ->
                log.info("{}: {}", lecture.getName(), lecture.getStartTime()));
        return "done";
    }

이렇게 결과값도 잘 나왔다.

 

2) 매개변수 전달

- ?n(숫자)으로 순서에 맞는 매개변수를 인자로 전달 가능하다. (매개변수는 숫자에 맞게 할당된다)

- 매개변수에 이름을 지정할 수도 있다. (Named Parameter)

   :<이름> 설정하고, 인자에 @Param("<이름>")으로 동일하게 통일해주면 된다. 

- native sql에도 적용 가능하다.

- IN, NOT IN과 같은 조건에 Collection 전달 가능

// 매개변수 설정
// 순서 매개변수
    @Query("select l from Lecture  l where l.startTime =?1 and l.endTime=?2")
    List<Lecture> findLecturesByTime(
            Integer startTime, Integer endTime
    );

// Named Parameter
    @Query("select l from Lecture l where l.startTime = :start and l.endTime = :end")
    List<Lecture> findLecturesByTimeNamed(
            @Param("start") Integer startTime,
            @Param("end") Integer endTime
    );
// native query도 가능
    @Query(value = "select * from lecture where start_time=?1 and end_time=?2",
            nativeQuery = true)
    List<Lecture> findLecturesByTimeNative(Integer startTime, Integer endTime);

// IN, NOT IN과 같은 조건에 Collection 전달 가능
    @Query("select l from Lecture l where l.day in :days")
    List<Lecture> findByDayIn(
            @Param("days")Collection<String> days
    );

// Controller
    @GetMapping("test-query")
    public String testQuery() {
        log.info("=====순서 매개변수");
        lectureRepository.findLecturesByTime(10, 13).forEach(lecture ->
                log.info("{}: {} ~ {}", lecture.getName(), lecture.getStartTime(), lecture.getEndTime()));

        log.info("======named parameters");
        lectureRepository.findLecturesByTimeNamed(10, 13).forEach(lecture ->
                log.info("{}: {}~{}", lecture.getName(), lecture.getStartTime(), lecture.getEndTime()));

        log.info("======native sql");
        lectureRepository.findLecturesByTimeNative(10, 15).forEach(lecture ->
                log.info("{}: {}~{}", lecture.getName(), lecture.getStartTime(), lecture.getEndTime()));

        log.info("======in 조건절 Collection");
        lectureRepository.findByDayIn(Set.of("mon", "tue")).forEach(lecture ->
                log.info("{}: {}", lecture.getName(), lecture.getDay()));
    return "done";
    }

 

 

3) Pageable과 Sort

- Pageable과 Sort를 받을 수도 있다! (메서드 오버로딩도 가능~~~)

- native이면 Pageable만 가능 : 따로 설정을 해주어야 한다. >> countQuery로!

	// JPQL Pagination
	@Query("select l from Lecture l where l.startTime < 11")
    Page<Lecture> findLecturesBeforeLunch(Pageable pageable);

	// JPQL Sort
    @Query("select l from Lecture l where l.startTime < 11")
    List<Lecture> findLecturesBeforeLunch(Sort sort);

	// native sql Pagination
    @Query(value = "select * from lecture where start_time<11",
            countQuery = "select count(*) from Lecture where lecture.start_time<11",
            nativeQuery = true)
    List<Lecture> findLecturesBeforeLunchNative(Pageable pageable);
    
    // native sql은 Sort 구현 불가.

    @GetMapping("test-query")
    public String testQuery() {
    
	// Pagination (4개씩 0쪽)
    log.info("======Pagination (3개씩 0쪽)");
    Page<Lecture> lecturePage = lectureRepository.findLecturesBeforeLunch(
            PageRequest.of(0, 3));
    lecturePage.stream().forEach(lecture
            -> log.info("{}: {}", lecture.getName(), lecture.getStartTime()));

    log.info("======Sort id 기준으로 내림차순 정렬");
    lectureRepository.findLecturesBeforeLunch(Sort.by(Sort.Direction.DESC, "id"))
            .forEach(lecture -> log.info("{}: {}", lecture.getId(), lecture.getStartTime()));

    log.info("======native sql Pagination (3개씩 0쪽");
    lectureRepository.findLecturesBeforeLunchNative(PageRequest.of(0, 3))
            .forEach(lecture -> log.info("{}: {}", lecture.getName(), lecture.getStartTime()));

    return "done";
}

 

 

4) @Modifying

: 상황에 따라 update, delete를 하고 싶다면 @Query와 함께 @Modifying을 추가해주어야 한다!

 

ex) 3시간 초과 강의를 찾고, 그 강의들을 모두 3시간짜리로 수정하자.

- 이 때 @Modifying을 붙여주자. 

    // 3시간 초과 강의 찾기
    @Query("select l from Lecture l where (l.endTime-l.startTime)>3")
    List<Lecture> tooLongLectures();

    // 그 강의들을 시작시간+3으로 바꿔서 3시간짜리로 바꾼다.
    @Modifying
    @Query("update Lecture l set l.endTime = l.startTime+3 where l.endTime-l.startTime>3")
    Integer setLectureMaxHour3();

    @Transactional
    @GetMapping("test-modifying")
    public String modifying() {
        log.info("modifying===================");

        log.info("lectures over 3 hours() : {}", lectureRepository.tooLongLectures().size());

        lectureRepository.setLectureMaxHour3();
        log.info("lectures over 3 hours() : {}", lectureRepository.tooLongLectures().size());
        return "done";
    }

>> 14개에서 update 이후 0개로 바뀌었다! 

 

+ 참고! 

- JPQL에서 INSERT는 지원하지 않는다...! JPA를 사용하거나,,,,, nativeQuery를 설정해서 INSERT문을 작성해줘야한다.

// native Query로 insert문 작성하기
    @Modifying
    @Query(value = "insert into lecture (end_time, start_time, instructor_id, day, name) " +
            "VALUES (:endTime, :startTime, :instructorId, :day, :name)",
            nativeQuery = true)
    void insertLecture(
            @Param("endTime") Integer endTime,
            @Param("startTime") Integer startTime,
            @Param("instructorId") Long instructorId,
            @Param("day") String day,
            @Param("name") String name
    );

// Controller
    @Transactional
    @GetMapping("test-modifying")
    public String modifying() {
        lectureRepository.insertLecture(13, 9, 2L, "mon", "sql");

        lectureRepository.findLecturesByTime(9, 13).forEach(
                lecture -> log.info("{}: {}", lecture.getName(), lecture.getDay())
        );
        return "done";
    }

 

 

2. 집계 함수와 Projection

: @Query에 집계함수를 사용할 수 있다.

1) List<Object[]> 반환

기본 반환형은 List<Object[]>이다. >> 활용 시에 여러 불편함을 야기하게 된다. ㅠ ㅠ 

public interface InstructorRepository extends JpaRepository<Instructor, Long> {
    //집계 함수
    @Query("select l.instructor, count(*) " +
            "from Lecture l group by l.instructor")
    List<Object[]> selectILCountObject();
}

    @GetMapping("test-agg")
    public String testAggregate() {
		// List<Object[]>형태로 받아서
		List<Object[]> results = instructorRepository.selectILCountObject();

		// 하나하나 Object[]배열을 까서 객체에 형변환하여 넣어주어야 한다...
        for (Object[] row : results) {
            Instructor instructor = (Instructor) row[0];
            Long count = (Long) row[1];
            log.info("instructor: {}, count: {}", instructor.getName(), count);
        }
        return "done";
    }

 

2)  새로운 인스턴스를 만들어서 (Dto)로 결과를 표현하자.

- dto 생성 : 결과 데이터가 가질 모습을 정의해준다. (Instructor 객체와 count(*)의 결과인 Long)

@Getter
@AllArgsConstructor
public class ILCountDto {
    private Instructor instructor;
    private Long count;
}

- 쿼리문 작성 : 쿼리문 안에서 new 객체를 생성해준다.

** (중요한 것은 패키지 이름을 포함한 전체 클래스 이름을 지정해주어야 한다!!!)

    @Query("select new com.example.jpasecond.school.dto.ILCountDto(l.instructor, count(*)) " +
            "from Lecture l group by l.instructor")
    List<ILCountDto> selectILCountDto();

- 쿼리 메서드 사용 : 메서드 사용은 아까보다 훨씬 낫다..

    @GetMapping("test-agg")
    public String testAggregate() {
        List<ILCountDto> resultDtos = instructorRepository.selectILCountDto();
        for (ILCountDto dto : resultDtos) {
            log.info("instructor: {}, count: {}",
                    dto.getInstructor().getName(),
                    dto.getCount());
        }
        return "done";
    }

 

3) Projection

- Projection interface 생성 : 안에 get 메서드도 함께 넣어주기.

public interface ILCountProjection {
    Instructor getInstructor();
    Long getLectureCount();
}

 

- alias 사용해 쿼리문 작성

    @Query("select l.instructor as instructor, count(*) as lectureCount " +
            "from Lecture l group by l.instructor")
    List<ILCountProjection> selectILCountProjection();

 

- 쿼리 메서드 사용 : get 메서드 사용.

    @GetMapping("test-agg")
    public String testAggregate() {
        List<ILCountProjection> resultProjections = instructorRepository.selectILCountProjection();
        for (ILCountProjection i : resultProjections) {
            log.info("instructor: {}, count: {}",
                    i.getInstructor().getName(),
                    i.getLectureCount());
        }
        return "done";
    }