四、动态SQL

说明：

if:判断

• choose (when, otherwise):分支选择；带了break的swtich-case

如果带了id就用id查，如果带了lastName就用lastName查;只会进入其中一个

• trim 字符串截取(where(封装查询条件), set(封装修改条件))

• foreach 遍历集合

目录结构：

主要测试写在各包的DynamicSQL文件中；

一、if & if-where & if-trim：判断；

实例说明：查询员工，携带什么字段查询添加就带上什么字段；

接口文件EmployeeMapper.java： 

 1 package dao;
 2 
 3 import java.util.List;
 4 
 5 import entity.Employee;
 6 
 7 public interface EmployeeMapperDynamicSQL {
 8 
 9     List<Employee> getList(Employee employee);
10　　　List<Employee> getListWhere(Employee employee);
11　　　　List<Employee> getListTrim(Employee employee);
12 }

映射文件EmployeeMapper.xml (if)

  • 版本1-if: 给where后面加1=1 以后条件都 and xxx
 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper
 3   PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4   "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5 <mapper namespace="dao.EmployeeMapperDynamicSQL">
 9     <!-- 查询员工，携带什么字段查询添加就带上什么字段 -->
10     <select id="getList" resultType="entity.Employee">
11         select id as id,
12         last_name as lastName,
13         email as email,
14         gender as gender
15         from tbl_employee
16         where 1=1
17         <!-- test 判断表达式(OGNL) OGNL 参照Apache官方文档 从参数中取值 进行判断 遇见特殊符号应该写转义字符 & ：&amp; 
18             也可以写 and ognl 会进行字符串和数字的转换 status==200 即 ”200“ 和 200可以比较 -->
19         <if test="lastName!='' and lastName!=null">
20             and last_name like #{lastName}
21         </if>
22         <if test="email!='' and email!=null">
23             and email like #{email}
24         </if>
25         <if test="gender!='' and gender!=null">
26             and gender like #{gender}
27         </if>
28 
29     </select>
30     
31 </mapper>

 

映射文件EmployeeMapper.xml (if-where)

  • 版本2-if-where：<where> 标签  给mybatis使用where标签来将所有的查询条件包括在内，mybatis就会将标签中拼装SQL，多出来的and或者or去掉 
  • where 只会去掉第一个 and 或者 or 
 1 <!-- 查询员工，携带什么字段查询添加就带上什么字段 <where> -->
 2     <select id="getListWhere" resultType="entity.Employee">
 3         select id as id,
 4         last_name as lastName,
 5         email as email,
 6         gender as gender
 7         from tbl_employee
 8         <!-- 查询的时候如果某些条件没带可能sql拼装会有问题 1.给where后面加1=1 以后条件都 and xxx 2.给mybatis使用where标签来将所有的查询条件包括在内，mybatis就会将标签中拼装SQL，多出来的and或者or去掉 
 9             //where 只会去掉第一个 and 或者 or -->
10         <where>
11             <if test="lastName!='' and lastName!=null">
12                 last_name like #{lastName}
13             </if>
14             <if test="email!='' and email!=null">
15                 and email like #{email}
16             </if>
17             <if test="gender!='' and gender!=null">
18                 and gender like #{gender}
19             </if>
20         </where>
21     </select> 

 

映射文件EmployeeMapper.xml (if-trim)

  • 版本3-if-trim：后面多出的and或者or where标签不能解决(推荐)

prefix="":前缀：trim标签体中是整个字符串拼接后的结果 prefix给拼接后的整个字符串加一个前缀 

prefixOverdides="": 前缀覆盖：去掉整个字符串前面多余的字符串

suffix=”“： 后缀 suffix给拼接后的整个字符串加一个后缀 

suffixOverrides="" 后缀覆盖：去掉整个字符串后面多余的字符串

 1 <!-- 查询员工，携带什么字段查询添加就带上什么字段 <where> -->
 2     <select id="getListTrim" resultType="entity.Employee">
 3         select id as id,
 4         last_name as lastName,
 5         email as email,
 6         gender as gender
 7         from tbl_employee
