Querydsl 오라클 SQL 쿼리실습예제, Oracle Rownum, With문, Sequence, Union, NVL, N…

FSP 0 41 07.26 20:45

Querydsl 오라클 SQL 쿼리실습예제, Oracle Rownum, With문, Sequence, Union, NVL, NVL2, DECODE, Rank, 계층형쿼리, 오라클힌트, 프러시저, 함수

 


6. Querydsl SQL 쿼리 with Oracle

오라클데이터베이스는 사용자 1위인 대표적인 관계형 데이터베이스입니다. 그만큼 오라클에서만 사용가능한 SQL 방법들이 많이 존재합니다. 이번 장에서 Querydsl 사용하여 이러한 것들을 어떻게 메소드 기반으로 작성할 있는지 살펴보겠습니다.

 

6.1. 테스트 프로젝트 만들기

개발을 위한 프로젝트 생성 환경설정 자체도 연습이 필요한 부분이므로 다음 내용을 따라서 진행하시는 것을 권해 드립니다. 하지만 "부록 7.4 Querydsl SQL Query with Oracle" 참조하여 진행하셔도 괜찮습니다.

 

6.1.1. 프로젝트 생성

File > New > Spring Starter Project >

프로젝트 : chapter6-1 > Next >

디펜던시 선택: Web, JPA, Lombok > Finish

 

6.1.2. 데이터베이스 테이블 생성

"부록 5. Oracle Database" 참조하여 오라클 데이터베이스를 설치합니다.

 

1. DB 생성

데이터베이스 설치 이미 생성되어 있는 SCOTT 계정의 DB 사용합니다.

 

2. 테이블 생성

데이터베이스 설치 이미 생성되어 있는 테스트 테이블들을 대상으로 학습합니다.

DEPT, EMP, SALGRADE

 

3. 테스트 더미 데이터 입력

데이터베이스 설치 이미 테스트 용도로 입력되어 있는 정보를 그대로 사용하여 학습합니다.

필요하다면 다음 쿼리를 사용하여 초기화 하십시오.


dept-emp-salgrade-ddl.sql

DROP TABLE EMP;

DROP TABLE DEPT;

DROP TABLE SALGRADE;

 

CREATE TABLE DEPT(

        DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,

        DNAME VARCHAR2(14),

        LOC   VARCHAR2(13)

);

  

CREATE TABLE EMP(

        EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,

        ENAME VARCHAR2(10),

        JOB   VARCHAR2(9),

        MGR   NUMBER(4),

        HIREDATE DATE,

        SAL   NUMBER(7,2),

        COMM  NUMBER(7,2),

        DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT

);

  

CREATE TABLE SALGRADE(

        GRADE NUMBER,

        LOSAL NUMBER,

        HISAL NUMBER

);

  

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');

INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');

INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');

INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

 

INSERT INTO EMP VALUES(7369,'SMITH','CLERK',    7902,

to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);

INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN', 7698,

to_date('20-2-1981', 'dd-mm-yyyy'),1600,300,30);

INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',  7698,

to_date('22-2-1981', 'dd-mm-yyyy'),1250,500,30);

INSERT INTO EMP VALUES(7566,'JONES','MANAGER',  7839,

to_date('2-4-1981',  'dd-mm-yyyy'),2975,NULL,20);

INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,

to_date('28-9-1981', 'dd-mm-yyyy'),1250,1400,30);

INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',  7839,

to_date('1-5-1981',  'dd-mm-yyyy'),2850,NULL,30);

INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',  7839,

to_date('9-6-1981',  'dd-mm-yyyy'),2450,NULL,10);

INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',  7566,

to_date('13-07-1987', 'dd-mm-yyyy'),3000,NULL,20);

INSERT INTO EMP VALUES(7839,'KING','PRESIDENT', NULL,

to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);

INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,

to_date('8-9-1981',  'dd-mm-yyyy'),1500,0,30);

INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',    7788,

to_date('13-07-1987', 'dd-mm-yyyy'),1100,NULL,20);

INSERT INTO EMP VALUES(7900,'JAMES','CLERK',    7698,

to_date('3-12-1981', 'dd-mm-yyyy'),950,NULL,30);

INSERT INTO EMP VALUES(7902,'FORD','ANALYST',   7566,

to_date('3-12-1981', 'dd-mm-yyyy'),3000,NULL,20);

INSERT INTO EMP VALUES(7934,'MILLER','CLERK',   7782,

to_date('23-1-1982', 'dd-mm-yyyy'),1300,NULL,10);

 

INSERT INTO SALGRADE VALUES (1, 700,1200);

INSERT INTO SALGRADE VALUES (2,1201,1400);

INSERT INTO SALGRADE VALUES (3,1401,2000);

INSERT INTO SALGRADE VALUES (4,2001,3000);

INSERT INTO SALGRADE VALUES (5,3001,9999);

 

COMMIT;

 

6.1.3. 프로젝트 환경설정

 

logback-spring.xml

