MyBatis动态Sql

动态 SQL是MyBatis强大特性之一。可以实现sql语句的拼装。

sql映射文件:

<select id="getEmpsByCondition" resultType="com.test.beans.Employee">          SELECT * FROM tb1_emplyee where          <if test="id!=null">            id=#{id}          </if>          <if test="lastName!=null">            AND  last_name  LIKE #{lastName}          </if>          <if test="email!=null">            AND email=#{email}          </if>        /*ognl会进行字符串以及数字的转换判断*/          <if test="gender==0 or gender==1">            AND  gender=#{gender}          </if></select>

对应的接口文件:

public interface EmployeeMapperDymanicSQL {    //些带了那个字段查询条件就带上这个字段的值    public List<Employee> getEmpsByCondition(Employee employee);}

测试方法:

@Testpublic void testDynamicSqlTest() throws IOException{SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();//1、获取到的SqlSession不会自动提交数据SqlSession openSession = sqlSessionFactory.openSession();try{            EmployeeMapperDymanicSQL mapper=openSession.getMapper(EmployeeMapperDymanicSQL.class);    Employee employee=new Employee(1,"%e%",null,"3");    List<Employee> emps=mapper.getEmpsByCondition(employee);    for (Employee e:emps){System.out.println(e);     }}    finally {openSession.close();}}

全局配置文件:

<mappers><mapper resource="EmployeeMapperDymanicSQL.xml"/></mappers>

sql语句会根据if标签中的内容动态进行拼装,但是如果第一个id值设置为null必然会导致错误,下面有两种解决方案:

1.where 1=1 后面全部跟and

ex:

<select id="getEmpsByCondition" resultType="com.test.beans.Employee">          SELECT * FROM tb1_emplyee where 1=1          <if test="id!=null">            AND id=#{id}          </if>          <if test="lastName!=null">            AND  last_name  LIKE #{lastName}          </if>          <if test="email!=null">            AND email=#{email}          </if>        /*ognl会进行字符串以及数字的转换判断*/          <if test="gender==0 or gender==1">            AND  gender=#{gender}          </if></select>

或者使用where标签:

<mapper namespace="com.test.dao.EmployeeMapperDymanicSQL">    <!--public List<Employee> getEmpsByCondition(Employee employee);-->    <select id="getEmpsByCondition" resultType="com.test.beans.Employee">          SELECT * FROM tb1_emplyee        <where>          <if test="id!=null">            id=#{id}          </if>          <if test="lastName!=null">            AND  last_name  LIKE #{lastName}          </if>          <if test="email!=null">            AND email=#{email}          </if>        /*ognl会进行字符串以及数字的转换判断*/          <if test="gender==0 or gender==1">            AND  gender=#{gender}          </if>        </where>    </select></mapper>