Querydsl을 이용한 SQL 쿼리, 조인, 서부쿼리 실습, SQLQueryFactory, Select, innerJoin,…

FSP 0 43 07.26 20:23

 

Querydsl을 이용한 SQL 쿼리, 조인, 서부쿼리 실습, SQLQueryFactory, Select, innerJoin, join,  leftJoin, fullJoin, on, where, having, orderBy, limit, offset, restrict, Insert, Update, Delete, 

5. Querydsl 이용한 SQL 쿼리

 

5.1. 테스트 프로젝트 만들기

"부록 7.3 Querydsl SQL Query with MySQL" 참조하여 진행합니다.

 

5.2. SQL Query 학습

5.2.1. Select

메소드에서 사용하는 쿼리를 주석으로 메소드 위에 추가해 놓았습니다.
먼저 SQL 쿼리를 살펴보고 어떻게 메소드로 작성하는지 살펴보세요.

 

1. JPASQLQuery, SQLQueryFactory 기본 사용법

SelectEmpDao.java

package com.example.employee.dao;

 

import java.util.List;

import javax.persistence.EntityManager;

import javax.persistence.PersistenceContext;

import javax.sql.DataSource;

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

import org.springframework.stereotype.Repository;

import com.example.gen.model.SEmp;

import com.querydsl.core.Tuple;

import com.querydsl.jpa.sql.JPASQLQuery;

import com.querydsl.sql.Configuration;

import com.querydsl.sql.MySQLTemplates;

import com.querydsl.sql.SQLQueryFactory;

import com.querydsl.sql.SQLTemplates;

import com.querydsl.sql.spring.SpringConnectionProvider;

import com.querydsl.sql.spring.SpringExceptionTranslator;

 

@Repository

public class SelectEmpDao {

        @PersistenceContext

        private EntityManager entityManager;

        @Autowired

        private DataSource dataSource;

       

//      select

//               emp.comm,

//               emp.deptno,

//               emp.empno,

//               emp.ename,

//               emp.hiredate,

//               emp.job,

//               emp.mgr,

//               emp.sal

//      from emp emp

//      order by emp.empno asc  

        public List<Tuple> getEmpUsingJPASQLQuery() {

                 QEmp emp = QEmp.emp;

                 SQLTemplates templates = new MySQLTemplates();

                 JPASQLQuery<?> query = new JPASQLQuery<Void>(entityManager, templates);

                

                 List<Tuple> rows = query.select(emp.all())

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

                 return rows;

        }

       

//      select

//               e.comm,

//               e.deptno,

//               e.empno,

//               e.ename,

//               e.hiredate,

//               e.job,

//               e.mgr,

//               e.sal

//      from emp e

//      order by e.empno asc    

        public List<Tuple> getEmpUsingSQLQueryFactory() {

                 QEmp emp = new QEmp("e");

                 SQLQueryFactory queryFactory = new SQLQueryFactory(

                                 querydslConfiguration(), new SpringConnectionProvider(dataSource));

                

                 List<Tuple> rows = queryFactory.select(emp.all())

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

                 return rows;

        }

       

        public Configuration querydslConfiguration() {

                 SQLTemplates templates = MySQLTemplates.builder().build();

                 Configuration configuration = new Configuration(templates);

                 configuration.setExceptionTranslator(new SpringExceptionTranslator());

                 return configuration;

        }

}

 

@PersistenceContext

빈으로 등록되어 있는 EntityManagerFactory 통해 EntityManager 주입 받습니다.

 


new JPASQLQuery<Void>(entityManager, templates)

JPASQLQuery 생성자에 EntityManager 방언정보를 갖고 있는 SQLTemplates 객체를 전달합니다. 엔티티매니저를 통해 쿼리를 처리하지만 그렇다고 EntityManager PersistenceContext객체 안에 결과가 보관되지는 않습니다.
PersistenceContext
보관처리하기 위해서는 엔티티 클래스를 사용해야 합니다.

 

new SQLQueryFactory(querydslConfiguration(), new SpringConnectionProvider(dataSource))

querydslConfiguration 메소드의 리턴결과는 방언정보 MySQLTemplates 객체를 갖고 있고 예외전환자로 SpringExceptionTranslator 객체를 갖고 있는 환경설정 정보를 취급하는 객체 Configuration 입니다. 데이터베이스 연결정보를 갖고 있으며 빈으로 등록되어 있는 DataSource 객체를 처리하여 번째 파라미터로 전달하고 있습니다. 부분의 로직은 별도의 환경설정 클래스로 분리하여 반복을 피하는 것이 좋습니다.

 

