目录
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: 8888
mydatasource:
first:
driverClassName: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/aaa
username: root
password: yuwen
second:
driverClassName: oracle.jdbc.driver.OracleDriver
jdbc-url: jdbc:oracle:thin:@127.0.0.1:1521:orcl
username: scott
password: yuwen4.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层
MysqlEmpDaopackage 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 {
@Autowired
private MysqlEmpDao mysqlEmpDao;
@Autowired
private OracleEmpdao oracleEmpdao;
@Override
public List<Emp> findInMysql() {
return mysqlEmpDao.findInMysql();
}
@Override
public 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 {
@Autowired
private 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.添加依赖
<!--atomikos-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jta-atomikos</artifactId>
</dependency>2.修改MysqlConfig和OracleConfig
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);
// }
}
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
使用 dba账号 通过以下命令给数据源的用户授予XA权限
grant select on sys.dba_pending_transactions to USER_NAME;
grant select on sys.pending_trans$ to USER_NAME;
grant select on sys.dba_2pc_pending to USER_NAME;
grant execute on sys.dbms_system to USER_NAME
参考：https://blog.csdn.net/qq_43601813/article/details/107248968
启动成功后，通过以下地址进行测试
http://localhost:8888/mysqlTrans
http://localhost:8888/oracleTrans
http://localhost:8888/twoTrans
分析页面结果，结果符合预期，atomikos 可以实现多数据库事务
更多spring事务相关
http://codin.im/2017/06/07/distributed-transactions-in-spring--with-and-without-xa-translate/
457
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
