MyBatis-动态SQL

最新推荐文章于 2024-08-04 12:22:11 发布

lelege01

最新推荐文章于 2024-08-04 12:22:11 发布

阅读量167

点赞数

分类专栏： MyBatis MySQL 文章标签： mybatis mysql java

本文链接：https://blog.csdn.net/qq_43775034/article/details/106199987

版权

MySQL 同时被 2 个专栏收录

10 篇文章 0 订阅

订阅专栏

MyBatis

4 篇文章 0 订阅

订阅专栏

一、简介&环境搭建

1.1简介

MyBatis最强大的特性之一就是它的动态SQL功能。 如果您有使用JDBC或任何类似框架的经验，您就会理解有条件地将SQL字符串连接在一起是多么痛苦，请确保不要忘记空格或忽略列列表末尾的逗号。处理动态SQL可能非常痛苦。虽然使用动态SQL永远不会成为问题，但是MyBatis使用了一种强大的动态SQL语言，可以在任何映射的SQL语句中使用。
在MyBatis的早期版本中，有很多元素需要了解和理解。MyBatis 3大大改进了这一点，现在可以使用的元素还不到一半。MyBatis使用强大的基于OGNL的表达式来消除大部分其他元素:
• if
• choose (when, otherwise)
• trim (where, set)
• foreach

2.环境搭建

编写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.atorg.mybatis.dao.EmployeeMapperDynamicSQl">
	<!--namespace的值为接口的全类名-->
	<!-- •	if
         •	choose (when, otherwise):分支选择;带了break的switch-case
         		如果带了id就用id查，带了lastname就用lastName查
         •	trim (where(封装查询条件), set(封装修改条件))
         •	foreach
	 -->
  </mapper>

二、if判断

条件：查询员工，携带了哪个字段查询条件就带上这个字段的值
接口编写

package com.atorg.mybatis.dao;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.atorg.mybatis.bean.Employee;

public interface EmployeeMapperDynamicSQl {
	//携带哪个字段查询条件就带上这个字段
	public List<Employee> getEmpsTestInnerParameter(Employee employee);
	}

接口实现编写

<?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.atorg.mybatis.dao.EmployeeMapperDynamicSQl">
	  <!-- public List<Employee> getEmpsByConditionIf(Employee employee); -->
	  <select id="getEmpsByConditionIf" resultType="emp">
	  	select * from tab1_employee
	  	where
	  	<!--test:判断表达式-->
	  	<!--test:判断表达式（OGNL）
		  	从参数中取值进行判断
		  	遇见特殊符号应该去写准转义字符
		  	  -->
		  	<if test="id!=null"><!--判断id不为空是拼接到sql语句中-->
		  		id=#{id}
		  	</if>
		  	<if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;"><!--如上和id判断一致-->
		  		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>
	  </select>

提示：遇见特殊符号应该去写转义字符
测试程序

package com.atorg.mybatis.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import com.atorg.mybatis.bean.Department;
import com.atorg.mybatis.bean.Employee;
import com.atorg.mybatis.dao.DepartmentMapper;
import com.atorg.mybatis.dao.EmployeeMapper;
import com.atorg.mybatis.dao.EmployeeMapperAnnotation;
import com.atorg.mybatis.dao.EmployeeMapperDynamicSQl;
import com.atorg.mybatis.dao.EmployeeMapperPlus;

