Mybatis 使用Mapper接口的Sql动态代码方式进行CURD和分页查询

1、Maven的pom.xml

View Code

2、配置文件

2.1、db.properties

View Code

2.2、mybatis.xml

View Code

2.3、log4j.xml

View Code

3、MybatisUtil工具类

View Code

4、Mapper映射文件

  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.mcs.mapper.EmployeeMapper">
  4     <resultMap id="employeeResultMap" type="com.mcs.entity.Employee">
  5         <id column="id" property="id" jdbcType="INTEGER" />
  6         <result column="name" property="name" jdbcType="VARCHAR" />
  7         <result column="sex" property="sex" jdbcType="VARCHAR" />
  8         <result column="birthday" property="birthday" jdbcType="DATE" />
  9         <result column="email" property="email" jdbcType="VARCHAR" />
 10         <result column="telephone" property="telephone" jdbcType="VARCHAR" />
 11         <result column="cellphone" property="cellphone" jdbcType="VARCHAR" />
 12         <result column="address" property="address" jdbcType="VARCHAR" />
 13         <result column="department_id" property="departmentId" jdbcType="INTEGER" />
 14     </resultMap>
 15 
 16     <!-- 新增职员，并返回插入后的ID值 -->
 17     <insert id="add" keyColumn="id" keyProperty="id" useGeneratedKeys="true" parameterType="Employee">
 18         insert into t_employee
 19         ( name, sex, birthday, email, telephone, cellphone, address, department_id )
 20         values
 21         ( #{name}, #{sex}, #{birthday}, #{email}, #{telephone}, #{cellphone}, #{address}, #{departmentId} )
 22     </insert>
 23 
 24     <update id="updateById" parameterType="Employee">
 25         update t_employee
 26         set name = #{name,jdbcType=VARCHAR},
 27         sex = #{sex,jdbcType=VARCHAR},
 28         birthday = #{birthday,jdbcType=DATE},
 29         email = #{email,jdbcType=VARCHAR},
 30         telephone = #{telephone,jdbcType=VARCHAR},
 31         cellphone = #{cellphone,jdbcType=VARCHAR},
 32         address = #{address,jdbcType=VARCHAR},
 33         department_id = #{departmentId,jdbcType=INTEGER}
 34         where id = #{id,jdbcType=INTEGER}
 35     </update>
 36 
 37     <delete id="deleteById" parameterType="Integer">
 38         delete from t_employee
 39         where id = #{id}
 40     </delete>
 41 
 42     <select id="findById" parameterType="Integer" resultMap="employeeResultMap">
 43         select
 44         id,name, sex, birthday, email, telephone, cellphone, address, department_id
 45         from t_employee
 46         where id = #{id}
 47     </select>
 48 
 49     <!-- 基本字段 -->
 50     <sql id="baseColumn">
 51         id,name, sex, birthday, email, telephone, cellphone, address, department_id
 52     </sql>
 53 
 54 
 55     <sql id="whereParam">
 56         <where>
 57             <if test="id!=null">
 58                 id = #{id}
 59             </if>
 60             <if test="name!=null">
 61                 name like #{name}
 62             </if>
 63             <if test="sex!=null">
 64                 sex = #{sex}
 65             </if>
 66             <if test="departmentId!=null">
 67                 department_id = #{departmentId}
 68             </if>
 69         </where>
 70     </sql>
 71     <!-- 动态查询与分页 -->
 72     <select id="findListByDynamic" parameterType="EmployeeCustom" resultMap="employeeResultMap">
 73         select
 74         <include refid="baseColumn"></include>
 75         from t_employee
 76         <include refid="whereParam"></include>
 77         <if test="pageNo!=null">
 78             <if test="pageSize!=null">
 79                 limit #{pageNo}, #{pageSize}
 80             </if>
 81         </if>
 82     </select>
 83     <select id="findListByDynamicCount" parameterType="EmployeeCustom" resultType="Long">
 84         select count(id) totalNumber
 85         from t_employee
 86         <include refid="whereParam"></include>
 87     </select>
 88 
 89     <!-- 动态更新 -->
 90     <update id="dynamicUpdateById" parameterType="Employee">
 91         update t_employee
 92         <!-- set标签自动判断哪个是最后一个字段，会自动去掉最后一个,号 -->
 93         <set>
 94             <if test="name!=null">
 95                 name = #{name},
 96             </if>
 97             <if test="sex!=null">
 98                 sex = #{sex},
 99             </if>
100             <if test="birthday!=null">
101                 birthday = #{birthday},
102             </if>
103             <if test="email!=null">
104                 email = #{email},
105             </if>
106             <if test="telephone!=null">
107                 telephone = #{telephone},
108             </if>
109             <if test="cellphone!=null">
110                 cellphone = #{cellphone},
111             </if>
112             <if test="address!=null">
113                 address = #{address},
114             </if>
115             <if test="departmentId!=null">
116                 department_id = #{departmentId},
117             </if>
118         </set>
119         where id = #{id}
120     </update>
121 
122     <!-- 动态批量删除，参数：Integer[] ids delete from t_employee where id in (10,12,13) -->
123     <delete id="dynamicDeleteByArray">
124         delete from t_employee where id in
125         <!-- foreach用于迭代数组元素 open表示开始符号 close表示结束符合 separator表示元素间的分隔符 item表示迭代的数组，属性值可以任意，但提倡与方法的数组名相同 #{ids}表示数组中的每个元素值 -->
126         <foreach collection="array" open="(" close=")" separator="," item="ids">
127             #{ids}
128         </foreach>
129     </delete>
130 
131     <!-- 动态批量删除，参数：List<Integer> ids delete from t_employee where id in (10,12,13) -->
132     <delete id="dynamicDeleteByList">
133         delete from t_employee where id in
134         <foreach collection="list" open="(" close=")" separator="," item="ids">
135             #{ids}
136         </foreach>
137     </delete>
138 
139     
140     <sql id="key">
141         <!-- 去掉最后一个, -->
142         <trim suffixOverrides=",">
143             <if test="name!=null">
144                 name,
145             </if>
146             <if test="sex!=null">
147                 sex,
148             </if>
149             <if test="birthday!=null">
150                 birthday,
151             </if>
152             <if test="email!=null">
153                 email,
154             </if>
155             <if test="telephone!=null">
156                 telephone,
157             </if>
158             <if test="cellphone!=null">
159                 cellphone,
160             </if>
161             <if test="address!=null">
162                 address,
163             </if>
164             <if test="departmentId!=null">
165                 department_id,
166             </if>
167         </trim>
168     </sql>
169     <sql id="value">
170         <!-- 去掉最后一个, -->
171         <trim suffixOverrides=",">
172             <if test="name!=null">
173                 #{name},
174             </if>
175             <if test="sex!=null">
176                 #{sex},
177             </if>
178             <if test="birthday!=null">
179                 #{birthday},
180             </if>
181             <if test="email!=null">
182                 #{email},
183             </if>
184             <if test="telephone!=null">
185                 #{telephone},
186             </if>
187             <if test="cellphone!=null">
188                 #{cellphone},
189             </if>
190             <if test="address!=null">
191                 #{address},
192             </if>
193             <if test="departmentId!=null">
194                 #{departmentId},
195             </if>
196         </trim>
197     </sql>
198     <!-- 动态增加 -->
199     <insert id="dynamicInsert" parameterType="Employee">
200         insert into t_employee(<include refid="key"/>) values(<include refid="value"/>)
201     </insert>
202     
203     
204 
205 </mapper>

View Code

5、Mapper映射文件对应的接口文件

 1 package com.mcs.mapper;
 2 
 3 import java.util.List;
 4 
 5 import com.mcs.entity.Employee;
 6 import com.mcs.entity.EmployeeCustom;
 7 
 8 public interface EmployeeMapper {
 9     /**
10      * 新增员工
11      */
12     public void add(Employee employee) throws Exception;
13     /**
14      * 根据Id修改员工
15      */
16     public void updateById(Employee employee) throws Exception;
17     /**
18      * 根据ID删除员工
19      */
20     public void deleteById(Integer id) throws Exception;
21     /**
22      * 根据ID查找员工
23      */
24     public Employee findById(Integer id) throws Exception;
25     /**
26      * 根据输入参数，动态查找员工，可分页
27      */
28     public List<Employee> findListByDynamic(EmployeeCustom employeeCustom) throws Exception;
29     /**
30      * 根据输入参数，动态合计员工记录数量
31      */
32     public Long findListByDynamicCount(EmployeeCustom employeeCustom) throws Exception;
33     /**
34      * 根据输入参数，动态更新
35      */
36     public void dynamicUpdateById(Employee employee) throws Exception;
37     /**
38      * 根据输入的Array参数，动态删除
39      */
40     public void dynamicDeleteByArray(Integer[] ids) throws Exception;
41     /**
42      * 根据输入List参数，动态删除
43      */
44     public void dynamicDeleteByList(List<Integer> ids) throws Exception;
45     /**
46      * 根据输入参数，动态插入
47      */
48     public void dynamicInsert(Employee employee) throws Exception;
49 }

View Code

此文件应与Mapper在同一命名空间下

6、测试代码

  1 package com.mcs.test;
  2 
  3 import java.util.ArrayList;
  4 import java.util.Date;
  5 import java.util.List;
  6 
  7 import org.apache.ibatis.session.SqlSession;
  8 import org.apache.log4j.Logger;
  9 import org.junit.After;
 10 import org.junit.Before;
 11 import org.junit.Test;
 12 
 13 import com.mcs.entity.Employee;
 14 import com.mcs.entity.EmployeeCustom;
 15 import com.mcs.mapper.EmployeeMapper;
 16 import com.mcs.util.MybatisUtil;
 17 
 18 public class TestEmployeeMapper {
 19     /**
 20      * Logger for this class
 21      */
 22     private static final Logger logger = Logger.getLogger(TestEmployeeMapper.class);
 23 
 24     private EmployeeMapper employeeMapper;
 25     private SqlSession sqlSession = null;
 26 
 27     @Before
 28     public void init() {
 29         sqlSession = MybatisUtil.getSqlSession();
 30         employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
 31     }
 32 
 33     @After
 34     public void destory() {
 35         MybatisUtil.closeSqlSession();
 36     }
 37 
 38     @Test
 39     public void testFindById() throws Exception {
 40         Employee employee = employeeMapper.findById(1);
 41         logger.debug(employee);
 42     }
 43 
 44     @Test
 45     public void testFindAll() throws Exception {
 46         List<Employee> employees = employeeMapper.findListByDynamic(null);
 47         logger.debug(employees);
 48         Long totalNumber = employeeMapper.findListByDynamicCount(null);
 49         logger.debug("共" + totalNumber + "条记录");
 50     }
 51 
 52     @Test
 53     public void testAdd() throws Exception {
 54         Employee employee = new Employee();
 55         employee.setName("赵小凤");
 56         employee.setSex("female");
 57         employee.setBirthday(new Date());
 58         employee.setEmail("xiaofeng@126.com");
 59         try {
 60             employeeMapper.add(employee);
 61             sqlSession.commit();
 62         } catch (Exception e) {
 63             e.printStackTrace();
 64             sqlSession.rollback();
 65             throw e;
 66         }
 67 
 68         logger.debug(employee);
 69     }
 70 
 71     @Test
 72     public void testEditById() throws Exception {
 73         Employee employee = employeeMapper.findById(13);
 74         employee.setDepartmentId(3);
 75         employee.setAddress("天津");
 76 
 77         try {
 78             employeeMapper.updateById(employee);
 79             sqlSession.commit();
 80         } catch (Exception e) {
 81             e.printStackTrace();
 82             sqlSession.rollback();
 83             throw e;
 84         }
 85 
 86         logger.debug(employee);
 87     }
 88 
 89     @Test
 90     public void testDeleteById() throws Exception {
 91         Employee employee = employeeMapper.findById(13);
 92         logger.debug(employee);
 93         try {
 94             employeeMapper.deleteById(13);
 95             sqlSession.commit();
 96         } catch (Exception e) {
 97             e.printStackTrace();
 98             sqlSession.rollback();
 99             throw e;
100         }
101 
102         logger.debug("已成功删除员工：" + employee.getName());
103     }
104 
105     @Test
106     public void testFindListByParam() throws Exception {
107         EmployeeCustom employeeCustom = new EmployeeCustom();
108         employeeCustom.setSex("male");
109         employeeCustom.setPageNo(0 * 5);
110         employeeCustom.setPageSize(5);
111 
112         List<Employee> employees = employeeMapper.findListByDynamic(employeeCustom);
113         for (Employee employee : employees) {
114             logger.debug(employee.getName());
115         }
116 
117         Long totalNumber = employeeMapper.findListByDynamicCount(employeeCustom);
118         if (employees.size() > 0) {
119             logger.debug("当前第" + (employeeCustom.getPageNo() + 1) + "页");
120             logger.debug("每页" + employeeCustom.getPageSize() + "条记录");
121             logger.debug("共" + totalNumber + "条记录");
122         }
123     }
124 
125     @Test
126     public void testDynamicUpdateByID() throws Exception {
127         Employee employee = new Employee();
128         employee.setId(13);
129         employee.setName("张丽");
130 
131         try {
132             employeeMapper.dynamicUpdateById(employee);
133             sqlSession.commit();
134         } catch (Exception e) {
135             e.printStackTrace();
136             sqlSession.rollback();
137             throw e;
138         }
139 
140     }
141 
142     @Test
143     public void testDynamicDeleteByArray() throws Exception {
144         Integer[] ids = new Integer[] { 10, 12, 13 };
145         try {
146             employeeMapper.dynamicDeleteByArray(ids);
147             sqlSession.commit();
148         } catch (Exception e) {
149             e.printStackTrace();
150             sqlSession.rollback();
151             throw e;
152         }
153     }
154 
155     @Test
156     public void testDynamicDeleteByList() throws Exception {
157         List<Integer> ids = new ArrayList<Integer>();
158         ids.add(10);
159         ids.add(12);
160         ids.add(13);
161 
162         try {
163             employeeMapper.dynamicDeleteByList(ids);
164             sqlSession.commit();
165         } catch (Exception e) {
166             e.printStackTrace();
167             sqlSession.rollback();
168             throw e;
169         }
170     }
171     
172     @Test
173     public void testDynamicInsert() throws Exception {
174         Employee employee = new Employee();
175         employee.setName("赵小梅");
176         employee.setSex("female");
177 
178         try {
179             employeeMapper.dynamicInsert(employee);
180             sqlSession.commit();
181         } catch (Exception e) {
182             e.printStackTrace();
183             sqlSession.rollback();
184             throw e;
185         }
186     }
187 
188 }

View Code

posted @ 2015-12-16 16:58 小个子猫阅读(3835) 评论(0) 编辑收藏举报

刷新页面返回顶部

茆长松

Mybatis 使用Mapper接口的Sql动态代码方式进行CURD和分页查询

公告