Several postures of parameter transfer of mybatis in springboot series

Time:2021-12-1

Several postures of parameter transfer of mybatis in springboot series

Several postures of parameter transfer of mybatis in springboot series

In the daily development of mybatis, how do the parameters defined in the mapper interface map to the parameters in XML? In addition to our common@ParamWhat are the other ways besides annotation?

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

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

<!– more –>

1. Environment configuration

We use springboot + mybatis + Mysql to build the instance 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>

Core dependencymybatis-spring-boot-starter, as for version selection, go to the MVN repository to find the latest version

Another unavailable is 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 table

Database for testing

CREATE TABLE `money` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Name ` varchar (20) not null default '' comment 'user name',
  `Money ` int (26) not null default '0' comment 'money',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `create_ at` timestamp NOT NULL DEFAULT CURRENT_ Timestamp comment 'creation time',
  `update_ at` timestamp NOT NULL DEFAULT CURRENT_ TIMESTAMP ON UPDATE CURRENT_ Timestamp comment 'update time',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=551 DEFAULT CHARSET=utf8mb4;

2. Parameter transfer

Next, let’s look at several postures of mapping parameters in mapper interface and parameters in XML file; I will skip the construction of mybatis project 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. Under the resource folder, the directory level is completely consistent with the package path of the mapper interface (follow the default mapper interface and XML file binding relationship. For details, see the mapper interface and SQL binding gestures of the spring boot series mybatis)

<?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

Add on the parameters of the interface@ParamAnnotation to internally specify the parameter name passed to XML

A simple case is as follows

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

Focus on the above parameters

  • adopt@ParamTo specify the parameter name when passed to XML

The SQL in the corresponding XML file is as follows, using#{}To implement 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 look at not using@ParamHow to specify parameters in XML in the default scenario during annotation; Because the actual results of single parameter and multi parameter are inconsistent, they are described separately here

In the single parameter scenario, the parameter name in XML can be represented by any value

The mapper interface is defined as follows

/**
 *In case of a single parameter, it can be directly represented by the parameter name by default. In fact #{}, any value can be used without any restriction. It represents the unique parameter
 * @param id
 * @return
 */
MoneyPo findById(int id);

/**
 *Demonstrate that #{} is a matching string in XML, and parameter replacement can also be implemented correctly
 * @param id
 * @return
 */
MoneyPo findByIdV2(int id);

The corresponding XML file contents are 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 abovefindByIdV2, the parameters passed in the above SQL are#{dd}, and the parameter names in the mapper interface are different, but the final result is no different

3. Multi parameter

When the number of parameters exceeds 1,#{}There are two ways to the parameters in

  • Param1… N: where n represents the number of parameters in the interface
  • arg0…N
/**
 *When the parameter name is not specified, mybatis automatically encapsulates a map of Param1... Paramn, where n represents the nth parameter
 *Arg0... N can also be used to refer to specific parameters
 *
 * @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, both parameters can be passed. Of course, it is not recommended to use this default method to pass parameters, because it is very non intuitive and not elegant for subsequent maintenance

3. Map parameter transfer

If the parameter type is not a simple type, the corresponding key in the map can be directly used to refer to the parameters in the XML file when the map type is selected

/**
 *When the parameter type is map, you can directly use 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 transfer parameters to 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

/**
 *If the parameter type is a Java object, you can also directly use 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 parameter + map parameter

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 above rules
  • The map parameter is passed by 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 paper mainly introduces several postures of parameter transmission in mybatis:

  • By default, in the case of single parameter, any name can be used in the XML file instead of the parameter
  • In the default scenario, when there are multiple parameters, the first parameter can be represented by Param1 or arg0, and the second parameter is param2 or arg1…
  • When it is a single parameter and is a map, you can directly use the key of the map as the transfer parameter
  • Single parameter. When POJO object, use the fieldname of the object to represent the parameter passed
  • @The value defined in the param annotation indicates that this parameter is associated with the placeholder mapping in XML
  • In the multi parameter scenario, when a simple object + map / POJO, you can use theparamN.xxxThe way to complete

Finally, how does mybatis map the parameters in the mapper interface with the placeholders in XML?

How to predict the future, see the details below; I’m a grey. Welcome to pay attention and pay a return visit

3. Can not miss the source code and related knowledge points

0. Project

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

Series blog posts

  • [DB series] basic curd posture in mybatis series tutorials
  • [DB series] mybatis series tutorial: curd basic use posture – Annotation

1. WeChat official account: a gray Blog

The above contents are only the words of one family. Due to limited personal ability, it is inevitable that there are omissions and mistakes. If you find a bug or have better suggestions, you are welcome to criticize and correct and be grateful

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

  • A personal blog https://blog.hhui.top
  • A gray blog spring special blog http://spring.hhui.top

Several postures of parameter transfer of mybatis in springboot series