public class MyBatisTest {
	public SqlSessionFactory getSqlSessionFactory() throws IOException {
		String resource = "mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		return new SqlSessionFactoryBuilder().build(inputStream);
	}
	@Test
	public void testDynamicSQl() throws IOException {
		// 获取sqlSessionFactory对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		// 获取sqlSession对象不会自动提交数据
		SqlSession openSession = sqlSessionFactory.openSession();
		// 获取接口的实现类对象
		try {
			EmployeeMapperDynamicSQl mapper = openSession.getMapper(EmployeeMapperDynamicSQl.class);
			// 测试if/where
			Employee employee = new Employee(1,"Admin",null,null);
		    List<Employee> employees=mapper.getEmpsByConditionIf(employee);
			for (Employee emp : employees) {
			System.out.println(emp);
			}
		} finally {
			openSession.close();
		}
	}

测试结果
测试类中给出的参数为 id和 name
执行结果
结论： 与测试程序给出的参数一致
讨论
当实现接口的程序动态sql中的第一个参数不合法时，sql语句为
select * from tab1_employee where and last_name=#{LastName} 这时就出现了问题，where条件后直接出现一个and，此时执行出现语法错误

解决办法
查询的时候如果某些条件没带可能sql拼装会有问题
1.给where后面加上1=1，以后的条件都and xxx

<?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.atorg.mybatis.dao.EmployeeMapperDynamicSQl">
	  <!-- public List<Employee> getEmpsByConditionIf(Employee employee); -->
	  <select id="getEmpsByConditionIf" resultType="emp">
	  	select * from tab1_employee
	  	where 1=1
	  	<!--test:判断表达式-->
	  	<!--test:判断表达式（OGNL）
		  	从参数中取值进行判断
		  	遇见特殊符号应该去写准转义字符
		  	  -->
		  	<if test="id!=null"><!--判断id不为空是拼接到sql语句中-->
		  		and id=#{id}
		  	</if>
		  	<if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;"><!--如上和id判断一致-->
		  		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>
	  </select>

此时执行
执行成功
2.mybatis推荐使用where标签来将所有的查询条件包括在内，mybatis就会where标签中拼装sql，多出来的and或者or去掉

<?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.atorg.mybatis.dao.EmployeeMapperDynamicSQl">
	  <!-- public List<Employee> getEmpsByConditionIf(Employee employee); -->
	  <select id="getEmpsByConditionIf" resultType="emp">
	  	select * from tab1_employee
	  	<where>
	  	<!--test:判断表达式-->
	  	<!--test:判断表达式（OGNL）
		  	从参数中取值进行判断
		  	遇见特殊符号应该去写准转义字符
		  	  -->
		  	<if test="id!=null"><!--判断id不为空是拼接到sql语句中-->
		  		id=#{id}
		  	</if>
		  	<if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;"><!--如上和id判断一致-->
		  		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>

此时执行
执行成功
讨论
当我们的sql语句中的and出现在条件后边的时候，当倒数第一个参数为null时select * from tab1_employee WHERE last_name like ? and 此时最后多出一个and，出现语法错误
测试
sql语句出现语法错误
结论：where标签只会去掉第一个多出的and或者or 此时就引出了另一个标签trim

三、trim标签

编写接口

package com.atorg.mybatis.dao;

import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.atorg.mybatis.bean.Employee;

public interface EmployeeMapperDynamicSQl {
	public List<Employee> getEmpsByConditionTrim(Employee employee);
	}

编写接口实现

<?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.atorg.mybatis.dao.EmployeeMapperDynamicSQl">
	  <!-- public List<Employee> getEmpsByConditionTrim(Employee employee); -->
	  <select id="getEmpsByConditionTrim" resultType="emp">
	  	select * from tab1_employee
	  	<!-- 后面多出的and或者or  where标签不能解决
	  	 prefix="":前缀，trim标签体是整个字符串拼串后的结果
	  	 			prefix给拼串后的整个字符串加一个前缀
	  	 prefixOverrides="" :前缀覆盖，去掉整个字符串前面多余的字符
	  	 suffix="" 后缀
	  	 			suffix给拼串后的整个字符串加一个后缀
	  	 suffixOverrides=""
	  	 			后缀覆盖：去掉整个字符串后面多余的字符-->
	  	 <!-- trim自定义字符串截取规则:只要满足条件都拼串 -->
	  	<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>
</mapper>

测试程序

package com.atorg.mybatis.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import com.atorg.mybatis.bean.Department;
import com.atorg.mybatis.bean.Employee;
import com.atorg.mybatis.dao.DepartmentMapper;
import com.atorg.mybatis.dao.EmployeeMapper;
import com.atorg.mybatis.dao.EmployeeMapperAnnotation;
import com.atorg.mybatis.dao.EmployeeMapperDynamicSQl;
import com.atorg.mybatis.dao.EmployeeMapperPlus;

public class MyBatisTest {
	public SqlSessionFactory getSqlSessionFactory() throws IOException {
		String resource = "mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		return new SqlSessionFactoryBuilder().build(inputStream);
	}
	@Test
	public void testDynamicSQl() throws IOException {
		// 获取sqlSessionFactory对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		// 获取sqlSession对象不会自动提交数据
		SqlSession openSession = sqlSessionFactory.openSession();
		// 获取接口的实现类对象
		try {
			EmployeeMapperDynamicSQl mapper = openSession.getMapper(EmployeeMapperDynamicSQl.class);
			// 测试trim
			 Employee employee = new Employee(5,"%e%",null,null);
			 List<Employee> employees=mapper.getEmpsByConditionTrim(employee);
			 for (Employee employee2 : employees) {
			 System.out.println(employee2); }
		} finally {
			openSession.close();
		}
	}

测试结果
原先where标签如果最后一个参数为空时，会多出and或者or出现sql语句语法错误，此时使用trim标签，会将多余的and删除，以下测试结果证实了这一说法
测试成功
prefix="":前缀，trim标签体是整个字符串拼串后的结果
prefix给拼串后的整个字符串加一个前缀
prefixOverrides="" :前缀覆盖，去掉整个字符串前面多余的字符
suffix="" 后缀 suffix给拼串后的整个字符串加一个后缀
suffixOverrides="" 后缀覆盖：去掉整个字符串后面多余的字符

四、choose标签(只选择满足条件的其中一个)

编写接口

package com.atorg.mybatis.dao;

import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.atorg.mybatis.bean.Employee;
public interface EmployeeMapperDynamicSQl {
	public List<Employee> getEmpByConditionChoose(Employee employee);
	}

编写接口实现

<?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.atorg.mybatis.dao.EmployeeMapperDynamicSQl">
	  <!--choose：只选择满足条件的一个  -->
	  <!-- public List<Employee> getEmpByConditionChoose(Employee employee); -->
	  <select id="getEmpByConditionChoose" resultType="emp">
	  	select * from tab1_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>
</mapper>

测试程序

package com.atorg.mybatis.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import com.atorg.mybatis.bean.Department;
import com.atorg.mybatis.bean.Employee;
import com.atorg.mybatis.dao.DepartmentMapper;
import com.atorg.mybatis.dao.EmployeeMapper;
import com.atorg.mybatis.dao.EmployeeMapperAnnotation;
import com.atorg.mybatis.dao.EmployeeMapperDynamicSQl;
import com.atorg.mybatis.dao.EmployeeMapperPlus;
public class MyBatisTest {

