Mybatis与Spring整合（CURD）

项目采用Maven构建，用Junit进行测试，数据库是Mysql，连接池是c3p0，未测试缓存部分

1、Maven的“pom.xml”文件

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.mcs</groupId>
    <artifactId>mybatis04</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <properties>
        <!-- Generic properties -->
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <!-- Custom properties -->
        <mybatis.version>3.3.0</mybatis.version>
    </properties>

    <dependencies>
        <!-- junit -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- log4j -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
        </dependency>
        <!-- 数据库连接驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!-- c3p0 -->
        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
        </dependency>

        <!-- spring -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context-support</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aspects</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-messaging</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
        </dependency>

        <!-- apache commons -->
        <dependency>
            <groupId>commons-logging</groupId>
            <artifactId>commons-logging</artifactId>
        </dependency>

        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>${mybatis.version}</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>1.2.3</version>
        </dependency>

    </dependencies>


    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>io.spring.platform</groupId>
                <artifactId>platform-bom</artifactId>
                <version>2.0.0.RELEASE</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <finalName>Mybatis</finalName>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <version>2.19</version>
                <configuration>
                    <!-- Maven 跳过运行 Test 代码的配置 -->
                    <skipTests>true</skipTests>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

2、配置文件

2.1、db.properties　　

jdbc.driverClass = com.mysql.jdbc.Driver
jdbc.jdbcUrl = jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8
jdbc.user = root
jdbc.password = root

c3p0.initialPoolSize = 5
c3p0.maxPoolSize = 100

2.2、spring-core.xml　　

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd">

    <!-- 配置自动扫描的包 -->
    <context:component-scan base-package="com.mcs">
        <context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller" />
        <context:exclude-filter type="annotation" expression="org.springframework.web.bind.annotation.ControllerAdvice" />
    </context:component-scan>
    
</beans>

2.3、spring-mybatis.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:mybatis-spring="http://mybatis.org/schema/mybatis-spring" xsi:schemaLocation="http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.2.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.2.xsd">
    
    <!-- 加载数据库配置文件 -->
    <context:property-placeholder location="classpath:db.properties"/>
    
    <!-- 1、配置数据源 -->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="user" value="${jdbc.user}"></property>
        <property name="password" value="${jdbc.password}"></property>
        <property name="driverClass" value="${jdbc.driverClass}"></property>
        <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
        
        <property name="initialPoolSize" value="${c3p0.initialPoolSize}"></property>
        <property name="maxPoolSize" value="${c3p0.maxPoolSize}"></property>
    </bean>
    
    <!-- 2、配置sqlSessionFactory -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!-- 加载数据源 -->
        <property name="dataSource" ref="dataSource"></property>
        <!-- 加载MyBatis配置文件 -->
        <property name="configLocation" value="classpath:mybatis.xml"></property>
    </bean>
    
    <!-- 3、配置Mapper文件自动扫描 -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.mcs.dao" ></property>
        <property name="sqlSessionFactoryBeanName"  value="sqlSessionFactory"></property>
    </bean>
    
    <!-- 4、配置事务管理器 -->
    <!-- MyBatis默认使用Spring JDBC的事务管理器 -->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    
    <!-- 5、拦截器方式配置事物 -->
    <tx:advice id="transactionAdvice" transaction-manager="transactionManager">
        <tx:attributes>
            <tx:method name="add*" propagation="REQUIRED" />
            <tx:method name="append*" propagation="REQUIRED" />
            <tx:method name="save*" propagation="REQUIRED" />
            <tx:method name="update*" propagation="REQUIRED" />
            <tx:method name="modify*" propagation="REQUIRED" />
            <tx:method name="edit*" propagation="REQUIRED" />
            <tx:method name="delete*" propagation="REQUIRED" />
            <tx:method name="remove*" propagation="REQUIRED" />
            <tx:method name="init" propagation="REQUIRED" />
            <tx:method name="delAndInit" propagation="REQUIRED" />

            <tx:method name="get*" propagation="SUPPORTS" read-only="true" />
            <tx:method name="find*" propagation="SUPPORTS" read-only="true" />
            <tx:method name="load*" propagation="SUPPORTS" read-only="true" />
            <tx:method name="search*" propagation="SUPPORTS" read-only="true" />
            <tx:method name="datagrid*" propagation="SUPPORTS" read-only="true" />

            <tx:method name="*" propagation="REQUIRED" />
        </tx:attributes>
    </tx:advice>
    <aop:config>
        <aop:pointcut id="transactionPointcut" expression="execution(* com.mcs.service..*Impl.*(..))" />
        <aop:advisor pointcut-ref="transactionPointcut" advice-ref="transactionAdvice" />
    </aop:config>

