db.properties 單獨提取出來的數據庫配置，方便以後維護管理
1 jdbc.driver=com.mysql.jdbc.Driver 2 jdbc.url=jdbc:mysql://localhost:3306/mybatis 3 jdbc.username=root 4 jdbc.password=root
SqlMapConfig.xml
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 <!-- 加載數據庫連接參數配置文件 -->
7 <properties resource="db.properties" />
8
9 <!--
10 全局配置參數
11 比如 二級緩存 延遲加載...等
12 此全局參數會影響mybatis運行的性能，要謹慎配置
13 -->
14 <!-- <settings> -->
15 <!-- <setting name="" value=""/> -->
16 <!-- </settings> -->
17
18 <!-- 定義別名 -->
19 <typeAliases>
20 <!-- 單個別名定義
21 type：pojo的路徑
22 alias：別名的名稱
23 -->
24 <!-- <typeAlias type="cn.itcast.mybatis.po.User" alias="user"/> -->
25 <!-- 批量別名定義
26 name：指定包名，將包下邊的所有pojo定義別名 ，別名為類名（首字母大寫或小寫都行）
27 -->
28 <package name="com.mybatis.bean"/>
29 </typeAliases>
30
31 <!-- 和spring整合後 environments配置將廢除 -->
32 <environments default="development">
33 <environment id="development">
34 <transactionManager type="JDBC" />
35 <dataSource type="POOLED">
36 <property name="driver" value="${jdbc.driver}"/>
37 <property name="url" value="${jdbc.url}"/>
38 <property name="username" value="${jdbc.username}"/>
39 <property name="password" value="${jdbc.password}"/>
40 </dataSource>
41 </environment>
42 </environments>
43
44 <!-- 配置mapper映射文件 -->
45 <mappers>
46 <!-- resource方式
47 在UserMapper.xml，定義namespace為mapper接口的地址，映射文件通過namespace找到對應的mapper接口文件
48 -->
49 <!-- <mapper resource="sqlmap/UserMapper.xml" /> -->
50 <!-- class方式
51 class：指定 mapper接口的地址
52 遵循規則：將mapper.xml和mapper.java文件放在一個目錄 且文件名相同
53 -->
54 <!-- <mapper class="cn.itcast.mybatis.mapper.UserMapper"/> -->
55
56 <!--
57 批量mapper掃描
58 遵循規則：將mapper.xml和mapper.java文件放在一個目錄 且文件名相同
59 主要以這樣的方式為主來加載mapper
60 -->
61 <package name="com.mybatis.mapper"/>
62
63
64 </mappers>
65 </configuration>
UserMapper.java
1 package com.mybatis.mapper;
2
3 import java.util.List;
4 import java.util.Map;
5
6 import com.mybatis.bean.QueryVo;
7 import com.mybatis.bean.User;
8
9 public interface UserMapper {
10
11 public User findUserById(int id) throws Exception;
12
13 public List<User> findUserList(String name) throws Exception;
14
15 public Integer insertUser(User user) throws Exception;
16
17 public void deleteUser(int id) throws Exception;
18
19 public void updateUser(User user) throws Exception;
20
21 public List<User> findUserByBean(User user) throws Exception;
22
23 public List<User> findUserByMap(Map<String, Object> map) throws Exception;
24
25 public List<User> findUserByCustom(QueryVo queryVo) throws Exception;
26
27 // public Map findUserMapByCustom(QueryVo queryVo) throws Exception;
28
29 public void updateUserSet(User user) throws Exception;
30
31 }
UserMapper.xml
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.mybatis.mapper.UserMapper" >
7
8 <!--
9 sql 片段，可以供其他的sql一起使用
10 建議以單表抽取查詢條件
11 -->
12 <sql id="query_for_user">
13 <if test=" user != null ">
14 <if test=" user.name != null and user.name != '' ">
15 and name like '%${user.name}%'
16 </if>
17 <if test=" user.sex != null and user.sex != '' ">
18 and sex = #{user.sex}
19 </if>
20 </if>
21
22 <if test="ids != null">
23 <foreach collection="ids" separator="or" item="item" open="and (" close=")">
24 id = #{item}
25 </foreach>
26 </if>
27 </sql>
28
29
30 <select id="findUserById" parameterType="int" resultType="com.mybatis.bean.User">
31 SELECT * FROM USER WHERE id = #{id}
32 </select>
33
34 <!--
35 #{} 表示占位符，#{}可以使用value或者其他字符，可以防止sql注入，使用時無需考慮參數的類型
36 ${} 表示sql拼接，把原始的內容不加修飾的放入sql中，${}只能使用value，不可以防止sql注入，必須考慮參數的類型
37 一般在沒有特殊情況下使用#{}為主
38 有些情況必須使用${}，比如
39 動態拼接表名：select * from ${tablename}, 如果使用了#{}則會在傳入的表名上加單引號 ''
40 動態拼接排序字段：select * from user order by ${username}
41
42 舉個栗子：
43 查詢日期的區別：
44 select * from user where birthday >= #{date}
45 select * from user where birthday >= to_date('${date}', 'yyyy-MM-dd')
46 -->
47
48 <select id="findUserList" parameterType="java.lang.String" resultType="com.mybatis.bean.User" >
49 select * from user where name like '%${value}%'
50 </select>
51
52 <insert id="insertUser" parameterType="com.mybatis.bean.User">
53 <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
54 select LAST_INSERT_ID()
55 </selectKey>
56 insert into user(name,age,sex) values(#{name},#{age},#{sex})
57 </insert>
58
59 <delete id="deleteUser" parameterType="int">
60 delete from user where id=#{id}
61 </delete>
62
63 <update id="updateUser" parameterType="com.mybatis.bean.User">
64 update user set name=#{name},age=#{age},sex=#{sex} where id=#{id}
65 </update>
66
67 <select id="findUserByBean" parameterType="User" resultType="User">
68 select * from user where name like '%${name}%' and sex = #{sex}
69 </select>
70
71 <select id="findUserByMap" parameterType="hashmap" resultType="User">
72 select * from user where name like '%${name}%' and age >= #{age}
73 </select>
74
75 <!--
76 parameterMap 已經過期不建議使用, 官方已經廢除
77 resultMap 不建議使用，太復雜
78 -->
79 <select id="findUserByCustom" parameterType="QueryVo" resultType="User">
80 select * from user
81
82 <!--
83 where標簽自動將 where後的第一個and去掉，比where 1=1 and 要好很多
84 where name like '%${user.name}%' and sex = #{user.sex}
85 -->
86 <!-- <where> -->
87 <!-- <if test=" user != null "> -->
88 <!-- <if test=" user.name != null and user.name != '' "> -->
89 <!-- and name like '%${user.name}%' -->
90 <!-- </if> -->
91 <!-- <if test=" user.sex != null and user.sex != '' "> -->
92 <!-- and sex = #{user.sex} -->
93 <!-- </if> -->
94 <!-- </if> -->
95 <!-- </where> -->
96
97 <where>
98 <include refid="query_for_user"></include>
99 </where>
100
101 </select>
102
103 <!--
104 不建議使用map作為返回值，因為在代碼中需要對key進行硬編碼
105 -->
106 <!-- <select id="findUserMapByCustom" parameterType="QueryVo" resultType="hashmap"> -->
107 <!-- select * from user where name like '%${user.name}%' and sex >= #{user.sex} -->
108 <!-- </select> -->
109
110 <update id="updateUserSet" parameterType="User">
111 update user
112 <set>
113 <if test="name != null and name != '' ">
114 name = #{name},
115 </if>
116 <if test="age != null and age != '' and age != 0 ">
117 age = #{age},
118 </if>
119 <if test="sex != null and sex != '' ">
120 sex = #{sex},
121 </if>
122 </set>
123 where id = #{id};
124 </update>
125
126 </mapper>
QueryVo.java
1 package com.mybatis.bean;
2
3 import java.util.List;
4
5 /**
6 * 查詢的封裝類
7 *
8 * @author leechenxiang
9 * @date 2016年3月5日
10 *
11 */
12 public class QueryVo {
13
14 private User user;
15
16 private UserCustom uc;
17
18 private List<Integer> ids;
19
20 public UserCustom getUc() {
21 return uc;
22 }
23
24 public void setUc(UserCustom uc) {
25 this.uc = uc;
26 }
27
28 public User getUser() {
29 return user;
30 }
31
32 public void setUser(User user) {
33 this.user = user;
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 }
User.java
1 package com.mybatis.bean;
2
3 public class User {
4
5 private int id;
6 private String name;
7 private int age;
8 private String sex;
9
10 public User() {
11 super();
12 }
13
14 public User(String name, int age, String sex) {
15 super();
16 this.name = name;
17 this.age = age;
18 this.sex = sex;
19 }
20
21 public int getId() {
22 return id;
23 }
24 public void setId(int id) {
25 this.id = id;
26 }
27 public String getName() {
28 return name;
29 }
30 public void setName(String name) {
31 this.name = name;
32 }
33 public int getAge() {
34 return age;
35 }
36 public void setAge(int age) {
37 this.age = age;
38 }
39 public String getSex() {
40 return sex;
41 }
42 public void setSex(String sex) {
43 this.sex = sex;
44 }
45
46 @Override
47 public String toString() {
48 return "User [id=" + id + ", name=" + name + ", age=" + age + ", sex="
49 + sex + "]";
50 }
51
52 }
UserCustom.java
1 package com.mybatis.bean;
2
3 /**
4 * 擴展User的自定義類
5 * 擴展對象以'XxxxCustom'的格式命名
6 *
7 * @author leechenxiang
8 * @date 2016年3月5日
9 *
10 */
11 public class UserCustom extends User {
12
13 private String youngOrOld;
14
15 public String getYoungOrOld() {
16 return youngOrOld;
17 }
18
19 public void setYoungOrOld(String youngOrOld) {
20 this.youngOrOld = youngOrOld;
21 }
22
23 }
最後附上github地址：https://github.com/leechenxiang/mybatis002-dynamic-proxy
