background

Where 1=1 where 1=1 where 1=1 where 1=1 where 1=1 This article. The article involves the alternative scheme of Mybatis. Some studious friends have asked questions based on the writing method of Mybatis in the comments section.

Hence, this article. This article will summarize and sort out the basic use forms, tips and easy pits of where tag in Mybatis, so as to facilitate people to better practice using D

Original manual splicing

When we do not use Mybatis where tag, we usually do manual stitching according to the query condition, that is, we use the above mentioned where 1=1 method, as shown in the following example:

<select id="selectSelective" resultType="com.secbro.entity.User"> select * from t_user where 1=1 <if test="username ! = null and username ! = ''"> and username = #{username} </if> <if test="idNo ! = null and idNo ! = ''"> and id_no = #{idNo} </if> </select>Copy the code

This method is mainly to avoid statement concatenation error, such as the following error SQL:

select * from t_user where and username = 'Tom' and id = '1001';
select * from t_user where and id = '1001';
Copy the code

The SQL statement is correct when 1=1 is added:

select * from t_user where 1=1 and username = 'Tom' and id = '1001';
select * from t_user where 1=1 and id = '1001';
Copy the code

We’ve already mentioned this before, but there’s some pressure on the MySQL database. Because 1=1 condition optimization filtering is required by MySQL. If you can do this in an application, it takes the pressure off MySQL. After all, applications can easily scale horizontally.

Use of the Mybatis where tag

In order to achieve MySQL performance tuning, we can be based on Mybatis where tag to implement. The WHERE tag is the top-level traversal tag and needs to be used together with the IF tag. It is meaningless to use it alone. There are two common implementations.

A:

<select id="selectSelective" resultType="com.secbro.entity.User"> select * from t_user <where> <if test="username ! = null and username ! = ''"> username = #{username} </if> <if test="idNo ! = null and idNo ! = ''"> and id_no = #{idNo} </if> </where> </select>Copy the code

Method 2:

<select id="selectSelective" resultType="com.secbro.entity.User"> select * from t_user <where> <if test="username ! = null and username ! = ''"> and username = #{username} </if> <if test="idNo ! = null and idNo ! = ''"> and id_no = #{idNo} </if> </where> </select>Copy the code

A closer look reveals that the difference between the two approaches is whether the SQL statement in the first if condition has and.

Here are two features of the WHERE tag:

  • First, the if tag will only be inserted if it has contentwhereClause;
  • Second, if the opening of the clause is”AND“Or”OR“,whereThe tag will replace it and remove it;

Mybatis’ WHERE tag will do something for us.

Note, however, that the WHERE tag will only intelligently remove (ignore) the prefix of the first statement that satisfies the condition. When using the WHERE tag, it is recommended that each statement be prefixed with an and or or prefix, otherwise there will be problems like the following:

<select id="selectSelective" resultType="com.secbro.entity.User"> select * from t_user <where> <if test="username ! = null and username ! = ''"> username = #{username} </if> <if test="idNo ! = null and idNo ! = ''"> id_no = #{idNo} </if> </where> </select>Copy the code

The generated SQL statement is as follows:

select * from t_user      WHERE username = ?  id_no = ?
Copy the code

Obviously, the grammar is wrong.

Therefore, when using the WHERE tag, it is recommended to add and or OR to all conditions;

Advanced: Custom trim label

When you use the where tag above, you can automatically remove the AND or OR of the first condition. Can you remove other customized keywords as well?

In this case, the WHERE tag is useless, and the trim tag is used to do the same.

<select id="selectSelective" resultType="com.secbro.entity.User"> select * from t_user <trim prefix="where" prefixOverrides="and | or "> <if test="username ! = null and username ! = ''"> and username = #{username} </if> <if test="idNo ! = null and idNo ! = ''"> and id_no = #{idNo} </if> </trim> </select>Copy the code

Rewrite the above write based on the WHERE tag to the trim tag, and find that the performance is exactly the same. The TRIM tag is also more flexible for customization.

The pit of the WHERE statement

There is one other area of concern when using where statements or other statements: the use of comments.

Let’s take an example:

<select id="selectSelective" resultType="com.secbro.entity.User"> select * from t_user <where> <if test="username ! = null and username ! = ''"> and username = #{username} </if> <if test="idNo ! = null and idNo ! = ''"> /* and id_no = #{idNo}*/ and id_no = #{idNo} </if> </where> </select>Copy the code

Add a comment /**/ to the above SQL statement, generate SQL statement:

select * from t_user WHERE username = ? /* and id_no = ? */ and id_no = ?Copy the code

An error is reported during execution.

Here’s another example:

<select id="selectSelective" resultType="com.secbro.entity.User"> select * from t_user <where> <if test="username ! = null and username ! = ''"> -- and username = #{username} and username = #{username} </if> <if test="idNo ! = null and idNo ! = ''"> and id_no = #{idNo} </if> </where> </select>Copy the code

The generated SQL statement is:

select * from t_user WHERE -- and username = ? and username = ? and id_no = ? 
Copy the code

This will also result in an error.

This is because when we configure SQL using XML, if we add comments after the WHERE tag, then when any child elements meet the criteria, except <! — –> comments are ignored by where, AND other comments, such as // OR /**/ OR –, are treated as the first clause element by where, resulting in a syntax error when the actual first AND OR clause element fails to be prefixed.

At the same time, in practice, I often find that improper use of annotations in XML leads to SQL syntax errors or execution of wrong results. It is strongly recommended that you do not comment out SQL in THE XML unless necessary, and that you use a version management tool to trace history and make changes.

summary

Based on the use of the WHERE tag in Mybatis, this paper introduces its use mode, features and the replacement function of trim tag. At the same time, it also mentions the possible pit when using. The content is simple, but if it is well practiced and avoided, it is also a sign of ability.

About the blogger: Author of the technology book SpringBoot Inside Technology, loves to delve into technology and writes technical articles.

Public account: “program new vision”, the blogger’s public account, welcome to follow ~

Technical exchange: Please contact the weibo user at Zhuan2quan