select count(*) as cnt from ( select REGEXP_REPLACE(REPLACE('.'||'192.168.100.55', '.', '.00'), '([^.]{3}(\.|$))|.', '\1') as ip , REGEXP_REPLACE(REPLACE('.'||'192.168.100.51', '.', '.00'), '([^.]{3}(\.|$))|.', '\1') as ip_start , REGEXP_REPLACE(REPLACE('.'||'192.168.100.56', '.', '.00'), '([^.]{3}(\.|$))|.', '\1') as ip_end from dual ) where ip between ip_start and ip_end
이런 정규식을 ibatis에서 사용시 아래와 같은 에러가 났다.
org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [null]; error code [0];
또는
The error occurred while preparing the mapped statement for execution.
토드나 쿼리창에서는 잘 돌아가는 쿼리인데..
곰곰히 생각해보니
문장을 맵핑해서 준비하는데 에러가 난다.... prepare statement 로 만드는데 에러가 난다는 말 같다.
첫번째는 JPAExpressions를 이용하여 키값과 max값을 조회조건으로 하여 가져오는것.
두번째는 orderby를 이용하여 첫번째 앤터티를 가져오는것.
1. JPAExpressions를 이용한 max값과 비교하여 가져오기
JPAQueryFactory query = new JPAQueryFactory(em); // JPA 쿼리
QBuilding qBuilding = QBuilding.building;
QBuilding b = new QBuilding("b");
Building building
= query.selectFrom(qBuilding)
.where(qBuilding.buildingID.eq(
JPAExpressions.select(b.buildingID.max()).from(b)))
.fetchFirst();
이런 경우 실제 실행되는 쿼리는 아래와 같다. (MS-SQL 기준)
select
TOP(1) building0_.BuildingID as Building1_3_,
building0_.Address as Address2_3_,
building0_.InsertID as InsertID3_3_,
building0_.InsertDT as InsertDT4_3_,
building0_.UpdateID as UpdateID5_3_,
building0_.UpdateDT as UpdateDT6_3_,
building0_.BranchID as BranchI10_3_,
building0_.BuildingNm as Building7_3_,
building0_.OwnType as OwnType8_3_,
building0_.Version as Version9_3_
from
dbo.Building building0_
where
building0_.BuildingID=(
select
max(building1_.BuildingID)
from
dbo.Building building1_
)
실행계획을 보면
2. orderby 를 이용한 첫번째 엔터티 가져오기
JPAQueryFactory query = new JPAQueryFactory(em); // JPA 쿼리
QBuilding qBuilding = QBuilding.building;
Building building
= query.selectFrom(qBuilding)
.orderBy(qBuilding.buildingID.desc())
.fetchFirst();
실제 실행되는 쿼리는 다음과 같다.
select
TOP(1) building0_.BuildingID as Building1_3_,
building0_.Address as Address2_3_,
building0_.InsertID as InsertID3_3_,
building0_.InsertDT as InsertDT4_3_,
building0_.UpdateID as UpdateID5_3_,
building0_.UpdateDT as UpdateDT6_3_,
building0_.BranchID as BranchI10_3_,
building0_.BuildingNm as Building7_3_,
building0_.OwnType as OwnType8_3_,
building0_.Version as Version9_3_
from
dbo.Building building0_
order by
building0_.BuildingID desc
