(자바스프링JPA교육, Querydsl교육, JPA교육)Querydsl을 이용한 오라클SQL 쿼리, 조인, 서브쿼리 실습, 스프링교육동영상
https://www.youtube.com/watch?v=RdIPh1iGm5A&list=PLxU-iZCqT52AlV-Y7UlwSQp8N6pMjijFX&index=7
Querydsl을 이용한 SQL 쿼리, 조인, 서브쿼리 실습, SQLQueryFactory, Select, innerJoin, join, leftJoin, fullJoin, on, where, having, orderBy, limit, offset, restrict, Insert, Update, Delete,
ojc.asia
Querydsl을 이용한 SQL 쿼리, 조인, 서부쿼리 실습, SQLQueryFactory, Select, innerJoin, join, leftJoin, fullJoin, on, where, having, orderBy, limit, offset, restrict, Insert, Update, Delete,5장. Querydsl을 이용한 SQL 쿼리5.1…
ojc.asia
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 메소드는 다음과 같습니다.
- 0열 선택0열 다음에 열 추가
- 1열 선택1열 다음에 열 추가
- 0행 선택0행 다음에 행 추가
- 1행 선택1행 다음에 행 추가
- 2행 선택2행 다음에 행 추가
- 3행 선택3행 다음에 행 추가
- 4행 선택4행 다음에 행 추가
- 5행 선택5행 다음에 행 추가
- 6행 선택6행 다음에 행 추가
- 7행 선택7행 다음에 행 추가
메소드 | 기능 |
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);
}
}
}
#JPA교육, #Querydsl교육, #JPA동영상, #스프링교육, #스프링프레임워크, #Querydsl, #오라클SQL, #Querydsl조인, #Querydsl서브쿼리, #SQLQueryFactory, #JPA동영상,JPA교육, Querydsl교육, JPA동영상, 스프링교육, 스프링프레임워크, Querydsl, 오라클SQL, Querydsl조인, Querydsl서브쿼리, SQLQueryFactory, JPA동영상
댓글 없음:
댓글 쓰기