[스프링교육,JPA교육★탑크리에듀][스프링JPA]Querydsl,오라클SQL,DISTINCT,IN,OR,예문

[스프링JPA]Querydsl,오라클SQL,DISTINCT,IN,OR예문입니다.

Querydsl for Oracle(Distinct, IN, OR)

@Override

public List<String> getJobDistinct(String deptno1, String deptno2) {

           //////////////////////////////////////////////////////////////

           // myemp1에서 '1'번부서 또는 '2'번부서원에 대해 중복제거 후 직무출력

           // DISTINCT, IN, OR 예문

           // JPA에서 실행하나 DB에서 직접쿼리하든지 8초정도소요(myemp1은 1000만건)

           //------------------------------------------------------------

           // select distinct MYEMP1.JOB

           // from MYEMP1 MYEMP1

           // where MYEMP1.DEPTNO in ('1', '2')

           /////////////////////////////////////////////////////////////

           List<String> rows = queryFactory.select(myemp1.job)

                                .distinct().from(myemp1)

                                .where(myemp1.deptno.in(deptno1, deptno2))                                        //.where(myemp1.deptno.eq(deptno1).or(myemp1.deptno.eq(deptno2)))

                                .fetch();

                                         

           return rows;

}

--------------------------------------------------------------------------------------------------

[실행 로그]

select distinct MYEMP1.JOB

from MYEMP1 MYEMP1

where MYEMP1.DEPTNO in ('1', '2')

 

20160304 21:13:21.700 [http-nio-8080-exec-5] INFO j.sqltiming - select distinct MYEMP1.JOB

from MYEMP1 MYEMP1

where MYEMP1.DEPTNO in ('1', '2')

 {executed in 7691 msec}

20160304 21:13:21.700 [http-nio-8080-exec-5] INFO j.resultsettable -

|-----------|

|job        |

|-----------|

|null      |

|CLERK      |

|SALESMAN  |

|DESIGNER  |

|MANAGER    |

|PROGRAMMER |

|ANALYST    |

|-----------|

 

[출처] 오라클자바커뮤니티 - http://ojc.asia/bbs/board.php?bo_table=LecJpa&wr_id=133