2020년 7월 24일 금요일

Querydsl, JPQL, 쿼리 작성용 Methods, from innerJoin, join, leftJoin, fullJoin, on where groupBy having orderBy limit, offset, restrict

Querydsl, JPQL, 쿼리 작성용 Methods, from innerJoin, join, leftJoin, fullJoin, on where groupBy having orderBy limit, offset, restrict

http://ojc.asia/bbs/board.php?bo_table=LecJpa&wr_id=345

4. 쿼리 작성용 Methods

JPAQuery 메소드들은 모두 자신이 속한 객체를 리턴하므로 메소드 체이닝기법으로 "."을 찍고 연속해서 메소드를 사용할 수 있습니다. JPAQuery가 구현한 JPQLQuery 인터페이스의 cascading 메소드들은 다음과 같습니다.

메소드

기능

from

쿼리 대상을 설정한다.

innerJoin, join,

leftJoin, fullJoin, on

조인 부분을 추가한다. 조인 메소드에서 첫 번째 인자는 조인 소스이고, 두 번재 인자는 대상(별칭으로 지정 가능)이다.

where

쿼리에 필터를 추가한다.

가변인자나 and, or 메소드를 이용해서 필터를 추가한다.

groupBy

가변인자 형식의 인자를 기준으로 그룹을 추가한다.

having

Predicate 표현식을 이용해서 "group by" 그룹핑의 필터를 추가한다.

orderBy

정렬 표현식을 이용해서 정렬 순서를 지정한다. 숫자나 문자열에 대해서는 asc()나 desc()를 사용하고, OrderSpecifier에 접근하기 위해 다른 비교 표현식을 사용한다.

limit, offset, restrict

결과의 페이징을 설정한다. limit은 최대 결과 개수, offset은 앞에서부터 건너 뛸 로우의 개수, restrict는 limit과 offset을 함께 정의한다.

​

5. Order by

EmpDao.java

package com.example.employee.repository;

import java.util.List;

import javax.persistence.EntityManager;

import javax.persistence.PersistenceContext;

import org.springframework.stereotype.Repository;

import com.example.employee.model.Emp;

import com.example.employee.model.QEmp;

import com.querydsl.jpa.impl.JPAQuery;

@Repository

public class EmpDao {

@PersistenceContext

private EntityManager entityManager;

public List<Emp> getEmpOrderByDeptnoAscEmpnoDesc(){

QEmp emp = QEmp.emp;

JPAQuery<?> query = new JPAQuery<Void>(entityManager);

List<Emp> emps = query.select(emp).from(emp)

.orderBy(emp.dept.deptno.asc(), emp.empno.desc()).fetch();

return emps;

}

}

EmpDaoTest.java

package com.example.employee.repository;

import static org.hamcrest.CoreMatchers.is;

import static org.junit.Assert.assertThat;

import java.util.List;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;

import org.springframework.test.context.junit4.SpringRunner;

import org.springframework.transaction.annotation.Transactional;

import com.example.employee.model.Emp;

@Transactional

@RunWith(SpringRunner.class)

@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)

public class EmpDaoTest {

@Autowired

private EmpDao empDao;

@Test

public void testGetEmpOrderByDeptnoAscEmpnoDesc() {

List<Emp> emps = empDao.getEmpOrderByDeptnoAscEmpnoDesc();

for (Emp emp : emps) {

System.out.println(emp);

}

assertThat(emps.size(), is(14));

}

}

SQL 로그

select

e.empno as empno1_1_,

e.comm as comm2_1_,

e.deptno as deptno7_1_,

e.ename as ename3_1_,

e.hiredate as hiredate4_1_,

e.job as job5_1_,

e.mgr as mgr8_1_,

e.sal as sal6_1_

from emp e left outer join dept d

on e.deptno=d.deptno

order by d.deptno asc, e.empno desc

​

Emp : Dept = N : 1 의 연관관계에서 Emp의 정보를 요청했습니다. 이 경우 EAGER 로딩정책에 따라 Dept의 정보를 구하는 쿼리가 수행되는 것을 로그에서 볼 수 있습니다.

emp.dept.deptno.asc()

위 구문을 처리하기 위해서 조인을 사용하였습니다. 사용된 SQL 쿼리를 살펴보면 "order by d.deptno asc" 처럼 처리하기 위해서 조인을 사용했다고 판단할 수 있습니다.

6. Where

EmpDao.java

다음 메소드를 추가합니다.

