目的:
项目中以前有整合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);
- }
- <!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 -->
- <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>
- <!-- querys -->
- <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);
- }
- <!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 -->
- <resultMap id="ResultMap" type="Teacher" >
- <id column="iTeacherId" property="iTeacherId" jdbcType="INTEGER" />
- <result column="sName" property="sName" jdbcType="VARCHAR" />
- </resultMap>
- <!-- querys -->
- <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>
- <!-- changes from the defaults for testing -->
- <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>
- <!-- changes from the defaults for testing -->
- <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" />
- <!-- 多个数据源的 DataSource名称不能相等 -->
- <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>
- <!-- 多个数据源的SqlSessionFactory名称不能相等 -->
- <bean id="studentSqlSessionFactory" name="studentSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
- <property name="dataSource" ref="studentDataSource" />
- <!-- 加载mybatis配置类 -->
- <property name="configLocation" value="resource/mybatis_student.xml" />
- </bean>
- <!-- ScanMapperFiles: mapper类要扫描的包;扫描mapper类 ,也不能同名 -->
- <bean name="studentMapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
- <!-- mapper类所在的包 -->
- <property name="basePackage" value="com.my.student.mapper"/>
- <property name="sqlSessionFactoryBeanName" value="studentSqlSessionFactory"/>
- </bean>
- <!-- ================================事务相关控制================================================= -->
- <!-- 开启通过注解实现事务,设置事务的名称 . 事务管理对象的名称也要不一样。系统默认事务管理对象名称为transactionManager -->
- <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">
- <!-- 多个数据源的 DataSource名称不能相等 -->
- <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>
- <!-- 多个数据源的SqlSessionFactory名称不能相等 -->
- <bean id="teacherSessionFactory" name="teacherSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
- <property name="dataSource" ref="teacherDataSource" />
- <!-- 加载mybatis的配置文件 -->
- <property name="configLocation" value="resource/mybatis_teacher.xml" />
- </bean>
- <!-- ScanMapperFiles 扫描mapper类 ,也不能同名-->
- <bean name="teacherMapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
- <!-- mapper类所在的包 -->
- <property name="basePackage" value="com.my.teacher.mapper"/>
- <property name="sqlSessionFactoryBeanName" value="teacherSessionFactory"/>
- </bean>
- <!-- ================================事务相关控制================================================= -->
- <!-- 开启通过注解实现事务,设置事务的名称 . 事务管理对象的名称也要不一样。系统默认事务管理对象名称为transactionManager -->
- <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个数据库,事务操作通过更新操作才能够更好演示出来,其实以上已经实现事务了,这里就省略步骤了。
以上内容源代码见附近
参考文献: