2021-01-31

MyBatis--动态SQL

一、if判断&OGNL

1、新增方法和配置

public interface EmployeeMapperDynamicSQL {
	public List<Employee> getEmpsByConditionIf(Employee employee);

}

<!--全局配置中-->
<mappers>
	 	<mapper resource="EmployeeMapperDynamicSQL.xml"/>
	 </mappers>

2、xml配置

<!-- 查询员工，要求携带了哪个字段查询条件就带上这个字段的值 -->
	<select id="getEmpsByConditionIf" resultType="com.fenga.mybatis.bean.Employee">
		select * from tbl_employee 
		where 
		<!-- test:判断表达式（OGNL） -->
		<if test="id!=null">
			id=#{id}
		</if>
		<if test="lastName!=null and lastName!=&quot;&quot;">
			and last_name like #{lastName}
		</if>
		<if test="email!=null and email.trim()!=&quot;&quot;">
			and email=#{email}
		</if>
		<if test="gender==0 or gender==1">
			and gender=#{gender}
		</if>
	</select>

3、测试及效果

@Test
	public void test1() throws IOException {
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession openSession = sqlSessionFactory.openSession();
		
		try {
			EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
			Employee employee = new Employee(3, "%a%", "marry@qq.com", null);
			List<Employee> emps = mapper.getEmpsByConditionIf(employee);
			for(Employee emp : emps) {
				System.out.println(emp);			
			}
			
		}finally {
			openSession.close();
		}
		
	}

二、where查询条件

如上代码，查询的时候如果某些条件没带可能sql拼装会有问题

解决办法：

1、给where后面添加1=1，以后的条件都and XXX

2、mybatis使用where标签来将所有的查询条件包括在内；mybatis就会将where标签中拼接的SQL想，多出来的and或者or去掉

where只会去掉第一个多出来的and或者or（例：可以去掉and id=#{id}的and，不会去掉id=#{id} and 的and）

<select id="getEmpsByConditionIf" resultType="com.fenga.mybatis.bean.Employee">
		select * from tbl_employee 
		<where> 
			<!-- test:判断表达式（OGNL） -->
			<if test="id!=null">
				id=#{id}
			</if>
			<if test="lastName!=null and lastName!=&quot;&quot;">
				and last_name like #{lastName}
			</if>
			<if test="email!=null and email.trim()!=&quot;&quot;">
				and email=#{email}
			</if>
			<if test="gender==0 or gender==1">
				and gender=#{gender}
			</if>
		</where>
	</select>

@Test
	public void test1() throws IOException {
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession openSession = sqlSessionFactory.openSession();
		
		try {
			EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
			Employee employee = new Employee(null, "%r%", null, null);
			List<Employee> emps = mapper.getEmpsByConditionIf(employee);
			for(Employee emp : emps) {
				System.out.println(emp);			
			}
			
		}finally {
			openSession.close();
		}
		
	}

三、trim标签

为解决where标签所解决不了的问题

<!-- 
		prefix="" 	:前缀，标签trim标签体中是整体字符拼串后的结果。prefix给拼串后的整体字符拼串后的结果
		prefixOverrides="" :前缀覆盖，去掉整个字符串前面多余的字符
		suffix="" :后缀，suffix给拼串后的整体字符拼串后的结果
		suffixOverrides="" :后缀覆盖，去掉整个字符串后面多余的字符
	 -->
	<select id="getEmpsByConditionTrim" resultType="com.fenga.mybatis.bean.Employee">
		select * from tbl_employee 
		<trim prefix="where" suffixOverrides="and">
			<if test="id!=null">
				id=#{id} and
			</if>
			<if test="lastName!=null and lastName!=&quot;&quot;">
				last_name like #{lastName} and
			</if>
			<if test="email!=null and email.trim()!=&quot;&quot;">
				email=#{email} and
			</if>
			<if test="gender==0 or gender==1">
				gender=#{gender}
			</if>
		</trim>
	</select>

四、choose——分支选择

当一个条件满足，不会查询后面的条件

<select id="getEmpsByConditionChoose" resultType="com.fenga.mybatis.bean.Employee">
		select * from tbl_employee 
		<where>
			<choose>
				<when test="id!=null">
					id=#{id}
				</when>
				<when test="lastName!=null">
					last_name like #{lastName}
				</when>
				<when test="email!=null">
					email=#{email}
				</when>
				
				<otherwise>
					gender = 0
				</otherwise>
			</choose>
		</where>
	</select>

五、set与if动态结合

方法一

<update id="updateEmp">
		update tbl_employee
		<set>
			<if test="lastName!=null">
				last_name=#{lastName},
			</if>
			<if test="email!=null">
				emai=#{emai},
			</if>
			<if test="gender!=null">
				gender=#{gender}
			</if>
		</set>
		where id=#{id}
	</update>

方法二

<update id="updateEmp">
		update tbl_employee
		<trim prefix="set" suffixOverrides=",">
			<if test="lastName!=null">
				last_name=#{lastName},
			</if>
			<if test="email!=null">
				emai=#{emai},
			</if>
			<if test="gender!=null">
				gender=#{gender}
			</if>
		</trim>
		where id=#{id}
	</update>

六、foreach

//此处若没加@Param给变量命名，可能会出现Parameter 'ids' not found. Available parameters are [arg0, collection, list]错误

public List<Employee> getEmpsByConditionForeach(@Param("ids")List<Integer> ids);

<select id="getEmpsByConditionForeach" resultType="com.fenga.mybatis.bean.Employee">
		select * from tbl_employee
	 	<!--
	 		collection：指定要遍历的集合：
	 			list类型的参数会特殊处理封装在map中，map的key就叫list
	 		item：将当前遍历出的元素赋值给指定的变量
	 		separator:每个元素之间的分隔符
	 		open：遍历出所有结果拼接一个开始的字符
	 		close:遍历出所有结果拼接一个结束的字符
	 		index:索引。遍历list的时候是index就是索引，item就是当前值
	 				      遍历map的时候index表示的就是map的key，item就是map的值
	 		
	 		#{变量名}就能取出变量的值也就是当前遍历出的元素
	 	  -->
	 	<foreach collection="ids" item="item_id" separator=","
	 		open="where id in(" close=")">
	 		#{item_id}
	 	</foreach>
	</select>

@Test
	public void test1() throws IOException {
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession openSession = sqlSessionFactory.openSession();
		
		try {
			EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
			
			List<Employee> list = mapper.getEmpsByConditionForeach(Arrays.asList(1,2,3));
			for (Employee emp : list) {
				System.out.println(emp);
			}
			
		}finally {
			openSession.close();
		}
		
	}

 

用于批量保存，代码如下：

//dao
public void addEmps(@Param("emps")List<Employee> emps);
//xml
<insert id="addEmps">
		insert into tbl_employee(last_name,email,gender,d_id)
		values
		<foreach collection="emps" item="emp" separator=",">
			(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
		</foreach>
	</insert>

七、bind绑定

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

<bind name="_lastName" value="'%'+lastName+'%'"/>
select * from tbl_employee
<if test="_parameter!=null">
where last_name like #{_lastName}
</if>

八、SQL标签

抽取可重用看的sql片段

<sql id="xxx">
    xxxx
</sql>

<!--引用-->
<include refid="xxx"/>

九、两个重要的内置参数_parameter&_databaseld

_parameter:代表整个参数

单个参数：_parameter就是这个参数

多个参数：参数会被封装为一个map，_parameter就是代表这个map

_databaseld：如果配置了DatabaseIdProvider标签

_databaseld就是代表当前数据库的别名