Mybatis dynamic mapping, this time I finally got it

Time:2021-4-13

Focus on “Java back end technology stack”

Reply to “interview” for full interview information

Dynamic SQL is one of the powerful features of mybatis. If you have used JDBC or other similar frameworks, you should be able to understand how painful it is to splice SQL statements according to different conditions. For example, when splicing, make sure you do not forget to add necessary spaces, and also pay attention to removing the comma at the last column name of the list. Using dynamic SQL, we can get rid of this pain completely.

Using dynamic SQL is not easy, but with the powerful dynamic SQL language that can be used in any SQL mapping statement, mybatis significantly improves the ease of use of this feature.

If you’ve used JSTL or any XML like text processor before, you may feel familiar with dynamic SQL elements. In previous versions of mybatis, it took time to understand a large number of elements. With the powerful expression based on ognl, mybatis 3 replaces most of the previous elements, greatly reducing the types of elements. Now the types of elements to learn are less than half of the original ones.

Mybatis dynamic mapping, this time I finally got it

If: using if to realize simple condition selection.

Choose (when, otherwise): equivalent to the switch statement in Java, usually with when and otherwise.

Set: solve dynamic update statement.

Trim: flexible removal of redundant keywords.

Foreach: iterates over a set, usually for the in condition.

In practice, many times, these tags are used in combination.

Today’s demo usesSpring-Boot+MybatisFor spring boot integration, mybatis recommends:

If + where to realize multi condition query

Create a database table:

