mybatis 一二事（2） - 动态代理

1 jdbc.driver=com.mysql.jdbc.Driver
2 jdbc.url=jdbc:mysql://localhost:3306/mybatis
3 jdbc.username=root
4 jdbc.password=root

 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>

 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 }

  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>

 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 }

 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 }

 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 }