</beans>

2.4、mybatis.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>        
    
    <!-- 全局的参数配置，如开启二级缓存等 -->    
    <settings>
        <!-- 配置延迟加载,按需加载对象属性 -->
        <setting name="lazyLoadingEnabled" value="true"/>  
        <setting name="aggressiveLazyLoading" value="false"/> 
    </settings>
    
    <!-- 使用别名 -->
    <typeAliases>
        <!-- 为包下的所有文件设置别名，别名为类名，不分大小写 -->
        <package name="com.mcs.entity"/>
    </typeAliases>
    
</configuration>

2.5、log4j.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
    <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
        <layout class="org.apache.log4j.PatternLayout">
            <param name="ConversionPattern" 
                value="%-5p %d{MM-dd HH:mm:ss,SSS} %m  (%F:%L) 
" />
        </layout>
    </appender>
    <logger name="java.sql">
        <level value="debug" />
    </logger>
    <logger name="org.apache.ibatis">
        <level value="debug" />
    </logger>
    <root>
        <level value="debug" />
        <appender-ref ref="STDOUT" />
    </root>
</log4j:configuration>

3、数据库实体类

package com.mcs.entity;

import java.io.Serializable;

public class Department implements Serializable {

    private static final long serialVersionUID = 6706877732712610561L;

    private Integer id;

    private String name;

    private String telephone;

    private Integer managerId;

    private Integer parentId;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name == null ? null : name.trim();
    }

    public String getTelephone() {
        return telephone;
    }

    public void setTelephone(String telephone) {
        this.telephone = telephone == null ? null : telephone.trim();
    }

    public Integer getManagerId() {
        return managerId;
    }

    public void setManagerId(Integer managerId) {
        this.managerId = managerId;
    }

    public Integer getParentId() {
        return parentId;
    }

    public void setParentId(Integer parentId) {
        this.parentId = parentId;
    }

    @Override
    public String toString() {
        return "Department [id=" + id + ", name=" + name + ", telephone=" + telephone + ", managerId=" + managerId
                + ", parentId=" + parentId + "]";
    }
    
    
}

4、Mapper.xml文件与对应的接口

