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配置文件，内容如下：
 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <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"
 3     xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:jdbc="http://www.springframework.org/schema/jdbc"
 4     xmlns:context="http://www.springframework.org/schema/context" xmlns:cache="http://www.springframework.org/schema/cache"
 5     xsi:schemaLocation="
 6       http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
 7       http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
 8       http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd
 9       http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
10       http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
11       http://www.springframework.org/schema/cache http://www.springframework.org/schema/cache/spring-cache-3.2.xsd"
12     default-autowire="byName" default-lazy-init="false">
13 
14     <!-- Mybatis's sqlSessionFactory config -->
15     <bean id="mysqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
16         <property name="dataSource" ref="dataSource"></property>
17         <property name="configLocation" value="classpath:mybatis/mybatis-config-mysql.xml" />
18     </bean>
19     <bean id="mapperScannerConfigurer"  class="org.mybatis.spring.mapper.MapperScannerConfigurer">
20         <property name="sqlSessionFactoryBeanName" value="mysqlSessionFactory" />
21         <property name="basePackage" value="com.ruijie.crazy.dao.persistence" />
22     </bean>
23 
24     <!-- Spring JtaTransactionManager -->
25     <bean name="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
26         <property name="dataSource" ref="dataSource"></property>
27     </bean>
28     
29     <!-- enable the configuration of transactional behavior based on annotations -->
30     <tx:annotation-driven transaction-manager="transactionManager" order="1" />
31 
32     <!-- 启用事务 -->
33     <tx:advice id="txAdvice" transaction-manager="transactionManager">
34         <tx:attributes>
35             <tx:method name="select*" read-only="true" propagation="SUPPORTS" />
36             <tx:method name="list*" read-only="true" propagation="SUPPORTS" />
37             <tx:method name="query*" read-only="true" propagation="SUPPORTS" />
38             <tx:method name="get*" read-only="true" propagation="SUPPORTS" />
39             <tx:method name="find*" read-only="true" propagation="SUPPORTS" />
40             <tx:method name="count*" read-only="true" propagation="SUPPORTS" />
41 
42             <tx:method name="add*" read-only="false" propagation="REQUIRED" rollback-for="Throwable" />
43             <tx:method name="del*" read-only="false" propagation="REQUIRED" rollback-for="Throwable" />
44             <tx:method name="insert*" read-only="false" propagation="REQUIRED" rollback-for="Throwable" />
45             <tx:method name="update*" read-only="false" propagation="REQUIRED" rollback-for="Throwable" />
46             <tx:method name="delete*" read-only="false" propagation="REQUIRED" rollback-for="Throwable" />
47             <tx:method name="save*" read-only="false" propagation="REQUIRED" rollback-for="Throwable" />
48 
49             <tx:method name="newTran*" read-only="false" propagation="REQUIRES_NEW" rollback-for="Throwable" />
50             <tx:method name="xaTran*" read-only="false" propagation="REQUIRED" isolation="SERIALIZABLE" rollback-for="Throwable" />
51 
52             <tx:method name="*" propagation="REQUIRED" rollback-for="Throwable" />
53         </tx:attributes>
54     </tx:advice>
55         
56 </beans>

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

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <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"
 3     xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:jdbc="http://www.springframework.org/schema/jdbc"
 4     xmlns:context="http://www.springframework.org/schema/context" xmlns:cache="http://www.springframework.org/schema/cache"
 5     xsi:schemaLocation="
 6       http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
 7       http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
 8       http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd
 9       http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
