실전! QueryDsl 1,2 (설정 ,문법)

 

server:
  port : 8083

#spring:
#  datasource:
#    url: jdbc:h2:tcp://localhost/D:\H2\jpashop;
#    username: sa
#    password:
#    driver-class-name: org.h2.Driver
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/spring_data_jpa?serverTimezone=UTC&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true
    username: tony
    password: 1234
    driver-class-name: com.mysql.cj.jdbc.Driver


  jpa:
    hibernate:
      ddl-auto: create
    properties:
      hibernate:
        show_sql: true
        format_sql: true
    database: mysql
    databse-platform: org.hibernate.dialect.MySQL5InnoDBDialect

mysql + jpa + querydsl 를 위한 appilcation.yml

plugins {
	id 'org.springframework.boot' version '2.4.4'
	id 'io.spring.dependency-management' version '1.0.11.RELEASE'
	//querydsl 추가
	id "com.ewerk.gradle.plugins.querydsl" version "1.0.10"
	id 'java'
}

group = 'study'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '11'

configurations {
	compileOnly {
		extendsFrom annotationProcessor
	}
}

repositories {
	mavenCentral()
}

dependencies {
	implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
	implementation 'org.springframework.boot:spring-boot-starter-web'
	//querydsl 추가
	implementation 'com.querydsl:querydsl-jpa'

	compileOnly 'org.projectlombok:lombok'
	runtimeOnly 'mysql:mysql-connector-java'
	annotationProcessor 'org.projectlombok:lombok'
	testImplementation 'org.springframework.boot:spring-boot-starter-test'
}

test {
	useJUnitPlatform()
}

//querydsl 추가 시작
def querydslDir = "$buildDir/generated/querydsl"

querydsl {
	jpa = true
	querydslSourcesDir = querydslDir
}
sourceSets {
	main.java.srcDir querydslDir
}
configurations {
	querydsl.extendsFrom compileClasspath

}
compileQuerydsl {
	options.annotationProcessorPath = configurations.querydsl
}

gradle.build 의 설정이다.

gradle 에서 other에서 compileQuerydsl 를 눌려주면

build/generated/ 에 소스코드가 생성되었으면 정상적으로 잘 동작한 것이다.

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

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

	}

간단한 예시다.

 

    @Test
    public void startJPQL() throws Exception
    {
        //given
        String qlString =
                "select m from Member m " +
                "where m.username = :username";
        Member findMember = em.createQuery(qlString, Member.class)
                .setParameter("username", "member1")
                .getSingleResult();
        //when
        //then
        assertThat(findMember.getUsername()).isEqualTo("member1");
    }

    @Test
    public void startQuerydsl() throws Exception
    {
        //given
        JPAQueryFactory queryFactory = new JPAQueryFactory(em);
        QMember m = new QMember("m");
        //when

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

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

위에는 jpql 아래는 Querydsl이다.

jpql 쿼리는 String으로 되어있지만 Querydsl 은 컴파일 시점에 쿼리의 오류를 잡아준다.

파미터 바인딩도 똑같이된다. Querydsl 도 SQL injection 을 당하지 않게 설계 되어있다.

JPAQueryFactory의 경우는 필드로 빼도 동시성 문제 없이 잘 작동한다.

 

    public static final QMember member = new QMember("member1");

    @Test
    public void startQuerydsl() throws Exception
    {
        Member findMember = queryFactory
                .select(member)
                .from(member)
                .where(member.username.eq("member1"))
                .fetchOne();
        assertThat(findMember.getUsername()).isEqualTo("member1");
    }

    /* select
        member1 
    from
        Member member1 
    where
        member1.username = ?1 */ select
            member0_.member_id as member_i1_1_,
            member0_.age as age2_1_,
            member0_.team_id as team_id4_1_,
            member0_.username as username3_1_ 
        from
            member member0_ 
        where
            member0_.username=?

QMember.member 를 static import 를 통해서 간결하게 할 수 있고

쿼리를 확인해보면 alias가 "member1" 으로 잡혀있는데 그게 기본으로 QueryDsl에서 넣어준 변수명이다.

 

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

        //then
       assertThat(findMember.getUsername()).isEqualTo("member1");
    }

and ,or , like , in 등등 모든것이 존재한다. where 안에는 and를 명시적으로 선언안하고 comma로 구분해도 and() 로 인식한다.

 

조회

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

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

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

        QueryResults<Member> results = queryFactory
                .selectFrom(member)
                .fetchResults();
        long total = results.getTotal();
        List<Member> content = results.getResults();
        long limit = results.getLimit();
        long offset = results.getOffset();
    }

fetchOne의 경우 null 이거 나 1개 초과면 Exception을 발생시킨다. 

fetchResults의 경우에는 페이징정보 와 totalcount 쿼리를 추가 실행한다. 페이징 너무 복잡할 경우 쓰면 안되고 따로 countQuery를 만들어서 2번날려주는것이 좋다.

 