public Emp getEmpByEnameAndJob(String ename, String job){

QEmp emp = QEmp.emp;

JPAQuery<?> query = new JPAQuery<Void>(entityManager);

Emp result = query.select(emp).from(emp)

.where(emp.ename.eq(ename), emp.job.eq(job)).fetchFirst();

return result;

}

where 메소드에 전달하는 구문으로 콤마(,) 대신 and 메소드로 대체할 수 있습니다.

Emp result = query.select(emp).from(emp)

.where(emp.ename.eq(ename).and(emp.job.eq(job))).fetchFirst();

필터 조건을 or로 조합하고 싶다면 다음 패턴을 사용합니다.

List<Emp> result = query.select(emp).from(emp)

.where(emp.ename.eq(ename).or(emp.job.eq(job))).fetch();

EmpDaoTest.java

@Test

public void testGetEmpByEnameAndJob() {

Emp emp = empDao.getEmpByEnameAndJob("SMITH", "CLERK");

System.out.println(emp);

assertThat(emp.getEname(), is("SMITH"));

assertThat(emp.getJob(), is("CLERK"));

}

SQL 로그

select

e.empno as empno1_1_,

e.comm as comm2_1_,

e.deptno as deptno7_1_,

e.ename as ename3_1_,

e.hiredate as hiredate4_1_,

e.job as job5_1_,

e.mgr as mgr8_1_,

e.sal as sal6_1_

from emp e

where e.ename='SMITH' and e.job='CLERK'

limit 1

​

​

fetchFirst 메소드를 사용하면 ' limit 1' 구문이 사용되는 것을 알 수 있습니다. 추가로 deptno=20에 해당하는 정보를 구합니다.

객체 그래프 탐색을 위한 결과

'SMITH'에 상사 직원정보를 연속적으로 쿼리해서 얻어진 결과 로그입니다.

Emp(

empno=7369,

ename=SMITH,

job=CLERK,

mgr=Emp(

empno=7902,

ename=FORD,

job=ANALYST,

mgr=Emp(

empno=7566,

ename=JONES,

job=MANAGER,

mgr=Emp(

empno=7839,

ename=KING,

job=PRESIDENT,

mgr=null,

hiredate=1981-11-17,

sal=5000.0,

comm=null,

dept=Dept(deptno=10, dname=ACCOUNTING, loc=NEW YORK)

),

hiredate=1981-04-02,

sal=2975.0,

comm=null,

dept=Dept(deptno=20, dname=RESEARCH, loc=DALLAS)

),

hiredate=1981-12-03,

sal=3000.0,

comm=null,

dept=Dept(deptno=20, dname=RESEARCH, loc=DALLAS)

),

hiredate=1980-12-17,

sal=800.0,

comm=null,

dept=Dept(deptno=20, dname=RESEARCH, loc=DALLAS)

)

​

7. 비교구문

com.querydsl.core.types.dsl.ComparableExpression<T>

where 조건절에 필터로 설정할 수 있는 메소드 목록입니다.

메소드

설명

as(Path<T> alias)

Create an alias for the expression

as(String alias)

Create an alias for the expression

between(Expression<T> from, Expression<T> to)

Create a this between from and to expression

between(T from, T to)

Create a this between from and to expression

goe(Expression<T> right)

Create a this >= right expression

goe(T right)

Create a this >= right expression

goeAll(CollectionExpression<?,? super T> right)

Create a this >= all right expression

goeAll(SubQueryExpression<? extends T> right)

Create a this >= all right expression

goeAny(CollectionExpression<?,? super T> right)

Create a this >= any right expression

goeAny(SubQueryExpression<? extends T> right)

Create a this >= any right expression

gt(Expression<T> right)

Create a this > right expression

gt(T right)

Create a this > right expression

gtAll(CollectionExpression<?,? super T> right)

Create a this > all right expression

gtAll(SubQueryExpression<? extends T> right)

Create a this > all right expression

gtAny(CollectionExpression<?,? super T> right)

Create a this > any right expression

gtAny(SubQueryExpression<? extends T> right)

Create a this > any right expression

loe(Expression<T> right)

Create a this <= right expression

loe(T right)

Create a this <= right expression

loeAll(CollectionExpression<?,? super T> right)

Create a this <= all right expression

loeAll(SubQueryExpression<? extends T> right)

Create a this <= all right expression

loeAny(CollectionExpression<?,? super T> right)

Create a this <= any right expression

loeAny(SubQueryExpression<? extends T> right)

Create a this <= any right expression

lt(Expression<T> right)

Create a this < right expression

lt(T right)

Create a this < right expression

ltAll(CollectionExpression<?,? super T> right)

Create a this < all right expression

