MyBatis USES the dynamic SQL tag as a little trap

  • 2020-05-10 18:13:10
  • OfStack

MyBatis is an excellent persistence layer framework that supports normal SQL queries, stored procedures, and advanced mappings. MyBatis eliminates almost all manual Settings of JDBC code and parameters as well as retrieval encapsulation of result sets. MyBatis can use simple XML or annotations for configuration and raw mapping of the interface and POJO of Java (Plain Old Java Objects, common Java objects) to records in the database.

Now MyBatis is becoming more and more popular among people, and its advantages are well known to all, so I won't say any more about it, but just to get to the point.

MyBatis provides dynamic SQL functionality that we can use < if > < when > < where > < otherwise > < foreach > And so on, so that we can write the dynamic SQL generated by the conditions, but, in the middle of it, we use it a lot < if > There is a small mistake in the label, 1 accidentally will fall, the following is 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 example above, when title does not equal null, < if > The conditions in the middle of the tag are spliced together so that the SQL statement is dynamic.

But when we judge all the conditions, do you write something like this:


<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>

Okay? At least it's syntactically good, at least it generates 1 SQL normally.

But, I don't know if you noticed, what happens when everything is null?


SELECT * FROM BLOG 
WHERE

See? Can such SQL be successfully executed?

The answer, of course, is NO.

So what to do? Remember, when you write dynamic SQL, first consider whether 1 will result in all the conditions are not true, whether there will be only 1 WHERE and no conditions, then what you need to do is to add 1 < where > The label wraps all the 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>

This way, when all else fails, WHERE will not be spelled.

At this point, some smart friend found out that if the first condition is not true, the second is true, will SQL become like this?


SELECT * FROM BLOG 
WHERE
AND title like #{title}

You can rest assured of that when you use it < if > After the label wraps around the condition, it will automatically remove the AND.


Related articles: