mybatis动态sql

一、if 标签

if 标签单分支判断语句

1. DeptMapper.java

public interface DeptMapper {
    List<Dept> queryByIf(Dept dept);
}

2. DeptMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
	PUBLIC "-//mybatis.org//DTDMapper3.0//EN"
	"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dyh.dao.DeptMapper">
	<!-- type属性值是实体类，id属性值，为映射的唯一标识  -->
	<resultMap type="dept" id="resultMapDept">
		<id property="deptno" column="deptno" />
		<!-- property实体类中的字段，column表示查询结果中的列名  -->
		<result property="dname" column="dname" />
		<result property="city" column="loc" />
	</resultMap>

	<sql id="deptColumns">
		deptno,dname,loc as city
	</sql>
	<select id="queryByIf" resultType="dept">
		select <include refid="deptColumns"/> from dept_bak where 1=1
		<if test="deptno != null and deptno != '' ">
			and deptno = #{deptno}
		</if>
		<if test="dname != null and dname != '' ">
			and dname = #{dname}
		</if>
		<if test="city != null and city != '' ">
			and loc = #{city}
		</if>
	</select>

</mapper>

3. DeptMapperTest.java

package com.dyh.test;

import com.dyh.dao.DeptMapper;
import com.dyh.pojo.Dept;
import com.dyh.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

import static org.junit.Assert.*;

public class DeptMapperTest {
    SqlSession session;
    DeptMapper mapper;

    @org.junit.Before
    public void setUp() throws Exception {
        session = MybatisUtil.getConnection();
        mapper = session.getMapper(DeptMapper.class);
    }

    @Test
    public void testQueryByIf(){
        Dept dept = new Dept();
        //dept.setDeptno(1);
        dept.setDname("财务部");
        dept.setCity("南京");
        List<Dept> list = mapper.queryByIf(dept);
        list.forEach(System.out::println);
    }

    @org.junit.After
    public void tearDown() throws Exception {
        MybatisUtil.closeConnection();
    }
}

二、choose、 when、 otherwise标签

choose是多选一，类似于switch……case中加了break。

1. DeptMapper.java

添加如下方法：

List<Dept> queryByChoose(Dept dept);

2. DeptMapper.xml

<select id="queryByChoose" resultType="dept">
		select <include refid="deptColumns"/> from dept_bak where 1=1
		<choose>
			<when test="deptno != null and deptno != '' ">
				and deptno = #{deptno}
			</when>
			<when test="dname != null and dname != '' ">
				and dname = #{dname}
			</when>
			<when test="city != null and city != '' ">
				and loc = #{city}
			</when>
			<otherwise>
				and deptno = 1
			</otherwise>
		</choose>
	</select>

3. TestDeptMapper.java

@Test
    public void testQueryByChoose(){
        Dept dept = new Dept();
        //dept.setDeptno(1);
        dept.setDname("财务部");
        dept.setCity("南京");
        List<Dept> list = mapper.queryByChoose(dept);
        list.forEach(System.out::println);
    }

测试运行结果

 

三、where标签

使用 where 标签，就不需要提供where 1=1 这样的条件了。 如果判断条件不为空则自动添加 where 关键字，并且会自动去掉第一个条件前面的 and 或or。

1. DeptMapper.java

添加如下代码：

List<Dept> queryByWhere(Dept dept);

2. DeptMapper.xml

添加如下代码：

<select id="queryByWhere" resultType="dept">
		select <include refid="deptColumns"/> from dept_bak
		<where>
			<if test="deptno != null and deptno != '' ">
				and deptno = #{deptno}
			</if>
			<if test="dname != null and dname != '' ">
				and dname = #{dname}
			</if>
			<if test="city != null and city != '' ">
				and loc = #{city}
			</if>
		</where>
	</select>

3. DeptMapperTest.java

添加如下代码：

@Test
    public void testQueryByWhere(){
        Dept dept = new Dept();
        //dept.setDeptno(1);
        dept.setDname("财务部");
        dept.setCity("南京");
        List<Dept> list = mapper.queryByWhere(dept);
        list.forEach(System.out::println);
    }

四、bind标签

