BiuBiu

# MyBatis文档

✔️ SpringBoot之前是怎么使用MyBatis操作数据的？

# 日志打印

mybatis自带了SQL打印插件，只需要简单配置就可以，如下

mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
1
2
3

日志打印结果如下。

JDBC Connection [HikariProxyConnection@1277031321 wrapping com.mysql.cj.jdbc.ConnectionImpl@57bb59] will not be managed by Spring
==>  Preparing: SELECT user_id as id,name,phone as tel FROM tb_user WHERE user_id=?
==> Parameters: 10001(Integer)
<==    Columns: id, name, tel
<==        Row: 10001, 马化云腾, 13900889988
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@383c99d3]
1
2
3
4
5
6
7

# xml头部

<?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.demo.DeptDO">

</mapper>
1
2
3
4
5
6
7

# ${}和#{}的区别

List findByUsername(String username);

List findByUsername2(String username);

<!-- 使用#{} -->
<select id="findByUsername" parameterType="java.lang.String" 
    resultType="com.biubiu.entity.User">
    select * from user where username like #{username}
</select>

<!-- 使用${},注意${}中的值必须要填value -->
<select id="findByUsername2" parameterType="java.lang.String" 
    resultType="com.biubiu.entity.User">
    select * from user where username like '%${value}%'
</select>
1
2
3
4
5
6
7
8
9
10
11

List userList = userDao.findByUsername("%小%"); List userList2 = userDao.findByUsername2("小");

  • 上述两种都能查到数据，且都正确
  • 但是${}会产生SQL注入，#{}不会产生SQL注入问题
  • 使用${}的话，里面必须要填value，即：${value}，#{}则随意

比如我们参数 findByUsername2("1' or 1=1; --")和findByUsername("1' or 1=1; --")看日志：

==> Preparing: select * from user where username like '% aaa' or 1=1 --%'       --生成的SQL
==> Parameters:

==> Preparing: select * from user where username like ?         --生成的SQL
==> Parameters: aaa' or 1=1 --(String)
1
2
3
4
5

区别

#{}匹配的是一个占位符，相当于jdbc的占位符？会对一些敏感的字符进行过滤，编译过后会对传递的值加上双引号，因此可以防止SQL注入问题 ${}属于静态文本替换，匹配的是真实传递的值，传递过后，会与sql语句进行字符串拼接。${}会与其他sql进行字符串拼接，不能预防sql注入问题。

为啥#{}可以防止SQL注入： 翻阅源码，发现MyBatis在#{}传递参数时，是借助PreparedStatement类的setString()方法来完成

# Mapper映射文件

if-where、choose-when-otherwise、foreach 动态SQL

<!-- 根据Id查询 -->
<select id="get" resultType="com.bootdo.system.domain.DeptDO">
    select <include refid="base_column"/>
    from sys_dept
    where dept_id = #{value}
</select>

<!--sql片段-->
<sql id="base_column">
    `dept_id`,`parent_id`,`name`,`order_num`,`del_flag`
</sql>

<!-- 列表批量查询 -->
<select id="list" resultType="com.bootdo.system.domain.DeptDO">
    select <include refid="base_column"/>
    from sys_dept
    <where>
        <if test="deptId != null and deptId != ''"> and dept_id = #{deptId} </if>
        <if test="parentId != null and parentId != ''"> and parent_id = #{parentId} </if>
        <if test="name != null and name != ''"> and name = #{name} </if>
        <if test="orderNum != null and orderNum != ''"> and order_num = #{orderNum} </if>
        <if test="delFlag != null and delFlag != ''"> and del_flag = #{delFlag} </if>
    </where>
    <choose>
        <when test="sort != null and sort.trim() != ''">
            order by ${sort} ${order}
        </when>
        <otherwise>
            order by dept_id desc
        </otherwise>
    </choose>
    <if test="offset != null and limit != null">
        limit #{offset}, #{limit}
    </if>
</select>

<!-- 查询条数 -->
<select id="count" resultType="int">
    select count(*) from sys_dept
    <where>
        <if test="deptId != null and deptId != ''"> and dept_id = #{deptId} </if>
        <if test="parentId != null and parentId != ''"> and parent_id = #{parentId} </if>
        <if test="name != null and name != ''"> and name = #{name} </if>
        <if test="orderNum != null and orderNum != ''"> and order_num = #{orderNum} </if>
        <if test="delFlag != null and delFlag != ''"> and del_flag = #{delFlag} </if>
    </where>
