[동영상]7.3 Querydsl SQL Query with MySQL, 스프링교육, JPA교육, 스프링학원, 자바학원
아래에서 동영상 확인 하세요~
http://ojc.asia/bbs/board.php?bo_table=LecJpa&wr_id=370
ojc.asia
https://www.youtube.com/watch?v=RdIPh1iGm5A&list=PLxU-iZCqT52AlV-Y7UlwSQp8N6pMjijFX&index=7&t=4s
https://www.youtube.com/watch?v=2zovSWrKhmo&list=PLxU-iZCqT52AlV-Y7UlwSQp8N6pMjijFX&index=8
https://www.youtube.com/watch?v=jrKfAdJXq7Q&list=PLxU-iZCqT52AlV-Y7UlwSQp8N6pMjijFX&index=9
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);
}
}
#Querydsl, #JPA, #JPA실습, #JPAMYSQL, #스프링교육, #자바교육, #스프링학원, #자바학원, #JPA동영상, #자바동영상,Querydsl, JPA, JPA실습, JPAMYSQL, 스프링교육, 자바교육, 스프링학원, 자바학원, JPA동영상, 자바동영상
