MyBatis动态sql_trim自定义字符串截取
继续上次文章http://blog.csdn.net/zhupengqq/article/details/78632444
EmployeeMapperDynamicSQL.java
package com.cn.mybatis.dao;
import java.util.List;
import com.cn.zhu.bean.Employee;
public interface EmployeeMapperDynamicSQL {
//<!-- 查询员工,要求,携带了哪个字段查询条件就带上这个字段的值 -->
public List<Employee> getEmpsByConditionIf(Employee employee);
//测试trim截取字符串
public List<Employee> getEmpsByConditionTrim(Employee employee);
}
EmployeeMapperDynamicSQL.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.cn.mybatis.dao.EmployeeMapperDynamicSQL">
<!-- public List<Employee> getEmpsByConditionTrim(Employee employee); -->
<select id="getEmpsByConditionTrim" resultType="com.cn.zhu.bean.Employee">
select * from tbl_employee
<!-- 后面多出的and或者or where标签不能解决
prefix="" 前缀: trim标签体中是整个字符串拼串后的结果
prefix给拼串后的整个字符加一个前缀
prefixOverrides=""
前缀覆盖: 去掉整个字符串前面多余的字符
suffixOverrides=""
后缀覆盖 去掉整个字符串前面多余的字符
suffix="" 后缀
给拼串后的整个字符加一个后缀
-->
<!-- 自定义字符串截取规则 -->
<trim prefix="where" suffixOverrides="and">
<if test="id!=null">
id=#{id} and
</if>
<if test="lastName!=null && lastName!=""">
last_name like #{lastName} and
</if>
<if test="email !=null and email.trim()!=""">
email=#{email} and
</if>
<!-- ognl 会进行字符串和数字的转换 "0"==0 -->
<if test="gender==0 or gender==1">
gender=#{gender}
</if>
</trim>
</select>
</mapper>
MyBatisTest.java
@Test
public void testDynamicSql() throws IOException{
SqlSessionFactory sqlSessionFactory=getSqlSessionFactory();
SqlSession openSession=sqlSessionFactory.openSession();
try {
EmployeeMapperDynamicSQL mapper= openSession.getMapper(EmployeeMapperDynamicSQL.class);
//测试if where
Employee employee=new Employee(null, "%h%", null, null);
List<Employee> emps= mapper.getEmpsByConditionIf(employee);
for(Employee emp: emps)
System.out.println(emp);
// 查询的时候如果某些条件没带可能sql拼装会有问题
// 1.给where 后面加上1=1,以后的条件都and
//2. mybatis 使用where 标签将所有的查询条件包括在内。
// mybatis 就会将where标签后面第一个and 去掉
// where只会去掉第一个多出来的and 或者 or
List<Employee> emps2=mapper.getEmpsByConditionTrim(employee);
for(Employee emp: emps2 ){
System.out.println(emp);
}
//测试Trim
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}