Dynamic SQL

IBATIS

iBatis is a persistence framework that facilitates the connection between databases based on SQL and Java applications. It works by separating SQL queries from the program's source code and storing them in separate XML files, linking the two together for interaction.

In practical scenarios, when writing SQL queries, there are often situations where dynamic queries need to be constructed. In such cases, repeatedly using control statements can reduce the readability of the source code. By utilizing iBatis' dynamic queries, you can implement cleaner and more maintainable code.

SQL written in iBatis must be a well-formed document. Therefore, special characters like < and > should be converted and used as < and >, respectively.

Below are several methods for using dynamic queries along with examples.

Variables are used in queries by writing them as #variableName#.

You can test the following example query using the Select Invoke automation model in X-UP Builder and the Sample DB Table Creation Scriptprovided in this manual.

Dynamic columns
Used when dynamically assigning 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

Dynamic WHERE clause
Dynamically use WHERE clause based on properties

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>
Check if the property attribute value is equal to the 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>

<isGreaterThan>
Check if the property attribute value is greater than the 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>

<isGreaterEqual>
Check if the property attribute value is greater than or equal to the 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>

<isLessThan>
Check if the property attribute value is less than the 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>

<isLessEqual>
Check if the property attribute value is less than or equal to the 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>

<isPropertyAvailable>
Check if the property is valid.

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

<isEmpty>
Check if the specified property is null, an empty string (""), an empty collection, or an empty String.valueOf().

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

<isNotEmpty>
Check if the specified property is not null, not an empty string (""), not an empty collection, and not an empty String.valueOf().

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

<isParameterPresent>
Evaluate if the parameter object exists.

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

<iterate>요소
Receive a property as a collection or array and generate repetitive parts of the SQL from its values.

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>

In X-UP, the target of the iterator is the dataset. Iteration can be applied to a single column of the dataset.

<iterate>Element-Many
Receive multiple collection or array properties and generate repetitive parts of the SQL from them.

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>

Composite dynamic SQL
Use of 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>

14. <if>

The query below the tag is executed when the condition is met.

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>

Receives multiple collection or array properties and generates repetitive parts of the 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>