mybatis动态sql之初探（学习where、if、trim标签）

概述EmployeeMapperDynamicSql.java package com.gong.mybatis.mapper; import java.util.List; import java.ut

EmployeeMapperDynamicsql.java

package com.gong.mybatis.mapper;import java.util.List; java.util.Map; org.apache.ibatis.annotations.MapKey; 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 遇见特殊符号应使用转义字符 -->            ="lastname!=null &amp;&amp; lastname!=&quot;&quot;"                and last_name like #{lastname}            ="email!=null and email.trim()!=&quot;&quot;"                and email=#{email}             ognl会进行字符串和数字进行转换 ="gender==0 or gender==1"                and gender=#{gender}            >        select>mapper>

在TestMybatis3.java中进行测试：

 com.gong.mybatis.test; java.io.IOException; java.io.inputStream; org.apache.ibatis.io.Resources; org.apache.ibatis.session.sqlSession; org.apache.ibatis.session.sqlSessionFactory; org.apache.ibatis.session.sqlSessionFactoryBuilder; org.junit.Test; com.gong.mybatis.bean.Employee; com.gong.mybatis.mapper.EmployeeMapperDynamicsql;class TestMybatis3 {        public sqlSessionFactory getsqlSessionFactory() throws IOException {        String resource = "mybatis-config.xml";        inputStream is = Resources.getResourceAsstream(resource);        return new sqlSessionFactoryBuilder().build(is);    }    @Test    voID test()  IOException {        sqlSessionFactory sqlSessionFactory = getsqlSessionFactory();        sqlSession openSession = sqlSessionFactory.openSession();        try {            EmployeeMapperDynamicsql mapper = openSession.getMapper(EmployeeMapperDynamicsql.);            Employee employee =  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=小红,1)">gender=0,1)">email=xiaohong@qq.com,1)">dept=null]Employee [ID=4,1)">=小明,1)">=xiaoming@qq.com,1)">=null]

说明：上述的动态sql会根据存在的字段进行查询。where标签可以去除掉第一个and。也就是说当我们根据姓名来查询时，原本sql拼接结果应该为select * from tbl-employee where and last_name like #{lastname}，由于使用了where标签，去掉了这里的第一个and。假设我们现在这么写：

>                                ID=#{ID} and                            last_name like #{lastname} and                            email=#{email} and                            gender=#{gender}            >

即将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标签来解决：

trim prefix="where" prefixOverrIDes="" suffix suffixOverrIDes="and"trim>

trim标签中有四个属性：

prefix：前缀，为SQL语句从该处开始加上指定字符串

prefixOverrIDes：去除掉SQL语句从该处开始指定的字符串

suffix：后缀，为SQL语句最后加上指定字符串

suffixOverrIDes：去除掉SQL语句最后的指定的字符串

之后再进行测试：

=null]

发现SQL语句正常，能够正确运行。

总结

以上是内存溢出为你收集整理的mybatis动态sql之初探（学习where、if、trim标签）全部内容，希望文章能够帮你解决mybatis动态sql之初探（学习where、if、trim标签）所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。