本主要讲述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：以指定字符串结尾。
标签：02,05,age,DEBUG,emp,SQL,MyBatis,DynamicMapper,动态 From： https://www.cnblogs.com/zwgitOne123/p/17093361.html