mybatis动态sql

lienhui68 收录于技术

2020-06-05 约 2846 字预计阅读 6 分钟次阅读

概述

动态 SQL是MyBatis强大特性之一。极大的简化我们拼装 SQL的操作。

动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。

MyBatis 采用功能强大的基于 OGNL 的表达式来简化操作。

if
choose (when, otherwise)
trim (where, set)
foreach

ongl中的操作符

常用

详细

官网更详细说明

转义字符

带有实体名称的 ASCII 实体

if

查询员工，要求，携带了哪个字段查询条件就带上这个字段的值

 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


<?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.eh.eden.mybatis.orm.dao.EmployeeMapperDynamic">

    <resultMap id="employee" type="com.eh.eden.mybatis.orm.bean.Employee">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
    </resultMap>

    <!--Employee getEmployeeByConditionIf(Employee employee);-->
    <select id="getEmployeeByConditionIf" resultMap="employee">
        select * from tbl_employee where
        1 = 1
        <!-- test：判断表达式（OGNL），从参数中取值进行判断，遇见特殊符号应该去写转义字符-->
        <if test="id != null">
            and id = #{id}
        </if>
        /*
            and 等价于 <!--&&--> ，因为是特殊符号需要转义，所以得用 &amp;&amp; 表示
            "" 也可以使用转义字符表示 &quot;&quot;
        */
        <if test="lastName != null and lastName != ''">
            and last_name = #{lastName}
        </if>
        <if test="email != null and email.trim() != ''">
            and email = #{email}
        </if>
        <!-- ognl会自动进行字符串与数字的转换判断  "0"==0 -->
        <if test="gender == 0 or gender == 1">
            and gender = #{gender};
        </if>
    </select>

</mapper>

where

查询的时候如果某些条件没带可能sql拼装会有问题，解决方法有两种

给where 后面加上1=1，以后的条件都and xxx 像if中例子所示
mybatis推荐做法是使用where标签来将所有的查询条件包括在内。mybatis就会自动地将where标签中拼装的sql，多出来的and或or去掉。

示例，改造if中的例子

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


<!--Employee getEmployeeByConditionIf(Employee employee);-->
<select id="getEmployeeByConditionIf" resultMap="employee">
    select * from tbl_employee
    <where>
        <if test="id != null">
            and id = #{id}
        </if>
        <if test="lastName != null and lastName != ''">
            and last_name = #{lastName}
        </if>
        <if test="email != null and email.trim() != ''">
            and email = #{email}
        </if>
        <if test="gender == 0 or gender == 1">
            and gender = #{gender}
        </if>
    </where>
</select>

注意：1. where标签里面不能有任何注释。 2. where只会去掉第一个多出来的and或or，所以and要写在if条件里的前面。

trime 字符串截取

由上一节得知，如果把and写在if标签的后面，使用where标签也解决不了问题，此时可以使用trim标签。

trim会将标签里的整个字符串拼串后的结果的前缀或者后缀进行特殊处理，如将后面的and去掉。

演示

 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


    <!--Employee getEmployeeByConditionIf(Employee employee);-->
    <select id="getEmployeeByConditionIf" resultMap="employee">
        select * from tbl_employee
        <!-- 后面多出的and或者or where标签不能解决
	 	prefix="":前缀：trim标签体中是整个字符串拼串 后的结果。
	 			prefix给拼串后的整个字符串加一个前缀
	 	prefixOverrides="":
	 			前缀覆盖： 去掉整个字符串前面多余的字符
	 	suffix="":后缀
	 			suffix给拼串后的整个字符串加一个后缀
	 	suffixOverrides=""
	 			后缀覆盖：去掉整个字符串后面多余的字符
	 	-->
        <!-- 自定义字符串的截取规则 -->
        <trim prefix="where" suffixOverrides="and">
            <if test="id != null">
                id = #{id} and
            </if>
            <if test="lastName != null and lastName != ''">
                last_name = #{lastName} and
            </if>
            <if test="email != null and email.trim() != ''">
                email = #{email} and
            </if>
            <if test="gender == 0 or gender == 1">
                gender = #{gender}
            </if>
        </trim>
    </select>

choose

java里的swich case default， ongl中使用 choose/when/otherwise标签

要求：如果带了id就用id查，如果带了lastName就用lastName查;否则就用gender查，只会进入其中一个

演示

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


<!--Employee getEmployeeByConditionChoose(Employee employee);-->
<select id="getEmployeeByConditionChoose" resultMap="employee">
    select * from tbl_employee
    <where>
        <choose>
            <when test="id !=null">
                id = #{id}
            </when>
            <when test="lastName !=null">
                last_name = #{lastName}
            </when>
            <otherwise>
                gender = 0
            </otherwise>
        </choose>
    </where>
</select>

set

where是封装查询条件，去除前面多余的and；set是封装更新条件，去除后面多余的逗号。

演示

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


<!--void updateEmployeeById(Employee employee);-->
<update id="updateEmployeeById">
    update tbl_employee
    <set>
        <if test="lastName!=null">
            last_name=#{lastName},
        </if>
        <if test="email!=null">
            email=#{email},
        </if>
        <if test="gender!=null">
            gender=#{gender}
        </if>
    </set>
    where id = #{id}
</update>

同理可以使用trim标签

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


<!--void updateEmployeeById(Employee employee);-->
<update id="updateEmployeeById">
    update tbl_employee
    <trim prefix="set" suffixOverrides=",">
        <if test="lastName!=null">
            last_name=#{lastName},
        </if>
        <if test="email!=null">
            email=#{email},
        </if>
        <if test="gender!=null">
            gender=#{gender}
        </if>
    </trim>
    where id = #{id}