ltAll(SubQueryExpression<? extends T> right)

Create a this < all right expression

ltAny(CollectionExpression<?,? super T> right)

Create a this < any right expression

ltAny(SubQueryExpression<? extends T> right)

Create a this < any right expression

notBetween(Expression<T> from, Expression<T> to)

Create a this not between from and to expression

notBetween(T from, T to)

Create a this not between from and to expression

​

실습

다음 요청사항에 맞는 코드를 작성하시오.

n emp 테이블에서 직업의 종류를 출력하시오.

n 이름이 "S"로 시작하는 직원을 출력하시오.

n 이름중에 "s" 또는 "S"가 들어가는 직원을 출력하시오.

n 이름중에 "A"자가 포함되는 직원을 출력하시오.

n 직원번호가 1000부터 2000번 사이에 있는 직원을 출력하시오.

8. Paging

페이징은 offset과 limit를 사용합니다. 페이징처리를 위해서는 전체건수를 알아야 하므로 count 쿼리를 내부적으로 실행합니다.

public QueryResults<Emp> getEmpForPaging(long offset, long count) {

JPAQuery<Emp> query = queryFactory.select(emp).from(emp)

.orderBy(emp.empno.asc());

query.offset(offset).limit(count);

QueryResults<Emp> result = query.fetchResults();

return result;

}

​

@Test

public void testGetEmpForPaging() {

long offset = 10;

long count = 10;

QueryResults<Emp> result = dao.getEmpForPaging(offset, count);

long resultCount = result.getTotal();

System.out.println("resultCount = " + resultCount);

if (!result.isEmpty()) {

List<Emp> emps = result.getResults();

for (Emp emp : emps) {

System.out.println(emp);

}

}

}

​

4.2.2. Insert, Update, Delete

데이터를 조작하는 방법을 살펴보겠습니다. 코드 자체가 설명적이므로 자세한 설명은 생략합니다.

DmlDeptDao.java

package com.example.employee.repository;

import javax.persistence.EntityManager;

import javax.persistence.PersistenceContext;

import org.springframework.stereotype.Repository;

import com.example.employee.model.Dept;

import com.example.employee.model.QDept;

import com.querydsl.jpa.impl.JPADeleteClause;

import com.querydsl.jpa.impl.JPAQuery;

import com.querydsl.jpa.impl.JPAUpdateClause;

@Repository

public class DmlDeptDao {

@PersistenceContext

private EntityManager entityManager;

// select count(d.deptno) as col_0_0_ from dept d

public long count() {

QDept dept = QDept.dept;

JPAQuery<?> query = new JPAQuery<Void>(entityManager);

long count = query.select(dept.deptno).from(dept).fetchCount();

return count;

}

// select

// d.deptno as deptno1_0_,

// d.dname as dname2_0_,

// d.loc as loc3_0_

// from dept d

// where d.deptno=10

public Dept getDeptByDeptno(long deptno) {

QDept dept = QDept.dept;

JPAQuery<?> query = new JPAQuery<Void>(entityManager);

Dept result = query.select(dept).from(dept).where(dept.deptno.eq(deptno)).fetchOne();

return result;

}

// insert into dept (dname, loc, deptno) values ('XXX', 'SEOUL', 50)

public long insert(Dept dept) {

// Querydsl은 입력쿼리를 따로 지원하지 않는다.

// 엔티티매니저를 통해 입력을 처리한다.

entityManager.persist(dept);

entityManager.flush();

return dept.getDeptno();

}

// update dept set dname='YYY' where deptno=50

public long update(Dept dept) {

QDept department = QDept.dept;

long affected = new JPAUpdateClause(entityManager, department)

.where(department.deptno.eq(dept.getDeptno()))

.set(department.dname, dept.getDname()).execute();

return affected;

}

// delete from dept where deptno=50

public long delete(Long deptno) {

QDept department = QDept.dept;

long affected = new JPADeleteClause(entityManager, department)

.where(department.deptno.eq(deptno)).execute();

return affected;

}

}

JPAQueryFactory 객체를 주입받아 사용하는 경우 사용방법은 다음과 같습니다.

long affected = queryFactory.update(department)

.where(department.deptno.eq(dept.getDeptno()))

.set(department.dname, dept.getDname()).execute();

​

long affected = queryFactory.delete(department)

.where(department.deptno.eq(deptno)).execute();

DmlDeptDaoTest.java

package com.example.employee.repository;

import static org.hamcrest.CoreMatchers.is;

import static org.junit.Assert.assertThat;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;

