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，显示结果如下：
