Dynamic SQL

IBATIS

iBatis는 SQL에 기반한 데이터베이스와 자바 등을 연결시켜 주는 역할을 하는 영속성 프레임워크입니다. 프로그램의 소스코드에서 SQL문장을 분리하여 별도의 XML파일로 저장하고 이 둘을 서로 연결시켜주는 방식으로 작동합니다.

실무에서 SQL문을 작성하다 보면 동적 쿼리문 작성을 작성해야 할 경우가 많이 생깁니다. 이럴 경우 제어문을 반복해서 사용하다보면 소스 코드의 가독성을 떨어트립니다. 이 때 IBatis의 동적 쿼리문을 사용함으로써 소스를 깔끔하게 구현할 수 있습니다.

iBatis로 작성되는 SQL의 경우 Well-formed한 문서여야 한다. 그렇기 때문에 <, > 와 같은 특수문자의 경우 < > 로 변환하여 사용하여야 한다.

아래는 몇가지 동적 쿼리문의 사용방법과 예제입니다.

변수는 #변수명칭#를 이용하여 쿼리를 작성합니다.

X-UP Builder에서 Automation 모델인 Select Invoke와 본 매뉴얼에서 제공하는 예제 DB 테이블 생성 스크립트를 이용하여 아래 Example 쿼리를 테스트할 수 있습니다.

동적 column
컬럼을 동적으로 할당할 경우에 사용

select
	empno,
	ename,
	job
	<dynamic>
		<isEqual prepend="," property="inParam" compareValue="sal">
			sal
		</isEqual>
		<isEqual prepend="," property="inParam" compareValue="hiredate">
			to_char(hiredate,'YYYY-MM-DD') as hiredate
		</isEqual>
	</dynamic>
from emp

동적 where 조건절
프로퍼티에 따라 동적으로 where 조건절을 사용

select empno, ename, job 
from emp
where 1=1
<dynamic>
	<isEmpty  prepend="AND" property="inParam">
		comm is null
	</isEmpty>
	<isNotNull  prepend="AND" property ="inParam">
		comm = #inParam#
	</isNotNull>
</dynamic>

<isEqual>
프로퍼티 속성값이 compare프로퍼티 값이나 compare 값과 같은지 검사

select empno, ename, job 
from emp
where 1=1
<dynamic>
	<isEqual property="inParam" compareValue="WARD" prepend="AND">
		ename=#inParam#
	</isEqual>
</dynamic>

<isGreaterThan>
프로퍼티 속성값이 compare프로퍼티 값이나 compare값보다 큰지 검사

select empno, ename, job 
from emp
where 1=1
<dynamic>
	<isGreaterThan prepend="AND" property="inParam" compareValue="2000">
		sal  &gt; #inParam#
	</isGreaterThan>
</dynamic>

<isGreaterEqual>
프로퍼티 속성값이 compare프로퍼티 값이나 compare값보다 크거나 같은지 검사

select empno, ename, job 
from emp
where 1=1
<dynamic>
	<isGreaterEqual prepend="AND" property="inParam" compareValue="3000">
		sal  &gt;= #inParam#
	</isGreaterEqual>
</dynamic>

<isLessThan>
프로퍼티 속성값이 compare프로퍼티 값이나 compare값보다 작은지 검사

select empno, ename, job 
from emp
where 1=1
<dynamic>
	<isLessThan prepend="AND" property="inParam" compareValue="1000">
		sal  &lt; #inParam#
	</isLessThan>
</dynamic>

<isLessEqual>
프로퍼티 속성값이 compare프로퍼티 값이나 compare값보다 작거나 같은지 검사

select empno, ename, job 
from emp
where 1=1
<dynamic>
	<isLessEqual prepend="AND" property="inParam" compareValue="2000">
		sal  &lt;= #inParam#
	</isLessEqual>
</dynamic>

<isPropertyAvailable>
프로퍼티가 유효한지 검사

<isPropertyAvailable property="memberName" prepend=",">

<isEmpty>
명시된 프로퍼티가 null이거나 빈 문자열(“”), 빈 컬렉션이나 빈 String.valueOf()인지를 검사

select empno, ename, job 
from emp
where 1=1
<dynamic>
	<isEmpty property="inParam" prepend="AND">
		comm is null
	</isEmpty>
</dynamic>

