摘要：
--对于查询，请输入条件--˃select*fromtbl_Empl雇员email=#{email}；importjava.io。IOException；importjava.io。输入流；导入java.util。列表导入java.util。地图importorg.apache.ibatis.io。资源；importorg.apache.ibatis会话。SqlSession；importorg.apache.ibatis会话。SqlSessionFactory；importorg.apache.ibatis会话。SqlSessionFactoryBuilder；importorg.junit。测验importcom.gong.mybatis.bean。受雇者importcom.gong.mybatis.mapper。EmployeeMapperDynamicSql；publicclassTestMybatis3｛publicSqlSessionFactorygetSqlSessionFactory（）throwsIOException｛Stringresource=“mybatis config.xml”；InputStreamis=Resources.getResourceAsStream；returnnewSqlSessionFactoryBuilder（）.build；｝@Testpublicvoidest（）throwsIOException｛SqlSessionFactorysqlSessionFactory=getSqlSessionFactory（）；SqlSessionopenSession=sqlSessionFactory.openSession（）；try｛EmployeeMapperDynamicSqlmapper=openSession.getMapper；Employeemployee=newEmployee（）；employee.setId；employment.setLastName；//employee.setGender（“0”）；//employees.setEmail；mapper.updateEmployee.Listes=mapper.getEmployeByConditionIf；for｛System.err.println；｝openSession。commit（）；｝最后｛openSession.close（）；｝｝让我们修改Id=1 last_ Name=hanbin的记录，结果：将报告一个错误，因为sql_ employeesetlast_ Name=结尾处有逗号DEBUG01-1114:31:18323==˃正在准备：updatetbl？DEBUG02-1114:31:18366==˃参数：hanbin，1有两种解决方案：一种是使用set标记自动过滤冗余逗号updatetbl_employee˂iftest=“lastName！
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); public void updateEmp(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> <choose> <when test="id!=null"> id=#{id} </when> <when test="lastName!=null"> last_name like #{lastName} </when> <when test="email!=null"> email=#{email} </when> <otherwise> </otherwise> </choose> </where> </select> <update id="updateEmp"> update tbl_employee set <if test="lastName!=null"> last_name=#{lastName}, </if> <if test="gender!=null and gender == 0 or gender == 1"> gender=#{gender}, </if> <if test="email!=null"> email=#{email} </if> where id=#{id} </update> </mapper>
结合之前的知识，我们在这里不使用set标签：
进行测试：
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(1); employee.setLastName("hanbin"); // employee.setGender("0"); // employee.setEmail("dema@qq.com"); mapper.updateEmp(employee); List<Employee> es = mapper.getEmpByConditionIf(new Employee()); for(Employee e:es) { System.err.println(e); } openSession.commit(); } finally { openSession.close(); } } }
我们先对Id=1的记录修改last_name=hanbin，结果：会报错，因为sql最后面多了个逗号
DEBUG 01-21 14:31:18,323 ==> Preparing: update tbl_employee set last_name=?, where id=? (BaseJdbcLogger.java:145)
DEBUG 01-21 14:31:18,366 ==> Parameters: hanbin(String), 1(Integer) (BaseJdbcLogger.java:145) 解决方法有两种：
第一种：使用set标签，会自动过滤掉多余的逗号
<update id="updateEmp"> update tbl_employee <set> <if test="lastName!=null"> last_name=#{lastName}, </if> <if test="gender!=null and gender == 0 or gender == 1"> gender=#{gender}, </if> <if test="email!=null"> email=#{email} </if> </set> where id=#{id} </update>
结果：
DEBUG 01-21 14:46:11,726 ==> Preparing: select * from tbl_employee (BaseJdbcLogger.java:145) DEBUG 01-21 14:46:11,727 ==> Parameters: (BaseJdbcLogger.java:145) DEBUG 01-21 14:46:11,756 <== Total: 4 (BaseJdbcLogger.java:145) Employee [id=1, lastName=hanbin, gender=0, email=hanbin@qq.com, dept=null]
第二种：使用之前学过的trim标签
<update id="updateEmp"> update tbl_employee <trim prefix="set" suffixOverrides=","> <if test="lastName!=null"> last_name=#{lastName}, </if> <if test="gender!=null and gender == 0 or gender == 1"> gender=#{gender}, </if> <if test="email!=null"> email=#{email} </if> </trim> where id=#{id} </update>
并修改id=1的last_name为dema，gender为1，email为dema@qq.com
employee.setId(1);
employee.setLastName("dema");
employee.setGender("1");
employee.setEmail("dema@qq.com");结果：
DEBUG 01-21 14:53:38,851 ==> Preparing: select * from tbl_employee (BaseJdbcLogger.java:145) DEBUG 01-21 14:53:38,852 ==> Parameters: (BaseJdbcLogger.java:145) DEBUG 01-21 14:53:38,885 <== Total: 4 (BaseJdbcLogger.java:145) Employee [id=1, lastName=dema, gender=1, email=dema@qq.com, dept=null] Employee [id=2, lastName=jack, gender=1, email=675544321@qq.com, dept=null] Employee [id=3, lastName=小红, gender=0, email=xiaohong@qq.com, dept=null] Employee [id=4, lastName=小明, gender=0, email=xiaoming@qq.com, dept=null]