11         <!-- 自定义字符串截取规则 -->
12         <trim prefix="where" suffixOverrides="and">
13             <if test="lastName!='' and lastName!=null">
14                 last_name like #{lastName} and
15             </if>
16             <if test="email!='' and email!=null">
17                 email like #{email} and
18             </if>
19             <if test="gender!='' and gender!=null">
20                 gender like #{gender}
21             </if>
22         </trim>
23     </select>

 

junit测试类：

 1 @Test
 2     public void test01() {
 3         String resource = "mybatis-config.xml";
 4         SqlSession openSession = null;
 5         try {
 6             InputStream inputStream = Resources.getResourceAsStream(resource);
 7             SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 8             // 获取openSession 不会自动提交数据
 9             openSession = sqlSessionFactory.openSession(true);
10             EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
11             Employee emp = new Employee();
12             emp.setLastName("%x%");
13             List<Employee> testResultType = mapper.getList(emp);
14             List<Employee> testResultTypeWhere = mapper.getListWhere(emp);
15             List<Employee> testResultTypeTrim = mapper.getListTrim(emp);
16             System.out.println("测试 if：" + testResultType);
17             System.out.println("测试 if where：" + testResultTypeWhere);
18             System.out.println("测试 if Trim：" + testResultTypeTrim);
19 
20         } catch (Exception e) {
21             // TODO: handle exception
22         } finally {
23             if (openSession != null) {
24                 openSession.close();
25             }
26 
27         }
28     }

运行结果：

测试 if：[Employee [id=2, lastName=joy2x22222, email=joy52112225@iclound.com, gender=男]]

测试 if where：[Employee [id=2, lastName=joy2x22222, email=joy52112225@iclound.com, gender=男]]

测试 if Trim：[Employee [id=2, lastName=joy2x22222, email=joy52112225@iclound.com, gender=男]]

 

二、 choose：只进入一个查询；

接口文件EmployeeMapper.java：

    List<Employee> getListChoose(Employee employee);

映射文件EmployeeMapper.xml

 1 <select id="getListChoose" resultType="entity.Employee">
 2         select id as id,
 3         last_name as lastName,
 4         email as email,
 5         gender as gender
 6         from tbl_employee
 7         <where>
 8             <choose>
 9                 <when test="id!=null">
10                     id = #{id}
11                 </when>
12                 <when test="lastName!=null">
13                     last_name like #{lastName}
14                 </when>
15                 <when test="email!=null">
16                     email like #{email}
17                 </when>
18                 <otherwise>
19                     1=1
20                 </otherwise>
21             </choose>
22         </where>
23 
24     </select>

junit测试类：

 1 @Test
 2     public void test02() {
 3         String resource = "mybatis-config.xml";
 4         SqlSession openSession = null;
 5         try {
 6             InputStream inputStream = Resources.getResourceAsStream(resource);
 7             SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 8             // 获取openSession 不会自动提交数据
 9             openSession = sqlSessionFactory.openSession(true);
10             EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
11             Employee emp = new Employee();
12             emp.setEmail("%112225%");
13             List<Employee> testResultType = mapper.getListChoose(emp);
14             System.out.println("测试 choose：" + testResultType);
15         } catch (Exception e) {
16             // TODO: handle exception
17         } finally {
18             if (openSession != null) {
19                 openSession.close();
20             }
21 
22         }
23     }

 运行结果：

测试 choose：[Employee [id=1, lastName=joy33333, email=joy52112225@iclound.com, gender=女], Employee [id=2, lastName=joy2x22222, email=joy52112225@iclound.com, gender=男]]

三、set

更新数据的时候，如果直接写set 很容器出现 “,”多出来的问题；这里直接用trim来解决该问题；

接口文件EmployeeMapper.java：

void updateEmpTrim(Employee employee);

 

映射文件EmployeeMapper.xml

 1 <!-- trim 方式实现更新 -->
 2     <update id="updateEmpTrim" parameterType="entity.Employee">
 3         update tbl_employee 
 4         <trim prefix="set" suffixOverrides=",">
 5             <if test="lastName!=null">
 6                 last_name = #{lastName},
 7             </if>
 8             <if test="email!=null">
 9                 email=#{email},
