当前位置： 代码迷 >> 综合 >> springboot设置多数据源，使用 atomikos 管理多数据库事务
  详细解决方案

springboot设置多数据源，使用 atomikos 管理多数据库事务

热度：55   发布时间：2024-03-08 03:52:47.0

springboot设置多数据源

本文使用springboot框架，设置两个数据源（mysql，oracle）。

1.项目结构

2.数据库表结构

两个数据库的结构一样

mysql：

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`  (`id` int(11) NOT NULL,`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,`remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;SET FOREIGN_KEY_CHECKS = 1;INSERT INTO `aaa`.`emp`(`id`, `name`, `remark`) VALUES (1, 'mysql', '我从mysql来');

oracle：

create table EMP
(id     NUMBER not null,name   VARCHAR2(50),remark VARCHAR2(50)
);insert into emp (ID, NAME, REMARK)
values (1, 'oracle', '我来自oracle');

3.application.yml

server:port: 8888mydatasource:first:driverClassName: com.mysql.jdbc.Driverjdbc-url: jdbc:mysql://127.0.0.1:3306/aaausername: rootpassword: yuwensecond:driverClassName: oracle.jdbc.driver.OracleDriverjdbc-url: jdbc:oracle:thin:@127.0.0.1:1521:orclusername: scottpassword: yuwen

 4.pom文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.2.1.RELEASE</version><relativePath/> <!-- lookup parent from repository --></parent><groupId>com.example</groupId><artifactId>demo-dataSource</artifactId><version>0.0.1-SNAPSHOT</version><name>demo-dataSource</name><properties><java.version>1.8</java.version></properties><dependencies><!--ojdbc--><dependency><groupId>com.oracle</groupId><artifactId>ojdbc6</artifactId><version>11.2.0.3</version></dependency><!--mysql--><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.46</version></dependency><!--druid连接池--><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.0.29</version></dependency><!--mybatis--><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>1.3.2</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope><exclusions><exclusion><groupId>org.junit.vintage</groupId><artifactId>junit-vintage-engine</artifactId></exclusion></exclusions></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId><configuration><jvmArguments>-Xdebug -Xrunjdwp:transport=dt_socket,server=y,suspend=n,address=9999</jvmArguments></configuration></plugin></plugins></build></project>

5.自定义数据源的配置类

MysqlConfig：定义了mysql数据库的相关配置

package com.example.demo.config;import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;import javax.sql.DataSource;/*** className: MysqlConfig <br/>* packageName:com.example.demo.config <br/>* description:  <br/>** @date: 2020-10-20 20:48 <br/>*/@Configuration
@MapperScan(basePackages = "com.example.demo.dao.mysql", sqlSessionTemplateRef = "firstSqlSessionTemplate")
public class MysqlConfig {@Bean("firstDataSource")@ConfigurationProperties(prefix = "mydatasource.first")public DataSource firstDataSource() {return DataSourceBuilder.create().build();}@Bean("firstSqlSessionFactory")public SqlSessionFactory firstSqlSessionFactory(@Qualifier("firstDataSource") DataSource dataSource) throws Exception {SqlSessionFactoryBean bean = new SqlSessionFactoryBean();bean.setDataSource(dataSource);// mapper的xml形式文件位置必须要配置，不然将报错：no statement （这种错误也可能是mapper的xml中，namespace与项目的路径不一致导致）bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/mysql/*.xml"));return bean.getObject();}@Bean("firstSqlSessionTemplate")public SqlSessionTemplate firstSqlSessionTemplate(@Qualifier("firstSqlSessionFactory") SqlSessionFactory factory) {return new SqlSessionTemplate(factory);}@Bean("firstDataSourceTransactionManager")public DataSourceTransactionManager firstDataSourceTransactionManager(@Qualifier("firstDataSource") DataSource dataSource) {return new DataSourceTransactionManager(dataSource);}}

OracleConfig：定义了oracle数据库的相关配置

package com.example.demo.config;import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;import javax.sql.DataSource;/*** className: OracleConfig <br/>* packageName:com.example.demo.config <br/>* description:  <br/>** @date: 2020-10-20 20:48 <br/>*/@Configuration
@MapperScan(basePackages = "com.example.demo.dao.oracle", sqlSessionTemplateRef = "secondSqlSessionTemplate")
public class OracleConfig {@Value("${mydatasource.second.driverClassName}")private String driverClassName;@Value("${mydatasource.second.jdbc-url}")private String jdbcUrl;@Value("${mydatasource.second.username}")private String username;@Value("${mydatasource.second.password}")private String password;@Bean("secondDataSource")public DataSource secondDataSource() {DruidDataSource dataSource = new DruidDataSource();dataSource.setUsername(username);dataSource.setPassword(password);dataSource.setUrl(jdbcUrl);dataSource.setDriverClassName(driverClassName);return dataSource;}@Bean("secondSqlSessionFactory")public SqlSessionFactory secondSqlSessionFactory(@Qualifier("secondDataSource") DataSource dataSource) throws Exception {SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();factoryBean.setDataSource(dataSource);// mapper的xml形式文件位置必须要配置，不然将报错：no statement （这种错误也可能是mapper的xml中，namespace与项目的路径不一致导致）factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/oracle/*.xml"));return factoryBean.getObject();}@Bean("secondSqlSessionTemplate")public SqlSessionTemplate secondSqlSessionTemplate(@Qualifier("secondSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {return new SqlSessionTemplate(sqlSessionFactory);}@Bean("secondDataSourceTransactionManager")public DataSourceTransactionManager secondDataSourceTransactionManager(@Qualifier("secondDataSource") DataSource dataSource) {return new DataSourceTransactionManager(dataSource);}}

6.实体类

package com.example.demo.entity;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;/*** className: Emp <br/>* packageName:com.example.demo.entity <br/>* description:  <br/>** @date: 2020-10-20 20:31 <br/>*/
@AllArgsConstructor
@NoArgsConstructor
@Data
public class Emp {private int id;private String name;private String remark;}

7.dao层

MysqlEmpDao
package com.example.demo.dao.mysql;import com.example.demo.entity.Emp;
import org.springframework.stereotype.Repository;import java.util.List;/*** className: EmpDao <br/>* packageName:com.example.demo.dao.mysql <br/>* description:  <br/>** @date: 2020-10-20 20:34 <br/>*/@Repository
public interface MysqlEmpDao {List<Emp> findInMysql();
}

 OracleEmpdao

package com.example.demo.dao.oracle;import com.example.demo.entity.Emp;
import org.springframework.stereotype.Repository;import java.util.List;/*** className: Empdao <br/>* packageName:com.example.demo.dao.oracle <br/>* description:  <br/>** @date: 2020-10-20 20:35 <br/>*/@Repository
public interface OracleEmpdao {List<Emp> findInOracle();
}

 mysql的dao对应的xml

<?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" >
<!-- 映射文件，映射到对应的SQL接口 -->
<mapper namespace="com.example.demo.dao.mysql.MysqlEmpDao"><select id="findInMysql" resultType="com.example.demo.entity.Emp">SELECT * FROM emp</select></mapper>

oracle的dao对应的xmk

<?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" >
<!-- 映射文件，映射到对应的SQL接口 -->
<mapper namespace="com.example.demo.dao.oracle.OracleEmpdao"><select id="findInOracle" resultType="com.example.demo.entity.Emp">SELECT * FROM emp</select></mapper>

8.service层

接口

package com.example.demo.service;import com.example.demo.entity.Emp;import java.util.List;/*** className: EmpService <br/>* packageName:com.example.demo.service <br/>* description:  <br/>** @date: 2020-10-20 20:32 <br/>*/
public interface EmpService {/*** 查询mysql** @return*/List<Emp> findInMysql();/*** 查询oracle** @return*/List<Emp> findInOracle();
}

实现类 

package com.example.demo.service.impl;import com.example.demo.dao.mysql.MysqlEmpDao;
import com.example.demo.dao.oracle.OracleEmpdao;
import com.example.demo.entity.Emp;
import com.example.demo.service.EmpService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.util.List;/*** className: EmpServiceImpl <br/>* packageName:com.example.demo.service.impl <br/>* description:  <br/>** @date: 2020-10-20 20:32 <br/>*/@Service
@Slf4j
public class EmpServiceImpl implements EmpService {@Autowiredprivate MysqlEmpDao mysqlEmpDao;@Autowiredprivate OracleEmpdao oracleEmpdao;@Overridepublic List<Emp> findInMysql() {return mysqlEmpDao.findInMysql();}@Overridepublic List<Emp> findInOracle() {return oracleEmpdao.findInOracle();}
}

 9.controller层

package com.example.demo.controller;import com.example.demo.entity.Emp;
import com.example.demo.service.EmpService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;import java.util.HashMap;
import java.util.List;
import java.util.Map;/*** className: TestController <br/>* packageName:com.example.demo.controller <br/>* description:  <br/>** @author yuwen <br/>* @date: 2020-6-30 21:24 <br/>*/@RestController
@Slf4j
public class TestController {@Autowiredprivate EmpService empService;@GetMapping("/findInMysql")public List<Emp> findInMysql() {return empService.findInMysql();}@GetMapping("/findInOracle")public List<Emp> findInOracle() {return empService.findInOracle();}
}

10.测试

 启动项目，

访问  http://localhost:8888/findInMysql   出现   [{"id":1,"name":"mysql","remark":"我从mysql来"}]

访问  http://localhost:8888/findInOracle  出现   [{"id":1,"name":"oracle","remark":"我从oracle来"}]

 

至此，整合多数据源成功。

 

使用 atomikos 管理多数据库事务

多数据源可以正常使用后，接下来该思考事务问题。

针对单个数据源，在service层的方法上加上 @Transactional ，设置 transactionManager 属性后（对应数据源的事务管理器），即可进行事务控制。

可是如果事务跨库了呢，例如在 mysql 和 oracle 分别插入一条数据，在执行 mysql 的新增成功了，执行 oracle 的新增失败了，那么 mysql的事务会回滚吗？答案是不可以

怎么才能实现跨数据库的事务呢？

百度得知使用 atomikos  可以进行多数据库的事务管理，以下为调研代码。

1.修改MysqlConfig

package com.example.demo.config;import com.mysql.jdbc.jdbc2.optional.MysqlXADataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;import javax.sql.DataSource;/*** className: MysqlConfig <br/>* packageName:com.example.demo.config <br/>* description:  <br/>** @date: 2020-10-20 20:48 <br/>*/@Configuration
@MapperScan(basePackages = "com.example.demo.dao.mysql", sqlSessionTemplateRef = "firstSqlSessionTemplate")
public class MysqlConfig {@Value("${mydatasource.first.jdbc-url}")private String jdbcUrl;@Value("${mydatasource.first.username}")private String username;@Value("${mydatasource.first.password}")private String password;@Bean("firstDataSource")public DataSource firstDataSource() {// 创建MYsql实现XA规范的分布式数据源MysqlXADataSource mysqlXaDataSource = new MysqlXADataSource();// 设置连接信息mysqlXaDataSource.setUrl(jdbcUrl);mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);mysqlXaDataSource.setPassword(password);mysqlXaDataSource.setUser(username);mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);// 数据源改为Atomikos，将事务交给Atomikos统一管理AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();xaDataSource.setXaDataSource(mysqlXaDataSource);xaDataSource.setUniqueResourceName("firstDataSource");return xaDataSource;}@Bean("firstSqlSessionFactory")public SqlSessionFactory firstSqlSessionFactory(@Qualifier("firstDataSource") DataSource dataSource) throws Exception {SqlSessionFactoryBean bean = new SqlSessionFactoryBean();bean.setDataSource(dataSource);// mapper的xml形式文件位置必须要配置，不然将报错：no statement （这种错误也可能是mapper的xml中，namespace与项目的路径不一致导致）bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/mysql/*.xml"));return bean.getObject();}@Bean("firstSqlSessionTemplate")public SqlSessionTemplate firstSqlSessionTemplate(@Qualifier("firstSqlSessionFactory") SqlSessionFactory factory) {return new SqlSessionTemplate(factory);}
//
//    @Bean("firstDataSourceTransactionManager")
//    public DataSourceTransactionManager firstDataSourceTransactionManager(@Qualifier("firstDataSource") DataSource dataSource) {
//        return new DataSourceTransactionManager(dataSource);
//    }}

2.修改OracleConfig

package com.example.demo.config;import oracle.jdbc.xa.client.OracleXADataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;import javax.sql.DataSource;
import java.sql.SQLException;/*** className: OracleConfig <br/>* packageName:com.example.demo.config <br/>* description:  <br/>** @date: 2020-10-20 20:48 <br/>*/@Configuration
@MapperScan(basePackages = "com.example.demo.dao.oracle", sqlSessionTemplateRef = "secondSqlSessionTemplate")
public class OracleConfig {@Value("${mydatasource.second.jdbc-url}")private String jdbcUrl;@Value("${mydatasource.second.username}")private String username;@Value("${mydatasource.second.password}")private String password;@Bean("secondDataSource")public DataSource secondDataSource() throws SQLException {OracleXADataSource mysqlXaDataSource = new OracleXADataSource();mysqlXaDataSource.setURL(jdbcUrl);mysqlXaDataSource.setPassword(password);mysqlXaDataSource.setUser(username);AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();xaDataSource.setXaDataSource(mysqlXaDataSource);xaDataSource.setUniqueResourceName("secondDataSource");return xaDataSource;}@Bean("secondSqlSessionFactory")public SqlSessionFactory secondSqlSessionFactory(@Qualifier("secondDataSource") DataSource dataSource) throws Exception {SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();factoryBean.setDataSource(dataSource);// mapper的xml形式文件位置必须要配置，不然将报错：no statement （这种错误也可能是mapper的xml中，namespace与项目的路径不一致导致）factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/oracle/*.xml"));return factoryBean.getObject();}@Bean("secondSqlSessionTemplate")public SqlSessionTemplate secondSqlSessionTemplate(@Qualifier("secondSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {return new SqlSessionTemplate(sqlSessionFactory);}//    @Bean("secondDataSourceTransactionManager")
//    public DataSourceTransactionManager secondDataSourceTransactionManager(@Qualifier("secondDataSource") DataSource dataSource) {
//        return new DataSourceTransactionManager(dataSource);
//    }}

3.添加测试方法

TestController：

/*** 单个库事务(mysql)** @return*/@GetMapping("/mysqlTrans")public Map<String, String> mysqlTrans() {Map<String, String> ret = new HashMap<>();try {empService.mysqlTrans();ret.put("stats", "true");} catch (Exception e) {log.error("TestController mysqlTrans failed", e);ret.put("stats", "false");}return ret;}/*** 单个库事务(oracle)** @return*/@GetMapping("/oracleTrans")public Map<String, String> oracleTrans() {Map<String, String> ret = new HashMap<>();try {empService.oracleTrans();ret.put("stats", "true");} catch (Exception e) {log.error("TestController oracleTrans failed", e);ret.put("stats", "false");}return ret;}/*** 两个库事务** @return*/@GetMapping("/twoTrans")public Map<String, String> twoTrans() {Map<String, String> ret = new HashMap<>();try {empService.twoTrans();ret.put("stats", "true");} catch (Exception e) {log.error("TestController twoTrans failed", e);ret.put("stats", "false");}return ret;}

service层：

 void oracleTrans();void mysqlTrans();void twoTrans();

 

@Override@Transactional(rollbackFor = Exception.class)public void mysqlTrans() {int id = (int) (Math.random() * 100);Emp emp = new Emp(id, "mysql", "yes");mysqlEmpDao.insert(emp);log.info("EmpServiceImpl mysqlTrans insert success");System.out.println(1 / 0); // 发生异常int id2 = (int) (Math.random() * 100);Emp emp2 = new Emp(id2, "mysql", "yes");mysqlEmpDao.insert(emp2);log.info("EmpServiceImpl mysqlTrans insert success");}@Override@Transactional(rollbackFor = Exception.class)public void oracleTrans() {int id = (int) (Math.random() * 100);Emp emp = new Emp(id, "oracle", "yes");oracleEmpdao.insert(emp);log.info("EmpServiceImpl oracleTrans insert success");System.out.println(1 / 0); // 发生异常int id2 = (int) (Math.random() * 100);Emp emp2 = new Emp(id2, "oracle", "yes");oracleEmpdao.insert(emp2);log.info("EmpServiceImpl oracleTrans insert success");}@Override@Transactional(rollbackFor = Exception.class)public void twoTrans() {int id = (int) (Math.random() * 100);Emp emp = new Emp(id, "oracle", "yes");mysqlEmpDao.insert(emp);log.info("EmpServiceImpl twoTrans insert success");int id2 = (int) (Math.random() * 100);Emp emp2 = new Emp(id2, "oracle", "yes");oracleEmpdao.insert(emp2);log.info("EmpServiceImpl twoTrans insert success");System.out.println(1 / 0); // 发生异常}

4.测试

启动项目，如果报错：javax.transaction.xa.XAException: null，可参考：https://blog.csdn.net/qq_43601813/article/details/107248968

启动成功后，分别访问

http://localhost:8888/mysqlTrans

http://localhost:8888/oracleTrans

http://localhost:8888/twoTrans

然后访问

http://localhost:8888/findInMysql

http://localhost:8888/findInOracle

分析页面结果，结果符合预期，atomikos 可以实现多数据库事务

 

  相关解决方案

代码迷 - 您身边的软件异常，代码异常，编程异常助手（发现，解决，分享）
Copyright © 2009-2013 DAIMAMI.COM. All rights reserved.