One of the powerful features of MyBatis is usually its dynamic SQL capabilities. If you have experience using JDBC or other similar frameworks, you understand how painful it is to concatenate SQL strings together conditionally, make sure you can't forget spaces or omit commas at the end of the list. Dynamic SQL can handle this pain thoroughly.
Dynamic SQL
MyBatis' dynamic SQL solves the pain of SQL string splicing.
1.if
<select id="findActiveBlogWithTitleLike"parameterType="Blog" resultType="Blog">SELECT * FROM BLOGWHERE state = 'ACTIVE'<if test="title != null">AND title like #{title}</if></select> This sentence will provide an optional text search function. If no title is passed, then all activated blogs will be returned.
If a title is passed, then the similar title will be found.
2. Choose, when, otherwise
<select id="findActiveBlogLike"parameterType="BLOG" resultType="BLOG">SELECT * FROM BLOGWHERE<choose><when test="title != null">AND title like #{title}</when><when test="author != null and author.name != null">AND title like #{author.name}</when><otherwise>AND featured = 1</otherwise></choose></select> Note: If none of the above conditions match, it will become SELECT * FROM BLOG WHERE
If there is only a second match, it will become SELECT * FROM BLOG WHERE AND title LIKE somelike
Obviously, this will fail in query. To solve this problem, mybatis provides a solution.
<select id="findActiveBlogLike"parameterType="BLOG" resultType="BLOG">SELECT * FROM BLOGWHERE<trim prefix="WHERE" prefixOverrides="AND |OR "><choose><when test="title != null">AND title like #{title}</when><when test="author != null and author.name != null">AND title like #{author.name}</when><otherwise>AND featured = 1</otherwise></choose></trim></select>The overrides property is overridden with a pipe text separator, and the blank space is important here. The result is to remove the content specified in overrides in InnerText.
3.set
<update id="updateAuthorIfNecessary"parameterType="Author">update Author<set><if test="username != null">username=#{username},</if><if test="password != null">password=#{password},</if><if test="email != null">email=#{email}</if></set>where id=#{id}</update>The same problem as above, after optimization:
<update id="updateAuthorIfNecessary"parameterType="Author">update Author<trim prefix="where" prefixOverrides=","><set><if test="username != null">username=#{username},</if><if test="password != null">password=#{password},</if><if test="email != null">email=#{email}</if></set>where id=#{id}</trim></update>The above is the problem of MyBatis dynamic splicing of Sql strings introduced to you by the editor. 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!