Some mappings between Mybatis interface parameters and XML placeholders are introduced

Small knowledge, big challenge! This paper is participating in theEssentials for programmers”Creative activities

SpringBoot series of Mybatis parameter transfer several postures

In the daily development of Mybatis, how to map the parameters defined in mapper interface to the parameters in XML? In addition to the @param annotations we often use, what are the other ways?

What if you don’t add annotations to the default scenario?
What if the interface parameter type is Map/POJO?

This article will mainly introduce the daily development of Mybatis, mapper interface defined parameters and XML placeholder mapping binding methods

I. Environment configuration

We use SpringBoot + Mybatis + MySql to build the 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>
Copy the code

The core relies on mybatis-spring-boot-starter. For version selection, go to the MVN repository and find the latest one

Another unavailable db configuration information is 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:
Copy the code

2. Database tables

Database for testing

CREATE TABLE `money` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT ' ' COMMENT 'Username',
  `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;
Copy the code

II. Parameter transfer

Let’s take a look at several ways in which parameters in the Mapper interface can be mapped to parameters in an XML file. As for the construction of mybatis project, I will skip it here, and 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;
}
Copy the code

Mapper interfaces

@Mapper
public interface MoneyMapper {}Copy the code

XML file, in the resource folder, directory level and mapper interface package path is exactly the same (follow the default mapper interface and XML file binding relationship, details see SpringBoot Mybatis mapper interface and Sql binding several postings)


      
<! DOCTYPEmapper 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>
Copy the code

1. @ Param annotation

Add @param annotations to the parameters of the interface to internally specify the parameter names to be passed to the XML

A simple case is as follows

int addMoney(@Param("id") int id, @Param("money") int money);
Copy the code

Focus on the above parameters

through@ParamTo specify the name of the parameter to be 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>
Copy the code

2. Single parameter

Let’s take a look at how parameters should be specified in XML by default without the @param annotation. Because the actual results of single parameter and multi parameter are inconsistent, they are explained separately here

In the single-parameter scenario, parameter names in XML can be expressed with any value

Mapper interfaces are defined as follows

/ * * * a single parameter, the default can be directly through the parameter name to represent, in fact # {} in any one value can be, without any limitation, is this the only parameters * said@param id
 * @return* /
MoneyPo findById(int id);

/** * shows that #{} in XML is a matching string, and that it can also be implemented correctly@param id
 * @return* /
MoneyPo findByIdV2(int id);
Copy the code

The content of the corresponding XML file 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>
Copy the code

FindByIdV2 = #{dd}; mapper = #{dd}

3. Many parameters

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

param1… N: indicates the parameter number of the interface
arg0… N

/** * Mybatis automatically wraps a param1... A Map of paramN, where n represents the NTH argument *. Arg0... N refers to the specific parameter * *@param name
 * @param money
 * @return* /
List<MoneyPo> findByNameAndMoney(String name, Integer money);
Copy the code

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>
Copy the code

Note that in the XML above, it is possible to pass parameters in either way. Of course, it is not recommended to use the default method as it is very unintuitive and not elegant for subsequent maintenance

3. The Map reference

If the parameter type is not simple, the parameter in the XML file can be directly referred to by the corresponding key in the Map

/** * If the parameter type is map, use key *@param map
 * @return* /
List<MoneyPo> findByMap(Map<String, Object> map);
Copy the code

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>
Copy the code

4. A POJO object

Another common case is passing arguments to simple entity objects, where parameters in XML can also be referred to directly by the object’s fieldName, similar to the way maps are used

/** * The argument type is Java object, also directly use the field name *@param po
 * @return* /
List<MoneyPo> findByPo(MoneyPo po);
Copy the code

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>
Copy the code

5. Simple parameters + Map parameters

How to deal with multiple parameters, some of which are simple types and some of which are Map?

Simple types follow the rules above
The map parameter is passed using 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);
Copy the code

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>
Copy the code

6. Summary

This paper mainly introduces several postures of transferring parameters in Mybatis:

By default, any name in an XML file can be used instead of a single parameter
By default, in multi-parameter scenarios, the first parameter can be param1 or arg0, and the second parameter can be param2 or arg1…
If the parameter is map, you can directly use the map key as the transmission parameter
Single-parameter, POJO objects use the object’s fieldName to represent the passed arguments
The value defined in the @param annotation, indicating that this parameter is associated with a placeholder map in XML
In the multi-parameter scenario, if simple objects + Map/POJO, the parameter placeholder in map/ POJO can be passedparamN.xxxTo do so

Finally, how does Mybatis associate mapper interface parameters with placeholders in XML?

How to predict the future, and see the details below; I am a grey, welcome to pay attention to return visit

III. Can’t miss the source code and related knowledge points

0. Project

Project: github.com/liuyueyi/sp…
Source: github.com/liuyueyi/sp…

Series of blog posts

【DB series 】Mybatis series of CURD basic use posture
【DB series 】Mybatis series of CURD basic use posture – notes

1. Wechat official account: Yash Blog

As far as the letter is not as good, the above content is purely one’s opinion, due to the limited personal ability, it is inevitable that there are omissions and mistakes, if you find bugs or have better suggestions, welcome criticism and correction, don’t hesitate to appreciate

Below a gray personal blog, record all the study and work of the blog, welcome everyone to go to stroll

A grey Blog Personal Blog blog.hhui.top
A Grey Blog-Spring feature Blog Spring.hhui.top

Some mappings between Mybatis interface parameters and XML placeholders are introduced

I. Environment configuration

1. Project configuration

2. Database tables

II. Parameter transfer

1. @ Param annotation

2. Single parameter

3. Many parameters

3. The Map reference

4. A POJO object

5. Simple parameters + Map parameters

6. Summary

III. Can’t miss the source code and related knowledge points

0. Project

1. Wechat official account: Yash Blog

Related Posts

Remove Elasticsearch search templates step by step

Depth: Technical exploration of Kafka cluster breaking millions of partitions

Java multithreaded, concurrent programming