개발 필기장

제약이 커서 복잡한 실무 환경에서 사용하기에는 많이 부족하다.

인터페이스 지원 - QuerydslPredicateExecutor

Querydsl Web 지원

리포지토리 지원 - QuerydslRepositorySupport

Querydsl 지원 클래스 직접 만들기

스프링 데이터 JPA 리포지토리로 변경

repository 패키지 - MemberRepository interface 생성

package study.querydsl.repository;

public interface MemberRepository extends JpaRepository<Member, Long> {
    List<Member> findByUsername(String username);
}

test 작성

package study.querydsl.repository;

@SpringBootTest
@Transactional
class MemberRepositoryTest {

    @Autowired EntityManager em;
    @Autowired MemberRepository memberRepository;

    @Test
    public void basicTest() {
        Member member = new Member("member1", 10);
        memberRepository.save(member);

        Member findMember = memberRepository.findById(member.getId()).get();
        assertThat(findMember).isEqualTo(member);

        List<Member> result1 = memberRepository.findAll();
        assertThat(result1).containsExactly(member);

        List<Member> result2 = memberRepository.findByUsername("member1");
        assertThat(result2).containsExactly(member);
    }
}

사용자 정의 리포지토리

사용자 정의 리포지토리 사용법

1. 사용자 정의 인터페이스 작성

repository 패키지 - MemberRepositoryCustom 인터페이스 생성

package study.querydsl.repository;

public interface MemberRepositoryCustom {
    List<MemberTeamDto> search(MemberSearchCondition condition);
}

2. 사용자 정의 인터페이스 구현

repository 패키지 - MemberRepositoryCustomImpl 클래스 생성

package study.querydsl.repository;

public class MemberRepositoryCustomImpl implements MemberRepositoryCustom{

    private final JPAQueryFactory queryFactory;

    public MemberRepositoryCustomImpl(EntityManager em) {
        this.queryFactory = new JPAQueryFactory(em);
    }

    @Override
    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 StringUtils.hasText(username) ? member.username.eq(username) : null;
    }
    private BooleanExpression teamNameEq(String teamName) {
        return StringUtils.hasText(teamName) ? team.name.eq(teamName) : null;
    }
    private BooleanExpression ageGoe(Integer ageGoe) {
        return ageGoe != null ? member.age.goe(ageGoe) : null;
    }
    private BooleanExpression ageLoe(Integer ageLoe) {
        return ageLoe != null ? member.age.loe(ageLoe) : null;
    }
}

3. 스프링 데이터 리포지토리에 사용자 정의 인터페이스 상속

repository 패키지 - MeberRepository 인터페이스

package study.querydsl.repository;

public interface MemberRepository extends JpaRepository<Member, Long>, MemberRepositoryCustom {
    List<Member> findByUsername(String username);
}

test 작성

@Test
public void searchTest() {
    Team teamA = new Team("teamA");
    Team teamB = new Team("teamB");
    em.persist(teamA);
    em.persist(teamB);
    Member member1 = new Member("member1", 10, teamA);
    Member member2 = new Member("member2", 20, teamA);
    Member member3 = new Member("member3", 30, teamB);
    Member member4 = new Member("member4", 40, teamB);
    em.persist(member1);
    em.persist(member2);
    em.persist(member3);
    em.persist(member4);

    MemberSearchCondition condition = new MemberSearchCondition();
    condition.setAgeGoe(35);
    condition.setAgeLoe(40);
    condition.setTeamName("teamB");

    List<MemberTeamDto> result = memberRepository.search(condition);

    assertThat(result).extracting("username").containsExactly("member4");
}

*만약 조회 쿼리가 너무 복잡하거나 그러면 그냥 따로 repository를 생성해서 사용해도 된다.

너무 사용자 정의 리포지토리(custom)에 억압될 필요는 없다.*

스프링 데이터 페이징 활용 1 - Querydsl 페이징 연동

repository 패키지 - MemberRepositoryCustom 인터페이스

- searchPage 메서드 추가

package study.querydsl.repository;

public interface MemberRepositoryCustom {
    List<MemberTeamDto> search(MemberSearchCondition condition);

    Page<MemberTeamDto> searchPage(MemberSearchCondition condition, Pageable pageable);
}

repository 패키지 - MeberRepositoryCustomImpl 클래스

- content 쿼리와 count 쿼리 분리

- PageImpl<페이지 데이터, 페이지 요청정보, 전체 개수>() = Page객체를 만들어서 반환

package study.querydsl.repository;

public class MemberRepositoryCustomImpl implements MemberRepositoryCustom{

    private final JPAQueryFactory queryFactory;

    public MemberRepositoryCustomImpl(EntityManager em) {
        this.queryFactory = new JPAQueryFactory(em);
    }

