/MyBatis_04_DynamicSQL/src/com/atguigu/mybatis/dao/EmployeeMapperDynamicSQL.java
package com.atguigu.mybatis.dao; import java.util.List; import org.apache.ibatis.annotations.Param; import com.atguigu.mybatis.bean.Employee; public interface EmployeeMapperDynamicSQL { public List<Employee> getEmpsTestInnerParameter(Employee employee); //携带了哪个字段查询条件就带上这个字段的值 public List<Employee> getEmpsByConditionIf(Employee employee); public List<Employee> getEmpsByConditionTrim(Employee employee); public List<Employee> getEmpsByConditionChoose(Employee employee); public void updateEmp(Employee employee); //查询员工id'在给定集合中的 public List<Employee> getEmpsByConditionForeach(@Param("ids")List<Integer> ids); public void addEmps(@Param("emps")List<Employee> emps); }
/MyBatis_04_DynamicSQL/conf/com/atguigu/mybatis/dao/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.atguigu.mybatis.dao.EmployeeMapperDynamicSQL"> <!-- • if:判断 • choose (when, otherwise):分支选择；带了break的swtich-case 如果带了id就用id查，如果带了lastName就用lastName查;只会进入其中一个 • trim 字符串截取(where(封装查询条件), set(封装修改条件)) • foreach 遍历集合 --> <!-- 查询员工，要求，携带了哪个字段查询条件就带上这个字段的值 --> <!-- public List<Employee> getEmpsByConditionIf(Employee employee); --> <select id="getEmpsByConditionIf" resultType="com.atguigu.mybatis.bean.Employee"> select * from tbl_employee <!-- where --> <where> <!-- test：判断表达式（OGNL） OGNL参照PPT或者官方文档。 c:if test 从参数中取值进行判断 遇见特殊符号应该去写转义字符： &&： --> <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会进行字符串与数字的转换判断 "0"==0 --> <if test="gender==0 or gender==1"> and gender=#{gender} </if> </where> </select> <!--public List<Employee> getEmpsByConditionTrim(Employee employee); --> <select id="getEmpsByConditionTrim" resultType="com.atguigu.mybatis.bean.Employee"> select * from tbl_employee <!-- 后面多出的and或者or where标签不能解决 prefix="":前缀：trim标签体中是整个字符串拼串 后的结果。 prefix给拼串后的整个字符串加一个前缀 prefixOverrides="": 前缀覆盖： 去掉整个字符串前面多余的字符 suffix="":后缀 suffix给拼串后的整个字符串加一个后缀 suffixOverrides="" 后缀覆盖：去掉整个字符串后面多余的字符 --> <!-- 自定义字符串的截取规则 --> <trim prefix="where" 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会进行字符串与数字的转换判断 "0"==0 --> <if test="gender==0 or gender==1"> gender=#{gender} </if> </trim> </select> <!-- public List<Employee> getEmpsByConditionChoose(Employee employee); --> <select id="getEmpsByConditionChoose" resultType="com.atguigu.mybatis.bean.Employee"> select * from tbl_employee <where> <!-- 如果带了id就用id查，如果带了lastName就用lastName查;只会进入其中一个 --> <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> gender = 0 </otherwise> </choose> </where> </select> <!--public void updateEmp(Employee employee); --> <update id="updateEmp"> <!-- Set标签的使用 --> update tbl_employee <set> <if test="lastName!=null"> last_name=#{lastName}, </if> <if test="email!=null"> email=#{email}, </if> <if test="gender!=null"> gender=#{gender} </if> </set> where id=#{id} <!-- Trim：更新拼串 update tbl_employee <trim prefix="set" suffixOverrides=","> <if test="lastName!=null"> last_name=#{lastName}, </if> <if test="email!=null"> email=#{email}, </if> <if test="gender!=null"> gender=#{gender} </if> </trim> where id=#{id} --> </update> <!--public List<Employee> getEmpsByConditionForeach(List<Integer> ids); --> <select id="getEmpsByConditionForeach" resultType="com.atguigu.mybatis.bean.Employee"> select * from tbl_employee <!-- collection：指定要遍历的集合： list类型的参数会特殊处理封装在map中，map的key就叫list item：将当前遍历出的元素赋值给指定的变量 separator:每个元素之间的分隔符 open：遍历出所有结果拼接一个开始的字符 close:遍历出所有结果拼接一个结束的字符 index:索引。遍历list的时候是index就是索引，item就是当前值 遍历map的时候index表示的就是map的key，item就是map的值 #{变量名}就能取出变量的值也就是当前遍历出的元素 --> <foreach collection="ids" item="item_id" separator="," open="where id in(" close=")"> #{item_id} </foreach> </select> <!-- 批量保存 --> <!--public void addEmps(@Param("emps")List<Employee> emps); --> <!--MySQL下批量保存：可以foreach遍历 mysql支持values(),(),()语法--> <insert id="addEmps"> insert into tbl_employee( <include refid="insertColumn"></include> ) values <foreach collection="emps" item="emp" separator=","> (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id}) </foreach> </insert><!-- --> <!-- 这种方式需要数据库连接属性allowMultiQueries=true； 这种分号分隔多个sql可以用于其他的批量操作（删除，修改） --> <!-- <insert id="addEmps"> <foreach collection="emps" item="emp" separator=";"> insert into tbl_employee(last_name,email,gender,d_id) values(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id}) </foreach> </insert> --> <!-- Oracle数据库批量保存： Oracle不支持values(),(),() Oracle支持的批量方式 1、多个insert放在begin - end里面 begin insert into employees(employee_id,last_name,email) values(employees_seq.nextval,'test_001','test_001@atguigu.com'); insert into employees(employee_id,last_name,email) values(employees_seq.nextval,'test_002','test_002@atguigu.com'); end; 2、利用中间表： insert into employees(employee_id,last_name,email) select employees_seq.nextval,lastName,email from( select 'test_a_01' lastName,'test_a_e01' email from dual union select 'test_a_02' lastName,'test_a_e02' email from dual union select 'test_a_03' lastName,'test_a_e03' email from dual ) --> <insert id="addEmps" databaseId="oracle"> <!-- oracle第一种批量方式 --> <!-- <foreach collection="emps" item="emp" open="begin" close="end;"> insert into employees(employee_id,last_name,email) values(employees_seq.nextval,#{emp.lastName},#{emp.email}); </foreach> --> <!-- oracle第二种批量方式 --> insert into employees( <!-- 引用外部定义的sql --> <include refid="insertColumn"> <property name="testColomn" value="abc"/> </include> ) <foreach collection="emps" item="emp" separator="union" open="select employees_seq.nextval,lastName,email from(" close=")"> select #{emp.lastName} lastName,#{emp.email} email from dual </foreach> </insert> <!-- 两个内置参数： 不只是方法传递过来的参数可以被用来判断，取值。。。 mybatis默认还有两个内置参数： _parameter:代表整个参数 单个参数：_parameter就是这个参数 多个参数：参数会被封装为一个map；_parameter就是代表这个map _databaseId:如果配置了databaseIdProvider标签。 _databaseId就是代表当前数据库的别名oracle --> <!--public List<Employee> getEmpsTestInnerParameter(Employee employee); --> <select id="getEmpsTestInnerParameter" resultType="com.atguigu.mybatis.bean.Employee"> <!-- bind：可以将OGNL表达式的值绑定到一个变量中，方便后来引用这个变量的值 --> <bind name="_lastName" value="'%'+lastName+'%'"/> <if test="_databaseId=='mysql'"> select * from tbl_employee <if test="_parameter!=null"> where last_name like #{lastName} </if> </if> <if test="_databaseId=='oracle'"> select * from employees <if test="_parameter!=null"> where last_name like #{_parameter.lastName} </if> </if> </select> <!-- 抽取可重用的sql片段。方便后面引用 1、sql抽取：经常将要查询的列名，或者插入用的列名抽取出来方便引用 2、include来引用已经抽取的sql： 3、include还可以自定义一些property，sql标签内部就能使用自定义的属性 include-property：取值的正确方式${prop}, #{不能使用这种方式} --> <sql id="insertColumn"> <if test="_databaseId=='oracle'"> employee_id,last_name,email </if> <if test="_databaseId=='mysql'"> last_name,email,gender,d_id </if> </sql> </mapper>
遇见特殊符号应用试验特殊符号
<if test="lastName!=null && lastName!="""> and last_name like #{lastName} </if>
上面的转化为sql语句就是latsname!=null && lastname !=" ";
我们来看看测试代码
package com.atguigu.mybatis.test; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.Arrays; import java.util.HashMap; 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.atguigu.mybatis.bean.Department; import com.atguigu.mybatis.bean.Employee; import com.atguigu.mybatis.dao.DepartmentMapper; import com.atguigu.mybatis.dao.EmployeeMapper; import com.atguigu.mybatis.dao.EmployeeMapperAnnotation; import com.atguigu.mybatis.dao.EmployeeMapperDynamicSQL; import com.atguigu.mybatis.dao.EmployeeMapperPlus; /** * 1、接口式编程 * 原生： Dao ====> DaoImpl * mybatis： Mapper ====> xxMapper.xml * * 2、SqlSession代表和数据库的一次会话；用完必须关闭； * 3、SqlSession和connection一样她都是非线程安全。每次使用都应该去获取新的对象。 * 4、mapper接口没有实现类，但是mybatis会为这个接口生成一个代理对象。 * （将接口和xml进行绑定） * EmployeeMapper empMapper = sqlSession.getMapper(EmployeeMapper.class); * 5、两个重要的配置文件： * mybatis的全局配置文件：包含数据库连接池信息，事务管理器信息等...系统运行环境信息 * sql映射文件：保存了每一个sql语句的映射信息： * 将sql抽取出来。 * * * @author lfy * */ public class MyBatisTest { public SqlSessionFactory getSqlSessionFactory() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); return new SqlSessionFactoryBuilder().build(inputStream); } @Test public void testInnerParam() throws IOException{ SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(); SqlSession openSession = sqlSessionFactory.openSession(); try{ EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class); Employee employee2 = new Employee(); employee2.setId(1); employee2.setLastName("%e%"); List<Employee> list = mapper.getEmpsByConditionIf(employee2); for (Employee employee : list) { System.out.println(employee); } }finally{ openSession.close(); } } @Test public void testBatchSave() throws IOException{ SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(); SqlSession openSession = sqlSessionFactory.openSession(); try{ EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class); List<Employee> emps = new ArrayList<>(); emps.add(new Employee(null, "smith0x1", "smith0x1@atguigu.com", "1",new Department(1))); emps.add(new Employee(null, "allen0x1", "allen0x1@atguigu.com", "0",new Department(1))); mapper.addEmps(emps); openSession.commit(); }finally{ openSession.close(); } } @Test public void testDynamicSql() throws IOException{ SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(); SqlSession openSession = sqlSessionFactory.openSession(); try{ EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class); //select * from tbl_employee where id=? and last_name like ? //测试ifwhere Employee employee = new Employee(1, "Admin", null, null); /* List<Employee> emps = mapper.getEmpsByConditionIf(employee ); for (Employee emp : emps) { System.out.println(emp); }*/ //查询的时候如果某些条件没带可能sql拼装会有问题 //1、给where后面加上1=1，以后的条件都and xxx. //2、mybatis使用where标签来将所有的查询条件包括在内。mybatis就会将where标签中拼装的sql，多出来的and或者or去掉 //where只会去掉第一个多出来的and或者or。 //测试Trim /*List<Employee> emps2 = mapper.getEmpsByConditionTrim(employee); for (Employee emp : emps2) { System.out.println(emp); }*/ //测试choose /*List<Employee> list = mapper.getEmpsByConditionChoose(employee); for (Employee emp : list) { System.out.println(emp); }*/ //测试set标签 /*mapper.updateEmp(employee); openSession.commit();*/ List<Employee> list = mapper.getEmpsByConditionForeach(Arrays.asList(1,2)); for (Employee emp : list) { System.out.println(emp); } }finally{ openSession.close(); } } /** * 1、根据xml配置文件（全局配置文件）创建一个SqlSessionFactory对象 有数据源一些运行环境信息 * 2、sql映射文件；配置了每一个sql，以及sql的封装规则等。 * 3、将sql映射文件注册在全局配置文件中 * 4、写代码： * 1）、根据全局配置文件得到SqlSessionFactory； * 2）、使用sqlSession工厂，获取到sqlSession对象使用他来执行增删改查 * 一个sqlSession就是代表和数据库的一次会话，用完关闭 * 3）、使用sql的唯一标志来告诉MyBatis执行哪个sql。sql都是保存在sql映射文件中的。 * * @throws IOException */ @Test public void test() throws IOException { // 2、获取sqlSession实例，能直接执行已经映射的sql语句 // sql的唯一标识：statement Unique identifier matching the statement to use. // 执行sql要用的参数：parameter A parameter object to pass to the statement. SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(); SqlSession openSession = sqlSessionFactory.openSession(); try { Employee employee = openSession.selectOne( "com.atguigu.mybatis.EmployeeMapper.selectEmp", 1); System.out.println(employee); } finally { openSession.close(); } } @Test public void test01() throws IOException { // 1、获取sqlSessionFactory对象 SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(); // 2、获取sqlSession对象 SqlSession openSession = sqlSessionFactory.openSession(); try { // 3、获取接口的实现类对象 //会为接口自动的创建一个代理对象，代理对象去执行增删改查方法 EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class); Employee employee = mapper.getEmpById(1); System.out.println(mapper.getClass()); System.out.println(employee); } finally { openSession.close(); } } @Test public void test02() throws IOException{ SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(); SqlSession openSession = sqlSessionFactory.openSession(); try{ EmployeeMapperAnnotation mapper = openSession.getMapper(EmployeeMapperAnnotation.class); Employee empById = mapper.getEmpById(1); System.out.println(empById); }finally{ openSession.close(); } } /** * 测试增删改 * 1、mybatis允许增删改直接定义以下类型返回值 * Integer、Long、Boolean、void * 2、我们需要手动提交数据 * sqlSessionFactory.openSession();===》手动提交 * sqlSessionFactory.openSession(true);===》自动提交 * @throws IOException */ @Test public void test03() throws IOException{ SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(); //1、获取到的SqlSession不会自动提交数据 SqlSession openSession = sqlSessionFactory.openSession(); try{ EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class); //测试添加 Employee employee = new Employee(null, "jerry4",null, "1"); mapper.addEmp(employee); System.out.println(employee.getId()); //测试修改 //Employee employee = new Employee(1, "Tom", "jerry@atguigu.com", "0"); //boolean updateEmp = mapper.updateEmp(employee); //System.out.println(updateEmp); //测试删除 //mapper.deleteEmpById(2); //2、手动提交数据 openSession.commit(); }finally{ openSession.close(); } } @Test public void test04() throws IOException{ SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(); //1、获取到的SqlSession不会自动提交数据 SqlSession openSession = sqlSessionFactory.openSession(); try{ EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class); //Employee employee = mapper.getEmpByIdAndLastName(1, "tom"); Map<String, Object> map = new HashMap<>(); map.put("id", 2); map.put("lastName", "Tom"); map.put("tableName", "tbl_employee"); Employee employee = mapper.getEmpByMap(map); System.out.println(employee); /*List<Employee> like = mapper.getEmpsByLastNameLike("%e%"); for (Employee employee : like) { System.out.println(employee); }*/ /*Map<String, Object> map = mapper.getEmpByIdReturnMap(1); System.out.println(map);*/ /*Map<String, Employee> map = mapper.getEmpByLastNameLikeReturnMap("%r%"); System.out.println(map);*/ }finally{ openSession.close(); } } @Test public void test05() throws IOException{ SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(); SqlSession openSession = sqlSessionFactory.openSession(); try{ EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class); /*Employee empById = mapper.getEmpById(1); System.out.println(empById);*/ /*Employee empAndDept = mapper.getEmpAndDept(1); System.out.println(empAndDept); System.out.println(empAndDept.getDept());*/ Employee employee = mapper.getEmpByIdStep(3); System.out.println(employee); //System.out.println(employee.getDept()); System.out.println(employee.getDept()); }finally{ openSession.close(); } } @Test public void test06() throws IOException{ SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(); SqlSession openSession = sqlSessionFactory.openSession(); try{ DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class); /*Department department = mapper.getDeptByIdPlus(1); System.out.println(department); System.out.println(department.getEmps());*/ Department deptByIdStep = mapper.getDeptByIdStep(1); System.out.println(deptByIdStep.getDepartmentName()); System.out.println(deptByIdStep.getEmps()); }finally{ openSession.close(); } } }
我们来看下运行的效果
DEBUG 04-26 06:52:48,375 ==> Preparing: select * from tbl_employee WHERE id=? and last_name like ? (BaseJdbcLogger.java:145)
DEBUG 04-26 06:52:48,430 ==> Parameters: 1(Integer), %e%(String) (BaseJdbcLogger.java:145)
DEBUG 04-26 06:52:48,452 <== Total: 0 (BaseJdbcLogger.java:145)
注意点：
<select id="getEmpsByConditionIf" resultType="com.atguigu.mybatis.bean.Employee"> select * from tbl_employee <!-- where --> <where> <!-- test：判断表达式（OGNL） OGNL参照PPT或者官方文档。 c:if test 从参数中取值进行判断 遇见特殊符号应该去写转义字符： &&： --> <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会进行字符串与数字的转换判断 "0"==0 --> <if test="gender==0 or gender==1"> and gender=#{gender} </if> </where> </select>
上面中如果将where标签去掉，写成下面的形式会存在问题
<select id="getEmpsByConditionIf" resultType="com.atguigu.mybatis.bean.Employee"> select * from tbl_employee <!-- where --> <!-- test：判断表达式（OGNL） OGNL参照PPT或者官方文档。 c:if test 从参数中取值进行判断 遇见特殊符号应该去写转义字符： &&： --> <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会进行字符串与数字的转换判断 "0"==0 --> <if test="gender==0 or gender==1"> and gender=#{gender} </if> </select>
在查询的时候，如果id字段为null会出现sql字符串拼接错误
DEBUG 04-26 07:02:46,554 ==> Preparing: select * from tbl_employee and last_name like ? (BaseJdbcLogger.java:145)
DEBUG 04-26 07:02:46,612 ==> Parameters: %e%(String) (BaseJdbcLogger.java:145)
解决办法除了上面添加where字段之外，还可以使用下面的语句增加where 1 =1
<select id="getEmpsByConditionIf" resultType="com.atguigu.mybatis.bean.Employee"> select * from tbl_employee where 1=1 <!-- where --> <!-- test：判断表达式（OGNL） OGNL参照PPT或者官方文档。 c:if test 从参数中取值进行判断 遇见特殊符号应该去写转义字符： &&： --> <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会进行字符串与数字的转换判断 "0"==0 --> <if test="gender==0 or gender==1"> and gender=#{gender} </if> </select>
但是上面的代码还存在问题
DEBUG 04-26 07:08:12,950 ==> Preparing: select * from tbl_employee where 1=1 id=? and last_name like ? (BaseJdbcLogger.java:145)
DEBUG 04-26 07:08:13,018 ==> Parameters: 1(Integer), %e%(String) (BaseJdbcLogger.java:145)
我们应该后续的所有判断条件上都增加and标签
<select id="getEmpsByConditionIf" resultType="com.atguigu.mybatis.bean.Employee"> select * from tbl_employee where 1=1 <!-- where --> <!-- test：判断表达式（OGNL） OGNL参照PPT或者官方文档。 c:if test 从参数中取值进行判断 遇见特殊符号应该去写转义字符： &&： --> <if test="id!=null"> and 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会进行字符串与数字的转换判断 "0"==0 --> <if test="gender==0 or gender==1"> and gender=#{gender} </if> </select>
但是我们最好还是使用where标签
如果写成下面的形式可以不了
<select id="getEmpsByConditionIf" resultType="com.atguigu.mybatis.bean.Employee"> select * from tbl_employee <where> <!-- where --> <!-- test：判断表达式（OGNL） OGNL参照PPT或者官方文档。 c:if test 从参数中取值进行判断 遇见特殊符号应该去写转义字符： &&： --> <if test="id!=null"> and 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会进行字符串与数字的转换判断 "0"==0 --> <if test="gender==0 or gender==1"> and gender=#{gender} </if> </where> </select>
测试代码
@Test public void testInnerParam() throws IOException{ SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(); SqlSession openSession = sqlSessionFactory.openSession(); try{ EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class); Employee employee2 = new Employee(); employee2.setId(null); employee2.setLastName("%e%"); employee2.setEmail(null); employee2.setGender("0"); List<Employee> list = mapper.getEmpsByConditionIf(employee2); for (Employee employee : list) { System.out.println(employee); } }finally{ openSession.close(); } }
我们来看下运行效果
DEBUG 04-26 07:14:15,499 ==> Preparing: select * from tbl_employee WHERE last_name like ? and gender=? (BaseJdbcLogger.java:145) DEBUG 04-26 07:14:15,556 ==> Parameters: %e%(String), 0(String) (BaseJdbcLogger.java:145) DEBUG 04-26 07:14:15,577 <== Total: 0 (BaseJdbcLogger.java:145)
我们可以看到id字段为null，email字段为null，不会报错，使用where标签会自动的将第一个and标签去掉，不会出现select * from tbl_employee WHERE and last_name like ? and gender=? 这样的错误语法
where标签只会去掉判断语句中第一个语句是and的情况，如果写成将and写在判断语句的最后面，也会出现问题
<select id="getEmpsByConditionIf" resultType="com.atguigu.mybatis.bean.Employee"> select * from tbl_employee <where> <!-- where --> <!-- test：判断表达式（OGNL） OGNL参照PPT或者官方文档。 c:if test 从参数中取值进行判断 遇见特殊符号应该去写转义字符： &&： --> <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会进行字符串与数字的转换判断 "0"==0 --> <if test="gender==0 or gender==1"> gender=#{gender} </if> </where> </select>
如果我们的查询的时候geder字段为null就会出现问题，where不会解决把and放在后面的情况
@Test public void testInnerParam() throws IOException{ SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(); SqlSession openSession = sqlSessionFactory.openSession(); try{ EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class); Employee employee2 = new Employee(); employee2.setId(1); employee2.setLastName("%e%"); employee2.setEmail(null); List<Employee> list = mapper.getEmpsByConditionIf(employee2); for (Employee employee : list) { System.out.println(employee); } }finally{ openSession.close(); } }
DEBUG 04-26 07:27:53,605 ==> Preparing: select * from tbl_employee WHERE id=? and last_name like ? and (BaseJdbcLogger.java:145) ，
DEBUG 04-26 07:27:53,665 ==> Parameters: 1(Integer), %e%(String) (BaseJdbcLogger.java:145)
所以使用where一定要按照标准，将and字段写在前面，如果上面使用where标签，and要写在后面如何解决了，我们来看下面的案例使用trim标签，trim标签有下面的四个属性来解决
DEBUG 04-26 07:27:53,605 ==> Preparing: select * from tbl_employee WHERE id=? and last_name like ? and (BaseJdbcLogger.java:145) 会存在在最后的sql语句多了一个and标签。我们可以使用
<select id="getEmpsByConditionIf" resultType="com.atguigu.mybatis.bean.Employee"> select * from tbl_employee <trim suffixOverrides="and"> <where> <!-- where --> <!-- test：判断表达式（OGNL） OGNL参照PPT或者官方文档。 c:if test 从参数中取值进行判断 遇见特殊符号应该去写转义字符： &&： --> <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会进行字符串与数字的转换判断 "0"==0 --> <if test="gender==0 or gender==1"> gender=#{gender} </if> </where> </trim> </select>
<trim suffixOverrides="and"> 标签将拼接成功最后的sql语句如果最后是and字段，就去掉就解决了上面的问题，trim标签使用较少，可以作为一个了解
上面的sql语句，还可以优化事情trim标签的前缀，去掉where标签
<select id="getEmpsByConditionIf" resultType="com.atguigu.mybatis.bean.Employee"> select * from tbl_employee <trim prefix="where" suffixOverrides="and"> <!-- where --> <!-- test：判断表达式（OGNL） OGNL参照PPT或者官方文档。 c:if test 从参数中取值进行判断 遇见特殊符号应该去写转义字符： &&： --> <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会进行字符串与数字的转换判断 "0"==0 --> <if test="gender==0 or gender==1"> gender=#{gender} </if> </trim> </select>
接下来，我们来使用分支选择器，等价于java中的switch -case
<!-- public List<Employee> getEmpsByConditionChoose(Employee employee); --> <select id="getEmpsByConditionChoose" resultType="com.atguigu.mybatis.bean.Employee"> select * from tbl_employee <where> <!-- 如果带了id就用id查，如果带了lastName就用lastName查;只会进入其中一个 --> <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> gender = 0 </otherwise> </choose> </where> </select>
接下来更新操作set标签
<!--public void updateEmp(Employee employee); --> <update id="updateEmp"> <!-- Set标签的使用 --> update tbl_employee <set> <if test="lastName!=null"> last_name=#{lastName} </if> <if test="email!=null"> email=#{email} </if> <if test="gender!=null"> gender=#{gender} </if> </set> where id=#{id} </update>
也可以使用trim来实现set标签
<!--public void updateEmp(Employee employee); --> <update id="updateEmp"> <!-- Set标签的使用 --> update tbl_employee <trim prefix="set" suffixOverrides=","> <if test="lastName!=null"> last_name=#{lastName}, </if> <if test="email!=null"> email=#{email}, </if> <if test="gender!=null"> gender=#{gender} </if> </trim> where id=#{id} --> </update>