10       http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
11       http://www.springframework.org/schema/cache http://www.springframework.org/schema/cache/spring-cache-3.2.xsd"
12     default-autowire="byName" default-lazy-init="false">
13     <!-- 加载配置属性文件 -->
14     <context:property-placeholder ignore-unresolvable="true" location="classpath:jdbc-dbcp.properties" />
15 
16     <!-- 数据库连接池 -->
17     <bean id="parentDataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close" abstract="true">
18         <property name="username" value="${dbcp.db.username}" />
19         <property name="password" value="${dbcp.db.password}" />
20         <property name="driverClassName" value="${dbcp.db.driverClassName}" />
21         <property name="connectionProperties" value="${dbcp.db.connectionProperties}" />
22 
23 
24         <!-- Connection Pooling Info -->
25         <!-- 连接池启动时创建的初始化连接数量 -->
26         <property name="initialSize" value="${dbcp.db.initialSize}" />
27         <!-- 连接池中可同时连接的最大的连接数（默认值为8，调整为20，高峰单机器在20并发左右，自己根据应用场景定） -->
28         <property name="maxTotal" value="${dbcp.db.maxTotal}" />
29         <!-- 连接池中最大的空闲的连接数，超过的空闲连接将被释放，如果设置为负数表示不限制（默认为8个，maxIdle不能设置 太小，因为假如在高负载的情况下，连接的打开时间比关闭的时间快，会引起连接池中idle的个数 上升超过maxIdle，而造成频繁的连接销毁>和创建，类似于jvm参数中的Xmx设置) -->
30         <property name="maxIdle" value="${dbcp.db.maxIdle}" />
31         <!-- 连接池中最小的空闲的连接数，低于这个数量会被创建新的连接（默认为0，调整为5，该参数越接近maxIdle，性能越>好，因为连接的创建和销毁，都是需要消耗资源的；但是不能太大，因为在机器很空闲的时候，也会创建低于minidle个数的连接，类似于jvm>参数中的Xmn设置） -->
32         <property name="minIdle" value="${dbcp.db.minIdle}" />
33         <!--最大等待时间，当没有可用连接时，连接池等待连接释放的最大时间，超过该时间限制会抛出异常，如果设置-1表示无>限等待（默认为无限，调整为60000ms，避免因线程池不够用，而导致请求被无限制挂起） -->
34         <property name="maxWaitMillis" value="${dbcp.db.maxWaitMillis}" />
35 
36 
37         <!-- Validate配置代码 -->
38         <property name="testOnCreate" value="${dbcp.db.testOnCreate}" />
39         <!-- GenericObjectPool中针对pool管理，起了一个Evict的TimerTask定时线程进行控制(可通过设置参数timeBetweenEvictionRunsMillis>0),定时对线程池中的链接进行validateObject校验，对无效的链接进行关闭后，会调用ensureMinIdle，适当建立链接保证最小的minIdle连接数。 -->
40         <property name="testWhileIdle" value="${dbcp.db.testWhileIdle}" />
41         <!-- 对拿到的connection进行validateObject校验 -->
42         <property name="testOnBorrow" value="${dbcp.db.testOnBorrow}" />
43         <property name="testOnReturn" value="${dbcp.db.testOnReturn}" />
44         
45         <!-- 设置的Evict线程的时间，单位ms，大于0才会开启evict检查线程 -->
46         <property name="timeBetweenEvictionRunsMillis" value="${dbcp.db.timeBetweenEvictionRunsMillis}" />
47         <!-- 代表每次检查链接的数量，建议设置和maxActive一样大，这样每次可以有效检查所有的链接. -->
48         <property name="numTestsPerEvictionRun" value="${dbcp.db.numTestsPerEvictionRun}" />
49         <property name="minEvictableIdleTimeMillis" value="${dbcp.db.minEvictableIdleTimeMillis}" />
50         <!-- 验证连接是否可用sql -->
51         <property name="validationQuery" value="${dbcp.db.validationQuery}" />
52 
53         <!--超过removeAbandonedTimeout时间后，是否进 行没用连接（废弃）的回收（默认为false，调整为true) -->
54         <property name="removeAbandonedOnMaintenance" value="${dbcp.db.removeAbandonedOnMaintenance}" />
55         <property name="removeAbandonedOnBorrow" value="${dbcp.db.removeAbandonedOnBorrow}" />
56         <!--超过时间限制，回收没有用(废弃)的连接（默认为 300秒，调整为180） -->
57         <property name="removeAbandonedTimeout" value="${dbcp.db.removeAbandonedTimeout}" />
58         <!-- 是否在自动回收超时连接的时候打印连接的超时错误 -->
59         <property name="logAbandoned" value="${dbcp.db.logAbandoned}" />        
60         
61     </bean>
62 
63     <!-- 数据库连接池 -->
64     <bean id="defaultDataSource" parent="parentDataSource">
65         <property name="url" value="${dbcp.db.url}" />
66     </bean>
67    
68     <bean id="dataSource" class="com.ruijie.crazy.core.db.DynamicDataSource">
69         <property name="targetDataSources">
70             <map key-type="java.lang.String">
71             </map>
72         </property>
73         <property name="defaultTargetDataSource" ref="defaultDataSource" />
74     </bean>
75         
76 </beans>

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

 1 dbcp.db.driverClassName=com.mysql.jdbc.Driver
 2 dbcp.db.url=jdbc:mysql://127.0.0.1:3306/test
 3 dbcp.db.username=root
 4 dbcp.db.password=admin
 5 
 6 dbcp.db.connectionProperties=useUnicode=true;characterEncoding=utf8;
 7 #Connection Pooling Info
 8 dbcp.db.initialSize=20
 9 dbcp.db.maxTotal=50