</update>

foreach

批量查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


<!--List<Employee> getEmployeesByIds(List<Integer> ids);-->
<select id="getEmployeesByIds" resultMap="employee">
    select * from tbl_employee where id in
    <!--
collection：指定要遍历的集合：
   list类型的参数会特殊处理封装在map中，map的key就叫list
item：将当前遍历出的元素赋值给指定的变量
separator:每个元素之间的分隔符
open：遍历出所有结果拼接一个开始的字符
close:遍历出所有结果拼接一个结束的字符
index:索引。遍历list的时候是index就是索引，item就是当前值
            遍历map的时候index表示的就是map的key，item就是map的值

#{变量名}就能取出变量的值也就是当前遍历出的元素
 -->
    <foreach collection="list" open="(" close=")" separator="," item="id">
        #{id}
    </foreach>
</select>

注意

collection标签默认值是list或者map(根据传过来的是list还是map),想要更改就在接口的参数值前加上@Param(“自定义参数”)

如：

java List<Employee> getEmployeesByIds(@Param("ids") List<Integer> ids)

sql 映射文件 <foreach collection="ids" open="(" close=")" separator="," item="id">

批量插入

方式一

mysql支持insert into xx values(),(),()语法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


<sql id="insertColumnList">
    last_name,email,gender,dept_id
</sql>
<!--void insertEmployees(List<Employee> employees);-->
<!--MySQL下批量保存：可以foreach遍历   mysql支持values(),(),()语法-->
<insert id="insertEmployees">
    insert into tbl_employee(<include refid="insertColumnList"/>)
    values
    <foreach collection="list" item="emp" separator=",">
        (#{emp.lastName}, #{emp.email},#{emp.gender},#{emp.dept.id})
    </foreach>
</insert>

测试类：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


@Test
public void test3() throws IOException {
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

    try (SqlSession session = sqlSessionFactory.openSession(true)) {
        EmployeeMapperDynamic employeeMapper = session.getMapper(EmployeeMapperDynamic.class);
        List<Employee> employees = Lists.newArrayList(
                new Employee(null, "宋江", "1", "sj@sh.com", new Dept(1, null, null)),
                new Employee(null, "武松", "1", "ws@sh.com", new Dept(2, null, null))
        );
        employeeMapper.insertEmployees(employees);
    }
}

方式二

insert into xx values();

修改sql映射文件：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


  <sql id="insertColumnList">
      last_name,email,gender,dept_id
  </sql>
  <!-- 这种方式需要数据库连接属性allowMultiQueries=true；
如：jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true
   这种分号分隔多个sql可以用于其他的批量操作（删除，修改） -->
  <insert id="insertEmployees">
      <foreach collection="list" item="emp" separator=";">
          insert into tbl_employee(<include refid="insertColumnList"/>)
          values
          (#{emp.lastName}, #{emp.email},#{emp.gender},#{emp.dept.id})
      </foreach>
  </insert>

db设置：

1

url=jdbc:mysql://localhost:3306/db01?useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true&allowMultiQueries=true

两个内置参数

 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


<!-- 两个内置参数：
	 	不只是方法传递过来的参数可以被用来判断，取值。。。
	 	mybatis默认还有两个内置参数：
	 	_parameter:代表整个参数
	 		单个参数：_parameter就是这个参数
	 		多个参数：参数会被封装为一个map；_parameter就是代表这个map
	 	
	 	_databaseId:如果配置了databaseIdProvider标签。
	 		_databaseId就是代表当前数据库的别名oracle
	  -->
	  
	  <!--public List<Employee> getEmpsTestInnerParameter(Employee employee);  -->
	  <select id="getEmpsTestInnerParameter" resultType="com.atguigu.mybatis.bean.Employee">
	  		<if test="_databaseId=='mysql'">
	  			select * from tbl_employee
	  			<if test="_parameter!=null">
	  				where last_name like #{lastName}
	  			</if>
	  		</if>
	  		<if test="_databaseId=='oracle'">
	  			select * from employees
	  			<if test="_parameter!=null">
	  				where last_name like #{_parameter.lastName}
	  			</if>
	  		</if>
	  </select>

bind绑定

bind：可以将OGNL表达式的值绑定到一个变量中，方便后来引用这个变量的值

示例：

1
2
3
4


<select id="getMultiMapByLastNameLike" resultType="map">
    <bind name="_last_Name" value="'%'+lastName+'%'"></bind>
    select * from tbl_employee where last_name like #{lastName}
</select>

注意：对于模糊查询来说，不推荐使用绑定，可以在传值的时候直接附上%，那样也比较灵活！

抽取可重用sql片段

 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


<!-- 
	  	抽取可重用的sql片段。方便后面引用 
	  	1、sql抽取：经常将要查询的列名，或者插入用的列名抽取出来方便引用
	  	2、include来引用已经抽取的sql：
	  	3、include还可以自定义一些property，sql标签内部就能使用自定义的属性
	  			include-property：取值的正确方式 ${prop},
	  			不能使用这种方式 #{prop}
	  -->
	<insert id="addEmps">
       <!--insert into tbl_employee(last_name,email,gender,d_id)-->
	 	insert into tbl_employee(
	 		<include refid="insertColumn"></include><!--这里可以引用下面的sql片段-->
	 	) 
		values
		<foreach collection="emps" item="emp" separator=",">
			(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
		</foreach>
	 </insert>

	  <sql id="insertColumn">
	  		<if test="_databaseId=='oracle'">
	  			employee_id,last_name,email
	  		</if>
	  		<if test="_databaseId=='mysql'">
	  			last_name,email,gender,d_id
	  		</if>
	  </sql>