SelectEmpDaoTest.java

package com.example.employee.dao;

 

import static org.hamcrest.CoreMatchers.is;

import static org.junit.Assert.*;

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.gen.model.SEmp;

import com.querydsl.core.Tuple;

 

@Transactional

@RunWith(SpringRunner.class)

@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)

public class SelectEmpDaoTest {

        @Autowired

        private SelectEmpDao empDao;

       

        @Test

        public void testGetEmpUsingJPASQLQuery() {

                 QEmp emp = QEmp.emp;

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

                 for (Tuple row : rows) {

                         System.out.print(row.get(emp.empno)+",");

                         System.out.print(row.get(emp.ename)+",");

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

                 }

                

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

        }

 

        @Test

        public void testGetEmpUsingSQLQueryFactory() {

                 // 쿼리 작성 사용한 별칭으로 사용해야 한다.

                 QEmp emp = new QEmp("e");

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

                 for (Tuple row : rows) {

                         System.out.print(row.get(emp.empno)+",");

                         System.out.print(row.get(emp.ename)+",");

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

                 }

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

        }

}

 

Dialect 목록

Querydsl 지원하는 데이터베이스의 방언목록입니다.

CUBRIDTemplates (tested with CUBRID 8.4)

DerbyTemplates (tested with Derby 10.8.2.2)

FirebirdTemplates (tested with Firebird 2.5)

HSQLDBTemplates (tested with HSQLDB 2.2.4)

H2Templates (tested with H2 1.3.164)

MySQLTemplates (tested with MySQL 5.5)

OracleTemplates (test with Oracle 10 and 11)

PostgresTemplates (tested with PostgreSQL 9.1)

SQLiteTemplates (tested with xerial JDBC 3.7.2)

SQLServerTemplates (tested with SQL Server)

SQLServer2005Templates (for SQL Server 2005)

SQLServer2008Templates (for SQL Server 2008)

SQLServer2012Templates (for SQL Server 2012 and later)

TeradataTemplates (tested with Teradata 14)

 


2. SQLQueryFactory 등록 사용

반복적인 환경설정 관련 코드는 분리해서 처리해 놓고 사용하는 것이 좋습니다.

 

MyQuerydslConfig.java

package com.example.common.config;

 

import java.sql.Connection;

import javax.inject.Provider;

import javax.sql.DataSource;

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

import org.springframework.context.annotation.Bean;

import com.querydsl.sql.Configuration;

import com.querydsl.sql.MySQLTemplates;

import com.querydsl.sql.SQLQueryFactory;

import com.querydsl.sql.SQLTemplates;

import com.querydsl.sql.spring.SpringConnectionProvider;

import com.querydsl.sql.spring.SpringExceptionTranslator;

 

@org.springframework.context.annotation.Configuration

public class MyQuerydslConfig {

        @Autowired

        private DataSource dataSource;

       

        @Bean

        public Configuration configuration() {

                 SQLTemplates templates = MySQLTemplates.builder().build();

                 Configuration configuration = new Configuration(templates);

                 configuration.setExceptionTranslator(new SpringExceptionTranslator());

                 return configuration;

        }

       

        @Bean

        public SQLQueryFactory queryFactory() {

                 Provider<Connection> provider = new SpringConnectionProvider(dataSource);

                 return new SQLQueryFactory(configuration(), provider);

        }

}

 


SelectEmpDao2.java

package com.example.employee.dao;

 

import java.util.List;

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

import org.springframework.stereotype.Repository;

import com.example.gen.model.SEmp;

import com.querydsl.core.Tuple;

import com.querydsl.sql.SQLQueryFactory;

 

@Repository

public class SelectEmpDao2 {

        @Autowired

        private SQLQueryFactory queryFactory;

 

        public List<Tuple> getEmpUsingSQLQueryFactory() {

                 QEmp emp = new QEmp("e");

                 List<Tuple> rows = queryFactory.select(emp.all())

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

                 return rows;

        }

}

 

SQLQueryFactory 빈으로 등록해 놓고 DI 받아서 사용하면 메소드 코드가 간결해져서 관리성이 증대됩니다.

 

SelectEmpDaoTest2.java

package com.example.employee.dao;

 

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.gen.model.SEmp;

import com.querydsl.core.Tuple;

 

@Transactional

@RunWith(SpringRunner.class)

@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)

public class SelectEmpDaoTest2 {

        @Autowired

        private SelectEmpDao2 empDao;

       

        @Test

