MyBatis开发中常用总结

常用MyBatis参数映射

单个参数

XML中可以通过 #{xxx}, #{param1} 来获取Mapper接口中的单个参数没有任何限制，即使前后名字不一致的同时也没有@Param注解别名也能传入给XML

但为了开发规范尽量使用和入参时一样，使用@Param注解约定好名称

// Mapper
User selectUserById(Long id);// XML
<select id="selectUserById" parameterType="long" resultMap="BaseResultMap">SELECT * FROM user WHERE id = #{123}
</select>

多个参数

使用索引【不推荐】

// Mapper
User selectUserByIdAndName(Long id, String username);// XML
<select id="selectUserByIdAndName" resultMap="BaseResultMap">SELECT * FROM user WHERE id = #{param1} AND username = #{param2}
</select>

@Param注解

// Mapper
User selectUserByIdAndName(@Param(value = "id") Long id, @Param(value = "username") String username);// XML
<select id="selectUserByIdAndName" resultMap="BaseResultMap">SELECT * FROM user WHERE id = #{id} AND username = #{username}
</select>

Map传参

Mybatis底层也是通过 Map 传参，因此传入也可以一个 Map 作为参数。Map 中的 key 就对应 XML 中 #{key}

单Map参数

// Mapper
User selectUserByIdAndNameMap(Map<String, Object> map);// XML
<select id="selectUserByIdAndNameMap" parameterType="map" resultMap="BaseResultMap">SELECT * FROM user WHERE id = #{id} AND username = #{username}
</select>

简单类型+map

// Mapper
User selectUserByIdAndNameMap(@Param(value = "id") Long id,  Map<String, Object> map);// XML
<select id="selectUserByIdAndNameMap" resultMap="BaseResultMap">SELECT * FROM user WHERE id = #{id} AND username = #{map.username}
</select>

简单类型+map

// Mapper
User selectUserByIdAndNameMap(@Param(value = "id") Long id,  @Param(value = "map") Map<String, Object> map);// XML
<select id="selectUserByIdAndNameMap" resultMap="BaseResultMap">SELECT * FROM user WHERE id = #{id} AND username = #{map.username}
</select>

测试

Map<String, Object> map=new HashMap<>();
map.put("id", 1);
map.put("username", "Jack");
// 单 map 参数
User user = userMapper.selectUserByIdAndNameMap(map);
// 简单参数+map
User user = userMapper.selectUserByIdAndNameMap(2L, map);

POJO【推荐】

多个参数可以使用实体类封装，key就是属性名，实体类需要有 getXXX()方法

不使用@Param注解

// Mapper
User selectUserByEntity(User user);// XML
<select id="selectUserByEntity" parameterType="app.domain.po.User" resultMap="BaseResultMap">SELECT * FROM user WHERE id = #{id} AND username = #{username}
</select>

使用@Para注解

// Mapper
User selectUserByEntity(@Param(value = "user") User user);// XML
<select id="selectUserByEntity" parameterType="app.domain.po.User" resultMap="BaseResultMap">SELECT * FROM user WHERE id = #{user.id} AND username = #{user.username}
</select>

测试

User user=new User();
user.setId(1L);
user.setUsername("Jack");
User entity = userMapper.selectUserByEntity(user);

List

// Mapper
List<User> selectList(@Param(value = "ids") List<Long> ids);// XML
<select id="selectList" parameterType="list" resultMap="BaseResultMap">SELECT * FROM user WHERE id IN<foreach collection="ids" item="item" separator=", " open="(" close=")">#{item}</foreach>
</select>

测试

List<Long> ids = List.of(1L, 2L, 3L);
List<User> userList = userMapper.selectList(ids);

数组

// Mapper
List<User> selectArray(@Param(value = "ids") Long[] ids);// XML
<select id="selectArray" parameterType="arraylist" resultMap="BaseResultMap">SELECT * FROM user WHERE id IN<foreach collection="ids" item="item" separator=", " open="(" close=")">#{item}</foreach>
</select>

测试

Long[] idsArray = ids.toArray(new Long[0]);
userList = userMapper.selectArray(idsArray);

