package com.beige0905.test.repository;
import com.beige0905.test.repository.vo.StudentVo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import java.time.LocalDate;
import java.util.List;
@Repository
public interface StudentTotalScore extends JpaRepository<StudentVo, Long> {
@Query(nativeQuery = true,
value ="SELECT student_id AS student_id, student_name AS student_name," +
" SUM(math_score) AS math_score, SUM(eng_score) AS eng_score, SUM(kor_score) AS kor_score " +
" FROM student" +
" WHERE insert_date BETWEEN ?1 AND ?2" +
" GROUP BY student_id, student_name" +
" ORDER BY student_id")
List<StudentVo> selectStudentScoreList(LocalDate startDate, LocalDate endDate);
}
이렇게 시도하는 경우 student 테이블의 컬럼값인 address, phone의 컬럼명을 찾을수 없습니다. 라는 오류를 보게 될 것이다.
해결하기 위해 SELECT절에 MAX(address), MAX(phone)를 추가하여 의미없는 값을 가져오면 해결은 되지만
이것보다 컬럼수가 더 많게 된다면 불필요한 값이 너무 많아지고, 코드도 복잡하게 된다.
찾아보다가 return 타입을 List<VO>가 아닌 List<Object[]>로 값을 받은 후 Service단에서 데이터를 가공하는 방법으로 변경하였다.
Repository
package com.beige0905.test.repository;
import com.beige0905.test.repository.vo.StudentVo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import java.time.LocalDate;
import java.util.List;
@Repository
public interface StudentTotalScore extends JpaRepository<StudentVo, Long> {
@Query(nativeQuery = true,
value ="SELECT student_id AS student_id, student_name AS student_name," +
" SUM(math_score) AS math_score, SUM(eng_score) AS eng_score, SUM(kor_score) AS kor_score " +
" FROM student" +
" WHERE insert_date BETWEEN ?1 AND ?2" +
" GROUP BY student_id, student_name" +
" ORDER BY student_id")
List<Object[]> selectStudentScoreList(LocalDate startDate, LocalDate endDate);
}
Service
public List<StudentVo> getStudentTotalScore(LocalDate startDate, LocalDate endDate) {
List<Object[]> list = studentRepository.selectStudentScoreList(startDate, endDate);
List<StudentVo> result = new ArrayList<>();
for(Object[] obj : list) {
result.add(StudentVo.builder()
.studentId(obj[0].toString())
.studentName(obj[1].toString())
.mathScore(Long.parseLong(String.valueOf(obj[2])))
.engScore(Long.parseLong(String.valueOf(obj[3])))
.korScore(Integer.parseInt(String.valueOf(obj[4])))
.build());
}
return result;
}
public Page<StudentVo> seachList(List<Search> search, Pageable pageable) {
String startDate = null;
String endDate = null;
String[] orderBy = String.valueOf(pageable.getSort()).replaceAll(" ","").split(":");
String aa = orderBy[0] + " " + orderBy[1];
for(ColumnSearch searchVo : search) {
if(searchVo.getTargetColumn().equalsIgnoreCase("start")) {
startDate = searchVo.getSearchValue();
} else if(searchVo.getTargetColumn().equalsIgnoreCase("end")) {
endDate = searchVo.getSearchValue();
} else {
log.info("check start date or end date");
}
}
List<StudentVo> result = studentRepository.getStudentTotalScore(startDate, endDate);
Collections.sort(result);
List<StudentVo> sortResult = new ArrayList<>();
if(orderBy[1].equals("ASC")) {
switch(orderBy[0]) {
case "sudentId":
sortResult = result.stream().sorted(Comparator.comparing(StudentVo::sudentId)).collect(Collectors.toList());
break;
case "studentName":
sortResult = result.stream().sorted(Comparator.comparing(StudentVo::studentName)).collect(Collectors.toList());
break;
case "totalMathScore":
sortResult = result.stream().sorted(Comparator.comparing(StudentVo::totalMathScore)).collect(Collectors.toList());
break;
case "totalEngScore":
sortResult = result.stream().sorted(Comparator.comparing(StudentVo::totalEngScore)).collect(Collectors.toList());
break;
default:
log.info("ORDER BY ASC 컬럼명 오류");
break;
}
} else if(orderBy[1].equals("DESC")) {
switch (orderBy[0]) {
case "sudentId":
sortResult = result.stream().sorted(Comparator.comparing(StudentVo::sudentId).reversed()).collect(Collectors.toList());
break;
case "studentName":
sortResult = result.stream().sorted(Comparator.comparing(StudentVo::studentName).reversed()).collect(Collectors.toList());
break;
case "totalMathScore":
sortResult = result.stream().sorted(Comparator.comparing(StudentVo::totalMathScore).reversed()).collect(Collectors.toList());
break;
case "totalEngScore":
sortResult = result.stream().sorted(Comparator.comparing(StudentVo::totalEngScore).reversed()).collect(Collectors.toList());
break;
default:
log.info("ORDER BY DESC 컬럼명 오류");
break;
}
}
PageRequest pageRequest = PageRequest.of(pageable.getPageNumber(), pageable.getPageSize());
int start = (int) pageRequest.getOffset();
int end = Math.min((start + pageRequest.getPageSize()), sortResult.size());
Page<StudentVo> page = new PageImpl<>(sortResult.subList(start, end), pageRequest, sortResult.size());
log.info("Page : {} ", page.getContent());
}
return page;
}
열심히 삽질하다가 찾은 것이 Projection을 이용하는 방법이다.
먼저 필요한 기존VO에서 데이터들(DTO로 변환할 데이터)만 모은 인터페이스 VO를 생성한다.
Interface VO
package com.beige0905.test.repository.vo;
public interface IStudentVo {
int getStudentId();
String getStudentName();
int getTotalMathScore();
int getTotalEngScore();
}
기존 VO에 .map 함수를 이용하여 IStudentVo -> StudentVo로 Convert 해주는 method를 추가한다.
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: {VO class name} near line 1, column 249 [query문]
오류 코드
@Query("SELECT v.studentId AS studentId, v.studentName AS studentName, SUM(v.mathScore) AS mathScore, "
+"SUM(v.engScore) AS engScore, v.className AS className, v.grade AS grade"
+"FROM StudentVo v WHERE v.insertDate BETWEEN ?1 AND ?2 GROUP BY v.studentId, v.studentName, v.className, v.grade")
JPA를 사용한 Repository에서 나타난 오류이다
딱봐도 구문이 틀린 오류인데 쿼리문을 봤을때는 이상이 없어서 사실 249번까지 세었는데 로그에서도 틀린 부분이 확인되었다ㅎ
그렇다 FROM 앞에 띄어쓰기가 안되어서 그런거였다
수정코드
@Query("SELECT v.studentId AS studentId, v.studentName AS studentName, SUM(v.mathScore) AS mathScore, "
+"SUM(v.engScore) AS engScore, v.className AS className, v.grade AS grade "
+"FROM StudentVo v WHERE v.insertDate BETWEEN ?1 AND ?2 GROUP BY v.studentId, v.studentName, v.className, v.grade")
