mybatis动态sql之初探（学习where、if、trim标签）
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语句正常，能够正确运行。
