MyBatis的使用八(动态SQL)
本主要讲述mybatis处理动态sql语句
一. 问题引入
前端展示的数据表格中,查询条件可能不止一个,如何将用户输入的多个查询条件,拼接到sql语句中呢?
DynamicMapper接口声明如下
public interface DynamicMapper { // 动态查询员工信息 List<Employee> selectEmpDynamic(@Param("emp") Employee emp); }
如何在DynamicMapper.xml文件中,编写sql语句,使其动态查询呢?
二. 动态SQL查询
1. <if>标签
DynamicMapper.xml文件声明如下
<!--// 动态查询员工信息 List<Employee> selectEmpDynamic(Employee emp);--> <select id="selectEmpDynamic" resultType="Employee"> select * from t_emp where 1=1 <if test="emp.empName != null and emp.empName != '' "> and emp_name = #{emp.empName} </if> <if test="emp.age != null and emp.age != '' "> and age = #{emp.age} </if> <if test="emp.gender != null and emp.gender != '' "> and gender = #{emp.gender} </if> </select>
测试test
@Test // 测试动态查询 public void test01(){ SqlSession sqlSession = SQLSessionUtils.getSqlSession(); DynamicMapper mapper = sqlSession.getMapper(DynamicMapper.class); Employee emp = new Employee(null, "", 23, ""); List<Employee> list = mapper.selectEmpDynamic(emp); System.out.println(list); }
运行结果如下
DEBUG 02-05 13:49:28,390 ==> Preparing: select * from t_emp where 1=1 and age = ? (BaseJdbcLogger.java:137) DEBUG 02-05 13:49:28,432 ==> Parameters: 23(Integer) (BaseJdbcLogger.java:137) DEBUG 02-05 13:49:28,461 <== Total: 1 (BaseJdbcLogger.java:137) [Employee{empId=1, empName='张三', age=23, gender='男', dept=null}]
注意:单独使用 < if > 标签时,需要在where后面添加恒成立的条件,例如 1=1,之后在< if >标签中的test写判断条件
2. <where> + <if>标签
DynamicMapper.xml文件声明如下
<select id="selectEmpDynamicTwo" resultType="Employee"> select * from t_emp <where> <if test="emp.empName != null and emp.empName != '' "> and emp_name = #{emp.empName} </if> <if test="emp.age != null and emp.age != '' "> and age = #{emp.age} and </if> <if test="emp.gender != null and emp.gender != '' "> and gender = #{emp.gender} </if> </where> </select>
测试test1
@Test // 测试动态查询 public void test01(){ SqlSession sqlSession = SQLSessionUtils.getSqlSession(); DynamicMapper mapper = sqlSession.getMapper(DynamicMapper.class); Employee emp = new Employee(null, "张三", 23, ""); List<Employee> list = mapper.selectEmpDynamic(emp); System.out.println(list); }
test1 运行结果如下
DEBUG 02-05 14:00:47,159 ==> Preparing: select * from t_emp WHERE emp_name = ? and age = ? (BaseJdbcLogger.java:137) DEBUG 02-05 14:00:47,190 ==> Parameters: 张三(String), 23(Integer) (BaseJdbcLogger.java:137) DEBUG 02-05 14:00:47,211 <== Total: 1 (BaseJdbcLogger.java:137) [Employee{empId=1, empName='张三', age=23, gender='男', dept=null}]
注意:<where>标签只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“WHERE”子句。而且,若语句的开头为“AND”或“OR”,<where>标签也会将它们去除。
但是当DynamicMapper.xml声明如下
<select id="selectEmpDynamic" resultType="Employee"> select * from t_emp <where> <if test="emp.empName != null and emp.empName != '' "> emp_name = #{emp.empName} and </if> <if test="emp.age != null and emp.age != '' "> age = #{emp.age} and </if> <if test="emp.gender != null and emp.gender != '' "> gender = #{emp.gender} </if> </where> </select>
测试test2
@Test // 测试动态查询 public void test01(){ SqlSession sqlSession = SQLSessionUtils.getSqlSession(); DynamicMapper mapper = sqlSession.getMapper(DynamicMapper.class); Employee emp = new Employee(null, "张三", 23, ""); List<Employee> list = mapper.selectEmpDynamic(emp); System.out.println(list); }
test2 运行结果如下
DEBUG 02-05 14:08:29,175 ==> Preparing: select * from t_emp WHERE emp_name = ? and age = ? and (BaseJdbcLogger.java:137)
DEBUG 02-05 14:08:29,206 ==> Parameters: 张三(String), 23(Integer) (BaseJdbcLogger.java:137)
sql语句出现错误,即<where>标签无法去除SQL子句后面的and或者or。
3. <trim> + <if> 标签
DynamicMapper.xml文件声明如下
<select id="selectEmpDynamic" resultType="Employee"> select * from t_emp <!--prefix="where" 前缀添加where suffix="where" 后缀添加where prefixOverrides="and" 前缀删除and suffixOverrides="and" 后缀删除and--> <trim prefix="where" suffixOverrides="and"> <if test="emp.empName != null and emp.empName != '' "> emp_name = #{emp.empName} and </if> <if test="emp.age != null and emp.age != '' "> age = #{emp.age} and </if> <if test="emp.gender != null and emp.gender != '' "> gender = #{emp.gender} </if> </trim> </select>
注意:<trim>标签的功能比<where>标签的功能更加丰富
test2运行结果如下
DEBUG 02-05 14:14:51,893 ==> Preparing: select * from t_emp where emp_name = ? and age = ? (BaseJdbcLogger.java:137) DEBUG 02-05 14:14:51,924 ==> Parameters: 张三(String), 23(Integer) (BaseJdbcLogger.java:137) DEBUG 02-05 14:14:51,943 <== Total: 1 (BaseJdbcLogger.java:137) [Employee{empId=1, empName='张三', age=23, gender='男', dept=null}]
完美解决and在SQL子句后面的问题。
三. 批量增加【删除】
1. 批量增加
DynamicMapper接口声明如下
public interface DynamicMapper { // 批量添加emp对象 int insertEmpList(@Param("emps") List<Employee> emps); }
DynamicMapper.xml文件声明如下
<!--// 批量添加emp对象 int insertEmpList(@Param("emps") List<Employee> emps);--> <insert id="insertEmpList" > insert into t_emp values <foreach collection="emps" item="emp" separator=","> (null,#{emp.empName},#{emp.age},#{emp.gender},null) </foreach> </insert>
2. 批量删除
DynamicMapper接口声明如下
public interface DynamicMapper { // 批量删除emp对象 int deleteEmpList(@Param("empIds") Integer[] empIds); }
DynamicMapper.xml文件声明如下
<!--// 批量删除emp对象--> <!--int deleteEmpList(@Param("empIds") Integer[] empIds);--> <!--方式1: --> <delete id="deleteEmpListOne" > delete from t_emp where emp_id in ( <foreach collection="empIds" item="empId" separator=","> #{empId} </foreach> ) </delete> <!--方式2: --> <delete id="deleteEmpListTwo" > delete from t_emp where emp_id in <foreach collection="empIds" item="empId" separator="," open="(" close=")"> #{empId} </foreach> </delete> <!--方式3: --> <delete id="deleteEmpList" > delete from t_emp where <foreach collection="empIds" item="empId" separator="or"> emp_id = #{empId} </foreach> </delete>
注意:< foreach >标签的使用,
collecion:传入的集合【数组】名;
item:集合【数组】中元素的类型;
separator:以指定字符串为分隔符;
open:以指定字符串开始,close:以指定字符串结尾。