How To Make Dynamic Sql Query Using Mybatis

Sql queries can be dynamic using Mybatis, may help reusing sql queries for more than one occasion. As per the context, Sql "where" conditions can be appended and executed for resultsaccordingly. Here are few samples that demonstrate the use of test conditons in Sql queries.

Sample 1 demonstrate the simple test of condition when S_EMP_NAME parameter is not null

#Sample 1
SELECT A.EMP_ID,A.EMP_NAME,B.DEPT_ID B
FROM EMP A,DEPT B
WHERE A.DEPT_ID=B.DEPT_ID
AND A.EMP_ID=#{EMP_ID}
<if test="S_EMP_NAME !=null ">
AND A.EMP_NAME LIKE #{S_EMP_NAME} || '%'
</if>

Sample 2 demonstrate the build of total "where" condition. The previxOverrides variable is "AND | OR' is generated dynamically to make the statement complete.

#Sample 2
SELECT A.EMP_ID,A.EMP_NAME
FROM EMP A
<trim prefix="WHERE" prefixOverrides="AND |OR ">
<if test="S_EMP_ID !=null ">
A.EMP_ID=#{S_EMP_ID}
</if>
<if test="S_EMP_NAME !=null ">
A.EMP_NAME LIKE #{S_EMP_NAME}||'%'
</if>
</trim>

Sample 3 demonstrate build of like search for EMP_NAME only when S_EMP_ID parameter is null

#Sample 3
SELECT A.EMP_ID,A.EMP_NAME
FROM EMP A
<trim prefix="WHERE" prefixOverrides="AND |OR ">
<if test="S_EMP_ID !=null ">
A.EMP_ID=#{S_EMP_ID}
</if>
<if test="S_EMP_ID ==null ">
<if test="S_EMP_NAME !=null ">
A.EMP_NAME LIKE #{S_EMP_NAME}||'%'
</if>
</if>
</trim>