    @Override
    public Page<MemberTeamDto> searchPage(MemberSearchCondition condition, Pageable pageable) {
        List<MemberTeamDto> content = 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())
                )
                .offset(pageable.getOffset())
                .limit(pageable.getPageSize())
                .fetch();

        long total = queryFactory
                .select(member.count())
                .from(member)
                .leftJoin(member.team, team)
                .where(
                        usernameEq(condition.getUsername()),
                        teamNameEq(condition.getTeamName()),
                        ageGoe(condition.getAgeGoe()),
                        ageLoe(condition.getAgeLoe())
                )
                .fetchOne();

        return new PageImpl<>(content, pageable, total);
    }
}

*content 쿼리와 count 쿼리를 분리할 경우, 추후에 최적화 하기가 편하다.*

스프링 데이터 페이징 활용 2 - CountQuery 최적화

count 쿼리가 생략 가능한 경우 생략해서 처리

1. 페이지 시작이면서 컨텐츠 사이즈가 페이지 사이즈보다 작을 때

2. 마지막 페이지 일 때

repository 패키지 - MemberRepositoryCustomImpl 클래스

- count 쿼리의 .fetchOne부분을 지우고, JPAQuery<> 형태(countQuery)로 반환

- PageableExecutionUtils.getPage(페이지 데이터, 페이지 요청정보, 전체 개수를 구하는 함수(지연 실행)) = count쿼리 최적화

package study.querydsl.repository;

public class MemberRepositoryCustomImpl implements MemberRepositoryCustom{

    private final JPAQueryFactory queryFactory;

    public MemberRepositoryCustomImpl(EntityManager em) {
        this.queryFactory = new JPAQueryFactory(em);
    }

    @Override
    public Page<MemberTeamDto> searchPage(MemberSearchCondition condition, Pageable pageable) {
        List<MemberTeamDto> content = 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())
                )
                .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());
    }
}

스프링 데이터 페이징 활용 3 - 컨트롤러 개발

controller 패키지 - MemberController 클래스

package study.querydsl.controller;

@RestController
@RequiredArgsConstructor
public class MemberController {

    private final MemberJpaRepository memberJpaRepository;
    private final MemberRepository memberRepository;

    @GetMapping("/v1/members")
    public List<MemberTeamDto> searchMemberV1(MemberSearchCondition condition){
        return memberJpaRepository.search(condition);
    }

    @GetMapping("/v2/members")
    public Page<MemberTeamDto> searchMemberV2(MemberSearchCondition condition, Pageable pageable){
        return memberRepository.searchPage(condition, pageable);
    }
}

*정렬(Sort)은 조건이 복잡해지면, Sort사용 보다는 파라미터를 받아서 직접 처리하는 것을 권장*

순수 JPA 리포지토리와 Querydsl

package study.querydsl.repository;

import static study.querydsl.entity.QMember.*;

@Repository
public class MemberJpaRepository {

    private final EntityManager em;
    private final JPAQueryFactory queryFactory;

    public MemberJpaRepository(EntityManager em) {
        this.em = em;
        this.queryFactory = new JPAQueryFactory(em);
    }

    public void save(Member member) {
        em.persist(member);
    }

    public Optional<Member> findById(Long id){
        Member findMember = em.find(Member.class, id);
        return Optional.ofNullable(findMember);
    }

    public List<Member> findAll(){
        return em.createQuery("select m from Member m", Member.class)
                .getResultList();
    }
    public List<Member> findAll_Querydsl(){
        return queryFactory
                .selectFrom(member)
                .fetch();
    }

    public List<Member> findByUsername(String username){
        return em.createQuery("select m from Member m where m.username = :username", Member.class)
                .setParameter("username", username)
                .getResultList();
    }
    public List<Member> findByUsername_Querydsl(String username){
        return queryFactory
                .selectFrom(member)
                .where(member.username.eq(username))
                .fetch();
    }
}

package study.querydsl.repository;

@SpringBootTest
@Transactional
class MemberJpaRepositoryTest {

    @Autowired EntityManager em;
    @Autowired MemberJpaRepository memberJpaRepository;

    @Test
    public void basicTest() {
        Member member = new Member("member1", 10);
        memberJpaRepository.save(member);

        Member findMember = memberJpaRepository.findById(member.getId()).get();
        assertThat(findMember).isEqualTo(member);

        List<Member> result1 = memberJpaRepository.findAll_Querydsl();
        assertThat(result1).containsExactly(member);

        List<Member> result2 = memberJpaRepository.findByUsername_Querydsl("member1");
        assertThat(result2).containsExactly(member);
    }
}

동적 쿼리와 성능 최적화 조회 - Builder 사용

dto 패키지 - MemberTeamDto 클래스 생성

- 원하는 형태의 반환 객체 생성

- @QueryProjection 사용

package study.querydsl.dto;

@Data
public class MemberTeamDto {

