Querydsl JPA Query with MySQL, JPAQueryFactory 빈 설정, Querydsl JPA Quer…

FSP 0 48 07.26 21:50

 

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

 

 3fe82d2d8d5524c279e16d127817c0a7_1595767


작업결과 프로젝트 구조

프로젝트 선택 > 새로고침 > target/generated-sources/java 폴더 확인

3fe82d2d8d5524c279e16d127817c0a7_1595767
 

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</url>;

                 </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 폴더 확인

3fe82d2d8d5524c279e16d127817c0a7_1595767
 

 

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,'MA 302 Found

Found

The document has moved here.