        public void testGetEmpUsingSQLQueryFactory() {

                 QEmp emp = new QEmp("e");

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

                 for (Tuple row : rows) {

                         System.out.print(row.get(emp.empno)+",");

                         System.out.print(row.get(emp.ename)+",");

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

                 }

                

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

        }

}

 

 

3. 쿼리 작성용 Methods

SQLQuery 클래스의 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.2.2. Insert, Update, Delete

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

1. 기본 사용법

EmpDto.java

package com.example.employee.dto;

 

import lombok.Data;

 

@Data

public class EmpDto {

        private int empno;

        private String ename;

        private String job;

        private int deptno;

}

 

InsertEmpDao.java

package com.example.employee.dao;

 

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

import org.springframework.stereotype.Repository;

import com.example.employee.dto.EmpDto;

import com.example.gen.model.SDept;

import com.example.gen.model.SEmp;

import com.querydsl.core.Tuple;

import com.querydsl.sql.SQLExpressions;

import com.querydsl.sql.SQLQueryFactory;

 

@Repository

public class InsertEmpDao {

        @Autowired

        private SQLQueryFactory queryFactory;

       

//      select count(*) from emp

        public long count(){

                 QEmp emp = QEmp.emp;

                 return queryFactory.select(emp.empno).from(emp).fetchCount();

        }

       

//      select

//               emp.comm, emp.deptno, emp.empno,

//               emp.ename, emp.hiredate, emp.job,

//               emp.mgr, emp.sal

//      from emp emp

//      where emp.empno = 7369

//      limit 2
//      2
개를 구해서 만약 2개라면 예외를 발생시킨다.

        public Tuple getEmpByEmpno(int empno){

                 QEmp emp = QEmp.emp;

                 Tuple row = queryFactory.select(emp.all()).from(emp)

                                                .where(emp.empno.eq(empno)).fetchOne();

                 return row;

        }

 

//      insert into emp (empno, ename, job, deptno)

//               values (4001, 'CHRIS', 'GUIDE',

//                              (select max(dept.deptno) from dept dept))

        public long insert(EmpDto empDto){

                 QEmp emp = QEmp.emp;

                 QDept dept = QDept.dept;

                 queryFactory.insert(emp)

                         .columns(emp.empno, emp.ename,

                                          emp.job, emp.deptno)

                         .values(empDto.getEmpno(), empDto.getEname(),

                                          empDto.getJob(),
                                        SQLExpressions.select(dept.deptno.max()).from(dept)).execute();

                 return 1;

        }

 

//      insert into emp (empno, ename, job, deptno)

//               values (4001, 'CHRIS', 'GUIDE',

//                              (select max(dept.deptno) from dept dept))

        public long insert2(EmpDto empDto){

                 SEmp emp = SEmp.emp;

                 SDept dept = SDept.dept;

                 queryFactory.insert(emp)

                         .set(emp.empno, empDto.getEmpno())

                         .set(emp.ename, empDto.getEname())

                         .set(emp.job, empDto.getJob())

                         .set(emp.deptno, SQLExpressions.select(dept.deptno.max()).from(dept))

                         .execute();

                 return 1;

        }

}


InsertEmpDaoTest.java

package com.example.employee.dao;

 

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.dto.EmpDto;

import com.example.gen.model.SEmp;

import com.querydsl.core.Tuple;

 

@Transactional

@RunWith(SpringRunner.class)

@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)

public class InsertEmpDaoTest {

        @Autowired

        private InsertEmpDao empDao;

       

        @Test

        public void testCount() {

                 long count = empDao.count();

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

                

                 assertThat(count, is(14L));

        }

       

        @Test

        public void testGetEmpByEmpno() {

                 SEmp emp = SEmp.emp;

                 Tuple row = empDao.getEmpByEmpno(7369);

                

                 assertThat(row.get(emp.ename), is("SMITH"));

        }

       

        @Test

        public void testInsert() {

                 SEmp emp = SEmp.emp;

                 long count = empDao.count();

                

                 assertThat(count, is(14L));

                

                 EmpDto empDto = new EmpDto();

                 empDto.setEmpno(4001);

                 empDto.setEname("CHRIS");

                 empDto.setJob("GUIDE");

                

                 empDao.insert(empDto);

                

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

                

                 Tuple row = empDao.getEmpByEmpno(4001);

                

                 assertThat(row.get(emp.deptno), is(40));

        }

 

        @Test

