MyBatis 动态SQL

最新推荐文章于 2024-08-21 23:39:15 发布

澄清石灰水t

最新推荐文章于 2024-08-21 23:39:15 发布

阅读量232

点赞数

文章标签： mybatis

本文链接：https://blog.csdn.net/chengqingshihuishui/article/details/113732373

版权

1、动态SQL简介

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

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

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

总结：动态sql，就是在sql语句（select标签下）中，写OGNL表达式，让sql语句不是“死的”

2、if标签

（1）if:判断

test：判断表达式（OGNL）
OGNL参照PPT或者官方文档。
c:if test：从参数中取值进行判断，遇见特殊符号应该去写转义字符：&&

（2）where标签（sql语句中有坑，可是传的实参不填坑）

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

mybatis使用where标签来将所有的查询条件包括在内。mybatis就会将where标签中拼装的sql，多出来的and或者or去掉，但是where只会去掉第一个（最前面的）多出来的and或者or（末尾的去不掉）

choose (when, otherwise):分支选择；带了break的swtich-case
如果带了id就用id查，如果带了lastName就用lastName查;只会进入其中一个
• trim 字符串截取(where(封装查询条件), set(封装修改条件))
• foreach 遍历集合

（3）trim标签

正因为where标签只去掉前面的and 或者 or，没法去掉后面的。因此mybatis提供trim标签