이전 프로젝트에서 사용하던 파일을 그대로 사용합니다.

log4jdbc.log4j2.properties

이전 프로젝트에서 사용하던 파일을 그대로 사용합니다.

 

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=sccott

spring.datasource.password=1234

spring.datasource.driver-class-name=net.sf.log4jdbc.sql.jdbcapi.DriverSpy

# ignore schema.sql, data.sql

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

 


pom.xml

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0"

        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

        xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

        <modelVersion>4.0.0</modelVersion>

 

        <groupId>com.example</groupId>

        <artifactId>boot</artifactId>

        <version>0.0.1-SNAPSHOT</version>

        <packaging>jar</packaging>

 

        <parent>

                 <groupId>org.springframework.boot</groupId>

                 <artifactId>spring-boot-starter-parent</artifactId>

                 <version>1.4.1.RELEASE</version>

                 <relativePath /> <!-- lookup parent from repository -->

        </parent>

 

        <properties>

                 <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

                 <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>

                 <java.version>1.8</java.version>

                 <querydsl.version>4.1.2</querydsl.version>

        </properties>

 

        <dependencies>

                 <dependency>

                         <groupId>org.springframework.boot</groupId>

                         <artifactId>spring-boot-starter-data-jpa</artifactId>

                 </dependency>

                 <dependency>

                         <groupId>org.projectlombok</groupId>

                         <artifactId>lombok</artifactId>

                 </dependency>

                 <dependency>

                         <groupId>org.springframework.boot</groupId>

                         <artifactId>spring-boot-starter-web</artifactId>

                 </dependency>

 

                 <dependency>

                         <groupId>org.springframework.boot</groupId>

                         <artifactId>spring-boot-starter-test</artifactId>

                         <scope>test</scope>

                 </dependency>

 

                 <dependency>

                         <groupId>org.bgee.log4jdbc-log4j2</groupId>

                         <artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>

                         <version>1.16</version>

                 </dependency>

                 <dependency>

                         <groupId>com.querydsl</groupId>

                         <artifactId>querydsl-jpa</artifactId>

                         <version>${querydsl.version}</version>

                 </dependency>

                 <dependency>

                         <groupId>com.querydsl</groupId>

                         <artifactId>querydsl-sql-spring</artifactId>

                         <version>${querydsl.version}</version>

                 </dependency>

                 <!-- for oracle -->

                 <dependency>

                         <groupId>com.oracle</groupId>

                         <artifactId>ojdbc6</artifactId>

                         <version>11.1.0.7.0</version>

                 </dependency>

        </dependencies>

 

        <build>

                 <plugins>

                         <plugin>

                                 <groupId>org.springframework.boot</groupId>

                                 <artifactId>spring-boot-maven-plugin</artifactId>

                         </plugin>

                         <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>jpa.model</packageName>

                                          <exportTable>true</exportTable>

                                          <exportView>false</exportView>

                                          <exportPrimarykey>true</exportPrimarykey>

<!-- schemaPattern 안쓰면 all_tables select할수 있는 모든 테이블이 export -->

                                          <schemaPattern>SCOTT</schemaPattern>

<!-- 테이블 이름을 콤마로 구분해서 패턴을 있다. 테이블명을 대문자로 사용할 -->

                                         <tableNamePattern>DEPT,EMP,SALGRADE</tableNamePattern>

                                          <targetFolder>target/generated-sources/java</targetFolder>

                                          <namePrefix>Q</namePrefix>

        <!-- targetFolder 테이블에 대한 모델 클래스 생성 -->

                                          <exportBeans>false</exportBeans>

                                 </configuration>

                                 <dependencies>

                                          <dependency>

                                                  <groupId>com.oracle</groupId>

                                                  <artifactId>ojdbc6</artifactId>

                                                  <version>11.1.0.7.0</version>

                                          </dependency>

                                 </dependencies>

 

                         </plugin>

                 </plugins>

        </build>

 

        <repositories>

                 <repository>

                         <id>oracle</id>

                         <name>ORACLE JDBC Repository</name>

                         <url>https://maven.oracle.com</url>;

                 </repository>

        </repositories>

</project>

 

오라클 드라이버는 Maven 중앙저장소에 없기 때문에 별도의 Repository 설정해야 합니다.

 

6.1.4. Q 타입 클래스 생성

 

프로젝트 선택 > 마우스 오른쪽 클릭 > Run As > Maven generate-sources 클릭

3fe82d2d8d5524c279e16d127817c0a7_1595763
 

 

QType 클래스가 3개가 생성되었습니다.

 


6.1.5. SQLQueryFactory 등록

 

QuerydslConfig.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.OracleTemplates;

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 QuerydslConfig {

        @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);

        }

}

 


6.2. Rownum

 

6.2.1. 상위 로우 구하기

결과처리를 모두 마친 자동으로 추가되는 칼럼인 rownum 사용하는 방법을 살펴봅니다.

 

목표 쿼리

select empno, ename from emp where rownum <=5

 

구현 메소드