        public void testInsert2() {

                 SEmp emp = SEmp.emp;

                 long count = empDao.count();

                

                 assertThat(count, is(14L));

                

                 EmpDto empDto = new EmpDto();

                 empDto.setEmpno(4001);

                 empDto.setEname("CHRIS");

                 empDto.setJob("GUIDE");

                

                 empDao.insert2(empDto);

                

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

                

                 Tuple row = empDao.getEmpByEmpno(4001);

                

                 assertThat(row.get(emp.deptno), is(40));

        }

}


UpdateEmpDao.java

package com.example.employee.dao;

 

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

import org.springframework.stereotype.Repository;

 

import com.example.employee.dto.EmpDto;

import com.example.gen.model.SDept;

import com.example.gen.model.SEmp;

import com.querydsl.core.Tuple;

import com.querydsl.sql.SQLExpressions;

import com.querydsl.sql.SQLQueryFactory;

 

@Repository

public class UpdateEmpDao {

        @Autowired

        private SQLQueryFactory queryFactory;

       

        public long count(){

                 SEmp emp = SEmp.emp;

                 return queryFactory.select(emp.empno).from(emp).fetchCount();

        }

       

        public Tuple getEmpByEmpno(int empno){

                 SEmp emp = SEmp.emp;

                 Tuple row = queryFactory.select(emp.all()).from(emp)

                        .where(emp.empno.eq(empno)).fetchOne();

                 return row;

        }

       

        public long insert(EmpDto empDto){

                 SEmp emp = SEmp.emp;

                 SDept dept = SDept.dept;

                 long affected = queryFactory.insert(emp)

                         .set(emp.empno, empDto.getEmpno())

                         .set(emp.ename, empDto.getEname())

                         .set(emp.job, empDto.getJob())

                         .set(emp.deptno, SQLExpressions.select(dept.deptno.max()).from(dept))

                         .execute();

                 return affected;

        }

       

//      update emp

//      set ename = 'SONG', job = 'CURATOR'

//      where emp.empno = 4001

        public long update(EmpDto empDto){

                 SEmp emp = SEmp.emp;

                 long affected = queryFactory.update(emp)

                         .where(emp.empno.eq(empDto.getEmpno()))

                         .set(emp.ename, empDto.getEname())

                         .set(emp.job, empDto.getJob())

                         .execute();

                 return affected;

        }

       

//      delete from emp

//      where emp.empno = 7369

        public long delete(int empno){

                 SEmp emp = SEmp.emp;

                 long affected = queryFactory.delete(emp)

                         .where(emp.empno.eq(empno))

                         .execute();

                 return affected;

        }

}

 

UpdateEmpDaoTest.java

package com.example.employee.dao;

 

import static org.hamcrest.CoreMatchers.is;

import static org.junit.Assert.*;

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;

import com.example.gen.model.SEmp;

import com.querydsl.core.Tuple;

 

@Transactional

@RunWith(SpringRunner.class)

@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)

public class UpdateEmpDaoTest {

        @Autowired

        private UpdateEmpDao empDao;

       

        @Test

        public void testUpdate() {

                 SEmp emp = SEmp.emp;

                 EmpDto empDto = new EmpDto();

                 empDto.setEmpno(4001);

                 empDto.setEname("CHRIS");

                 empDto.setJob("GUIDE");

                

                 empDao.insert(empDto);

                

                 empDto.setEname("SONG");

                 empDto.setJob("CURATOR");

                

                 empDao.update(empDto);

                

                 Tuple row = empDao.getEmpByEmpno(4001);

                 assertThat(row.get(emp.ename), is("SONG"));

                 assertThat(row.get(emp.job), is("CURATOR"));

        }

 

        @Test

        public void testDelete() {

                 assertThat(empDao.count(), is(14L));

                 empDao.delete(7369);

                 assertThat(empDao.count(), is(13L));

        }

}

 

2. DML 쿼리 실습

프로젝트 이름: chapter5-2-2-2-querydsl-sql-query-with-mysql

 

NativeSqlCrudRepository.java

package com.example.employee.repository;

 

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

import org.springframework.stereotype.Repository;

import com.example.employee.model.QDept;

import com.example.employee.model.QEmp;

import com.querydsl.sql.SQLQueryFactory;

import com.querydsl.sql.dml.SQLDeleteClause;

import com.querydsl.sql.dml.SQLInsertClause;

import com.querydsl.sql.dml.SQLUpdateClause;

 

@Repository

public class NativeSqlCrudRepository {

        @Autowired

        SQLQueryFactory queryFactory;

 

        QEmp emp = new QEmp("emp");