10             </if>
11             <if test="gender!=null">
12                 gender = #{gender}
13             </if>
14         </trim>
15         
16         where id = #{id}
17     </update>

 

junit测试类：

 1 @Test
 2     public void test03() {
 3         String resource = "mybatis-config.xml";
 4         SqlSession openSession = null;
 5         try {
 6             InputStream inputStream = Resources.getResourceAsStream(resource);
 7             SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 8             // 获取openSession 不会自动提交数据
 9             openSession = sqlSessionFactory.openSession(true);
10             EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
11             Employee emp = new Employee();
12             emp.setEmail("1602211057@qq.com");
13             emp.setId(7);
14             // mapper.updateEmp(emp);
15             mapper.updateEmpTrim(emp);
16         } catch (Exception e) {
17             // TODO: handle exception
18         } finally {
19             if (openSession != null) {
20                 openSession.close();
21             }
22 
23         }

 

四、foreach

foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况 下，该属性的值是不一样的，主要有一下3种情况：

1. 如果传入的是单参数且参数类型是一个List的时候，collection属性值为list;如果User有属性List ids。入参是User对象，那么这个collection = "ids"；如果User有属性Ids ids;其中Ids是个对象，Ids有个属性List id;入参是User对象，那么collection = "ids.id"；

2. 如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array；
3. 如果使用Map封装了，collection的属性值为对应的Key；

以下例子以list 的3中情况为实例，array 和 map不做赘述；

情况一：传入的是单参数且参数类型是一个List的时候

接口文件EmployeeMapper.java：

List<Employee> getEmpsByConditionForeach(List<Integer> ids);

 

映射文件EmployeeMapper.xml

 1 <select id="getEmpsByConditionForeach" resultType="entity.Employee" parameterType="java.lang.Integer">
 2         select * from tbl_employee where id in
 3         
 4         <!-- 
 5         collection:指定要遍历的集合
 6         list类型的参数会封装在map中，map的key就是list
 7         itme:将当前遍历出的元素赋值给指定的变量
 8         #{变量名}就能取出变量的值也就是当前遍历出的元素
 9         separator:元素的分割符
10         open:遍历出所有结果拼接一个开始字符
11         close：遍历出所有结果拼接一个结束字符
12         index:索引。遍历list的时候 index 是索引 item 就是值  遍历map的时候index表示就是map的key,item就是map的值
13          -->
14          <foreach collection="list" item="item_id" separator="," open="(" close=")" index="">
15          #{item_id }
16          </foreach>
17     </select>

junit测试类：

 1 @Test
 2     public void test04() {
 3         String resource = "mybatis-config.xml";
 4         SqlSession openSession = null;
 5         try {
 6             InputStream inputStream = Resources.getResourceAsStream(resource);
 7             SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 8             // 获取openSession 不会自动提交数据
 9             openSession = sqlSessionFactory.openSession(true);
10             EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
11             List<Integer> list = Arrays.asList(1, 2, 3);
12             List<Employee> empList = mapper.getEmpsByConditionForeach(list);
13             System.out.println(empList);
14         } catch (Exception e) {
15             // TODO: handle exception
16         } finally {
17             if (openSession != null) {
18                 openSession.close();
19             }
20 
21         }
22     }

运行结果：

[Employee [id=1, lastName=null, email=joy52112225@iclound.com, gender=女], Employee [id=2, lastName=null, email=joy52112225@iclound.com, gender=男]]

 

情况二：传入的是一个实体且参数类型是一个List

给Employee实体添加了一个List<Integer> ids 私有成员变量； 该情况下，foreach 标签的collection 属性的值必须是ids，否则，就执行失败；

Employee 实体：

 1 package entity;
 2 
 3 import java.util.List;
 4 
 5 public class Employee {
 6 
 7     private Integer id;
 8     private String lastName;
 9     private String email;
10     private String gender;
11     private List<Integer> ids;
12 
13     public Integer getId() {
14         return id;
15     }
16 
17     public void setId(Integer id) {
18         this.id = id;
19     }
20 
21     public String getLastName() {
22         return lastName;
23     }
24 
25     public void setLastName(String lastName) {
26         this.lastName = lastName;
27     }
28 
29     public String getEmail() {
30         return email;
31     }
32 
33     public void setEmail(String email) {
34         this.email = email;
35     }
36 
37     public String getGender() {
38         return gender;
39     }
40 
41     public void setGender(String gender) {
42         this.gender = gender;
43     }
44 
45     public List<Integer> getIds() {
46         return ids;
47     }
48 
49     public void setIds(List<Integer> ids) {
50         this.ids = ids;
51     }
52 
53     @Override
54     public String toString() {
55         return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + "]";
56     }
57 
58 }
View Code

 

接口文件EmployeeMapper.java：

List<Employee> getEmpsByConditionForeach2(Employee employee);

 

映射文件EmployeeMapper.xml

1 <select id="getEmpsByConditionForeach2" resultType="entity.Employee" parameterType="entity.Employee">
2         select * from tbl_employee where id in
3          <foreach collection="ids" item="item_id" separator="," open="(" close=")" index="">
4          #{item_id }
5          </foreach>
6     </select>

 

junit测试类：

 1 @Test
 2     public void test04() {
 3         String resource = "mybatis-config.xml";
 4         SqlSession openSession = null;
 5         try {
 6             InputStream inputStream = Resources.getResourceAsStream(resource);
 7             SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 8             // 获取openSession 不会自动提交数据
 9             openSession = sqlSessionFactory.openSession(true);
10             EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
11             List<Integer> list = Arrays.asList(1, 2, 3);
12             Employee employee = new Employee();
13             employee.setIds(list);
14             List<Employee> empList = mapper.getEmpsByConditionForeach2(employee);
15             System.out.println(empList);
16         } catch (Exception e) {
17             // TODO: handle exception
18         } finally {
19             if (openSession != null) {
20                 openSession.close();
21             }
22 
23         }
24     }

运行结果同情况1；

 

情况三：传入的是一个实体且参数也是一个实体；

该情况下，foreach collection属性就是该实体的list属性值；

也就是说 ids 在 Employee.java实体 的 department成员变量的 ids中;

Employee 实体：

 1 package entity;
 2 
 3 public class Employee {
 4 
 5     private Integer id;
 6     private String lastName;
 7     private String email;
 8     private String gender;
 9     private Department department;
10 
11     public Integer getId() {
12         return id;
13     }
14 
15     public void setId(Integer id) {
16         this.id = id;
17     }
18 
19     public String getLastName() {
20         return lastName;
21     }
22 
23     public void setLastName(String lastName) {
24         this.lastName = lastName;
25     }
26 
27     public String getEmail() {
28         return email;
29     }
30 
31     public void setEmail(String email) {
32         this.email = email;
33     }
34 
35     public String getGender() {
36         return gender;
37     }
38 
39     public void setGender(String gender) {
40         this.gender = gender;
41     }
42 
43     public Department getDepartment() {
44         return department;
45     }
46 
47     public void setDepartment(Department department) {
48         this.department = department;
49     }
50 
51     @Override
52     public String toString() {
53         return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + "]";
54     }
55 
56 }
View Code

 Department实体：

 1 package entity;
 2 
 3 import java.util.List;
 4 
 5 public class Department {
 6 
 7     private Integer id;
 8     private String departmentName;
 9     private List<Employee> empList;
10     private List<Integer> ids;
11 
12     public Integer getId() {
13         return id;
14     }
15 
16     public void setId(Integer id) {
17         this.id = id;
18     }
19 
20     public String getDepartmentName() {
21         return departmentName;
22     }
23 
24     public void setDepartmentName(String departmentName) {
25         this.departmentName = departmentName;
26     }
27 
28     public List<Employee> getEmpList() {
29         return empList;
30     }
31 
32     public void setEmpList(List<Employee> empList) {
33         this.empList = empList;
34     }
35 
36     public List<Integer> getIds() {
37         return ids;
38     }
39 
40     public void setIds(List<Integer> ids) {
41         this.ids = ids;
42     }
43 
44     @Override
45     public String toString() {
46         return "Department [id=" + id + ", departmentName=" + departmentName + "]";
47     }
48 
49 }
View Code

 

接口文件EmployeeMapper.java：同情况二

映射文件EmployeeMapper.xml： 

1 <select id="getEmpsByConditionForeach2" resultType="entity.Employee" parameterType="entity.Employee">
2         select * from tbl_employee where id in
3          <foreach collection="department.ids" item="item_id" separator="," open="(" close=")" index="">
4          #{item_id }
5          </foreach>
6     </select>

junit测试类：同情况二

运行结果同上；

 情况四 foreach 循环map的情况

入参设置：

 1 @Override
 2     public PageInfo<Map<String, Object>> list(Employee employee) {
 3         FiledDao f1 = new FiledDao();
 4         f1.setColName("TEST_COL1");
 5         f1.setFieldName("field1");
 6         f1.setFileType("string");
 7         FiledDao f2 = new FiledDao();
 8         f2.setColName("TEST_COL2");
 9         f2.setFieldName("field2");
10         f2.setFileType("list");
11         FiledDao f3 = new FiledDao();
12         f3.setColName("TEST_COL3");
13         f3.setFieldName("field3");
14         f3.setFileType("bool");
15         FiledDao f4 = new FiledDao();
16         f4.setColName("TEST_COL4");
17         f4.setFieldName("field4");
18         f4.setFileType("number");
19         List<FiledDao> list = new ArrayList<>();
20         list.add(f1);
21         list.add(f2);
22         list.add(f3);
23         list.add(f4);
24         employee.setList(list);
25         Map<String, String> map = new HashMap<>();
26         employee.setMap(map);
27         map.put("TEST_COL1", "stringstring");
28         map.put("TEST_COL2", "listlist");
29         map.put("TEST_COL3", "Y");
30         map.put("TEST_COL4", "20");
31         map.put("TEST_COL4", "40");
32         return employeeMapper.list(employee);
33     }
View Code

入参实体类：

 1 package ssm.entity;
 2 
 3 import java.util.List;
 4 import java.util.Map;
 5 
 6 public class Employee extends CommonEntity {
 7 
 8     private Integer id;
 9     private String lastName;
10     private String email;
11     private String gender;
12 
13     private List<FiledDao> list;
14     private Map<String, String> map;
15 
16     public Integer getId() {
17         return id;
18     }
19 
20     public void setId(Integer id) {
21         this.id = id;
22     }
23 
24     public String getLastName() {
25         return lastName;
26     }
27 
28     public void setLastName(String lastName) {
29         this.lastName = lastName;
30     }
31 
32     public String getEmail() {
33         return email;
34     }
35 
36     public void setEmail(String email) {
37         this.email = email;
38     }
39 
40     public String getGender() {
41         return gender;
42     }
43 
44     public void setGender(String gender) {
45         this.gender = gender;
46     }
47 
48     public List<FiledDao> getList() {
49         return list;
50     }
51 
52     public void setList(List<FiledDao> list) {
53         this.list = list;
54     }
55 
56     public Map<String, String> getMap() {
57         return map;
58     }
59 
60     public void setMap(Map<String, String> map) {
61         this.map = map;
62     }
63 
64     @Override
65     public String toString() {
66         return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + "]";
67     }
68 
69 }
View Code

sql语句：

 1 <!-- 查询列表 -->
 2     <select id="list" resultType="map">
 3         select id,last_name as
 4         lastName,email,gender 
 5         <foreach collection="list" item="item" open="," separator=",">
 6         ${item.colName} AS ${item.fieldName}
 7         </foreach>
 8         from tbl_employee_test
 9         where 1=1
10         
11         <foreach collection="map" item="value" index="ey" open="" separator="">
12             <foreach collection="list" item="item" open="" separator="">
13                 <if test="item.fileType =='string' and key == item.colName">
14                     and ${item.colName} like concat("%",#{value,jdbcType=VARCHAR},"%") 
15                 </if>
16                 <if test="item.fileType =='list' and key == item.colName">
17                     and ${item.colName} =  #{value,jdbcType=VARCHAR}
18                 </if>
19                 <if test="item.fileType =='bool'and key == item.colName">
20                     and ${item.colName} =  #{value,jdbcType=VARCHAR}
21                 </if>
22                 <if test="item.fileType =='number' and key == item.colName">
23                     and ${item.colName} =  #{value,jdbcType=INTEGER}
24                 </if>
25             </foreach>
26         </foreach>
27     </select>

 

五、批量插入

接口文件EmployeeMapper.java：

    void insertEmps(List<Employee> emps);

映射文件EmployeeMapper.xml

 MySQL版本：

1 <insert id="insertEmps" parameterType="entity.Employee" useGeneratedKeys="true" keyProperty="id">
2         insert into tbl_employee(last_name,gender,email) values
3         <foreach collection="list" item="item"  separator=",">
4             (#{item.lastName},#{item.gender},#{item.email})
5         </foreach>
6     </insert>

Oracle版本方式1：

1 <insert id="insertBatChOracle">
2          <foreach collection="list" item="item" open="begin" close="end">
3              insert into tbl_employee(id,last_name,email)
4              values(employees_seq.nextval,#{item.lastName},#{item.email});_
5          </foreach>
6          
7      </insert>

Oracle版本方式2：

1 <insert id="insertBatChOracle">         
2          insert into tbl_employee(id,last_name,email)
3              select employees_seq.nextval,lastName,email from(
4              <foreach collection="list" item="item" separator="union">
5              select #{item.lastName},#{item.email} from dual
6              </foreach>
7              )
8      </insert>

 

junit测试类：

 1 @Test
 2     public void test05() {
 3         String resource = "mybatis-config.xml";
 4         SqlSession openSession = null;
 5         try {
 6             InputStream inputStream = Resources.getResourceAsStream(resource);
 7             SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 8             // 获取openSession 不会自动提交数据
 9             openSession = sqlSessionFactory.openSession(true);
10             EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
11             Employee employee1 = new Employee();
12             employee1.setLastName("zhumama");
13             employee1.setEmail("zhumama@166.com");
14             Employee employee2 = new Employee();
15             employee2.setLastName("zhubaba");
16             employee2.setEmail("zhubaba@166.com");
17             List<Employee> list = new ArrayList<Employee>();
18             list.add(employee1);
19             list.add(employee2);
20             mapper.insertEmps(list);
21         } catch (Exception e) {
22             // TODO: handle exception
23         } finally {
24             if (openSession != null) {
25                 openSession.close();
26             }
27 
28         }
29     }

 

六：内置参数：_parameter、_databaseId

mybatis默认两个内置参数：

1._parameter:代表整个参数

  • 单参数：_parameter就是这个参数
  • 多个参数：参数会封装为一个map；_parameter就代表整个参数

2._databaseId：如果配置了 DatabaseIdProvider标签 那么 databaseId 就是当前数据库的别名

 全局配置文件：

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE configuration
 3   PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 4   "http://mybatis.org/dtd/mybatis-3-config.dtd">
 5 <configuration>
 6     <properties resource="dbconfig.properties"></properties>
 7 
 8     <environments default="development">
 9         <environment id="development">
10             <transactionManager type="JDBC" />
11             <dataSource type="POOLED">
12                 <property name="driver" value="${jdbc.driver}" />
13                 <property name="url" value="${jdbc.url}" />
14                 <property name="username" value="${jdbc.username}" />
15                 <property name="password" value="${jdbc.password}" />
16             </dataSource>
17         </environment>
18         <environment id="development_mysql2">
19             <transactionManager type="JDBC" />
20             <dataSource type="POOLED">
21                 <property name="driver" value="${jdbc2.driver}" />
22                 <property name="url" value="${jdbc2.url}" />
23                 <property name="username" value="${jdbc2.username}" />
24                 <property name="password" value="${jdbc2.password}" />
25             </dataSource>
26         </environment>
27     </environments>
28 
29     <databaseIdProvider type="DB_VENDOR">
30         <!-- 为不同的数据库厂商取别名 -->
31         <property name="MySQL" value="mysql" />
32         <property name="Oracle" value="oracle" />
33         <property name="SQL Server" value="sqlserver" />
34     </databaseIdProvider>
35     <mappers>
36         <package name="dao" />
37     </mappers>
38 </configuration>

接口文件EmployeeMapper.java：

List<Employee> getEmpsTestInnerParam(Employee employee);

映射文件EmployeeMapper.xml

 1 <select id="getEmpsTestInnerParam" parameterType="entity.Employee" resultType="entity.Employee">
 2               <!-- bind:可以将ognl 表达式的值绑定到一个变量中，方面后来引用这个变量的值 -->
 3               
 4               <if test="_databaseId=='mysql'">
 5                   select * from tbl_employee
 6                   <if test="_parameter!=null">
 7                       <where>
 8                           <if test="id!=null">
 9                               id=#{id}<!--  也可以这么写 id=#{_parameter.id} -->
10                           </if>
11                           <if test="lastName!=null">
12                           <bind name="_lastanme" value="'%'+lastName+'%'"/>
13                               and last_name like #{_lastanme}<!--  也可以这么写 id=#{_parameter.id} -->
14                           </if>
15                       </where>
16                   </if>
17               </if>
18       </select>

junit测试类：

 1 @Test
 2     public void test06() {
 3         String resource = "mybatis-config.xml";
 4         SqlSession openSession = null;
 5         try {
 6             InputStream inputStream = Resources.getResourceAsStream(resource);
 7             SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 8             // 获取openSession 不会自动提交数据
 9             openSession = sqlSessionFactory.openSession(true);
10             EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
11             List<Employee> list = mapper.getEmpsTestInnerParam(null);
12             System.out.println(list);
13             Employee employee = new Employee();
14             // employee.setId(1);
15             employee.setLastName("zhu");
16             List<Employee> list1 = mapper.getEmpsTestInnerParam(employee);
17             System.out.println(list1);//            List<Employee> list2 = mapper.getListSqlSegment(null);
18         } catch (Exception e) {
19             // TODO: handle exception
20         } finally {
21             if (openSession != null) {
22                 openSession.close();
23             }
24 
25         }
26     }
View Code

运行结果：

[Employee [id=9, lastName=null, email=zhumama@166.com, gender=null], Employee [id=10, lastName=null, email=zhubaba@166.com, gender=null], Employee [id=11, lastName=null, email=zhumama@166.com, gender=null], Employee [id=12, lastName=null, email=zhubaba@166.com, gender=null], Employee [id=13, lastName=null, email=zhumama@166.com, gender=null], Employee [id=14, lastName=null, email=zhubaba@166.com, gender=null], Employee [id=27, lastName=null, email=zhumama@166.com, gender=null], Employee [id=28, lastName=null, email=zhubaba@166.com, gender=null], Employee [id=29, lastName=null, email=zhumama@166.com, gender=null], Employee [id=30, lastName=null, email=zhubaba@166.com, gender=null], Employee [id=31, lastName=null, email=zhumama@166.com, gender=null], Employee [id=32, lastName=null, email=zhubaba@166.com, gender=null]]

 

 七 、sql 片段

抽取可重用的SQL 片段 方便以后引用 

  1.sql抽取，经常将要查询的类名，或者插入用的列名抽取出来方便引用

  2.include 来引用已经抽取的SQL

  3.include 还可以自定义一些property SQL标签内部就能使用自定义属性

  include-property：取值的正确方式${prop}

  #{不能使用这种方式}

 

  例如：<include refid="com.common.dao.FunctionDao.SF_GET_LNG_RES_TYPE">

        <property name="AI_RES_TYPE_ID" value="a.res_type_id"/>

        <property name="lng" value="#{lngId}"/>

        <property name="female" value="'女'"/>

    </include> 

应用实例举例：

接口文件EmployeeMapper.java：

    List<Employee> getListSqlSegment(Employee employee);

映射文件EmployeeMapper.xml

 1 <select id="getListSqlSegment" resultType="entity.Employee">
 2         select 
 3         <!-- 引用外部定义的SQL -->
 4         <include refid="searchColumn"></include>
 5         from tbl_employee
 6         where 1=1
 7         <!-- test 判断表达式(OGNL) OGNL 参照Apache官方文档 从参数中取值 进行判断 遇见特殊符号应该写转义字符 & ：&amp; 
 8             也可以写 and ognl 会进行字符串和数字的转换 status==200 即 ”200“ 和 200可以比较 -->
 9         <if test="lastName!='' and lastName!=null">
10             and last_name like #{lastName}
11         </if>
12         <if test="email!='' and email!=null">
13             and email like #{email}
14         </if>
15         <if test="gender!='' and gender!=null">
16             and gender like #{gender}
17         </if>
18 
19     </select>
1  <sql id="searchColumn">
2       id as id,
3         last_name as lastName,
4         email as email,
5         gender as gender
6       </sql>

 

junit测试类：

 1 @Test
 2     public void test06() {
 3         String resource = "mybatis-config.xml";
 4         SqlSession openSession = null;
 5         try {
 6             InputStream inputStream = Resources.getResourceAsStream(resource);
 7             SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 8             // 获取openSession 不会自动提交数据
 9             openSession = sqlSessionFactory.openSession(true);
10             EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
11             List<Employee> list2 = mapper.getListSqlSegment(null);
12             System.out.println(list2);
13         } catch (Exception e) {
14             // TODO: handle exception
15         } finally {
16             if (openSession != null) {
17                 openSession.close();
18             }
19 
20         }
21     }

运行结果：

[Employee [id=1, lastName=joy33333, email=joy52112225@iclound.com, gender=女], Employee [id=2, lastName=joy2x22222, email=joy52112225@iclound.com, gender=男], Employee [id=4, lastName=joy, email=joy521125@iclound.com, gender=女], Employee [id=5, lastName=joy, email=joy521125@iclound.com, gender=女], Employee [id=6, lastName=joy, email=joy521125@iclound.com, gender=女], Employee [id=7, lastName=joy, email=1602211057@qq.com, gender=女], Employee [id=8, lastName=joy, email=1602211058@qq.com, gender=女], Employee [id=9, lastName=zhumama, email=zhumama@166.com, gender=null], Employee [id=10, lastName=zhubaba, email=zhubaba@166.com, gender=null], Employee [id=11, lastName=zhumama, email=zhumama@166.com, gender=null], Employee [id=12, lastName=zhubaba, email=zhubaba@166.com, gender=null], Employee [id=13, lastName=zhumama, email=zhumama@166.com, gender=null], Employee [id=14, lastName=zhubaba, email=zhubaba@166.com, gender=null], Employee [id=15, lastName=Tom, email=678@qq.com, gender=男], Employee [id=16, lastName=Tom, email=678@qq.com, gender=男], Employee [id=17, lastName=Tom, email=678@qq.com, gender=男], Employee [id=18, lastName=Tom, email=678@qq.com, gender=男], Employee [id=19, lastName=Tom, email=678@qq.com, gender=男], Employee [id=20, lastName=Tom, email=678@qq.com, gender=男], Employee [id=21, lastName=Tom, email=678@qq.com, gender=男], Employee [id=22, lastName=Tom, email=678@qq.com, gender=男], Employee [id=23, lastName=Tom, email=678@qq.com, gender=男], Employee [id=25, lastName=joy1111, email=joy521125@icloud.com, gender=女], Employee [id=26, lastName=joy222, email=joy521125@icloud.com, gender=女], Employee [id=27, lastName=zhumama, email=zhumama@166.com, gender=null], Employee [id=28, lastName=zhubaba, email=zhubaba@166.com, gender=null], Employee [id=29, lastName=zhumama, email=zhumama@166.com, gender=null], Employee [id=30, lastName=zhubaba, email=zhubaba@166.com, gender=null], Employee [id=31, lastName=zhumama, email=zhumama@166.com, gender=null], Employee [id=32, lastName=zhubaba, email=zhubaba@166.com, gender=null]]

posted @ 2022-03-04 11:33  啄木鸟伍迪  阅读(179)  评论(0)    收藏  举报
//火箭 GenerateContentList();