[스프링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 |
|------------------|----------|
댓글 없음:
댓글 쓰기