Dynamic SQL

iBATIS is a persistence framework that serves to connect SQL-based databases and Java. It operates by separating SQL statements from a program's source code, storing them in a separate XML file, and connecting the two elements.

In practice, there are many instances when dynamic query statements need to be created while writing SQL statements. In such a case, a source code's readability degrades when control statements are used repeatedly. This can be remedied by using iBATIS dynamic query statements.

SQLs created with iBATIS must be well-formed text. This is why special characters such as < and > are converted to < and >, respectively.

Below are some usage methods and examples of dynamic query statements.

Use #variable name# for variables when creating queries.

The select invoke automation model in X-UP Builder and the Sample DB Table Creation Script section provided in this manual can be used to test the sample queries below.

  1. Dynamic column

    Used to dynamically allocate columns.

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

  1. Dynamic where conditional clause

    Dynamically use a where conditional clause depending on the property.

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>

  1. <isEqual>

    Check whether a property value is equal to a compare property value or compare value.

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

  1. <isGreaterThan>

    Check whether a property value is greater than a compare property value or compare value.

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

  1. <isGreaterEqual>

    Check whether a property value is greater than or equal to a compare property value or compare value.

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

  1. <isLessThan>

    Check whether a property value is less than a compare property value or compare value.

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

  1. <isLessEqual>

    Check whether a property value is less than or equal to a compare property value or compare value.

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

  1. <isPropertyAvailable>

    Check whether a property is valid.

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

  1. <isEmpty>

    Check whether a specified property is null, empty string (""), empty string(“”), empty collection, or empty String.valueOf().

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

  1. <isNotEmpty>

    Check whether a specified property is not null, empty string (""), empty string(“”), empty collection, or empty String.valueOf().

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

  1. <isParameterPresent>

    Evaluate whether a parameter object is present.

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

  1. <iterate>element

    Generate an iterative part of SQL from the values of a collection or array properties.

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>

The iterator target is a dataset in X-UP. You can apply iteration to a single column in a dataset.

  1. <iterate>element-multiple

    Generate an iterative part of SQL from the values of multiple collections or array properties.

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>

  1. Complex dynamic SQL

    Use multiple dynamic queries.

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>