11、SpringBootmybatisplusdruid多源数据事务

系列导航

1、springboot工程新建（单模块）

2、springboot创建多模块工程

3、springboot连接数据库

4、SpringBoot连接数据库引入druid

5、SpringBoot连接数据库引入mybatis

6、SpringBoot-mybatis分页实现pagehelper

7、SpringBoot-mybatis-plus引入

8、SpringBoot 事务

9、SpringBoot-mybatis-druid多源数据多源数据

10、SpringBoot-mybatis-plus-druid多源数据

11、SpringBoot-mybatis-plus-druid多源数据事务

未完待续

      上一篇博客中因为多源项目的事务没有解决，本篇博客介绍一种可以在多源项目中让事务生效的方法，mybatis-plus配置多源数据还要使的事务生效，需要引入新的依赖atomikos

1数据库中创建表

zy数据库：

CREATE TABLE TEST_BLOCK_T
(
BLOCK_ID VARCHAR2(10 BYTE) PRIMARY KEY, --编码
BLOCK_NAME VARCHAR2(200 BYTE) --资源名称
);
Insert into TEST_BLOCK_T (BLOCK_ID, BLOCK_NAME) Values ('1', 'java');
COMMIT;

yc数据库：

CREATE TABLE TEST_USER_T
(
USER_ID VARCHAR2(10 BYTE) PRIMARY KEY,
NAME VARCHAR2(200 BYTE)
);
Insert into TEST_USER_T (USER_ID, NAME) Values ('1', '张三');
COMMIT;

 

2、pom.xml

1.8

UTF-8

UTF-8
2.1.17.RELEASE



org.springframework.boot
spring-boot-starter-web


org.springframework.boot
spring-boot-starter-test
test



com.alibaba
druid-spring-boot-starter
1.1.20



com.baomidou
mybatis-plus-boot-starter
3.3.1



org.springframework.boot
spring-boot-starter-jta-atomikos



com.oracle
ojdbc6
11.2.0.3



org.projectlombok
lombok
true

3、      application.properties配置

# 应用名称
spring.application.name=demo
# 应用服务 WEB 访问端口
server.port=8080
spring.datasource.type=com.alibaba.druid.pool.xa.DruidXADataSource
spring.datasource.druid.one.name=oneDataSource
spring.datasource.druid.one.url=jdbc:oracle:thin:@192.168.0.100:1521:orcl
spring.datasource.druid.one.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.druid.one.username=zy
spring.datasource.druid.one.password=1
spring.datasource.druid.one.initialSize=5
spring.datasource.druid.one.minIdle=5
spring.datasource.druid.one.maxActive=20
spring.datasource.druid.one.maxWait=60000
spring.datasource.druid.one.timeBetweenEvictiOnRunsMillis=60000
spring.datasource.druid.one.minEvictableIdleTimeMillis=300000
spring.datasource.druid.one.validatiOnQuery= SELECT 1 from dual
spring.datasource.druid.one.validatiOnQueryTimeout=10000
spring.datasource.druid.one.testWhileIdle=true
spring.datasource.druid.one.testOnBorrow=false
spring.datasource.druid.one.testOnReturn=false
spring.datasource.druid.one.poolPreparedStatements=true
spring.datasource.druid.one.maxPoolPreparedStatementPerCOnnectionSize=20
spring.datasource.druid.one.filters=stat,wall
spring.datasource.druid.one.cOnnectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
spring.datasource.druid.one.useGlobalDataSourceStat=true
spring.datasource.druid.two.name=twoDataSource
spring.datasource.druid.two.url=jdbc:oracle:thin:@192.168.0.100:1521:orcl
spring.datasource.druid.two.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.druid.two.username=yc
spring.datasource.druid.two.password=1
spring.datasource.druid.two.initialSize=5
spring.datasource.druid.two.minIdle=5
spring.datasource.druid.two.maxActive=20
spring.datasource.druid.two.maxWait=60000
spring.datasource.druid.two.timeBetweenEvictiOnRunsMillis=60000
spring.datasource.druid.two.minEvictableIdleTimeMillis=300000
spring.datasource.druid.two.validatiOnQuery=SELECT 1 from dual
spring.datasource.druid.two.validatiOnQueryTimeout=10000
spring.datasource.druid.two.testWhileIdle=true
spring.datasource.druid.two.testOnBorrow=false
spring.datasource.druid.two.testOnReturn=false
spring.datasource.druid.two.poolPreparedStatements=true
spring.datasource.druid.two.maxPoolPreparedStatementPerCOnnectionSize=20
spring.datasource.druid.two.filters=stat,wall
spring.datasource.druid.two.cOnnectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
spring.datasource.druid.two.useGlobalDataSourceStat=true
spring.jta.atomikos.properties.log-base-dir=tx-logs
spring.jta.transaction-manager-id=txManager

 

