Mybatis中的动态SQL

学习目标

• 了解常用的动态SQL元素及其作用
• 掌握动态SQL中主要元素的使用

---

动态SQL中的元素

元素	说明
<if>	判断语句，用于单条件分支判断
<choose><when><otherwise>	相当于Java中的switch...case...default语句，用于多条件分支判断
<where><trim><set>	辅助元素，用于处理一些SQL拼装，特殊字符问题
<foreach>	循环语句，常用于in灯光列举条件中
<bind>	从OGNL表达式中创建一个变量，并将其绑定到上下文，常用于模糊查询中的sql中

下面各元素在XML文件中的配置：

<?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.mybatis.mapper.CustomerMapper">
	<!-- <if>元素的使用 -->
<！-- <if>元素可通过多个条件精确的查询某个数据 -->
	<select id="findCustomerByNameAndJobs1"
		parameterType="com.mybatis.po.Customer"
		resultType="com.mybatis.po.Customer">
		select * from user where 1=1
		<if test="username 
		!=null and username !=''"> and username like concat('%',#{username},'%')
		</if>
		<if test="jobs != null and jobs !=''"> and jobs =#{jobs} </if>
	</select>

	<!-- <choose> <when> <otherwise> 元素使用 -->
<!-- 可实现从多个选项中选择一个去执行 -->
	<select id="findCustomerByNameOrJobs"
		parameterType="com.mybatis.po.Customer"
		resultType="com.mybatis.po.Customer">
		select * from user where 1=1
		<choose>
			<when test="username !=null and username !=''">
				and username like concat('%',#{username},'%')
			</when>
			<when test="jobs != null and jobs !=''">
				and jobs =#{jobs}
			</when>
			<otherwise>
				and id is not null
			</otherwise>
		</choose>
	</select>

	<!-- <where> 元素使用 -->
<!-- 替代映射文件中的 'where 1 = 1' -->
	<select id="findCustomerByNameAndJobs2"
		parameterType="com.mybatis.po.Customer"
		resultType="com.mybatis.po.Customer">
		select * from user
		<where>
			<if test="username 
		!=null and username !=''"> and username like concat('%',#{username},'%')
			</if>
			<if test="jobs != null and jobs !=''"> and jobs =#{jobs} </if>
		</where>
	</select>

	<!-- <trim>元素 -->
<!--  同样能实现对 'where 1 = 1' 的替换，作用是去除一些特殊的字符串 -->
	<select id="findCustomerByNameAndJobs3"
		parameterType="com.mybatis.po.Customer"
		resultType="com.mybatis.po.Customer">
		select * from user
		<trim prefix="where" prefixOverrides="and">
			<if test="username !=null and username !=''">
				and username like concat ('%',#{username},'%')
			</if>
			<if test="jobs != null and jobs !=''">
				and jobs=#{jobs}
			</if>
		</trim>
	</select>

	<!-- <set>元素使用 -->
<!-- <set> 元素主要用于更新操作，其主要作用是在动态包含的SQL语句前输出一个SET关键字，并将SQL语句中最后一个多余的逗号去除-->
	<update id="updateCustomer"
		parameterType="com.mybatis.po.Customer">
		update user
		<set>
			<if test="username !=null and username !=''">
				username =#{username}
			</if>
			<if test="jobs != null and jobs !=''">
				jobs =#{jobs}
			</if>
		</set>
		where id=#{id}
	</update>

	<!-- <foreach>元素使用 -->
<!--用于数组和集合循环遍历的方式-->
	<select id="findCustomerByIds" parameterType="List"
		resultType="com.mybatis.po.Customer">
		select * from user where id in
		<foreach item="id" index="index" collection="list" open="("
			separator="," close=")">
			#{id}
		</foreach>
	</select>

	<!-- <bind>元素的使用：根据客户名模糊查询客户信息 -->

	<select id="findCustomerByName"
		parameterType="com.mybatis.po.Customer"
		resultType="com.mybatis.po.Customer">
		<!-- _parameter.getUsername()也可直接写成传入的字段属性名，即username -->
		<bind name="pattern_username"
			value="'%'+_parameter.getUsername()+'%'" />
		select * from user
		where username like #{pattern_username}
	</select>

</mapper>	

 下面是具体的代码实现：

 

package com.mybatis.test;

import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.mybatis.po.Customer;
import com.mybatis.util.MybatisUtils;

public class MybatisTest {

	/*
	 * 根据客气姓名和职业组合条件查询客户信息列表
	 */

	@Test
	public void findCustomerByNameAndJobsTest() throws Exception {
		// 通过工具类生成SqlSession对象
		SqlSession sqlSession = MybatisUtils.getSession();
		// 创建Cuetomer对象，封装需要组合查询的条件
		Customer customer = new Customer();
		customer.setUsername("Rose"); // 数据库表中实际存在的名字
		customer.setJobs("公务员"); // 数据库表中实际存在的职位
		// 执行SqlSession的查询方法，返回结果集
		List<Customer> customers = sqlSession
				.selectList("com.mybatis.mapper" + ".CustomerMapper.findCustomerByNameAndJobs", customer);
		// CustomerMapper.xml中有3个findCustomerByNameAndJobs的id，查询时需加序号1，2，3
		// 输出查询信息
		for (Customer customer2 : customers) {
			// 打印输出结果
			System.out.println(customer2);
		}
		// 关闭 sqlSession
		sqlSession.close();
	}

	/*
	 * 根据客气姓名或职业组合条件查询客户信息列表
	 */
	@Test
	public void findCustomerByNameOrJobsTest() throws Exception {
		// 通过工具类生成SqlSession对象
		SqlSession sqlSession = MybatisUtils.getSession();
		// 创建Cuetomer对象，封装需要组合查询的条件
		Customer customer = new Customer();
		customer.setUsername("Rose"); // 数据库表中实际存在的名字
		customer.setJobs("公务员"); // 数据库表中实际存在的职位
		// 执行SqlSession的查询方法，返回结果集
		List<Customer> customers = sqlSession
				.selectList("com.mybatis.mapper" + ".CustomerMapper.findCustomerByNameOrJobs", customer);
		// 输出查询信息
		for (Customer customer2 : customers) {
			// 打印输出结果
			System.out.println(customer2);
		}
		// 关闭 sqlSession
		sqlSession.close();
	}

	/*
	 * 更新客户
	 */
	@Test
	public void updateCustomerTest() throws Exception {
		// 获取SqlSession对象
		SqlSession sqlSession = MybatisUtils.getSession();
		// 创建Cuetomer对象，封装需要组合查询的条件
		Customer customer = new Customer();
		customer.setId(2); // 数据库表中实际存在的id
		customer.setJobs("程序员"); // 数据库表中实际存在的职位
		// 执行SqlSession的查询方法，返回结果集
		int rows = sqlSession.update("com.mybatis.mapper" + ".CustomerMapper.updateCustomer1", customer);
		// 通过返回结果判断更新操作是否执行成功
		if (rows > 0) {
			System.out.println("您成功修改了" + rows + "条数据！");
		} else {
			System.out.println("执行修改操作失败！！！");
		}
		// 提交事务
		sqlSession.commit();
		// 关闭 sqlSession
		sqlSession.close();
	}

	/**
	 * 根据客户编号批量查询客户信息
	 */
	@Test
	public void findCustomerByIdsTest() throws Exception {
		// 获取SqlSession对象
		SqlSession sqlSession = MybatisUtils.getSession();
		// 创建List集合，封装查询id
		List<Integer> ids = new ArrayList<Integer>();
		ids.add(1);
		ids.add(2);
		ids.add(5);
		ids.add(6);
		List<Customer> customers = sqlSession.selectList("com.mybatis.mapper" + ".CustomerMapper.findCustomerByIds",
				ids);
		// 输出查询结果
		for (Customer customer : customers) {
			// 打印输出结果
			System.out.println(customer);
		}
		// 关闭 sqlSession
		sqlSession.close();
	}

	/*
	 * <bind>元素的使用：根据客户名模糊查询客户信息
	 */

	@Test
	public void findCustomerByNameTest() throws Exception {
		// 获取SqlSession对象
		SqlSession sqlSession = MybatisUtils.getSession();
		// 创建Customer对象，封装查询的条件
		Customer customer = new Customer();
		customer.setUsername("R");
		// 执行SqlSession的查询方法，返回结果集
		List<Customer> customers = sqlSession.selectList("com.mybatis.mapper" + ".CustomerMapper.findCustomerByName",
				customer);
		// 输出查询结果
		for (Customer customer2 : customers) {
			// 打印输出结果
			System.out.println(customer2);
		}
		// 关闭 sqlSession
		sqlSession.close();
	}
}