bind 标签允许我们在 OGNL 表达式以外创建一个变量，并可以将其绑定到当前的 SQL 语句中。 一般应用于模糊查询，通过 bind 绑定通配符和查询值。

1. DeptMapper.java

其中添加如下代码：

 /**
     * 根据部门名称模糊查询，使用bind标签
     * @param dname
     * @return
     */
    List<Dept> queryByLikeBind(String dname);

2. DeptMapper.xml

其中添加如下代码：

<select id="queryByLikeBind" resultType="dept">
		<bind name="likeDname" value="'%'+dname+'%'" />
		select <include refid="deptColumns"/> from dept_bak
		where dname like #{likeDname}
	</select>

3. DeptMapperTest.java

添加如下代码：

 @Test
    public void testQueryLikeByBind(){
        List<Dept> list = mapper.queryByLikeBind("务");
        list.forEach(System.out::println);
    }

五、set标签

set 标签用在 update 语句中。 借助 if 标签，可以只对有具体值的字段进行更新。 set 标 签会自动添加 set 关键字， 自动去掉最后一个 if 语句的多余的逗号。

1. DeptMapper.java

  /**
     * 更新数据，使用set标签
     * @param dept
     */
    void updateDeptUseSet(Dept dept);

2. DeptMapper.xml

添加如下代码：

<update id="updateDeptUseSet" parameterType="dept">
		update dept_bak
		<set>
			<if test="dname != null and dname !='' ">
				dname = #{dname},
			</if>
			<if test="city != null and city !='' ">
				loc = #{city},
			</if>
		</set>
		where deptno = #{deptno}
	</update>

3. DeptMapperTest.java

添加如下代码：

 @Test
    public void testupdateDeptUseSet(){
        Dept dept = new Dept(10,"acount","纽约");
        dept.setCity(null);	// 第二次测试
        dept.setDeptno(11);
        dept.setDeptno(12);	// 第三次测试
        dept.setCity("伦敦");
        dept.setDname(null);
        mapper.updateDeptUseSet(dept);
        session.commit();
    }

六、foreach标签

foreach 标签的功能非常强大，我们可以将任何可迭代对象如 List、 Set 、 Map 或者数 组对象作为集合参数传递给 foreach 标签进行遍历。它也允许我们指定开头与结尾的字符串以及集合项迭代之间的分隔符。

属性	描述
collection	表示迭代集合的名称或类型。如果使用名称指定，可以使用@Param 注解指定。 该属 性为必选属性。
item	表示本次迭代获取的元素，若 collection 为 List、 Set 或者数组，则表示其中的元素； 若 collection 为 map，则代表 key-value 的 value，该属性为必选属性。
open	表示该语句以什么开始，最常用的是左括弧’(’，注意:Mybatis 会将该字符拼接到 循环元素之前，并且只拼接一次，该属性为可选属性。
index	在 List、 Set 和数组中,index 表示当前迭代的序号。 在 Map 中，index 为元素的 Key， item 为元素的 Value。 该属性为可选属性。
separator	在每次循环中为 SQL 语句添加 separator 属性指定的字符，但最后一次循环不在添加。 该属性为可选属性。
close	表示该语句以什么结束，最常用的是右括弧’)’，注意:Mybatis 会将该字符拼接到 循环元素之后，并且只拼接一次，该属性为可选属性。

1、遍历Collection或数组

1.1 DeptMapper.java

增加如下代码：

 /**
     * 根据id集合查询
     * @param collection  id集合
     */
    List<Dept> queryInIdsCollection(Collection collection);

    /**
     * 根据id数组查询
     * @param ids     id数组
     */
    List<Dept> queryInIdsArray(int[] ids);

1.2 DeptMapper.xml

增加如下代码：

<select id="queryInIdsCollection" resultType="dept">
		select <include refid="deptColumns"/> from dept_bak where deptno in
		<foreach collection="collection" item="did" separator="," open="(" close=")">
			#{did}
		</foreach>
	</select>

	<select id="queryInIdsArray" resultType="dept">
		select <include refid="deptColumns"/> from dept_bak where deptno in
		<foreach collection="array" item="did" separator="," open="(" close=")">
			#{did}
		</foreach>
	</select>

1.3 DeptMapperTest.java

