[자바교육,JPA교육,스프링교육_탑크리에듀][스프링JPA]Querydsl,오라클SQL,LIKE,BETWEEN예문

[스프링JPA]Querydsl,오라클SQL,LIKE,BETWEEN예문

Querydsl for Oracle(Like, Between)

public List<Tuple> getEnameSalBetweenLike(Long sal1, Long sal2, String namePattern) {

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

// 급여가 sal1~sal2 사이, 이름이 namePattern으로 시작하는 사원이름, 급여추출 

// JPA에서 0.4초 DB에서 직접쿼리하든지 0.3초 정도소요 

// (myemp1은 1000만건, ename, sal 칼럼은 각각 인덱스 생성되어 있음)

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

// select MYEMP1.ENAME, MYEMP1.SAL

// from MYEMP1 MYEMP1

// where MYEMP1.SAL between 5999990 and 6000000

// and MYEMP1.ENAME like '가%' escape '\'

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

QMyemp1 myemp1 = QMyemp1.myemp1;

List<Tuple> rows = queryFactory.select(myemp1.ename, myemp1.sal)

.from(myemp1)

.where(myemp1.sal.between(sal1, sal2)

  .and(myemp1.ename.like(namePattern + "%")))

.fetch();

return rows;

}

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

[실행 로그]

10. select MYEMP1.ENAME, MYEMP1.SAL

from MYEMP1 MYEMP1

where MYEMP1.SAL between 5999990 and 6000000 and MYEMP1.ENAME like '가%' escape '\'

20160304 21:06:11.419 [http-nio-8080-exec-7] INFO j.sqltiming - select MYEMP1.ENAME, MYEMP1.SAL

from MYEMP1 MYEMP1

where MYEMP1.SAL between 5999990 and 6000000 and MYEMP1.ENAME like '가%' escape '\'

 {executed in 406 msec}

20160304 21:06:11.420 [http-nio-8080-exec-7] INFO j.resultsettable -

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

|ename         |sal    |

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

|가길동5999993 |5999993 |

|가길동5999999 |5999999 |

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