This is the third day of my participation in the November Gwen Challenge. Check out the details: the last Gwen Challenge 2021

Recently took over an old project, “happy mood” naturally indescribable, do development friends know, not to say more here, are tears…

When taking on an old project, it is natural to familiarize yourself with the business code first. However, when browsing the Mapper file, I discovered a strange thing. Here is the simplified business code:


      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.mapper.UserMapper">
    <select id="list" resultType="com.example.demo.model.User">
        select * from user
        where 1=1
        <if test="name! =null">
            and name=#{name}
        </if>
        <if test="password! =null">
            and password=#{password}
        </if>
    </select>
</mapper>
Copy the code

Those of you who are savvy enough to see the problem have found a common vision in many mapper’s, almost all of which contain a useless concatenation SQL: Where 1=1. As a person who almost has code cleanliness disease, nature can not help but start to transform.

The wrong way to remodel

The simplest way to remove where 1=1 is to remove it directly from the code, as shown below:

<? xml version="1.0" encoding="UTF-8"? > <! DOCTYPE mapper PUBLIC"- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.mapper.UserMapper">
    <select id="list" resultType="com.example.demo.model.User">
        select * from user
            where
            <if test="name! =null">
                name=#{name}
            </if>
            <if test="password! =null">
                and password=#{password}
            </if>
    </select>
</mapper>
Copy the code

The above code removes 1=1 and removes the AND from the first name query in case the SQL query fails.

But is that all right? Let’s look directly at the result. When the query contains the parameter name, the result is as follows:Everything went well. It went perfectly.

However, when the name argument is omitted (which can be omitted because name is not required), the following exception is actually raised:Or if only password is used, the result is the same:Error messages, so what to do? Is it difficult to restore 1=1?

The right way to improve

MyBatis has already thought of this problem, we can change the SQL where keyword into MyBatis tag, and add and concatenate each tag, so that the problem is solved, as shown in the following code:


      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.mapper.UserMapper">
    <select id="list" resultType="com.example.demo.model.User">
        select * from user
        <where>
            <if test="name! =null">
               and name=#{name}
            </if>
            <if test="password! =null">
                and password=#{password}
            </if>
        </where>
    </select>
</mapper>
Copy the code

Now that the code transformation is complete, let’s test all of the request scenarios.

Request without passing any parameters

At this point we can pass no parameters (query all data), as shown below:The generated SQL statement is as follows:

A request that passes 1 parameter

You can also pass 1 parameter to query by name, as shown in the following figure:The generated SQL looks like the following figure:You can also query by password only, as shown in the following figure:The generated SQL looks like the following figure:

A request that passes 2 parameters

You can also conduct joint query according to the way of name plus password, as shown in the figure below:The generated SQL looks like the following figure:

Use analytical

We were pleasantly surprised to find that after using tags, it is easy to pass one or more parameters in any query scenario, or pass no parameters at all.

First, the tag determines that if there are no parameters, the WHERE query will not be concatenated in the SQL statement. Otherwise, the WHERE query will be concatenated. Secondly, in the query labels, each label can be added with the and keyword. MyBatis will automatically delete the AND keyword in front of the first condition, so as not to cause SQL syntax errors. This is also explained in the official document, as shown in the figure below:

conclusion

In MyBatis, it is recommended to avoid using meaningless SQL splice where 1=1, we can use tag instead of where 1=1, such writing is concise and elegant, why not? The above content is only personal opinion, welcome to comment in the comments section.

Follow the public account “Java Chinese Community” to see more MyBatis and Spring Boot series of articles.