import org.springframework.test.context.junit4.SpringRunner;

import org.springframework.transaction.annotation.Transactional;

import com.example.employee.model.Dept;

@Transactional

@RunWith(SpringRunner.class)

@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)

public class DmlDeptDaoTest {

@Autowired

private DmlDeptDao deptDao;

@Test

public void testCount() {

long count = deptDao.count();

System.out.println("count = "+count);

assertThat(count, is(4L));

}

@Test

public void testGetDeptByDeptno() {

Dept dept = deptDao.getDeptByDeptno(10L);

System.out.println(dept);

assertThat(dept.getDname(), is("ACCOUNTING"));

}

@Test

public void testInsert() {

long count = deptDao.count();

Dept dept = new Dept();

dept.setDeptno(50L);

dept.setDname("XXX");

dept.setLoc("SEOUL");

long key = deptDao.insert(dept);

System.out.println(key);

assertThat(key, is(50L));

assertThat(deptDao.count(), is(count+1));

}

@Test

public void testUpdate() {

Dept dept = new Dept();

dept.setDeptno(50L);

dept.setDname("XXX");

dept.setLoc("SEOUL");

// insert into dept (dname, loc, deptno) values ('XXX', 'SEOUL', 50)

deptDao.insert(dept);

dept.setDname("YYY");

// update dept set dname='YYY' where deptno=50

long affected = deptDao.update(dept);

assertThat(affected, is(1L));

// select

// d.deptno as deptno1_0_,

// d.dname as dname2_0_,

// d.loc as loc3_0_

// from dept d

// where d.deptno=50

Dept result = deptDao.getDeptByDeptno(dept.getDeptno());

assertThat(result.getDname(), is("YYY"));

}

@Test

public void testDelete() {

long count = deptDao.count();

Dept dept = new Dept();

dept.setDeptno(50L);

dept.setDname("XXX");

dept.setLoc("SEOUL");

deptDao.insert(dept);

assertThat(deptDao.count(), is(count+1));

long affected = deptDao.delete(dept.getDeptno());

assertThat(affected, is(1L));

assertThat(deptDao.count(), is(count));

}

}

​

4.2.3. Grouping, Having

GroupEmpDao.java

package com.example.employee.repository;

import java.util.List;

import javax.persistence.EntityManager;

import javax.persistence.PersistenceContext;

import org.springframework.stereotype.Repository;

import com.example.employee.model.QEmp;

import com.querydsl.core.Tuple;

import com.querydsl.jpa.impl.JPAQuery;

@Repository

public class GroupEmpDao {

@PersistenceContext

private EntityManager entityManager;

public List<Tuple> getEmpGroupByDeptno(){

QEmp emp = QEmp.emp;

JPAQuery<?> query = new JPAQuery<Void>(entityManager);

List<Tuple> rows = query

.select(emp.dept.deptno,

emp.count(),

emp.sal.avg(),

emp.sal.max())

.from(emp).groupBy(emp.dept.deptno).fetch();

return rows;

}

public List<Tuple> getEmpGroupByDeptnoHaving(){

QEmp emp = QEmp.emp;

JPAQuery<?> query = new JPAQuery<Void>(entityManager);

List<Tuple> rows = query

.select(emp.dept.deptno,

emp.count(),

emp.sal.avg(),

emp.sal.max())

.from(emp).groupBy(emp.dept.deptno)

.having(emp.count().goe(5)).fetch();

return rows;

}

}

​

GroupEmpDaoTest.java

package com.example.employee.repository;

import java.util.List;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;

import org.springframework.test.context.junit4.SpringRunner;

import org.springframework.transaction.annotation.Transactional;

import com.querydsl.core.Tuple;

@Transactional

@RunWith(SpringRunner.class)

@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)

public class GroupEmpDaoTest {

@Autowired

private GroupEmpDao empDao;

@Test

public void testGetEmpGroupByDeptno() {

List<Tuple> rows = empDao.getEmpGroupByDeptno();

for (Tuple row : rows) {

System.out.println("--------------------");

System.out.println(row.get(0, Long.class));

System.out.println(row.get(1, Long.class));

System.out.println(Math.round(row.get(2, Double.class)));

System.out.println(row.get(3, Long.class));

}

}

@Test

public void testGetEmpGroupByDeptnoHaving() {

List<Tuple> rows = empDao.getEmpGroupByDeptnoHaving();

for (Tuple row : rows) {

System.out.println("--------------------");

System.out.println(row.get(0, Long.class));

System.out.println(row.get(1, Long.class));

System.out.println(Math.round(row.get(2, Double.class)));

System.out.println(row.get(3, Long.class));

}

}

}

