[Spring_Boot] queryDSL 활용(1)

최현석·2022년 12월 16일
0

Spring_Boot

목록 보기
30/31

🧩 기본 QType 활용

QMember qMember = new QMember("m"); //별칭 직접 지정
QMember qMember = QMember.member; // 기본 인스턴스를 사용
QMember.member

  • Qmember에 있는 하위내용까지 사용(제일 많이 사용하는방법)
    • import static com.koreait.querydsl.entity.QMember.*;
    • member 만 넣고 사용 할 수 있다.

🧩 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%’ 검색

🧩 결과 조회

  • fetch() : 리스트 조회, 데이터 없으면 빈 리스트 반환
  • fetchOne() : 단건 조회
    -> 결과가 없으면 null
    -> 결과가 둘 이상이면 : NonUniqueResultException
  • fetchFirst() -> limit(1).fetchOne();
  • fetchResults() -> 페이징 정보 포함, total count 쿼리 추가 실행

Member

  • @NoArgsConstructor(access = AccessLevel.PROTECTED)
    기본 생성자를 PROTECTED 레벨로 만들어준다
@Entity
@Getter @Setter @ToString(of = {"id", "username", "age"})
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class Member {

	@Id @GeneratedValue
	@Column(name = "member_id")
	private Long id;
	private String username;
	private int age;
	
	@ManyToOne
	@JoinColumn(name = "team_id")
	private Team team;
	
	// 3번째 member호출
	public Member(String username, int age) {
		this(username, age, null);
	}
	
	// 첫번째 member호출
	public Member(String username) {
		this(username, 0);
	}
	
	public Member(String username, int age, Team team) {
		super();
		this.username = username;
		this.age = age;
		
		if(team != null) {
			changeTeam(team);
		}
		
	}
	
	public void changeTeam(Team team) {
		this.team = team;
		team.getMembers().add(this);
	}
	
	// lombok 에서 구현 가능
//	protected Member() {}
	
	// toString
	// lombok에서 구현
//	@Override
//	public String toString() {
//		return "Member [id=" + id + ", username=" + username + ", age=" + age + "]";
//	}
	
}

Team

@Entity
@Getter @Setter  @ToString(of = {"id", "name"})
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class Team {

	@Id @GeneratedValue
	@Column(name = "team_id")
	private Long id;
	private String name;
	
	@OneToMany(mappedBy = "team")
	private List<Member> members = new ArrayList<Member>();
	
	public Team(String name) {
		this.name = name;
	}
	
}

QClass 생성

  • Gradle Tasks -> build -> run -> refresh

QueryDSL Main 세팅

  • QClass를 사용한다.
  • static JPAQueryFactory queryFactory;
  • queryFactory = new JPAQueryFactory(em);

Main

			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);
			
			// 초기화
			em.flush();
			em.clear();

🧩 member 찾기

jpql 부분

			// member1 찾기
			String qString = "select m from Member m where m.username = :username";
			Member findByJpql = em.createQuery(qString, Member.class)
								  .setParameter("username", "member1")
								  .getSingleResult();
			
			System.out.println("findByJpql : " + findByJpql.getUsername().equals("member1"));

queryDSL 부분

  • QMember m = new QMember("m"); // QMember의 이름 부여, 별칭 부여
  • QMember m = QMember.member;
  • selectFrom : select하는 list와 from절이 같을 때 사용
	Member findByqQeryDSL = queryFactory.select(member)
											.from(member)
											.where(member.username.eq("member1")
													.and(member.age.eq(10))) // 파라미터 바인딩
											.fetchOne();
			System.out.println("findByqQeryDSL : " + findByqQeryDSL.getUsername().equals("member1"));
            
     // 첫번 째 방법         
	Member findByqQeryDSL2 = queryFactory.selectFrom(member)
											.where(member.username.eq("member1")
													.and(member.age.between(10, 30)))
											.fetchOne();  
     // 두번 째 방법                                       
	Member findByqQeryDSL3 = queryFactory.selectFrom(member)
											.where(
													member.username.eq("member1"),
													member.age.between(10, 30)
											)
											.fetchOne();
  • select 사용
  • between 사용

🧩 list, 단건,count 결과 조회

			// List
			List<Member> fetch = queryFactory
									.selectFrom(member)
									.fetch();
			
			// 단건
			Member fetch2 = queryFactory
								.selectFrom(member)
								.fetchOne();
			
			
			Member fetch3 = queryFactory
								.selectFrom(member)
								.fetchFirst();
						
			long count = queryFactory
							.selectFrom(member)
							.fetchCount();
			
			Long totalCount = queryFactory
								.select(member.count())
								.from(member)
								.fetchOne();
			System.out.println("totalCount : "+ totalCount);
  • count

🧩 정렬

  • 회원 정렬 순서
    1. 회원 나이 내림차순(desc)
    2. 회원 이름 올림차순(asc)
      단, 2에서 회원 이름이 없으면 마지막에 출력(nulls last)
			List<Member> result = queryFactory
									.selectFrom(member)
									.where(member.age.eq(100))
									.orderBy(member.age.desc(), member.username.asc().nullsLast())
									.fetch();
			
			System.out.println("result1 : " + result.get(0));
			System.out.println("result2 : " + result.get(1));
			System.out.println("result3 : " + result.get(2));
  • 결과값
  • 쿼리문

🧩 페이징

  • totalcount가 보고싶을 때는 한번 더 다녀오면 된다.
			List<Member> result = queryFactory
									.selectFrom(member)
									.orderBy(member.username.desc())
									.offset(1)		// index -> 0부터 시작, 
									.limit(2)		// 최대 2건 조회
									.fetch();

🧩집합 함수

  • List<Tuple>를 사용한다.

	/*
			 * [jpql]
			 * select
			 *  count(m), 		// 회원수
			 *  sum(m.age)		// 나이 합
			 *  avg(m.age)		// 평균 나이
			 *  max(m.age)		// 최대 나이
			 *  min(m.age)		// 최소 나이 
			 * from Member m
			 * 
			 */
			
			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);
			
			System.out.println(tuple.get(member.count()));
			System.out.println(tuple.get(member.age.sum()));
			System.out.println(tuple.get(member.age.avg()));
			System.out.println(tuple.get(member.age.max()));
			System.out.println(tuple.get(member.age.min()));
  • 결과
  • 쿼리문
  • group by 사용
			/*
			 * 팀의 이름과 각 팀의 평균 연령 구하기
			 */
			List<Tuple> result2 = queryFactory
									.select(team.name, member.age.avg())
									.from(member)
                                    // member에 있는 team과 team을 join
									.join(member.team, team) 
									.groupBy(team.name)
									.fetch();
			
			System.out.println("teamA : " + result2.get(0).toString());
			System.out.println("teamB : " + result2.get(1).toString());
  • 결과
  • 쿼리
  • having
			
			List<Tuple> result3 = queryFactory
					.select(team.name, member.age.avg())
					.from(member)
					.join(member.team, team) // member에 있는 team과 team을 join
					.groupBy(team.name)
					.having(member.age.avg().gt(10))
					.fetch();
  • 쿼리문
profile
최현석
이전 포스트

[Spring_Boot] JPA SHOP - Order( 예외처리 만들기 & 가변인자 & CasecadeType & queryDSL)

다음 포스트

[Spring_Boot] queryDSL 활용(2)

0개의 댓글