    private Long memberId;
    private String username;
    private int age;
    private Long teamId;
    private String teamName;

    @QueryProjection
    public MemberTeamDto(Long memberId, String username, int age, Long teamId, String teamName) {
        this.memberId = memberId;
        this.username = username;
        this.age = age;
        this.teamId = teamId;
        this.teamName = teamName;
    }
}

repository 패키지 생성 - MemberJpaRepository 생성

- StringUtils.hasText() = 값이 있는지 확인(넘어오는 값이 null or "" 인거를 방지) 

package study.querydsl.repository;

@Repository
public class MemberJpaRepository {

    private final EntityManager em;
    private final JPAQueryFactory queryFactory;

    public MemberJpaRepository(EntityManager em) {
        this.em = em;
        this.queryFactory = new JPAQueryFactory(em);
    }

    public List<MemberTeamDto> searchByBuilder(MemberSearchCondition condition){

        BooleanBuilder builder = new BooleanBuilder();
        if (StringUtils.hasText(condition.getUsername())) {
            builder.and(member.username.eq(condition.getUsername()));
        }
        if (StringUtils.hasText(condition.getTeamName())) {
            builder.and(team.name.eq(condition.getTeamName()));
        }
        if (condition.getAgeGoe() != null) {
            builder.and(member.age.goe(condition.getAgeGoe()));
        }
        if (condition.getAgeLoe() != null) {
            builder.and(member.age.loe(condition.getAgeLoe()));
        }

        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(builder)
                .fetch();
    }
}

test 작성

@Test
public void searchTest() {
    Team teamA = new Team("teamA");
    Team teamB = new Team("teamB");
    em.persist(teamA);
    em.persist(teamB);
    Member member1 = new Member("member1", 10, teamA);
    Member member2 = new Member("member2", 20, teamA);
    Member member3 = new Member("member3", 30, teamB);
    Member member4 = new Member("member4", 40, teamB);
    em.persist(member1);
    em.persist(member2);
    em.persist(member3);
    em.persist(member4);

    MemberSearchCondition condition = new MemberSearchCondition();
    condition.setAgeGoe(35);
    condition.setAgeLoe(40);
    condition.setTeamName("teamB");

    List<MemberTeamDto> result = memberJpaRepository.searchByBuilder(condition);

    assertThat(result).extracting("username").containsExactly("member4");
}

동적 쿼리와 성능 최적화 조회 - Where절 파라미터 사용

repositsory 패키지 - MemberJpaRepository 클래스

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 StringUtils.hasText(username) ? member.username.eq(username) : null;
}
private BooleanExpression teamNameEq(String teamName) {
    return StringUtils.hasText(teamName) ? team.name.eq(teamName) : null;
}
private BooleanExpression ageGoe(Integer ageGoe) {
    return ageGoe != null ? member.age.goe(ageGoe) : null;
}
private BooleanExpression ageLoe(Integer ageLoe) {
    return ageLoe != null ? member.age.loe(ageLoe) : null;
}

test 작성

@Test
public void searchTest() {
    Team teamA = new Team("teamA");
    Team teamB = new Team("teamB");
    em.persist(teamA);
    em.persist(teamB);
    Member member1 = new Member("member1", 10, teamA);
    Member member2 = new Member("member2", 20, teamA);
    Member member3 = new Member("member3", 30, teamB);
    Member member4 = new Member("member4", 40, teamB);
    em.persist(member1);
    em.persist(member2);
    em.persist(member3);
    em.persist(member4);

    MemberSearchCondition condition = new MemberSearchCondition();
    condition.setAgeGoe(35);
    condition.setAgeLoe(40);
    condition.setTeamName("teamB");

    List<MemberTeamDto> result = memberJpaRepository.search(condition);

    assertThat(result).extracting("username").containsExactly("member4");
}

조회 API 컨트롤러 개발

main - application.yml

- profiles:, active: local 추가

spring:
  profiles:
    active: local
  datasource:
    url: jdbc:h2:tcp://localhost/~/querydsl
    username: sa
    password:
    driver-class-name: org.h2.Driver

test - resources 생성 - application.yml 복.붙

- local -> test 변경

spring:
  profiles:
    active: test
  datasource:
    url: jdbc:h2:tcp://localhost/~/querydsl
    username: sa
    password:
    driver-class-name: org.h2.Driver

초기화 클래스 생성

controller 패키지 - InitMember 클래스 생성

- @Profile로 지정

package study.querydsl.controller;

@Profile("local")
@Component
@RequiredArgsConstructor
public class InitMember {

    private final InitMemberService initMemberService;

    @PostConstruct
    public void init() {
        initMemberService.init();
    }

    @Component
    static class InitMemberService{

        @PersistenceContext private EntityManager em;

