MyBatis is an excellent persistence layer framework that supports plain SQL queries, stored procedures and advanced mapping. MyBatis eliminates manual settings of almost all JDBC code and parameters and search encapsulation of the result set. MyBatis can use simple XML or annotations for configuration and original mapping, mapping interfaces and Java's POJOs (Plain Old Java Objects) into records in the database.
Now that MyBatis is becoming more and more popular, everyone knows its advantages, so I won’t say much, just talk about the key points.
MyBatis provides dynamic SQL functions. We can use <if><when><where><otherwise><foreach> and so on, so that we can write dynamic SQL generated according to conditions. However, in the middle, there is a small misunderstanding of the <if> tag we often use, which will fall down if we accidentally. Let me give you a normal example:
<select id="findActiveBlogWithTitleLike" parameterType="Blog" resultType="Blog">SELECT * FROM BLOG WHERE state = 'ACTIVE' <if test="title != null">AND title like #{title}</if></select>In the above example, when the title is not equal to null, the conditions in the <if> tag will be spliced, so that the SQL statement will be dynamic.
But when we judge all the conditions, do you write this way:
<select id="findActiveBlogWithTitleLike" parameterType="Blog" resultType="Blog">SELECT * FROM BLOG WHERE<if test="userId != null">state = 'ACTIVE' </if><if test="title != null">AND title like #{title}</if></select>No problem, right? At least it's syntactically good, at least it can generate a SQL normally.
However, I don’t know if you have noticed that when all the conditions are null, what happens?
SELECT * FROM BLOG WHERE
Have you seen it? Can such SQL be successfully executed?
The answer is of course NO.
So what should I do? Then remember that when you write dynamic SQL, first consider whether all conditions will occur if all conditions are not true, and whether there will be only one WHERE but no conditions. Then all you have to do is add a <where> tag to wrap all conditions.
<select id="findActiveBlogWithTitleLike" parameterType="Blog" resultType="Blog">SELECT * FROM BLOG <where><if test="userId != null">state = 'ACTIVE' </if><if test="title != null">AND title like #{title}</if></where></select>In this way, when all conditions are not true, WHERE will not be spelled out.
At this time, the smart friend discovered that if the first condition is not true and the second condition is true, will SQL become like this?
SELECT * FROM BLOG WHEREAND title like #{title}Don't worry, when you use the <if> tag to surround the conditions, it will automatically remove AND.
The above is the small trap of using dynamic SQL tags by the editor to introduce to you by MyBatis. 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!