Java服务_Mybatis动态SQL总结

发表于 | 分类于

Java服务_Mybatis动态SQL总结

1.Mybatis映射xml文件概述

【基础知识】_MyBatis

2.Mybatis动态SQL

OGNL，全称为Object-Graph Navigation Language，它是一个功能强大的表达式语言，用来获取和设置Java对象的属性，它旨在提供一个更高的更抽象的层次来对Java对象图进行导航。

mybatis 的动态sql语句是基于OGNL表达式的，可以方便的在sql语句中实现某些逻辑，总体说来mybatis动态SQL 语句主要有以下几类:

 　　1. if (简单的条件判断)；
 　　2. choose (when,otherwize)相当于java语言中的switch；
 　　3. trim (对包含的内容加上前缀或者后缀等)；
 　　4. where (主要是用来简化sql语句中where条件判断的，能智能的处理and/or，不必担心多余导致语法错误)；
 　　5. set (主要用于update语句中)；
 　　6. foreach (在实现mybatis in语句查询时特别有用)。

2.1 if 语句

2.1 if 语句

2.1 if 语句

案例：

1
2
3
4
5
6
7
8
9
10
11
12
<select id="dynamicIfTest" parameterType="Blog" resultType="Blog">
  select * from t_blog where 1 = 1
  <if test="title != null">
    and title = #{title}
  </if>
  <if test="content != null">
    and content = #{content}
  </if>
  <if test="owner != null">
    and owner = #{owner}
  </if>
</select>

如果Blog对象入参提供了title参数，那么sql中就要加上title=#{title}，同样如果提供了content和owner的时候，它们也需要满足相应的条件。

以往使用其他类型框架或者直接使用JDBC的时候， 如果我们要达到同样的选择效果的时候，就需要在java代码中拼SQL语句，这是极其麻烦的，上述的动态SQL就要简单多了。

2.2 choose(when,otherwize) 语句

choose(when,otherwize)相当于java语言中的switch，案例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<select id="dynamicChooseTest" parameterType="Blog" resultType="Blog">
  select * from t_blog where 1 = 1 
  <choose>
    <when test="title != null">
      and title = #{title}
    </when>
    <when test="content != null">
      and content = #{content}
    </when>
    <otherwise>
      and owner = "owner1"
    </otherwise>
  </choose>
</select>

when元素表示当when中的条件满足的时候就拼接其中的内容，跟JAVA中的switch效果差不多，按照条件的顺序，当when中有条件满足的时候，就会跳出choose，即所有的when和otherwise条件中，只有一个会拼接，当所有的条件都不满足的时候就拼接otherwise中的内容。

所以上述语句的意思非常简单， 当title!=null的时候就拼接and titlte = #{title}，不再往下判断条件，当title为空且content!=null的时候就输出and content = #{content}，当所有条件都不满足的时候就输出otherwise中的内容。

2.3 trim 语句

trim对包含的内容加上prefix或者suffix等，案例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<select id="dynamicTrimTest" parameterType="Blog" resultType="Blog">
  select * from t_blog 
  <trim prefix="where" prefixOverrides="and|or">
    <if test="title != null">
      title = #{title}
    </if>
    <if test="content != null">
      and content = #{content}
    </if>
    <if test="owner != null">
      or owner = #{owner}
    </if>
  </trim>
</select>

trim元素的主要功能是可以在其包含的内容前加上某些前缀，或者在其后加上某些后缀，与之对应的属性是prefix和suffix；同时可以把包含内容的首部某些内容覆盖，即忽略，或者把尾部的某些内容覆盖，对应的属性是 prefixOverrides和suffixOverrides。正因为trim有这样的功能，所以我们也可以非常简单的利用trim来代替where元素的功能。

所以上述语句是为trim元素中语句先将and或or的前缀去掉，再加上where前缀。

2.4 where 语句

where主要是用来简化sql语句中where条件判断的，能智能的处理and/or条件，案例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<select id="dynamicWhereTest" parameterType="Blog" resultType="Blog">
  select * from t_blog 
  <where>
    <if test="title != null">
      title = #{title}
    </if>
    <if test="content != null">
      and content = #{content}
    </if>
    <if test="owner != null">
      and owner = #{owner}
    </if>
  </where>
</select>

where元素经常与if搭配使用，作用是会在写入where元素的地方拼接一个where，用户不需要考虑where元素里面的条件拼接是什么样子的，MyBatis智能处理。如果where元素中的所有if语句都不满足，那么MyBatis就不会把where拼接上去；如果拼接内容是以and开头的，MyBatis会把第一个and忽略，当然如果是or开头的，MyBatis也会把它忽略；此外，在where元素中不需要考虑空格的问题，MyBatis会智能填充。

