iBatis를 사용하다 보니 또하나의 문제에 봉착했다.. LIKE 검색의 %기호를 인식하지 못하는 것이었다.
구글신에게 검색해 보고 다음과 같은 해결책을 얻을 수 있었다.
MySQL :
SELECT * FROM tbl_name WHERE column_name LIKE "%$username$%"
ORACLE :
SELECT * FROM tbl_name WHERE column_name LIKE '%' || #username# || '%'
SYBASE/SQL SERVER
SELECT * from tbl_name WHERE column_name LIKE '%' + #username# + '%'
여기서 변수명을 #로 둘러싸는 것과 $로 둘러싸는것의 차이점을 알 필요가 있다.
#의 경우에는 Prepare Statement로 등록이 된다. 디버그를 찍어봐도 ?로 치환된 이후 값이 대입된다.
하지만 $의 경우 바로 값이 치환된다. 특정 변수가 바로 DB에 입력되므로 보안에 좀더 신경을 써야 할것으로 생각된다.
여기서 변수명을 #로 둘러싸는 것과 $로 둘러싸는것의 차이점을 알 필요가 있다.
#의 경우에는 Prepare Statement로 등록이 된다. 디버그를 찍어봐도 ?로 치환된 이후 값이 대입된다.
하지만 $의 경우 바로 값이 치환된다. 특정 변수가 바로 DB에 입력되므로 보안에 좀더 신경을 써야 할것으로 생각된다.
3.9. Dynamic SQL
ADO에서 작업을 수행할때 발생되는 문제는 동적 SQL 이었다. 이것은 보통 SQL 문장과 함께 작업할때 파라미터의 값을 변경하는 작업으로 어려울때 이용을 하게 된다. 일반적인 방법은 if-else를 이용하거나, 끔찍한 스트링 조합으로 이를 해결하고 있다. 바라던 결과는 종종 각 예에 따른 쿼리를 작성하는 것이다. iBATIS DataMapper API는 어떠한 매핑되는 문장의 엘리먼트에도 적용할 수 있도록 좀더 좋은 코드를 만들 수 있도록 지원해준다. 간단한 예를 보면 다음과 같다.
Example 3.56. A simple dynamic select sttatement, with two possible outcomes
resultMap="account-result" >
select * from ACCOUNT
<isGreaterThan prepend="and" property="Id" compareValue="0">
where ACC_ID = #Id#
</isGreaterThan>
order by ACC_LAST_NAME
< /select>
상단에 제시된 예제는 2개의 가능한 문장을 생성해낼 수 있으며, 이 기준은 Id 프로퍼티에 들어갈 파라미터 객체에 달려 있다. 만약에 Id 파라미터가 0보다 큰 경우 문장은 다음 쿼리를 만들어 낸다.
만약 파라미터가 0보다 작거나 인경우에는 스테이트 먼트는 다음과 같은 쿼리를 만든다.
복잡한 상황에 부딛히지 않을경우 언뜻 보기에는 유용하지 않을 수있다. 예를 들어 다음과 같은 복잡한 상황이 될경우는 달라진다.
Example 3.57. A complex dynamic select statement, with 16 possible outcomes
select * from ACCOUNT
<dynamic prepend="WHERE">
<isNotNull prepend="AND" property="FirstName">
( ACC_FIRST_NAME = #FirstName#
<isNotNull prepend="OR" property="LastName">
ACC_LAST_NAME = #LastName#
</isNotNull>
)
</isNotNull>
<isNotNull prepend="AND" property="EmailAddress">
ACC_EMAIL like #EmailAddress#
</isNotNull>
<isGreaterThan prepend="AND" property="Id" compareValue="0">
ACC_ID = #Id#
</isGreaterThan>
</dynamic>
order by ACC_LAST_NAME
< /select>
이러한 상황에서는 1개의 서로다른 쿼리가 만들어 진다. if-else 구문과 스트링 조합으로 만든다면 아마도 매우 혼란하고, 수백 라인의 코드가 필요할 수있을 것이다.
dynamic 문장은 가능하면 간단한 컨디션 태그를 SQL에 넣어야 한다. 예를 들면 다음과 같다.
Example 3.58. Creating a dynamic statement with conditional tags
select * from ACCOUNT
<dynamic prepend="where">
<isGreaterThan prepend="and" property="id" compareValue="0">
ACC_ID = #id#
</isGreaterThan>
<isNotNull prepend="and" property="lastName">
ACC_LAST_NAME = #lastName#
</isNotNull>
</dynamic>
order by ACC_LAST_NAME
< /statement>
상기 구문에서 <dynamic>엘리먼트로 쌓여있는 SQL의 영역이 동적 영역이다. dynamic 엘리먼트는 옵션이고, prepend("WHERE")를 이용하여 조건 문장을 포함하지 않고서도 조건절을 적용할 수 있도록 해준다. 스테이트먼트 섹션은 아래에서 말하는것과 같이 몇개의 컨디션 엘리먼트를 포함하고 있다. 모든 컨디션 엘리먼트쿼리에 파라미터 객체를 전달한 상태를 기본으로 처리가 된다. prepend 속성은 코드 부분으로 이것은 필요한 경우 상위 엘리먼트의 prepend를 상속 받는다. 상기 예에세는 "where" prepend를 첫번째 조건이 true인경우 적용이 된다. 이것은 SQL 문장이 정당하게 만들어 졌는지 보장하는데 필요한 요소이다. 예를 들어 첫번째 상황이 true인상황이고, AND가 필요없다고 한다면 사실 스테이트먼트는 깨질 것이다. 다음 섹션에서 엘리먼트의 다양한 종류에 대해서 설명하고, Binary Conditionals와 Unary Conditionals 그리고 반복에 대해서 다룰 것이다.
이항 조건 엘리먼트는 속성 값과 정적값을 비고하거나 다른 프로퍼티 값과 비교를 수행한다. 만약 결과가 true인경우 바디 컨텐츠는 SQL 쿼리문에 포함된다.
| prepend – 구문이 SQL 의 부분으로 붙어서 사용될 것인지 결정한다. (옵션) |
| property – 비교할 대상 프로퍼티 (필요) |
| compareProperty – 비교할 다른 프로퍼티(필수 혹은 비교할 값) |
| compareValue – 비교할 값 (필수 혹은 compareProperty) |
Table 3.7. Binary conditional attributes
| Element | Description |
|---|---|
| <isEqual> | 두 프로퍼티의 비교에서 프로퍼티의 값이 다른 프로퍼티와 동일한지 검사 <isEqual prepend="AND" property="status" compareValue="Y"> MARRIED = ‘TRUE' < /isEqual> |
| <isNotEqual> | 하나의 프로퍼티가 다른 프로퍼티와 다른지 검사<isNotEqual prepend="AND" property="status" compareValue="N"> MARRIED = ‘FALSE' < /isNotEqual> |
| <isGreaterThan> | 하나의 프로퍼티가 다른 프로퍼티보다 큰지 검사 <isGreaterThan prepend="AND" property="age" compareValue="18"> ADOLESCENT = ‘FALSE' < /isGreaterThan> |
| <isGreaterEqual> | 하나의 프로퍼티가 다른 프로퍼티값보다 크거나 같은지 검사한다.<isGreaterEqual prepend="AND" property="shoeSize" compareValue="12"> BIGFOOT = ‘TRUE' < /isGreaterEqual> |
| <isLessEqual> | 프로퍼티가 다른 프로퍼티보다 작거나 같은지 검사한다.<isLessEqual prepend="AND" property="age" compareValue="18"> ADOLESCENT = ‘TRUE' < /isLessEqual> |
3.9.2. Unary Conditional Elements
단항 조건 엘리먼트는 특정 상황에 대해서 프로퍼티의 상태를 검사한다.
| prepend – 적용하고자 하는 문장 (옵션) |
| property – 체크된 프로퍼티 (필수) |
Table 3.8. Unary conditional attributes
| Element | Description |
|---|---|
| <isPropertyAvailable> | 프로퍼티가 사용가능한 상태인지 체크한다.<isPropertyAvailable property="id" > ACCOUNT_ID=#id# < /isPropertyAvailable> |
| <isNotPropertyAvailable> | 프로퍼티가 사용가능하지 않는지 검사한다.<isNotPropertyAvailable property="age" > STATUS='New' < /isNotEmpty> |
| <isNull> | 프로퍼티가 널인지 검사한다. <isNull prepend="AND" property="order.id" > ACCOUNT.ACCOUNT_ID = ORDER.ACCOUNT_ID(+) < /isNotEmpty> |
| <isNotNull> | 프로퍼티가 널이 아닌지 검사한다. <isNotNull prepend="AND" property="order.id" > ORDER.ORDER_ID = #order.id# < /isNotEmpty> |
| <isEmpty> | 컬렉션 벨류, 스트링이 널이거나 혹은 비어 잇는지 ("" 혹은 size() < 1)인지 검사한다.<isEmpty property="firstName" > LIMIT 0, 20 < /isNotEmpty> |
| <isNotEmpty> | 컬렉션 밸류, 스트링이 널이 아닌지, 혹은 비어있지 않은지 검사한다. <isNotEmpty prepend="AND" property="firstName" > FIRST_NAME LIKE '%$FirstName$%' < /isNotEmpty> |
3.9.3. Parameter Present Elements
이 엘리먼트는 파라미터 객체의 존재에 대해서 체크를 수행한다.
prepend – 적용할 문장 구문 (옵션)
Table 3.9. Testing to see if a parameter is present
| Element | Description |
|---|---|
| <isParameterPresent> | 파라미터 객체가 현재 값이 존재하는지 (널이 아닌지)검사한다.<isParameterPresent prepend="AND"> EMPLOYEE_TYPE = #empType# < /isParameterPresent> |
| <isNotParameterPresent> | 파라미터 객체가 현재 값이 아닌지 (널인지) 검사한다.<isNotParameterPresent prepend="AND"> EMPLOYEE_TYPE = ‘DEFAULT' < /isNotParameterPresent> |
3.9.4. Iterate Element
이 태그는 컬렉션을 반복하고, 리스트에서 각 아이템의 내용을 반복한다.
| prepend – 사용할 구문(옵션) |
| property – 반복할 리스트 객체 (필수) |
| open – 반복에서 전체 블록을 열어줄때 시작할 단어 보통 [, ( 이 이용된다. (옵션) |
| close – 블록 반복의 끝에 닫을때 사용할 단어 보통 ], ) 이 이용된다.(옵션) |
| conjunction – 반복할 단어 사이에 연결자 AND, OR (옵션) |
Table 3.10. Creating a list of conditional clauses
| Element | Description |
|---|---|
| <iterate> | 리스트 반복 예제 <iterate prepend="AND" property="UserNameList" open="(" close=")" conjunction="OR"> username=#UserNameList[]# < /iterate>Note: [] 을 리스트 의 시작과 끝을 나타낼때 매우 중요한 값이다. 이 브라켓은 리스트에서 단순히 스트링 값을 담고 있다고 판단하고, 값을 출력하게 된다. |
3.9.5. Simple Dynamic SQL Elements
상단에 설명한 전체 동적 매핑된 구문의 강력함에도 불구하고, 가끔 단순하고, 작은 내용을 처리해야할 때가 있다. 여기 예에서는 SQL 스테이트문에는 단순한 동적 SQL 엘리먼트를 포함할 수 있으며 이것은 order by 문장에서 동적 구문을 구현하도록 하고 있다. 이것은 인파인 파라미터와 매우 닮은 형태를 가지고 있다. 그러나 약간의 차이점이 있자. 다음 예를 보자.
Example 3.59. A dynamic element that changes the collating order
select * from PRODUCT order by $preferredOrder$
< /statement>
상단의 예에서 preferredOrder 동적 엘리먼트는 파라미터 객체로 넘어온 preferredOrder의 값으로 대체된다. 차이점은 SQL 문장을 근본적으로 바꾼다는 것이고, 단순하게 파라미터 값을 세팅하는 것보다 더 심각할 수 있다. 동적 엘리먼트를 잘못 만들게 되면 보안, 성능, 안정성의 문제를 야기 시킨다. 그러므로 나머지 부분의 체크를 수행하기 위해서 적당한 다이나믹 엘리먼트를 이용해야할 것이다. 또한 디자인에서 명심해야할 것은 잠재적으로 데이터베이스 스펙이 비즈니스 객체 모델을 침범할 수 있다는것을 알아야 한다.
For example, you may not want a column name intended for an order by clause to end up as a property in your business object, or as a field value on your server page.
단순한 동적 엘리먼트는 <statements>를 추가하고, 변화가 필요한 부분을 직접 수정하면 된다.
Example 3.60. A dynamic element that changes the comparison operator
SELECT * FROM PRODUCT
<dynamic prepend="WHERE">
<isNotEmpty property="Description">
PRD_DESCRIPTION $operator$ #Description#
</isNotEmpty>
</dynamic>
< /statement>
상단 예제는 $operator$토큰을 전달 엘리먼트로 교체하는 예제이다. 만약 검색이 LIKE 검색을 수행하는 경우 %dog%로 값이 들어왔다면 SQL 문장은 다음과 같은 구문을 만들어 낸다.
