Querydsl

Querydsl 설정

• 개발환경

  Java 17
  SpringBoot 3.1.2
  Querydsl 5.0
  Intellij

• build.gradle 설정

dependencies {
	===기타 설정===
	//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"
}


//clean시 Q파일 디렉토리 삭제
clean {
	delete file('src/main/generated')
}

• 빌드 (Intellij)
  

1. 클린 : gradle Task > build > clean
2. 빌드 : gradle Task > other > compileJava

• Q파일 생성확인
  build > generated > 디렉토리 확인

기본문법

• JPQL vs Querydsl

public class QuerydslTest{
	@Autowired
    EntityManager em;
    
    //JPQL
    public void Jpql(){
    	Member findMember = 
        	em.createQuery("select m from Member m where m.username = :username")
            	.setParameter("username","ParamMember")
                .getSingleResult();
    }
    
    //Querydsl
    public void Querydsl(){
    	JPAQueryFactory queryFactory = new JPAQueryFactory(em);
        QMember m = new QMember("m");
        
        Member findMember = queryFactory
        	.select(m)
            .from(m)
            .where(m.username.eq("ParamMember"))
            .fetchOne();
    }
}

EntityManager로 JPAQueryFactory 생성

• 기본 Q-Type

1. QMember qMember = new QMember("m"); //별칭 직접 사용
2. QMember qMember = QMember.member; //기본 인스턴스 사용,static import와 함께사용

import static 패키지명.QMember.*;

public class QuerydslTest{
	@Autowired
    EntityManager em;
    //queryFactory 필드로
    JPAQueryFactory queryFactory;
    
	public void Querydsl(){
    	queryFactory = new JPAQueryFactory(em);
        //QMember.member 에서 QMember를 static import 처리
        Member findMember = queryFactory
        	.select(member)
            .from(member)
            //.selectFrom(member)  //select와 from을 합쳐서 사용가능
            .where(member.username.eq.("ParamMember"))
            .fetchOne();
	}
}

• 검색 조건 쿼리
  JPQL이 제공하는 모든 검색 조건 제공

member.username.eq("member1") // username = 'member1'
member.username.ne("member1") //username != 'member1'
member.username.eq("member1").not() // username != 'member1'
member.username.isNotNull() //이름이 is not null

member.age.in(10, 20) // age in (10,20)
member.age.notIn(10, 20) // age not in (10, 20)
member.age.between(10,30) //between 10, 30

member.age.goe(30) // age >= 30
member.age.gt(30) // age > 30
member.age.loe(30) // age <= 30
member.age.lt(30) // age < 30

member.username.like("member%") //like 검색
member.username.contains("member") // like ‘%member%’ 검색
member.username.startsWith("member") //like ‘member%’ 검색

• 결과 조회

//List
List<Member> fetch = queryFactory
 .selectFrom(member)
 .fetch();
 
//단 건
Member findMember1 = queryFactory
 .selectFrom(member)
 .fetchOne();
 
//처음 한 건 조회
Member findMember2 = queryFactory
 .selectFrom(member)
 .fetchFirst();
 
//페이징에서 사용 deprecated
QueryResults<Member> results = queryFactory
 .selectFrom(member)
 .fetchResults();
 
//count 쿼리로 변경 deprecated
long count = queryFactory
 .selectFrom(member)
 .fetchCount();
 
 //fetchResults()와 fetchCount()가 deprecated 되어 
 카운트쿼리가 필요시 fetch()로 별도의 쿼리로 구하기

• 정렬

/ * 회원 정렬 순서
 * 1. 회원 나이 내림차순(desc)
 * 2. 회원 이름 올림차순(asc)
 * 단 2에서 회원 이름이 없으면 마지막에 출력(nulls last)
*/
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();

}
     //desc(),asc() : 일반 정렬
     //nullsLast(), nullsFirst() : null 데이터 순서 부여

• 페이징 (조회건수 제한)

public void paging1() {
	 List<Member> result = queryFactory
		.selectFrom(member)
		.orderBy(member.username.desc())
		.offset(1) //0부터 시작(zero index)
 	 	.limit(2) //최대 2건 조회
	  	.fetch();
 }

• 집합 함수

public void aggregation() throws Exception {
	 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);
// tuple.get(member.count.())  튜플 결과반환

• GroupBy 사용

public void group() throws Exception {
	 List<Tuple> result = queryFactory
		 .select(team.name, member.age.avg())
		 .from(member)
		 .join(member.team, team)
		 .groupBy(team.name)
         .having(team.name.eq("teamName"))
		 .fetch();
}        
         
 Tuple teamA = result.get(0);
 Tuple teamB = result.get(1);

• 조인사용

  조인의 기본 문법은 첫 번째 파라미터에 조인 대상을 지정하고, 두번째 파라미터에 별칭으로 사용할Q타입 지정
  join(조인대상, 별칭으로 사용할 Q타입)

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

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

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