        QDept dept = new QDept("dept");

       

//      insert into emp (empno, ename, job, sal, deptno)

//      values (100, '홍길동', '교수', 5000, 40)

        public long insert(){

                 long affected = queryFactory.insert(emp)

                                 .columns(emp.empno, emp.ename, emp.job, emp.sal, emp.deptno)

                                 .values(100, "홍길동", "교수", 5000, 40)

                                 .execute();

                 return affected;

        }

       

//      update emp

//      set sal = 9999.0

//      where emp.ename = '홍길동'

        public long update(){

                 insert();

                 long affected = queryFactory.update(emp)

                                 .where(emp.ename.eq("홍길동"))

                                 .set(emp.sal, 9999D)

                                 .execute();

                 return affected;

        }

       

//      delete from emp

//      where emp.ename = '홍길동'

        public long delete(){

                 insert();

                 long affected = queryFactory.delete(emp)

                                 .where(emp.ename.eq("홍길동"))

                                 .execute();

                 return affected;

        }

       

//      1:  insert into emp (empno, ename, job, sal, deptno)

//      values (100, '홍길동', '교수', 5000, 40)

//      2:  insert into emp (empno, ename, job, sal, deptno)

//      values (101, '일지매', '교수', 7000, 40)

//     

//      affected = 2

        public long insertBatch(){

                 SQLInsertClause myInsert = queryFactory.insert(emp);

                 myInsert.columns(emp.empno, emp.ename, emp.job, emp.sal, emp.deptno)

                         .values(100, "홍길동", "교수", 5000, 40).addBatch();

                 myInsert.columns(emp.empno, emp.ename, emp.job, emp.sal, emp.deptno)

                         .values(101, "일지매", "교수", 7000, 40).addBatch();

                 long affected = myInsert.execute();

                 return affected;

        }

       

//      1:  update emp

//      set sal = 6000.0

//      where emp.empno = 100

//      2:  update emp

//      set sal = 8000.0

//      where emp.empno = 101

//     

//      affected = 2

        public long updateBatch(){

                 insertBatch();

                 SQLUpdateClause myUpdate = queryFactory.update(emp);

                 myUpdate.set(emp.sal, 6000D).where(emp.empno.eq(100)).addBatch();

                 myUpdate.set(emp.sal, 8000D).where(emp.empno.eq(101)).addBatch();

                 long affected = myUpdate.execute();

                 return affected;

        }

       

//      1:  delete from emp

//      where emp.ename = '홍길동'

//      2:  delete from emp

//      where emp.ename = '일지매'

//     

//      affected = 2

        public long deleteBatch(){

                 insertBatch();

                 SQLDeleteClause myDelete = queryFactory.delete(emp);

                 myDelete.where(emp.ename.eq("홍길동")).addBatch();

                 myDelete.where(emp.ename.eq("일지매")).addBatch();

                 long affected = myDelete.execute();

                 return affected;

        }

}

 

NativeSqlCrudRepositoryTest.java

package com.example.employee.repository;

 

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;

 

@Transactional

@RunWith(SpringRunner.class)

@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)

public class NativeSqlCrudRepositoryTest {

        @Autowired

        private NativeSqlCrudRepository repo;

 

        @Test

        public void testInsert() {

                 long affected = repo.insert();

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

        }

 

        @Test

        public void testUpdate() {

                 long affected = repo.update();

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

        }

 

        @Test

        public void testDelete() {

                 long affected = repo.delete();

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

        }

 

        @Test

        public void testInsertBatch() {

                 long affected = repo.insertBatch();

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

        }

 

        @Test

        public void testUpdateBatch() {

                 long affected = repo.updateBatch();

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

        }

 

        @Test

        public void testDeleteBatch() {

                 long affected = repo.deleteBatch();

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

        }

}

 


5.2.3. Grouping, Having

 

GroupEmpDao.java

package com.example.employee.dao;

 

import java.util.List;

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

import org.springframework.stereotype.Repository;

import com.example.gen.model.SEmp;

import com.querydsl.core.Tuple;

import com.querydsl.sql.SQLExpressions;

import com.querydsl.sql.SQLQueryFactory;

 

@Repository

public class GroupEmpDao {

        @Autowired

        private SQLQueryFactory queryFactory;

 

//      select

//               e.deptno, avg(e.sal), max(e.sal), min(e.sal)

//      from emp e

//      group by e.deptno

//      having avg(e.sal) > (select avg(e.sal) from emp e)

