Querydsl JPA Query with MySQL, JPAQueryFactory 빈 설정, Querydsl JPA Query with Oracle, Querydsl SQL Query with MySQL, Querydsl SQL Query with Oracle
http://ojc.asia
새로운 기술을 습득하고 이를 실무에 반영하기 위해서는 많은 테스트가 필요합니다. 대표적인 데이터베이스인 Oracle, 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
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>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-apt</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-jpa</artifactId>
</dependency>
<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 타입 클래스를 생성합니다.
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);
}
}
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
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과 동일합니다. 다만 나중 편의를 위하여 조금 반복작업을 수행한다고 볼 수 있습니다.
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에서 사용한 엔티티 클래스와 같습니다.
<dependencies>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-apt</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-jpa</artifactId>
</dependency>
<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>
만약 작업 중 프로젝트에 빨간 x 표시가 보이면 다음 작업을 수행합니다.
프로젝트 선택 > 마우스 오른쪽 클릭 > Maven > Update Project… > 대상 프로젝트 확인 > OK
Q 타입 클래스를 생성하기 위해서 다음 작업을 수행합니다.
프로젝트 선택 > 마우스 오른쪽 클릭 > Run AS > Maven generate-sources
작업결과 프로젝트 구조
프로젝트 선택 > 새로고침 > target/generated-sources/java 폴더 확인
7.1과 7.2는 많은 부분이 비슷합니다. 사실상 다른 부분은 application.properties 뿐인데 이는 Q 타입 클래스 생성 시 사용되지 않으므로 거의 동일하다고 볼 수 있습니다.
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);
}
}
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>
만약 작업 중 프로젝트에 빨간 x 표시가 보이면 다음 작업을 수행합니다.
프로젝트 선택 > 마우스 오른쪽 클릭 > Maven > Update Project… > 대상 프로젝트 확인 > OK
Q 타입 클래스를 생성하기 위해서 다음 작업을 수행합니다.
프로젝트 선택 > 마우스 오른쪽 클릭 > Run AS > Maven generate-sources
작업 로그
작업결과 프로젝트 구조
프로젝트 선택 > 새로고침 > target/generated-sources/java 폴더 확인
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);
}
}
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
부록 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>
<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을 대문자로 설정해야 합니다.
만약 작업 중 프로젝트에 빨간 x 표시가 보이면 다음 작업을 수행합니다.
프로젝트 선택 > 마우스 오른쪽 클릭 > Maven > Update Project… > 대상 프로젝트 확인 > OK
Q 타입 클래스를 생성하기 위해서 다음 작업을 수행합니다.
프로젝트 선택 > 마우스 오른쪽 클릭 > Run AS > Maven generate-sources
작업결과 프로젝트 구조
프로젝트 선택 > 새로고침 > target/generated-sources/java 폴더 확인
SQLQueryFactory를 빈으로 등록해 놓고 필요할 때 DI받아서 사용하면 편리합니다.
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);
}
}