rownum 처리를 위하여 limit 메소드를 사용합니다.

        public List<Tuple> getEmpByRownum() {

                 List<Tuple> rows = queryFactory

                                 .select(emp.empno, emp.ename)

                                 .from(emp).orderBy(emp.empno.asc())

                                 .limit(5).fetch();

                 return rows;

        }

 

테스트 메소드

        @Autowired

        private EmpDaoRownum dao;

        QEmp emp = QEmp.emp;

        @Test

        public void testGetEmpByRownum() {

                 List<Tuple> rows = dao.getEmpByRownum();

                 rows.forEach((row)->System.out.println(

                                 row.get(emp.empno) +"," + row.get(emp.ename)));

        }

 


테스트 결과

        select * from (

                 select EMP.EMPNO, EMP.ENAME

                 from EMP EMP

                 order by EMP.EMPNO asc

        ) where rownum <= 5

3fe82d2d8d5524c279e16d127817c0a7_1595763
 

 

6.2.2. 범위 로우 구하기

목표 쿼리

        select * from (

          select rownum as rnum, e.empno, e.ename from emp e

          order by e.empno asc

        ) where rnum > 1 and rnum < 5;

 

구현 메소드

offset 메소드로 상단 로우를 출력 범위에서 제거합니다. limit 메소드로 구하고자 하는 로우의 개수를 지정합니다.

        public List<Tuple> getEmpByOffsetLimit() {

                 List<Tuple> rows = queryFactory

                                 .select(emp.empno, emp.ename)

                                 .from(emp).orderBy(emp.empno.asc())

                                 .offset(1).limit(3).fetch();

                 return rows;

        }

 

테스트 메소드

        @Test

        public void testGetEmpByOffsetLimit() {

                 List<Tuple> rows = dao.getEmpByOffsetLimit();

                 rows.forEach((row)->System.out.println(

                                 row.get(emp.empno) +"," + row.get(emp.ename)));

        }


테스트 결과

        select * from (

                 select a.*, rownum rn from (

                         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

                 ) a

        ) where rn > 1 and rownum <= 3

3fe82d2d8d5524c279e16d127817c0a7_1595763
 

 

 

6.3. With

서브쿼리가 반복되어 사용되는 경우 with 구문을 사용해서 서브쿼리를 독립적으로 지정하고 활용하여 사용할 있습니다.

 

목표 쿼리

        with employee as (

                 select deptno, avg(sal) avg_sal

                 from emp group by deptno)

        select dname, employee.avg_sal

        from dept inner join employee

        on dept.deptno = employee.deptno;

 

구현 메소드

        public List<Tuple> getDeptAvgSal() {

                 // 서브쿼리를 위해 사용한다.

                 QEmp e = new QEmp("e");

                 // with 구문은 별칭을 사용해야 한다.

                 QEmp employee = new QEmp("employee");

                

                 // employee.sal 표현해야 하기 때문에 월급 평균값의 별칭을 "sal" 지정해야 한다.

                 List<Tuple> rows = queryFactory

                                 .query().with(employee, SQLExpressions

                                                  .select(e.deptno, e.sal.avg().as("sal"))

                                                  .from(e).groupBy(e.deptno))

                                 .select(dept.dname, employee.sal)

                                 .from(dept).innerJoin(employee)

                                 .on(dept.deptno.eq(employee.deptno)).fetch();

                 return rows;

        }

 

테스트 메소드

        @Test

        public void testGetDeptAvgSal() {

                 // 별칭을 사용했으므로 결과를 꺼낼 때도 별칭을 사용한다.

                 QEmp employee = new QEmp("employee");

                

                 List<Tuple> rows = dao.getDeptAvgSal();

                 rows.forEach((row)->System.out.println(

                                 row.get(dept.dname) +"," + row.get(employee.sal)));

        }

 

테스트 결과

        with employee as (

                 select e.DEPTNO, avg(e.SAL) sal

                 from EMP e group by e.DEPTNO)

        select DEPT.DNAME, employee.SAL

        from DEPT DEPT inner join employee employee

        on DEPT.DEPTNO = employee.DEPTNO

3fe82d2d8d5524c279e16d127817c0a7_1595763
 

 

 


6.4. Sequence

테이블과 별도로 존재하면서 값이 순차적으로 증가하는 칼럼을 위해서 사용하는 시퀀스의 설정방법을 살펴봅니다.

 

시퀀스 생성 쿼리

        create sequence test_seq_emp_empno

        start with 1 increment by 1

        nomaxvalue nominvalue

        nocycle nocache ;

 

사용 메소드

        public Long insert(Emp model) {

                 // 시퀀스이름을 설정할 앞에 스키마명까지 사용해야 한다.

                 Long affected = queryFactory.insert(emp)

                         .columns(emp.empno, emp.ename, emp.deptno)

                         .values(SQLExpressions.nextval("SCOTT.test_seq_emp_empno"),

                                          model.getEname(), model.getDeptno()).execute();

                 return affected;

        }

 

