skarltjr
commented
3 years ago DTO로 반환하기 - 프로젝션
- dto
@Data public class MemberDto { private String username; private int age; public MemberDto() { } @QueryProjection public MemberDto(String username, int age) { this.username = username; this.age = age; } } - dto로 반환하기
List<MemberDto> result = queryFactory .select(new QMemberDto(member.username, member.age)) .from(member) .fetch(); - 이 방법은 컴파일에 에러를 걸러낼 수 있는 장점이 있다.
- 주의할 점은 dto의 생성자에 @QueryProjection어노테이션과 dto도 q파일을 생성해야한다.
동적쿼리 ★
- BooleanBuilder와 where 다중 파라미터 사용
- where 다중 파라미터를 사용하면 가독성과 재활용성 증가
-
private List<Member> searchMember2(String usernameCond, Integer ageCond) { return queryFactory .selectFrom(member) .where(usernameEq(usernameCond), ageEq(ageCond)) .fetch(); } private BooleanExpression usernameEq(String usernameCond) { return usernameCond != null ? member.username.eq(usernameCond) : null; } private BooleanExpression ageEq(Integer ageCond) { return ageCond != null ? member.age.eq(ageCond) : null; }
대량 수정, 벌크연산
- where조건에 부합하는 멤버들 대상으로 모두 이름을 변겅하기
queryFactory .update(member) .set(member.username, "비회원") .where(member.age.lt(28)) .execute(); - 모든 멤버의 나이 1더하기
queryFactory .update(member) .set(member.age, member.age.add(1)) .execute(); - 당연히 대량 삭제도 가능
long count = queryFactory .delete(member) .where(member.age.gt(18))where절을 통해 동적쿼리를 사용한다면
public List<MemberTeamDto> search(MemberSearchCondition condition) { return queryFactory .select(new QMemberTeamDto( member.id.as("memberId"), member.username, member.age, team.id.as("teamId"), team.name.as("teamName"))) .from(member) .leftJoin(member.team, team) .where(usernameEq(condition.getUsername()), teamNameEq(condition.getTeamName()), ageGoe(condition.getAgeGoe()), ageLoe(condition.getAgeLoe())) .fetch(); } private BooleanExpression usernameEq(String username) { return isEmpty(username) ? null : member.username.eq(username); } private BooleanExpression teamNameEq(String teamName) { return isEmpty(teamName) ? null : team.name.eq(teamName); } private BooleanExpression ageGoe(Integer ageGoe) { return ageGoe == null ? null : member.age.goe(ageGoe); } private BooleanExpression ageLoe(Integer ageLoe) { return ageLoe == null ? null : member.age.loe(ageLoe); }
Query dsl vs JPQL
query dsl을 사용하기위해선 Q클래스를 생성해야한다 - 메이븐 컴파일 후 생성된것을 확인한다.
jpql
query dsl
스프링 프레임워크는 여러 쓰레드에서 동시에 같은EntityManager에 접근해도, 트랜잭션 마다 별도의 영속성 컨텍스트를 제공하기 때문에, 동시성 문제는 걱정하지 않아도 된다.
스프링데이터jpa의 확장성을 이용하여 query dsl을 사용하자
결과 조회
정렬
페이징
이를 통해 페이징한 결과를 리턴
집합에 대한 함수도 사용가능 / sum, avg ,max ,min등
.select(member.count(), member.age.sum(), member.age.avg(), member.age.max(), member.age.min()) .from(member) .fetch();
팀의 이름과 각 팀의 평균연령을 구하자 @Test public void group() throws Exception { List result = queryFactory
.select(team.name, member.age.avg())
.from(member)
.join(member.team, team)
.groupBy(team.name)
.fetch();
Tuple teamA = result.get(0);
Tuple teamB = result.get(1);
assertThat(teamA.get(team.name)).isEqualTo("teamA");
assertThat(teamA.get(member.age.avg())).isEqualTo(15);
assertThat(teamB.get(team.name)).isEqualTo("teamB");
assertThat(teamB.get(member.age.avg())).isEqualTo(35);
}
@Test public void join() throws Exception { QMember member = QMember.member; QTeam team = QTeam.team; List result = queryFactory
.selectFrom(member)
.join(member.team, team)
.where(team.name.eq("teamA"))
.fetch();
assertThat(result)
.extracting("username")
.containsExactly("member1", "member2");
}
List result = queryFactory
.select(member, team)
.from(member)
.leftJoin(member.team, team).on(team.name.eq("teamA"))
.fetch();
@Override public Page findByKeyword(String keyWord, Pageable pageable) {
JPQLQuery query = from(study)
.where(study.published.isTrue()
.and(study.title.containsIgnoreCase(keyWord))
.or(study.tags.any().title.containsIgnoreCase(keyWord))
.or(study.zones.any().localNameOfCity.containsIgnoreCase(keyWord)))
.leftJoin(study.tags, tag).fetchJoin()
.leftJoin(study.zones, zone).fetchJoin()
.distinct();
JPQLQuery studyJPQLQuery = getQuerydsl().applyPagination(pageable, query);
QueryResults result = studyJPQLQuery.fetchResults();
return new PageImpl<>(result.getResults(), pageable, result.getTotal());
}
나이가 평균나이 이상인 회원을 고르자 @Test public void subQueryGoe() throws Exception { QMember memberSub = new QMember("memberSub"); List result = queryFactory
.selectFrom(member)
.where(member.age.goe(
JPAExpressions
.select(memberSub.age.avg())
.from(memberSub)
))
.fetch();
assertThat(result).extracting("age")
.containsExactly(30,40);
}
List fetch = queryFactory
.select(member.username,
JPAExpressions
.select(memberSub.age.avg())
.from(memberSub)
).from(member)
.fetch();
for (Tuple tuple : fetch) {
System.out.println("username = " + tuple.get(member.username));
System.out.println("age = " +
tuple.get(JPAExpressions.select(memberSub.age.avg())
.from(memberSub)));
}
List result = queryFactory
.select(new CaseBuilder()
.when(member.age.between(0, 20)).then("0~20살")
.when(member.age.between(21, 30)).then("21~30살")
.otherwise("기타"))
.from(member)
.fetch();