Several postures of parameter transfer of SpringBoot series Mybatis

Several postures for parameter transfer of SpringBoot series Mybatis

In the daily development of mybatis, how do the parameters defined in the mapper interface map with the parameters in xml? Except for our usual @Param annotation, what are the other methods?

• What happens to the default scene without annotations?
• What should I do if the interface parameter type is Map/POJO?

This article will mainly introduce several mapping binding methods between the parameters defined in the mapper interface and the placeholders in xml in the daily development of mybatis

<!-- more -->

I. Environment configuration

We use SpringBoot + Mybatis + MySql to build an example demo

• springboot: 2.2.0.RELEASE
• mysql: 5.7.22

1. Project configuration

<dependencies>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.2.0</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
</dependencies>

The core dependency is mybatis-spring-boot-starter . As for the version selection, go to the mvn repository and find the latest

Another inaccessible is the db configuration information, appliaction.yml

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
    username: root
    password:

2. Database tables

Database for testing

CREATE TABLE `money` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '用户名',
  `money` int(26) NOT NULL DEFAULT '0' COMMENT '钱',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=551 DEFAULT CHARSET=utf8mb4;

II. Parameter Passing

Next, let's take a look at the several postures of the parameter mapping in the Mapper interface and the parameter in the xml file; about the construction of the mybatis project, skip it here, the key information is as follows

Database entity object

@Data
public class MoneyPo {
    private Integer id;

    private String name;

    private Long money;

    private Integer isDeleted;

    private Timestamp createAt;

    private Timestamp updateAt;

    private Integer cnt;
}

mapper interface

@Mapper
public interface MoneyMapper {
}

xml file, in the resource folder, the directory level is exactly the same as the package path of the mapper interface (following the default Mapper interface and xml file binding relationship, see SpringBoot series Mybatis Mapper interface and Sql binding several postures )

<?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.git.hui.boot.mybatis.mapper.MoneyMapper">

    <resultMap id="BaseResultMap" type="com.git.hui.boot.mybatis.entity.MoneyPo">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="money" property="money" jdbcType="INTEGER"/>
        <result column="is_deleted" property="isDeleted" jdbcType="TINYINT"/>
        <result column="create_at" property="createAt" jdbcType="TIMESTAMP"/>
        <result column="update_at" property="updateAt" jdbcType="TIMESTAMP"/>
    </resultMap>
    <sql id="money_po">
      id, name, money, is_deleted, create_at, update_at
    </sql>
</mapper>

1. @Param annotation

@Param annotation to the interface parameter, and specify the parameter name passed to xml internally

A simple case is as follows

int addMoney(@Param("id") int id, @Param("money") int money);

Focus on the above parameters

• Pass @Param to specify the parameter name when passed to xml

The sql in the corresponding xml file is as follows, use #{} to realize parameter binding

<update id="addMoney" parameterType="java.util.Map">
    update money set money=money+#{money} where id=#{id}
</update>

2. Single parameter

Next, let's take a look at @Param annotation is not used in the default scenario; because the actual results of a single parameter and multiple parameters are inconsistent, here is a separate description

In the single parameter scenario, the parameter name in xml can be indicated by any value

The mapper interface is defined as follows

/**
 * 单个参数时，默认可以直接通过参数名来表示，实际上#{}中用任意一个值都可以，没有任何限制，都表示的是这个唯一的参数
 * @param id
 * @return
 */
MoneyPo findById(int id);

/**
 * 演示xml中的 #{} 为一个匹配补上的字符串，也可以正确的实现参数替换
 * @param id
 * @return
 */
MoneyPo findByIdV2(int id);

The corresponding xml file content is as follows

<select id="findById" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select
    <include refid="money_po"/>
    from money where id=#{id}
</select>

<select id="findByIdV2" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select
    <include refid="money_po"/>
    from money where id=#{dd}
</select>

Focus on the above findByIdV2 , the above sql parameter used is #{dd} , which is not the same as the parameter name in the mapper interface, but the final result is no different

3. Multiple parameters

When the number of parameters exceeds 1, there are two ways for the parameters in #{}

• param1...N: where n represents the number of parameters in the interface
• arg0...N

/**
 * 不指定参数名时，mybatis自动封装一个  param1 ... paramN的Map，其中n表示第n个参数
 * 也可以使用 arg0...n 来指代具体的参数
 *
 * @param name
 * @param money
 * @return
 */
List<MoneyPo> findByNameAndMoney(String name, Integer money);

The corresponding xml is as follows

