一、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,显示结果如下: