Spring MVC（三）—— 集成MyBatis

一、MyBatis 介绍

　　MyBatis 是支持普通 SQL查询，存储过程和高级映射的优秀持久层框架。MyBatis 消除了几乎所有的JDBC代码和参数的手工设置以及结果集的检索。MyBatis 使用简单的 XML或注解用于配置和原始映射，将接口和 Java 的POJOs（Plain Old Java Objects，普通的 Java对象）映射成数据库中的记录。

 

二、Spring MVC中集成MyBatis 

1、准备依赖的jar包，添加到工程

 

2、MyBatis 与数据库连接配置

　先添加如下配置文件到工程：

 

 

applicationContext-mybatis.xml为mybatis配置文件，内容如下：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:p="http://www.springframework.org/schema/p" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:jdbc="http://www.springframework.org/schema/jdbc"
    xmlns:context="http://www.springframework.org/schema/context" xmlns:cache="http://www.springframework.org/schema/cache"
    xsi:schemaLocation="
      http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
      http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
      http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd
      http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
      http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
      http://www.springframework.org/schema/cache http://www.springframework.org/schema/cache/spring-cache-3.2.xsd"
    default-autowire="byName" default-lazy-init="false">

    <!-- Mybatis's sqlSessionFactory config -->
    <bean id="mysqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"></property>
        <property name="configLocation" value="classpath:mybatis/mybatis-config-mysql.xml" />
    </bean>
    <bean id="mapperScannerConfigurer"  class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="sqlSessionFactoryBeanName" value="mysqlSessionFactory" />
        <property name="basePackage" value="com.ruijie.crazy.dao.persistence" />
    </bean>

    <!-- Spring JtaTransactionManager -->
    <bean name="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    
    <!-- enable the configuration of transactional behavior based on annotations -->
    <tx:annotation-driven transaction-manager="transactionManager" order="1" />

    <!-- 启用事务 -->
    <tx:advice id="txAdvice" transaction-manager="transactionManager">
        <tx:attributes>
            <tx:method name="select*" read-only="true" propagation="SUPPORTS" />
            <tx:method name="list*" read-only="true" propagation="SUPPORTS" />
            <tx:method name="query*" read-only="true" propagation="SUPPORTS" />
            <tx:method name="get*" read-only="true" propagation="SUPPORTS" />
            <tx:method name="find*" read-only="true" propagation="SUPPORTS" />
            <tx:method name="count*" read-only="true" propagation="SUPPORTS" />

            <tx:method name="add*" read-only="false" propagation="REQUIRED" rollback-for="Throwable" />
            <tx:method name="del*" read-only="false" propagation="REQUIRED" rollback-for="Throwable" />
            <tx:method name="insert*" read-only="false" propagation="REQUIRED" rollback-for="Throwable" />
            <tx:method name="update*" read-only="false" propagation="REQUIRED" rollback-for="Throwable" />
            <tx:method name="delete*" read-only="false" propagation="REQUIRED" rollback-for="Throwable" />
            <tx:method name="save*" read-only="false" propagation="REQUIRED" rollback-for="Throwable" />

            <tx:method name="newTran*" read-only="false" propagation="REQUIRES_NEW" rollback-for="Throwable" />
            <tx:method name="xaTran*" read-only="false" propagation="REQUIRED" isolation="SERIALIZABLE" rollback-for="Throwable" />

            <tx:method name="*" propagation="REQUIRED" rollback-for="Throwable" />
        </tx:attributes>
    </tx:advice>
        
</beans>

