mybatis 一二事（2）

db.properties 单独提取出来的数据库配置，方便以后维护管理

1 jdbc.driver=com.mysql.jdbc.Driver2 jdbc.url=jdbc:mysql://localhost:3306/mybatis3 jdbc.username=root4 jdbc.password=root

SqlMapConfig.

 1 <??> 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.48     -->49     <!-- <mapper resource="sqlmap/UserMapper.-->50     <!-- class方式51     class：指定 mapper接口的地址52     遵循规则：将mapper.53     -->54     <!-- <mapper /> -->55     56     <!--57       批量mapper扫描58       遵循规则：将mapper.59       主要以这样的方式为主来加载mapper60      -->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.

 1 <??> 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 user112     <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   @Override47   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

原标题：mybatis 一二事（2）

关键词：mybatis