10 dbcp.db.maxIdle=50
11 dbcp.db.minIdle=20
12 dbcp.db.maxWaitMillis=90000
13 
14 dbcp.db.validationQuery=SELECT 1 FROM DUAL
15 
16 dbcp.db.testOnCreate=false
17 dbcp.db.testWhileIdle=true
18 dbcp.db.testOnBorrow=true
19 dbcp.db.testOnReturn=false
20 
21 dbcp.db.timeBetweenEvictionRunsMillis=60000
22 dbcp.db.numTestsPerEvictionRun=50
23 #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).
24 dbcp.db.minEvictableIdleTimeMillis=300000
25 
26 dbcp.db.removeAbandonedOnBorrow=true
27 dbcp.db.removeAbandonedOnMaintenance=true
28 dbcp.db.removeAbandonedTimeout=180
29 dbcp.db.logAbandoned=false

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

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
 3 <configuration>
 4     <!-- 基础设置 -->
 5     <settings>
 6         <!-- 全局映射器启用缓存，设置为false，由应用去管理缓存，mybatis专注于sql -->
 7         <setting name="cacheEnabled" value="false" />
 8         <!-- 查询时，关闭关联对象即时加载以提高性能 -->
 9         <setting name="lazyLoadingEnabled" value="true" />
10         <!-- 设置关联对象加载的形态，此处为按需加载字段(加载字段由SQL指 定)，不会加载关联表的所有字段，以提高性能 -->
11         <setting name="aggressiveLazyLoading" value="false" />
12         <!-- 对于未知的SQL查询，允许返回不同的结果集以达到通用的效果 -->
13         <setting name="multipleResultSetsEnabled" value="true" />
14         <!-- 允许使用列标签代替列名 -->
15         <setting name="useColumnLabel" value="true" />
16         <!-- 允许使用自定义的主键值(比如由程序生成的UUID 32位编码作为键值)，数据表的PK生成策略将被覆盖 -->
17         <setting name="useGeneratedKeys" value="false" />
18         <!-- 给予被嵌套的resultMap以字段-属性的映射支持 -->
19         <setting name="autoMappingBehavior" value="FULL" />
20         <!-- 对于批量更新操作缓存SQL以提高性能，如果返回行数有问题，可以修改该值为SIMPLE -->
21         <setting name="defaultExecutorType" value="REUSE" />
22         <!-- 数据库超过120秒仍未响应则超时 -->
23         <setting name="defaultStatementTimeout" value="120" />
24     </settings>
25 
26     <!-- SQL映射文件 -->
27     <mappers>
28         <!-- 通用SQL语句片段 -->
29         <mapper resource="mybatis/mapper/common_SqlMap.xml" />        
30         <mapper resource="mybatis/mapper/TProvinceMapper.xml" />        
31     </mappers>
32 </configuration>

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

 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="common" >
 4   <sql id="If_Example_Conditon_Valid" >
 5     <if test="condition.valid" >
 6       <trim prefix="(" suffix=")" prefixOverrides="and" >
 7         <foreach collection="condition.criterions" item="criterion" >
 8           <choose >
 9             <when test="criterion.noValue" >
