MyBatis 动态SQL 

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 3         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 4 
 5 <mapper namespace="com.iotek.dao.IEmployeeDAO">
 6         <resultMap id="empMap" type="Employee">
 7                 <id property="empId" column="empId"/>
 8                 <result property="empName" column="empName"/>
 9                 <result property="salary" column="salary"/>
10                 <!--association:配置多对一、property：多方中一方的属性名，javaType：一方的类型-->
11                 <association property="dept" javaType="Dept">
12                         <id property="deptId" column="deptId"/>
13                         <result property="deptName" column="deptName"/>
14                 </association>
15         </resultMap>
16 
17 
18         <select id="getEmpAndDeptByEmpId" parameterType="int" resultMap="empMap">
19                 select t_dept.*,t_employee.* from t_dept,t_employee
20                     where t_dept.deptId = t_employee.dept_id
21                       AND t_employee.empId = #{id}
22         </select>
23 
24         <select id="getEmpByParams" parameterType="map" resultType="Employee">
25                 select t_employee.* from t_employee where 1=1
26                 <if test="empName!=null and empName!=''">
27                       and  empName like #{empName}
28                 </if>
29                 <if test="minSalary!=null and minSalary>=0">
30                         and salary >= #{minSalary}
31                 </if>
32                 <if test="maxSalary!=null and maxSalary>=0">
33                         and salary &lt;= #{maxSalary}
34                 </if>
35                 and salary is not null
36         </select>
37 
38         <select id="getEmpByParams2" parameterType="map" resultType="Employee">
39                 select t_employee.* from t_employee
40                 <where>
41                         <if test="empName!=null and empName!=''">
42                                 and  empName like #{empName}
43                         </if>
44                         <if test="minSalary!=null and minSalary>=0">
45                                 and salary >= #{minSalary}
46                         </if>
47                         <if test="maxSalary!=null and maxSalary>=0">
48                                 and salary &lt;= #{maxSalary}
49                         </if>
50                 </where>
51                 and salary is not null
52         </select>
53 
54         <select id="getEmpByParams3" parameterType="map" resultType="Employee">
55                 select t_employee.* from t_employee
56                 <choose>
57                         <when test="empName!=null and empName!=''">
58                                 where  empName like #{empName}
59                         </when>
60                         <when test="minSalary!=null and minSalary>=0">
61                                 where salary >= #{minSalary}
62                         </when>
63                         <when test="maxSalary!=null and maxSalary>=0">
64                                 where salary &lt;= #{maxSalary}
65                         </when>
66                         <otherwise>
67                                 where salary is not null
68                         </otherwise>
69                 </choose>
70         </select>
71 
72         <select id="getEmpInIds" parameterType="list" resultMap="empMap">
73                 select t_employee.* from t_employee
74                 <where>
75                         empId IN
76                         <foreach collection="list" item="id" open="(" close=")" separator=",">
77                                 #{id}
78                         </foreach>
79                 </where>
80 
81                 <!--等价于：select * from t_employee where empId in (1,2,3)-->
82                 <!--1,2,3就是被封装在list集合-->
83         </select>
84 
85         <update id="DynamicUpdateEmployee" parameterType="Employee">
86                 update t_employee
87                 <set>
88                         <if test="empName!=null and empName!=''">empName=#{empName},</if>
89                         <if test="salary!=null and salary>0">salary=#{salary},</if>
90 
91                 </set>
92                 where empId=#{empId}
93         </update>
94 </mapper>

 

posted @ 2017-04-12 17:12  Bob.Xie  阅读(144)  评论(0)    收藏  举报