mybatis动态sql之初探（学习where、if、trim标签）
mybatis动态sql之初探（学习where、if、trim标签）
西西嘛呦
发布于 2020-08-26 14:43:43
发布于 2020-08-26 14:43:43
EmployeeMapperDynamicSql.java
package com.gong.mybatis.mapper;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.MapKey;
import com.gong.mybatis.bean.Employee;
public interface EmployeeMapperDynamicSql {
public List<Employee> getEmpByConditionIf(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.gong.mybatis.mapper.EmployeeMapperDynamicSql">
<!-- 查询，要查那个就带上那个条件 -->
<select id="getEmpByConditionIf" resultType="com.gong.mybatis.bean.Employee">
select * from tbl_employee
<where>
<if test="id!=null">
<!-- 取值是从参数里面取 -->
id=#{id}
</if>
<!-- 遇见特殊符号应使用转义字符 -->
<if test="lastName!=null && lastName!=""">
and last_name like #{lastName}
</if>
<if test="email!=null and email.trim()!=""">
and email=#{email}
</if>
<!-- ognl会进行字符串和数字进行转换 -->
<if test="gender==0 or gender==1">
and gender=#{gender}
</if>
</where>
</select>
</mapper>在TestMybatis3.java中进行测试：
package com.gong.mybatis.test;
import java.io.IOException;
import java.io.InputStream;
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.gong.mybatis.bean.Employee;
import com.gong.mybatis.mapper.EmployeeMapperDynamicSql;
public class TestMybatis3 {
public SqlSessionFactory getSqlSessionFactory() throws IOException {
String resource = "mybatis-config.xml";
InputStream is = Resources.getResourceAsStream(resource);
return new SqlSessionFactoryBuilder().build(is);
}
@Test
public void test() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperDynamicSql mapper = openSession.getMapper(EmployeeMapperDynamicSql.class);
Employee employee = new Employee();
employee.setId(3);
// employee.setLastName("%小%");
// employee.setEmail("xiaoming@qq.com");
List<Employee> es = mapper.getEmpByConditionIf(employee);
for(Employee e:es) {
System.err.println(e);
}
openSession.commit();
} finally {
openSession.close();
}
}
}首先是根据Id查询，结果为：
DEBUG 01-21 13:02:20,329 ==> Preparing: select * from tbl_employee WHERE id=? (BaseJdbcLogger.java:145) Employee [id=3, lastName=小红, gender=0, email=xiaohong@qq.com, dept=null] DEBUG 01-21 13:02:20,375 ==> Parameters: 3(Integer) (BaseJdbcLogger.java:145) DEBUG 01-21 13:02:20,452 <== Total: 1 (BaseJdbcLogger.java:145)
再根据姓名查询，结果为：
DEBUG 01-21 13:11:43,961 ==> Preparing: select * from tbl_employee where last_name like ? (BaseJdbcLogger.java:145)
DEBUG 01-21 13:11:44,023 ==> Parameters: %小%(String) (BaseJdbcLogger.java:145)
DEBUG 01-21 13:11:44,086 <== Total: 2 (BaseJdbcLogger.java:145)
Employee [id=3, lastName=小红, gender=0, email=xiaohong@qq.com, dept=null]
Employee [id=4, lastName=小明, gender=0, email=xiaoming@qq.com, dept=null]说明：上述的动态sql会根据存在的字段进行查询。where标签可以去除掉第一个and。也就是说当我们根据姓名来查询时，原本sql拼接结果应该为select * from tbl-employee where and last_name like #{lastName}，由于使用了where标签，去掉了这里的第一个and。假设我们现在这么写：
<?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.gong.mybatis.mapper.EmployeeMapperDynamicSql">
<!-- 查询，要查那个就带上那个条件 -->
<select id="getEmpByConditionIf" resultType="com.gong.mybatis.bean.Employee">
select * from tbl_employee
<where>
<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会进行字符串和数字进行转换 -->
<if test="gender==0 or gender==1">
gender=#{gender}
</if>
</where>
</select>
</mapper>即将and连接符放在if语句的最后，再进行按姓名查询就会报错：
DEBUG 01-21 13:08:17,137 ==> Preparing: select * from tbl_employee WHERE last_name like ? and (BaseJdbcLogger.java:145)
DEBUG 01-21 13:08:17,191 ==> Parameters: %小%(String) (BaseJdbcLogger.java:145) 注意到日志里的sql语句最后存在一个and，这是不合法的，我们可以使用trim标签来解决：
<?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.gong.mybatis.mapper.EmployeeMapperDynamicSql">
<!-- 查询，要查那个就带上那个条件 -->
<select id="getEmpByConditionIf" resultType="com.gong.mybatis.bean.Employee">
select * from tbl_employee
<trim prefix="where" prefixOverrides="" suffix="" 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会进行字符串和数字进行转换 -->
<if test="gender==0 or gender==1">
gender=#{gender}
</if>
</trim>
</select>
</mapper>trim标签中有四个属性：
prefix：前缀，为sql语句从该处开始加上指定字符串
prefixOverrides：去除掉sql语句从该处开始指定的字符串
suffix：后缀，为sql语句最后加上指定字符串
suffixOverrides：去除掉sql语句最后的指定的字符串
之后再进行测试：
DEBUG 01-21 13:11:43,961 ==> Preparing: select * from tbl_employee where last_name like ? (BaseJdbcLogger.java:145)
DEBUG 01-21 13:11:44,023 ==> Parameters: %小%(String) (BaseJdbcLogger.java:145)
DEBUG 01-21 13:11:44,086 <== Total: 2 (BaseJdbcLogger.java:145)
Employee [id=3, lastName=小红, gender=0, email=xiaohong@qq.com, dept=null]
Employee [id=4, lastName=小明, gender=0, email=xiaoming@qq.com, dept=null]发现sql语句正常，能够正确运行。
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2020-01-21 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
日志服务
日志服务（Cloud Log Service，CLS）是腾讯云提供的一站式日志服务平台，提供了从日志采集、日志存储到日志检索，图表分析、监控告警、日志投递等多项服务，协助用户通过日志来解决业务运维、服务监控、日志审计等场景问题。