applicationContext-database.xml为数据库连接配置文件，内容如下：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:p="http://www.springframework.org/schema/p" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:jdbc="http://www.springframework.org/schema/jdbc"
    xmlns:context="http://www.springframework.org/schema/context" xmlns:cache="http://www.springframework.org/schema/cache"
    xsi:schemaLocation="
      http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
      http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
      http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd
      http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
      http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
      http://www.springframework.org/schema/cache http://www.springframework.org/schema/cache/spring-cache-3.2.xsd"
    default-autowire="byName" default-lazy-init="false">
    <!-- 加载配置属性文件 -->
    <context:property-placeholder ignore-unresolvable="true" location="classpath:jdbc-dbcp.properties" />

    <!-- 数据库连接池 -->
    <bean id="parentDataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close" abstract="true">
        <property name="username" value="${dbcp.db.username}" />
        <property name="password" value="${dbcp.db.password}" />
        <property name="driverClassName" value="${dbcp.db.driverClassName}" />
        <property name="connectionProperties" value="${dbcp.db.connectionProperties}" />


        <!-- Connection Pooling Info -->
        <!-- 连接池启动时创建的初始化连接数量 -->
        <property name="initialSize" value="${dbcp.db.initialSize}" />
        <!-- 连接池中可同时连接的最大的连接数（默认值为8，调整为20，高峰单机器在20并发左右，自己根据应用场景定） -->
        <property name="maxTotal" value="${dbcp.db.maxTotal}" />
        <!-- 连接池中最大的空闲的连接数，超过的空闲连接将被释放，如果设置为负数表示不限制（默认为8个，maxIdle不能设置 太小，因为假如在高负载的情况下，连接的打开时间比关闭的时间快，会引起连接池中idle的个数 上升超过maxIdle，而造成频繁的连接销毁>和创建，类似于jvm参数中的Xmx设置) -->
        <property name="maxIdle" value="${dbcp.db.maxIdle}" />
        <!-- 连接池中最小的空闲的连接数，低于这个数量会被创建新的连接（默认为0，调整为5，该参数越接近maxIdle，性能越>好，因为连接的创建和销毁，都是需要消耗资源的；但是不能太大，因为在机器很空闲的时候，也会创建低于minidle个数的连接，类似于jvm>参数中的Xmn设置） -->
        <property name="minIdle" value="${dbcp.db.minIdle}" />
        <!--最大等待时间，当没有可用连接时，连接池等待连接释放的最大时间，超过该时间限制会抛出异常，如果设置-1表示无>限等待（默认为无限，调整为60000ms，避免因线程池不够用，而导致请求被无限制挂起） -->
        <property name="maxWaitMillis" value="${dbcp.db.maxWaitMillis}" />


        <!-- Validate配置代码 -->
        <property name="testOnCreate" value="${dbcp.db.testOnCreate}" />
        <!-- GenericObjectPool中针对pool管理，起了一个Evict的TimerTask定时线程进行控制(可通过设置参数timeBetweenEvictionRunsMillis>0),定时对线程池中的链接进行validateObject校验，对无效的链接进行关闭后，会调用ensureMinIdle，适当建立链接保证最小的minIdle连接数。 -->
        <property name="testWhileIdle" value="${dbcp.db.testWhileIdle}" />
        <!-- 对拿到的connection进行validateObject校验 -->
        <property name="testOnBorrow" value="${dbcp.db.testOnBorrow}" />
        <property name="testOnReturn" value="${dbcp.db.testOnReturn}" />
        
        <!-- 设置的Evict线程的时间，单位ms，大于0才会开启evict检查线程 -->
        <property name="timeBetweenEvictionRunsMillis" value="${dbcp.db.timeBetweenEvictionRunsMillis}" />
        <!-- 代表每次检查链接的数量，建议设置和maxActive一样大，这样每次可以有效检查所有的链接. -->
        <property name="numTestsPerEvictionRun" value="${dbcp.db.numTestsPerEvictionRun}" />
        <property name="minEvictableIdleTimeMillis" value="${dbcp.db.minEvictableIdleTimeMillis}" />
        <!-- 验证连接是否可用sql -->
        <property name="validationQuery" value="${dbcp.db.validationQuery}" />

        <!--超过removeAbandonedTimeout时间后，是否进 行没用连接（废弃）的回收（默认为false，调整为true) -->
        <property name="removeAbandonedOnMaintenance" value="${dbcp.db.removeAbandonedOnMaintenance}" />
        <property name="removeAbandonedOnBorrow" value="${dbcp.db.removeAbandonedOnBorrow}" />
        <!--超过时间限制，回收没有用(废弃)的连接（默认为 300秒，调整为180） -->
        <property name="removeAbandonedTimeout" value="${dbcp.db.removeAbandonedTimeout}" />
        <!-- 是否在自动回收超时连接的时候打印连接的超时错误 -->
        <property name="logAbandoned" value="${dbcp.db.logAbandoned}" />        
        
    </bean>

    <!-- 数据库连接池 -->
    <bean id="defaultDataSource" parent="parentDataSource">
        <property name="url" value="${dbcp.db.url}" />
    </bean>
   
    <bean id="dataSource" class="com.ruijie.crazy.core.db.DynamicDataSource">
        <property name="targetDataSources">
            <map key-type="java.lang.String">
            </map>
        </property>
        <property name="defaultTargetDataSource" ref="defaultDataSource" />
    </bean>
        
