[Querydsl] 2. 고급 문법

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

1. 프로젝션 대상이 하나

List<String> result = queryFactory
	.select(member.username)
	.from(member)
	.fetch();

• 프로젝션 대상이 하나면 타입을 명확하게 지정할 수 있음
• 프로젝션 대상이 둘 이상이면 튜플이나 DTO로 조회

2. 튜플 조회

com.querydsl.core.Tuple

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 조회

1. 순수 JPA에서 DTO 조회 코드

List<MemberDto> result = em.createQuery(
	"select new study.querydsl.dto.MemberDto(m.username, m.age) " +
			"from Member m", MemberDto.class)
	.getResultList();

• 순수 JPA에서 DTO를 조회할 때는 new 명령어를 사용해야함
• DTO의 package이름을 다 적어줘야해서 지저분함
• 생성자 방식만 지원함

2. 프로퍼티 접근 - Setter

List<MemberDto> result = queryFactory
	.select(Projections.bean(MemberDto.class,
		member.username,
		member.age))
	.from(member)
	.fetch();

3. 필드 직접 접근

List<MemberDto> result = queryFactory
	.select(Projections.fields(MemberDto.class,
		member.username,
		member.age))
	.from(member)
	.fetch();

• Getter, Setter 사용하지 않고 필드에 바로 주입

4. 생성자 사용

List<MemberDto> result = queryFactory
	.select(Projections.constructor(MemberDto.class,
		member.username,
		member.age))
	.from(member)
	.fetch();
}

5. 필드 명칭이 다를 때

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();

• 프로퍼티나, 필드 접근 생성 방식에서 이름이 다를 때 해결 방안
• ExpressionUtils.as(source,alias) : 필드나, 서브 쿼리에 별칭 적용
• username.as("memberName") : 필드에 별칭 적용

---

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

1. 생성자 + @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;
	}
}

• QMemberDto 생성 확인

2. @QueryProjection 활용

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

• 컴파일러로 타입을 체크할 수 있으므로 가장 안전한 방법
• DTO에 QueryDSL 어노테이션을 유지 + DTO까지 Q 파일을 생성해야 하는 단점

3. distinct

List<String> result = queryFactory
	.select(member.username).distinct()
	.from(member)
	.fetch(); 

• distinct는 JPQL의 distinct와 같다

---

⭐동적 쿼리

1. BooleanBuilder

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

2. 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;
}

private BooleanExpression allEq(String usernameCond, Integer ageCond) {
	return usernameEq(usernameCond).and(ageEq(ageCond));
}

• where 조건에 null 값은 무시된다
• 메서드를 다른 쿼리에서도 재활용 할 수 있다
• 쿼리 자체의 가독성이 높아진다
• null 체크는 주의해서 처리해야함

---

📗수정, 삭제 벌크 연산

1. 쿼리 한번으로 대량 데이터 수정

long count = queryFactory
		.update(member)
		.set(member.username, "비회원")
		.where(member.age.lt(28))
		.execute();

2. 기존 숫자에 1 더하기

long count = queryFactory
		.update(member)
		.set(member.age, member.age.add(1))
		.execute();

• 곱하기: multiply(x)

3. 쿼리 한번으로 대량 데이터 삭제

long count = queryFactory
 .delete(member)
 .where(member.age.gt(18))
 .execute();

• JPQL 배치와 마찬가지로, 영속성 컨텍스트에 있는 엔티티를 무시하고 실행
• 배치 쿼리를 실행하고 나면 영속성 컨텍스트를 초기화 하는 것이 안전

---

📘SQL function 호출하기?

• SQL function은 JPA와 같이 Dialect에 등록된 내용만 호출할 수 있다

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)))

lower 같은 ansi 표준 함수들은 querydsl이 상당부분 내장

.where(member.username.eq(member.username.lower()))

---

🔎페이징

• 커스텀 repository

1. 복잡한 페이징 예시

/** 복잡한 페이징
 *  데이터 조회 쿼리와, 전체 카운트 쿼리를 분리
 */
import org.springframework.data.support.PageableExecutionUtils; //패키지 변경
@Override
public Page<MemberTeamDto> searchPageComplex(MemberSearchCondition condition, Pageable pageable) {
	List<MemberTeamDto> content = queryFactory
			.select(new QMemberTeamDto(
					member.id.as("memberId"),// QDto를 사용하기에 as 필요없음
                    member.username,
                    member.age,
                    team.id, 
                    team.name))
			.from(member)
			.leftJoin(member.team, team)
			.where(
                  usernameEq(condition.getUsername()),
                  teamNameEq(condition.getTeamName()),
                  ageGoe(condition.getAgeGoe()),
                  ageLoe(condition.getAgeLoe()))
            .offset(pageable.getOffset())
            .limit(pageable.getPageSize())
            .fetch();

JPAQuery<Long> countQuery = queryFactory
        .select(member.count())
        .from(member)
        .leftJoin(member.team, team)
        .where(
                usernameEq(condition.getUsername()),
                teamNameEq(condition.getTeamName()),
                ageGoe(condition.getAgeGoe()),
                ageLoe(condition.getAgeLoe())
        );

return PageableExecutionUtils.getPage(content, pageable, countQuery::fetchOne);
}

2. CountQuery 최적화

PageableExecutionUtils.getPage()로 최적화

JPAQuery<Member> countQuery = queryFactory
        .select(member)
        .from(member)
        .leftJoin(member.team, team)
        .where(usernameEq(condition.getUsername()),
                teamNameEq(condition.getTeamName()),
                ageGoe(condition.getAgeGoe()),
                ageLoe(condition.getAgeLoe()));

