【MyBatis】day03动态SQL和缓存机制

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. 
6. <mapper namespace="com.atguigu.mybatis.mapper.EmployeeMapper">
7. <sql id="emp_col">
8.         id,
9.         last_name,
10.         email,
11.         salary
12. </sql>
13. <sql id="select_employee">
14.         select
15.             id,
16.             last_name,
17.             email,
18.             salary
19.         from
20.             tbl_employee
21. </sql>
22. <!-- 按条件查询员工信息【条件不确定】-->
23. <select id="selectEmpByOpr" resultType="employee">
24. <include refid="select_employee"></include>
25. <where>
26. <if test="id != null">
27.                and id = #{id}
28. </if>
29. <if test="lastName != null">
30.                 and last_name = #{lastName}
31. </if>
32. <if test="email != null">
33.                 and email = #{email}
34. </if>
35. <if test="salary != null">
36.                 and salary = #{salary}
37. </if>
38. </where>
39. </select>
40. 
41. <select id="selectEmpByOprTrim" resultType="employee">
42. <include refid="select_employee"></include>
43. <trim prefix="where" suffixOverrides="and">
44. <if test="id != null">
45.                 id = #{id} and
46. </if>
47. <if test="lastName != null">
48.                 last_name = #{lastName} and
49. </if>
50. <if test="email != null">
51.                 email = #{email} and
52. </if>
53. <if test="salary != null">
54.                 salary = #{salary}
55. </if>
56. </trim>
57. </select>
58. 
59. <update id="updateEmpByOpr">
60.         update
61.             tbl_employee
62. <set>
63. <if test="lastName != null">
64.                 last_name=#{lastName},
65. </if>
66. <if test="email != null">
67.                 email=#{email},
68. </if>
69. <if test="salary != null">
70.                 salary=#{salary}
71. </if>
72. </set>
73.         where
74.             id = #{id}
75. </update>
76. 
77. 
78. <select id="selectEmpByOneOpr" resultType="employee">
79.         select
80. <include refid="emp_col"></include>
81.         from
82.             tbl_employee
83. <where>
84. <choose>
85. <when test="id != null">
86.                     id = #{id}
87. </when>
88. <when test="lastName != null">
89.                     last_name = #{lastName}
90. </when>
91. <when test="email != null">
92.                     email = #{email}
93. </when>
94. <when test="salary != null">
95.                     salary = #{salary}
96. </when>
97. <otherwise>
98.                     1=1
99. </otherwise>
100. </choose>
101. </where>
102. </select>
103. 
104. <select id="selectEmpByIds" resultType="employee">
105.         select
106.             id,
107.             last_name,
108.             email,
109.             salary
110.         from
111.             tbl_employee
112. <where>
113.             id in(
114. <foreach collection="ids" item="id" separator=",">
115.                 #{id}
116. </foreach>
117.             )
118. </where>
119. 
120. </select>
121. 
122. <insert id="batchInsertEmp">
123.         INSERT INTO
124.             tbl_employee(last_name,email,salary)
125.         VALUES
126. <foreach collection="employees" item="emp" separator=",">
127.                 (#{emp.lastName},#{emp.email},#{emp.salary})
128. </foreach>
129. </insert>
130. </mapper>

1. <!-- mybatis-ehcache -->
2. <dependency>
3. <groupId>org.mybatis.caches</groupId>
4. <artifactId>mybatis-ehcache</artifactId>
5. <version>1.0.3</version>
6. </dependency>
7. 
8. <!-- slf4j-log4j12 -->
9. <dependency>
10. <groupId>org.slf4j</groupId>
11. <artifactId>slf4j-log4j12</artifactId>
12. <version>1.6.2</version>
13. <scope>test</scope>
14. </dependency>

1. <?xml version="1.0" encoding="UTF-8"?>
2. <ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
3. xsi:noNamespaceSchemaLocation="../config/ehcache.xsd">
4. <!-- 磁盘保存路径 -->
5. <diskStore path="E:\mybatis\ehcache" />
6. 
7. <defaultCache
8. maxElementsInMemory="512"
9. maxElementsOnDisk="10000000"
10. eternal="false"
11. overflowToDisk="true"
12. timeToIdleSeconds="120"
13. timeToLiveSeconds="120"
14. diskExpiryThreadIntervalSeconds="120"
15. memoryStoreEvictionPolicy="LRU">
16. </defaultCache>
17. </ehcache>