10               and ${criterion.condition}
11             </when>
12             <when test="criterion.singleValue" >
13               and ${criterion.condition} #{criterion.value}
14             </when>
15             <when test="criterion.betweenValue" >
16               and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
17             </when>
18             <when test="criterion.listValue" >
19               and ${criterion.condition}
20               <foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
21                 #{listItem}
22               </foreach>
23             </when>
24             <when test="criterion.dateValue" >
25               and ${criterion.condition} #{criterion.value,jdbcType=TIMESTAMP}
26             </when>
27           </choose>
28         </foreach>
29       </trim>
30     </if>
31   </sql>
32   <sql id="Example_Where_Clause" >
33     <where >
34       <foreach collection="oredCriteria" item="condition" separator="or" >
35         <include refid="common.If_Example_Conditon_Valid" />
36       </foreach>
37     </where>
38   </sql>
39   <sql id="Update_By_Example_Where_Clause" >
40     <where >
41       <foreach collection="example.oredCriteria" item="condition" separator="or" >
42         <include refid="common.If_Example_Conditon_Valid" />
43       </foreach>
44     </where>
45   </sql>
46 </mapper>

TProvinceMapper.xml为与数据库中具体的表匹配的映射文件：

  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.ruijie.crazy.dao.persistence.TProvinceMapper" >
  4   <resultMap id="BaseResultMap" type="com.ruijie.crazy.entity.beans.TProvince" >
  5     <id column="id" property="id" jdbcType="INTEGER" />
  6     <result column="province" property="province" jdbcType="VARCHAR" />
  7   </resultMap>
  8   <sql id="Base_Column_List" >
  9     id, province
 10   </sql>
 11   <select id="selectByExample" resultMap="BaseResultMap" parameterType="com.ruijie.crazy.core.mybatis.Criteria" >
 12     select
 13     <if test="distinct" >
 14       distinct
 15     </if> 
 16     <include refid="Base_Column_List" />
 17     from t_province
 18     <if test="_parameter != null" >
 19       <include refid="common.Example_Where_Clause" />
 20     </if>
 21   </select>
 22   <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
 23     select 
 24     <include refid="Base_Column_List" />
 25     from t_province
 26     where id = #{id,jdbcType=INTEGER}
 27   </select>
 28   <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
 29     delete from t_province
 30     where id = #{id,jdbcType=INTEGER}
 31   </delete>
 32   <delete id="deleteByExample" parameterType="com.ruijie.crazy.core.mybatis.Criteria" >
 33     delete from t_province
 34     <if test="_parameter != null" >
 35       <include refid="common.Example_Where_Clause" />
 36     </if>
 37   </delete>
 38   <insert id="insert" parameterType="com.ruijie.crazy.entity.beans.TProvince" >
 39     <selectKey resultType="java.lang.Integer" keyProperty="id" order="BEFORE" >
 40       SELECT LAST_INSERT_ID()
 41     </selectKey>
 42     insert into t_province (id, province)
 43     values (#{id,jdbcType=INTEGER}, #{province,jdbcType=VARCHAR})
 44   </insert>
 45   <insert id="insertSelective" parameterType="com.ruijie.crazy.entity.beans.TProvince" >
 46     <selectKey resultType="java.lang.Integer" keyProperty="id" order="BEFORE" >
 47       SELECT LAST_INSERT_ID()
 48     </selectKey>
 49     insert into t_province
 50     <trim prefix="(" suffix=")" suffixOverrides="," >
 51       id,
 52       <if test="province != null" >
 53         province,
 54       </if>
 55     </trim>
 56     <trim prefix="values (" suffix=")" suffixOverrides="," >
 57       #{id,jdbcType=INTEGER},
 58       <if test="province != null" >
 59         #{province,jdbcType=VARCHAR},
 60       </if>
 61     </trim>
 62   </insert>
 63   <select id="countByExample" parameterType="com.ruijie.crazy.core.mybatis.Criteria" resultType="java.lang.Integer" >
 64     select count(*) from t_province
 65     <if test="_parameter != null" >
 66       <include refid="common.Example_Where_Clause" />
 67     </if>
 68   </select>
 69   <update id="updateByExampleSelective" parameterType="map" >
 70     update t_province
 71     <set >
 72       <if test="record.id != null" >
 73         id = #{record.id,jdbcType=INTEGER},
 74       </if>
 75       <if test="record.province != null" >
 76         province = #{record.province,jdbcType=VARCHAR},
 77       </if>
 78     </set>
 79     <if test="_parameter != null" >
 80       <include refid="common.Update_By_Example_Where_Clause" />
 81     </if>
 82   </update>
 83   <update id="updateByExample" parameterType="map" >
 84     update t_province
 85     set id = #{record.id,jdbcType=INTEGER},
 86       province = #{record.province,jdbcType=VARCHAR}
 87     <if test="_parameter != null" >
 88       <include refid="common.Update_By_Example_Where_Clause" />
 89     </if>
 90   </update>
 91   <update id="updateByPrimaryKeySelective" parameterType="com.ruijie.crazy.entity.beans.TProvince" >
 92     update t_province
 93     <set >
 94       <if test="province != null" >
 95         province = #{province,jdbcType=VARCHAR},
 96       </if>
 97     </set>
 98     where id = #{id,jdbcType=INTEGER}
 99   </update>
100   <update id="updateByPrimaryKey" parameterType="com.ruijie.crazy.entity.beans.TProvince" >
101     update t_province
102     set province = #{province,jdbcType=VARCHAR}
103     where id = #{id,jdbcType=INTEGER}
104   </update>
105     
106 </mapper>

3、java中代码

4、测试

Controller中写入如下测试代码：
 1 package com.ruijie.crazy.controller;
 2 
 3 import java.util.HashMap;
 4 import java.util.List;
 5 import java.util.Map;
 6 
 7 import javax.annotation.Resource;
 8 
 9 import org.springframework.stereotype.Controller;
10 import org.springframework.web.bind.annotation.RequestMapping;
11 import org.springframework.web.bind.annotation.RequestMethod;
12 import org.springframework.web.bind.annotation.ResponseBody;
13 import org.springframework.web.servlet.ModelAndView;
14 
15 import com.ruijie.crazy.dao.service.TProvinceDao;
16 import com.ruijie.crazy.entity.beans.TProvince;
17 
18 @Controller
19 @RequestMapping("/myweb")
20 public class MyFirstController {
21     @Resource
22     TProvinceDao tProvinceDao;
23         
24     @RequestMapping(value = "/test", method = RequestMethod.GET)
25     public ModelAndView getUserInfoByCode() {              
26         System.out.println("/myweb/test");
27         Map<String, Object> map = new HashMap<String, Object>();  
28         
29         List<TProvince> plist= tProvinceDao.getAll();
30         map.put("userName", "ypf: " + plist.get(0).getProvince());  
31         return new ModelAndView("hello",map);
32     }
33         
34 }

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

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/ypf1989/p/5546158.html