Writing Dynamic SQL in MyBatis

This post will take you through a several examples of creating a dynamic sql queries which you will come up in a day to day development using mybatis.

• Use of if statement

We can use the if statement where we need to conditionally include parts of a where clause.

eg: Search Employee. Employee name and role can be added as filter fields.

	<select id="searchEmployee" resultType="Employee">
	SELECT * FROM Employee
	WHERE status = 'ACTIVE'
	<if test="empName != null">
	AND empName LIKE #{empName}
	</if>
	<if test="empRole != null">
	AND empRole = #{empRole}
	</if>
	</select>

view raw ibatis1.xml hosted with ❤ by GitHub

• Use of choose, when, otherwise

What about switch statement in query where we need to choose only one case among many options.

	<select id="findEmployeesByLocation" resultType="Employee">
	SELECT * FROM Employee WHERE status = 'ACTIVE'
	<choose>
	<when test="locationType == 'local'">
	AND local_loc_code = #{loc_code}
	</when>
	<when test="locationType == 'remote'">
	AND local_loc_code = #{loc_code}
	</when>
	<otherwise>
	AND loc_code = #{loc_code}
	</otherwise>
	</choose>
	</select>

view raw ibatis2.xml hosted with ❤ by GitHub

• Use of where

When you have a query with multiple optional where clauses you may find a difficulty to build the query with correct prefix. MyBatis has a simple solution for this. It is where element. The where element knows to only insert "WHERE" if there is any content returned by the containing tags. Furthermore, if that content begins with "AND" or "OR", it knows to strip it off.

	<select id="searchEmployee" resultType="Employee">
	SELECT * FROM Employee
	<where>
	<if test="status != null">
	status = #{status}
	</if>
	<if test="empName != null">
	AND empName LIKE #{empName}
	</if>
	<if test="empRole != null">
	AND empRole = #{empRole}
	</if>
	</where>
	</select>

view raw ibatis3.xml hosted with ❤ by GitHub

• Use of set

The set element can be used to dynamically include columns to update, and leave out others.

	<update id="updateEmployee">
	update Employee
	<set>
	<if test="firstName != null">firstName=#{firstName},</if>
	<if test="lastName != null">lastName=#{lastName},</if>
	<if test="email != null">email=#{email},</if>
	<if test="mobileNo != null">mobileNo=#{mobileNo}</if>
	</set>
	where id=#{id}
	</update>

view raw ibatis4.xml hosted with ❤ by GitHub

• Use of foreach

Another common necessity for dynamic SQL is the need to iterate over a collection, often to build an IN condition.

	<select id="selectEmployeesIn" resultType="Employee">
	SELECT *
	FROM Employee
	WHERE id in
	<foreach item="item" index="index" collection="list"
	open="(" separator="," close=")">
	#{item}
	</foreach>
	</select>

view raw ibatis5.xml hosted with ❤ by GitHub