	public SqlSessionFactory getSqlSessionFactory() throws IOException {
		String resource = "mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		return new SqlSessionFactoryBuilder().build(inputStream);
	}
	@Test
	public void testDynamicSQl() throws IOException {
		// 获取sqlSessionFactory对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		// 获取sqlSession对象不会自动提交数据
		SqlSession openSession = sqlSessionFactory.openSession();
		// 获取接口的实现类对象
		try {
			EmployeeMapperDynamicSQl mapper = openSession.getMapper(EmployeeMapperDynamicSQl.class);
			Employee employee = new Employee(null,"%e%",null,null);
			// 测试choose
			  List<Employee> emps= mapper.getEmpByConditionChoose(employee);
			  for (Employee employee2 : emps) { System.out.println(employee2);
			  }
		} finally {
			openSession.close();
		}
	}

测试结果
此时只有name属性不为空，所以此时choose会选择姓名作为查询参数，以下测试结果证实这一说法 如果一次带了多个参数（多个参数都不为null）则默认会选择第一个作为查询参数，如果所有参数都为null则默认执行其他条件即otherwise标签中的条件作为查询条件测试成功

五、set标签和if标签结合做更新操作(封装修改条件)

接口编写

package com.atorg.mybatis.dao;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.atorg.mybatis.bean.Employee;
public interface EmployeeMapperDynamicSQl {
	public void updateEmp(Employee employee);
	}

接口实现编写

<?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.atorg.mybatis.dao.EmployeeMapperDynamicSQl">
	  <update id="updateEmp">
	  	update tab1_employee 
	  	<!-- set标签去除，号 -->
		<set>
			<if test="lastName!=null">
				last_name=#{lastName},
			</if>
			<if test="email!=null">
				email=#{email},
			</if>
			<if test="gender!=null">
				gender=#{gender}
			</if>
		</set>
		<!--trim做更新操作
		 <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>

测试程序

package com.atorg.mybatis.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import com.atorg.mybatis.bean.Department;
import com.atorg.mybatis.bean.Employee;
import com.atorg.mybatis.dao.DepartmentMapper;
import com.atorg.mybatis.dao.EmployeeMapper;
import com.atorg.mybatis.dao.EmployeeMapperAnnotation;
import com.atorg.mybatis.dao.EmployeeMapperDynamicSQl;
import com.atorg.mybatis.dao.EmployeeMapperPlus;
public class MyBatisTest {
	public SqlSessionFactory getSqlSessionFactory() throws IOException {
		String resource = "mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		return new SqlSessionFactoryBuilder().build(inputStream);
	}
@Test
	public void testDynamicSQl() throws IOException {
		// 获取sqlSessionFactory对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		// 获取sqlSession对象不会自动提交数据
		SqlSession openSession = sqlSessionFactory.openSession();
		// 获取接口的实现类对象
		try {
			EmployeeMapperDynamicSQl mapper = openSession.getMapper(EmployeeMapperDynamicSQl.class);
			 Employee employee = new Employee(1,"Admin",null,null);
			// 测试更新
			/*
			 * mapper.updateEmp(employee); openSession.commit();
			 */
			mapper.updateEmp(employee);
			openSession.commit();
		} finally {
			openSession.close();
		}
	}
}