trim 字符串截取(where(封装查询条件), 设置封装修改条件

（3）演示： if标签、where标签、trim标签

需求：查询员工，要求，携带了哪个字段查询条件就带上这个字段的值（字段的值是动态的，即随机的，有时候是含有e的lastname，有时候是含有h的lastname）

① 新建package com.atguigu.mybatis.dao 包，创建EmployeeMapperDynamicSQL.java接口

写两个方法，分别用于演示If 和 Trim标签

package com.atguigu.mybatis.dao;
public interface EmployeeMapperDynamicSQL {
	//携带了哪个字段查询条件就带上这个字段的值
	public List<Employee> getEmpsByConditionIf(Employee employee);
	public List<Employee> getEmpsByConditionTrim(Employee employee);

}

② 进入EmployeeMapperDynamicSQL.xml配置文件

❶ if 标签和 where标签 id为getEmpsByConditionIf

备注：where没法干掉后面多出来的and

<mapper namespace="com.atguigu.mybatis.dao.EmployeeMapperDynamicSQL">
	 <!-- 查询员工，要求，携带了哪个字段查询条件就带上这个字段的值 -->
	 <!-- public List<Employee> getEmpsByConditionIf(Employee employee); -->
	 <select id="getEmpsByConditionIf" resultType="com.atguigu.mybatis.bean.Employee">
	 	select * from tbl_employee
	 	<!-- where -->
	 	<where>
		 	<!-- test：判断表达式（OGNL）
		 	OGNL参照PPT或者官方文档。
		 	  	 c:if  test
		 	从参数中取值进行判断
		 	
		 	遇见特殊符号应该去写转义字符：查表
		 	&&：
		 	-->
		 	<if test="id!=null">
		 		id=#{id}
		 	</if>
		 	<if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;">
		 		and last_name like #{lastName}
		 	</if>
		 	<if test="email!=null and email.trim()!=&quot;&quot;">
		 		and email=#{email}
		 	</if> 
		 	<!-- ognl会进行字符串与数字的转换判断  "0"==0 -->
		 	<if test="gender==0 or gender==1">
		 	 	and gender=#{gender}
		 	</if>
	 	</where>
	 </select>
</mapper>

❷ trim配置

trim标签：由于where标签后面多出的and或者or where标签不能解决，trim标签应运而生

prefix="":前缀：trim标签体中是整个字符串拼串后的结果。
              prefix给拼串后的整个字符串加一个前缀，一般前面加一个where
      prefixOverrides="":
              前缀覆盖：去掉整个字符串前面多余的字符
      suffix="":后缀
              suffix给拼串后的整个字符串加一个后缀
      suffixOverrides=""
              后缀覆盖：去掉整个字符串后面多余的字符

	 <!--public List<Employee> getEmpsByConditionTrim(Employee employee);  -->
	 <select id="getEmpsByConditionTrim" resultType="com.atguigu.mybatis.bean.Employee">
	 	select * from tbl_employee
	 	<!-- 自定义字符串的截取规则 -->
	 	<trim prefix="where" suffixOverrides="and">
	 		<if test="id!=null">
		 		id=#{id} and
		 	</if>
		 	<if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;">
		 		last_name like #{lastName} and
		 	</if>
		 	<if test="email!=null and email.trim()!=&quot;&quot;">
		 		email=#{email} and
		 	</if> 
		 	<!-- ognl会进行字符串与数字的转换判断  "0"==0 -->
		 	<if test="gender==0 or gender==1">
		 	 	gender=#{gender}
		 	</if>
		 </trim>
	 </select>

③ mybatis.test测试文件

package com.atguigu.mybatis.test;


public class MyBatisTest {
   @Test
	public void testDynamicSql() throws IOException{
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession openSession = sqlSessionFactory.openSession();
		try{
			EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
			//select * from tbl_employee where id=? and last_name like ?
			//测试if\where
			Employee employee = new Employee(1, "Admin", null, null);
		/*	List<Employee> emps = mapper.getEmpsByConditionIf(employee );
			for (Employee emp : emps) {
				System.out.println(emp);
			}*/
			
			//测试Trim，
			List<Employee> emps2 = mapper.getEmpsByConditionTrim(employee);
			for (Employee emp : emps2) {
				System.out.println(emp);
			}
}

3、choose（when otherwise）标签分支选择

简单的说，就是分支选择；即带了break的swtich-case

需求：如果提供员工部分信息（id或者lastName），如果带了id，就用id查。如果带了lastName就用lastName查，只会进入其中一个查询

（1）进入接口中

conditionchoose方法

package com.atguigu.mybatis.dao;
public interface EmployeeMapperDynamicSQL {
	public List<Employee> getEmpsByConditionChoose(Employee employee);
}

（2）进入映射文件中

 <!-- public List<Employee> getEmpsByConditionChoose(Employee employee); -->
	 <select id="getEmpsByConditionChoose" resultType="com.atguigu.mybatis.bean.Employee">
	 	select * from tbl_employee 
	 	<where>
	 		<!-- 如果带了id就用id查，如果带了lastName就用lastName查;只会进入其中一个 -->
	 		<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>

（3）测试文件中

package com.atguigu.mybatis.test;


public class MyBatisTest {
   @Test
	public void testDynamicSql() throws IOException{
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession openSession = sqlSessionFactory.openSession();
		try{
			EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
            Employee employee = new Employee(1, "Admin", null, null);
	        //测试choose
			List<Employee> list = mapper.getEmpsByConditionChoose(employee);
			for (Employee emp : list) {
				System.out.println(emp);
			}
}

进阶：set标签可以和 trim搭配同时和If联动，就是mysql的set关键字

接口文件中：

package com.atguigu.mybatis.dao;
public interface EmployeeMapperDynamicSQL {
	public void updateEmp(Employee employee);
}

映射文件中

复习Mysql修改语法：

UPDATE 表名（执行1 MYSQL首先找到对应数据表）
SET 列名=新值，列名=新值…（执行3 MYSQL按照新值修改）
WHERE 筛选条件（执行2 MYSQL确认数据表里要修改的列）

<!--public void updateEmp(Employee employee);  -->
	 <update id="updateEmp">
	 	<!-- Set标签的使用 -->
	 	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>

说明：set标签非常贴心，还可以解决sql语句末尾的多于逗号。

方法二：使用trim更新拼串

prefix= set ，就相当于用了mysql的set 修改语法，通过suffixOverrides属性做掉多余的“，”

<!--public void updateEmp(Employee employee);  -->
		Trim：更新拼串
		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>

测试文件中

updateEmp(employee)

package com.atguigu.mybatis.test;


public class MyBatisTest {
   @Test
	public void testDynamicSql() throws IOException{
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession openSession = sqlSessionFactory.openSession();
		try{
			EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
            Employee employee = new Employee(1, "Admin", null, null);
	        //测试set标签
			mapper.updateEmp(employee);
			openSession.commit();
}

null的部分，就不会更新了

4、foreach:遍历查询到的元素

动态SQL 的另外一个常用的必要操作是需要对一个集合进行遍历，通常是在构建IN 条件语句的时候。

复习：mysql模糊查询https://blog.csdn.net/chengqingshihuishui/article/details/110354636
IN：判断某字段的值是否属于in列表中的某一项

Ⅰ 使用in可以提高语句简洁度（相比用OR）

Ⅱ in列表的值的类型必须一致或兼容（指可以隐式转换为同一类型）

exp：查询员工工种编号是 IT、AD、VP中的一个员工名和工种编号

SELECT last_name,job_id FROM employees WHERE job_id IN( 'IT', 'AD' ,'VP' );

等价于 SELECT last_name,job_id FROM employees WHERE job_id ='IT', OR job_id ='AD' OR job_id ='VP' ;

接口中，该方法想让实现类实现，遍历传进来的集合

package com.atguigu.mybatis.dao;
public interface EmployeeMapperDynamicSQL {
	//查询员工id'在给定集合中的
	public List<Employee> getEmpsByConditionForeach(@Param("ids")List<Integer> ids);
}

映射文件中

foreach标签讲解：

collection：指定要遍历的集合：list类型的参数会特殊处理封装在map中，map的key就叫list
item：将当前遍历出的元素赋值给指定的变量
separator:每个元素之间的分隔符
open：遍历出所有结果拼接一个开始的字符
close:遍历出所有结果拼接一个结束的字符
index:索引。遍历list的时候是index就是索引，item就是当前值
遍历map的时候index表示的就是map的key，item就是map的值

#{变量名}就能取出变量的值也就是当前遍历出的元素

 <!--public List<Employee> getEmpsByConditionForeach(List<Integer> ids);  -->
	 <select id="getEmpsByConditionForeach" resultType="com.atguigu.mybatis.bean.Employee">
	 	select * from tbl_employee
	 	<foreach collection="ids" item="item_id" separator=","
	 		open="where id in(" close=")">
	 		#{item_id}
	 	</foreach>
	 </select>

测试类中，这里用工具类Array.asList造了一个集合

package com.atguigu.mybatis.test;


public class MyBatisTest {
   @Test
	public void testDynamicSql() 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));
			for (Employee emp : list) {
				System.out.println(emp);
}

成功执行出sql语句：select * from tbl_employee where id in(?,?)

Employee[id=1,lastName=Admin,email=jerry@atguigu.com,gener=0]

5、Mysql 用forEach向数据库批量插入行

扩展：sql标签

抽取可重用的sql片段，方便后面引用

step1 sql抽取：经常将要查询的列名，或者插入用的列名抽取出来方便引用
step2 抽取完成后，include来引用已经抽取的sql：
进阶：include还可以自定义一些property，sql标签内部就能使用自定义的属性
include-property：取值的正确方式${prop},
#{不能使用这种方式}

dao中

addEmps

package com.atguigu.mybatis.dao;

public interface EmployeeMapperDynamicSQL {
  public void addEmps(@Param("emps")List<Employee> emps);
}

映射文件中

批量插入方式

<!-- 批量插入 -->
	 <!--public void addEmps(@Param("emps")List<Employee> emps);  -->
	 <!--MySQL下批量保存：可以foreach遍历   mysql支持values(),(),()语法-->
	<insert id="addEmps">
	 	insert into tbl_employee(
	 		<include refid="insertColumn"></include>
	 	) 
		values
		<foreach collection="emps" item="emp" separator=",">
			(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
		</foreach>
	 </insert>

<!--抽取要反复用的语句，同时加入if判断-->
<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>

测试文件中

package com.atguigu.mybatis.test;


public class MyBatisTest {
   @Test
	public void testBatchSave() throws IOException{
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession openSession = sqlSessionFactory.openSession();
		try{
			EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
			List<Employee> emps = new ArrayList<>();
			emps.add(new Employee(null, "smith0x1", "smith0x1@atguigu.com", "1",new Department(1)));
			emps.add(new Employee(null, "allen0x1", "allen0x1@atguigu.com", "0",new Department(1)));
			mapper.addEmps(emps);
			openSession.commit();
		}finally{
			openSession.close();
		}
	}
}

6、两个内置参数( _parameter和_databaseId) bind标签

mybatis默认还有两个内置参数：
（1）_parameter:代表整个参数
          单个参数：_parameter就是这个参数
          多个参数：参数会被封装为一个map；_parameter就是代表这个map

（2） _databaseId:如果配置了databaseIdProvider标签。
_databaseId就是代表当前数据库的别名oracle

说明：environments标签中的default是哪个数据库，_databaseId就是哪个数据库

（3）bind标签

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

<bind name="_lastName" value="'%'+lastName+'%'"/>

说明：此时 _lastName变量就代表%lastName的值%

映射文件中

<!--public List<Employee> getEmpsTestInnerParameter(Employee employee);  -->
	  <select id="getEmpsTestInnerParameter" resultType="com.atguigu.mybatis.bean.Employee">
	  		<!-- bind：可以将OGNL表达式的值绑定到一个变量中，方便后来引用这个变量的值 -->
	  		<bind name="_lastName" value="'%'+lastName+'%'"/>
	  		<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>

接口中

package com.atguigu.mybatis.dao;

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

}

测试文件中：

package com.atguigu.mybatis.test;


public class MyBatisTest {
    @Test
	public void testInnerParam() throws IOException{
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession openSession = sqlSessionFactory.openSession();
		try{
			EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
			Employee employee2 = new Employee();
			employee2.setLastName("%e%");//配置文件中已经加了% 建议这里用&
			List<Employee> list = mapper.getEmpsTestInnerParameter(employee2);
			for (Employee employee : list) {
				System.out.println(employee);
			}
		}finally{
			openSession.close();
		}
	}
}