</select>

<!-- 插入 -->
<insert id="save" parameterType="com.bootdo.system.domain.DeptDO"
    useGeneratedKeys="true" keyProperty="deptId">
    insert into sys_dept
        (`parent_id`,`name`,`order_num`,`del_flag`)
    values
        (#{parentId},#{name,jdbcType=VARCHAR},#{orderNum},#{delFlag})
</insert>

<!-- 修改 -->
<update id="update" parameterType="com.bootdo.system.domain.DeptDO">
    update sys_dept
    <set>
        <if test="parentId != null">`parent_id` = #{parentId}, </if>
        <if test="name != null">`name` = #{name}, </if>
        <if test="orderNum != null">`order_num` = #{orderNum}, </if>
        <if test="delFlag != null">`del_flag` = #{delFlag}</if>
    </set>
    where dept_id = #{deptId}
</update>

<!-- 删除 -->
<delete id="remove">
    delete from sys_dept where dept_id = #{value}
</delete>

<!-- 批量删除 -->
<delete id="batchRemove">
    delete from sys_dept where dept_id in
    <foreach item="deptId" collection="array" open="(" separator="," close=")">
        #{deptId}
    </foreach>
</delete>

<select id="listParentDept" resultType="long">
    select DISTINCT parent_id from sys_dept
</select>

<select id="getDeptUserNumber" resultType="int">
    select count(*) from sys_user where dept_id = #{value}
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89

结果集映射

<resultMap id="CropResultMap" type="agriculture_basedata.entity.Crop" >
    <id column="id" property="id" jdbcType="BIGINT" />
    <result column="crop" property="crop" jdbcType="VARCHAR" />
    <result column="pic" property="pic" jdbcType="VARCHAR" />
    <result column="crop_pic" property="crop_pic" jdbcType="VARCHAR" />
    <result column="status" property="status" jdbcType="INTEGER" />
    <result column="del" property="del" jdbcType="TINYINT" />
</resultMap>

<sql id="Crop_Column_List" >
    id, crop, status,pic, crop_pic,del
</sql>
1
2
3
4
5
6
7
8
9
10
11
12

批量添加

<insert id="addDayList" parameterType="java.util.List"  useGeneratedKeys="true"  keyProperty="id">
    insert into summary_day
        (energy_type,team,sum,base_data_day,company,equipment_id,total,create_time)
    values
    <foreach collection="gourList" item="hour" separator=",">
        (#{hour.energy_type},#{hour.team},#{hour.sum},
        #{hour.base_data_day},#{hour.company},#{hour.equipment_id},#{hour.total},now())
    </foreach>
</insert>
1
2
3
4
5
6
7
8
9

映射到SQL插入数据

-- 单条插入
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");

-- 批量插入
INSERT INTO `table1` (`field1`, `field2`) 
VALUES ("data1", "data2"),
       ("data1", "data2"),
       ("data1", "data2"),
       ("data1", "data2"),
       ("data1", "data2");
1
2
3
4
5
6
7
8
9
10
11

Mybatis映射文件里批量插入

​```xml
<insert id="batchInsert" parameterType="java.util.List">
    insert into USER (id, name) values
    <foreach collection="list" item="model" index="index" separator=","> 
        (#{model.id}, #{model.name})
    </foreach>
</insert>
1
2
3
4
5
6
7

# MyBatis插件

一个接口：Interceptor
四大对象：

  • Executor 执行器
  • ParameterHandler 参数处理
  • ResultSetHandler 结果集处理
  • StatementHandler sql语法构建器

步骤：

  1. 新建拦截器，实现 Interceptor 接口，重写intercept方法，在 invocation.proceed() 之前之后都能进行拦截逻辑。
  2. 配置拦截得对象，四大对象之一
  3. 实现逻辑
// ExamplePlugin.java
@Intercepts({@Signature(
        type = Executor.class, //哪个对象（四大对象之一）
        method = "update",      //对象的某个方法
        args = {MappedStatement.class, Object.class})}) ////对象的某个方法的参数
public class ExamplePlugin implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        // implement pre processing if need
        Object returnObject = invocation.proceed();
        // implement post processing if need
        return returnObject;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 脱敏插件实战

mybatis插件-脱敏插件 支持自定义策略进行脱敏

← Java 正则和爬虫 →