// return new PageImpl<>(content, pageable, total);
return PageableExecutionUtils.getPage(content, pageable, countQuery::fetchCount);

---

☠️Querydsl 지원 클래스 직접 만들기

• 스프링 데이터가 제공하는 QuerydslRepositorySupport 가 지닌 한계를 극복하기 위해 직접 Querydsl 지원 클래스를 만들어보자

장점

• 스프링 데이터가 제공하는 페이징을 편리하게 변환
• 페이징과 카운트 쿼리 분리 가능
• 스프링 데이터 Sort 지원
• select() , selectFrom() 으로 시작 가능
• EntityManager , QueryFactory 제공

@Repository
public abstract class Querydsl4RepositorySupport {
    private final Class domainClass;
    private Querydsl querydsl;
    private EntityManager entityManager;
    private JPAQueryFactory queryFactory;

    public Querydsl4RepositorySupport(Class<?> domainClass) {
        Assert.notNull(domainClass, "Domain class must not be null!");
        this.domainClass = domainClass;
    }

    @Autowired
    public void setEntityManager(EntityManager entityManager) {
        Assert.notNull(entityManager, "EntityManager must not be null!");
        JpaEntityInformation entityInformation =
                JpaEntityInformationSupport.getEntityInformation(domainClass, entityManager);
        SimpleEntityPathResolver resolver = SimpleEntityPathResolver.INSTANCE;
        EntityPath path = resolver.createPath(entityInformation.getJavaType());
        this.entityManager = entityManager;
        this.querydsl = new Querydsl(entityManager, new
                PathBuilder<>(path.getType(), path.getMetadata()));
        this.queryFactory = new JPAQueryFactory(entityManager);
    }

    @PostConstruct
    public void validate() {
        Assert.notNull(entityManager, "EntityManager must not be null!");
        Assert.notNull(querydsl, "Querydsl must not be null!");
        Assert.notNull(queryFactory, "QueryFactory must not be null!");
    }

    protected JPAQueryFactory getQueryFactory() {
        return queryFactory;
    }

    protected Querydsl getQuerydsl() {
        return querydsl;
    }

    protected EntityManager getEntityManager() {
        return entityManager;
    }

    protected <T> JPAQuery<T> select(Expression<T> expr) {
        return getQueryFactory().select(expr);
    }

    protected <T> JPAQuery<T> selectFrom(EntityPath<T> from) {
        return getQueryFactory().selectFrom(from);
    }

    protected <T> Page<T> applyPagination(Pageable pageable,
                                          Function<JPAQueryFactory, JPAQuery> contentQuery) {
        JPAQuery jpaQuery = contentQuery.apply(getQueryFactory());
        List<T> content = getQuerydsl().applyPagination(pageable,
                jpaQuery).fetch();
        return PageableExecutionUtils.getPage(content, pageable,
                jpaQuery::fetchCount);
    }

    protected <T> Page<T> applyPagination(Pageable pageable,
                                          Function<JPAQueryFactory, JPAQuery> contentQuery, Function<JPAQueryFactory,
            JPAQuery> countQuery) {
        JPAQuery jpaContentQuery = contentQuery.apply(getQueryFactory());
        List<T> content = getQuerydsl().applyPagination(pageable, jpaContentQuery).fetch();
        JPAQuery countResult = countQuery.apply(getQueryFactory());
        return PageableExecutionUtils.getPage(content, pageable,
                countResult::fetchCount);
    }
}

Querydsl4RepositorySupport 사용 코드

@Repository
public class MemberTestRepository extends Querydsl4RepositorySupport {
    public MemberTestRepository() {
        super(Member.class);
    }

    public List<Member> basicSelect() {
        return select(member)
                .from(member)
                .fetch();
    }

    public List<Member> basicSelectFrom() {
        return selectFrom(member)
                .fetch();
    }

    public Page<Member> searchPageByApplyPage(MemberSearchCondition condition,
                                              Pageable pageable) {
        JPAQuery<Member> query = selectFrom(member)
                .leftJoin(member.team, team)
                .where(usernameEq(condition.getUsername()),
                        teamNameEq(condition.getTeamName()),
                        ageGoe(condition.getAgeGoe()),
                        ageLoe(condition.getAgeLoe()));
        List<Member> content = getQuerydsl().applyPagination(pageable, query)
                .fetch();
        return PageableExecutionUtils.getPage(content, pageable,
                query::fetchCount);
    }

    public Page<Member> applyPagination(MemberSearchCondition condition,
                                        Pageable pageable) {
        return applyPagination(pageable, contentQuery -> contentQuery
                .selectFrom(member)
                .leftJoin(member.team, team)
                .where(usernameEq(condition.getUsername()),
                        teamNameEq(condition.getTeamName()),
                        ageGoe(condition.getAgeGoe()),
                        ageLoe(condition.getAgeLoe())));
    }

    public Page<Member> applyPagination2(MemberSearchCondition condition,
                                         Pageable pageable) {
        return applyPagination(pageable, contentQuery -> contentQuery
                        .selectFrom(member)
                        .leftJoin(member.team, team)
                        .where(usernameEq(condition.getUsername()),
                                teamNameEq(condition.getTeamName()),
                                ageGoe(condition.getAgeGoe()),
                                ageLoe(condition.getAgeLoe())),
                countQuery -> countQuery.selectFrom(member)
                        .leftJoin(member.team, team)
                        .where(usernameEq(condition.getUsername()),
                                teamNameEq(condition.getTeamName()),
                                ageGoe(condition.getAgeGoe()),
                                ageLoe(condition.getAgeLoe()))
        );
    }

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

---

Reference

[인프런] 실전! Querydsl - 김영한