        @Transactional
        public void init() {
            Team teamA = new Team("teamA");
            Team teamB = new Team("teamB");
            em.persist(teamA);
            em.persist(teamB);
            for (int i = 0; i < 100; i++) {
                Team selectedTeam = i % 2 == 0 ? teamA : teamB;
                em.persist(new Member("member" + i, i, selectedTeam));
            }
        }
    }
}

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

프로젝션 = select에 대상 지정

프로젝션 대상이 하나

타입을 명확하게 지정할 수 있음

@Test
public void simpleProjection() {
    List<String> result = queryFactory
            .select(member.username)
            .from(member)
            .fetch();

    for (String s : result) {
        System.out.println("s = " + s);
    }
}

프로젝션 대상이 둘 이상

튜플이나 DTO로 조회

@Test
public void tupleProjection() {
    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);
    }
}

*튜플도 결국 Querydsl의 종속적인 타입이므로, repository계층 안에서만 사용하고,

밖으로 내보낼 때에는 DTO로 변환해서 반환*

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

순수 JPA에서 DTO 조회

new 명령어 사용

package이름 다 작성

생성자 방식만 지원

@Test
public void findDtoByJPQL() {
    List<MemberDto> result = em.createQuery("select new study.querydsl.dto.MemberDto(m.username, m.age) from Member m")
            .getResultList();

    for (MemberDto memberDto : result) {
        System.out.println("memberDto = " + memberDto);
    }
}

Querydsl 빈 생성

1. 프로퍼티 접근 - Setter

Dto에 Setter 및 기본생성자 포함,

Projections.bean 사용

@Test
public void findDtoBySetter() {
    List<MemberDto> result = queryFactory
            .select(Projections.bean(MemberDto.class,
                    member.username,
                    member.age))
            .from(member)
            .fetch();

    for (MemberDto memberDto : result) {
        System.out.println("memberDto = " + memberDto);
    }
}

2. 필드 직접 접근

Dto에 기본 생성자만 포함,

Projections.fields 사용

@Test
public void findDtoByField() {
    List<MemberDto> result = queryFactory
            .select(Projections.fields(MemberDto.class,
                    member.username,
                    member.age))
            .from(member)
            .fetch();

    for (MemberDto memberDto : result) {
        System.out.println("memberDto = " + memberDto);
    }
}

3. 생성자 사용

Dto만 필요,

Projections.constructor사용

@Test
public void findDtoByConstructor() {
    List<MemberDto> result = queryFactory
            .select(Projections.constructor(MemberDto.class,
                    member.username,
                    member.age))
            .from(member)
            .fetch();

    for (MemberDto memberDto : result) {
        System.out.println("memberDto = " + memberDto);
    }
}

별칭이 다를때

ExpressionUtils.as(source,alias) : 필드나, 서브 쿼리에 별칭 적용

username.as("memberName") : 필드에 별칭 적용

*필드는 .as 사용, 서브쿼리는 ExpressionUteils.as 사용*

@Test
public void findUserDto() {
    QMember memberSub = new QMember("memberSub");
    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();
}

*bean이나 properties는 필드 이름을 같게 해야한다. (별칭 사용)

하지만 contructor는 필드 이름이 달라도 타입만 같으면 된다.*

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

DTO의 생성자에 @QueryProjection을 추가

*컴파일중에 에러를 확인할 수 있다.*

package study.querydsl.dto;

@Data
@NoArgsConstructor
public class MemberDto {