4、文件目录

 

 

5、源码

package com.example.demo;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class DemoApplication {
private org.springframework.dao.support.DaoSupport dao ;
public static void main(String[] args) {
SpringApplication.run(DemoApplication.class, args);
}
}

 

package com.example.demo.config;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import java.util.Properties;
/**
* 多数据源和Druid配置
*
* @author leilei
*/
@Configuration
public class DruidConfig {
/**
* 数据源1配置 使用AtomikosDataSourceBean 支持多数据源事务
*
* @param env
* @return Primary 指定主库 （必须指定一个主库 否则会报错）
*/
@Bean(name = "MybatisPlusOneDataSource")
@Primary
@Autowired
public AtomikosDataSourceBean oneDataSource(Environment env) {
AtomikosDataSourceBean ds = new AtomikosDataSourceBean();
Properties prop = build(env, "spring.datasource.druid.one.");
ds.setXaDataSourceClassName("com.alibaba.druid.pool.xa.DruidXADataSource");
ds.setUniqueResourceName("oneDataSource");
ds.setPoolSize(5);
ds.setXaProperties(prop);
return ds;
}
/**
* 数据源2配置 使用AtomikosDataSourceBean 支持多数据源事务
*
* @param env
* @return
*/
@Autowired
@Bean(name = "MybatisPlusTwoDataSource")
public AtomikosDataSourceBean twoDataSource(Environment env) {
AtomikosDataSourceBean ds = new AtomikosDataSourceBean();
Properties prop = build(env, "spring.datasource.druid.two.");
ds.setXaDataSourceClassName("com.alibaba.druid.pool.xa.DruidXADataSource");
ds.setUniqueResourceName("twoDataSource");
ds.setPoolSize(5);
ds.setXaProperties(prop);
return ds;
}
// @Autowired
// @Bean(name = "MybatisPlusThreeDataSource")
// public AtomikosDataSourceBean threeDataSource(Environment env) {
// AtomikosDataSourceBean ds = new AtomikosDataSourceBean();
// Properties prop = build(env, "spring.datasource.druid.three.");
// ds.setXaDataSourceClassName("com.alibaba.druid.pool.xa.DruidXADataSource");
// ds.setUniqueResourceName("threeDataSource");
// ds.setPoolSize(5);
// ds.setXaProperties(prop);
// return ds;
// }
// /**
// * 注入事物管理器
// * @return
// */
// @Bean(name = "leijta")
// public JtaTransactionManager regTransactionManager () {
// UserTransactionManager userTransactiOnManager= new UserTransactionManager();
// UserTransaction userTransaction = new UserTransactionImp();
// return new JtaTransactionManager(userTransaction, userTransactionManager);
// }
/**
* 从配置文件中加载数据源信息
*
* @param env
* @param prefix
* @return
*/
private Properties build(Environment env, String prefix) {
Properties prop = new Properties();
prop.put("url", env.getProperty(prefix + "url"));
prop.put("username", env.getProperty(prefix + "username"));
prop.put("password", env.getProperty(prefix + "password"));
prop.put("driverClassName", env.getProperty(prefix + "driverClassName", ""));
prop.put("initialSize", env.getProperty(prefix + "initialSize", Integer.class));
prop.put("maxActive", env.getProperty(prefix + "maxActive", Integer.class));
prop.put("minIdle", env.getProperty(prefix + "minIdle", Integer.class));
prop.put("maxWait", env.getProperty(prefix + "maxWait", Integer.class));
prop.put("poolPreparedStatements", env.getProperty(prefix + "poolPreparedStatements", Boolean.class));
prop.put("maxPoolPreparedStatementPerConnectionSize",
env.getProperty(prefix + "maxPoolPreparedStatementPerConnectionSize", Integer.class));
prop.put("maxPoolPreparedStatementPerConnectionSize",
env.getProperty(prefix + "maxPoolPreparedStatementPerConnectionSize", Integer.class));
prop.put("validationQuery", env.getProperty(prefix + "validationQuery"));
prop.put("validationQueryTimeout", env.getProperty(prefix + "validationQueryTimeout", Integer.class));
prop.put("testOnBorrow", env.getProperty(prefix + "testOnBorrow", Boolean.class));
prop.put("testOnReturn", env.getProperty(prefix + "testOnReturn", Boolean.class));
prop.put("testWhileIdle", env.getProperty(prefix + "testWhileIdle", Boolean.class));
prop.put("timeBetweenEvictionRunsMillis",
env.getProperty(prefix + "timeBetweenEvictionRunsMillis", Integer.class));
prop.put("minEvictableIdleTimeMillis", env.getProperty(prefix + "minEvictableIdleTimeMillis", Integer.class));
prop.put("filters", env.getProperty(prefix + "filters"));
return prop;
}
/**
* druid访问配置
*
* @return
*/
@Bean
public ServletRegistrationBean druidServlet() {
ServletRegistrationBean servletRegistratiOnBean= new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
//控制台管理用户，加入下面2行 进入druid后台就需要登录
servletRegistrationBean.addInitParameter("loginUsername", "leilei");
servletRegistrationBean.addInitParameter("loginPassword", "123456");
return servletRegistrationBean;
}
@Bean
public FilterRegistrationBean filterRegistrationBean() {
FilterRegistrationBean filterRegistratiOnBean= new FilterRegistrationBean();
filterRegistrationBean.setFilter(new WebStatFilter());
filterRegistrationBean.addUrlPatterns("/*");
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
filterRegistrationBean.addInitParameter("profileEnable", "true");
return filterRegistrationBean;
}
@Bean
public StatFilter statFilter() {
StatFilter statFilter = new StatFilter();
//slowSqlMillis用来配置SQL慢的标准，执行时间超过slowSqlMillis的就是慢。
statFilter.setLogSlowSql(true);
//SQL合并配置
statFilter.setMergeSql(true);
//slowSqlMillis的缺省值为3000，也就是3秒。
statFilter.setSlowSqlMillis(1000);
return statFilter;
}
@Bean
public WallFilter wallFilter() {
WallFilter wallFilter = new WallFilter();
//允许执行多条SQL
WallConfig cOnfig= new WallConfig();
config.setMultiStatementAllow(true);
wallFilter.setConfig(config);
return wallFilter;
}
}

 