4.1、DepartmentMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.mcs.dao.DepartmentMapper">

    <resultMap id="departmentResultMap" type="Department">
        <id column="id" property="id" jdbcType="INTEGER" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="telephone" property="telephone" jdbcType="VARCHAR" />
        <result column="manager_id" property="managerId" jdbcType="INTEGER" />
        <result column="parent_id" property="parentId" jdbcType="INTEGER" />
    </resultMap>

    <sql id="Base_Column_List">
        id, name, telephone, manager_id, parent_id
    </sql>

    <select id="findById" parameterType="Integer" resultMap ="departmentResultMap">
        select * from t_department where id = #{id}
    </select>

    <select id="findAll" resultMap="departmentResultMap">
        select * from t_department
    </select>

    <select id="findDetails" parameterType="Integer" resultMap="departmentResultMap">
        select * from t_department where parent_id = #{value}
    </select>


    <insert id="add" parameterType="com.mcs.entity.Department">
        <!-- 将Insert插入的数据的主键返回到User对象中，只限Mysql的自增长的Key -->
        <selectKey keyProperty="id" order="AFTER" resultType="int">
            SELECT LAST_INSERT_ID()
        </selectKey>
        
        insert into t_department
            (name, telephone, manager_id, parent_id)
        values
            (#{name}, #{telephone}, #{managerId}, #{parentId})
    </insert>
    
    <!-- 
    新增获取自增Key的另外一种方式
    <insert id="add"  keyColumn="id" keyProperty="id" useGeneratedKeys="true"  parameterType="com.mcs.entity.Department">
        insert into t_department
            (name, telephone, manager_id, parent_id)
        values
            (#{name}, #{telephone}, #{managerId}, #{parentId})
    </insert>
     -->

    <update id="updateById" parameterType="com.mcs.entity.Department">
        update t_department
        set name = #{name},
        telephone = #{telephone},
        manager_id = #{managerId},
        parent_id = #{parentId}
        where id = #{id}
    </update>

    <delete id="deleteById" parameterType="Integer">
        delete from t_department where id = #{id}
    </delete>

</mapper>

4.2、DepartmentMapper.java

package com.mcs.dao;

import java.util.List;

import com.mcs.entity.Department;

public interface DepartmentMapper {

    public void add(Department department) throws Exception;

    public void updateById(Department department) throws Exception;

    public void deleteById(Integer id) throws Exception;

    public Department findById(Integer id) throws Exception;

    public List<Department> findAll() throws Exception;

    public List<Department> findDetails(Integer parentId) throws Exception;


}

5、Service层代码

5.1、DepartmentService.java

package com.mcs.service;

import java.util.List;

import com.mcs.entity.Department;

public interface DepartmentService {
    public Department add(Department department) throws Exception;

    public void updateById(Integer id, Department department) throws Exception;

    public void deleteById(Integer id) throws Exception;

    public Department findById(Integer id) throws Exception;

    public List<Department> findAll() throws Exception;

    public List<Department> findDetails(Integer parentId) throws Exception;

}

5.2、DepartmentServiceImpl.java

package com.mcs.service.impl;

import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.mcs.dao.DepartmentMapper;
import com.mcs.entity.Department;
import com.mcs.service.DepartmentService;

@Service("departmentService")
public class DepartmentServiceImpl implements DepartmentService {
    
    @Autowired
    private DepartmentMapper departmentMapper;

    public Department add(Department department) throws Exception {
        departmentMapper.add(department);
        return department;
    }

    public void updateById(Integer id, Department department) throws Exception {
        Department d = departmentMapper.findById(id);
        BeanUtils.copyProperties(department, d);
        departmentMapper.updateById(d);
    }

    public void deleteById(Integer id) throws Exception {
        departmentMapper.deleteById(id);
    }

    public Department findById(Integer id) throws Exception {
        return departmentMapper.findById(id);
    }

    public List<Department> findAll() throws Exception {
        List<Department> departments = new ArrayList<Department>();
        departments = departmentMapper.findAll();
        return departments;
    }

    public List<Department> findDetails(Integer parentId) throws Exception {
        List<Department> departments = new ArrayList<Department>();
        departments = departmentMapper.findDetails(parentId);
        return departments;
    }


}

6、测试代码TestDepartmentService.java

package com.mcs.test;

import java.util.List;

import org.apache.log4j.Logger;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.mcs.entity.Department;
import com.mcs.service.DepartmentService;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "classpath:spring-core.xml", "classpath:spring-mybatis.xml" })
public class TestDepartmentService {
    /**
     * Logger for this class
     */
    private static final Logger logger = Logger.getLogger(TestDepartmentService.class);
    
    @Autowired
    private DepartmentService departmentService;

    @Test
    public void test() throws Exception {

    }
    
    @Test
    public void testFindById() throws Exception {
        Department department = departmentService.findById(3);
        logger.debug(department);
    }
    
    @Test
    public void testAdd() throws Exception {
        Department department = new Department();
        department.setName("行政部");
        department.setTelephone("021-86868686");
        department.setParentId(2);
        
        department = departmentService.add(department);
        logger.debug(department);
    }
    
    @Test
    public void testUpdateById() throws Exception {
        Department department = departmentService.findById(14);
        
        department.setTelephone("010-66668888");
        
        departmentService.updateById(14, department);
        logger.debug("部门更新成功！");
    }
    
    @Test
    public void testDeleteById() throws Exception {
        departmentService.deleteById(15);
    } 
    
    @Test
    public void testFindAll() throws Exception {
        List<Department> departments = departmentService.findAll();
        
        logger.debug(departments);
    } 
    
    @Test
    public void testFindDetails() throws Exception {
        List<Department> departments = departmentService.findDetails(1);
        
        logger.debug(departments);
    } 
    
    
}