테스트 메소드

        @Test

        public void testInsert() {

                 Byte deptno = 40;

                 Emp model = new Emp();

                 model.setEname("TESTER");

                 model.setDeptno(deptno);

                

                 Long affected = dao.insert(model);

                 System.out.println("affected = "+affected);

        }

 

테스트 결과

        insert into EMP (EMPNO, ENAME, DEPTNO)

        values (SCOTT.test_seq_emp_empno.nextval, 'TESTER', 40)


6.5. Union

이상의 테이블의 질의 결과를 결합할 사용합니다. union 사용하면 중복된 로우를 제거하고 결과를 구하지만 union all 사용하면 중복된 로우도 결과에 포함됩니다. union 중복 로우를 제거하기 위해서 정렬을 수행하며 따라서 union all 보다 느립니다.

 

목표 쿼리

        select empno, ename, deptno, job from emp where job='CLERK'

        union all

        select empno, ename, deptno, job from emp where deptno=20

 

구현 메소드

        @SuppressWarnings("unchecked")

        public List<Tuple> get(String job, Byte deptno){

                 QEmp e = new QEmp("e");

                 List<Tuple> rows = queryFactory.query().unionAll(

                         SQLExpressions.select(emp.empno, emp.ename, emp.deptno, emp.job)

                                 .from(emp).where(emp.job.eq(job)),

                         SQLExpressions.select(e.empno, e.ename, e.deptno, e.job)

                                 .from(e).where(e.deptno.eq(deptno))).fetch();

                 return rows;

        }

 

테스트 메소드

        @Test

        public void testGet() {

                 String job = "CLERK";

                 Byte deptno = 20;

                 List<Tuple> rows = dao.get(job, deptno);

                 rows.forEach((row)->System.out.println(

                                 row.get(emp.empno) + "," +

                                 row.get(emp.ename) + "," +

                                 row.get(emp.deptno) + "," +

                                 row.get(emp.job)));

        }


테스트 결과

        (

                 select EMP.EMPNO, EMP.ENAME, EMP.DEPTNO, EMP.JOB

                 from EMP EMP where EMP.JOB = 'CLERK'

        )

        union all

        (

                 select e.EMPNO, e.ENAME, e.DEPTNO, e.JOB

                 from EMP e where e.DEPTNO = 20

        )      
3fe82d2d8d5524c279e16d127817c0a7_1595763
 

 

 

 

6.6. Nvl

결과값이 null 결과값을 변경하기 위해서 사용합니다.

 

6.6.1. Nvl

 

목표 쿼리 1

        --mgr null 경우 대신 0 값을 리턴한다.

        select empno, nvl(mgr, 0) mgr from emp where deptno = 10

 

목표 쿼리 2

        --coalesce 함수는 null 아닌 값을 사용하므로 mgr null 경우 0 값을 리턴한다.

        select empno, coalesce(mgr, 0) mgr from emp where deptno=10

 


구현 메소드

        public List<Tuple> getUsingNvl(Byte deptno){

                 Short zero = 0;

                 List<Tuple> rows = queryFactory

                         .select(emp.empno, emp.mgr.coalesce(zero).as("mgr"))

                         .from(emp).where(emp.deptno.eq(deptno)).fetch();

                 return rows;

        }

 

테스트 메소드

        @Test

        public void testGetUsingNvl() {

                 Byte deptno = 10;

                 List<Tuple> rows = dao.getUsingNvl(deptno);

                 rows.forEach((row)->System.out.println(

                                 row.get(emp.empno) +"," + row.get(emp.mgr)));

        }

 

테스트 결과

        select EMP.EMPNO, coalesce(EMP.MGR, 0) mgr

        from EMP EMP

        where EMP.DEPTNO = 10

3fe82d2d8d5524c279e16d127817c0a7_1595763
 

 


6.6.2. Nvl2

Querydsl CaseBuilder 클래스를 사용하여 구현할 있습니다.

 

목표 쿼리

        --mgr null 아닌 경우 1, null 경우 0 값을 리턴한다.

        select empno, nvl2(mgr, 1, 0) mgr from emp where deptno = 10

 

구현 메소드

        public List<Tuple> getUsingNvl2(Byte deptno){

                 Expression<Short> nvl2 = new CaseBuilder()

                                 .when(emp.mgr.isNotNull()).then((short) 1)

                                 .otherwise((short) 0).as(emp.mgr);

                

                 List<Tuple> rows = queryFactory

                         .select(emp.empno, nvl2)

                         .from(emp).where(emp.deptno.eq(deptno)).fetch();

                 return rows;

        }      

 

테스트 메소드

        @Test

        public void testGetUsingNvl2() {

                 Byte deptno = 10;

                 List<Tuple> rows = dao.getUsingNvl2(deptno);

                 rows.forEach((row)->System.out.println(

                                 row.get(emp.empno) +"," + row.get(emp.mgr)));

        }

 

테스트 결과

        select EMP.EMPNO, (

                 case when EMP.MGR is not null then 1

                 else 0 end) mgr

        from EMP EMP

        where EMP.DEPTNO = 10