像上述例子中，如果title=null， 而content != null，那么输出的整个语句会是select * from t_blog where content = #{content}，而不是select * from t_blog where and content = #{content}，因为MyBatis会智能的把首个and或or给忽略。

2.5 set 语句

set主要用于update语句中，案例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<update id="dynamicSetTest" parameterType="Blog">
  update t_blog
  <set>
    <if test="title != null">
      title = #{title},
    </if>
    <if test="content != null">
      content = #{content},
    </if>
    <if test="owner != null">
      owner = #{owner}
    </if>
  </set>
  where id = #{id}
</update>

set元素主要是用在更新操作的时候，它的主要功能和where元素其实是差不多的，主要是在包含的语句前拼接一个set，然后如果包含的语句是以逗号结束的话将会把该逗号忽略，如果set包含的内容为空的话则会出错。有了set元素我们就可以动态的更新那些修改了的字段

2.6 foreach 语句

foreach的主要用在构建in条件中，它可以在SQL语句中进行遍历一个集合。foreach元素的属性主要有item，index，collection，open，separator，close。

  • item 表示集合中每一个元素进行迭代时的别名；
  • index 指定一个名字，用于表示在迭代过程中，每次迭代到的位置；
  • open 表示该语句以什么开始；
  • separator 表示在每次进行迭代之间以什么符号作为分隔符；
  • close 表示以什么结束。

在使用foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况下，该属性的值是不一样的，主要有一下3种情况：

  • 如果传入的是单参数且参数类型是一个List的时候，collection属性值为list；
  • 如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array；
  • 如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map，实际上如果你在传入参数的时候，在MyBatis里面也是会把它封装成一个Map的，map的key就是参数名，所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key。

单参数List的案例：

1
2
3
4
5
6
<select id="dynamicForeachTest" resultType="Blog">
  select * from t_blog where id in
  <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
    #{item}
  </foreach>
</select>

上述案例对应mapper为：

1
public List<Blog> dynamicForeachTest(List<Integer> ids);

单参数array的案例：

1
2
3
4
5
6
<select id="dynamicForeach2Test" resultType="Blog">
  select * from t_blog where id in
  <foreach collection="array" index="index" item="item" open="(" separator="," close=")">
    #{item}
  </foreach>
</select>

上述案例对应mapper为：

1
public List<Blog> dynamicForeach2Test(int[] ids);

多参数map的案例：

1
2
3
4
5
6
<select id="dynamicForeach3Test" resultType="Blog">
  select * from t_blog where title like "%"#{title}"%" and id in
  <foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
    #{item}
  </foreach>
</select>

上述案例对应mapper为：

1
public List<Blog> dynamicForeach3Test(Map<String, Object> params);

3.Mybatis将insert方法将自增主键返回入参对象

mybatis的配置参数mybatis.configuration.use-generated-keys=true标识允许JDBC生成主键，默认为false，如果设为了true，这个设置将强制将被生成的主键返回给入参对象。

案例如下:

