Recommended reading:
MyBatis Introduction Learning Tutorial (I) - MyBatis Quick Start
What is dynamic SQL? What is dynamic SQL's function?
In the traditional way of using JDBC, I believe that when you combine complex SQL statements, you need to splice them. If you are not careful, even if you lack spaces, it will lead to errors. Mybatis' dynamic SQL function is to solve this problem. It can be combined into very flexible SQL statements through the if, choose, when, otherwise, trim, where, set, foreach tags, thereby improving the efficiency of developers.
Let’s experience the charm of Mybatis dynamic SQL:
1. if: You can judge, and I can judge too!
As a programmer, who doesn't understand if! You can also use if in mybatis:
<select id="findUserById" resultType="user">select * from user where <if test="id != null">id=#{id}</if>and deleteFlag=0;</select>The above example: If the incoming id is not empty, then SQL will splice id = #{id}. I believe everyone can understand this by reading it, and I won’t say much.
Careful people will find a problem: "This is wrong with you! If the id you pass in is null, then your final SQL statement will become select * from user where and deleteFlag=0, there is a problem with this statement!"
Yes, at this time, the where tag of mybatis should be grandly made:
2. where, with me, SQL statement splicing conditions are all about the clouds!
Let’s modify the above example through where:
<select id="findUserById" resultType="user">select * from user where <where><if test="id != null">id=#{id}</if>and deleteFlag=0;</where></select>Some people are about to ask: "What are you doing? Compared with the above, there is just an extra where tag! Will this still appear select * from user where and deleteFlag=0?"
Indeed, on the surface, there is just an extra where tag, but in essence, mybatis deals with it. When it encounters AND or OR, it knows how to deal with it. In fact, we can customize this processing rules through the trim tag.
3. trim: My territory, I make the decision!
The above where tag can be expressed as follows using trim:
<trim prefix="WHERE" prefixOverrides="AND |OR ">... </trim>
It means: when WHERE is followed by AND or OR, AND or OR is removed. In addition to WHERE, there is actually another classic implementation, that is SET.
4. set: Believe me, no mistakes!
<update id="updateUser" parameterType="com.dy.entity.User">update user set <if test="name != null">name = #{name},</if> <if test="password != null">password = #{password},</if> <if test="age != null">age = #{age}</if> <where><if test="id != null">id = #{id}</if> and deleteFlag = 0;</where></update>The question comes again: "If I only have name but not null, then this SQL becomes update set name = #{name}, where .........? The comma after your name will cause an error!"
Yes, at this time, you can use the set tag provided by mybatis for us. The following is modified through the set tag:
<update id="updateUser" parameterType="com.dy.entity.User">update user set <set><if test="name != null">name = #{name},</if> <if test="password != null">password = #{password},</if> <if test="age != null">age = #{age},</if> </set><where><if test="id != null">id = #{id}</if> and deleteFlag = 0;</where></update>This can be expressed as:
<trim prefix="SET" suffixOverrides=",">...</trim>
WHERE uses prefixOverrides (prefix), SET uses suffixOverrides (suffix), you can understand it!
5. foreach: You have for, I have foreach, don’t think it’s just you!
There is for in java, and it can be looped through for. Similarly, there is foreach in mybatis, and it can be used to implement loops. Of course, the looped objects are mainly java containers and arrays.
<select id="selectPostIn" resultType="domain.blog.Post">SELECT *FROM POST PWHERE ID in<foreach item="item" index="index" collection="list"open="(" separator="," close=")">#{item}</foreach></select>Pass a List instance or array as a parameter object to MyBatis. When doing so, MyBatis will automatically wrap it in a Map and use the name as the key. The List instance will take "list" as the key, and the key of the array instance will be "array". Similarly, when the loop object is a map, index is actually the map key.
6. choose: I chose you, you chose me!
There is switch in Java, and mybatis has choice.
<select id="findActiveBlogLike"resultType="Blog">SELECT * FROM BLOG WHERE state = 'ACTIVE'<choose><when test="title != null">AND title like #{title}</when><when test="author != null and author.name != null">AND author_name like #{author.name}</when><otherwise>AND featured = 1</otherwise></choose></select>In the above example: When neither title nor author is null, then choose one of two (the former is preferred). If both are null, then choose the otherwise one. If there is only one of tilte and author that is not null, then choose the one that is not null.
The above is the introduction tutorial for Mybatis (IV) of Mybatis Dynamic SQL introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to Wulin.com website!