​

SQL 로그

select

e.deptno as col_0_0_,

count(e.empno) as col_1_0_,

avg(e.sal) as col_2_0_,

max(e.sal) as col_3_0_

from emp e

group by e.deptno

​

select

e.deptno as col_0_0_,

count(e.empno) as col_1_0_,

avg(e.sal) as col_2_0_,

max(e.sal) as col_3_0_

from emp e

group by e.deptno

having count(e.empno)>=5

​

4.2.4. Join

여러 개의 테이블을 조인처리하여 데이터를 구하는 방법을 살펴보겠습니다.

JoinEmpDao.java

package com.example.employee.repository;

import java.util.List;

import javax.persistence.EntityManager;

import javax.persistence.PersistenceContext;

import org.springframework.stereotype.Repository;

import com.example.employee.model.QDept;

import com.example.employee.model.QEmp;

import com.querydsl.core.Tuple;

import com.querydsl.jpa.impl.JPAQuery;

@Repository

public class JoinEmpDao {

@PersistenceContext

private EntityManager entityManager;

// select

// e.empno as col_0_0_,

// e.ename as col_1_0_,

// e.deptno as col_2_0_,

// d.dname as col_3_0_

// from emp e inner join dept d

// on e.deptno=d.deptno

// order by e.empno asc

public List<Tuple> getEmpDeptUsingJoin(){

QEmp emp = QEmp.emp;

QDept dept = QDept.dept;

JPAQuery<?> query = new JPAQuery<Void>(entityManager);

List<Tuple> rows = (List<Tuple>) query

.select(emp.empno,

emp.ename,

emp.dept.deptno,

dept.dname)

.from(emp).join(emp.dept, dept)

.orderBy(emp.empno.asc())

.fetch();

return rows;

}

// select

// e.empno as col_0_0_,

// e.ename as col_1_0_,

// e.deptno as col_2_0_,

// d.dname as col_3_0_

// from emp e inner join dept d

// on e.deptno=d.deptno

public List<Tuple> getEmpDeptUsingInnerJoin(){

QEmp emp = QEmp.emp;

QDept dept = QDept.dept;

JPAQuery<?> query = new JPAQuery<Void>(entityManager);

List<Tuple> rows = (List<Tuple>) query

.select(emp.empno,

emp.ename,

emp.dept.deptno,

dept.dname)

.from(emp).innerJoin(emp.dept, dept)

.orderBy(emp.empno.asc())

.fetch();

return rows;

}

public List<Tuple> getEmpDeptUsingLeftJoin(){

QEmp emp = QEmp.emp;

QDept dept = QDept.dept;

JPAQuery<?> query = new JPAQuery<Void>(entityManager);

​

// select

// d.deptno as col_0_0_,

// d.dname as col_1_0_,

// e.empno as col_2_0_,

// e.ename as col_3_0_

// from emp e left outer join dept d

// on e.deptno=d.deptno

List<Tuple> rows = (List<Tuple>) query

.select(dept.deptno,

dept.dname,

emp.empno,

emp.ename)

.from(emp).leftJoin(emp.dept, dept)

.orderBy(emp.empno.asc())

.fetch();

return rows;

}

// select

// d.deptno as col_0_0_,

// d.dname as col_1_0_,

// e.empno as col_2_0_,

// e.ename as col_3_0_

// from emp e right outer join dept d

// on e.deptno=d.deptno

public List<Tuple> getEmpDeptUsingRightJoin(){

QEmp emp = QEmp.emp;

QDept dept = QDept.dept;

JPAQuery<?> query = new JPAQuery<Void>(entityManager);

List<Tuple> rows = (List<Tuple>) query

.select(dept.deptno,

dept.dname,

emp.empno,

emp.ename)

.from(emp).rightJoin(emp.dept, dept)

.orderBy(emp.empno.asc())

.fetch();

return rows;

}

// select

// e.empno as col_0_0_,

// e.ename as col_1_0_,

// e.deptno as col_2_0_,

// m.empno as col_3_0_,

// m.ename as col_4_0_,

// d.deptno as deptno1_0_,

// d.dname as dname2_0_,

// d.loc as loc3_0_

// from emp e inner join emp m

// on e.mgr=m.empno inner join dept d

// on e.deptno=d.deptno

// order by e.empno asc

public List<Tuple> getEmpDeptUsingSelfJoin(){

QEmp e = new QEmp("e");

QEmp m = new QEmp("m");

JPAQuery<?> query = new JPAQuery<Void>(entityManager);

List<Tuple> rows = (List<Tuple>) query

.select(e.empno,

e.ename,

e.dept,

m.empno,

m.ename)

.from(e).join(e.mgr, m)

.orderBy(e.empno.asc())

.fetch();

return rows;

}

// select

// e.empno as col_0_0_,

// e.ename as col_1_0_,

// e.deptno as col_2_0_,

// m.empno as col_3_0_,

// m.ename as col_4_0_

// from emp e inner join emp m

// on e.mgr=m.empno

// order by e.empno asc

public List<Tuple> getEmpDeptUsingSelfJoin2(){

QEmp e = new QEmp("e");

QEmp m = new QEmp("m");

JPAQuery<?> query = new JPAQuery<Void>(entityManager);

List<Tuple> rows = (List<Tuple>) query

.select(e.empno,

e.ename,

e.dept.deptno,

m.empno,

m.ename)

.from(e).join(e.mgr, m)

.orderBy(e.empno.asc())

.fetch();

return rows;

}

// select

// d.deptno as col_0_0_,

// d.dname as col_1_0_,

// e.empno as col_2_0_,

// e.ename as col_3_0_,

// e.sal as col_4_0_

// from dept d left outer join emp e

// on d.deptno=e.deptno

// and (e.sal>=2000.0)

// order by d.deptno asc, e.empno asc

public List<Tuple> getEmpDeptUsingLeftJoin2(){

QEmp emp = QEmp.emp;

QDept dept = QDept.dept;

JPAQuery<?> query = new JPAQuery<Void>(entityManager);

List<Tuple> rows = query

.select(dept.deptno,

dept.dname,

emp.empno,

emp.ename,

emp.sal)

.from(dept).leftJoin(dept.emps, emp)

.on(emp.sal.goe(2000D))

.orderBy(dept.deptno.asc(),emp.empno.asc())

.fetch();

return rows;

}

}

