SELECT mm.member_id, m.meeting_start_time, m.meeting_end_time
FROM meeting_member mm
JOIN meeting m ON m.id = mm.meeting_id
WHERE mm.member_id = 5;
# 조회 결과
# member_id | meeting_start_time | meeting_end_time
# 5 | 2024-04-08 05:41:58.539000 | 2024-04-08 07:41:58.539000
@Query(value = "SELECT mm.member_id, m.meeting_start_time, m.meeting_end_time " +
"FROM meeting_member mm " +
"JOIN meeting m ON mm.meeting_id = m.id " +
"WHERE mm.member_id = :memberId",
nativeQuery = true)
List<Meeting> getStudyTimeList(Long memberId);
2024-04-08T19:28:32.224+09:00 ERROR 19996 --- [moit] [nio-8080-exec-1]
o.h.engine.jdbc.spi.SqlExceptionHelper : Column 'id' not found.
2024-04-08T19:28:32.246+09:00 ERROR 19996 --- [moit] [nio-8080-exec-1]
o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessResourceUsageException: Unable to find column position by name: id [Column 'id' not found.] [n/a]; SQL [n/a]] with root cause
java.sql.SQLException: Column 'id' not found.
nativeQuery = true
를 사용하여 네이티브 쿼리를 실행하는 경우, 결과를 엔티티 객체(Meeting
)로 반환하려고 했지만, 결과(Join → 컬럼 수 증가)가 엔티티 객체와 일치하지 않기 때문에 오류가 발생public interface MeetingRepository extends JpaRepository<Meeting, Long>
Repository의 리턴타입은 <Meeting, Long> → Meeting으로 정해져 있다. ⇒ NativeQuery를 Dto에 매핑하는 추가 작업 필요 ex) QLRM 라이브러리 사용
public interface MeetingMemberRepository extends JpaRepository<MeetingMember, Long> {
List<MeetingMember> findAllByMember(Member member);
}
List<MeetingMember> testList = meetingMemberRepository.findAllByMember(member);
for (MeetingMember meetingMember : testList) {
log.info(meetingMember.getMember().getId().toString());
log.info(meetingMember.getMeeting().getMeetingStartTime().toString());
log.info(meetingMember.getMeeting().getMeetingEndTime().toString());
}
// Mypage : 6
// Mypage : 2024-04-08T05:41:58.539
// Mypage : 2024-04-08T07:41:58.539
⇒ 너무나 간단한 문제를 멀리 돌아왔다.
참고 블로그
[Spring Data JPA] JPQL 사용 방법(@Query & nativeQuery & DTO Mapping & function)