3fe82d2d8d5524c279e16d127817c0a7_1595763
 


6.7. Decode

오라클에서 사용하는 SQL DECODE CASE문으로 풀어서 있습니다.

Querydsl에서는 CaseBuilder 클래스를 사용합니다.

 

목표 쿼리

        select emp.ename,

                 decode(emp.deptno,

                         10, 'ACCOUNTING',

                         20, 'RESEARCH',

                         30, 'SALES',

                         40, 'OPERATIONS',

                         'UNKNOWN') dname

        from emp

 

구현 메소드

        public List<Tuple> get(){

                 Expression<String> cases = new CaseBuilder()

                                 .when(emp.deptno.eq((byte) 10)).then("ACCOUNTING")

                                 .when(emp.deptno.eq((byte) 20)).then("RESEARCH")

                                 .when(emp.deptno.eq((byte) 30)).then("SALES")

                                 .when(emp.deptno.eq((byte) 40)).then("OPERATIONS")

                                 .otherwise("UNKNOWN").as(dept.dname);

                                                 

                 List<Tuple> rows = queryFactory

                                 .select(emp.ename, cases)

                                 .from(emp).fetch();

                 return rows;

        }

 

앨리어스 설정으로 dept.dname 사용하지 않고 문자열을 사용하면 Tuple에서 칼럼 데이터를 꺼내기 위해서 인덱스를 사용해야 하는 불편함이 생깁니다.

 

테스트 메소드

        @Test

        public void testGet() {

                 List<Tuple> rows = dao.get();

                 rows.forEach((row)->System.out.println(

                                 row.get(emp.ename) +"," + row.get(dept.dname)));

        }

 


테스트 결과

        select EMP.ENAME, (

                 case

                         when EMP.DEPTNO = 10 then 'ACCOUNTING'

                         when EMP.DEPTNO = 20 then 'RESEARCH'

                         when EMP.DEPTNO = 30 then 'SALES'

                         when EMP.DEPTNO = 40 then 'OPERATIONS'

                         else 'UNKNOWN' end) dname

        from EMP EMP

3fe82d2d8d5524c279e16d127817c0a7_1595763
 

 


6.8. Rank

rank 결과값을 기준으로 순위를 부여하기 위해서 사용합니다. rank 1등이 2명인 경우 다음 순위는 3등으로 처리합니다. dense_rank 1등이 2명인 경우 다음 순위는 2등으로 처리합니다.

 

목표 쿼리

        select

                 ename, sal, deptno,

                 rank() over (order by sal desc) sal_rank,

                 dense_rank() over (partition by deptno order by sal desc) dept_sal_rank

        from emp

        order by sal_rank asc, dept_sal_rank asc;

 

구현 메소드

        @SuppressWarnings("unchecked")

        public List<Tuple> get() {

                 // 칼럼 별칭으로 정렬하기 위해서 사용한다.

                 Path<Long> sal_rank = Expressions.numberPath(Long.class, "sal_rank");

                 Path<Long> dept_sal_rank = Expressions.numberPath(Long.class, "dept_sal_rank");

                                

                 List<Tuple> rows = queryFactory

                         .select(emp.ename, emp.sal, emp.deptno,

                                 SQLExpressions.rank().over().orderBy(emp.sal.desc()).as(sal_rank),

                                 SQLExpressions.denseRank().over().partitionBy(emp.deptno)
                                        .orderBy(emp.sal.desc()).as(dept_sal_rank))

                 .from(emp)

                 .orderBy(((ComparableExpressionBase<Long>) sal_rank).asc(),
                                ((ComparableExpressionBase<Long>) dept_sal_rank).asc())

                 .fetch();

                 return rows;

        }

 


테스트 메소드

        @Test

        public void testGet() {

                 // Tuple 객체에 담겨 있는 칼럼 정보를 꺼낼 칼럼 별칭을 사용하기 위해서 필요하다.

                 Path<Long> sal_rank = Expressions.numberPath(Long.class, "sal_rank");

                 Path<Long> dept_sal_rank = Expressions.numberPath(Long.class, "dept_sal_rank");

                

                 List<Tuple> rows = dao.get();

                 rows.forEach((row)->System.out.println(

                                 row.get(emp.ename) +"," +

                                 row.get(emp.sal) +"," +

                                 row.get(emp.deptno) +"," +

                                 row.get(sal_rank) +"," +

                                 row.get(dept_sal_rank)));

        }

 

테스트 결과

        select

                 EMP.ENAME, EMP.SAL, EMP.DEPTNO,

                 rank() over (order by EMP.SAL desc) sal_rank,

                 dense_rank() over (partition by EMP.DEPTNO order by EMP.SAL desc) dept_sal_rank

        from EMP EMP

        order by sal_rank asc, dept_sal_rank asc 

3fe82d2d8d5524c279e16d127817c0a7_1595763
 


6.9. Hierarchical Queries, Hint

 

6.9.1. 프로젝트 생성

