QueryDsl 중급문법

프로젝션과 결과 반환 - 기본

//프로젝션 대상이 하나
List<String> result = queryFactory
    .select(member.username)
    .from(member)
    .fetch();

//프로젝션이 둘 이상
List<Tuple> result = queryFactory
    .select(member.username, member.age)
    .from(member)
    .fetch();


for (Tuple tuple : result){
    String username = tuple.get(member.username);
    Integer age = tuple.get(member.age);
    System.out.println("username=" + username);
    System.out.println("age="+ age);
}

프로젝션과 결과 반환 - DTO 조회

//MemberDto

@Data
public class MemberDto{
    private String username;
    private int age;

    public MemberDto(){
    }

    public MemberDto(String username, int age){
        this.username = username;
        this.age = age;
    }
}

//순수 JPA DTO조회 코드
List<MemberDto> result = em.createQuery(
    "select new study.querydsl.dto.MemberDto
    (m.username, m.age)" + "from Member m", MemberDto.class)
    .getResultList();

Query 빈 생성

3가지 방법 지원

• 프로퍼티 접근
• 필드 직접 접근
• 생성자 사용

//프로퍼티 접근 - setter
List<MemberDto> result = queryFactory
    .select(Projections.bean(MemberDto.class,
        member.username,
        member.age))
    .from(member)
    .fetch();


//필드 직접 접근
List<MemberDto> result = queryFactory
    .select(Projections.fields(MemberDto.class,
        member.username,
        member.age))
    .from(member)
    .fetch()


//별칭이 다를때
@Data
public class UserDto{
    private String name;
    private int age;
}

List<UserDto> fetch = queryFactory
    .select(Projections.fields(UserDto.class,
    member.username.as("name")
    ExpressionUtils.as(
        JPAExpressions
            .select(memberSub.age.max())
             .from(memberSub),"age")
        )
    ).from(member)
    .fetch();


//생성자 사용
List<MemberDto> result = queryFactory
    .select(Projections.constructor(MemberDto.class,
    member.username,
    member.age))
    .from(member)
    .fetch();

프로젝션과 결과 반환 - @QueryProjection

//생성자 + @QueryProjection

@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;
    }
}

//활용
List<MemberDto> result = queryFactory
    .select(new QMemberDto(member.username, member.age))
    .from(member)
    .fetch();

동적쿼리 - BooleanBuilder

@Test
public void 동적쿼리_BooleanBuilder() throws Exception{
    String usernameParam = "member1";
    Integer ageParam = 10;

    List<Member> result = searchMember1(usernameParam, ageParam);
    Assertions.assertThat(result.size()).isEqualTo(1);
}

private List<Member> searchMember1
(String usernameCond, Integer ageCond){
    BooleanBuilder builder = new BooleanBuilder();
    if(usernameCond != null){
        builder.and(member.username.eq(usernameCond));
    }
    if(age != null){
        builder.and(member.age.eq(ageCond));
    }
    return queryFactory
        .selectFrom(member)
        .where(builder)
        .fetch();
}

동적 쿼리 - Where 다중 파라미터 사용

@Test
public void 동적쿼리_WhereParam() throws Exception{
    String usernameParam = "member1";
    Integer ageParam = 10;

    List<Member> result = searchMember2(usernameParam, ageParam);
    Assertions.assertThat(result.size()).isEqualTo(1);
}

private List<Member> searchMember2
(String usernameCond, Integer ageCond){
    return queryFactory
        .selectFrom(member)
        .where(usernameEq(usernameCond), ageEq(ageCond))
        .fetch();
}

private BooleanExpression usernameEq(String usernameCond){
    return 
    usernmaeCond != null ? member.username.eq(username) : null;
}

private BooleanExpression ageEq(Integer ageCond){
    return ageCond != null ? member.age.eq(ageCond) : null;
}

//조합 가능
private BooleanExprssion allEq(String usernameCond, Integer ageCond){
    return usernameEq(usernameCond).and(ageEq(ageCond));
}

수정 삭제 벌크 연산

//쿼리 한번으로 대량 데이터 수정
long count = queryFactory
    .update(member)
    .set(member.username,"비회원")
    .where(member.age.lt(28))
    .execute();

// 기존 숫자에 1더하기
long count = queryFactory
    .update(member)
    .set(member.age, member.age.add(1))
    .execute();

// 대량 데이터 삭제
long count = queryFactory
    .delete(member)
    .where(member.age.gt(18))
    .execute();

SQL function 호출

//member -> M으로 변경하는 replace 함수 사용

String result = queryFactory
    .select(Expressions.StringTemplate
    ("function('replace', {0},{1},{2}",
     member.username, "member","M"))
    .from(member)
    .fetchFirst();

//소문자로 변경 비교
.select(member.username)
.from(member
.where(member.username.eq
(Expressions.stringTemplate
("function('lower',{0}", member.username))))