You’re a beginner with Mybatis Dynamic SQL?. Let me show you 5 conditions you should know about it. Because previous project I work with Mybatis, so listen to me. Read this post carefully, they will help you so much.
The content and this post very helpful and easy to understand. Of course, with example.
Get started now!
1. if condition
If is a basic statement, when the if a condition is true, they will append everything in if to SQL query. With Mybatis Dynamic SQL, if else condition render in XML make we code less in Java. Write only one SQL statement but still match all business and requirement.
Here’s a sample example
<select id="searchCourses" parameterType="hashmap" resultMap="ArticleResult">
<![CDATA[ SELECT * FROM KIEBLOG WHERE ARTICLE_ID = #{articleId}
<if test="subjectName != null">
AND NAME LIKE #{subjectName}
</if>
<if test="startDate != null">
AND START_DATE >= #{startDate}
</if>
<if test="endDate != null">
AND END_DATE <= #{endDate}
</if>
]]>
</select>If is a basic condition but please take note that when you use if with AND, OR condition inside. When the SQL generate, ensure that’s no SQL error like this.
// Please take care the SQL error when use if SELECT * FROM any_table_we_can_do WHERE AND condition_a = 1
2. choose, when, and otherwise conditions
Sometimes, we need to implement function search with many search type. Write too many if else statement is not best idea. So it’s time for choose when.
MyBatis Dynamic SQL provides the element to support this kind of dynamic SQL preparation. It’s like case when in SQL. Let me show you a example below:
<select id="searchCourses" parameterType="hashmap" resultMap="CourseResult">
SELECT * FROM COURSES
<choose>
<when test="searchBy == 'Tutor'">
WHERE TUTOR_ID= #{tutorId}
</when>
<when test="searchBy == 'CourseName'">
WHERE name like #{courseName}
</when>
<otherwise>
WHERE TUTOR start_date >= now() </otherwise> </choose>
</select>Actually, choose when like if else and else if. They append otherwise for the last else. If you have more than 3 case, I suggest use choose, when. It make the SQL write by Mybatis look pretty and easy to understand.
3. where condition
Take a look to the example in choosen why. Do you think that they have problem?.
// If use choosen and otherwise, we have many WHERE here
<when test="searchBy == 'Tutor'">
WHERE TUTOR_ID= #{tutorId}
</when>
<when test="searchBy == 'CourseName'">
WHERE name like #{courseName}
</when>
<otherwise>
WHERE TUTOR start_date >= now() </otherwise>The problem is WHERE to show many times. It’s not necessary and when to use too many WHERE, maybe two conditions are matched. So Mybatis Dynamic SQL generate two WHERE in only one SQL, it make SQLException
// If the value matched two times or we not sure about the conditon, we will meet the SQLException SELECT a,b,c FROM characters WHERE a = 1 WHERE b = 1
That’s why we need where, where condition auto append WHERE to each condition in tag.
// Dont care how many condition, we have only one WHERE
<select id="searchCourses" parameterType="hashmap" resultMap="ArticleResult">
SELECT * FROM ARTICLES
<where>
<if test=" tutorId != null ">
TUTOR_ID= #{tutorId}
</if>
<if test="articleName != null">
AND name like #{courseName}
</if>
<if test="startDate != null">
AND start_date >= #{startDate}
</if>
<if test="endDate != null">
AND end_date <= #{endDate}
</if>
</where>
</select> 4. trim condition
The element works similar to but provides additional flexibility on what prefix/suffix needs to be prefixed/suffixed and what prefix/suffix needs to be stripped off.
// Dont care how many condition, we have only one WHERE
<select id="searchArticle" parameterType="hashmap" resultMap="ArticleResult">
SELECT * FROM ARTICLES
<trim prefix="WHERE" prefixOverrides="AND | OR">
<if test=" articleId != null ">
TUTOR_ID= #{articleId}
</if>
<if test="articleName != null">
AND name like #{articleName}
</if>
</trim>
</select> Here will insert WHERE if any of the conditions are true and remove the AND or OR prefixes just after WHERE. It’s amazing thing from Mybatis Dynamic SQL and I think not many people working with Mybatis know about trim
5. foreach loop
The last tag I want to show you in this post is foreach. It’s a very common requirement for iterating through an array or list.
We have a list and want to search IN, we have array and want to search LIKE with each item in it. Mybatis Dynamic SQL will help. When they see the foreach, we also see the collection (the list or array we want to loop)
Foreach tag is easy to understand with example below:
<select id="searchArticleBySubjectId" parameterType="map" resultMap="ArticleResult">
SELECT * FROM ARTICLES
<if test="tutorIds != null">
<where>
subject_id IN
<foreach item="subjectId" collection="subjectIds" open="(" separator="," close=")">
#{subjectId}
</foreach>
</where>
</if>
</select>When the subjectIds has value like this [1,2,3]. The SQL generate by Mybatis is:
SELECT * FROM ARTICLES WHERE subject_id IN (1,2,3)
6. Reference about Mybatis Dynamic SQL
- Introduction about MyBatis Dynamic SQL – Kieblog
- Java Persistence with MyBatis 3
- Mybatis 3 – Dynamic SQL
Thank for reading, please like a Facebook page. HAPPY CODING!
