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.
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
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>
<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>
<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 > #inParam# </isGreaterThan> </dynamic>
<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 >= #inParam# </isGreaterEqual> </dynamic>
<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 < #inParam# </isLessThan> </dynamic>
<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 <= #inParam# </isLessEqual> </dynamic>
<isPropertyAvailable>
Check whether a property is valid.
<isPropertyAvailable property="memberName" prepend=",">
<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>
<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>
<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>
<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.
<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>
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 >= 2000 </isGreaterThan> </dynamic>