        public List<Tuple> getEmpGroup() {

                 SEmp emp = new SEmp("e");

                 List<Tuple> rows = queryFactory

                                 .select(emp.deptno,

                                                  emp.sal.avg(),

                                                  emp.sal.max(),

                                                  emp.sal.min())

                                 .from(emp)

                                 .groupBy(emp.deptno)

                                 .having(emp.sal.avg().gt(

                                                  SQLExpressions.select(emp.sal.avg()).from(emp)))

                                 .fetch();

                 return rows;

        }

}

 


GroupEmpDaoTest.java

package com.example.employee.dao;

 

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 testGetEmpGroup() {

                 for (Tuple row : empDao.getEmpGroup()) {

                         System.out.println(row);

                 }

        }

}


5.2.4. Join

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

 

JoinEmpDao.java

package com.example.employee.dao;

 

import java.util.List;

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

import org.springframework.stereotype.Repository;

import com.example.gen.model.SDept;

import com.example.gen.model.SEmp;

import com.querydsl.core.Tuple;

import com.querydsl.core.types.SubQueryExpression;

import com.querydsl.sql.SQLQueryFactory;

 

@Repository

public class JoinEmpDao {

        @Autowired

        private SQLQueryFactory queryFactory;

 

//      select

//               emp.empno, emp.ename, dept.deptno, dept.dname

//      from emp emp, dept dept

//      where emp.deptno = dept.deptno

        public List<Tuple> getEmpDeptUsingJoin1() {

                 SEmp emp = SEmp.emp;

                 SDept dept = SDept.dept;

                 List<Tuple> rows = queryFactory

                                 .select(emp.empno, emp.ename,

                                                  dept.deptno, dept.dname)

                                 .from(emp, dept)

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

                                 .fetch();

                 return rows;

        }

}


JoinEmpDaoTest.java

package com.example.employee.dao;

 

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 JoinEmpDaoTest {

        @Autowired

        private JoinEmpDao empDao;

       

        @Test

        public void testGetEmpDeptUsingJoin1() {

                 for (Tuple row : empDao.getEmpDeptUsingJoin1()) {

                         System.out.println(row);

                 }

        }

}

 

 

5.2.5. Subquery

Projection 영역이나 where 조건절 order by 등에서 서브쿼리를 사용할 있으며 인라인뷰 서브쿼리도 가능합니다. SQL 직접 사용하는 것과 마찬가지이므로 모든 서브쿼리가 가능합니다.

 

1. 기본 사용법

SubqueryEmpDao.java

package com.example.employee.dao;

 

import java.util.List;

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

import org.springframework.stereotype.Repository;

import com.example.gen.model.SDept;

import com.example.gen.model.SEmp;

import com.querydsl.core.Tuple;

import com.querydsl.sql.SQLExpressions;

import com.querydsl.sql.SQLQueryFactory;

 

@Repository

public class SubqueryEmpDao {

        @Autowired

        private SQLQueryFactory queryFactory;

 

//      select

//               emp.empno, emp.ename, emp.deptno,

//               (select dept.dname from dept dept where dept.deptno = emp.deptno)

//      from emp emp

//      order by emp.empno asc

        public List<Tuple> getEmpUsingSubquery1() {

                 SEmp emp = SEmp.emp;

                 SDept dept = SDept.dept;

                 List<Tuple> rows = queryFactory

                                 .select(emp.empno, emp.ename, emp.deptno,

                                        SQLExpressions.select(dept.dname).from(dept)
                                        .where(dept.deptno.eq(emp.deptno)))

                                 .from(emp)

                                 .orderBy(emp.empno.asc())

                                 .fetch();

                 return rows;

        }

 

//      select

//               emp.empno, emp.ename, emp.deptno

//      from (

//               select e.comm, e.deptno, e.empno, e.ename, e.hiredate, e.job, e.mgr, e.sal

//               from emp e

//               where e.job = 'CLERK'

//      ) as emp

//      order by emp.empno asc

        public List<Tuple> getEmpUsingSubquery2() {

                 SEmp emp = SEmp.emp;

                 SEmp e = new SEmp("e");

                 // every derived table must have its own alias : as(emp) 앨리어스를 붙여서 해결한다.

                 List<Tuple> rows = queryFactory

                                 .select(emp.empno, emp.ename, emp.deptno)

                                 .from(SQLExpressions.select(e.all()).from(e).where(e.job.eq("CLERK")).as(emp))

                                 .orderBy(emp.empno.asc())

                                 .fetch();

                 return rows;

        }

}

 

SubqueryEmpDaoTest.java

