“This is the fifth day of my participation in the November Gwen Challenge. See details of the event: The Last Gwen Challenge 2021”.

ParameterType, ResultMap, ResultMap, ResultType when ParameterType, ResultMap, ResultType is ParameterType, ResultMap, ResultType when ParameterType, ResultMap, ResultType are ParameterType, ResultMap, ResultType. What is the difference between Type and Map? When to specify a Type and when not to specify a Type?

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. The Parameter/Result is introduced

1. ParameterMap & ParameterType

These two are mainly used to specify the type of parameter transfer, there is a previous introduction to the posture of parameter transfer interested partners can check [DB series] Mybatis parameter transfer several posture

Parameters passed in Mybatis can generally be divided into two categories

  • Basic data types: int, string, long, Date;
  • Complex data types: classes (Javabeans, Integer, and so on) and Maps

ParameterType is a basic ParameterType. You do not need to specify ParameterType when writing SQL in XML

List<MoneyPo> queryByName(@Param("name") String name);


<select id="queryByName"  parameterType="java.lang.String" resultMap="BaseResultMap">
    select * from money where `name` = #{name}
</select>
Copy the code

There are two common scenarios where you will see ParmeterType, such as passing the parameter as Map

List<MoneyPo> queryByCondition(Map<String, Object> params); <select id="queryByCondition" resultMap="BaseResultMap" parameterType="java.util.Map"> select <include refid="money_po"/> from money where 1=1 <if test="id ! = null"> and id = #{id} </if> <if test="name ! = null"> and `name` = #{name} </if> <if test="is_deleted ! = null"> and `is_deleted` = #{is_deleted} </if> </select>Copy the code

If the parameter is a Java bean, it can be as follows

@Data
public class QueryBean {
    private String name;
    private Long id;
}

List<MoneyPo> queryByBean(QueryBean queryBean);

<select id="queryByBean" resultMap="BaseResultMap" parameterType="com.git.hui.boot.mybatis.entity.QueryBean">
    select
    <include refid="money_po"/>
    from money where 1=1
    <if test="id ! = null">
        and id = #{id}
    </if>
    <if test="name ! = null">
        and `name` = #{name}
    </if>
</select>
Copy the code

instructions

  • In the above cases, it is possible to specify noneparameterType

All of this is parameterType. When will parameterMap be used?

In addition to specifying it in #{}, parameterMap is also a good option when we want to do some TypeHandler for certain query conditions, such as

List<MoneyPo> queryByNameV2(Map<String, Object> params);
Copy the code

StrTypeHandler is a custom type capture, no matter what type is passed, it will be converted to String, okay

<parameterMap id="queryMap" type="java.util.Map">
    <parameter property="name" typeHandler="com.git.hui.boot.mybatis.handler.StrTypeHandler"/>
</parameterMap>

<select id="queryByNameV2" parameterMap="queryMap" resultMap="BaseResultMap">
    select * from money where `name` = #{name}
</select>
Copy the code

2. Finally verify our use

The following figure shows the core data in the DB

public void testV4(a) {
    System.out.println(moneyMapperV4.queryByName("1"));

    Map<String, Object> map = new HashMap<>();
    map.put("id".1L);
    map.put("name"."A Gray Blog");
    System.out.println(moneyMapperV4.queryByCondition(map));

    QueryBean queryBean = new QueryBean();
    queryBean.setId(1L);
    queryBean.setName("A Gray Blog");
    System.out.println(moneyMapperV4.queryByBean(queryBean));


    Map<String, Object> map2 = new HashMap<>();
    map2.put("name".120L); // Even if the Long type is passed in, it will be converted to a string type by TypeHandler when it is finally passed to mysql
    System.out.println(moneyMapperV4.queryByCondition(map2));
}
Copy the code

The output is as follows

[] [MoneyPo(id=1, name= 1, money=100, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0, CNT =null, bank=null)] [MoneyPo(id=1, name= 1, money=100, isDeleted=0, createAt=2019-04-18 17:01:40.0, UpdateAt =2019-04-18 17:01:40.0, CNT =null, bank=null)] [MoneyPo(id=120, name=120, money=200, isDeleted=0, CreateAt =2021-05-24 20:04:39, updateAt=2021-09-27 19:21:40, CNT =null, bank=null)]Copy the code

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
  • 【DB series 】 Several posture of Mybatis parameter transfer
  • 【DB series 】 The use of escape characters in Mybatis
  • 【DB series 】 How to determine the type of Mybatis parameter

1. Wechat official account: Yash Blog

All letter is better than no book, the above content, purely one’s words, due to the limited personal ability, it is hard to avoid omissions and mistakes, such as finding bugs or better suggestions, welcome criticism and correction, not grudging gratitude

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