MyBatis

 1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "/WEB-INF/dtd/mybatis-3-config.dtd"> 3 <configuration> 4  5     <!-- 开启缓存机制，要求所有的映射器都支持缓存 --> 6     <settings> 7         <setting name="cacheEnabled" value="true"></setting> 8     </settings> 9 10 11     <typeAliases>12         <!-- 一个一个给实体类型取类的别名 13         <typeAlias type="com.lovo.beans.UserBean" alias="UserBean"/>-->14     15         <package name="com.lovo.beans"/>16     </typeAliases>17 18 19     <environments default="first">20         <!-- 一个数据源对应一个环境配置 -->21         <environment id="first">22         23             <!-- 配置事务管理器，type="JDBC"表示使用JDBC事务处理机制来处理事务，MANAGED表示什么都不干，等待外部容器或者其他应用程序来处理事务 -->24             <transactionManager type="JDBC"></transactionManager>25             26             <!-- 配置数据源,type="POOLED"表示使用JDBC连接池；27             UNPOOLED表示不使用JDBC连接池，也就是说每次请求就分别对应一个连接 ；28             JNDI表示需要依赖外部容器提供连接池-->29             30             <dataSource type="POOLED">31                 <property name="driver" value="com.mysql.jdbc.Driver"/>32                 <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&amp;characterEncoding=UTF-8"/>33                 <property name="username" value="root"/>34                 <property name="password" value="lovo"/>35             </dataSource>36         </environment>37     </environments>38 39     <mappers>40         <!-- 41         <mapper resource="com/lovo/mapper/UserMapper.xml"/> -->42         <package name="com.lovo.mapper"/>43     </mappers>44 45 46 47 </configuration>

  1 <?xml version="1.0" encoding="UTF-8"?>  2 <!DOCTYPE mapper PUBLIC "-mybatis.org/DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">  3 <mapper namespace="com.lovo.mapper.UserMapper">  4     <!-- 为该映射器配置缓存 -->  5     <cache/>  6       7     <!-- resultMap 与resultType 是指定的返回结果集与类中属性的关系，而不是返回类型 -->  8     <resultMap type="UserBean" id="userMap">  9         <id property="id" column="id" javaType="java.lang.Long"/> 10         <result property="userName" column="user_name" javaType="java.lang.String"/> 11     </resultMap> 12  13     <!-- id="saveUserInfo" 代表着实现的是哪个接口方法， 14     parameterType="com.lovo.beans.UserBean" 代表着传入的参数，它的类型 15     useGeneratedKeys="true" 代表着获取数据库自增长的ID信息 16     keyProperty="u.id" 代表着将获取到的ID值赋值给哪个属性 --> 17     <insert id="saveUserInfo" parameterType="UserBean" useGeneratedKeys="true" keyProperty="u.id"> 18         insert into t_user(id,user_name,sex) values (null,#{u.userName},#{u.sex}) 19     </insert> 20      21     <insert id="batchSaveUser"> 22         insert into t_user (user_name,sex) values 23          24          25         <!-- 动态SQL之foreach的用法 --> 26         <!-- collection="users" 用于指定循环集合的名称，如果接口中并未指定参数别名，那么默认就是list 27          item="u" 用于指定每次循环后的对象的别名 28          separator="," 用于指定每次循环后之间的分割符--> 29         <foreach collection="users" item="u" separator=","> 30             (#{u.userName},#{u.sex}) 31         </foreach> 32     </insert> 33  34     <update id="updateUserInfo"> 35         <!--  36         update t_user set user_name = #{u.userName},sex = #{u.sex} where id = #{id}     37          --> 38         <!-- 动态SQL之set if的用法 --> 39         update t_user  40         <set> 41             <if test="u.userName != null"> 42                 user_name = #{u.userName}, 43             </if> 44          45             <if test="u.sex != null"> 46                 sex = #{u.sex} 47             </if> 48          49         </set> 50          51         <where> 52             id = #{id} 53         </where> 54     </update> 55  56     <delete id="deleteUserById"> 57         delete from t_user where id = #{id} 58     </delete> 59      60     <delete id="batchDeleteUser"> 61         delete from t_user where id in ( 62         <foreach collection="ids" item="id" separator=","> 63             #{id} 64         </foreach> 65         )     66          67         <!-- 第二种批量删除的写法 68         delete from t_user where id in  69         <foreach collection="ids" item="id" separator="," open="(" close=")"> 70             #{id} 71         </foreach> --> 72     </delete> 73  74     <select id="getUserInfoById" resultMap="userMap" useCache="true"> 75         select * from t_user where id = #{id}     76     </select> 77  78     <select id="queryUserInfoByName" resultType="UserBean"> 79         <!-- CONCAT(‘%‘,#{name},‘%‘)该数据库函数，主要用户拼接字符串     --> 80         select u.user_name as userName,u.sex as sex from t_user as u where u.user_name like CONCAT(‘%‘,#{name},‘%‘)     81     </select> 82      83     <select id="findUserInfoByDeptName" resultType="UserBean"> 84         select u.user_name as userName,u.sex as sex from t_user as u left join t_dept as d on u.fk_dept_id = d.id where d.dept_name like CONCAT(‘%‘,#{name},‘%‘) 85     </select> 86      87     <!-- mybatis取得复合对象内部值的方式是：对象.复合对象.复合对象的属性 --> 88     <select id="findUserInfoByDept" resultType="UserBean"> 89         select u.user_name as userName,u.sex as sex from t_user as u left join t_dept as d on u.fk_dept_id = d.id where d.dept_name like CONCAT(‘%‘,#{u.dept.deptName},‘%‘) 90     </select> 91      92     <!-- 分页统计 --> 93     <select id="countUserInfoByParams" resultType="int"> 94         select count(u.id) from t_user as u left join t_dept as d on u.fk_dept_id = d.id  95         <where> 96             <include refid="commonSQL"></include> 97         </where> 98     </select> 99     100     <select id="queryUserInfoByParams" resultType="UserBean">101         select u.id as id,u.user_name as userName,u.sex as sex from t_user as u left join t_dept as d on u.fk_dept_id = d.id 102         <where>103             <include refid="commonSQL"></include>104             limit ${page.index},${page.rows}105         </where>106     </select>107     108     109     <!-- 公共的SQL语句可以提到一处，其他地方采用include获取 -->110     <sql id="commonSQL">111         <if test="userName != null &amp;&amp; userName != ‘‘">112                 and u.user_name like CONCAT(‘%‘,#{userName},‘%‘)113         </if>114             115         <if test="deptName != null &amp;&amp; deptName != ‘‘">116                 and d.dept_name like CONCAT(‘%‘,#{deptName},‘%‘)117         </if>118     </sql>119     120     <!-- MAP作为参数传值时，直接通过#{key}去获取对应的值 -->121     122     <select id="countUserInfoByMapParams" parameterType="java.util.Map" resultType="int">123         select count(u.id) from t_user as u left join t_dept as d on u.fk_dept_id = d.id 124         <trim prefix="where" prefixOverrides="and|or">125             <include refid="commonSQL"></include>126         </trim>127     </select>128     129     <select id="queryUserInfoByMapParams" resultType="UserBean">130     select u.id as id,u.user_name as userName,u.sex as sex from t_user as u left join t_dept as d on u.fk_dept_id = d.id 131         <trim prefix="where" prefixOverrides="and|or" suffix="order by" suffixOverrides="and|or">132             <include refid="commonSQL"></include>133         </trim>134         <!--SQL语法： order by需要放置在limit之前 -->135         u.id desc limit ${index},${rows}136     </select>137     138     <select id="queryUserInfoByNameAndSex" resultType="UserBean">139         select id as id,user_name as userName,sex as sex from t_user140         <where>141         142         <!-- choose when 等同于java中的switch case的组合，143         只匹配一项，如果都不匹配，就执行otherwise中的内容，等同于default-->144             <choose>145                 <when test="name != null">146                     user_name like CONCAT(‘%‘,#{name},‘%‘)147                 </when>148                 <when test="sex != null">149                     and sex like CONCAT(‘%‘,#{sex},‘%‘)150                 </when>151                 <otherwise>1=1</otherwise>152             </choose>153         </where>154     </select>155     156 </mapper>

  1 package com.lovo.mapper;  2   3 import java.util.List;  4 import java.util.Map;  5   6 import org.apache.ibatis.annotations.CacheNamespace;  7 import org.apache.ibatis.annotations.CacheNamespaceRef;  8 import org.apache.ibatis.annotations.Delete;  9 import org.apache.ibatis.annotations.Insert; 10 import org.apache.ibatis.annotations.Options; 11 import org.apache.ibatis.annotations.Param; 12 import org.apache.ibatis.annotations.Result; 13 import org.apache.ibatis.annotations.ResultMap; 14 import org.apache.ibatis.annotations.ResultType; 15 import org.apache.ibatis.annotations.Results; 16 import org.apache.ibatis.annotations.Select; 17  18 import com.lovo.beans.Page; 19 import com.lovo.beans.UserBean; 20  21  22 @CacheNamespace//为该Mapper映射文件开启一个二级缓存空间 23 @CacheNamespaceRef(value = http://www.mamicode.com/UserMapper.class) 24 public interface UserMapper { 25  26     /** 27      * 保存用户 28      *  29      * @param user 30      * @return 31      */ 32     @Insert(value = "http://www.mamicode.com/insert into t_user(id,user_name,sex) values (null,#{u.userName},#{u.sex})") 33     @Options(useGeneratedKeys = true, keyProperty = "u.id") 34     public int saveUserInfo(@Param("u") UserBean user); 35  36     /** 37      * 根据ID修改用户 38      *  39      * @param user 40      * @param id 41      * @return 42      */ 43      44     public int updateUserInfo(@Param("u") UserBean user, @Param("id") Long id); 45  46     /** 47      * 根据ID删除用户 48      *  49      * @param id 50      * @return 51      */ 52     @Delete(value = "http://www.mamicode.com/delete from t_user where id = #{id}") 53     public int deleteUserById(@Param("id") Long id); 54  55     /** 56      * 根据ID查询用户 57      *  58      * @param id 59      * @return 60      */ 61     @Select(value = "http://www.mamicode.com/select * from t_user where id = #{id}") 62     @Options(useCache = true) 63     @ResultMap(value = "http://www.mamicode.com/userMap") 64     public UserBean getUserInfoById(@Param("id") Long id); 65  66     /** 67      * 根据用户名模糊查询所有的用户 68      *  69      * @param name 70      * @return 71      */ 72     @Select("select u.user_name as userName,u.sex as sex from t_user as u where u.user_name like CONCAT(‘%‘,#{name},‘%‘)") 73     @ResultType(UserBean.class) 74     public List<UserBean> queryUserInfoByName(@Param("name") String name); 75  76     /** 77      * 批量的增加 78      *  79      * @param users 80      * @return 81      */ 82     public int batchSaveUser(@Param("users") List<UserBean> users); 83  84     /** 85      * 批量的删除 86      *  87      * @param ids 88      * @return 89      */ 90     public int batchDeleteUser(@Param("ids") Long[] ids); 91  92     /** 93      * 根据部门名称查询所有的用户 94      *  95      * @param deptName 96      * @return 97      */ 98     @Select("select u.user_name as userName,u.sex as sex from t_user as u left join t_dept as d on u.fk_dept_id = d.id where d.dept_name like CONCAT(‘%‘,#{name},‘%‘)") 99     @Results({100             @Result(id = true, property = "id", column = "id", javaType = Long.class),101             @Result(property = "userName", column = "userName", javaType = String.class),102             @Result(property = "sex", column = "sex", javaType = String.class)103             })104     public List<UserBean> findUserInfoByDeptName(@Param("name") String deptName);105 106     /**107      * 根据用户中的部分信息查询用户108      * 109      * @param user110      * @return111      */112     @Select("select u.user_name as userName,u.sex as sex from t_user as u left join t_dept as d on u.fk_dept_id = d.id where d.dept_name like CONCAT(‘%‘,#{u.dept.deptName},‘%‘)")113     @ResultType(UserBean.class)114     public List<UserBean> findUserInfoByDept(@Param("u") UserBean user);115 116     /**117      * 根据条件统计查询总条数118      * 119      * @param userName120      * @param deptName121      * @return122      */123     public int countUserInfoByParams(@Param("userName") String userName,124             @Param("deptName") String deptName);125 126     /**127      * 查询满足条件的分页数据128      * 129      * @param userName130      * @param deptName131      * @param page132      * @return133      */134     public List<UserBean> queryUserInfoByParams(135             @Param("userName") String userName,136             @Param("deptName") String deptName, @Param("page") Page page);137 138     /**139      * 根据条件统计查询总条数140      * 141      * @param map142      * @return143      */144     public int countUserInfoByMapParams(Map<String, Object> map);145 146     /**147      * 查询满足条件的分页数据148      * 149      * @param map150      * @return151      */152     // Map作为多参数传值时，不能与其他类型参数结合。153     public List<UserBean> queryUserInfoByMapParams(Map<String, Object> map);154 155     /**156      * 根据用户名与密码查询用户157      * 158      * @param name159      * @param sex160      * @return161      */162     public List<UserBean> queryUserInfoByNameAndSex(@Param("name") String name,163             @Param("sex") String sex);164 165 }