• 조인대상 필터링 (조인 - on)

/* 
JPQL: 
 SELECT m, t FROM Member m LEFT JOIN m.team t on t.name = 'teamA'
SQL: 
 SELECT m.*, t.* FROM Member m LEFT JOIN Team t ON m.TEAM_ID=t.id and
 t.name='teamA'
*/
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();
 }

• 연관관계 없는 외부조인

/* 
JPQL: 
 SELECT m, t FROM Member m LEFT JOIN Team t on m.username = t.name
SQL: 
 SELECT m.*, t.* FROM Member m LEFT JOIN Team t ON m.username = t.name
 */
public void join_on_no_relation() throws Exception {
 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();
 }

• 페치조인
  페치 조인은 SQL에서 제공하는 기능은 아니다. SQL조인을 활용해서 연관된 엔티티를 SQL 한번에 조회하
  는 기능이다. 주로 성능 최적화에 사용하는 방법이다

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

• 서브 쿼리

public void subQuery(){
	 QMember memberSub = new QMember("memberSub");
     //QMember memberSub = QMember.memberSub;
 
   //서브쿼리 select절 사용
  List<Tuple> fetch = queryFactory
       .select(member.username,
               JPAExpressions
               .select(memberSub.age.avg())
               .from(memberSub)
		).from(member)
       .fetch();
       
 	//서브쿼리 where절 사용
    List<Member> result = queryFactory
       .selectFrom(member)
       .where(member.age.in(
             JPAExpressions
             .select(memberSub.age)
             .from(memberSub)
	         .where(memberSub.age.gt(10))
       ))
       .fetch();
}
//서브쿼리 사용되는 Q타입도 static import 사용

• Case문

List<String> result = queryFactory
   .select(member.age
   .when(10).then("열살")
   .when(20).then("스무살")
   .otherwise("기타"))
   .from(member)
   .fetch();
 
 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();

• 상수, 문자 더하기

//상수더하기
Tuple result = queryFactory
   .select(member.username, Expressions.constant("A"))
   .from(member)
   .fetchFirst();
 
 //문자더하기
 //stringValue() 를 사용하여 문자가 아닌 타입을 문자로 변경
 //enum처리시 자주 사용
 String result = queryFactory
   .select(member.username.concat("_").concat(member.age.stringValue()))
   .from(member)
   .where(member.username.eq("member1"))
   .fetchOne();

중급문법

• 프로젝션 : select 대상 지정
• 프로젝션 대상이 하나
  타입을 명확하게 지정가능

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

//프로퍼티접근 - 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();
   
//별칭이 다를 경우  
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

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

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

@QueryProjection을 사용시 컴파일러로 타입 체크를 할 수 있지만,
DTO에 Querydsl 어노테이션을 유지해야하고, DTO까지 Q파일을 생성해야함.

• 동적쿼리 - BooleanBuilder

@Test
public void 동적쿼리_BooleanBuilder() throws Exception {
   String usernameParam = "member1";
   Integer ageParam = 10;
   
   List<Member> result = searchMember1(usernameParam, ageParam);
   
}


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 다중 파라미터 사용

@Test
public void 동적쿼리_WhereParam() throws Exception {
 String usernameParam = "member1";
 Integer ageParam = 10;
 
 List<Member> result = searchMember2(usernameParam, ageParam);

}

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을 사용시에 null값은 무시된다.
메서드를 다른 쿼리에서더 재활용가능

• 수정, 삭제 벌크연산

//기존 숫자에 +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();

JPQL과 마찬가지로 영속성 컨텍스트에 있는 엔티티를 무시하고 실행되기에
실행 후 영속성 컨텍스트 초기화 flush(), clear()

• SQL function 호출

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

스프링데이터 JPA와 Querydsl

• 사용자 정의 리포지토리
  

1. 사용자 정의 인터페이스 작성
2. 사용자 정의 인터페이스 구현
3. 스프링데이터 리포지토리에 사용자 정의 인터페이스 상속

1. 사용자 정의 인터페이스 작성
public interface MemberRepositoryCustom {
 	List<MemberTeamDto> search(MemberSearchCondition condition);
}

2. 사용자 정의 인터페이스 구현
public class MemberRepositoryImpl implements MemberRepositoryCustom {
	 private final JPAQueryFactory queryFactory;
	
    public MemberRepositoryImpl(EntityManager em) {
		 this.queryFactory = new JPAQueryFactory(em);
	}
    
 @Override
 //회원명, 팀명, 나이(ageGoe, ageLoe)
 public List<MemberTeamDto> search(MemberSearchCondition condition) {
	===구현===
 }
}

3. 스프링데이터 리포지토리에 사용자 정의 인터페이스 상속
public interface MemberRepository extends JpaRepository<Member, Long>,
MemberRepositoryCustom {
	 List<Member> findByUsername(String username);
}   

• 페이징 연동

public Page<MemberTeamDto> searchPageComplex(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);
}