MyBatis入坑之动态SQL语句
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(); } }
作者：胖先森
链接：https://juejin.cn/post/7026875682110046222