package com.example.demo.config;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.RestControllerAdvice;
import javax.servlet.http.HttpServletRequest;
import java.util.HashMap;
import java.util.Map;
/**
* @author : leilei
* @date : 14:20 2020/3/5
* @desc : 自定义异常响应
*/
@RestControllerAdvice
public class ExceptionHadler {
@ExceptionHandler(value = Exception.class)
public Map exceptionHandler(HttpServletRequest req, Exception e) {
HashMap map = new HashMap<>(4);
map.put("请求状态", "False");
map.put("请求路径", req.getRequestURI());
map.put("请求方式", req.getMethod());
map.put("错误信息", e.getMessage());
return map;
}
}

配置数据源1

package com.example.demo.config;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.logging.stdout.StdOutImpl;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import javax.sql.DataSource;
/**
* @author leilei
*/
@Configuration
@MapperScan(basePackages = "com.example.demo.mapper.one", sqlSessiOnFactoryRef= "oneSqlSessionFactory")
public class OneDataSourceConfig {
@Primary
@Bean(name = "oneSqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("MybatisPlusOneDataSource") DataSource dataSource) throws Exception {
//配置myabtisSqlSession
MybatisSqlSessionFactoryBean sessiOnFactoryBean= new MybatisSqlSessionFactoryBean();
// 指明mapper.xml位置(配置文件中指明的xml位置会失效用此方式代替，具体原因未知)
//sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:com/leilei/mapper/one/*/*Mapper.xml"));
// 指明实体扫描(多个package用逗号或者分号分隔)
sessionFactoryBean.setTypeAliasesPackage("com.example.demo.entity.one");
MybatisConfiguration mybatisCOnfiguration= new MybatisConfiguration();
// mybatisConfiguration.setJdbcTypeForNull(JdbcType.NULL);
//驼峰
mybatisConfiguration.setMapUnderscoreToCamelCase(true);
//是否开启缓存
mybatisConfiguration.setCacheEnabled(false);
//多数据源下分页模式
mybatisConfiguration.addInterceptor(new PaginationInterceptor());
// 配置打印sql语句
mybatisConfiguration.setLogImpl(StdOutImpl.class);
sessionFactoryBean.setConfiguration(mybatisConfiguration);
//数据源注入
sessionFactoryBean.setDataSource(dataSource);
return sessionFactoryBean.getObject();
}
@Primary
@Bean(name = "oneSqlSessionTemplate")
public SqlSessionTemplate sqlSessionTemplate(@Qualifier("oneSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}

配置数据源2  需要几个就配置几个

package com.example.demo.config;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.logging.stdout.StdOutImpl;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import javax.sql.DataSource;
/**
* @author leilei
*/
@Configuration
@MapperScan(basePackages = "com.example.demo.mapper.two", sqlSessiOnFactoryRef= "twoSqlSessionFactory")
public class TwoDataSourceConfig {
@Bean(name = "twoSqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("MybatisPlusTwoDataSource") DataSource dataSource) throws Exception {
//配置myabtisSqlSession
MybatisSqlSessionFactoryBean sessiOnFactoryBean= new MybatisSqlSessionFactoryBean();
// 指明mapper.xml位置(配置文件中指明的xml位置会失效用此方式代替，具体原因未知)
//sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:com/leilei/mapper/two/*/*Mapper.xml"));
// 指明实体扫描(多个package用逗号或者分号分隔)
sessionFactoryBean.setTypeAliasesPackage("com.example.demo.entity.two");
MybatisConfiguration mybatisCOnfiguration= new MybatisConfiguration();
//mybatisConfiguration.setJdbcTypeForNull(JdbcType.NULL);
//驼峰
mybatisConfiguration.setMapUnderscoreToCamelCase(true);
//是否开启缓存
mybatisConfiguration.setCacheEnabled(false);
//多数据源下分页模式
mybatisConfiguration.addInterceptor(new PaginationInterceptor());
// 配置打印sql语句
mybatisConfiguration.setLogImpl(StdOutImpl.class);
sessionFactoryBean.setConfiguration(mybatisConfiguration);
//数据源注入
sessionFactoryBean.setDataSource(dataSource);
return sessionFactoryBean.getObject();
}
@Bean(name = "twoSqlSessionTemplate")
public SqlSessionTemplate sqlSessionTemplate(@Qualifier("twoSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}

 

package com.example.demo.controller;
import com.example.demo.service.ManySourceService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
@RestController
@RequestMapping("/hello")
public class ManySourceController {
@Autowired
ManySourceService manySourceService;
@GetMapping("/getZyBlock")
@ResponseBody
public String test1() {
return manySourceService.getZyBlock();
}
@GetMapping("/getYcUser")
@ResponseBody
public String test2() {
return manySourceService.getYcUser();
}
@PostMapping("/insertZyBlock")
@ResponseBody
public String test3() {
return manySourceService.insertZyBlock();
}
@PostMapping("/insertYcUser")
@ResponseBody
public String test4() {
return manySourceService.insertYcUser();
}
@PostMapping("/insertMany")
@ResponseBody
public String test5() {
return manySourceService.insertMany();
}
}

 

package com.example.demo.service;

import com.example.demo.entity.one.Block;
import com.example.demo.entity.two.User;
import com.example.demo.mapper.one.BlockMapper;
import com.example.demo.mapper.two.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@Service
public class ManySourceService {
//SpringBoot在使用事物Transactional的时候，要在main方法上加上 @EnableTransactionManagement
// 注解开发事物声明，在使用的service层的公共方法加上 @Transactional (spring)注解。
@Autowired
BlockMapper blockMapper;
@Autowired
UserMapper userMapper;
//获取zy库中的block中的数据
public String getZyBlock() {
return blockMapper.selectById("1").toString();
}
//获取yc库中的user中的数据
public String getYcUser() {
return userMapper.selectById("2").toString() ;
}
public String insertZyBlock() {
Block block = new Block();
block.setBlockId("99999");
block.setBlockName("PHP");
return blockMapper.insert(block)+"";
}
public String insertYcUser() {
User user = new User();
user.setUserId("2");
user.setName("李四");
return userMapper.insert(user)+"";
}
@Transactional
public String insertMany() {
Block block = new Block();
block.setBlockId("99999");
block.setBlockName("PHP");
blockMapper.insert(block) ;
int a = 100/0;
User user = new User();
user.setUserId("2");
user.setName("李四");
userMapper.insert(user) ;
return "1";
}
}

 

package com.example.demo.entity.one;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
/**
*

* 。
*

*
* @author yc
* @since 2021-09-18
*/
@TableName(value = "TEST_BLOCK_T")
public class Block {
private static final long serialVersiOnUID= 1L;
@TableId
private String blockId;
/**
* $field.comment。
*/
private String blockName;
public String getBlockId() {
return blockId;
}
public void setBlockId(String blockId) {
this.blockId = blockId;
}
public String getBlockName() {
return blockName;
}
public void setBlockName(String blockName) {
this.blockName = blockName;
}
@Override
public String toString() {
return "XyDicBlockT{" +
"blockId='" + blockId + '\'' +
", blockName='" + blockName + '\'' +
'}';
}
}

 

package com.example.demo.entity.two;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
/**
*

* 。
*

*
* @author yc
* @since 2021-09-18
*/
@TableName(value = "TEST_USER_T")
public class User {
private static final long serialVersiOnUID= 1L;
@TableId
private String userId;
/**
* $field.comment。
*/
private String name;
public String getUserId() {
return userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "User{" +
"userId='" + userId + '\'' +
", name='" + name + '\'' +
'}';
}
}

 

package com.example.demo.entity.two;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
/**
*

* 。
*

*
* @author yc
* @since 2021-09-18
*/
@TableName(value = "TEST_USER_T")
public class User {
private static final long serialVersiOnUID= 1L;
@TableId
private String userId;
/**
* $field.comment。
*/
private String name;
public String getUserId() {
return userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "User{" +
"userId='" + userId + '\'' +
", name='" + name + '\'' +
'}';
}
}

 

package com.example.demo.mapper.two;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demo.entity.two.User;
public interface UserMapper extends BaseMapper {
}

 

 

 

 

 

6、启动项目访问项目

 

清空yc库里的表TEST_USER_T 和 zy库里的表TEST_BLOCK_T

 

（1）访问http://localhost:8080/hello/insertZyBlock 成功插入数据到TEST_BLOCK_T

（2）访问http://localhost:8080/hello/insertYcUser成功插入数据到TEST_USER_T

说明分别向不同数据源的数据库的插入没有问题。

（3）访问http://localhost:8080/hello/getZyBlock

 

 

 

（4）访问http://localhost:8080/hello/getYcUser

 

 

 

说明分别从不同数据源的数据库的查询没有问题。

 

再次清空yc库里的表TEST_USER_T 和 zy库里的表TEST_BLOCK_T

重头戏来了

（5）访问http://localhost:8080/hello/insertMany

 这里添加了事务，并且在插入TEST_BLOCK_T后制造了一个除数为0的错误，如果事务生效，前面插入的数据就应该回滚。

下面可以看到前台返回的消息报错了说了除数为0的错误。

 

后台开始也插入了数据，但是会发现数据库里并没有插入数据，说清事务生效了。