前言
动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。
使用动态 SQL 并非一件易事，但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。
这篇文章总结了日常的增删改查，重点关注@Param的注解以及批量操作相关动作。
这篇文章总结了比较通用的自定义SQL的语法，包含的标签包含if、choose (when, otherwise)、trim (where, set)、foreach等。
准备
CREATE TABLE `t_student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(255) DEFAULT NULL COMMENT '姓名',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`clazz_id` int(11) DEFAULT NULL COMMENT '班级id',
`number` varchar(6) DEFAULT NULL COMMENT '学号',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
public class Student {
private Integer id;
private String name;
private Integer age;
private Integer clazzId;
private String number;
}
- 前置准备的数据库表字段和对应的类对象定义。
常用SQL
select语句
<sql id="base_columns">
id, name, age, clazz_id, number
</sql>
<select id="selectById" resultMap="BaseResultMap">
select <include refid="base_columns"/> from t_student where id = #{id}
</select>
Student selectById(@Param("id") int id);
- 根据单个id进行查询的例子。
<sql id="base_columns">
id, name, age, clazz_id, number
</sql>
<select id="selectByIds" resultMap="BaseResultMap">
SELECT <include refid="base_columns"/> from t_student where id in
<foreach collection="ids" separator="," item="id" open="(" close=")">
#{id}
</foreach>
AND age=#{age}
</select>
List<Student> selectByIds(@Param("ids") List<Integer> idList, @Param("age") Integer age);
- 根据多个ids进行查询例子，前提是保证ids不为空。
<sql id="base_columns">
id, name, age, clazz_id, number
</sql>
<select id="selectByIdsV2" resultMap="BaseResultMap">
SELECT <include refid="base_columns"/> from t_student
<where>
<if test="ids != null and ids.size() > 0">
id in
<foreach collection="ids" open="(" close=")" separator=",">
#{id}
</foreach>
</if>
<if test="age != null">
AND age = #{age}
</if>
</where>
</select>
List<Student> selectByIdsV2(@Param("ids") List<Integer> idList, @Param("age") Integer age);
- <foreach>的collection等价于@Param注解的变量，如例子中的ids。
- 通过<where>注解解决idList为空的场景。
- where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。
<sql id="base_columns">
id, name, age, clazz_id, number
</sql>
<select id="selectByIdsV3" resultMap="BaseResultMap">
SELECT <include refid="base_columns"/> from t_student
<where>
<if test="studentList != null and studentList.size() > 0">
id in
<foreach collection="studentList" item="item" open="(" close=")" separator=",">
#{item.id}
</foreach>
</if>
<if test="age != null">
AND age = #{age}
</if>
</where>
</select>
List<Student> selectByIdsV3(@Param("studentList") List<Student> studentList, @Param("age") Integer age);
- <foreach>的collection等价于@Param注解的变量，如例子中的studentList。
- 通过<where>注解解决studentList为空的场景。
- where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。
<sql id="base_columns">
id, name, age, clazz_id, number
</sql>
<select id="selectByIdsV4" resultMap="BaseResultMap">
SELECT <include refid="base_columns"/> from t_student
<trim prefix="where" prefixOverrides="and|or">
<if test="studentList != null and studentList.size() > 0">
id in
<foreach collection="studentList" item="item" open="(" close=")" separator=",">
#{item.id}
</foreach>
</if>
<if test="age != null">
AND age = #{age}
</if>
</trim>
</select>
List<Student> selectByIdsV4(@Param("studentList") List<Student> studentList, @Param("age") Integer age);
- 过自定义 trim 元素来定制 where 元素的功能。
- prefix 属性中指定前置插入的内容
- prefixOverrides 属性会忽略前置的通过管道符分隔的文本序列。
- suffix 属性中指定后置插入的内容
- suffixOverrides属性会忽略后置的通过管道符分隔的文本序列。
insert语句
<insert id="insertV1" parameterType="com.example.model.Student" keyProperty="id" useGeneratedKeys="true">
insert into t_student (`name`, age, clazz_id, `number`)
values (#{name}, #{age}, #{clazzId}, #{number})
</insert>
Integer insertV1(Student student);
- 通用的插入单个对象的例子。
<insert id="insertV2" parameterType="com.example.model.Student" keyProperty="id" useGeneratedKeys="true">
insert into t_student (`name`, age, clazz_id, `number`)
values (#{student.name}, #{student.age}, #{student.clazzId}, #{student.number})
</insert>
Integer insertV2(@Param("student") Student student);
- 通过@param指定别名student来进行单个对象的插入。
<insert id="batchInsertV1">
insert into t_student (`name`, age, clazz_id, `number`)
values
<foreach collection="list" item="item" separator=",">
(#{item.name}, #{item.age}, #{item.clazzId}, #{item.number})
</foreach>
</insert>
Integer batchInsertV1(List<Student> studentList);
- 批量插入多个对象的例子，没有指定别名通用的collection是list，注意foreach没有open和close属性。
<insert id="batchInsertV2">
insert into t_student (`name`, age, clazz_id, `number`)
values
<foreach collection="studentList" item="item" separator=",">
(#{item.name}, #{item.age}, #{item.clazzId}, #{item.number})
</foreach>
</insert>
Integer batchInsertV2(@Param("studentList") List<Student> studentList);
- 批量插入多个对象的例子，通过@Param指定别名studentList，对应的collection为别名变量。
delete语句
<delete id="deleteById" parameterType="java.lang.Integer">
delete from t_student where id = #{id}
</delete>
int deleteById(@Param("id") int id);
- 通用的单个对象的删除例子。
<delete id="batchDetele">
delete from t_student
<if test="idList != null and idList.size() > 0">
where id in
<foreach collection="idList" item="item" separator="," open="(" close=")">
#{item}
</foreach>
</if>
</delete>
int batchDetele(@Param("idList") List<Integer> idList);
- 批量删除的例子，通过@Param执行别名idList，注意<foreach>在这种场景指定open和close对应的值。
update语句
<update id="updateV1" parameterType="com.example.model.Student">
update t_student
<set>
<if test="name != null">`name` = #{name},</if>
<if test="age != null" >age = #{age},</if>
<if test="clazzId != null">clazzId = #{clazzId},</if>
<if test="number != null">`number` = #{number}</if>
</set>
where id = #{id}
</update>
Integer updateV1(Student student);
- 用于动态更新语句的类似解决方案叫做 set。
- set 元素可以用于动态包含需要更新的列，忽略其它不更新的列。
- set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）。
<update id="updateV2" parameterType="com.example.model.Student">
update t_student
<trim prefix="set" suffixOverrides=",">
<if test="name != null">`name` = #{name},</if>
<if test="age != null" >age = #{age},</if>
<if test="clazzId != null">clazzId = #{clazzId},</if>
<if test="number != null">`number` = #{number},</if>
</trim>
where id = #{id}
</update>
Integer updateV2(Student student);
- 通过自定义 trim 元素来实现等价的set操作。
<update id="batchUpdate" parameterType="java.util.List">
update t_student
<trim prefix="set" suffixOverrides=",">
<trim prefix="name = case " suffix="end,">
<foreach collection="studentList" item="item">
<if test="item.name != null">
when id = #{item.id} then #{item.name}
</if>
</foreach>
</trim>
<trim prefix="age = case " suffix="end,">
<foreach collection="studentList" item="item">
<if test="item.age != null">
when id = #{item.id} then #{item.age}
</if>
</foreach>
</trim>
<trim prefix="clazzId = case " suffix="end,">
<foreach collection="studentList" item="item">
<if test="item.clazzId != null">
when id = #{item.id} then #{item.clazzId}
</if>
</foreach>
</trim>
<trim prefix="number = case " suffix="end,">
<foreach collection="studentList" item="item">
<if test="item.number != null">
when id = #{item.id} then #{item.number}
</if>
</foreach>
</trim>
</trim>
where id in
<foreach collection="studentList" item="item" open="(" close=")" separator=",">
#{item.id}
</foreach>
</update>
Integer batchUpdate(@Param("studentList") List<Student> studentList);
- 通过自定义trim标签来实现批量更新操作。
