SpringBoot默认是集成事务的,只要在方法上加上@Transactional既可,
但某些项目用到了多个数据库,也就代表有多个数据源。此时需要用到分布式事务。
多数据源
此时有两个数据库:
数据库1:
Databases:spring
Table:enjoy_user
CREATE TABLE `enjoy_user` (
`id` int NOT NULL AUTO_INCREMENT ,
`passwd` varchar(255) NULL ,
`username` varchar(255) NULL ,
PRIMARY KEY (`id`)
);
数据库2:
Databases:spring2
Table:enjoy_order
CREATE TABLE `enjoy_order` (
`id` int(10) NOT NULL AUTO_INCREMENT ,
`name` varchar(255) ,
`user_id` int(11) NOT NULL ,
`account` int(255) NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
pom文件
新建一个项目,在pom文件里面增加mybatis等依赖信息
<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId> enjoy</groupId>
<artifactId>springbootatomikos</artifactId>
<version>1.0-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.2.RELEASE</version>
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
</properties>
<dependencies>
<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>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
</dependencies>
</project>
3.5.1.3. 新增model(Orders,Users)
package cn.enjoy.model;
public class Users {
private Integer id;
private String passwd;
private String username;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getPasswd() {
return passwd;
}
public void setPasswd(String passwd) {
this.passwd = passwd == null ? null : passwd.trim();
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username == null ? null : username.trim();
}
}
package cn.enjoy.model;
public class Orders {
private Integer id;
private String name;
private Integer userId;
private Integer account;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name == null ? null : name.trim();
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public Integer getAccount() {
return account;
}
public void setAccount(Integer account) {
this.account = account;
}
}
新增mapper接口
注意:由于这两mapper接口对应不同的数据库,为了配置与管理方便,不同的数据库的mapper接口单独放置在一个package中
package cn.enjoy.dao.orders;
import cn.enjoy.model.Orders;
public interface OrdersMapper {
int deleteByPrimaryKey(Integer id);
int insert(Orders record);
int insertSelective(Orders record);
Orders selectByPrimaryKey(Integer id);
int updateByPrimaryKeySelective(Orders record);
int updateByPrimaryKey(Orders record);
}
package cn.enjoy.dao.users;
import cn.enjoy.model.Users;
import org.apache.ibatis.annotations.Param;
public interface UsersMapper {
int deleteByPrimaryKey(Integer id);
int insert(Users record);
int insertSelective(Users record);
Users selectByPrimaryKey(Integer id);
int updateByPrimaryKeySelective(Users record);
int updateByPrimaryKey(Users record);
}
Mapper的XML配置
为了管理方便,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" >
<mapper namespace="cn.enjoy.dao.users.UsersMapper" >
<resultMap id="BaseResultMap" type="cn.enjoy.model.Users" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="passwd" property="passwd" jdbcType="VARCHAR" />
<result column="username" property="username" jdbcType="VARCHAR" />
</resultMap>
<sql id="Base_Column_List" >
id, passwd, username
</sql>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
select
<include refid="Base_Column_List" />
from enjoy_user
where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
delete from enjoy_user
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="cn.enjoy.model.Users" >
insert into enjoy_user (id, passwd, username
)
values (#{id,jdbcType=INTEGER}, #{passwd,jdbcType=VARCHAR}, #{username,jdbcType=VARCHAR}
)
</insert>
<insert id="insertSelective" parameterType="cn.enjoy.model.Users" >
insert into enjoy_user
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="id != null" >
id,
</if>
<if test="passwd != null" >
passwd,
</if>
<if test="username != null" >
username,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="id != null" >
#{id,jdbcType=INTEGER},
</if>
<if test="passwd != null" >
#{passwd,jdbcType=VARCHAR},
</if>
<if test="username != null" >
#{username,jdbcType=VARCHAR},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="cn.enjoy.model.Users" >
update enjoy_user
<set >
<if test="passwd != null" >
passwd = #{passwd,jdbcType=VARCHAR},
</if>
<if test="username != null" >
username = #{username,jdbcType=VARCHAR},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="cn.enjoy.model.Users" >
update enjoy_user
set passwd = #{passwd,jdbcType=VARCHAR},
username = #{username,jdbcType=VARCHAR}
where id = #{id,jdbcType=INTEGER}
</update>
</mapper>
<?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="cn.enjoy.dao.orders.OrdersMapper" >
<resultMap id="BaseResultMap" type="cn.enjoy.model.Orders" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="user_id" property="userId" jdbcType="INTEGER" />
<result column="account" property="account" jdbcType="INTEGER" />
</resultMap>
<sql id="Base_Column_List" >
id, name, user_id, account
</sql>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
select
<include refid="Base_Column_List" />
from enjoy_order
where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
delete from enjoy_order
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="cn.enjoy.model.Orders" >
insert into enjoy_order (id, name, user_id,
account)
values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{userId,jdbcType=INTEGER},
#{account,jdbcType=INTEGER})
</insert>
<insert id="insertSelective" parameterType="cn.enjoy.model.Orders" >
insert into enjoy_order
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="id != null" >
id,
</if>
<if test="name != null" >
name,
</if>
<if test="userId != null" >
user_id,
</if>
<if test="account != null" >
account,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="id != null" >
#{id,jdbcType=INTEGER},
</if>
<if test="name != null" >
#{name,jdbcType=VARCHAR},
</if>
<if test="userId != null" >
#{userId,jdbcType=INTEGER},
</if>
<if test="account != null" >
#{account,jdbcType=INTEGER},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="cn.enjoy.model.Orders" >
update enjoy_order
<set >
<if test="name != null" >
name = #{name,jdbcType=VARCHAR},
</if>
<if test="userId != null" >
user_id = #{userId,jdbcType=INTEGER},
</if>
<if test="account != null" >
account = #{account,jdbcType=INTEGER},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="cn.enjoy.model.Orders" >
update enjoy_order
set name = #{name,jdbcType=VARCHAR},
user_id = #{userId,jdbcType=INTEGER},
account = #{account,jdbcType=INTEGER}
where id = #{id,jdbcType=INTEGER}
</update>
</mapper>
新建service接口与实现类
package cn.enjoy.service;
import cn.enjoy.model.Orders;
import cn.enjoy.model.Users;
public interface IOrderService {
void addOrder(Orders orders, Users users);
}
实现类
package cn.enjoy.service.impl;
import cn.enjoy.dao.orders.OrdersMapper;
import cn.enjoy.dao.users.UsersMapper;
import cn.enjoy.model.Orders;
import cn.enjoy.model.Users;
import cn.enjoy.service.IOrderService;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
@Service
public class OrderServiceImpl implements IOrderService{
@Resource
private UsersMapper usersMapper;
@Resource
private OrdersMapper ordersMapper;
@Override
public void addOrder(Orders orders, Users users) {
usersMapper.insertSelective(users);
ordersMapper.insertSelective(orders);
}
}
上面只是准备工作,目前未知没有配置与数据源相关的任何配置
application.properties
新增application.properties,里面配置数据源相关信息
分别配置了两个数据库
spring.datasource.spring.driverClassName=com.mysql.jdbc.Driver
spring.datasource.spring.jdbcUrl=jdbc:mysql://127.0.0.1:3306/spring?serverTimezone=GMT%2B8
spring.datasource.spring.username=root
spring.datasource.spring.password=root1234%
spring.datasource.spring2.driverClassName=com.mysql.jdbc.Driver
spring.datasource.spring2.jdbcUrl=jdbc:mysql://127.0.0.1:3306/spring2?serverTimezone=GMT%2B8
spring.datasource.spring2.username=root
spring.datasource.spring2.password=root1234%
数据源配置类
package cn.enjoy.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.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "cn.enjoy.dao.users", sqlSessionFactoryRef = "test1SqlSessionFactory")
public class DataSource1Config {
@Bean(name = "test1DataSource")
@ConfigurationProperties(prefix = "spring.datasource.spring")
@Primary
public DataSource testDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "test1SqlSessionFactory")
@Primary
public SqlSessionFactory testSqlSessionFactory(@Qualifier("test1DataSource") DataSource dataSource)
throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapping/users/*.xml"));
return bean.getObject();
}
@Bean(name = "test1TransactionManager")
@Primary
public DataSourceTransactionManager testTransactionManager(@Qualifier("test1DataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "test1SqlSessionTemplate")
@Primary
public SqlSessionTemplate testSqlSessionTemplate(
@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
package cn.enjoy.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.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "cn.enjoy.dao.orders", sqlSessionFactoryRef = "test2SqlSessionFactory")
public class DataSource2Config {
@Bean(name = "test2DataSource")
@ConfigurationProperties(prefix = "spring.datasource.spring2")
public DataSource testDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "test2SqlSessionFactory")
public SqlSessionFactory testSqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource)
throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapping/orders/*.xml"));
return bean.getObject();
}
@Bean(name = "test2TransactionManager")
public DataSourceTransactionManager testTransactionManager(@Qualifier("test2DataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "test2SqlSessionTemplate")
public SqlSessionTemplate testSqlSessionTemplate(
@Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
这两个配置文件是重中之重,配置了数据源,连接工厂,Mapper扫描的包, mapper xml配置的位置等
单元测试
package enjoy.test;
import cn.enjoy.App;
import cn.enjoy.model.Orders;
import cn.enjoy.model.Users;
import cn.enjoy.service.IOrderService;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.annotation.Resource;
@SpringBootTest(classes = {App.class})
@RunWith(SpringRunner.class)
public class Test {
@Resource
private IOrderService iOrderService;
@org.junit.Test
public void test1() {
Users users = new Users();
users.setUsername("enjoy");
users.setPasswd("123");
users.setId(1);
Orders orders = new Orders();
orders.setAccount(12);
orders.setName("娃娃");
orders.setUserId(1);
iOrderService.addOrder(orders,users);
}
}
这个时候以及集成了2套数据源,并且已经测试发现可以同时入库。
jta+atomikos分布式事务
修改pom文件
增加atomikos支持
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jta-atomikos</artifactId>
</dependency>
新增配置类(DBConfig1,DBConfig2)
package cn.enjoy.config;
import org.springframework.boot.context.properties.ConfigurationProperties;
@ConfigurationProperties(prefix = "spring.datasource.spring")
@Component
public class DBConfig1 {
private String driverClassName;
private String jdbcUrl;
private String username;
private String password;
public String getDriverClassName() {
return driverClassName;
}
public void setDriverClassName(String driverClassName) {
this.driverClassName = driverClassName;
}
public String getJdbcUrl() {
return jdbcUrl;
}
public void setJdbcUrl(String jdbcUrl) {
this.jdbcUrl = jdbcUrl;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
package cn.enjoy.config;
import org.springframework.boot.context.properties.ConfigurationProperties;
@ConfigurationProperties(prefix = "spring.datasource.spring2")
@Component
public class DBConfig2 {
private String driverClassName;
private String jdbcUrl;
private String username;
private String password;
public String getDriverClassName() {
return driverClassName;
}
public void setDriverClassName(String driverClassName) {
this.driverClassName = driverClassName;
}
public String getJdbcUrl() {
return jdbcUrl;
}
public void setJdbcUrl(String jdbcUrl) {
this.jdbcUrl = jdbcUrl;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
修改数据源配置类
package cn.enjoy.config;
import com.mysql.cj.jdbc.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.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
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 org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "cn.enjoy.dao.users", sqlSessionFactoryRef = "test1SqlSessionFactory")
public class DataSource1Config {
@Bean(name = "test1DataSource")
@Primary
public DataSource testDataSource(DBConfig1 config1) {
MysqlXADataSource mysqlXADataSource=new MysqlXADataSource();
mysqlXADataSource.setUrl(config1.getJdbcUrl());
mysqlXADataSource.setPassword(config1.getPassword());
mysqlXADataSource.setUser(config1.getUsername());
AtomikosDataSourceBean atomikosDataSourceBean=new AtomikosDataSourceBean();
atomikosDataSourceBean.setXaDataSource(mysqlXADataSource);
atomikosDataSourceBean.setUniqueResourceName("test1Datasource");
return atomikosDataSourceBean;
}
@Bean(name = "test1SqlSessionFactory")
@Primary
public SqlSessionFactory testSqlSessionFactory(@Qualifier("test1DataSource") DataSource dataSource)
throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapping/users/*.xml"));
return bean.getObject();
}
@Bean(name = "test1TransactionManager")
@Primary
public DataSourceTransactionManager testTransactionManager(@Qualifier("test1DataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "test1SqlSessionTemplate")
@Primary
public SqlSessionTemplate testSqlSessionTemplate(
@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
package cn.enjoy.config;
import com.mysql.cj.jdbc.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.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
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 org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "cn.enjoy.dao.orders", sqlSessionFactoryRef = "test2SqlSessionFactory")
public class DataSource2Config {
@Bean(name = "test2DataSource")
public DataSource testDataSource(DBConfig2 config2) {
MysqlXADataSource mysqlXADataSource=new MysqlXADataSource();
mysqlXADataSource.setUrl(config2.getJdbcUrl());
mysqlXADataSource.setPassword(config2.getPassword());
mysqlXADataSource.setUser(config2.getUsername());
AtomikosDataSourceBean atomikosDataSourceBean=new AtomikosDataSourceBean();
atomikosDataSourceBean.setXaDataSource(mysqlXADataSource);
atomikosDataSourceBean.setUniqueResourceName("test2Datasource");
return atomikosDataSourceBean;
}
@Bean(name = "test2SqlSessionFactory")
public SqlSessionFactory testSqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource)
throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapping/orders/*.xml"));
return bean.getObject();
}
@Bean(name = "test2TransactionManager")
public DataSourceTransactionManager testTransactionManager(@Qualifier("test2DataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "test2SqlSessionTemplate")
public SqlSessionTemplate testSqlSessionTemplate(
@Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
修改Service方法
package cn.enjoy.service.impl;
import cn.enjoy.dao.orders.OrdersMapper;
import cn.enjoy.dao.users.UsersMapper;
import cn.enjoy.model.Orders;
import cn.enjoy.model.Users;
import cn.enjoy.service.IOrderService;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import javax.annotation.Resource;
@Service
public class OrderServiceImpl implements IOrderService{
@Resource
private UsersMapper usersMapper;
@Resource
private OrdersMapper ordersMapper;
@Override
@Transactional
public void addOrder(Orders orders, Users users) {
usersMapper.insertSelective(users);
int i=10/0;
ordersMapper.insertSelective(orders);
}
}
最后使用之前的单元测试方法即可