MyBatis常用操作示例

發表於 | 分類於 | | 閱讀次數:

该篇主要是Mybatis在日常开发中的使用积累, 对于Intellij IDEA 推荐一款Mybatis插件 Free Mybatis

Mybatis生成插件

mybatis-generator是一个可以生成mybatis通用代码的maven插件

插件引入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<build>
    <plugins>
        <!-- mybatis 代码生成器插件 mybatis-generator:generate-->
        <plugin>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-maven-plugin</artifactId>
            <version>1.3.2</version>
            <configuration>
                <verbose>true</verbose>
                <overwrite>true</overwrite>
            </configuration>
            <dependencies>
                <dependency>
                    <groupId>mysql</groupId>
                    <artifactId>mysql-connector-java</artifactId>
                    <version>5.1.37</version>
                </dependency>
            </dependencies>
        </plugin>
    </plugins>
</build>

生成命令

1
mvn mybatis-generator:generate

配置说明

工程目录下:src/main/resources/generatorConfig.xml

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
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<!-- 使用 mvn mybatis-generator:generate -->
<generatorConfiguration>
    <!-- 配置文件
    <properties resource="classpath*:property/jdbc.properties" />
     -->
    <context id="dbTestTables" targetRuntime="MyBatis3" defaultModelType="flat">

        <!-- plugin -->
        <plugin type="org.mybatis.generator.plugins.SerializablePlugin" />

        <!-- 是否取消生成的代码注释 true : 取消 false: 保留 -->
        <commentGenerator>
            <property name="suppressAllComments" value="false" />
        </commentGenerator>

        <!-- 连接配置 -->
        <jdbcConnection driverClass="com.mysql.jdbc.Driver"
                        connectionURL="jdbc:mysql://devel.mysql.com:3306/db_test?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"
                        userId="root"
                        password="rootpwd">
        </jdbcConnection>

        <!-- javaModelGenerator是模型的生成信息，这里将指定这些Java model类的生成路径 -->
        <javaModelGenerator targetPackage="com.tutorial.mybatis.gen.domain"
                            targetProject="/Users/elonsu/IdeaProjects/examples/elonsu-batisgen-example/src/main/java">
            <property name="enableSubPackages" value="true" />
            <property name="trimStrings" value="true" />
        </javaModelGenerator>

        <!-- 生成Mapper 配置文件 -->
        <sqlMapGenerator targetPackage="mapper/xml"
                         targetProject="/Users/elonsu/IdeaProjects/examples/elonsu-batisgen-example/src/main/resources">
            <property name="enableSubPackages" value="true" />
        </sqlMapGenerator>

        <!-- javaClientGenerator是应用接口的生成信息 -->
        <javaClientGenerator targetPackage="com.tutorial.mybatis.gen.mapper"
                             type="XMLMAPPER"
                             targetProject="/Users/elonsu/IdeaProjects/examples/elonsu-batisgen-example/src/main/java">
            <property name="enableSubPackages" value="true" />
        </javaClientGenerator>

        <!-- oracle 使用 schema 对应 用户名称空间 mysql 使用 catalog 对应 数据库, xByExample取掉生成的Example接口相关 -->
        <table tableName="tb_person" domainObjectName="Person"
               enableCountByExample="false"
               enableDeleteByExample="false"
               enableUpdateByExample="false"
               selectByExampleQueryId="false"
               enableSelectByExample="false">
            <generatedKey column="id" sqlStatement="MySql" identity="true"/>
        </table>
        <table tableName="tb_order" domainObjectName="Order"
               enableCountByExample="false"
               enableDeleteByExample="false"
               enableUpdateByExample="false"
               selectByExampleQueryId="false"
               enableSelectByExample="false">
            <generatedKey column="id" sqlStatement="MySql" identity="true"/>
        </table>
    </context>

</generatorConfiguration>

Mybatis常用业务SQL

示例库表

