Mybatis使用Mapper方式CURD
Mybatis 使用Dao代码方式进行增、删、改、查和分页查询。
1、Maven的pom.xml
View Code
2、配置文件
2.1、db.properties
View Code
2.2、mybatis.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 <!-- 配置属性,加载数据库配置参数 --> 8 <properties resource="db.properties"></properties> 9 10 <!-- 使用别名 --> 11 <typeAliases> 12 <!-- 为包下的所有文件设置别名,别名为类名,不分大小写 --> 13 <package name="com.mcs.entity"/> 14 </typeAliases> 15 16 <!-- 和Spring整合后environments配置将废除 --> 17 <environments default="mysql_developer"> 18 <environment id="mysql_developer"> 19 <!-- mybatis使用jdbc事务管理方式 --> 20 <transactionManager type="JDBC" /> 21 <!-- mybatis使用连接池方式来获取连接 --> 22 <dataSource type="POOLED"> 23 <!-- 配置与数据库交互的4个必要属性 --> 24 <property name="driver" value="${jdbc.driverClass}" /> 25 <property name="url" value="${jdbc.jdbcUrl}" /> 26 <property name="username" value="${jdbc.user}" /> 27 <property name="password" value="${jdbc.password}" /> 28 </dataSource> 29 </environment> 30 </environments> 31 32 <!-- 加载映射文件 --> 33 <mappers> 34 <!-- 自动加载包下的所有映射文件 --> 35 <package name="com.mcs.mapper"/> 36 </mappers> 37 38 </configuration>
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 <select id="findAll" resultMap="employeeResultMap"> 50 select 51 id,name, sex, birthday, email, telephone, cellphone, address, department_id 52 from t_employee 53 </select> 54 55 <!-- 分页无条件查询所有员工信息 --> 56 <select id="findAllWithPage" parameterType="map" resultMap="employeeResultMap"> 57 select 58 id,name, sex, birthday, email, telephone, cellphone, address, department_id 59 from t_employee 60 limit #{pstart}, #{psize} 61 </select> 62 63 </mapper>
5、Mapper映射文件对应的接口文件
1 package com.mcs.mapper; 2 3 import java.util.List; 4 import java.util.Map; 5 6 import com.mcs.entity.Employee; 7 8 public interface EmployeeMapper { 9 10 public void add(Employee employee) throws Exception; 11 public void updateById(Employee employee) throws Exception; 12 public void deleteById(Integer id) throws Exception; 13 public Employee findById(Integer id) throws Exception; 14 public List<Employee> findAll() throws Exception; 15 public List<Employee> findAllWithPage(Map<String, Object> pageMap) throws Exception; 16 17 }
此文件应与Mapper在同一命名空间下
6、测试代码
1 package com.mcs.test; 2 3 import java.util.Date; 4 import java.util.LinkedHashMap; 5 import java.util.List; 6 import java.util.Map; 7 8 import org.apache.ibatis.session.SqlSession; 9 import org.apache.log4j.Logger; 10 import org.junit.After; 11 import org.junit.Before; 12 import org.junit.Test; 13 14 import com.mcs.entity.Employee; 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 25 private EmployeeMapper employeeMapper; 26 private SqlSession sqlSession = null; 27 28 @Before 29 public void init() { 30 sqlSession = MybatisUtil.getSqlSession(); 31 employeeMapper = sqlSession.getMapper(EmployeeMapper.class); 32 } 33 34 @After 35 public void destory() { 36 MybatisUtil.closeSqlSession(); 37 } 38 39 @Test 40 public void testFindById() throws Exception { 41 Employee employee = employeeMapper.findById(1); 42 logger.debug(employee); 43 } 44 45 @Test 46 public void testFindAll() throws Exception { 47 List<Employee> employees = employeeMapper.findAll(); 48 logger.debug(employees); 49 } 50 51 @Test 52 public void testAdd() throws Exception { 53 Employee employee = new Employee(); 54 employee.setName("赵小凤"); 55 employee.setSex("female"); 56 employee.setBirthday(new Date()); 57 employee.setEmail("xiaofeng@126.com"); 58 try { 59 employeeMapper.add(employee); 60 sqlSession.commit(); 61 } catch (Exception e) { 62 e.printStackTrace(); 63 sqlSession.rollback(); 64 throw e; 65 } 66 67 logger.debug(employee); 68 } 69 70 @Test 71 public void testEditById() throws Exception { 72 Employee employee = employeeMapper.findById(13); 73 employee.setDepartmentId(3); 74 employee.setAddress("天津"); 75 76 try { 77 employeeMapper.updateById(employee); 78 sqlSession.commit(); 79 } catch (Exception e) { 80 e.printStackTrace(); 81 sqlSession.rollback(); 82 throw e; 83 } 84 85 logger.debug(employee); 86 } 87 88 @Test 89 public void testDeleteById() throws Exception { 90 Employee employee = employeeMapper.findById(13); 91 logger.debug(employee); 92 try { 93 employeeMapper.deleteById(13); 94 sqlSession.commit(); 95 } catch (Exception e) { 96 e.printStackTrace(); 97 sqlSession.rollback(); 98 throw e; 99 } 100 101 logger.debug("已成功删除员工:" + employee.getName()); 102 } 103 104 @Test 105 public void testFindAllWithPage()throws Exception { 106 Map<String, Object> pageMap = new LinkedHashMap<String, Object>(); 107 Integer start = 6; 108 Integer size = 3; 109 pageMap.put("pstart", start); 110 pageMap.put("psize", size); 111 112 List<Employee> employees = employeeMapper.findAllWithPage(pageMap); 113 114 logger.debug(employees); 115 } 116 117 }