join과 innerJoin 은 결과적으로 차이가 없습니다.

​

JoinEmpDaoTest.java

package com.example.employee.repository;

import java.util.List;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;

import org.springframework.test.context.junit4.SpringRunner;

import org.springframework.transaction.annotation.Transactional;

import com.example.employee.model.QDept;

import com.example.employee.model.QEmp;

import com.querydsl.core.Tuple;

@Transactional

@RunWith(SpringRunner.class)

@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)

public class JoinEmpDaoTest {

@Autowired

private JoinEmpDao empDao;

@Test

public void testGetEmpDeptUsingJoin() {

QEmp emp = QEmp.emp;

QDept dept = QDept.dept;

List<Tuple> rows = empDao.getEmpDeptUsingJoin();

System.out.println(rows);

for (Tuple row : rows) {

System.out.println("--------------------");

System.out.println(row.get(emp.empno));

System.out.println(row.get(emp.ename));

System.out.println(row.get(emp.dept.deptno));

System.out.println(row.get(dept.dname));

}

}

@Test

public void testGetEmpDeptUsingInnerJoin() {

QEmp emp = QEmp.emp;

QDept dept = QDept.dept;

List<Tuple> rows = empDao.getEmpDeptUsingInnerJoin();

System.out.println(rows);

for (Tuple row : rows) {

System.out.println("--------------------");

System.out.println(row.get(emp.empno));

System.out.println(row.get(emp.ename));

System.out.println(row.get(emp.dept.deptno));

System.out.println(row.get(dept.dname));

}

}

@Test

public void testGetEmpDeptUsingLeftJoin() {

QEmp emp = QEmp.emp;

QDept dept = QDept.dept;

List<Tuple> rows = empDao.getEmpDeptUsingLeftJoin();

System.out.println(rows);

for (Tuple row : rows) {

System.out.println("--------------------");

System.out.println(row.get(dept.deptno));

System.out.println(row.get(dept.dname));

System.out.println(row.get(emp.empno));

System.out.println(row.get(emp.ename));

}

}

@Test

public void testGetEmpDeptUsingRightJoin() {

QEmp emp = QEmp.emp;

QDept dept = QDept.dept;

List<Tuple> rows = empDao.getEmpDeptUsingRightJoin();

System.out.println(rows);

for (Tuple row : rows) {

System.out.println("--------------------");

System.out.println(row.get(dept.deptno));

System.out.println(row.get(dept.dname));

System.out.println(row.get(emp.empno));

System.out.println(row.get(emp.ename));

}

}

@Test

public void testGetEmpDeptUsingSelfJoin() {

QEmp e = new QEmp("e");

QEmp m = new QEmp("m");

List<Tuple> rows = empDao.getEmpDeptUsingSelfJoin();

System.out.println(rows);

for (Tuple row : rows) {

System.out.println("--------------------");

System.out.println(row.get(e.empno));

System.out.println(row.get(e.ename));

System.out.println(row.get(e.dept));

System.out.println(row.get(m.empno));

System.out.println(row.get(m.ename));

}

}

@Test

public void testGetEmpDeptUsingSelfJoin2() {

QEmp e = new QEmp("e");

QEmp m = new QEmp("m");

List<Tuple> rows = empDao.getEmpDeptUsingSelfJoin2();

System.out.println(rows);

for (Tuple row : rows) {

System.out.println("--------------------");

System.out.println(row.get(e.empno));

System.out.println(row.get(e.ename));

System.out.println(row.get(e.dept.deptno));

System.out.println(row.get(m.empno));

System.out.println(row.get(m.ename));

}

}

@Test

public void testGetEmpDeptUsingLeftJoin2() {

QEmp emp = QEmp.emp;

QDept dept = QDept.dept;

List<Tuple> rows = empDao.getEmpDeptUsingLeftJoin2();

System.out.println(rows);

for (Tuple row : rows) {

System.out.println("--------------------");

System.out.println(row.get(dept.deptno));

System.out.println(row.get(dept.dname));

System.out.println(row.get(emp.empno));

System.out.println(row.get(emp.ename));

System.out.println(row.get(emp.sal));

}

}

}