<isNotEmpty>
명시된 프로퍼티가 null이거나 빈 문자열(“”), 빈 컬렉션이나 빈 String.valueOf()가 아닌지 검사

select empno, ename, job 
from emp
where 1=1
<dynamic>
	<isNotEmpty property="inParam" prepend="AND">
		deptno = #inParam#
	</isNotEmpty>
</dynamic>

<isParameterPresent>
파라메터 객체가 존재하는지 평가

select empno, ename, job 
from emp
where 1=1
<dynamic>
	<isParameterPresent prepend="AND">
		ename = #inParam#
	</isParameterPresent> 
</dynamic>

<iterate>요소
컬렉션이나 배열로 된 프로퍼티를 받아서, 그 값들로부터 SQL의 반복적인 부분을 생성

Select empno,ename,job from emp 
<dynamic prepend="WHERE ename IN ">
	<iterate property="inDs.ename" open="(" close=")" conjunction=",">
		<isNotNull property="inDs.ename[]">
			#inDs.ename[]#
		</isNotNull>
	</iterate>
</dynamic>

X-UP에서 iterator 대상은 데이터셋입니다. 데이터셋의 단일컬럼에 대해 iterate를 적용시킬 수 있습니다.

<iterate>요소-다수
여러 개의 컬렉션이나 배열 프로퍼티를 받아 SQL의 반복적인 부분을 생성

select empno,ename,job
from emp
<dynamic prepend="WHERE 1=1 ">
	<iterate property="inDs.empno" open="and empno in (" close=")" conjunction=",">
		#inDs.empno[]#
	</iterate>
	<iterate property="inDsNames.ename" open="or ename in(" close=")" conjunction=",">
	   #inDsNames.ename[]#
	</iterate>
</dynamic>

복합 dynamic sql
여러 개의 동적 쿼리 사용

select empno , ename, job, 
sal,to_char(hiredate,'YYYY-MM-DD') hiredate
from emp
where 1=1 
<dynamic>
	<isNotEmpty prepend="AND" property="inParam1">
		(ename = #inParam1#
			<isNotEmpty prepend=" OR" property="inParam2">
				empno = #inParam2#
			</isNotEmpty> 
		)
	</isNotEmpty>     
	<isGreaterThan prepend="AND" property="inParam3" compareValue="2000">
		sal &gt;= 2000
	</isGreaterThan>
</dynamic>

MyBatis

MyBatis는 SQL에 기반한 데이터베이스와 자바 등을 연결시켜 주는 역할을 하는 영속성 프레임워크입니다. 프로그램의 소스코드에서 SQL문장을 분리하여 별도의 XML파일로 저장하고 이 둘을 서로 연결시켜주는 방식으로 작동합니다.

실무에서 SQL문을 작성하다 보면 동적 쿼리문 작성을 작성해야 할 경우가 많이 생깁니다. 이럴 경우 제어문을 반복해서 사용하다보면 소스 코드의 가독성을 떨어트립니다. 이 때 MyBatis의 동적 쿼리문을 사용함으로써 소스를 깔끔하게 구현할 수 있습니다.

MyBatis로 작성되는 SQL의 경우 Well-formed한 문서여야 한다. 그렇기 때문에 <, > 와 같은 특수문자의 경우 < > 로 변환하여 사용하여야 한다.

아래는 몇가지 동적 쿼리문의 사용법과 예제 입니다.

변수는 #변수명칭#를 이용하여 쿼리를 작성합니다.

1 <if>

조건을 만족 할 때 태그 이하의 쿼리를 추가 실행 합니다.

select 
	 name,
	 age,
	 color,
	 size
	 from PET
 
	<if test='#inParam#=="Y"'>
  		where age < 3
    </if>

select 
	 name,
	 age,
	 color,
	 size
	 from PET
 
	<if test='#inParam#=="Y" and #dsSearch.type#=="P"'>
  		where age < #dsSarch.type#
    </if>

2 <foreach>

여러 개의 컬렉션이나 배열 프로퍼티를 받아 SQL의 반복적인 부분을 생성 합니다.

select 
	 name,
	 age,
	 color,
	 size
	 from PET
 
    <if test='#inParam#.equals("Y")'>
       where age in 
        <foreach item="item" index="index" collection="#dsSearch.age#" open="("   
        separator="," close=")">
	
      	#dsSearch.age#
	 
    </foreach>
 </if>