Querydsl JPA Query with MySQL, JPAQueryFactory 빈 설정, Querydsl JPA Query with Oracle, Querydsl SQL Query with MySQL, Querydsl SQL Query with Oracle
7. Scaffolding Project
새로운 기술을 습득하고 이를 실무에 반영하기 위해서는 많은 테스트가 필요합니다. 대표적인 데이터베이스인 Oracle, MySQL과 연동되는 프로젝트를 미리 만들어 놓고 테스트가 필요할 때 간단히 프로젝트를 복사해서 이용하면 매우 편리합니다. 테스트를 위한 템플릿 프로젝트를 만들어 봅니다.
7.1 Querydsl JPA Query with MySQL
1. 프로젝트 생성
File > New > Spring Starter Project >
프로젝트 명: scaffolding-querydsl-jpa-query-with-mysql > Next >
디펜던시 선택: Lombok, JPA, MySQL, Web > Finish
2. 프로젝트 환경 설정application.properties
# DATASOURCE
spring.datasource.platform=mariadb
spring.datasource.sqlScriptEncoding=UTF-8
spring.datasource.url=jdbc:log4jdbc:mysql://localhost:3306/testdb?createDatabaseIfNotExist=true
spring.datasource.username=root
spring.datasource.password=1111
spring.datasource.driver-class-name=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
spring.datasource.initialize=false
# JPA
spring.jpa.hibernate.ddl-auto=none
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.show-sql=true
spring.data.jpa.repositories.enabled=true
# Logging
logging.config=classpath:logback-spring.xml
3. 프로젝트 로깅 설정pom.xml
net.sf.log4jdbc.sql.jdbcapi.DriverSpy 디펜던시 추가
<dependency>
<groupId>org.bgee.log4jdbc-log4j2</groupId>
<artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
<version>1.16</version>
</dependency>
logback-spring.xml
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
<!-- By default, encoders are assigned the type
ch.qos.logback.classic.encoder.PatternLayoutEncoder -->
<encoder>
<pattern>
%d{yyyyMMdd HH:mm:ss.SSS} [%thread] %-3level %logger{5} -%msg %n
</pattern>
</encoder>
</appender>
<logger name="jdbc" level="OFF" />
<logger name="jdbc.sqlonly" level="DEBUG" additivity="false">
<appender-ref ref="STDOUT" />
</logger>
<logger name="jdbc.resultsettable" level="DEBUG" additivity="false">
<appender-ref ref="STDOUT" />
</logger>
<include resource="org/springframework/boot/logging/logback/base.xml"/>
<appender name="dailyRollingFileAppender"
class="ch.qos.logback.core.rolling.RollingFileAppender">
<prudent>true</prudent>
<rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
<fileNamePattern>applicatoin.%d{yyyy-MM-dd}.log</fileNamePattern>
<maxHistory>30</maxHistory>
</rollingPolicy>
<filter class="ch.qos.logback.classic.filter.ThresholdFilter">
<level>INFO</level>
</filter>
<encoder>
<pattern>
%d{yyyy:MM:dd HH:mm:ss.SSS} %-5level --- [%thread] %logger{35} : %msg %n
</pattern>
</encoder>
</appender>
<logger name="org.springframework.web" level="INFO"/>
<logger name="org.thymeleaf" level="INFO"/>
<logger name="org.hibernate.SQL" level="INFO"/>
<logger name="org.quartz.core" level="INFO"/>
<logger name="org.h2.server.web" level="INFO"/>
<root level="INFO">
<appender-ref ref="STDOUT" />
<appender-ref ref="dailyRollingFileAppender" />
</root>
</configuration>
log4jdbc.log4j2.properties
log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator
# multi-line query display
log4jdbc.dump.sql.maxlinelength=0
4. 엔티티 클래스 생성Dept.java
package com.example.employee.model;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.Data;
import lombok.ToString;
@Data
@ToString(exclude={"emps"})
@Entity
public class Dept {
@Id
private Long deptno;
@Column(length = 14, nullable = false)
private String dname;
@Column(length = 13)
private String loc;
@OneToMany(mappedBy="dept")
@JsonIgnore
private List<Emp> emps = new ArrayList<Emp>();
public void addEmp(Emp emp){
this.emps.add(emp);
if (emp.getDept() != this) {
emp.setDept(this);
}
}
}
Emp.java
package com.example.employee.model;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.Data;
import lombok.ToString;
@Data
@ToString(exclude={"staff"})
@Entity
public class Emp {
@Id
private Long empno;
@Column(length = 10, nullable = false)
private String ename;
@Column(length = 9)
private String job;
@ManyToOne
@JoinColumn(name = "mgr")
private Emp mgr;
@OneToMany(mappedBy = "mgr")
@JsonIgnore
private List<Emp> staff = new ArrayList<Emp>();
@Temporal(TemporalType.DATE)
private Date hiredate;
private Double sal;
private Double comm;
@ManyToOne
@JoinColumn(name = "deptno")
private Dept dept;
public void setDept(Dept dept){
this.dept = dept;
if (!dept.getEmps().contains(this)) {
dept.getEmps().add(this);
}
}
}
5. 테스트 데이터 입력 처리data.sql
INSERT IGNORE INTO DEPT(deptno, dname, loc) VALUES (10,'ACCOUNTING','NEW YORK');
INSERT IGNORE INTO DEPT(deptno, dname, loc) VALUES (20,'RESEARCH','DALLAS');
INSERT IGNORE INTO DEPT(deptno, dname, loc) VALUES (30,'SALES','CHICAGO');
INSERT IGNORE INTO DEPT(deptno, dname, loc) VALUES (40,'OPERATIONS','BOSTON');
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7839,'KING','PRESIDENT', NULL,STR_TO_DATE('17-11-1981','%d-%m-%Y'),5000,NULL,10);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7566,'JONES','MANAGER', 7839,STR_TO_DATE('2-4-1981', '%d-%m-%Y'),2975,NULL,20);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7698,'BLAKE','MANAGER', 7839,STR_TO_DATE('1-5-1981', '%d-%m-%Y'),2850,NULL,30);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7782,'CLARK','MANAGER', 7839,STR_TO_DATE('9-6-1981', '%d-%m-%Y'),2450,NULL,10);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7902,'FORD','ANALYST', 7566,STR_TO_DATE('3-12-1981', '%d-%m-%Y'),3000,NULL,20);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7788,'SCOTT','ANALYST', 7566,STR_TO_DATE('13-07-1987', '%d-%m-%Y'),3000,NULL,20);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7499,'ALLEN','SALESMAN', 7698,STR_TO_DATE('20-2-1981', '%d-%m-%Y'),1600,300,30);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7521,'WARD','SALESMAN', 7698,STR_TO_DATE('22-2-1981', '%d-%m-%Y'),1250,500,30);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7654,'MARTIN','SALESMAN',7698,STR_TO_DATE('28-9-1981', '%d-%m-%Y'),1250,1400,30);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7844,'TURNER','SALESMAN',7698,STR_TO_DATE('8-9-1981', '%d-%m-%Y'),1500,0,30);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7900,'JAMES','CLERK', 7698,STR_TO_DATE('3-12-1981', '%d-%m-%Y'),950,NULL,30);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7934,'MILLER','CLERK', 7782,STR_TO_DATE('23-1-1982', '%d-%m-%Y'),1300,NULL,10);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7369,'SMITH','CLERK', 7902,STR_TO_DATE('17-12-1980','%d-%m-%Y'),800,NULL,20);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7876,'ADAMS','CLERK', 7788,STR_TO_DATE('13-07-1987', '%d-%m-%Y'),1100,NULL,20);
6. Q 타입 클래스 생성을 위한 디펜던시 설정
Q 타입 클래스 생성을 위한 디펜던시 "querydsl-apt"를 추가하고 "apt-maven-plugin" 플러그인을 설정합니다. Spring Boot로 프로젝트를 만들 경우 slf4j-log4j12 디펜던시 선언은 생략이 가능합니다.
pom.xml
<dependencies>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-apt</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-jpa</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>com.mysema.maven</groupId>
<artifactId>apt-maven-plugin</artifactId>
<version>1.1.3</version>
<executions>
<execution>
<goals>
<goal>process</goal>
</goals>
<configuration>
<outputDirectory>
target/generated-sources/java
</outputDirectory>
<processor>
com.querydsl.apt.jpa.JPAAnnotationProcessor
</processor>
</configuration>
</execution>
</executions>
</plugin>
</plugins>
</build>
7. Q 타입 클래스 생성
만약 작업 중 프로젝트에 빨간 x 표시가 보이면 다음 작업을 수행합니다.
프로젝트 선택 > 마우스 오른쪽 클릭 > Maven > Update Project… > 대상 프로젝트 확인 > OK
Q 타입 클래스를 생성하기 위해서 다음 작업을 수행합니다.
프로젝트 선택 > 마우스 오른쪽 클릭 > Run AS > Maven generate-sources
소스 폴더로 등록
target/generated-sources/java 폴더 선택 > 마우스 오른쪽 클릭 >
Build Path > Use as Source Folder
작업결과 프로젝트 구조
프로젝트 선택 > 새로고침 > target/generated-sources/java 폴더 확인
Q 타입 클래스 생성작업을 진행할 때 데이터베이스와 연결하는 동작은 필요하지 않습니다. 프로젝트 내의 엔티티 클래스를 찾아서 Q 타입 클래스를 생성합니다.
따라서 application.properties 파일의 데이터베이스 연결 설정은 사용되지 않습니다. 다만 나중에 데이터베이스 처리 로직을 학습할 때 필요하므로 미리 설정했다고 보면 됩니다.
8. JPAQueryFactory 빈 설정
JPAQueryFactory 를 빈으로 등록해 놓고 필요할 때 DI받아서 사용하면 편리합니다.
QuerydslJpaQueryConfig.java
package com.example.common.config;
import javax.persistence.EntityManager;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import com.querydsl.jpa.impl.JPAQueryFactory;
@Configuration
public class QuerydslJpaQueryConfig {
@Bean
public JPAQueryFactory queryFactory(EntityManager em) {
return new JPAQueryFactory(em);
}
}
7.2 Querydsl JPA Query with Oracle
1. 프로젝트 생성
File > New > Spring Starter Project >
프로젝트 명: scaffolding-querydsl-jpa-query-with-oracle > Next >
디펜던시 선택: Lombok, JPA, Web > Finish
2. 오라클 데이터베이스 연결 드라이버 디펜던시 설정 추가pom.xml
<dependencies>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.3</version>
</dependency>
</dependencies>
<repositories>
<repository>
<id>oracle</id>
<name>ORACLE JDBC Repository</name>
<url>https://maven.oracle.com;
</repository>
</repositories>
3. 프로젝트 환경 설정application.properties
# DATASOURCE
spring.datasource.platform=oracle
spring.datasource.sqlScriptEncoding=UTF-8
spring.datasource.url=jdbc:log4jdbc:oracle:thin:@192.168.0.225:1521:orcl
spring.datasource.username=scott
spring.datasource.password=1234
spring.datasource.driver-class-name=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
spring.datasource.initialize=false
# JPA
spring.jpa.hibernate.ddl-auto=none
spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect
spring.jpa.show-sql=true
spring.data.jpa.repositories.enabled=true
# Logging
logging.config=classpath:logback-spring.xml
4. 프로젝트 로깅 설정pom.xml
net.sf.log4jdbc.sql.jdbcapi.DriverSpy 디펜던시 추가
<dependency>
<groupId>org.bgee.log4jdbc-log4j2</groupId>
<artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
<version>1.16</version>
</dependency>
logback-spring.xml
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
<!-- By default, encoders are assigned the type
ch.qos.logback.classic.encoder.PatternLayoutEncoder -->
<encoder>
<pattern>
%d{yyyyMMdd HH:mm:ss.SSS} [%thread] %-3level %logger{5} -%msg %n
</pattern>
</encoder>
</appender>
<logger name="jdbc" level="OFF" />
<logger name="jdbc.sqlonly" level="DEBUG" additivity="false">
<appender-ref ref="STDOUT" />
</logger>
<logger name="jdbc.resultsettable" level="DEBUG" additivity="false">
<appender-ref ref="STDOUT" />
</logger>
<include resource="org/springframework/boot/logging/logback/base.xml"/>
<appender name="dailyRollingFileAppender"
class="ch.qos.logback.core.rolling.RollingFileAppender">
<prudent>true</prudent>
<rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
<fileNamePattern>applicatoin.%d{yyyy-MM-dd}.log</fileNamePattern>
<maxHistory>30</maxHistory>
</rollingPolicy>
<filter class="ch.qos.logback.classic.filter.ThresholdFilter">
<level>INFO</level>
</filter>
<encoder>
<pattern>
%d{yyyy:MM:dd HH:mm:ss.SSS} %-5level --- [%thread] %logger{35} : %msg %n
</pattern>
</encoder>
</appender>
<logger name="org.springframework.web" level="INFO"/>
<logger name="org.thymeleaf" level="INFO"/>
<logger name="org.hibernate.SQL" level="INFO"/>
<logger name="org.quartz.core" level="INFO"/>
<logger name="org.h2.server.web" level="INFO"/>
<root level="INFO">
<appender-ref ref="STDOUT" />
<appender-ref ref="dailyRollingFileAppender" />
</root>
</configuration>
log4jdbc.log4j2.properties
log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator
# multi-line query display
log4jdbc.dump.sql.maxlinelength=0
로깅 설정은 부록 7.1과 동일합니다. 다만 나중 편의를 위하여 조금 반복작업을 수행한다고 볼 수 있습니다.
5. 엔티티 클래스 생성Dept.java
package com.example.employee.model;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.Data;
import lombok.ToString;
@Data
@ToString(exclude={"emps"})
@Entity
public class Dept {
@Id
private Long deptno;
@Column(length = 14, nullable = false)
private String dname;
@Column(length = 13)
private String loc;
@OneToMany(mappedBy="dept")
@JsonIgnore
private List<Emp> emps = new ArrayList<Emp>();
public void addEmp(Emp emp){
this.emps.add(emp);
if (emp.getDept() != this) {
emp.setDept(this);
}
}
}
Emp.java
package com.example.employee.model;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.Data;
import lombok.ToString;
@Data
@ToString(exclude={"staff"})
@Entity
public class Emp {
@Id
private Long empno;
@Column(length = 10, nullable = false)
private String ename;
@Column(length = 9)
private String job;
@ManyToOne
@JoinColumn(name = "mgr")
private Emp mgr;
@OneToMany(mappedBy = "mgr")
@JsonIgnore
private List<Emp> staff = new ArrayList<Emp>();
@Temporal(TemporalType.DATE)
private Date hiredate;
private Double sal;
private Double comm;
@ManyToOne
@JoinColumn(name = "deptno")
private Dept dept;
public void setDept(Dept dept){
this.dept = dept;
if (!dept.getEmps().contains(this)) {
dept.getEmps().add(this);
}
}
}
엔티티 클래스의 내용은 부록 7.1에서 사용한 엔티티 클래스와 같습니다.
6. Q 타입 클래스 생성을 위한 디펜던시 설정pom.xml
<dependencies>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-apt</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-jpa</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>com.mysema.maven</groupId>
<artifactId>apt-maven-plugin</artifactId>
<version>1.1.3</version>
<executions>
<execution>
<goals>
<goal>process</goal>
</goals>
<configuration>
<outputDirectory>
target/generated-sources/java
</outputDirectory>
<processor>
com.querydsl.apt.jpa.JPAAnnotationProcessor
</processor>
</configuration>
</execution>
</executions>
</plugin>
</plugins>
</build>
7. Q 타입 클래스 생성
만약 작업 중 프로젝트에 빨간 x 표시가 보이면 다음 작업을 수행합니다.
프로젝트 선택 > 마우스 오른쪽 클릭 > Maven > Update Project… > 대상 프로젝트 확인 > OK
Q 타입 클래스를 생성하기 위해서 다음 작업을 수행합니다.
프로젝트 선택 > 마우스 오른쪽 클릭 > Run AS > Maven generate-sources
작업결과 프로젝트 구조
프로젝트 선택 > 새로고침 > target/generated-sources/java 폴더 확인
7.1과 7.2는 많은 부분이 비슷합니다. 사실상 다른 부분은 application.properties 뿐인데 이는 Q 타입 클래스 생성 시 사용되지 않으므로 거의 동일하다고 볼 수 있습니다.
연동 데이터베이스가 오라클인 경우 프로젝트 생성 시 오라클 드라이버 디펜던시를 설정할 수 없으므로 별도 수작업으로 pom.xml에 디펜던시를 추가한 부분이 다른 부분입니다.
8. JPAQueryFactory 빈 설정
JPAQueryFactory 를 빈으로 등록해 놓고 필요할 때 DI받아서 사용하면 편리합니다.
QuerydslJpaQueryConfig.java
package com.example.common.config;
import javax.persistence.EntityManager;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import com.querydsl.jpa.impl.JPAQueryFactory;
@Configuration
public class QuerydslJpaQueryConfig {
@Bean
public JPAQueryFactory queryFactory(EntityManager em) {
return new JPAQueryFactory(em);
}
}
7.3 Querydsl SQL Query with MySQL
1. 프로젝트 생성
File > New > Spring Starter Project >
프로젝트 명: scaffolding-querydsl-sql-query-with-mysql > Next >
디펜던시 선택: Lombok, JPA, MySQL, Web > Finish
2. 프로젝트 환경 설정application.properties
# DATASOURCE
spring.datasource.platform=mariadb
spring.datasource.sqlScriptEncoding=UTF-8
spring.datasource.url=jdbc:log4jdbc:mysql://localhost:3306/test2db?createDatabaseIfNotExist=true
spring.datasource.username=root
spring.datasource.password=1111
spring.datasource.driver-class-name=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
spring.datasource.initialize=false
# JPA
spring.jpa.hibernate.ddl-auto=none
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.show-sql=true
spring.data.jpa.repositories.enabled=true
# Logging
logging.config=classpath:logback-spring.xml
3. 프로젝트 로깅 설정
부록 7.1.3 과 동일합니다.
4. 엔티티 클래스 생성 대신 테이블 생성
Querydsl SQL Query는 프로젝트 내 엔티티 클래스를 만들지 않고 사용합니다. 이미 데이터베이스 내 테이블이 존재할 때 이를 바탕으로 리버스엔지니어링 작업을 수행해서 Q 타입 클래스와 모델 클래스를 생성한 후 사용합니다.
따라서 작업 대상 "test2db" 데이터베이스를 생성하고 필요한 테이블을 생성하는 쿼리와 테스트 데이터를 입력하는 쿼리를 실행합니다.
schema.sql
CREATE TABLE IF NOT EXISTS `dept` (
`deptno` INT(11) NOT NULL,
`dname` VARCHAR(14) NOT NULL,
`loc` VARCHAR(13) NULL DEFAULT NULL,
PRIMARY KEY (`deptno`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `emp` (
`empno` INT(11) NOT NULL,
`comm` DOUBLE NULL DEFAULT NULL,
`ename` VARCHAR(10) NOT NULL,
`hiredate` DATE NULL DEFAULT NULL,
`job` VARCHAR(9) NULL DEFAULT NULL,
`sal` DOUBLE NULL DEFAULT NULL,
`deptno` INT(11) NULL DEFAULT NULL,
`mgr` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`empno`),
INDEX `FKfqt0j25nlvjwt7qt1t3x7v6qf` (`deptno`),
INDEX `FKfehivfm7m674r8qrrnug1of2q` (`mgr`),
CONSTRAINT `FKfehivfm7m674r8qrrnug1of2q` FOREIGN KEY (`mgr`)
REFERENCES `emp` (`empno`),
CONSTRAINT `FKfqt0j25nlvjwt7qt1t3x7v6qf` FOREIGN KEY (`deptno`)
REFERENCES `dept` (`deptno`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
data.sql
INSERT IGNORE INTO DEPT(deptno, dname, loc) VALUES (10,'ACCOUNTING','NEW YORK');
INSERT IGNORE INTO DEPT(deptno, dname, loc) VALUES (20,'RESEARCH','DALLAS');
INSERT IGNORE INTO DEPT(deptno, dname, loc) VALUES (30,'SALES','CHICAGO');
INSERT IGNORE INTO DEPT(deptno, dname, loc) VALUES (40,'OPERATIONS','BOSTON');
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7839,'KING','PRESIDENT', NULL,STR_TO_DATE('17-11-1981','%d-%m-%Y'),5000,NULL,10);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7566,'JONES','MANAGER', 7839,STR_TO_DATE('2-4-1981', '%d-%m-%Y'),2975,NULL,20);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7698,'BLAKE','MANAGER', 7839,STR_TO_DATE('1-5-1981', '%d-%m-%Y'),2850,NULL,30);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7782,'CLARK','MANAGER', 7839,STR_TO_DATE('9-6-1981', '%d-%m-%Y'),2450,NULL,10);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7902,'FORD','ANALYST', 7566,STR_TO_DATE('3-12-1981', '%d-%m-%Y'),3000,NULL,20);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7788,'SCOTT','ANALYST', 7566,STR_TO_DATE('13-07-1987', '%d-%m-%Y'),3000,NULL,20);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7499,'ALLEN','SALESMAN', 7698,STR_TO_DATE('20-2-1981', '%d-%m-%Y'),1600,300,30);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7521,'WARD','SALESMAN', 7698,STR_TO_DATE('22-2-1981', '%d-%m-%Y'),1250,500,30);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7654,'MARTIN','SALESMAN',7698,STR_TO_DATE('28-9-1981', '%d-%m-%Y'),1250,1400,30);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7844,'TURNER','SALESMAN',7698,STR_TO_DATE('8-9-1981', '%d-%m-%Y'),1500,0,30);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7900,'JAMES','CLERK', 7698,STR_TO_DATE('3-12-1981', '%d-%m-%Y'),950,NULL,30);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7934,'MILLER','CLERK', 7782,STR_TO_DATE('23-1-1982', '%d-%m-%Y'),1300,NULL,10);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7369,'SMITH','CLERK', 7902,STR_TO_DATE('17-12-1980','%d-%m-%Y'),800,NULL,20);
INSERT IGNORE INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7876,'ADAMS','CLERK', 7788,STR_TO_DATE('13-07-1987', '%d-%m-%Y'),1100,NULL,20);
5. Q 타입 클래스 생성을 위한 디펜던시 설정pom.xml
<properties>
<querydsl.version>4.1.2</querydsl.version>
</properties>
<dependencies>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-sql</artifactId>
<version>${querydsl.version}</version>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-sql-spring</artifactId>
<version>${querydsl.version}</version>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-sql-codegen</artifactId>
<version>${querydsl.version}</version>
<scope>provided</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-maven-plugin</artifactId>
<version>${querydsl.version}</version>
<executions>
<execution>
<goals>
<goal>export</goal>
</goals>
</execution>
</executions>
<configuration>
<jdbcDriver>com.mysql.jdbc.Driver</jdbcDriver>
<jdbcUrl>jdbc:mysql://localhost:3306/test2db</jdbcUrl>
<jdbcUser>root</jdbcUser>
<jdbcPassword>1111</jdbcPassword>
<packageName>com.example.employee.model</packageName>
<targetFolder>target/generated-sources/java</targetFolder>
<namePrefix>Q</namePrefix>
<exportBeans>true</exportBeans>
<tableNamePattern>DEPT,EMP</tableNamePattern>
</configuration>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
<scope>compile</scope>
</dependency>
</dependencies>
</plugin>
</plugins>
</build>
6. Q 타입 클래스 생성
만약 작업 중 프로젝트에 빨간 x 표시가 보이면 다음 작업을 수행합니다.
프로젝트 선택 > 마우스 오른쪽 클릭 > Maven > Update Project… > 대상 프로젝트 확인 > OK
Q 타입 클래스를 생성하기 위해서 다음 작업을 수행합니다.
프로젝트 선택 > 마우스 오른쪽 클릭 > Run AS > Maven generate-sources
작업 로그
=
작업결과 프로젝트 구조
프로젝트 선택 > 새로고침 > target/generated-sources/java 폴더 확인
7. SQLQueryFactory 빈 설정
SQLQueryFactory를 빈으로 등록해 놓고 필요할 때 DI받아서 사용하면 편리합니다.
QuerydslSqlQueryConfig.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 QuerydslSqlQueryConfig {
@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);
}
}
7.4 Querydsl SQL Query with Oracle
1. 프로젝트 생성
File > New > Spring Starter Project >
프로젝트 명: scaffolding-querydsl-sql-query-with-oracle > Next >
디펜던시 선택: Lombok, JPA, Web > Finish
2. 오라클 데이터베이스 연결 드라이버 디펜던시 설정 추가pom.xml
<dependencies>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.3</version>
</dependency>
</dependencies>
<repositories>
<repository>
<id>oracle</id>
<name>ORACLE JDBC Repository</name>
<url>https://maven.oracle.com;
</repository>
</repositories>
3. 프로젝트 환경 설정application.properties
# DATASOURCE
spring.datasource.platform=oracle
spring.datasource.sqlScriptEncoding=UTF-8
spring.datasource.url=jdbc:log4jdbc:oracle:thin:@192.168.0.225:1521:orcl
spring.datasource.username=scott
spring.datasource.password=1234
spring.datasource.driver-class-name=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
spring.datasource.initialize=false
# JPA
spring.jpa.hibernate.ddl-auto=none
spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect
spring.jpa.show-sql=true
spring.data.jpa.repositories.enabled=true
# Logging
logging.config=classpath:logback-spring.xml
4. 프로젝트 로깅 설정
부록 7.2.4 와 동일합니다.
5. 엔티티 클래스 생성 대신 테이블 생성
따로 테이블을 생성하지 않고 SCOTT 계정으로 이미 존재하는 테이블을 대상으로 학습 해 보겠습니다.
6. Q 타입 클래스 생성을 위한 디펜던시 설정
pom.xml
<properties>
<querydsl.version>4.1.2</querydsl.version>
</properties>
<dependencies>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-sql</artifactId>
<version>${querydsl.version}</version>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-sql-spring</artifactId>
<version>${querydsl.version}</version>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-sql-codegen</artifactId>
<version>${querydsl.version}</version>
<scope>provided</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-maven-plugin</artifactId>
<version>${querydsl.version}</version>
<executions>
<execution>
<goals>
<goal>export</goal>
</goals>
</execution>
</executions>
<configuration>
<jdbcDriver>oracle.jdbc.driver.OracleDriver</jdbcDriver>
<jdbcUrl>jdbc:oracle:thin:@192.168.0.225:1521:orcl</jdbcUrl>
<jdbcUser>scott</jdbcUser>
<jdbcPassword>1234</jdbcPassword>
<packageName>com.example.employee.model</packageName>
<exportView>false</exportView>
<schemaPattern>SCOTT</schemaPattern>
<tableNamePattern>DEPT,EMP</tableNamePattern>
<targetFolder>target/generated-sources/java</targetFolder>
<namePrefix>Q</namePrefix>
<exportBeans>true</exportBeans>
</configuration>
<dependencies>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.1.0.7.0</version>
</dependency>
</dependencies>
</plugin>
</plugins>
</build>
tableNamePattern을 대문자로 설정해야 합니다.
7. Q 타입 클래스 생성
만약 작업 중 프로젝트에 빨간 x 표시가 보이면 다음 작업을 수행합니다.
프로젝트 선택 > 마우스 오른쪽 클릭 > Maven > Update Project… > 대상 프로젝트 확인 > OK
Q 타입 클래스를 생성하기 위해서 다음 작업을 수행합니다.
프로젝트 선택 > 마우스 오른쪽 클릭 > Run AS > Maven generate-sources
작업결과 프로젝트 구조
프로젝트 선택 > 새로고침 > target/generated-sources/java 폴더 확인
8. SQLQueryFactory 빈 설정
SQLQueryFactory를 빈으로 등록해 놓고 필요할 때 DI받아서 사용하면 편리합니다.
QuerydslSqlQueryConfig.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 QuerydslSqlQueryConfig {
@Autowired
private DataSource dataSource;
@Bean
public Configuration configuration() {
SQLTemplates templates = OracleTemplates.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);
}
}
