这是我参与11月更文挑战的第5天，活动详情查看：2021最后一次更文挑战
MyBatis动态SQL语句
通过各种判断生成不同的SQL语句，多条件查询，在我们之前的学习过程，我们是通过恒等式的SQL语句和Java判断动态拼接SQL语句，而现在是有MyBatis框架提供了两种方式，帮我们完成动态SQL语句的拼接
- 映射文件的标签方式
- 注解方式-定义动态拼接的类型
SELECT * FROM sys_user 查询全部，没有做任何判断
SELECT * FROM sys_user WHERE 1=1 当等式返回true，返回结果，如果存在1百万跳记录，恒等式需要判断1百万次
String sql = "SELECT * FROM sys_user WHERE 1=1 ";
if(!map.get("user_name").equals("")){
sql += " AND user_name LIKE '"+map.get("user_name")+"%'";
}
if(!map.get("age").equals("")){
sql += " AND age="+map.get("age");
}
由我们自己完成判断拼接
1.动态SQL语句标签
动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。
使用动态 SQL 并非一件易事，但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。
如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器，你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
<if test="只能返回boolean类型">我们可以通过很等式拼接动态SQL语句
<?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.hanpang.mapper.UserMapper">
<resultMap id="BaseMapper" type="com.hanpang.model.User">
<id column="id" property="userId" />
<result column="username" property="userName"/>
<result column="password" property="pwd"/>
<result column="nick_name" property="nickName"/>
<result column="age" property="userAge"/>
<result column="sex" property="userSex"/>
</resultMap>
<!-- 因为不使用SELECT * 那么我们可以将要查询的字段放置到公告区域 -->
<sql id="base_columns">
id,username,password,nick_name,age,sex
</sql>
<!-- 1.恒等式和IF标签完成动态SQL语句 -->
<select id="identityIfSQL" parameterType="com.hanpang.model.User" resultMap="BaseMapper">
SELECT <include refid="base_columns"/> FROM sys_user
WHERE 1=1
<!-- 通过IF标签进行判断，拼接SQL语句 -->
<!-- 在所有的XML文件中不支持&的使用，需要使用其转义字符串& -->
<!-- <if test="类中的属性或者Map中的KEY或者Param定义得到名称，如果Integer id，那么可以任意命名"></if>-->
<if test="userName != null && userName != ''"> <!-- 推荐使用and替换 -->
AND username LIKE CONCAT(#{userName},'%')
</if>
<if test="userSex != null">
AND sex=#{userSex}
</if>
</select>
</mapper>
-
<where>：跟IF标签组合使用，标签的作用：（1）当标签
<where>内容</where>标签之间存在内容，在整个的内容的最前面加入where关键字where 内容的形式（2）检查
where 跟进内容的最前端是否含有AND或者OR，如果以上述两种开头将其自动去掉
<select id="whereIfSQL" parameterType="com.hanpang.model.User" resultMap="BaseMapper">
SELECT <include refid="base_columns"/> FROM sys_user
<where>
<if test="userName != null and userName != ''"> <!-- 推荐使用and替换 -->
AND username LIKE CONCAT(#{userName},'%')
</if>
<if test="userSex != null">
AND sex=#{userSex}
</if>
</where>
</select>
<trim>标签可以实现where标签和set标签- 前面的trim标签不是去空格，标签中的属性是可以任意组合
- prefix="当发现标签之间有内容的时候，在该内容最前端加入的什么内容"
- prefixOverride="当发现标签之间有内容的时候，检查内容的最前端是跟给定内容的匹配，如果匹配成功，将其去掉"
- suffix="当发现标签之间有内容的时候，在该内容最后端加入的什么内容"
- suffixOverride"当发现标签之间有内容的时候，检查内容的最后端是跟给定内容的匹配，如果匹配成功，将其去掉"
<select id="trimIfSQL" parameterType="com.hanpang.model.User" resultMap="BaseMapper">
SELECT <include refid="base_columns"/> FROM sys_user
<!-- trim标签实现where标签 -->
<trim prefix="WHERE " prefixOverrides="AND |OR ">
<if test="userName != null and userName != ''">
AND username LIKE CONCAT(#{userName},'%')
</if>
<if test="userSex != null">
AND sex=#{userSex}
</if>
</trim>
</select>
-
<set>:跟IF标签组合使用,完成动态更新，标签的作用：（1）当标签
<set>内容</set>标签之间存在内容，在整个的内容的最前面加入set关键字set 内容的形式（2）检查
set 跟进内容的最后端是否含有逗号,，将其去掉
<update id="updateSet" parameterType="com.hanpang.model.User">
UPDATE sys_user
<set>
<if test="userName != null and userName.trim().length > 0">
username = #{userName},
</if>
<if test="pwd != null and pwd != ''">
password = #{pwd},
</if>
<if test="nickName != null and nickName.trim().length > 0">
nick_name = #{nickName},
</if>
<if test="userAge != null">
age =#{userAge},
</if>
<if test="userSex != null">
sex =#{userSex},
</if>
</set>
<where>
<if test="userId != null">
id=#{userId}
</if>
</where>
</update>
等价写法
<update id="updateTrim" parameterType="com.hanpang.model.User">
UPDATE sys_user
<trim prefix="SEX" suffixOverrides=",">
<if test="userName != null and userName.trim().length > 0">
username = #{userName},
</if>
<if test="pwd != null and pwd != ''">
password = #{pwd},
</if>
<if test="nickName != null and nickName.trim().length > 0">
nick_name = #{nickName},
</if>
<if test="userAge != null">
age =#{userAge},
</if>
<if test="userSex != null">
sex =#{userSex},
</if>
</trim>
<where>
<if test="userId != null">
id=#{userId}
</if>
</where>
</update>
<trim>的经典使用：动态添加语句，一一对应的顺序，不能混乱
<insert id="add" parameterType="com.hanpang.model.User" useGeneratedKeys="true" keyProperty="userId">
INSERT INTO sys_user
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="userName != null and userName.trim().length > 0">
username ,
</if>
<if test="pwd != null and pwd != ''">
password,
</if>
<if test="nickName != null and nickName.trim().length > 0">
nick_name ,
</if>
<if test="userAge != null">
age ,
</if>
<if test="userSex != null">
sex ,
</if>
</trim>
<trim prefix="VALUES (" suffix=")" suffixOverrides=",">
<if test="userName != null and userName.trim().length > 0">
#{userName},
</if>
<if test="pwd != null and pwd != ''">
#{pwd},
</if>
<if test="nickName != null and nickName.trim().length > 0">
#{nickName},
</if>
<if test="userAge != null">
#{userAge},
</if>
<if test="userSex != null">
#{userSex},
</if>
</trim>
</insert>
-
<foreach>标签：循环集合（数组、List、Map）<foreach collection="前提是一个形参(没有设置@Param)，是数组，默认的名称array,是list默认的名称是list" item="获取集合中的元素，具体的值或者对象" open="在内容的最前端加入的内容" close="在内容的最后端加入的内容" separator="设置元素之间使用什么分隔" ></foreach>-
数据没有设置别名的情况，默认名为array
int deleteArray(int[] tempArray); //没有使用@Param<!-- 1.注意如果使用数组形式，不要设置paramterType让其自动识别 --> <delete id="deleteArray"> DELETE FROM sys_user <where> <if test="array != null and array.length>0"> <!-- id in (10,20,30) --> id in <foreach collection="array" item="val" open="(" close=")" separator=","> #{val} </foreach> </if> </where> </delete> -
数据设置别名的情况
<delete id="deleteArrayParam"> DELETE FROM sys_user <where> <!-- id in (10,20,30) --> id in <foreach collection="myArray" item="val" open="(" close=")" separator=","> #{val} </foreach> </where> </delete>
-
-
集合操作
<delete id="deleteList" parameterType="list"> DELETE FROM sys_user <where> id in <foreach collection="list" item="val" open="(" close=")" separator=","> #{val} </foreach> </where> </delete> -
批量添加操作
<insert id="addBatch" parameterType="list"> INSERT INTO sys_user (username,nick_name,sex,age) VALUES <foreach collection="list" item="user" separator=","> (#{user.userName},#{user.nickName},#{user.userSex},#{user.userAge}) </foreach> </insert><insert id="addBatch" parameterType="list"> INSERT INTO sys_user (username,nick_name,sex,age) VALUES <foreach collection="list" item="user" open="(" close=")" separator="),("> #{user.userName},#{user.nickName},#{user.userSex},#{user.userAge} </foreach> </insert>
2.标签中不支持小于号
<select id="listAge" parameterType="int" resultMap="BaseMapper">
<!-- SELECT <include refid="base_columns"/> FROM sys_user WHERE age < #{age} -->
<!-- SELECT <include refid="base_columns"/> FROM sys_user WHERE age < #{age}-->
<!-- SELECT <include refid="base_columns"/> FROM sys_user WHERE age <= #{age}-->
SELECT <include refid="base_columns"/> FROM sys_user WHERE
<![CDATA[
age<#{age}
]]>
<!-- CDATA里面不能使用动态SQL语句标签 -->
</select>
3.一次性执行多条SQL语句
默认情况下MySQL是不支持一次性执行多态SQL语句
<update id="excuteMoreSQL">
UPDATE sys_user SET age=66 WHERE id=6;
DELETE FROM sys_user WHERE id=5;
</update>
可以在连接MySQL的时候设置属性 allowMultiQueries=true
<property name="url" value="jdbc:mysql://127.0.0.1:3306/antu_mybatis?characterEncoding=utf-8&autoReconnect=true&failOverReadOnly=false&allowMultiQueries=true"/>
4.注解动态SQL语句
package com.hanpang.mapper;
import com.hanpang.model.Dept;
import com.hanpang.provider.DeptProvider;
import org.apache.ibatis.annotations.*;
import java.util.List;
public interface DeptMapper {
@Insert(value = "INSERT INTO SYS_DEPT (DEPT_NAME,DEPT_USER) VALUES (#{deptName},#{deptUser})")
@Options(useGeneratedKeys=true,keyProperty="deptId")
int add(Dept dept);
@Select("SELECT * FROM sys_dept")
@Results(id="BaseMapper",value = {
@Result(id = true,column = "dept_id",property = "deptId"),
@Result(column = "dept_name",property = "deptName"),
@Result(column = "dept_user",property = "deptUser")
})
List<Dept> listAll();
@Select("SELECT * FROM sys_dept WHERE dept_name LIKE CONCAT('%',#{name},'%')")
@ResultMap(value="BaseMapper")
List<Dept> listByName(String deptName);
@Select("SELECT * FROM sys_dept")
@Results(value = {
@Result(id = true,column = "dept_id",property = "deptId"),
@Result(column = "dept_name",property = "deptName"),
@Result(column = "dept_user",property = "deptUser"),
@Result(property = "userList",column = "dept_id",many = @Many(select = "com.hanpang.mapper.UserMapper.getUserByDeptId"))
})
List<Dept> one2manyList();
@Select("SELECT sd.dept_id,sd.dept_name,sd.dept_user,su.* FROM sys_dept sd LEFT JOIN sys_user su ON sd.dept_id=su.dept_id")
@Results(value = {
@Result(id = true,column = "dept_id",property = "deptId"),
@Result(column = "dept_name",property = "deptName"),
@Result(column = "dept_user",property = "deptUser"),
@Result(property = "userList",column = "dept_id",many = @Many(resultMap= "com.hanpang.mapper.UserMapper.BaseMapper"))
})
List<Dept> one2manyListJoin();
@Insert("<script>INSERT INTO sys_dept (dept_name,dept_user) VALUES" +
"<foreach collection='list' item='dept' separator=','>" +
"(#{dept.deptName},#{dept.deptUser})" +
"</foreach>" +
"</script>")
void addBatch(List<Dept> deptList);
@InsertProvider(value= DeptProvider.class,method="insertDeptBatchSQL")
void insertBatch(List<Dept> deptList);
}
package com.hanpang.provider;
import com.hanpang.model.Dept;
import java.util.List;
public class DeptProvider {
public String insertDeptBatchSQL(List<Dept> deptList){
StringBuilder builder = new StringBuilder();
builder.append("INSERT INTO sys_dept (dept_name,dept_user) VALUES");
deptList.forEach(dept -> {
builder.append("('");
builder.append(dept.getDeptName());
builder.append("','");
builder.append(dept.getDeptUser()) ;
builder.append("'),");
});
builder.deleteCharAt(builder.length()-1);
System.out.println("*************");
System.out.println(builder.toString());
return builder.toString();
}
}
