七、Mybatis动态SQL

动态SQL:根据不同的条件需要执行不同的 SQL 命令.称为动态 SQL

 MyBatis 中动态 SQL 在 mapper.xml 中添加逻辑判断等.

 If 使用：

接口Mapper方法:

/**
 * 动态SQL where的使用
 */
List<Log> selectByWhereAccInOrAccOut(@Param("accIn")String accIn,@Param("accOut")String accOut);

 映射文件Mapper.xml：

	<!-- 动态SQL if使用-->
	<select id="selectByAccInOrAccOut" resultType="Log">
		select * from log where 1=1
		<!-- OGNL表达式，直接写key或对象的属性，不需要添加任何特定符号 -->
		<if test="accIn!=null and accIn!=''">
			and accIn = #{accIn}
		</if> 
		<if test="accOut!=null and accOut!=''">
			and accOut = #{accOut}
		</if>
	</select>

测试类：

	@Test
	public void testDynamicSqlByWhere() throws Exception {
		Scanner scanner = new Scanner(System.in);
		System.out.println("请输入收款账号:");
		String accIn = scanner.nextLine();
		System.out.println("请输入付款账号:");
		String accOut = scanner.nextLine();
		
		LogMapper logMapper = session.getMapper(LogMapper.class);
		List<Log> list = logMapper.selectByWhereAccInOrAccOut(accIn, accOut);
		System.out.println(list.toString());
	}

where使用：

1.当编写 where 标签时,如果内容中第一个是 and 去掉第一个

2.如果<where>中有内容会生成 where 关键字,如果没有内容不生成 where 关键

3.比直接使用<if>少写 where 1=1

接口Mapper方法:

	/**
	 * 动态SQL where的使用
	 */
	List<Log> selectByWhereAccInOrAccOut(@Param("accIn")String accIn,@Param("accOut")String accOut);

 映射文件Mapper.xml：

	<!-- 动态SQL where使用-->
	<select id="selectByWhereAccInOrAccOut" resultType="Log">
		select * from log
		<where>
			<if test="accIn!=null and accIn!=''">
				and accIn = #{accIn}
			</if> 
			<if test="accOut!=null and accOut!=''">
				and accOut = #{accOut}
			</if>
		</where>
		
	</select>

测试类：

	@Test
	public void testDynamicSqlByWhere() throws Exception {
		Scanner scanner = new Scanner(System.in);
		System.out.println("请输入收款账号:");
		String accIn = scanner.nextLine();
		System.out.println("请输入付款账号:");
		String accOut = scanner.nextLine();
		
		LogMapper logMapper = session.getMapper(LogMapper.class);
		List<Log> list = logMapper.selectByWhereAccInOrAccOut(accIn, accOut);
		System.out.println(list.toString());
	}

choose when otherwise 的使用：

1.只有有一个成立,其他都不执行.

2.如果 accin 和 accout 都不是 null 或不是””生成的 sql 中只有 where accin=? 就是只会执行第一个满足的

3.如果都不满足，就执行 <otherwise>

接口Mapper方法:

	/**
	 * 动态SQL choose when otherwise的使用
	 */
	List<Log> selectByChooseAccInAccout(@Param("accIn")String accIn,@Param("accOut")String accOut);

映射文件Mapper.xml：

	<!-- 动态SQL choose when otherwise 使用-->
	<select id="selectByChooseAccInAccout" resultType="Log">
		select * from log
		<where>
			<choose>
				<when test="accIn!=null and accIn!=''">
					and accIn = #{accIn}
				</when>
				<when test="accOut!=null and accOut!=''">
					and accOut = #{accOut}
				</when>
				<otherwise>
					and accIn = 1
				</otherwise>
			</choose>
		</where>
	</select>

测试类：

	@Test
	public void testDynamicSqlByChoose() throws Exception {
		Scanner scanner = new Scanner(System.in);
		System.out.println("请输入收款账号:");
		String accIn = scanner.nextLine();
		System.out.println("请输入付款账号:");
		String accOut = scanner.nextLine();
		
		LogMapper logMapper = session.getMapper(LogMapper.class);
		List<Log> list = logMapper.selectByChooseAccInAccout(accIn, accOut);
		System.out.println(list.toString());
	}

set的使用: <set>用在修改 SQL 中 set 从句

1.作用:去掉最后一个逗号

2.如果<set>里面有内容生成 set 关键字,没有就不生成

3.id=#{id} 目的防止<set>中没有内容,mybatis 不生成 set 关键字,如果修改中没有 set 从句 SQL 语法错误.

接口Mapper方法:

	/**
	 * 动态SQL set的使用
	 */
	int updateBySet(Log log);

映射文件Mapper.xml：

	<!-- 动态SQL set使用-->
	<update id="updateBySet" parameterType="Log">
		update log
		<set>
			id = #{id},
			<if test="accIn!=null and accIn!=''">
				accIn = #{accIn},
			</if>
			
			<if test="accOut!=null and accOut!=''">
				accOut = #{accOut},
			</if>
		</set>
		where id = #{id}
	</update>

测试类：

	@Test
	public void testDynamicSqlBySet() throws Exception {
		Scanner scanner = new Scanner(System.in);
		System.out.println("请输入收款账号:");
		String accIn = scanner.nextLine();
		System.out.println("请输入付款账号:");
		String accOut = scanner.nextLine();
		Log log = new Log(1,accIn,accOut);
		System.out.println(log.getId());
		LogMapper logMapper = session.getMapper(LogMapper.class);
		int index = logMapper.updateBySet(log);
		System.out.println(index);
		session.commit();
	}