    private String username;
    private int age;

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

@Test
public void findDtoQueryProjection() {
    List<MemberDto> result = queryFactory
            .select(new QMemberDto(member.username, member.age))
            .from(member)
            .fetch();

    for (MemberDto memberDto : result) {
        System.out.println("memberDto = " + memberDto);
    }
}

단점

DTO에 @QueryProjection을 추가해야 하므로, Querydsl의 의존성을 갖게 된다.(DTO도 Q파일로 생성됨)

동적 쿼리 - BooleanBuilder 사용

BooleanBuilder를 사용해서, 조건을 설정 후, where절 안에 넣는다

@Test
public void dynamicQuery_BooleanBuilder() {
    String usernameParam = "member1";
    Integer ageParam = null;

    List<Member> result = searchMember1(usernameParam, ageParam);

    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 (ageCond != null){
        builder.and(member.age.eq(ageCond));
    }

    return queryFactory
            .selectFrom(member)
            .where(builder)
            .fetch();
}

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

where조건에서 null값은 무시된다.

BooleanBuilder보다 가독성이 더 좋다.

재활용이 가능하다.

@Test
public void dynamicQuery_WhereParam() {
    String usernameParam = "member1";
    Integer ageParam = 10;

    List<Member> result = searchMember2(usernameParam, ageParam);

    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 usernameCond == null ? null : member.username.eq(usernameCond);
}
private BooleanExpression ageEq(Integer ageCond) {
    if (ageCond == null) {
        return null;
    } else {
        return member.age.eq(ageCond);
    }
}
private BooleanExpression allEq(String usernameCond, Integer ageCond){
    return usernameEq(usernameCond).and(ageEq(ageCond));
}

수정, 삭제 벌크 연산

쿼리 한번으로 대량의 데이터 처리

@Test
public void bulkUpdate() {
    long count = queryFactory
            .update(member)
            .set(member.username, "비회원")
            .where(member.age.lt(28))
            .execute();

    em.flush();
    em.clear();

    List<Member> result = queryFactory
            .selectFrom(member)
            .fetch();

    for (Member member1 : result) {
        System.out.println("member1 = " + member1);
    }
}

*벌크 연산은 영속성 컨텍스트(1차 캐시)를 거치지 않고 DB로 가기때문에, 그 후 select할 때 문제가 생긴다.

따라서 em.flush()로 DB에 반영되게 하고, em.clear()로 영속성 컨텍스트(1차 캐시)를 비워야 한다.*

쿼리 한번으로 더하기 및 삭제

@Test
public void bulkAdd() {
    long count = queryFactory
            .update(member)
            .set(member.age, member.age.add(1))
            .execute();
}

@Test
public void bulkDelete() {
    long count = queryFactory
            .delete(member)
            .where(member.age.gt(18))
            .execute();
}

SQL function 호출하기

Expressions.stringTemplate 사용

@Test
public void sqlFunction() {
    List<String> result = queryFactory
            .select(Expressions.stringTemplate(
                    "function('replace', {0}, {1}, {2})",
                    member.username, "member", "M"))
            .from(member)
            .fetch();

    for (String s : result) {
        System.out.println("s = " + s);
    }
}
@Test
public void sqlFunction2() {
    List<String> result = queryFactory.select(member.username)
            .from(member)
//                .where(member.username.eq(
//                        Expressions.stringTemplate("function('lower', {0})", member.username)))
            .where(member.username.eq(member.username.lower()))
            .fetch();

    for (String s : result) {
        System.out.println("s = " + s);
    }
}

시작 - JPQL vs Querydsl

test - QueryBasicTest 클래스 생성

- JPQL과 Querydsl의 차이점 확인

package study.querydsl;

@SpringBootTest
@Transactional
public class QuerydslBasicTest {

    @Autowired
    EntityManager em;

    JPAQueryFactory queryFactory;

    @BeforeEach
    public void before() {
        queryFactory = new JPAQueryFactory(em);
        Team teamA = new Team("teamA");
        Team teamB = new Team("teamB");
        em.persist(teamA);
        em.persist(teamB);
        Member member1 = new Member("member1", 10, teamA);
        Member member2 = new Member("member2", 20, teamA);
        Member member3 = new Member("member3", 30, teamB);
        Member member4 = new Member("member4", 40, teamB);
        em.persist(member1);
        em.persist(member2);
        em.persist(member3);
        em.persist(member4);
    }

    @Test
    public void startJPQL() {
        // member1 찾기
        Member findMember = em.createQuery("select m from Member m where m.username = :username", Member.class)
                .setParameter("username", "member1")
                .getSingleResult();

        Assertions.assertThat(findMember.getUsername()).isEqualTo("member1");
    }