1
2
3
4
5
6
7
8
9
CREATE TABLE `tb_test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(64) DEFAULT NULL COMMENT '名称',
  `age` bigint(20) DEFAULT '20',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录建立时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试表'

示例模型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class TbTest implements Serializable {

    private Long id;

    private String name;

    private Long age;

    private Date createTime;

    private Date updateTime;
}

示例操作

插入并返回主键
接口定义
1
public int insertFullId(TbTest tbTest);
Xml编写
1
2
3
4
5
6
<insert id="insertFullId" useGeneratedKeys="true" keyProperty="id" parameterType="com.boot.stream.domain.mint.TbTest">
insert into tb_test
 (id, name, age, create_time, update_time)
values
 (#{id}, #{name}, #{age},#{createTime}, #{updateTime})
</insert>
用例测试
1
2
3
4
5
6
7
8
9
@Test
public void insertFullId(){
   Date now = new Date();
   TbTest tbTest = TbTest.builder()
           .age(20L).name("Elonsu").createTime(now).updateTime(now).build();
   tbTestMapper.insertFullId(tbTest);
   // {"age":20,"createTime":1505443787809,"id":3,"name":"Elonsu","updateTime":1505443787809}
   System.out.println(JSON.toJSONString(tbTest));
}
SQL执行
1
2
3
DEBUG:2017-09-15 10:49:48.041[debug] ==>  Preparing: insert into tb_test (id, name, age, create_time, update_time) values (?, ?, ?,?, ?)
DEBUG:2017-09-15 10:49:48.070[debug] ==> Parameters: null, Elonsu(String), 20(Long), 2017-09-15 10:49:47.809(Timestamp), 2017-09-15 10:49:47.809(Timestamp)
DEBUG:2017-09-15 10:49:48.086[debug] <==    Updates: 1
执行结果

插入或者更新(记录存在则更新)
接口定义
1
public void batchUpset(@Param("list") List<TbTest> list);
Xml编写
1
2
3
4
5
6
7
8
9
10
11
12
13
<insert id="batchUpset" parameterType="java.util.Collection">
    insert into tb_test
      (name, age, create_time, update_time)
    values
    <foreach collection="list" item="li" separator=",">
      <trim prefix="(" suffix=")" suffixOverrides=",">
        #{li.name}, #{li.age}, #{li.createTime}, #{li.updateTime}
      </trim>
    </foreach>
    on duplicate key update
      age = values(age),
      update_time = values(update_time)
</insert>
用例测试
1
2
3
4
5
6
7
8
9
@Test
public void batchUpset(){
   Date now = new Date();
   TbTest tbTest1 = TbTest.builder()
           .age(22L).name("Elonsu").createTime(now).updateTime(now).build();
   TbTest tbTest2 = TbTest.builder()
           .age(23L).name("Dennisit").createTime(now).updateTime(now).build();
   tbTestMapper.batchUpset(Lists.newArrayList(tbTest1, tbTest2));
}
SQL执行
1
2
3
DEBUG:2017-09-15 10:54:03.623[debug] ==>  Preparing: insert into tb_test (name, age, create_time, update_time) values ( ?, ?, ?, ? ) , ( ?, ?, ?, ? ) on duplicate key update age = values(age), update_time = values(update_time)
DEBUG:2017-09-15 10:54:03.680[debug] ==> Parameters: Elonsu(String), 22(Long), 2017-09-15 10:54:03.212(Timestamp), 2017-09-15 10:54:03.212(Timestamp), Dennisit(String), 23(Long), 2017-09-15 10:54:03.212(Timestamp), 2017-09-15 10:54:03.212(Timestamp)
DEBUG:2017-09-15 10:54:03.721[debug] <==    Updates: 3
执行结果

批量更新(根据主键更新)
接口定义
1
public void batchUpdate(@Param("list") List<TbTest> list);
Xml编写
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<update id="batchUpdate"  parameterType="java.util.Collection">
    <foreach collection="list" item="li" index="index" open="" close="" separator=";">
      update tb_test
      <set >
        <if test="li.name != null" >
          name = #{li.name},
        </if>
        <if test="li.age != null" >
          age = #{li.age},
        </if>
        <if test="li.createTime != null" >
          create_time = #{li.createTime},
        </if>
        <if test="li.updateTime != null" >
          update_time = #{li.updateTime},
        </if>
      </set>
      where id = #{li.id}
    </foreach>
 </update>
用例测试
1
2
3
4
5
6
7
@Test
public void batchUpdate(){
   Date now = new Date();
   TbTest tbTest1 = new TbTest(3L, "苏若年", 88L, null, now);
   TbTest tbTest2 = new TbTest(4L, "墨少白", 99L, null, now);
   tbTestMapper.batchUpdate(Lists.newArrayList(tbTest1, tbTest2));
}
SQL执行
1
2
3
DEBUG:2017-09-15 11:00:46.741[debug] ==>  Preparing: update tb_test SET name = ?, age = ?, update_time = ? where id = ? ; update tb_test SET name = ?, age = ?, update_time = ? where id = ?
DEBUG:2017-09-15 11:00:46.776[debug] ==> Parameters: 苏若年(String), 88(Long), 2017-09-15 11:00:46.675(Timestamp), 3(Long), 墨少白(String), 99(Long), 2017-09-15 11:00:46.675(Timestamp), 4(Long)
DEBUG:2017-09-15 11:00:46.797[debug] <==    Updates: 1
执行结果

批量插入
接口定义
1
public void batchInsert(@Param("list") List<TbTest> list);
Xml编写
1
2
3
4
5
6
7
8
9
10
<insert id="batchInsert" useGeneratedKeys="true" keyProperty="id" parameterType="java.util.Collection">
    insert into tb_test
      (name, age, create_time, update_time)
    values
    <foreach collection="list" item="li" index="index" separator=",">
      <trim prefix="(" suffix=")" suffixOverrides=",">
        #{li.name}, #{li.age}, #{li.createTime}, #{li.updateTime}
      </trim>
    </foreach>
</insert>
用例测试
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@Test
public void batchInsert(){
   Date now = new Date();
   TbTest tbTest1 = TbTest.builder()
           .age(66L).name("mock1").createTime(now).updateTime(now).build();
   TbTest tbTest2 = TbTest.builder()
           .age(77L).name("mock2").createTime(now).updateTime(now).build();
   tbTestMapper.batchInsert(Lists.newArrayList(tbTest1, tbTest2));

   // tbTest1: {"age":66,"createTime":1505445581768,"id":8,"name":"mock1","updateTime":1505445581768}
   System.out.println("tbTest1: " + JSON.toJSONString(tbTest1));

   // tbTest2: {"age":77,"createTime":1505445581768,"id":9,"name":"mock2","updateTime":1505445581768}
   System.out.println("tbTest2: " + JSON.toJSONString(tbTest2));
}
SQL执行
1
2
3
DEBUG:2017-09-15 11:19:41.823[debug] ==>  Preparing: insert into tb_test (name, age, create_time, update_time) values ( ?, ?, ?, ? ) , ( ?, ?, ?, ? )  
DEBUG:2017-09-15 11:19:41.862[debug] ==> Parameters: mock1(String), 66(Long), 2017-09-15 11:19:41.768(Timestamp), 2017-09-15 11:19:41.768(Timestamp), mock2(String), 77(Long), 2017-09-15 11:19:41.768(Timestamp), 2017-09-15 11:19:41.768(Timestamp) 
DEBUG:2017-09-15 11:19:41.882[debug] <==    Updates: 2
执行结果

动态表/字段传递
接口定义
1
public TbTest columnDynamic(@Param("column") String column, @Param("val") String s);
Xml编写
1
2
3
<select id="columnDynamic" resultType="com.boot.stream.domain.mint.TbTest">
    select ${column} from tb_test where ${column} = #{val}
 </select>
用例测试
1
2
3
4
5
6
7
8
@Test
public void dynamicColumn(){
   // {"name":"苏若年"}
   System.out.println(JSON.toJSONString(tbTestMapper.columnDynamic("name", "苏若年")));

   // {"age":99}
   System.out.println(JSON.toJSONString(tbTestMapper.columnDynamic("age", "99")));
}
SQL执行
1
2
3
4
5
6
7
DEBUG:2017-09-15 11:39:10.585[debug] ==>  Preparing: select name from tb_test where name = ?  
DEBUG:2017-09-15 11:39:10.619[debug] ==> Parameters: 苏若年(String) 
DEBUG:2017-09-15 11:39:10.656[debug] <==      Total: 1 

DEBUG:2017-09-15 11:39:10.675[debug] ==>  Preparing: select age from tb_test where age = ?  
DEBUG:2017-09-15 11:39:10.675[debug] ==> Parameters: 99(String) 
DEBUG:2017-09-15 11:39:10.686[debug] <==      Total: 1
MyBatis中使用#和$书写占位符有什么区别

说明: 在动态sql解析过程，#{}与 ${}的效果是不一样的, #将传入的数据都当成一个字符串，会对传入的数据自动加上引号；$将传入的数据直接显示生成在SQL中.

  • #{}将传入的参数当成一个字符串，会给传入的参数加一个双引号
  • ${}将传入的参数直接显示生成在sql中，不会添加引号
  • #{}能够很大程度上防止sql注入，${}无法防止sql注入
  • ${}在预编译之前已经被变量替换了，这会存在sql注入的风险
  • 写order by子句的时候应该用${}而不是#{}

接下来展示一个sql注入的示例

接口定义
1
public List<TbTest> tableDynamic(@Param("tableName") String tableName);
Xml编写
1
2
3
<select id="tableDynamic" resultType="com.boot.stream.domain.mint.TbTest" statementType="STATEMENT">
    select * from ${tableName} where 1=1
</select>
用例测试
1
2
3
4
5
6
7
@Test
public void sqlInject(){
   // [{"age":88,"id":3,"name":"苏若年"},{"age":99,"id":4,"name":"墨少白"},{"age":66,"id":8,"name":"mock1"},{"age":77,"id":9,"name":"mock2"}]
   System.out.println(JSON.toJSONString(tbTestMapper.tableDynamic("tb_test; delete from tb_test; --")));
   // []
   System.out.println(JSON.toJSONString(tbTestMapper.tableDynamic("tb_test")));
}
SQL执行
1
2
3
4
5
6
7
8
DEBUG:2017-09-15 12:06:49.867[debug] ==>  Preparing: select * from tb_test; delete from tb_test; -- where 1=1
DEBUG:2017-09-15 12:06:49.868[debug] ==> Parameters:
DEBUG:2017-09-15 12:06:49.876[debug] <==      Total: 4
DEBUG:2017-09-15 12:06:49.876[debug] <==    Updates: 4

DEBUG:2017-09-15 12:06:49.878[debug] ==>  Preparing: select * from tb_test where 1=1
DEBUG:2017-09-15 12:06:49.879[debug] ==> Parameters:
DEBUG:2017-09-15 12:06:49.882[debug] <==      Total: 0

可以看到,因为SQL注入,导致表中所有数据被清除.

解释一下MyBatis中命名空间（namespace）的作用

在大型项目中, 可能存在大量的SQL语句，这时候为每个SQL语句起一个唯一的标识（ID）就变得并不容易了。为了解决这个问题，在MyBatis中，可以为每个映射文件起一个唯一的命名空间，这样定义在这个映射文件中的每个SQL语句就成了定义在这个命名空间中的一个ID。只要我们能够保证每个命名空间中这个ID是唯一的，即使在不同映射文件中的语句ID相同，也不会再产生冲突了。

批量主键查询
接口定义
1
public List<TbTest> selectByIds(@Param("list") List<Long> ids);
Xml编写
1
2
3
4
5
6
7
8
9
10
<select id="selectByIds" resultType="com.boot.stream.domain.mint.TbTest">
	SELECT
	 <include refid="sqlColumnList"/>
	FROM
	 tb_test
	where id IN
	<foreach collection="list" item="li" open="(" separator="," close=")">
	 #{li}
	</foreach>
</select>
用例测试
1
2
3
4
5
@Test
public void selectByIds(){
   // [{"age":88,"createTime":1505455272000,"id":10,"name":"苏若年","updateTime":1505455274000},{"age":99,"createTime":1505455272000,"id":11,"name":"墨少白","updateTime":1505455274000},{"age":99,"createTime":1505455272000,"id":12,"name":"林允儿","updateTime":1505455274000}]
   System.out.println(JSON.toJSONString(tbTestMapper.selectByIds(Lists.newArrayList(10L, 11L, 12L))));
}
SQL执行
1
2
3
EBUG:2017-09-15 14:15:32.061[debug] ==>  Preparing: SELECT id AS id, name AS name, age AS age, create_time AS createTime, update_time AS updateTime FROM tb_test where id IN ( ? , ? , ? )  
DEBUG:2017-09-15 14:15:32.089[debug] ==> Parameters: 10(Long), 11(Long), 12(Long) 
DEBUG:2017-09-15 14:15:32.123[debug] <==      Total: 3
动态条件查询
接口定义
1
public List<TbTest> selectByCondition(@Param("object") TbTest tbTest);
Xml编写
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
<!-- 根据条件查询 -->
<select id="selectByCondition" resultType="com.boot.stream.domain.mint.TbTest">
SELECT
 <include refid="sqlColumnList"/>
FROM
 tb_test
<include refid="sqlQueryCondition"/>
<include refid="sqlSortByList"/>
</select>

<!-- 表字段集 -->
<sql id="sqlColumnList">
<trim suffixOverrides=",">
 id AS id, name AS name, age AS age, create_time AS createTime, update_time AS updateTime,
</trim>
</sql>

<!-- 查询条件 -->
<sql id="sqlQueryCondition">
<trim suffixOverrides="," prefixOverrides="AND|OR">
 <where>
   <choose>
     <when test="object.id > 0">
       id = #{object.id}
     </when>
     <otherwise>
       <if test="object.name != null">
         AND name = #{object.name}
       </if>
       <if test="object.age != null">
         AND age = #{object.age}
       </if>
       <if test="object.createTime">
         AND create_time = #{object.createTime}
       </if>
       <if test="object.updateTime">
         AND update_time = #{object.updateTime}
       </if>
     </otherwise>
   </choose>
 </where>
</trim>
</sql>

<!-- 结果集排序集合 -->
<sql id="sqlSortByList">
ORDER BY
<trim suffixOverrides=",">
 id DESC, update_time DESC, create_time DESC, age DESC,
</trim>
</sql>
用例测试
1
2
3
4
5
6
7
8
@Test
public void selectByCondition(){
   // 条件含主键查询:[{"age":88,"createTime":1505455272000,"id":10,"name":"苏若年","updateTime":1505455274000}]
   System.out.println("条件含主键查询:" + JSON.toJSONString(tbTestMapper.selectByCondition(TbTest.builder().id(10L).build())));

   // 条件非主键查询:[{"age":99,"createTime":1505455272000,"id":12,"name":"林允儿","updateTime":1505455274000},{"age":99,"createTime":1505455272000,"id":11,"name":"墨少白","updateTime":1505455274000}]
   System.out.println("条件非主键查询:" + JSON.toJSONString(tbTestMapper.selectByCondition(TbTest.builder().age(99L).build())));
}
SQL执行
1
2
3
4
5
6
7
DEBUG:2017-09-15 14:18:26.630[debug] ==>  Preparing: SELECT id AS id, name AS name, age AS age, create_time AS createTime, update_time AS updateTime FROM tb_test WHERE id = ? ORDER BY id DESC, update_time DESC, create_time DESC, age DESC  
DEBUG:2017-09-15 14:18:26.659[debug] ==> Parameters: 10(Long) 
DEBUG:2017-09-15 14:18:26.700[debug] <==      Total: 1 

DEBUG:2017-09-15 14:18:26.723[debug] ==>  Preparing: SELECT id AS id, name AS name, age AS age, create_time AS createTime, update_time AS updateTime FROM tb_test WHERE age = ? ORDER BY id DESC, update_time DESC, create_time DESC, age DESC  
DEBUG:2017-09-15 14:18:26.723[debug] ==> Parameters: 99(Long) 
DEBUG:2017-09-15 14:18:26.727[debug] <==      Total: 2