<select id="findByNameAndMoney" resultMap="BaseResultMap">
    select
    <include refid="money_po"/>
    -- from money where name=#{param1} and money=#{param2}
    from money where name=#{arg0} and money=#{arg1}
</select>

Note that in the above xml, two kinds of parameter transfer are possible, of course, it is not recommended to use this default method to transfer parameters, because it is very unintuitive and very inelegant for subsequent maintenance

3. Map parameter transfer

If the parameter type is not a simple type, at the time of the Map type, the parameter in the xml file can be directly referred to by the corresponding key in the map

/**
 * 参数类型为map时，直接使用key即可
 * @param map
 * @return
 */
List<MoneyPo> findByMap(Map<String, Object> map);

The corresponding xml is as follows

<select id="findByMap" resultMap="BaseResultMap">
    select
    <include refid="money_po"/>
    from money
    <trim prefix="WHERE" prefixOverrides="AND | OR">
        <if test="id != null">
            id = #{id}
        </if>
        <if test="name != null">
            AND name=#{name}
        </if>
        <if test="money != null">
            AND money=#{money}
        </if>
    </trim>
</select>

4. POJO Object

Another common case is to pass parameters as simple entity objects. At this time, the parameters in xml can also be directly referred to by the fieldName of the object, which is similar to the use of map.

/**
 * 参数类型为java对象，同样直接使用field name即可
 * @param po
 * @return
 */
List<MoneyPo> findByPo(MoneyPo po);

The corresponding xml file is as follows

<select id="findByPo" parameterType="com.git.hui.boot.mybatis.entity.MoneyPo" resultMap="BaseResultMap">
    select
    <include refid="money_po"/>
    from money
    <trim prefix="WHERE" prefixOverrides="AND | OR">
        <if test="id != null">
            id = #{id}
        </if>
        <if test="name != null">
            AND name=#{name}
        </if>
        <if test="money != null">
            AND money=#{money}
        </if>
    </trim>
</select>

5. Simple parameters + Map parameters

When there are multiple parameters, some of which are simple types and some are Maps, how to deal with the parameters in such a scenario?

• Simple types follow the rules above
• To pass parameters of the map parameter, use the prefix + "." + key

An example is as follows

List<MoneyPo> findByIdOrCondition(@Param("id") int id, @Param("map") Map<String, Object> map);

List<MoneyPo> findByIdOrConditionV2(int id, Map<String, Object> map);

The corresponding xml is as follows

<select id="findByIdOrCondition" resultMap="BaseResultMap">
    select <include refid="money_po"/> from money where id = #{id} or  `name`=#{map.name}
</select>

<select id="findByIdOrConditionV2" resultMap="BaseResultMap">
    select <include refid="money_po"/> from money where id = #{param1} or `name`=#{param2.name}
</select>

6. Summary

This article mainly introduces several postures in mybatis:

• In the default scenario, when there is a single parameter, any name can be used instead of the parameter in the xml file
• In the default scenario, when there are multiple parameters, the first parameter can be represented by param1 or arg0, and the second parameter can be param2 or arg1. . .
• When it is a single parameter and a map, you can directly use the key of the map as a parameter
• Single parameter, when pojo object, the fieldName of the object is used to represent the parameter passing
• The value defined in the @Param annotation indicates that this parameter is associated with the placeholder mapping in xml
• In a multi-parameter scenario, when a simple object + map/pojo is used, the parameter occupancy in map/pojo can be paramN.xxx by way of 0614f2b6fe09a6

The last question is, how does mybatis associate the parameters in the mapper interface with the placeholders in the xml?

Foresee the future, let’s see the details below; I’m in a ash, welcome everyone to pay attention to the return visit

III. Source code and related knowledge points not to be missed

0. Project

• Engineering: https://github.com/liuyueyi/spring-boot-demo
• Source code: https://github.com/liuyueyi/spring-boot-demo/tree/master/spring-boot/103-mybatis-xml

series of blog posts

• [DB Series] The basic use posture of CURD in Mybatis series of tutorials
• [DB series] Mybatis series of tutorials CURD basic use posture-notes

1. WeChat public account: Yi Hui Hui Blog

It is not as good as the letter. The above content is purely a family statement. Due to limited personal ability, it is inevitable that there will be omissions and errors. If you find a bug or have better suggestions, criticisms and corrections are welcome, and I am grateful.

The following is a gray personal blog, which records all the blog posts in study and work. Welcome everyone to visit

• Yi Hui Hui Blog Personal Blog https://blog.hhui.top
• Blog-Spring Special Blog 1614f2b6fe0b53 http://spring.hhui.top