前言
動(dòng)態(tài) SQL 是 MyBatis 的強(qiáng)大特性之一。如果你使用過 JDBC 或其它類似的框架，你應(yīng)該能理解根據(jù)不同條件拼接 SQL 語句有多痛苦，例如拼接時(shí)要確保不能忘記添加必要的空格景馁，還要注意去掉列表最后一個(gè)列名的逗號(hào)萧诫。利用動(dòng)態(tài) SQL斥难，可以徹底擺脫這種痛苦。
使用動(dòng)態(tài) SQL 并非一件易事帘饶，但借助可用于任何 SQL 映射語句中的強(qiáng)大的動(dòng)態(tài) SQL 語言哑诊，MyBatis 顯著地提升了這一特性的易用性。
這篇文章總結(jié)了日常的增刪改查及刻，重點(diǎn)關(guān)注@Param的注解以及批量操作相關(guān)動(dòng)作镀裤。
這篇文章總結(jié)了比較通用的自定義SQL的語法，包含的標(biāo)簽包含if缴饭、choose (when, otherwise)暑劝、trim (where, set)、foreach等颗搂。
準(zhǔn)備
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 '班級(jí)id',
`number` varchar(6) DEFAULT NULL COMMENT '學(xué)號(hào)',
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;
}
- 前置準(zhǔn)備的數(shù)據(jù)庫表字段和對(duì)應(yīng)的類對(duì)象定義担猛。
常用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);
- 根據(jù)單個(gè)id進(jìn)行查詢的例子。
<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);
- 根據(jù)多個(gè)ids進(jìn)行查詢例子丢氢，前提是保證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等價(jià)于@Param注解的變量，如例子中的ids疚察。
- 通過<where>注解解決idList為空的場(chǎng)景蒸走。
- where 元素只會(huì)在子元素返回任何內(nèi)容的情況下才插入 “WHERE” 子句。而且貌嫡，若子句的開頭為 “AND” 或 “OR”载碌，where 元素也會(huì)將它們?nèi)コ?/li>
<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等價(jià)于@Param注解的變量，如例子中的studentList衅枫。
- 通過<where>注解解決studentList為空的場(chǎng)景。
- where 元素只會(huì)在子元素返回任何內(nèi)容的情況下才插入 “WHERE” 子句朗伶。而且弦撩，若子句的開頭為 “AND” 或 “OR”，where 元素也會(huì)將它們?nèi)コ?/li>
<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 屬性中指定前置插入的內(nèi)容
- prefixOverrides 屬性會(huì)忽略前置的通過管道符分隔的文本序列益楼。
- suffix 屬性中指定后置插入的內(nèi)容
- suffixOverrides屬性會(huì)忽略后置的通過管道符分隔的文本序列。
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);
- 通用的插入單個(gè)對(duì)象的例子点晴。
<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來進(jìn)行單個(gè)對(duì)象的插入感凤。
<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);
- 批量插入多個(gè)對(duì)象的例子，沒有指定別名通用的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);
- 批量插入多個(gè)對(duì)象的例子，通過@Param指定別名studentList屠橄，對(duì)應(yīng)的collection為別名變量族跛。
delete語句
<delete id="deleteById" parameterType="java.lang.Integer">
delete from t_student where id = #{id}
</delete>
int deleteById(@Param("id") int id);
- 通用的單個(gè)對(duì)象的刪除例子闰挡。
<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執(zhí)行別名idList礁哄，注意<foreach>在這種場(chǎng)景指定open和close對(duì)應(yīng)的值长酗。
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);
- 用于動(dòng)態(tài)更新語句的類似解決方案叫做 set。
- set 元素可以用于動(dòng)態(tài)包含需要更新的列桐绒，忽略其它不更新的列夺脾。
- set 元素會(huì)動(dòng)態(tài)地在行首插入 SET 關(guān)鍵字，并會(huì)刪掉額外的逗號(hào)（這些逗號(hào)是在使用條件語句給列賦值時(shí)引入的）茉继。
<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 元素來實(shí)現(xiàn)等價(jià)的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標(biāo)簽來實(shí)現(xiàn)批量更新操作。
