MyBatis small traps that use dynamic SQL tags

Now mybatis more and more loved by everyone, its advantages we all know, I will not say more, directly say the key.

MyBatis provides dynamic SQL functionality, we can use <if><when><where><otherwise><foreach> and so on, So we can write the dynamic SQL generated by the condition, but, in this middle, we often use the <if> tag has a small misunderstanding, accidentally will fall down, the following first to give a normal example:

<SelectID= "Findactiveblogwithtitlelike"ParameterType= "Blog"Resulttype= "Blog">SELECT * from BLOG WHERE state = ' ACTIVE '<ifTest= "Title! = NULL">and title like #{title}</if></Select>

In the above example, when the title is not equal to NULL, the conditions in the middle of the,<if> tag are stitched together, so that the SQL statement is dynamic.

But when we judge all the conditions, will you write this:

<SelectID= "Findactiveblogwithtitlelike"ParameterType= "Blog"Resulttype= "Blog">SELECT * from BLOG WHERE<ifTest= "UserId! = null">state = ' ACTIVE '</if>  <ifTest= "Title! = NULL">and title like #{title}</if></Select>

No problem, huh? At least syntactically good, at least it can generate a SQL normally.

But, I don't know if you noticed. What happens when all the conditions are null?

SELECT *  from BLOG    WHERE

Did you see that? Can such a SQL be executed successfully?

The answer is of course no.

So what should I do? That is to remember, when you write dynamic SQL, first think about whether all the conditions are not set up, there will be only one where and no conditions, then you have to do is to add a <where> tag all the conditions are wrapped up.

<SelectID= "Findactiveblogwithtitlelike"ParameterType= "Blog"Resulttype= "Blog">SELECT * from BLOG<where>  <ifTest= "UserId! = null">state = ' ACTIVE '</if>  <ifTest= "Title! = NULL">and title like #{title}</if> </where></Select>

In this way, when all conditions are not set, where is not spelled.

　　

At this time, there are clever small partners found, if the first condition is not established, the second set up, then SQL will become this?

SELECT *  from BLOG       WHERE     and  like #{title}

This is OK, when you use the <if> tag to surround the condition, it will automatically remove and.

MyBatis small traps that use dynamic SQL tags