</beans>

jdbc-dbcp.properties为数据库连接配置：

dbcp.db.driverClassName=com.mysql.jdbc.Driver
dbcp.db.url=jdbc:mysql://127.0.0.1:3306/test
dbcp.db.username=root
dbcp.db.password=admin

dbcp.db.connectionProperties=useUnicode=true;characterEncoding=utf8;
#Connection Pooling Info
dbcp.db.initialSize=20
dbcp.db.maxTotal=50
dbcp.db.maxIdle=50
dbcp.db.minIdle=20
dbcp.db.maxWaitMillis=90000

dbcp.db.validationQuery=SELECT 1 FROM DUAL

dbcp.db.testOnCreate=false
dbcp.db.testWhileIdle=true
dbcp.db.testOnBorrow=true
dbcp.db.testOnReturn=false

dbcp.db.timeBetweenEvictionRunsMillis=60000
dbcp.db.numTestsPerEvictionRun=50
#The minimum amount of time an object may sit idle in the pool before it is eligable for eviction by the idle object evictor (if any).
dbcp.db.minEvictableIdleTimeMillis=300000

dbcp.db.removeAbandonedOnBorrow=true
dbcp.db.removeAbandonedOnMaintenance=true
dbcp.db.removeAbandonedTimeout=180
dbcp.db.logAbandoned=false

mybatis-config-mysql.xml为sql映射配置文件：

<?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>
        <!-- 全局映射器启用缓存，设置为false，由应用去管理缓存，mybatis专注于sql -->
        <setting name="cacheEnabled" value="false" />
        <!-- 查询时，关闭关联对象即时加载以提高性能 -->
        <setting name="lazyLoadingEnabled" value="true" />
        <!-- 设置关联对象加载的形态，此处为按需加载字段(加载字段由SQL指 定)，不会加载关联表的所有字段，以提高性能 -->
        <setting name="aggressiveLazyLoading" value="false" />
        <!-- 对于未知的SQL查询，允许返回不同的结果集以达到通用的效果 -->
        <setting name="multipleResultSetsEnabled" value="true" />
        <!-- 允许使用列标签代替列名 -->
        <setting name="useColumnLabel" value="true" />
        <!-- 允许使用自定义的主键值(比如由程序生成的UUID 32位编码作为键值)，数据表的PK生成策略将被覆盖 -->
        <setting name="useGeneratedKeys" value="false" />
        <!-- 给予被嵌套的resultMap以字段-属性的映射支持 -->
        <setting name="autoMappingBehavior" value="FULL" />
        <!-- 对于批量更新操作缓存SQL以提高性能，如果返回行数有问题，可以修改该值为SIMPLE -->
        <setting name="defaultExecutorType" value="REUSE" />
        <!-- 数据库超过120秒仍未响应则超时 -->
        <setting name="defaultStatementTimeout" value="120" />
    </settings>

    <!-- SQL映射文件 -->
    <mappers>
        <!-- 通用SQL语句片段 -->
        <mapper resource="mybatis/mapper/common_SqlMap.xml" />        
        <mapper resource="mybatis/mapper/TProvinceMapper.xml" />        
    </mappers>
</configuration>

common_SqlMap.xml是为了封装sql查询语句使用的配置文件，非必须配置：

<?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="common" >
  <sql id="If_Example_Conditon_Valid" >
    <if test="condition.valid" >
      <trim prefix="(" suffix=")" prefixOverrides="and" >
        <foreach collection="condition.criterions" item="criterion" >
          <choose >
            <when test="criterion.noValue" >
              and ${criterion.condition}
            </when>
            <when test="criterion.singleValue" >
              and ${criterion.condition} #{criterion.value}
            </when>
            <when test="criterion.betweenValue" >
              and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
            </when>
            <when test="criterion.listValue" >
              and ${criterion.condition}
              <foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
                #{listItem}
              </foreach>
            </when>
            <when test="criterion.dateValue" >
              and ${criterion.condition} #{criterion.value,jdbcType=TIMESTAMP}
            </when>
          </choose>
        </foreach>
      </trim>
    </if>
  </sql>
  <sql id="Example_Where_Clause" >
    <where >
      <foreach collection="oredCriteria" item="condition" separator="or" >
        <include refid="common.If_Example_Conditon_Valid" />
      </foreach>
    </where>
  </sql>
  <sql id="Update_By_Example_Where_Clause" >
    <where >
      <foreach collection="example.oredCriteria" item="condition" separator="or" >
        <include refid="common.If_Example_Conditon_Valid" />
      </foreach>
    </where>
  </sql>