Sort

    /**
     * 1. 회원 나이 내림차순(desc)
     * 2. 회원 이름 올림차순(asc)
     * 단 2에서 회원 이름이 없으면 마치막에 출력 (nulls last)
     */
    @Test
    public void sort() throws Exception
    {
        em.persist(new Member(null, 100));
        em.persist(new Member("member5", 100));
        em.persist(new Member("member6", 100));
        //given
        List<Member> result = queryFactory
                .selectFrom(member)
                .where(member.age.eq(100))
                .orderBy(member.age.desc(), member.username.asc().nullsLast())
                .fetch();
        //when
        
        //then  
    }

nullsFirst() 도 존재한다.

 

페이징

    @Test
    public void paging1() throws Exception
    {
        //given
        List<Member> result = queryFactory
                .selectFrom(member)
                .orderBy(member.username.desc())
                .offset(1)
                .limit(2)
                .fetch();
        assertThat(result.size()).isEqualTo(2);
    }
    
    @Test
    public void paging2() throws Exception
    {
        QueryResults<Member> result = queryFactory
                .selectFrom(member)
                .orderBy(member.username.desc())
                .offset(1)
                .limit(2)
                .fetchResults();

        assertThat(result.getTotal()).isEqualTo(4);
        assertThat(result.getLimit()).isEqualTo(2);
        assertThat(result.getOffset()).isEqualTo(1);
        assertThat(result.getResults().size()).isEqualTo(2);
    }

 

집합

    @Test
    public void group() throws Exception
    {
        List<Tuple> result = queryFactory
                .select(
                        team.name,
                        member.age.avg()
                )
                .from(member.team, team)
                .groupBy(team.name)
                .having(member.age.avg().gt(3))
                .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);
    }

 

기본조인

    /**
     * 팀 A에 소속된 회원
     */
    @Test
    public void join() throws Exception
    {
        List<Member> result = queryFactory
                .selectFrom(member)
                .join(member.team, team)
                .where(team.name.eq("teamA"))
                .fetch();

        assertThat(result)
                .extracting("username")
                .containsExactly("member1","member2");
    }

    /**
     * 세타 조인
     * 회원의 이름이 팀 이름과 같은 회원 조회
     */
    @Test
    public void theta_join() throws Exception
    {
        //given
        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");

    }

좀더 많은 엔티티랑 계속해서 조인하고 싶으면 join().join() 처럼 이어서 써주면 된다.

 

join on

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

tuple = [Member(id=3, username=member1, age=10), Team(id=1, name=teamA)]
tuple = [Member(id=4, username=member2, age=20), Team(id=1, name=teamA)]
tuple = [Member(id=5, username=member3, age=30), null]
tuple = [Member(id=6, username=member4, age=40), null]

left join이기에 member들은 다 join하고 team은 teamA만 join한다.

join대상을 on절에서 걸러준다.

    /* select
        member1,
        team 
    from
        Member member1   
    left join
        member1.team as team with team.name = ?1 */ select
            member0_.member_id as member_i1_1_0_,
            team1_.id as id1_2_1_,
            member0_.age as age2_1_0_,
            member0_.team_id as team_id4_1_0_,
            member0_.username as username3_1_0_,
            team1_.name as name2_2_1_ 
        from
            member member0_ 
        left outer join
            team team1_ 
                on member0_.team_id=team1_.id 
                and (
                    team1_.name=?
                )

on절을 활용해 조인대상을 필터링 할 때, 외부조인이 아니라 내부조인(inner join )을 사용하면, where 절에서 필터링 하는 것과 기능이 동일하다. 

외부조인이 아니라면 앵간하면 where절을 쓰자

 

        List<Tuple> result = queryFactory
                .select(member, team)
                .from(member)
                .leftJoin(team).on(member.username.eq(team.name))
                .fetch();

    /* select
        member1,
        team 
    from
        Member member1   
    left join
        Team team with member1.username = team.name */ select
            member0_.member_id as member_i1_1_0_,
            team1_.id as id1_2_1_,
            member0_.age as age2_1_0_,
            member0_.team_id as team_id4_1_0_,
            member0_.username as username3_1_0_,
            team1_.name as name2_2_1_ 
        from
            member member0_ 
        left outer join
            team team1_ 
                on (
                    member0_.username=team1_.name
                )

tuple = [Member(id=3, username=member1, age=10), null]
tuple = [Member(id=4, username=member2, age=20), null]
tuple = [Member(id=5, username=member3, age=30), null]
tuple = [Member(id=6, username=member4, age=40), null]
tuple = [Member(id=7, username=teamA, age=0), Team(id=1, name=teamA)]
tuple = [Member(id=8, username=teamB, age=0), Team(id=2, name=teamB)]
tuple = [Member(id=9, username=teamC, age=0), null]

left outer join 임으로 member들은 다조회하되 on 절에 team name 하고 member name이 같은 team 만 조회하라했으니 team쪽은 저렇게 2개 뿐이다.

 

        List<Tuple> result = queryFactory
                .select(member, team)
                .from(member)
                .leftJoin(member.team,team).on(member.username.eq(team.name))
                .fetch();

    /* select
        member1,
        team 
    from
        Member member1   
    left join
        member1.team as team with member1.username = team.name */ select
            member0_.member_id as member_i1_1_0_,
            team1_.id as id1_2_1_,
            member0_.age as age2_1_0_,
            member0_.team_id as team_id4_1_0_,
            member0_.username as username3_1_0_,
            team1_.name as name2_2_1_ 
        from
            member member0_ 
        left outer join
            team team1_ 
                on member0_.team_id=team1_.id 
                and (
                    member0_.username=team1_.name
                )