앞서 만든 chapter6-1 프로젝트를 선택한 ctrl+c > ctrl+v 눌러서 소스 전체를 복사해서 프로젝트 명을 chapter6-9라고 명명하고 생성합니다.

 

6.9.2. 계층형 쿼리

EMP 테이블에서 KING으로부터 시작하여 계층적으로 출력하는 계층쿼리를 살펴보겠습니다. 오라클의 계층형쿼리를 사용하려면 OracleQuery 사용하고 LEVEL 같은 의사칼럼은 OracleGrammar 쓰면 됩니다.
물론 ROWID, ROWNUM, SYSDATE등도 OracleGrammar에서 지원합니다.

 

원래 만들고자 했던 오라클 계층 쿼리

select

        lpad(' ',(level-1)*2) || ename, sal

from emp

start with ename = 'KING'

connect by prior empno = mgr

 

querydsl OracleQuery 통해 생성된 SQL

select

        lpad(' ',(level - 1) * 2) || EMP.ENAME, EMP.SAL

from EMP EMP

start with EMP.ENAME = 'KING'

connect by prior EMP.EMPNO = EMP.MGR

 

Querydsl 사용하여 메소드기반으로 쿼리를 작성해도 원하는 오라클 전용 쿼리를 작성할 있음을 있습니다.


QuerydslRepository.java

package com.example.employee.repository;

 

import java.util.List;

import com.querydsl.core.Tuple;

 

public interface QuerydslRepository {

        // EMP 테이블에서 KING에서부터 시작하여 계층적으로 출력하는 계층쿼리

        List<Tuple> getHQuery() throws Exception;

       

        // EMP, DEPT DEPTNO 조인하여 SAL 내림차순 정렬, 5  출력(힌트 쓴것)

        // 인라인뷰, 조인 예문

        List<Tuple> getEnameDnameSalDesc();

       

        // EMP, DEPT DEPTNO 조인하여 SAL 내림차순 정렬, 5  출력(힌트 쓴것)

        // 인라인뷰, 조인, 힌트예문

        List<Tuple> getEnameDnameSalDesc2();

}

 

QuerydslRepositoryImpl.java

package com.example.employee.repository;

 

import static com.example.employee.model.QEmp.emp;

import static com.example.employee.model.QDept.dept;

import java.util.List;

import javax.sql.DataSource;

import javax.transaction.Transactional;

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.QueryFlag;

import com.querydsl.core.Tuple;

import com.querydsl.core.types.dsl.Expressions;

import com.querydsl.core.types.dsl.StringExpression;

import com.querydsl.core.types.dsl.StringExpressions;

import com.querydsl.sql.Configuration;

import com.querydsl.sql.SQLExpressions;

import com.querydsl.sql.SQLQueryFactory;

import com.querydsl.sql.oracle.OracleGrammar;

import com.querydsl.sql.oracle.OracleQuery;

 

@Repository

@Transactional

public class QuerydslRepositoryImpl implements QuerydslRepository {

        @Autowired

        SQLQueryFactory queryFactory;

        @Autowired

        DataSource dataSource;

        @Autowired

        Configuration configuration;

         

        @SuppressWarnings("unchecked")

        @Override

        public List<Tuple> getHQuery() throws Exception {

                 @SuppressWarnings("rawtypes")

                 OracleQuery query = new OracleQuery(

                                 dataSource.getConnection(), configuration);

 

                 List<Tuple> rows = query

                         .select(

                                 StringExpressions.lpad(

                                                  Expressions.stringTemplate("' '").stringValue(),

                                                  OracleGrammar.level.subtract(1).multiply(2)

                                 ).concat(emp.ename).as("ename"),

                                 emp.sal)

                         .startWith(emp.ename.eq("KING"))

                         .connectByPrior(emp.empno.eq(emp.mgr))

                         .from(emp).fetch();

                 return rows;

        }

}

 

출력 로그

3fe82d2d8d5524c279e16d127817c0a7_1595763
 

 


6.9.3. 힌트

EMP, DEPT DEPTNO 조인합니다. SAL 칼럼으로 내림차순 정렬하여 5건을 출력합니다.

limit 메소드 또는 OracleGrammar ROWNUM 사용합니다.

 

힌트를 적용하지 않은 예제

예상쿼리

SELECT *

FROM (

SELECT E.ENAME, D.DNAME, E.SAL

FROM (SELECT ENAME, SAL, DEPTNO FROM MYEMP1) E,

MYDEPT1 D

WHERE E.DEPTNO = D.DEPTNO

ORDER BY SAL DESC

)

WHERE ROWNUM <= 5;

 

Querydsl 만든 쿼리

select * from (

select ename, dname, sal

from (select MYEMP1.ENAME ename, MYDEPT1.DNAME dname, MYEMP1.SAL sal

from MYEMP1 MYEMP1

inner join MYDEPT1 MYDEPT1

on MYEMP1.DEPTNO = MYDEPT1.DEPTNO

order by MYEMP1.SAL desc)

) where rownum <= 5

 