动态标签

<if>标签

• if标签中的test是传入对象的属性

mapper

int addUser(@Param(value = "userInfo") UserInfo userInfo);

xml

<insert id="addUser" useGeneratedKeys="true" parameterType="app.model.UserInfo" keyColumn="id" keyProperty="id">INSERT INTO userinfo(username,password<if test="userInfo.photo != null">,photo</if>)VALUES(#{userInfo.name},#{userInfo.password}<if test="userInfo.photo != null">,#{userInfo.photo}</if>)
</insert>

<trim>标签

• prefix：表示整个语句块，以prefix的值作为前
  缀
• suffix：表示整个语句块，以suffix的值作为后缀
• prefixOverrides：表示整个语句块要去除掉的前
  缀
• suffixOverrides：表示整个语句块要去除掉的后缀

mapper

int addUser(@Param(value = "userInfo") UserInfo userInfo);

xml

<insert id="addUser" useGeneratedKeys="true" parameterType="app.model.UserInfo" keyColumn="id" keyProperty="id">INSERT INTO userinfo<trim prefix="(" suffix=")" suffixOverrides=",">username,password,<if test="userInfo.photo != null">photo,</if></trim>VALUES<trim prefix="(" suffix=")" suffixOverrides=",">#{userInfo.name},#{userInfo.password},<if test="userInfo.photo != null">#{userInfo.photo}</if></trim>
</insert>

<where>标签

• where标签会自动清除掉第一个if标签中第一个多余的 and字符串，因此也可以用 <trim prefix="where" preffixOverrides="and"></trim> 替换掉where标签。但一般不这么用

List<UserInfo> selectUserByCondition(@Param(value = "userInfo") UserInfo userInfo);

xml

<select id="selectUserByCondition" parameterType="app.model.UserInfo" resultMap="BaseMap">SELECT *FROM userinfo<where><if test="userInfo.name != null">and username = #{userInfo.name}</if><if test="userInfo.photo != null">and photo = #{userInfo.photo}</if><if test="userInfo.createTime != null">and DATE_FORMAT(createtime, '%Y-%m-%d %H:%i:%s') >= DATE_FORMAT(#{userInfo.createTime}, '%Y-%m-%d %H:%i:%s')</if><if test="userInfo.updateTime != null">and DATE_FORMAT(updatetime, '%Y-%m-%d %H:%i:%s') >= DATE_FORMAT(#{userInfo.updateTime}, '%Y-%m-%d %H:%i:%s')</if><if test="userInfo.state != null">and state = #{userInfo.state}</if></where>
</select>

<set>标签

• set标签会自动去除掉最后一个SQL的逗号：“,”。同理，也可以是用 <trime prefix="set" suffixOverridex=",></trim> 替换掉set标签。但一般不这么做

mapper

int updateUserByCondition(@Param("userInfo") UserInfo userInfo);

xml

<update id="updateUserByCondition">UPDATE userinfo<set><if test="userInfo.name != null">username = #{userInfo.name},</if><if test="userInfo.photo != null">photo = #{userInfo.photo},</if></set><where><if test="userInfo.id != null">and id >= #{userInfo.id}</if><if test="userInfo.createTime != null">and DATE_FORMAT(createtime, '%Y-%m-%d %H:%i:%s') >= DATE_FORMAT(#{userInfo.createTime}, '%Y-%m-%d %H:%i:%s')</if></where>
</update>

<foreach>标签

• collection：绑定方法参数中的集合的名称，如 List，Set，Map或数组对象「一般配合@Param注解搭配名称使用」
• open：语句块开头的字符串
• close：语句块结束的字符串
• item：遍历时的每一个对象
• separator：每次遍历之间间隔的字符串

mapper

int deleteUserByIds(@Param("idList") List<Integer> idList);

xml

<delete id="deleteUserByIds">DELETEFROM userinfoWHERE id IN<foreach collection="idList" open="(" close=")" item="id" separator=",">#{id}</foreach>
</delete>

MyBatis查询

一对一

数据库

CREATE TABLE `orders` (`id` int NOT NULL AUTO_INCREMENT,`amount` decimal(11,2) DEFAULT NULL,`uid` int NOT NULL,PRIMARY KEY (`id`,`uid`) USING BTREE
)CREATE TABLE `user` (`id` int NOT NULL AUTO_INCREMENT,`username` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,PRIMARY KEY (`id`)
)

一个用户有多个订单【这里只查询一个订单，一对多中会查询多个订单】，一个订单只从属于一个用户

SELECT o.id  o_id,o.amount,o.uid o_uid,u.id  u_id,u.username
FROM orders oLEFT JOIN `user` u ON o.uid = u.id;

用户实体类

import lombok.Data;@Data
public class User {private Long id;private String username;
}

订单实体类

import lombok.Data;@Data
public class Order {private Long id;private Double amount;private Long uid;// 一对一: 当前订单从属于哪个用户private User user;
}

XML方案一：通过构造新的实体类，resultMap 完成数据封装返回

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="app.mapper.OrderMapper"><resultMap id="OrderResultMap" type="app.domain.po.Order"><!--id: 主键property: 实体类属性column: 查询出来的数据库字段--><id property="id" column="o_id"/><result property="amount" column="amount"/><result property="uid" column="o_uid"/><!-- 一对一 --><result property="user.id" column="u_id"/><result property="user.username" column="username"/></resultMap><select id="selectAllOrder" resultMap="OrderResultMap">SELECT o.id  o_id,o.amount,o.uid o_uid,u.id  u_id,u.usernameFROM orders oLEFT JOIN `user` u ON o.uid = u.id;</select>
</mapper>

XML方案二：使用 resultMap 中的 association 完成封装

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="app.mapper.OrderMapper"><resultMap id="OrderResultMap" type="app.domain.po.Order"><id property="id" column="o_id"/><result property="amount" column="amount"/><result property="uid" column="o_uid"/><!-- 一对一 --><association property="user"><id property="id" column="u_id"/><result property="username" column="username"/></association></resultMap><select id="selectAllOrder" resultMap="OrderResultMap">SELECT o.id  o_id,o.amount,o.uid o_uid,u.id  u_id,u.usernameFROM orders oLEFT JOIN `user` u ON o.uid = u.id;</select>
</mapper>

Mapper接口

List<Order> selectAll();

一对多

数据库

CREATE TABLE `orders` (`id` int NOT NULL AUTO_INCREMENT,`amount` decimal(11,2) DEFAULT NULL,`uid` int NOT NULL,PRIMARY KEY (`id`,`uid`) USING BTREE
)CREATE TABLE `user` (`id` int NOT NULL AUTO_INCREMENT,`username` varchar(256) COLLATE utf8mb4_general_ci DEFAULT NULL,PRIMARY KEY (`id`)
)

一个用户有多个订单，一个订单只从属于一个用户

SELECT u.id  u_id,u.username,o.id  o_id,o.amount,o.uid o_uid
FROM `user` uLEFT JOIN orders o ON u.id = o.uid;

用户实体类

import lombok.Data;import java.util.List;@Data
public class User {private Long id;private String username;// 一对多: 当前用户有哪些订单private List<Order> orderList;
}

订单实体类

import lombok.Data;@Data
public class Order {private Long id;private Double amount;private Long uid;// 一对一: 当前订单从属于哪个用户private User user;
}

XML

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="app.mapper.UserMapper"><resultMap id="UserResultMap" type="app.domain.po.User"><id property="id" column="u_id"/><result property="username" column="username"/><!-- 一对多ofType: 指定集合中的数据类型--><collection property="orderList" ofType="app.domain.po.Order"><id property="id" column="o_id"/><result property="amount" column="amount"/><result property="uid" column="o_uid"/></collection></resultMap><select id="selectAll" resultMap="UserResultMap">SELECT u.id  u_id,u.username,o.id  o_id,o.amount,o.uid o_uidFROM `user` uLEFT JOIN orders o ON u.id = o.uid</select>
</mapper>

Mapper

List<User> selectAll();