tuple = [Member(id=3, username=member1, age=10), null]
tuple = [Member(id=4, username=member2, age=20), null]
tuple = [Member(id=5, username=member3, age=30), null]
tuple = [Member(id=6, username=member4, age=40), null]
tuple = [Member(id=7, username=teamA, age=0), null]
tuple = [Member(id=8, username=teamB, age=0), null]
tuple = [Member(id=9, username=teamC, age=0), null]

leftJoin(member.team, team) 을 해주면 FK를 이용해 이미 조인 한것이다. on절이 없었다면 아래와 같이 뜬다.

tuple = [Member(id=3, username=member1, age=10), Team(id=1, name=teamA)]
tuple = [Member(id=4, username=member2, age=20), Team(id=1, name=teamA)]
tuple = [Member(id=5, username=member3, age=30), Team(id=2, name=teamB)]
tuple = [Member(id=6, username=member4, age=40), Team(id=2, name=teamB)]
tuple = [Member(id=7, username=teamA, age=0), null]
tuple = [Member(id=8, username=teamB, age=0), null]
tuple = [Member(id=9, username=teamC, age=0), null]

하지만 위의 쿼리는 and절 때문에 Team 이 하나도 조인 못한것이다.

 

Fetch Join

    @PersistenceUnit
    EntityManagerFactory emf;
    @Test
    public void fetchJoinNo() throws Exception
    {
        em.flush();
        em.clear();

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

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

    /* select
        member1 
    from
        Member member1 
    where
        member1.username = ?1 */ select
            member0_.member_id as member_i1_1_,
            member0_.age as age2_1_,
            member0_.team_id as team_id4_1_,
            member0_.username as username3_1_ 
        from
            member member0_ 
        where
            member0_.username=?

단순히 이름이 member1 인 애들만 끌고온다

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

    /* select
        member1 
    from
        Member member1   
    inner join
        fetch member1.team as team 
    where
        member1.username = ?1 */ select
            member0_.member_id as member_i1_1_0_,
            team1_.id as id1_2_1_,
            member0_.age as age2_1_0_,
            member0_.team_id as team_id4_1_0_,
            member0_.username as username3_1_0_,
            team1_.name as name2_2_1_ 
        from
            member member0_ 
        inner join
            team team1_ 
                on member0_.team_id=team1_.id 
        where
            member0_.username=?

fetch join을 통해서 연관된 엔티티 까지 싸악 끌고온다.

 

서브쿼리

    /**
     * 나이가 가장 많은 회원 조회
     */
    @Test
    public void subQuery() throws Exception
    {
        QMember memberSub = new QMember("membersub");
        List<Member> result = queryFactory
                .selectFrom(member)
                .where(member.age.eq(
                        JPAExpressions
                                .select(memberSub.age.max())
                                .from(memberSub)
                ))
                .fetch();
    }

    /**
     * 나이가 평균 이상인 회원
     */
    @Test
    public void subQueryGoe() throws Exception
    {
        QMember memberSub = new QMember("membersub");
        List<Member> result = queryFactory
                .selectFrom(member)
                .where(member.age.goe(
                        JPAExpressions
                                .select(memberSub.age.avg())
                                .from(memberSub)
                ))
                .fetch();
    }

    /**
     * 나이가 평균 이상인 회원
     */
    @Test
    public void subQueryIn() throws Exception
    {
        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();
    }

    @Test
    public void selectSubQuery() throws Exception
    {
        QMember memberSub = new QMember("membersub");
        List<Tuple> result = queryFactory
                .select(member.username,
                        JPAExpressions
                                .select(memberSub.age.avg())
                                .from(memberSub))
                .from(member)
                .fetch();
        for (Tuple tuple : result)
        {
            System.out.println("tuple = " + tuple);
        }
    }

JPAExpressions 는 static import 를 해줄수 있다.

 

CASE 문

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

    /* select
        case 
            when (member1.age between ?1 and ?2) then ?3 
            when (member1.age between ?4 and ?5) then ?6 
            else '기타' 
        end 
    from
        Member member1 */ select
            case 
                when member0_.age between ? and ? then ? 
                when member0_.age between ? and ? then ? 
                else '기타' 
            end as col_0_0_ 
        from
            member member0_

complexCase의 쿼리이다. 

 

Constant, Concat

    @Test
    public void constant() throws Exception
    {
        List<Tuple> result = queryFactory
                .select(member.username, Expressions.constant("A"))
                .from(member)
                .fetch();
        for (Tuple tuple : result)
        {
            System.out.println("tuple = " + tuple);
        }
    }

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

stringValue() 문자가 아닌 다른타입들을 문자로. ENUM 을 처리할떄 자주 사용한다.