QuerydslRepositoryImpl.java

        @Override

        public List<Tuple> getEnameDnameSalDesc() {

                 // 아래 query stringTemplates으로 만들면 select 'ename', 'dname','sal' 형태의

                 // 쿼리가 생성되니 주의할

                 StringExpression query1 = Expressions.stringPath("ename");

                 StringExpression query2 = Expressions.stringPath("dname");

                 StringExpression query3 = Expressions.stringPath("sal");

 

                 QEmp myemp1 = new QEmp("myemp1");

                 QDept mydept1 = new QDept("mydept1");

                

                 List<Tuple> rows = queryFactory

                                 .select(query1, query2,

                                                  query3)

                                 .from(SQLExpressions

                                                  .select(myemp1.ename.as("ename"),

                                                                  mydept1.dname.as("dname"),

                                                                   myemp1.sal.as("sal"))

                                                  .from(myemp1)

                                                  .innerJoin(mydept1)

                                                  .on(myemp1.deptno.eq(mydept1.deptno))

                                                  .orderBy(myemp1.sal.desc()))

                                 .limit(5).fetch();

                 return rows;

        }

 

실행계획

3fe82d2d8d5524c279e16d127817c0a7_1595763
 

 

힌트를 적용한 예제

getEnameDnameSalDesc()에서 사용된 order by절을 빼고 오라클 힌트를 사용하여 쿼리를 튜닝합니다. EMP, DEPT DEPTNO 조인합니다. SAL칼럼으로 내림차순 정렬하여 5건을 출력합니다.

 

예상 쿼리

SELECT * FROM (

   SELECT /*+ index_desc(MYEMP1 idx_myemp1_sal) */

        MYEMP1.ENAME, MYDEPT1.DNAME, MYEMP1.SAL

FROM (SELECT ENAME, SAL, DEPTNO

           FROM MYEMP1) MYEMP1,

           MYDEPT1

WHERE MYEMP1.DEPTNO = MYDEPT1.DEPTNO

AND MYEMP1.SAL > 0

)

WHERE ROWNUM <= 5;


Querydsl 만든 쿼리

select * from (

select ename, dname, sal

from (select /*+ index_desc(MYEMP1 idx_myemp1_sal) */

          MYEMP1.ENAME ename, MYDEPT1.DNAME dname, MYEMP1.SAL sal

          from MYEMP1 MYEMP1

          inner join MYDEPT1 MYDEPT1

          on MYEMP1.DEPTNO = MYDEPT1.DEPTNO

          and MYEMP1.SAL > 0)

) where rownum <= 5

 

QuerydslRepositoryImpl.java

        @Override

        public List<Tuple> getEnameDnameSalDesc2() {

                 StringExpression query1 = Expressions.stringPath("ename");

                 StringExpression query2 = Expressions.stringPath("dname");

                 StringExpression query3 = Expressions.stringPath("sal");

                

                 QEmp myemp1 = new QEmp("myemp1");

                 QDept mydept1 = new QDept("mydept1");

                

                 List<Tuple> rows = queryFactory

                         .select(query1, query2, query3)

                         .from(SQLExpressions

                                 .select(myemp1.ename.as("ename"),

                                                  mydept1.dname.as("dname"),

                                                  myemp1.sal.as("sal"))

                                 .addFlag(QueryFlag.Position.AFTER_SELECT,

                                          "/*+ index_desc(MYEMP1 idx_myemp1_sal) */")

                                 .from(myemp1)

                                 .innerJoin(mydept1)

                                 .on(myemp1.deptno.eq(mydept1.deptno)

                                 .and(myemp1.sal.gt(0))

                                 ))

                         .limit(5).fetch();

                 return rows;

        }


실행계획

3fe82d2d8d5524c279e16d127817c0a7_1595763
 

 

힌트를 적용하여 쿼리의 퍼포먼스를 높였음을 있습니다.


6.10. Procedure

오라클의 프로시저와 연동하는 방법을 살펴보겠습니다. 프로시저와 연동하는 가장 간편한 방법은 Spring Data JPA 제공하는 기능을 이용하는 것입니다.

 

6.10.1. 프로젝트 생성

앞서 만든 chapter6-1 프로젝트를 선택한 ctrl+c > ctrl+v 눌러서 소스 전체를 복사해서 프로젝트 명을 chapter6-10라고 명명하고 생성합니다.

 

6.10.2. 오라클 프로시져

학습을 위해 사용할 프로시저를 데이터베이스에 접속하여 생성합니다.

 

p_ getEnameByEmpno

CREATE OR REPLACE PROCEDURE p_getEnameByEmpno(

        p_empno IN INTEGER, p_ename OUT VARCHAR2)

IS

BEGIN

        SELECT ename INTO p_ename FROM emp WHERE empno=p_empno;

END;

/

 

p_getEmpByDeptno

CREATE OR REPLACE PROCEDURE p_getEmpByDeptno(

        p_deptno IN INTEGER, emp_cursor OUT SYS_REFCURSOR)

IS

        sql_string Varchar2(400);