trim的使用:模拟where

1.prefix 在前面添加内容  prefixOverrides 去掉前面内容   suffix 在后面添加内容  suffixOverrieds 去掉后面内容

2. 执行顺序：先去掉内容后添加内容

接口Mapper方法:

	/**
	 * 动态SQL trim的使用 模拟where
	 */
	List<Log> selectByTrimPrefix(@Param("accIn")String accIn,@Param("accOut")String accOut);

映射文件Mapper.xml：

	<!-- 动态SQL使用trim模拟where 在前面添加标记内容-prefix 去掉第一个标记的内容-prefix-->
	<select id="selectByTrimPrefix" resultType="Log">
		select * from log
		<trim prefix="where" prefixOverrides="and">
			and accIn = #{accIn}
		</trim>
		<trim prefix="and">
			accOut = #{accOut}
		</trim>
	</select>

测试类：

	@Test
	public void testDynamicSqlByTrimPrefix() throws Exception {
		Scanner scanner = new Scanner(System.in);
		System.out.println("请输入收款账号:");
		String accIn = scanner.nextLine();
		System.out.println("请输入付款账号:");
		String accOut = scanner.nextLine();
		LogMapper logMapper = session.getMapper(LogMapper.class);
		List<Log> list = logMapper.selectByTrimPrefix(accIn, accOut);
		System.out.println(list.toString());
	}

模拟Set:

接口Mapper方法:

	/**
	 * 动态SQL使用trim模拟set 
	 */
	int updateByTrimSet(Log log);

映射文件Mapper.xml：

	<!-- 动态SQL使用trim模拟set 在前面添加标记内容-prefix  去掉最后一个标记的内容-suffixOverrides -->
	<update id="updateByTrimSet" parameterType="Log">
		<bind name="accIn" value="￥"/>
		update log
		<trim prefix="set" suffixOverrides=",">
			id = #{id},
			<if test="accIn!=null and accIn!=''">
				accIn = #{accIn},
			</if>
			<if test="accOut!=null and accOut!=''">
				accOut = #{accOut},
			</if>
		</trim>
		where id=#{id}
		
	</update>

测试类：

	@Test
	public void testDynamicSqlByTrimSuffix() throws Exception {
		Scanner scanner = new Scanner(System.in);
		System.out.println("请输入收款账号:");
		String accIn = scanner.nextLine();
		System.out.println("请输入付款账号:");
		String accOut = scanner.nextLine();
		Log log = new Log(1,accIn,accOut);
		System.out.println(log.getId());
		LogMapper logMapper = session.getMapper(LogMapper.class);
		int index = logMapper.updateByTrimSet(log);
		System.out.println(index);
		session.commit();
	}

 <bind>使用：

作用:给参数重新赋值

场景：1. 模糊查询

           2.在原内容前或后添加内容

接口Mapper方法:

/**
	 * 动态SQL使用bind 绑定实现模糊查询
	 */
	List<Log> selectAllByBind(@Param("accIn")String accIn);

 

映射文件Mapper.xml：

	<select id="selectAllByBind" resultType="Log">
		<bind name="accIn" value="'%'+accIn+'%'"/>
		select * from log
			<where>
				<if test="accIn!=null and accIn!=''">
					accIn like #{accIn}
				</if>
			</where>
	</select>

测试类：

	@Test
	public void testDynamicSqlByBind() throws Exception {
		Scanner scanner = new Scanner(System.in);
		System.out.println("请输入收款账号:");
		String accIn = scanner.nextLine();
		LogMapper logMapper = session.getMapper(LogMapper.class);
		List<Log> list = logMapper.selectAllByBind(accIn);
		System.out.println(list.toString());
	}

foreach使用:

 collectino=”” 要遍历的集合   、item 迭代变量, #{迭代变量名}获取内容、 open 循环后左侧添加的内容

 close 循环后右侧添加的内容、 separator 每次循环时,元素之间的分隔符

作用：循环参数内容,还具备在内容的前后添加内容,还具备添加分隔符功能.

适用场景:in  查询中.批量新增中(mybatis 中 foreach 效率比较低)

如果希望批量新增：1.SQL 命令：

insert into log VALUES(default,1,2,3),(default,2,3,4),(default,3,4,5)

2.openSession()必须指定   底层 JDBC 的 PreparedStatement.addBatch();

factory.openSession(ExecutorType.BATCH);

接口Mapper方法:

	/**
	 * 动态SQL 使用foreach循环 范围查询 
	 */
	List<Log> selectByForEach(List<Integer> list);

映射文件Mapper.xml：

	<select id="selectByForEach" parameterType="list" resultType="Log">
		select * from log where id in
		<foreach collection="list" item="i" open="(" close=")" separator=",">
			#{i}
		</foreach>
	</select>

测试类：

	@Test
	public void testDynamicSqlByForEach() throws Exception {
		List<Integer> idList = new ArrayList<Integer>(Arrays.asList(1,2,3,4,5));
		LogMapper logMapper = session.getMapper(LogMapper.class);
		List<Log> list = logMapper.selectByForEach(idList);
		System.out.println(list.toString());
	}

<sql> 和<include>

某些 SQL 片段如果希望复用,可以使用<sql>定义这个片段：

映射文件： 在<select>或<delete>或<update>或<insert>中使用<include>引用

<sql id="mysql">
    id,accin,accout,money
</sql>

使用：

<select id="">
    select <include refid="mysql"></include>from log
</select>