浅谈Mybatis之参数传递的几种姿势

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

spring:

   datasource:

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

     username: root

     password:

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;

@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

public interface MoneyMapper {

}

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

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

< update id = "addMoney" parameterType = "java.util.Map" >

     update money set money=money+#{money} where id=#{id}

</ update >

/**

  * 单个参数时，默认可以直接通过参数名来表示，实际上#{}中用任意一个值都可以，没有任何限制，都表示的是这个唯一的参数

  * @param id

  * @return

  */

MoneyPo findById( int id);

/**

  * 演示xml中的 #{} 为一个匹配补上的字符串，也可以正确的实现参数替换

  * @param id

  * @return

  */

MoneyPo findByIdV2( int id);

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

/**

  * 不指定参数名时，mybatis自动封装一个  param1 ... paramN的Map，其中n表示第n个参数

  * 也可以使用 arg0...n 来指代具体的参数

  *

  * @param name

  * @param money

  * @return

  */

List<MoneyPo> findByNameAndMoney(String name, Integer money);

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

/**

  * 参数类型为map时，直接使用key即可

  * @param map

  * @return

  */

List<MoneyPo> findByMap(Map<String, Object> map);

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

/**

  * 参数类型为java对象，同样直接使用field name即可

  * @param po

  * @return

  */

List<MoneyPo> findByPo(MoneyPo po);

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

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

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

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