mybatis 一二事（二）

1 jdbc.driver=com.mysql.jdbc.Driver2 jdbc.url=jdbc:mysql://localhost:3306/mybatis3 jdbc.username=root4 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             主要以这样的方式为主来加载mapper60           -->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 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>

 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     @Override47     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 }