    @Test
    public void startQuerydsl() {
        QMember m = new QMember("m");

        Member findMember = queryFactory
                .select(m)
                .from(m)
                .where(m.username.eq("member1"))
                .fetchOne();

        Assertions.assertThat(findMember.getUsername()).isEqualTo("member1");
    }
}

JPQL, Querydsl 차이점

JPQL = 문자(실행 시점 오류) / Querydsl = 코드(컴파일 시점 오류)

JPQL = 파라미터 바인딩 직접 / Querydsl = 파라미터 바인딩 자동 처리

기본 Q-Type 활용

기존

QMember m = new QMember("m");

Member findMember = queryFactory
        .select(m)
        .from(m)
        .where(m.username.eq("member1"))
        .fetchOne();

다음(QMember의 메서드 활용)

QMember m = QMember.member;

Member findMember = queryFactory
        .select(m)
        .from(m)
        .where(m.username.eq("member1"))
        .fetchOne();

최종(import static 활용)

Member findMember = queryFactory
        .select(member)
        .from(member)
        .where(member.username.eq("member1"))
        .fetchOne();

*실행되는 JPQL 보는 방법*

application.yml - 추가

hibernate:
    format_sql: true
    use_sql_comments: true

검색 조건 쿼리

.and와 .or를 메서드 체인으로 연결할 수 있다.

.eq("member1") : username = 'member1'

.ne("member1") : username != 'member1'

.eq("member1").not() : username != 'member1'

.isNotNull() : 이름이 is not null

.in(10, 20) : age in (10,20)

.notIn(10, 20) : age not in (10, 20)

.between(10,30) : between 10, 30

.goe(30) : age >= 30

.gt(30) : age > 30

.loe(30) : age <= 30

.lt(30) : age < 30

.like("member%") : like 검색

.contains("member") : like ‘%member%’ 검색

.startsWith("member") : like ‘member%’ 검색

*and인 경우 ,로 구분 가능*

@Test
public void search() {
    Member findMember = queryFactory
        .selectFrom(member)
        .where(member.username.eq("member1").and(member.age.eq(10)))
        .fetchOne();

}

@Test
public void searchAndParam() {
    Member findMember = queryFactory
        .selectFrom(member)
        .where(
                member.username.eq("member1"),
                member.age.eq(10)
        )
        .fetchOne();
}

결과 조회

fetch() : 리스트 조회, 데이터 없으면 빈 리스트 반환

fetchOne() : 단 건 조회 (결과가 없으면 : null, 결과가 둘 이상이면 : NonUniqueResultException)

fetchFirst() : limit(1).fetchOne()

fetchResults() : 페이징 정보 포함, total count 쿼리 추가 실행

fetchCount() : count 쿼리로 변경해서 count 수 조회

@Test
public void resultFetch() {
    List<Member> fetch = queryFactory
            .selectFrom(member)
            .fetch();

    Member fetchOne = queryFactory
            .selectFrom(member)
            .fetchOne();

    Member fetchFirst = queryFactory
            .selectFrom(member)
            .fetchFirst();

    QueryResults<Member> results = queryFactory
            .selectFrom(member)
            .fetchResults();

    long count = queryFactory
            .selectFrom(member)
            .fetchCount();
}

*fetchResults(), fetchCount() 앞으로 없어질 예정이므로 사용X*

정렬

.desc(), .asc()로 정렬 가능

.desc() = 내림차순

.asc() = 오름차순

nullsLast(), nullsFirst() 로 null 데이터 순서 부여

nullsLast() = null 값을 가장 앞쪽에 배치

nullsFirst() = null 값을 가장 뒷쪽에 배치

@Test
public void sort() {
    em.persist(new Member(null, 100));
    em.persist(new Member("member5", 100));
    em.persist(new Member("member6", 100));

    List<Member> result = queryFactory
            .selectFrom(member)
            .where(member.age.eq(100))
            .orderBy(member.age.desc(), member.username.asc().nullsLast())
            .fetch();

    Member member5 = result.get(0);
    Member member6 = result.get(1);
    Member memberNull = result.get(2);

    assertThat(member5.getUsername()).isEqualTo("member5");
    assertThat(member6.getUsername()).isEqualTo("member6");
    assertThat(memberNull.getUsername()).isNull();
}

페이징

실무에서는 페이징 쿼리와 카운트 쿼리는 별도로 분리하는게 좋다

@Test
public void paging() {
    List<Member> result = queryFactory
            .selectFrom(member)
            .orderBy(member.username.desc())
            .offset(1)
            .limit(2)
            .fetch();

    assertThat(result.size()).isEqualTo(2);
}

집합

@Test
public void aggregation() {
    List<Tuple> result = queryFactory
            .select(
                    member.count(),
                    member.age.sum(),
                    member.age.avg(),
                    member.age.max(),
                    member.age.min()
            )
            .from(member)
            .fetch();

    Tuple tuple = result.get(0);
    assertThat(tuple.get(member.count())).isEqualTo(4);
    assertThat(tuple.get(member.age.sum())).isEqualTo(100);
    assertThat(tuple.get(member.age.avg())).isEqualTo(25);
    assertThat(tuple.get(member.age.max())).isEqualTo(40);
    assertThat(tuple.get(member.age.min())).isEqualTo(10);
}

@Test
public void group() {
    List<Tuple> 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);
}

조인 - 기본 조인

join(조인 대상, QType) = 조인 대상 지정, 별칭으로 사용할 QType 지정

@Test
public void join() {
    List<Member> result = queryFactory
        .selectFrom(member)
        .join(member.team, team)
        .where(team.name.eq("teamA"))
        .fetch();
}

세타조인 = 연관관계가 없는 필드로 조인

@Test
public void theta_join() {
    em.persist(new Member("teamA"));
    em.persist(new Member("teamB"));
    List<Member> result = queryFactory
            .select(member)
            .from(member, team)
            .where(member.username.eq(team.name))
            .fetch();
    assertThat(result)
            .extracting("username")
            .containsExactly("teamA", "teamB");
}

*on절을 사용하면 외부 조인 가능*

조인 - on절

조인 대상 필터링

on 절을 활용해 조인 대상을 필터링 할 때, 외부조인이 아니라 내부조인(inner join)을 사용하면,

where 절 에서 필터링 하는 것과 기능이 동일하다.

