Spring 学习系列 -- Spring + Mybatis 从零开始配置多数据源访问

jiay · 发表于 2016-11-28 07:49:01

　　目的：
　　项目中以前有整合mybatis + spring操作数据库，但是以前都是单数据库，现需要实现mybatis访问多数据源，依旧使用spring调用mybatis。
　　通过注解的方式整合 spring + mybatis 多数据源，同时使两者能够执行事务操作
　　网上虽然也有类似的文章，但是或多或少有些问题。先将我的解决方法记录下来，以供参考。
　　步骤

导入相关的包

有2个数据库，分别是spring1 和 spring2

spring1 有表：teacher和数据

CREATE TABLE `teacher` (
`iTeacherId` int(11) NOT NULL AUTO_INCREMENT,
`sName` varchar(32) DEFAULT NULL,
PRIMARY KEY (`iTeacherId`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `teacher` VALUES (1, 'teacher1');
INSERT INTO `teacher` VALUES (2, 'teacher2');

　　spring2有表：

CREATE TABLE `student` (
`iStudentId` int(11) NOT NULL AUTO_INCREMENT,
`sName` varchar(32) DEFAULT NULL,
`iClass` int(11) DEFAULT NULL COMMENT '班级',
PRIMARY KEY (`iStudentId`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `student` VALUES (1, 'student1', 1);
INSERT INTO `student` VALUES (2, 'student2', 2);
INSERT INTO `student` VALUES (3, 'student3', 2);

功能：同时查询spring1.teacher和spring2.student 且支持事务操作

建立工程如下：

各个包说明：

com.my.teacher.pojo，com.my.student.pojo：和表结构一一对应的pojo类

Teacher类：

public class Teacher {
private Integer iTeacherId;
private String sName;
....
}

Student类：

public class Student {
private Integer iStudentId;
private String sName;
private Integer iClass;
...
}

com.my.student.mapper，com.my.teacher.mapper：mybatis的Mapper类，每个mapper都必须有一个同名的mapper.xml

StudentMapper类和对应的mapper.xml

public interface StudentMapper {
List<Student> querys(Map<String,Object> map);
}
<?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.my.student.mapper.StudentMapper" >

<resultMap id="ResultMap" type="Student" >
<id column="iStudentId" property="iStudentId" jdbcType="INTEGER" />
<result column="sName" property="sName" jdbcType="VARCHAR" />
<result column="iClass" property="iClass" jdbcType="INTEGER" />
</resultMap>

<select id="querys" resultMap="ResultMap" parameterType="map" >
select * from student
</select>
</mapper>

TeacherMapper类和对应的mapper.xml

public interface TeacherMapper {
List<Teacher> querys(Map<String,Object> map);
}
<?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.my.teacher.mapper.TeacherMapper" >

<resultMap id="ResultMap" type="Teacher" >
<id column="iTeacherId" property="iTeacherId" jdbcType="INTEGER" />
<result column="sName" property="sName" jdbcType="VARCHAR" />
</resultMap>

<select id="querys" resultMap="ResultMap" parameterType="map" >
select * from teacher
</select>
</mapper>

com.my.student.dao，com.my.student.dao.imp：调用mapper，逐个获取student、teacher记录

public interface Dao {
public void printAll();
public void printStudent();
public void printTeacher();
}

@Component
@Transactional
public class DaoImp implements Dao {
@Resource private StudentMapper studentMapper;
@Resource private TeacherMapper teacherMapper;

@Override
public void printAll() {
// TODO Auto-generated method stub
List<Student> l = this.studentMapper.querys(new HashMap<String,Object>());
for(Student o : l){
System.out.println(o);
}
List<Teacher> l2 = this.teacherMapper.querys(new HashMap<String,Object>());
for(Teacher o : l2){
System.out.println(o);
}
}
@Override
public void printStudent() {
// TODO Auto-generated method stub
List<Student> l = this.studentMapper.querys(new HashMap<String,Object>());
for(Student o : l){
System.out.println(o);
}
}
@Override
public void printTeacher() {

List<Teacher> l2 = this.teacherMapper.querys(new HashMap<String,Object>());
for(Teacher o : l2){
System.out.println(o);
}
}
}

com.my.student.dao.imp.test：测试dao类，代码略
resource：配置类，这里是重点介绍部分

main.properties：属性配置文件，把一些易变的属性配置到这个文件中，比配置到spring的配置文件中，要易于维护。

# ALL database are same
main.db.sIp=127.0.0.1
main.db.sPort=3306
main.db.sUsername=mysql
main.db.sPassword=password

mybatis_student.xml：spring2的mybatis配置xml

<configuration>
<settings>

<setting name="cacheEnabled" value="false" />
<setting name="useGeneratedKeys" value="true" />
<setting name="defaultExecutorType" value="REUSE" />
</settings>
<typeAliases>

<typeAlias type="com.my.student.pojo.Student" alias="Student"/>
</typeAliases>
</configuration>

mybatis_teacher.xml：spring1的mybatis配置xml 和 mybatis_student相似

<configuration>
<settings>

<setting name="cacheEnabled" value="false" />
<setting name="useGeneratedKeys" value="true" />
<setting name="defaultExecutorType" value="REUSE" />
</settings>
<typeAliases>

<typeAlias type="com.my.teacher.pojo.Teacher" alias="Teacher"/>
</typeAliases>
</configuration>

spring_student.xml：spring2的spring配置文件(具体说明见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:tx="http://www.springframework.org/schema/tx"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">

<context:annotation-config />

<context:component-scan base-package="
com.my.student.dao
"/>

<context:property-placeholder location="classpath:resource/main.properties" />

<bean id="studentDataSource" destroy-method="close"
class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://${main.db.sIp}:${main.db.sPort}/spring2" />
<property name="username" value="${main.db.sUsername}" />
<property name="password" value="${main.db.sPassword}" />
<property name="initialSize" value="20"/>
<property name="maxActive" value="200"/>
<property name="maxIdle" value="30"/>
<property name="maxWait" value="1000"/>
<property name="testOnBorrow" value="true"/>
<property name="testWhileIdle" value="true"/>
<property name="testOnReturn" value="true"/>
<property name="minEvictableIdleTimeMillis" value="300000" />
<property name="timeBetweenEvictionRunsMillis" value="120000" />
<property name="validationQuery" value="select 1 from dual" />
</bean>


<bean id="studentSqlSessionFactory" name="studentSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="studentDataSource" />

<property name="configLocation" value="resource/mybatis_student.xml" />
</bean>

<bean name="studentMapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">

<property name="basePackage" value="com.my.student.mapper"/>
<property name="sqlSessionFactoryBeanName" value="studentSqlSessionFactory"/>
</bean>



<tx:annotation-driven transaction-manager="studentTransactionManager"/>
<bean name="studentTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="studentDataSource"></property>
</bean>
</beans>

spring_teacher.xml：spring1的spring配置文件

<?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:tx="http://www.springframework.org/schema/tx"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">


<bean id="teacherDataSource" destroy-method="close"
class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://${main.db.sIp}:${main.db.sPort}/spring1" />
<property name="username" value="${main.db.sUsername}" />
<property name="password" value="${main.db.sPassword}" />
<property name="initialSize" value="20"/>
<property name="maxActive" value="200"/>
<property name="maxIdle" value="30"/>
<property name="maxWait" value="1000"/>
<property name="testOnBorrow" value="true"/>
<property name="testWhileIdle" value="true"/>
<property name="testOnReturn" value="true"/>
<property name="minEvictableIdleTimeMillis" value="300000" />
<property name="timeBetweenEvictionRunsMillis" value="120000" />
<property name="validationQuery" value="select 1 from dual" />
</bean>


<bean id="teacherSessionFactory"  name="teacherSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="teacherDataSource" />

<property name="configLocation" value="resource/mybatis_teacher.xml" />
</bean>

<bean  name="teacherMapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">

<property name="basePackage" value="com.my.teacher.mapper"/>
<property name="sqlSessionFactoryBeanName" value="teacherSessionFactory"/>
</bean>


<tx:annotation-driven  transaction-manager="teachaerTransactionManager" />
<bean name="teachaerTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="teacherDataSource"></property>
</bean>
</beans>

　　重点说明：

mybatis + spring 中，多数据库源的配置和单数据源配置和相似，最大的不同时，2个数据源的配置的名称不能相等。如上面配置spring配置xml中 DataSource、SqlSessionFactoryBean、MapperScannerConfigurer、DataSourceTransactionManager等在2个spring配置名称中都不相等。尤其是事务管理类DataSourceTransactionManager，必须通过以下方式开启并进行命名：<tx:annotation-driven transaction-manager="teachaerTransactionManager" />

　　同时部分只演示了同时操作2个数据库，事务操作通过更新操作才能够更好演示出来，其实以上已经实现事务了，这里就省略步骤了。
　　以上内容源代码见附近
　　参考文献：
　　

Spring下mybatis多数据源配置

账号		自动登录	找回密码
密码			立即注册

wirelessnetview好用的无线分析工具

亿图图示专家(EDraw Max) V7.9 中文破解版

zabbix3.4.1安装部署+微信推送信息+大屏显

Red Hat OpenShift I: Containers & Kubern

2025 年，C++ 还能“硬核”多久？

RH199 RHCSA Rapid Track

Red Hat RHCE 8 (EX294) Cert Guide

[经验分享] Spring 学习系列 -- Spring + Mybatis 从零开始配置多数据源访问

浏览过的版块

扫码加入运维网微信交流群