CREATE TABLE `m_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `Gender ` int (11) default null comment '0: female 1: Male',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

Initialize several pieces of data:

Mybatis dynamic mapping, this time I finally got it

Let’s see first UserMapper.xml File:

<mapper namespace="com.tian.mybatis.mapper.UserMapper">
    <resultMap id="User" type="com.tian.mybatis.entity.User">
        <id column="id" property="id"/>
        <result column="name" property="userName"/>
    </resultMap>
    <select id="selectUserById"  resultMap="User">
        select * from m_user
        <where>
            <if test="id != null">
                id = #{id}
            </if>
            <if test="name != null and name != ''">
                and `name` = #{name}
            </if>
        </where>
    </select>
</mapper>

UserMapper.java Content:

import com.tian.mybatis.entity.User;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
@Repository
public interface UserMapper {
    User selectUserById(@Param("name") String userName, @Param("id") Integer id);
}

UserService.java Content:

public interface UserService {
    User selectUserById(String userName, Integer id);
}

UserServiceImpl.java Content:

import com.tian.mybatis.entity.User;
import com.tian.mybatis.mapper.UserMapper;
import com.tian.mybatis.service.UserService;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
@Service
public class UserServiceImpl implements UserService {
    @Resource
    private UserMapper userMapper;
    @Override
    public User selectUserById(String userName, Integer id) {
        return userMapper.selectUserById(userName, id);
    }
}

UserController.java Content:

import com.tian.mybatis.entity.User;
import com.tian.mybatis.service.UserService;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
@RestController
public class UserController {
    @Resource
    private UserService userService;
    @GetMapping("/test")
    public User selectUserById() {
        return userService.selectUserById("tian", 1);
    }
}

Application.java Content:

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.tian.mybatis.mapper")
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

Start the project and then visit / test.

http://localhost:9002/test

return:

Mybatis dynamic mapping, this time I finally got it

The above case is also the code case in our work, but some of us use this method.

Mybatis dynamic mapping, this time I finally got it

All of the following demonstrations are based on the above code.

Back to the point.

Where + if is used in the above case. There seems to be a problem in the case

Mybatis dynamic mapping, this time I finally got it

If id = null, isn’t there more and?

We modify the code in the controller

 @GetMapping("/test")
    public User selectUserById() {
        return userService.selectUserById("tian", null);
    }

To enable SQL output, we add a configuration item to the configuration file

logging:
  level:
    com:
      tian:
        mybatis:
          mapper: debug

Again, the output is the same as before. There is no and in the SQL output from the console. This is one of the powerful features of so-called dynamic mapping.

If we don’t use dynamic mapping tags, we are likely to have problems when dealing with or or and.

The where element can intelligently handle the redundant problems of and and or without worrying about the syntax errors caused by redundant keywords.

If the test of the if element is used to determine whether the expression is consistent, then continue to splice the SQL statements.

proposal

It is recommended to use this kind of dynamic tag instead of the original one, because sometimes there is an unexpected judgment that leads to an additional and or, which leads to a bug. In serious cases, a certain function on the line can not be used.

If + trim + foreach to realize multi condition query

Adjust the previous code

<select id="selectUsersByIds" resultMap="User">
        select * from m_user
        <trim prefix="where" prefixOverrides="and | or">
            <if test="idList != null">
                id in
                <foreach collection="idList" item="id" open="(" close=")" separator=",">
                    #{id}
                </foreach>
            </if>
            <if test="gender != null and gender != 0">
                AND gender = #{gender}
            </if>
        </trim>
</select>

UserMapper.java increase

List<User> selectUsersByIds(@Param("idList") List<Integer> idList, @Param("gender") Integer gender);

New method of controller:

 @GetMapping("/users")
    public List<User> selectUsersByIds() {
        List<Integer> idList = new ArrayList<>();
        idList.add(1);
        idList.add(2);
        idList.add(3);
        idList.add(4);
        idList.add(5);
        return userService.selectUsersByIds(idList, null);
    }

Project run, visit

http://localhost:9002/users

Output:

Mybatis dynamic mapping, this time I finally got it

SQL output:

Explain the above related properties

Properties of trim

  • Prefix: prefix: it is used to add a prefix to the contents of trim after automatically identifying whether there is a return value, such as where in the above example.
  • Suffix: the function is to add a suffix to the content contained in the trim.
  • Prefixoverrides:: omit the specified content (such as and | or in the above example) for the header of the trim content;
  • Suffixoverlaps:: omits the specified content for the first part of the content contained in the trim.

Properties of foreach

  • Item: the alias of each element in the collection when it iterates.
  • Index:: specifies a name to indicate the location of each iteration in the process of iteration.
  • Open: indicates the beginning of the statement (since it is an in conditional statement, it must start with ‘(‘)
  • Separator:: indicates which symbol is used as the separator in each iteration (since it is an in conditional statement, it must be ‘,’)
  • Close:: indicates the end of the statement (since it is an in conditional statement, it must end with ‘)’)
  • Collection: the most critical and error prone attribute. It should be noted that the attribute must be specified. In different cases, the value of the attribute is different,

    There are three main situations

Mybatis dynamic mapping, this time I finally got it

@Param is a comment in mybatis. Don’t quote it wrong when you write it, @ param (“name”), here’s the name we are using Mapper.xml The name used in.

In the project, I have seen many people do this, that is, when the where statement is not sure that there will be conditions, use the

slect ...from...where 1=1

See if your code has it, too?

set

The set element can be used to dynamically include columns that need to be updated and ignore other columns that do not need to be updated.

UserMapper.xml newly added

<update id="updateAuthorIfNecessary">
        update m_user
        <set>
            <if test="userName != null and userName != ''">
               `name` = #{userName},
            </if>
            <if test="gender != null and gender != 0">
                gender = #{gender},
            </if>
            <if test="age != null and age != 0">
                age = #{age},
            </if>
        </set>
        where id=#{id}
</update>

UserMapper.java newly added

int updateAuthorIfNecessary(User user);

New controller

@PostMapping("/updateUser")
    public String update() {
        User user = new User();
        user.setAge(18);
        user.setUserName (brother Tian);
        user.setId(1);
        return userService.updateAuthorIfNecessary(user) == 1 ? "success" : "fail";
    }

Restart project, visit

http://localhost:9002/updateUser

Output: success

The data in the database table has been modified successfully

SQL output

In this example, the set element dynamically inserts the set keyword at the beginning of the line and removes extra commas (which are introduced when assigning values to columns using conditional statements).

Another way

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

Let’s adjust the diam in the above XML

 <update id="updateAuthorIfNecessary">
        update m_user
        <trim prefix="SET" suffixOverrides=",">
            <if test="userName != null and userName != ''">
                `name` = #{userName},
            </if>
            <if test="gender != null and gender != 0">
                gender = #{gender},
            </if>
            <if test="age != null and age != 0">
                age = #{age},
            </if>
        </trim>
        where id=#{id}
    </update>

Controller modification:

 @PostMapping("/updateUser")
    public String update() {
        User user = new User();
        user.setAge(19);
        user.setUserName("tian");
        user.setId(1);
        return userService.updateAuthorIfNecessary(user) == 1 ? "success" : "fail";
    }

Finally, look at the SQL output:

Mybatis dynamic mapping, this time I finally got it

Automatically added the set keyword to us. And the database is modified successfully.

Mybatis dynamic mapping, this time I finally got it

choose

It is equivalent to the switch statement in Java, and is usually matched with when and otherwise.

Sometimes, we don’t want to use all the conditions, we just want to use one of them. In this case, mybatis provides the choose element, which is a bit like the switch statement in Java.

Now we continue to use the above case code for demonstration.

UserMapper.xml New method:

<select id="selectUsersByName" resultMap="User">
        select * from m_user where age = 19
        <choose>
            <when test="userName != null and userName != ''">
                and `name` = #{userName}
            </when>
            <otherwise>
                AND gender = 1
            </otherwise>
        </choose>
</select>

New method of controller:

@GetMapping("/user/name")
    public  List<User>  selectUsersByName() {
        return userService.selectUsersByName("tian");
}

return:

SQL output:

Mybatis dynamic mapping, this time I finally got it

Correct output. What if our username is not null?

The output and the above are normal. Let’s look at the SQL output

Mybatis dynamic mapping, this time I finally got it

Because the condition of our username is not satisfied, we directly execute the agent.

above<otherwise>It is similar to the default in switch in our Java syntax. When the previous conditions are not met, the default module is executed.

Bind

This method is not used a lot, but it is also useful.bindElement allows you to create a variable outside of an ognl expression and bind it to the current context. For example:

<select id="selectUserByName" resultType="User">
  <bind name="pattern" value="'%' + userName + '%'" />
  select * from m_user
  WHERE `name` LIKE #{pattern}
</select>

There is also script. There is no need to demonstrate it. It can’t be used in work. It is to unload SQL into Java code. such as

 @Update({"<script>",
      "update m_user",
      "  <set>",
      "    <if test='username != null'>`name`=#{username},</if>",
      "    <if test='gender != null and gender != 0'>gender=#{gender},</if>",
      "  </set>",
      "where id=#{id}",
      "</script>"})
    void updateUserValues(User user);

summary

Part of the knowledge in this article is for demonstration, some code may not be very standard, especially the SQL part. During the development, I personally summarized several points for mybatis development

  • Whether there is an index in the table, and whether it is useful in our SQL when there is an index.
  • Try not to write asterisk * in the return field. It is recommended to write the required field.
  • Keyword suggestions are written in upper case to better distinguish non keywords.
  • Remember to use single quotation marks when the key of the field in the table is the same as that of the database.
  • When using @ param annotation, be sure to use the annotation in mybatis.
  • When using either one parameter or multiple parameters, the annotation @ param is used to specify the name, which is convenient for adding fields again in the future.
  • It is strongly recommended to use dynamic tags to avoid SQL errors with multiple and or keywords, and no need to write where 1 = 1

Recommended reading

My primary school sister asked me: how to use visual tools to check problems?

JVM Zhenxiang series: what is the relationship among method area, heap and stack