BEGIN

        sql_string:='select empno, ename, deptno from emp where deptno=:deptno';

        Open emp_cursor FOR sql_string USING p_deptno;

END;

/

 

 

6.10.3. @NamedStoredProcedureQuery, @Procedure

@StoredProcedureParameter name속성으로 DB 만든 프로시저의 파라미터명을 사용할 있지만 out parameter 같이 사용되는 경우에는 사용이 불가능 합니다. out parameter 사용하는 경우 프로시저의 파라미터 사용은 위치지정 방식으로 사용해야 합니다. 또한 위치 지정방식과 Named 방식은 혼재해서 사용할 없습니다.

 

참조: https://hibernate.atlassian.net/browse/HHH-10756

 

Spring Data JPA Hibernate REF_CURSOR 지원하지 않지만 EclipseLink 지원합니다.

REF CURSOR 사용하는 경우 Spring Data JPA에서 지원하지 않으므로 직접 구현해서 사용해야 합니다.

 

관련 오류메시지 : REF_CURSOR parameters should be accessed via results

이슈리포트 : https://github.com/spring-projects/spring-data-examples/issues/44

 

Emp.java

package com.example.employee.model;

 

import javax.persistence.Entity;

import javax.persistence.Id;

import javax.persistence.NamedStoredProcedureQueries;

import javax.persistence.NamedStoredProcedureQuery;

import javax.persistence.ParameterMode;

import javax.persistence.StoredProcedureParameter;

import lombok.Data;

 

@Data

@Entity

@NamedStoredProcedureQueries({

        @NamedStoredProcedureQuery(name = "Emp.getEnameByEmpno",

                 procedureName = "p_getEnameByEmpno",

                 parameters = {

                         @StoredProcedureParameter(

                                 mode = ParameterMode.IN, type = Integer.class),

                         @StoredProcedureParameter(

                                 mode = ParameterMode.OUT, type = String.class) }),

        @NamedStoredProcedureQuery(name = "Emp.getEmpByDeptno",

                 procedureName = "p_getEmpByDeptno",

                 resultClasses = Emp.class,

                 parameters = {

                         @StoredProcedureParameter(

                                 mode = ParameterMode.IN, type = Integer.class),

                         @StoredProcedureParameter(

                                 mode = ParameterMode.REF_CURSOR, type = void.class) }) })

public class Emp {

        @Id

        private Integer empno;

        private String ename;

        private Integer deptno;

}


EmpRepository.java

package com.example.employee.repository;

 

import java.util.List;

import org.springframework.data.jpa.repository.query.Procedure;

import org.springframework.data.repository.CrudRepository;

import org.springframework.data.repository.query.Param;

import com.example.employee.model.Emp;

 

public interface EmpRepository extends CrudRepository<Emp, Integer>, EmpRepositoryCustom {

        // 사번을 입력받아 이름을 OUT 파라미터로 던지는 프로시저를 호출

        @Procedure(name = "Emp.getEnameByEmpno")

        String getEnameByEmpno(@Param("p_empno") int p_empno);

 

        // DB 만든 프로시저 이름을 메소드명으로 사용해서 연동할 있다.

        // @Procedure

        // String p_getEnameByEmpno(Integer p_empno);

 

        @Procedure(name = "Emp.getEmpByDeptno")

        List<Emp> getEmpByDeptno(@Param("p_deptno") int p_deptno);

}

 

EmpRepositoryCustom.java

package com.example.employee.repository;

 

import java.util.List;

import com.example.employee.model.Emp;

 

public interface EmpRepositoryCustom {

        List<Emp> getEmpByDeptno(int deptno);

}

 

EmpRepositoryImpl.java

package com.example.employee.repository;

 

import java.util.List;

import javax.persistence.EntityManager;

import javax.persistence.PersistenceContext;

import javax.persistence.StoredProcedureQuery;

import com.example.employee.model.Emp;

 

public class EmpRepositoryImpl implements EmpRepositoryCustom {

        @PersistenceContext

        EntityManager em;

 

        @Override

        public List<Emp> getEmpByDeptno(int deptno) {

                 StoredProcedureQuery query = em

                        .createNamedStoredProcedureQuery("Emp.getEmpByDeptno");

                 query.setParameter(1, deptno);

                 query.execute();

 

                 @SuppressWarnings("unchecked")

                 List<Emp> emps = query.getResultList();

                 return emps;

        }

}

 

EmpRepositoryTest.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.Emp;

 

@Transactional

@RunWith(SpringRunner.class)

@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)

public class EmpRepositoryTest {

        @Autowired

        private EmpRepository repo;

 

        @Test

        public void testGetEmpByDeptno() {

                 List<Emp> emps = repo.getEmpByDeptno(10);

                 for (Emp emp : emps) {

                         System.out.println(emp);

                 }

        }

 

        @Test

        public void testGetEnameByEmpno() {

                 String ename = repo.getEnameByEmpno(7788);

                 System.out.println("ename = " + ename);

        }

}

 


 

Comments