package com.example.employee.dao;

 

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 SubqueryEmpDaoTest {

        @Autowired

        private SubqueryEmpDao empDao;

       

        @Test

        public void testGetEmpUsingSubquery1() {

                 for (Tuple row : empDao.getEmpUsingSubquery1()) {

                         System.out.println(row);

                 }

        }

 

        @Test

        public void testGetEmpUsingSubquery2() {

                 for (Tuple row : empDao.getEmpUsingSubquery2()) {

                         System.out.println(row);

                 }

        }

}

 

 


2. 조인 서브쿼리 실습

프로젝트 이름: chapter5-2-5-2-querydsl-sql-query-with-mysql

NativeSqlExamRepository.java

package com.example.employee.repository;

 

import java.util.List;

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

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.sql.SQLExpressions;

import com.querydsl.sql.SQLQueryFactory;

 

@Repository

public class NativeSqlExamRepository {

        @Autowired

        SQLQueryFactory queryFactory;

 

        QEmp emp = new QEmp("emp");

        QDept dept = new QDept("dept");

 

//      # Emp 테이블에서 모든 사원의 이름을 내림차순으로 출력

//      select emp.ename

//      from emp emp

//      order by emp.ename desc

        public List<String> getEmpsOrderByEnameDesc() {

                 List<String> enames = queryFactory

                                 .select(emp.ename).from(emp)

                                 .orderBy(emp.ename.desc()).fetch();

                 return enames;

        }

       

//      # Emp, Dept 조인하여 사원명, 부서명 출력

//      select emp.ename, dept.dname

//      from dept dept

//      inner join emp emp

//      on emp.deptno = dept.deptno

        public List<Tuple> getEmpDeptUsingJoin(){

                 List<Tuple> emps = queryFactory

                                 .select(emp.ename, dept.dname).from(dept)

                                 .innerJoin(emp).on(emp.deptno.eq(dept.deptno))

                                 .fetch();

                 return emps;

        }

       

//      # Emp, Dept 조인하여 사원명, 부서명 출력, 사원없는 부서도 출력

//      select emp.ename, dept.dname

//      from dept dept

//      left join emp emp

//      on emp.deptno = dept.deptno

        public List<Tuple> getEmpDeptUsingLeftJoin(){

                 List<Tuple> emps = queryFactory

                                 .select(emp.ename, dept.dname).from(dept)

                                 .leftJoin(emp).on(emp.deptno.eq(dept.deptno))

                                 .fetch();

                 return emps;

        }

       

//      # Emp 테이블에서 부서별로 그룹핑하여 부서명, 급여합 출력.

//      급여평균이 2000 이상인 부서만 대상

//      select dept.dname, sum(emp.sal)

//      from emp emp

//      inner join dept dept

//      on emp.deptno = dept.deptno

//      group by emp.deptno

//      having avg(emp.sal) > 2000.0

        public List<Tuple> getEmpGroupByUsingJoin(){

                 List<Tuple> emps = queryFactory

                                 .select(dept.dname, emp.sal.sum()).from(emp)

                                 .innerJoin(dept).on(emp.deptno.eq(dept.deptno))

                                 .groupBy(emp.deptno).having(emp.sal.avg().gt(2000))

                                 .fetch();

                 return emps;

        }

       

//      # Emp 테이블에서 급여가 최소인 사원의 모든 칼럼 추출

//      select

//               emp.comm, emp.deptno, emp.empno, emp.ename,

//               emp.hiredate, emp.job, emp.mgr, emp.sal

//      from emp emp

//      where emp.sal = (select min(e.sal)

//      from emp e)

        public List<Tuple> getEmpOfMinSal(){

                 QEmp e = new QEmp("e");

                 List<Tuple> emps = queryFactory

                                 .select(emp.all()).from(emp)

                    .where(emp.sal.eq(

                                   SQLExpressions.select(e.sal.min()).from(e)))

                    .fetch();

                 return emps;

        }

       

//      # Emp 테이블에서 job 최소급여 사원의 ename, job, sal 출력

//      select emp.ename, emp.job, emp.sal

//      from emp emp

//      where emp.sal = (select min(e.sal)

//      from emp e

//      where emp.job = e.job)