添加如下代码：

  @Test
    public void testQueryByIdsCollection(){
        List<Integer> list = new ArrayList<>();
        list.add(1);
        list.add(3);
        list.add(5);
        List<Dept> list1 = mapper.queryInIdsCollection(list);
        list1.forEach(System.out::println);
        Set<Integer> set = new HashSet<>();
        set.add(7);
        set.add(8);
        set.add(9);
        List<Dept> list2 = mapper.queryInIdsCollection(set);
        list2.forEach(System.out::println);
    }

    @Test
    public void testQueryByIdsArray(){
        int[] ids = new int[]{2,4,6};
        List<Dept> list =mapper.queryInIdsArray(ids);
        list.forEach(System.out::println);
    }

2、遍历Map

应用场景：组合查询，根据用户传递的参数拼接查询条件。

2.1 DeptMapper.java

增加如下代码：

/**
     * 组合查询，动态拼接SQL语句
     * @param map<String, Object>  : String 数据库中列名 Object 对象的属性值类型
     * @return
     */
    List<Dept> queryByMap(@Param(value="paramMap") Map<String, Object> map);

2.2 DeptMapper.xml

增加如下代码：

<select id="queryByMap" resultType="dept">
		select <include refid="deptColumns"/> from dept_bak where
		<foreach collection="paramMap" separator="and" index="key" item="value">
			${key} = #{value}
		</foreach>
	</select>

注意：collection后面的属性值表示参数，index获取map中的key值，item获取map中的key对应的value值。${}直接使用字符串，#{}会变成?，执行的时候用参数值替换。

2.3 DeptMapperTest.java

添加如下代码：

    @Test
    public void testQueryByMap(){
        Map<String, Object> paramMap = new HashMap<>();
        //paramMap.put("deptno", new Integer(9));
        paramMap.put("dname", "销售部");
        paramMap.put("loc", "深圳");
        List<Dept> list =mapper.queryByMap(paramMap);
        System.out.println(list.size());
        list.forEach(System.out::println);
    }

3、使用 foreach 标签完成批量添加

3.1 DeptMapper.java

增加如下代码：

 /**
     * 批量插入数据
     * @param depts   部门集合
     */
    void batchAdd(@Param(value="depts") List<Dept> depts);

3.2 DeptMapper.xml

增加如下代码：

<insert id="batchAdd" parameterType="dept">
		insert  into dept_bak(deptno, dname, loc) values
		<foreach collection="depts" item="d" separator=",">
			(default , #{d.dname}, #{d.city})
		</foreach>
	</insert>

最终SQL批量插入的语句，要拼接成下面的效果

INSERT INTO dept_bak(deptno,dname,loc) 
VALUES(DEFAULT,'研发部','上海'),
(DEFAULT,'策划部','武汉'),
(DEFAULT,'销售部','深圳');

3.3 DeptMapperTest.java

增加如下代码：

 @Test
    public void testBatchAdd(){
        List<Dept> depts = new ArrayList<>();
        depts.add(new Dept(17, "华山派", "陕西"));
        depts.add(new Dept(17, "嵩山派", "河南"));
        depts.add(new Dept(17, "衡山牌", "湖南"));
        mapper.batchAdd(depts);
        session.commit();
    }

总结

1、if 标签单分支判断语句，test后的值为测试条件；

2、choose……when……otherwise是多选一，类似于switch……case中加了break；

3、使用 where 标签，就不需要提供where 1=1 这样的条件了。 如果判断条件不为空则自动添加 where 关键字，并且会自动去掉第一个条件前面的 and 或or。

4、bind 标签允许我们在 OGNL 表达式以外创建一个变量，并可以将其绑定到当前的 SQL 语句中。 一般应用于模糊查询，通过 bind 绑定通配符和查询值。

5、set 标签用在 update 语句中。 借助 if 标签，可以只对有具体值的字段进行更新。 set 标 签会自动添加 set 关键字， 自动去掉最后一个 if 语句的多余的逗号。

6、foreach 标签的功能非常强大，我们可以将任何可迭代对象如 List、 Set 、 Map 或者数 组对象作为集合参数传递给 foreach 标签进行遍历。它也允许我们指定开头与结尾的字符串以及集合项迭代之间的分隔符。