</mapper>

TProvinceMapper.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.ruijie.crazy.dao.persistence.TProvinceMapper" >
  <resultMap id="BaseResultMap" type="com.ruijie.crazy.entity.beans.TProvince" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="province" property="province" jdbcType="VARCHAR" />
  </resultMap>
  <sql id="Base_Column_List" >
    id, province
  </sql>
  <select id="selectByExample" resultMap="BaseResultMap" parameterType="com.ruijie.crazy.core.mybatis.Criteria" >
    select
    <if test="distinct" >
      distinct
    </if> 
    <include refid="Base_Column_List" />
    from t_province
    <if test="_parameter != null" >
      <include refid="common.Example_Where_Clause" />
    </if>
  </select>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
    select 
    <include refid="Base_Column_List" />
    from t_province
    where id = #{id,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    delete from t_province
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <delete id="deleteByExample" parameterType="com.ruijie.crazy.core.mybatis.Criteria" >
    delete from t_province
    <if test="_parameter != null" >
      <include refid="common.Example_Where_Clause" />
    </if>
  </delete>
  <insert id="insert" parameterType="com.ruijie.crazy.entity.beans.TProvince" >
    <selectKey resultType="java.lang.Integer" keyProperty="id" order="BEFORE" >
      SELECT LAST_INSERT_ID()
    </selectKey>
    insert into t_province (id, province)
    values (#{id,jdbcType=INTEGER}, #{province,jdbcType=VARCHAR})
  </insert>
  <insert id="insertSelective" parameterType="com.ruijie.crazy.entity.beans.TProvince" >
    <selectKey resultType="java.lang.Integer" keyProperty="id" order="BEFORE" >
      SELECT LAST_INSERT_ID()
    </selectKey>
    insert into t_province
    <trim prefix="(" suffix=")" suffixOverrides="," >
      id,
      <if test="province != null" >
        province,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      #{id,jdbcType=INTEGER},
      <if test="province != null" >
        #{province,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <select id="countByExample" parameterType="com.ruijie.crazy.core.mybatis.Criteria" resultType="java.lang.Integer" >
    select count(*) from t_province
    <if test="_parameter != null" >
      <include refid="common.Example_Where_Clause" />
    </if>
  </select>
  <update id="updateByExampleSelective" parameterType="map" >
    update t_province
    <set >
      <if test="record.id != null" >
        id = #{record.id,jdbcType=INTEGER},
      </if>
      <if test="record.province != null" >
        province = #{record.province,jdbcType=VARCHAR},
      </if>
    </set>
    <if test="_parameter != null" >
      <include refid="common.Update_By_Example_Where_Clause" />
    </if>
  </update>
  <update id="updateByExample" parameterType="map" >
    update t_province
    set id = #{record.id,jdbcType=INTEGER},
      province = #{record.province,jdbcType=VARCHAR}
    <if test="_parameter != null" >
      <include refid="common.Update_By_Example_Where_Clause" />
    </if>
  </update>
  <update id="updateByPrimaryKeySelective" parameterType="com.ruijie.crazy.entity.beans.TProvince" >
    update t_province
    <set >
      <if test="province != null" >
        province = #{province,jdbcType=VARCHAR},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.ruijie.crazy.entity.beans.TProvince" >
    update t_province
    set province = #{province,jdbcType=VARCHAR}
    where id = #{id,jdbcType=INTEGER}
  </update>
    
</mapper>

3、java中代码

4、测试

Controller中写入如下测试代码：

package com.ruijie.crazy.controller;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import com.ruijie.crazy.dao.service.TProvinceDao;
import com.ruijie.crazy.entity.beans.TProvince;

@Controller
@RequestMapping("/myweb")
public class MyFirstController {
    @Resource
    TProvinceDao tProvinceDao;
        
    @RequestMapping(value = "/test", method = RequestMethod.GET)
    public ModelAndView getUserInfoByCode() {              
        System.out.println("/myweb/test");
        Map<String, Object> map = new HashMap<String, Object>();  
        
        List<TProvince> plist= tProvinceDao.getAll();
        map.put("userName", "ypf: " + plist.get(0).getProvince());  
        return new ModelAndView("hello",map);
    }
        
}

运行工程，浏览器输入http://127.0.0.1/crazypf/myweb/test.html，显示结果如下：