조인 시 조인 조건을 추가적으로 설정하기 위해서 on() 메소드를 사용합니다.

​

4.2.5. Subquery

JPAQuery, JPAQueryFactory 클래스는 인라인뷰 서브쿼리는 지원하지 않습니다. 그 외 Projection 영역이나 where 조건절 order by 절 등에서는 서브쿼리를 사용할 수 있습니다.

SubqueryEmpDao.java

package com.example.employee.repository;

import java.util.List;

import javax.persistence.EntityManager;

import javax.persistence.PersistenceContext;

import org.springframework.stereotype.Repository;

import com.example.employee.model.Emp;

import com.example.employee.model.QDept;

import com.example.employee.model.QEmp;

import com.querydsl.core.Tuple;

import com.querydsl.jpa.JPAExpressions;

import com.querydsl.jpa.impl.JPAQuery;

@Repository

public class SubqueryEmpDao {

@PersistenceContext

private EntityManager entityManager;

// select

// e.empno as empno1_1_,

// e.comm as comm2_1_,

// e.deptno as deptno7_1_,

// e.ename as ename3_1_,

// e.hiredate as hiredate4_1_,

// e.job as job5_1_,

// e.mgr as mgr8_1_,

// e.sal as sal6_1_

// from emp e

// where e.sal=(select max(e2.sal) from emp e2)

public List<Emp> getEmpOfMaxSalUsingSubquery() {

QEmp emp = QEmp.emp;

QEmp e = new QEmp("e");

JPAQuery<?> query = new JPAQuery<Void>(entityManager);

List<Emp> emps = query.select(emp)

.from(emp)

.where(emp.sal.eq(JPAExpressions.select(e.sal.max()).from(e)))

.fetch();

return emps;

}

// select

// d.deptno as col_0_0_,

// d.dname as col_1_0_,

// (select max(e.sal) from emp e

// where e.deptno=d.deptno) as col_2_0_

// from dept d

public List<Tuple> getDeptOfEmpSalUsingSubquery() {

QDept dept = QDept.dept;

QEmp emp = QEmp.emp;

JPAQuery<?> query = new JPAQuery<Void>(entityManager);

List<Tuple> emps = query

.select(dept.deptno,

dept.dname,

JPAExpressions.select(emp.sal.max())

.from(emp).where(emp.dept.deptno.eq(dept.deptno)))

.from(dept)

.fetch();

return emps;

}

}

SubqueryEmpDaoTest.java

package com.example.employee.repository;

import java.util.List;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;

import org.springframework.test.context.junit4.SpringRunner;

import org.springframework.transaction.annotation.Transactional;

import com.example.employee.model.Emp;

import com.querydsl.core.Tuple;

@Transactional

@RunWith(SpringRunner.class)

@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)