따라서 on 절을 활용한 조인 대상 필터링을 사용할 때, 내부조인 이면 익숙한 where 절로 해결하고,

외부조인이 필요한 경우에만 이 기능을 사용하자.

// JPQL: SELECT m, t FROM Member m LEFT JOIN m.team t on t.name = 'teamA'
@Test
public void join_on_filtering() {
    List<Tuple> result = queryFactory
            .select(member, team)
            .from(member)
            .leftJoin(member.team, team).on(team.name.eq("teamA"))
            .fetch();

    for (Tuple tuple : result) {
        System.out.println("tuple = " + tuple);
    }
}

연관관계가 없는 엔티티 외부 조인

// JPQL: SELECT m, t FROM Member m LEFT JOIN Team t on m.username = t.name
@Test
public void join_on_no_relation() {
    em.persist(new Member("teamA"));
    em.persist(new Member("teamB"));
    List<Tuple> result = queryFactory
            .select(member, team)
            .from(member)
            .leftJoin(team).on(member.username.eq(team.name))
            .fetch();

    for (Tuple tuple : result) {
        System.out.println("tuple = " + tuple);
    }
}

일반조인 : leftJoin(member.team, team)

on조인 :  from(member).leftJoin(team).on(xxx)

조인 - 페치 조인

페치 조인 = 연관된 엔티티를 SQL 한번에 조회하는 기능이다.

그냥 조인할 경우 Team객체는 Lazy이므로 Member만 조회했을때, Team을 조회하지는 않는다.

페치 조인 적용

.fetchJoin()만 추가하면 된다.

@PersistenceUnit EntityManagerFactory emf;

@Test
public void fetchJoinUse() {
    em.flush();
    em.clear();

    Member findMember = queryFactory
            .selectFrom(member)
            .join(member.team, team).fetchJoin()
            .where(member.username.eq("member1"))
            .fetchOne();

    boolean loaded = emf.getPersistenceUnitUtil().isLoaded(findMember.getTeam());
    assertThat(loaded).as("페치 조인 미적용").isTrue();
}

서브 쿼리

JPAExpressions 사용

@Test
public void subQuery() {

    QMember memberSub = new QMember("memberSub");

    List<Member> result = queryFactory
            .selectFrom(member)
            .where(member.age.eq(
                    JPAExpressions
                            .select(memberSub.age.max())
                            .from(memberSub)
            ))
            .fetch();

    assertThat(result).extracting("age")
            .containsExactly(40);
}
@Test
public void subQueryIn() {
    QMember memberSub = new QMember("memberSub");
    List<Member> result = queryFactory
            .selectFrom(member)
            .where(member.age.in(
                    JPAExpressions
                            .select(memberSub.age)
                            .from(memberSub)
                            .where(memberSub.age.gt(10))
            ))
            .fetch();
    assertThat(result).extracting("age")
            .containsExactly(20, 30, 40);
}

JPAExpressions를 static import 활용

import com.querydsl.jpa.JPAExpressions;

@Test
public void selectSubquery() {
    QMember memberSub = new QMember("memberSub");

    List<Tuple> result = queryFactory
            .select(member.username,
                    select(memberSub.age.avg())
                            .from(memberSub)
            )
            .from(member)
            .fetch();

    for (Tuple tuple : result) {
        System.out.println("tuple = " + tuple);
    }
}

한계점

from절의 서브쿼리(인라인 뷰)는 지원하지 않는다.

해결방안

1. 서브쿼리를 join으로 변경

2. 쿼리를 2번 분리해서 실행

3. nativeSQL 사용

Case 문

Case문 = select, 조건절(where), order by에서 사용 가능

@Test
public void basicCase() {
    List<String> result = queryFactory
            .select(member.age
                    .when(10).then("열살")
                    .when(20).then("스무살")
                    .otherwise("기타")
            )
            .from(member)
            .fetch();

    for (String s : result) {
        System.out.println("s = " + s);
    }
}
@Test
public void complexCase() {
    List<String> 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();

    for (String s : result) {
        System.out.println("s = " + s);
    }
}

상수, 문자 더하기

상수

Expressions.constant 사용

@Test
public void constant() {
    List<Tuple> result = queryFactory
            .select(member.username, Expressions.constant("A"))
            .from(member)
            .fetch();

    for (Tuple tuple : result) {
        System.out.println("tuple = " + tuple);
    }
}

문자 더하기

 .stringValue() = string 형태로 변환 (age는 int이므로 더하려면 같은 타입이여야 한다.)

@Test
public void concat() {
    List<String> result = queryFactory
            .select(member.username.concat("_").concat(member.age.stringValue()))
            .from(member)
            .where(member.username.eq("member1"))
            .fetch();

    for (String s : result) {
        System.out.println("s = " + s);
    }
}

예제 도메인 모델과 동작확인

entity 패키지 - Member, Team 클래스 생성