        public List<Tuple> getEmpOfMinSalGroupByJob(){

                 QEmp e = new QEmp("e");

                 List<Tuple> emps = queryFactory

                                 .select(emp.ename, emp.job, emp.sal).from(emp)

                                 .where(emp.sal.eq(

                                                  SQLExpressions.select(e.sal.min()).from(e)

                                                  .where(emp.job.eq(e.job))))

                                 .fetch();

                 return emps;

        }

       

//      # "SCOTT" 사원과 같은 부서에 있는 사원중 최대급여 사원의 이름 급여, 부서명출력

//      select

//               emp.ename, emp.sal, dept.dname

//      from emp emp

//      inner join dept dept

//      on emp.deptno = dept.deptno

//      where emp.deptno = (

//               select e.deptno from emp e

//               where e.ename = 'SCOTT')

//      and emp.sal = (

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

//               where emp.deptno = e.deptno)

        public List<Tuple> getEmpOfMaxSalOfDeptOfEname(){

                 QEmp e = new QEmp("e");

                 List<Tuple> emps = queryFactory

                                 .select(emp.ename, emp.sal, dept.dname)

                                 .from(emp)

                                 .innerJoin(dept)

                                 .on(emp.deptno.eq(dept.deptno))

                                 .where(emp.deptno.eq(

                                                  SQLExpressions.select(e.deptno).from(e)

                                                  .where(e.ename.eq("SCOTT")))

                                 .and(emp.sal.eq(

                                                  SQLExpressions.select(e.sal.max()).from(e)

                                                  .where(emp.deptno.eq(e.deptno)))))

                                 .fetch();

                 return emps;

        }

}

 

NativeSqlExamRepositoryTest.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 NativeSqlExamRepositoryTest {

        @Autowired

        private NativeSqlExamRepository repo;

 

        QEmp emp = QEmp.emp;

        QDept dept = QDept.dept;

 

        @Test

        public void testGetEmpsOrderByEnameDesc() {

                 List<String> enames = repo.getEmpsOrderByEnameDesc();

                 for (String ename : enames) {

                         System.out.println(ename);

                 }

        }

 

        @Test

        public void testGetEmpDeptUsingJoin() {

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

                 for (Tuple row : rows) {

                         System.out.println(row.get(emp.ename) + ":" + row.get(dept.dname));

                 }

        }

 

        @Test

        public void testGetEmpDeptUsingLeftJoin() {

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

                 for (Tuple row : rows) {

                         System.out.println(row.get(emp.ename) + ":" + row.get(dept.dname));

                 }

        }

 

        @Test

        public void testGetEmpGroupByUsingJoin() {

                 List<Tuple> rows = repo.getEmpGroupByUsingJoin();

                 for (Tuple row : rows) {

                         System.out.println(row.get(dept.dname) + ":" + row.get(1, Long.class));

                 }

        }

 

        @Test

        public void testGetEmpOfMinSal() {

                 List<Tuple> rows = repo.getEmpOfMinSal();

                 for (Tuple row : rows) {

                         System.out.println(row.get(emp.ename) + ":" + row.get(emp.sal));

                 }

        }

 

        @Test

        public void testGetEmpOfMinSalGroupByJob() {

                 List<Tuple> rows = repo.getEmpOfMinSalGroupByJob();

                 for (Tuple row : rows) {

                         System.out.println(row.get(emp.job) + ":" + row.get(emp.ename) + ":" +

                                row.get(emp.sal));

                 }

        }

 

        @Test

        public void testGetEmpOfMaxSalOfDeptOfEname() {

                 List<Tuple> rows = repo.getEmpOfMaxSalOfDeptOfEname();

                 for (Tuple row : rows) {

                         System.out.println(row.get(emp.ename) + ":" + row.get(emp.sal) + ":" +

                                row.get(dept.dname));

                 }

        }

}

 

 

5.2.6. Projection

 

ProjectionEmpDao.java

package com.example.employee.dao;

 

import java.util.List;

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

import org.springframework.stereotype.Repository;

import com.example.employee.dto.EmpDto;

import com.example.gen.model.SEmp;

import com.querydsl.core.types.Projections;

import com.querydsl.sql.SQLQueryFactory;

 

@Repository

public class ProjectionEmpDao {

        @Autowired

        private SQLQueryFactory queryFactory;

       

        public List<EmpDto> getEmpDto() {

                 SEmp emp = SEmp.emp;

                 List<EmpDto> rows = queryFactory

                                 .select(Projections.bean(EmpDto.class,

                                                  emp.empno, emp.ename, emp.job, emp.deptno))

                                 .from(emp)

                                 .fetch();

                 return rows;

        }

}

 

ProjectionEmpDaoTest.java

package com.example.employee.dao;

 

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 testGetEmpDto() {

                 for (EmpDto emp : empDao.getEmpDto()) {

                         System.out.println(emp);

                 }

        }

}

   ​ 

Comments