public class SubqueryEmpDaoTest {

@Autowired

private SubqueryEmpDao empDao;

@Test

public void testGetEmpOfMaxSalUsingSubquery() {

List<Emp> emps = empDao.getEmpOfMaxSalUsingSubquery();

for (Emp emp : emps) {

System.out.println(emp);

}

}

@Test

public void testGetDeptOfEmpSalUsingSubquery() {

List<Tuple> rows = empDao.getDeptOfEmpSalUsingSubquery();

for (Tuple row : rows) {

System.out.println(row.get(0, Long.class));

System.out.println(row.get(1, String.class));

System.out.println(row.get(2, Double.class));

}

}

}

4.2.6. Projection

검색결과로 얻고자 하는 칼럼을 설정하는 작업을 프로젝션이라고 합니다.

1. 기본 사용법

EmpDto.java

package com.example.employee.dto;

import lombok.Data;

@Data

public class EmpDto {

private Long empno;

private String ename;

}

ProjectionEmpDao.java

package com.example.employee.repository;

import java.util.List;

import javax.persistence.EntityManager;

import javax.persistence.PersistenceContext;

import org.springframework.stereotype.Repository;

import com.example.employee.dto.EmpDto;

import com.example.employee.model.QEmp;

import com.querydsl.core.types.Projections;

import com.querydsl.jpa.impl.JPAQuery;

@Repository

public class ProjectionEmpDao {

@PersistenceContext

private EntityManager entityManager;

public List<String> getJobList() {

QEmp emp = QEmp.emp;

JPAQuery<?> query = new JPAQuery<Void>(entityManager);

List<String> jobs = query.select(emp.job).distinct().from(emp)

.orderBy(emp.job.asc()).fetch();

return jobs;

}

/**

* Projections.bean : setter 메소드를 사용하여 대입한다.

* Projections.fileds : 필드변수에 직접 대입한다.

* Projections.consturctor : 생성자를 사용하여 대입한다.

*/

public List<EmpDto> getEmpForDto() {

QEmp emp = QEmp.emp;

JPAQuery<?> query = new JPAQuery<Void>(entityManager);

List<EmpDto> empDtos = query

.select(Projections.bean(EmpDto.class, emp.empno, emp.ename))

.from(emp)

.orderBy(emp.empno.asc()).fetch();

return empDtos;

}

}

distinct()

중복 결과를 제거하고 결과를 얻고자 하는 경우 추가적으로 설정해서 사용합니다.

ProjectionEmpDaoTest.java

package com.example.employee.repository;

import java.util.List;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;

import org.springframework.test.context.junit4.SpringRunner;

import org.springframework.transaction.annotation.Transactional;

import com.example.employee.dto.EmpDto;

@Transactional

@RunWith(SpringRunner.class)

@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)

public class ProjectionEmpDaoTest {

@Autowired

private ProjectionEmpDao empDao;

@Test

public void testGetJobList() {

List<String> jobs = empDao.getJobList();

for (String job : jobs) {

System.out.println(job);

}

}

@Test

public void testGetEmpForDto() {

List<EmpDto> empDtos = empDao.getEmpForDto();

for (EmpDto empDto : empDtos) {

System.out.println(empDto);

}

}

}

2. 결과 집합(aggregation)

com.querydsl.core.group.GroupBy클래스는 메모리에서 쿼리 결과에 대한 집합 연산을 수행하는 집합 함수를 제공합니다.

ProjectionEmpDao.java

public Map<Long, List<Emp>> getEmpForAggregation() {

QDept dept = QDept.dept;

QEmp emp = QEmp.emp;

JPAQuery<?> query = new JPAQuery<Void>(entityManager);

Map<Long, List<Emp>> results = query

.from(emp, dept)

.where(emp.dept.deptno.eq(dept.deptno))

.transform(groupBy(dept.deptno).as(list(emp)));

return results;

}

join 메소드 대신 form, where 메소드를 사용하여 조인 처리할 수도 있습니다.

ProjectionEmpDaoTest.java

@Test

public void testGetEmpForAggregation() {

Map<Long, List<Emp>> results = empDao.getEmpForAggregation();

for (Entry<Long, List<Emp>> entry : results.entrySet()) {

System.out.println(entry.getKey()+" : "+entry.getValue());

}

}

​

결과 로그

​

-
라벨:

댓글 없음:

댓글 쓰기

피드 구독하기: 댓글 (Atom)

(C#교육동영상)C# ADO.NET 실습 ODP.NET/ODAC 설치 오라클 함수 호출 실습, C#학원, WPF학원, 닷넷학원, 자바학원

  (C#교육동영상)C# ADO.NET 실습  ODP.NET/ODAC 설치  오라클 함수 호출 실습, C#학원, WPF학원, 닷넷학원, 자바학원 https://www.youtube.com/watch?v=qIPU85yAlzc&list=PLxU-i...