mybatis 一二事（2） - 动态代理
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