package study.querydsl.entity;

@Entity
@Getter @Setter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@ToString(of = {"id", "username", "age"})
public class Member {
    @Id
    @GeneratedValue
    @Column(name = "member_id")
    private Long id;
    private String username;
    private int age;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "team_id")
    private Team team;

    public Member(String username) {
        this(username, 0);
    }

    public Member(String username, int age) {
        this(username, age, null);
    }

    public Member(String username, int age, Team team) {
        this.username = username;
        this.age = age;
        if (team != null) {
            changeTeam(team);
        }
    }

    public void changeTeam(Team team) {
        this.team = team;
        team.getMembers().add(this);
    }
}

package study.querydsl.entity;

@Entity
@Getter @Setter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@ToString(of = {"id", "name"})
public class Team {
    @Id @GeneratedValue
    @Column(name = "team_id")
    private Long id;
    private String name;
    @OneToMany(mappedBy = "team")
    private List<Member> members = new ArrayList<>();
    public Team(String name) {
        this.name = name;
    }
}

프로젝트 생성

https://start.spring.io/ - Group(study) 작성 - Artifact(querydsl) 작성

- Dependencies(Spring web, Spring Data JPA, H2 Datbase, Lombok) 기입 - GENERATE

settings - gradle 입력 - 둘다 intellij로 변경

setting - annotation process 입력 - Enable annotation precessing 켜기 - 재시작

controller 패키지 생성 - HelloController 클래스 생성

package study.querydsl.controller;

@RestController
public class HelloController {

    @GetMapping("/hello")
    public String hello(){
        return "hello!";
    }
}

Querydsl 설정과 검증

build.gradle - 추가(QType 생긴지 확인)

//test 롬복 사용
testCompileOnly 'org.projectlombok:lombok'
testAnnotationProcessor 'org.projectlombok:lombok'
    
//Querydsl 추가
implementation 'com.querydsl:querydsl-jpa:5.0.0:jakarta'
annotationProcessor "com.querydsl:querydsl-apt:${dependencyManagement.importedProperties['querydsl.version']}:jakarta"
annotationProcessor "jakarta.annotation:jakarta.annotation-api"
annotationProcessor "jakarta.persistence:jakarta.persistence-api"

.gitignore파일 - 맨 아래에 추가(해당 부분을 git에 등록 못하도록)

# QueryDSL QType generated sources
/src/main/generated/

test - QuerydslApplicationTests 클래스

- 먼저 EntityManager을 통해 JPAQueryFactory 생성

- Querydsl을 사용할때에는 QType 사용

package study.querydsl;

@SpringBootTest
@Transactional
class QuerydslApplicationTests {

	@Autowired EntityManager em;

	@Test
	void contextLoads() {
		Hello hello = new Hello();
		em.persist(hello);

		JPAQueryFactory query = new JPAQueryFactory(em);
		QHello qHello = new QHello("h");

		Hello result = query
				.selectFrom(qHello)
				.fetchOne();

		Assertions.assertThat(result).isEqualTo(hello);
		Assertions.assertThat(result.getId()).isEqualTo(hello.getId());
	}

}

라이브러리 살펴보기

Querydsl 라이브러리

querydsl-apt = Querydsl 관련 코드 생성 기능 제공

querydsl-jpa = querydsl 라이브러리

H2 데이터베이스 설치

https://www.h2database.com - 다운로드

스프링 부트 2.x를 사용하면 1.4.200 버전 을 다운로드 받으면 된다.

스프링 부트 3.x를 사용하면 2.1.214 버전 이상 사용해야 한다.

h2 - jdbc:h2:~/querydsl(최초 한번) 연결

h2 - jdbc:h2:tcp://localhost/~/querydsl 연결

스프링 부트 설정 - JPA, DB

application.properties 삭제 후 application.yml 생성 및 작성

spring:
  datasource:
    url: jdbc:h2:tcp://localhost/~/querydsl
    username: sa
    password:
    driver-class-name: org.h2.Driver
  jpa:
    hibernate:
      ddl-auto: create
    properties:
      hibernate:
#        show_sql: true
        format_sql: true
logging.level:
  org.hibernate.SQL: debug
#  org.hibernate.type: trace

쿼리 파라미터 로그 남기기

build.gradle - 추가

// 쿼리 파라미터 로그
implementation 'com.github.gavlyukovskiy:p6spy-spring-boot-starter:1.9.0'

Specifications (명세)

실무에서는 JPA Criteria를 거의 안쓴다! 대신에 QueryDSL을 사용하자.

Query By Example

실무에서는 QueryDSL을 사용하자.

Projections

조금만 복잡해지면 QueryDSL을 사용하자.

네이티브 쿼리

가급적 네이티브 쿼리는 사용하지 않는게 좋음, 정말 어쩔 수 없을 때 사용(동적 쿼리 불가능)