笔记要点
出错分析与总结
/** 笔记: * 查询的时候,如果某些条件,没带可能SQL拼装会有问题; * 1.-->给where 后面加上 1=1, 以后的条件都and XXX * 2. <where> </where> 标签加上后,就不用写SQL的 where 条件语句! */
工程组织
数据库组织
0.重新修改Bean类
1.定义接口
EmployeeMapper_DynamicSQL
package com.dao; import com.bean.Employee; import java.util.List; public interface EmployeeMapper_DynamicSQL { public Employee getEmpsByConditionIf(int id); public List<Employee> getEmpsByConditionIf(Employee e); }
2.定义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"> <!--动态SQL几个主要模块:--> <!--if 标签 test="判断表达式,OGNL语法(参照PPT)" :从传入的参数中取值判断, 遇见特殊符号,写转义字符!-> and : && "" : "" 或者写单引号也可以!! 网址: W3CSchool->html教程->ISO-8859-> ASCII编码 OGNL : 会进行字符串与数字的转换判断 "0"==0 ,"1"==1 ; --> <!--where 标签 查询的时候,如果某些条件,没带可能SQL拼装会有问题; 给where 后面加上 1=1, 以后的条件都and XXX 2.mybatis 使用where 标签来将所有的查询条件包含在内, 自动去掉拼接后的SQL 的前面的多出来的and 或者or ;在后面的就不可以了! --> <!--choose (when, otherwise)--> <!--trim (where, set)--> <!--foreach--> <mapper namespace="com.dao.EmployeeMapper_DynamicSQL"> <!--1.查询员工,要求,携带了那个字段查询条件就带上那个条件的值; 传入两个条件进行模糊查询--> <select id="getEmpsByConditionIf" resultType="com.bean.Employee"> select * from tbl_employee <where> <if test="lastName !=null and lastName !='jerry' "> and last_name like #{lastName} </if> <if test="id != null"> and id=#{id} </if> </where> </select> </mapper>
3.编写测试代码
package com.test; import com.bean.Employee; import com.dao.EmployeeMapper_DynamicSQL; 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 java.io.IOException; import java.io.InputStream; import java.util.List; /** 笔记: * 查询的时候,如果某些条件,没带可能SQL拼装会有问题; * 1.-->给where 后面加上 1=1, 以后的条件都and XXX * 2. <where> </where> 标签加上后,就不用写SQL的 where 条件语句! */ public class test_tp38 { public SqlSessionFactory getSqlSessionFactory() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); return new SqlSessionFactoryBuilder().build(inputStream); } @Test public void test08() throws Exception { SqlSession openSession = getSqlSessionFactory().openSession(); try { System.out.println("++++++++++---- 1.测试 动态SQL元素:if关键字!"); EmployeeMapper_DynamicSQL mapper = openSession.getMapper(EmployeeMapper_DynamicSQL.class); Employee employee = new Employee(1, "%e%", null, null); List<Employee> emps = mapper.getEmpsByConditionIf(employee); for (Employee e:emps) System.out.println(e); openSession.commit(); } finally { openSession.close(); } } }
测试结果:
++++++++++---- 1.测试 动态SQL元素:if关键字! DEBUG 12-04 16:18:20,585 ==> Preparing: select * from tbl_employee WHERE last_name like ? and id=? (BaseJdbcLogger.java:145) DEBUG 12-04 16:18:20,608 ==> Parameters: %e%(String), 1(Integer) (BaseJdbcLogger.java:145) DEBUG 12-04 16:18:20,620 <== Total: 1 (BaseJdbcLogger.java:145) Employee{id=1, lastName='jerry', email='jerry@163.com', gender='1', dept=null}