1
2
3
4
5
6
7
8
mybatis:
  mapper-locations: classpath:mybatis/mapper/*/*.xml
  configuration:
    log-impl: org.apache.ibatis.logging.nologging.NoLoggingImpl
    cache-enabled: false
    use-generated-keys: true
    default-executor-type: reuse
    map-underscore-to-camel-case: true
1
2
3
<insert id="addUser" parameterType="com.model.User"  keyProperty="id">
    insert into User(name, age) values(#{name}, #{age})
</insert>
1
2
3
4
5
6
7
8
9
10
11
12
13
public class User {
    private int id;
    private String name;
    private Integer age;
}

User user = new User();
user.setName("jia");
user.setAge(17);
int rows = userMapper.insert(user);
// 重要：insert方法总是自动返回一个int值，表示插入数据库的行数；如果设置mybatis.configuration.use-generated-keys=true则会将自动生产的id键值赋值给入参对象。
System.out.println("rows inserted = " + rows);
System.out.println("generated key value = " + user.getId());

insert方法总是自动返回一个int值，表示插入数据库的行数；如果设置mybatis.configuration.use-generated-keys=true则会将自动生产的id键值赋值给入参对象。

4.Mybatis映射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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
<?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.jd.bdaa.arch.dao.mapper.drive.DrivePhysicTableMapper">

  <parameterMap id="map" type="java.util.Map"/>

  <resultMap id="BaseResultMap" type="com.jd.bdaa.arch.model.po.drive.DrivePhysicTablePO">
    <id column="id" jdbcType="BIGINT" property="id"/>
    <result column="engine" jdbcType="VARCHAR" property="engine"/>
    <result column="physic_table_type" jdbcType="VARCHAR" property="physicTableType"/>
    <result column="physic_data_source_id" jdbcType="BIGINT" property="physicDataSourceId"/>
    <result column="original_data_source_id" jdbcType="BIGINT" property="originalDataSourceId"/>
    <result column="execution_engine" jdbcType="VARCHAR" property="executionEngine"/>
    <result column="task_id" jdbcType="BIGINT" property="taskId"/>
    <result column="task_time_interval" jdbcType="VARCHAR" property="taskTimeInterval"/>
    <result column="partition_by" jdbcType="VARCHAR" property="partitionBy"/>
    <result column="sharding_key" jdbcType="VARCHAR" property="shardingKey"/>
    <result column="order_by" jdbcType="VARCHAR" property="orderBy"/>
    <result column="committer" jdbcType="VARCHAR" property="committer"/>
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
  </resultMap>

  <!--表名-->
  <sql id="meta_table">unify_drive_physic_table</sql>

  <sql id="Base_Column_List">
    id,physic_table_type,physic_data_source_id,original_data_source_id,execution_engine,task_id,task_time_interval,partition_by, sharding_key, order_by, committer,create_time,update_time,engine
  </sql>

  <select id="selectByDrivePhysicTableIds" resultMap="BaseResultMap" parameterType="java.util.List">
    select
    <include refid="Base_Column_List"/>
    from
    <include refid="meta_table"></include>
    where id in
    <foreach collection="physicTableIds" index="index" item="physicTableId" open="(" separator="," close=")">
      #{physicTableId}
    </foreach>
  </select>

  <insert id="insert"  keyProperty="id" parameterType="com.jd.bdaa.arch.model.po.drive.DrivePhysicTablePO">
   insert into
    <include refid="meta_table"></include>
    (
    <trim prefix="" suffixOverrides=",">
      <if test="engine != null">engine, </if>
      <if test="physicTableType != null">physic_table_type, </if>
      <if test="physicDataSourceId != null">physic_data_source_id, </if>
      <if test="originalDataSourceId != null">original_data_source_id, </if>
      <if test="committer != null">committer,</if>
      <if test="executionEngine != null">execution_engine,</if>
      <if test="taskId != null">task_id,</if>
      <if test="taskTimeInterval != null">task_time_interval,</if>
      <if test="partitionBy != null">partition_by,</if>
      <if test="shardingKey != null">sharding_key,</if>
      <if test="orderBy != null">order_by,</if>
    </trim>
    ) values
    (
    <trim prefix="" suffixOverrides=",">
      <if test="engine != null">#{engine}, </if>
      <if test="physicTableType != null">#{physicTableType}, </if>
      <if test="physicDataSourceId != null">#{physicDataSourceId}, </if>
      <if test="originalDataSourceId != null">#{originalDataSourceId}, </if>
      <if test="committer != null">#{committer},</if>
      <if test="executionEngine != null">#{executionEngine},</if>
      <if test="taskId != null">#{taskId},</if>
      <if test="taskTimeInterval != null">#{taskTimeInterval},</if>
      <if test="partitionBy != null">#{partitionBy},</if>
      <if test="shardingKey != null">#{shardingKey},</if>
      <if test="orderBy != null">#{orderBy},</if>
    </trim>
    )
  </insert>

  <!-- 更新任务执行信息-->
  <update id="updateTaskInfo">
    update
    <include refid="meta_table"></include>
    <set>
      task_id = #{taskId},
      task_time_interval = #{taskTimeInterval}
    </set>
    where id = #{id}
  </update>

  <delete id="deleteByIds">
    delete
    from
    <include refid="meta_table"></include>
    where id in
    <foreach collection="physicTableIds" index="index" item="physicTableId" open="(" separator="," close=")">
      #{physicTableId}
    </foreach>
  </delete>

  <update id="update" parameterType="com.jd.bdaa.arch.model.po.drive.DrivePhysicTablePO">
    update
    <include refid="meta_table"/>
    <trim prefix="set" suffixOverrides=",">
      <if test="nameCn != null">name_cn = #{nameCn},</if>
      <if test="description != null">description = #{description},</if>
      <if test="committer != null">committer = #{committer},</if>
      <if test="decorateDefIdList != null">decorate_def_id_list = #{decorateDefIdList},</if>
      <if test="physicTableId != null">physic_table_id = #{physicTableId},</if>
      <if test="timeInterval != null">time_interval = #{timeInterval},</if>
      <if test="ezdApiId != null">ezd_api_id = #{ezdApiId},</if>
      <if test="isFromDataSet != null">is_from_dataset = #{isFromDataSet},</if>
      <if test="version != null">version = #{version},</if>
      <if test="serviceType != null">version = #{serviceType},</if>
    </trim>
    where id = #{id} and enable = 1 and data_checked = 1
  </update>
</mapper>

5.参考文献

mybatis 动态sql