一. 需求采集
有一个业务操作,需要同时对两个数据库执行DML操作,要么都执行成功,要么都不成功。
二. 需求分析
这是一个典型的分布式事务应用场景,但以前只是听说过分布式事务,却没有亲自动手实践过。项目本来是使用的SpringBoot框架,因此拿“springboot 分布式事务”作为关键词交给度娘,发现可参考的资料很多,心中窃喜,但仔细一看,绝大多数文章都使用了mybatis,mybatis虽盛行,但我们公司却没用过,咱用的是JPA,Hibernate实现的。所以觉得有必要为基于JPA的分布式事务实现方式留下点什么,故有此一篇文章。
三.程序设计
- 首先,看看pom.xml的依赖
<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>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!-- 数据库 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.18</version>
</dependency>
<dependency>
<groupId>com.oracle.driver</groupId>
<artifactId>jdbc-driver</artifactId>
<version>11g</version>
</dependency>
<dependency>
<groupId>org.apache.httpcomponents</groupId>
<artifactId>httpmime</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.16</version>
</dependency>
<dependency>
<groupId>commons-collections</groupId>
<artifactId>commons-collections</artifactId>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.0</version>
</dependency>
<!--jta+atomokos进行分布式事务管理需要用到的包-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jta-atomikos</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
</dependencies>
- 然后是application.properties关于两个数据源的配置:
#源库数据源
spring.datasource.url=jdbc:oracle:thin:@127.0.0.1:1521/orcl
spring.datasource.username=aditest
spring.datasource.password=testpassone
spring.datasource.driverClassName = oracle.jdbc.OracleDriver
#中间库数据源
spring.datasource.business.url=jdbc:oracle:thin:@127.0.0.1:1521/orcl
spring.datasource.business.username=python
spring.datasource.business.password=testpasstwo
spring.datasource.business.driverClassName = oracle.jdbc.OracleDriver
#####以下为数据源连接的拓展配置属性包含连接池等开始#####
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=50
spring.datasource.maxWait=60000
#####以上为数据源连接的拓展配置属性结束#####
- 在config包(也可以是其他包名)下创建第一个数据源的配置类ClientDBConfig.class
package com.bitservice.adi.client.config;
import java.sql.SQLException;
import java.util.Properties;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import com.alibaba.druid.pool.xa.DruidXADataSource;
import com.atomikos.jdbc.AtomikosDataSourceBean;
@Configuration
//basePackages:设置Repository所在的包
//entityManagerFactoryRef:设置实体管理工厂
//transactionManagerRef:设置事务管理器
@EnableJpaRepositories(basePackages = "com.bitservice.adi.client.dao", entityManagerFactoryRef = "entityManagerFactory", transactionManagerRef = "transactionManager")
public class ClientDBConfig {
// 数据库连接信息
@Value("${spring.datasource.url}")
private String dbUrl;
@Value("${spring.datasource.username}")
private String username;
@Value("${spring.datasource.password}")
private String password;
@Value("${spring.datasource.driverClassName}")
private String driverClassName;
// 连接池连接信息
@Value("${spring.datasource.initialSize}")
private int initialSize;
@Value("${spring.datasource.minIdle}")
private int minIdle;
@Value("${spring.datasource.maxActive}")
private int maxActive;
@Value("${spring.datasource.maxWait}")
private int maxWait;
@Bean // 声明其为Bean实例
@Primary // 在同样的DataSource中,首先使用被标注的DataSource
@Qualifier("dataSource")
public DataSource dataSource() throws SQLException {
DruidXADataSource druidXADataSource = new DruidXADataSource();
// 基础连接信息
druidXADataSource.setUrl(this.dbUrl);
druidXADataSource.setUsername(username);
druidXADataSource.setPassword(password);
druidXADataSource.setDriverClassName(driverClassName);
// 连接池连接信息
druidXADataSource.setInitialSize(initialSize);
druidXADataSource.setMinIdle(minIdle);
druidXADataSource.setMaxActive(maxActive);
druidXADataSource.setMaxWait(maxWait);
druidXADataSource.setPoolPreparedStatements(true); // 是否缓存preparedStatement,也就是PSCache。PSCache对支持游标的数据库性能提升巨大,比如说oracle。在mysql下建议关闭。
druidXADataSource.setMaxPoolPreparedStatementPerConnectionSize(50);
druidXADataSource.setConnectionProperties("oracle.net.CONNECT_TIMEOUT=6000;oracle.jdbc.ReadTimeout=60000");// 对于耗时长的查询sql,会受限于ReadTimeout的控制,单位毫秒
druidXADataSource.setTestOnBorrow(true); // 申请连接时执行validationQuery检测连接是否有效,这里建议配置为TRUE,防止取到的连接不可用
druidXADataSource.setTestWhileIdle(true);// 建议配置为true,不影响性能,并且保证安全性。申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。
String validationQuery = "select 1 from dual";
druidXADataSource.setValidationQuery(validationQuery); // 用来检测连接是否有效的sql,要求是一个查询语句。如果validationQuery为null,testOnBorrow、testOnReturn、testWhileIdle都不会起作用。
druidXADataSource.setFilters("stat,wall");// 属性类型是字符串,通过别名的方式配置扩展插件,常用的插件有:监控统计用的filter:stat日志用的filter:log4j防御sql注入的filter:wall
druidXADataSource.setTimeBetweenEvictionRunsMillis(60000); // 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
druidXADataSource.setMinEvictableIdleTimeMillis(180000); // 配置一个连接在池中最小生存的时间,单位是毫秒,这里配置为3分钟180000
druidXADataSource.setKeepAlive(true); // 打开druid.keepAlive之后,当连接池空闲时,池中的minIdle数量以内的连接,空闲时间超过minEvictableIdleTimeMillis,则会执行keepAlive操作,即执行druid.validationQuery指定的查询SQL,一般为select
// * from
// dual,只要minEvictableIdleTimeMillis设置的小于防火墙切断连接时间,就可以保证当连接空闲时自动做保活检测,不会被防火墙切断
druidXADataSource.setRemoveAbandoned(true); // 是否移除泄露的连接/超过时间限制是否回收。
druidXADataSource.setRemoveAbandonedTimeout(3600); // 泄露连接的定义时间(要超过最大事务的处理时间);单位为秒。这里配置为1小时
druidXADataSource.setLogAbandoned(true); 移除泄露连接发生是是否记录日志
AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
xaDataSource.setXaDataSource(druidXADataSource);
xaDataSource.setUniqueResourceName("dataSource");
return xaDataSource;
}
@Bean(name = "entityManagerFactory")
@Qualifier("entityManagerFactory")
@Primary
public LocalContainerEntityManagerFactoryBean entityManageFactory(EntityManagerFactoryBuilder builder)
throws SQLException {
LocalContainerEntityManagerFactoryBean entityManagerFactory = builder.dataSource(dataSource())
.packages("com.bitservice.adi.client.entity").persistenceUnit("clientPersistenceUnit").build();
Properties jpaProperties = new Properties();
jpaProperties.put("hibernate.dialect", "org.hibernate.dialect.Oracle10gDialect");
jpaProperties.put("hibernate.physical_naming_strategy",
"org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy");
jpaProperties.put("hibernate.connection.charSet", "utf-8");
jpaProperties.put("hibernate.ddl-auto", "update");
jpaProperties.put("hibernate.show_sql", "true");
entityManagerFactory.setJpaProperties(jpaProperties);
return entityManagerFactory;
}
@Bean(name = "entityManager")
@Qualifier("entityManager")
@Primary
public EntityManager entityManager(EntityManagerFactoryBuilder builder) throws SQLException {
return entityManageFactory(builder).getObject().createEntityManager();
}
@Bean(name = "namedParameterJdbcTemplate")
@Qualifier("namedParameterJdbcTemplate")
@Primary
public NamedParameterJdbcTemplate jdbcTemplate(@Qualifier("dataSource") DataSource dataSource) {
return new NamedParameterJdbcTemplate(dataSource);
}
/**
* 返回数据源的事务管理器
*
* @param builder
* @return
* @throws SQLException
*/
@Bean(name = "transactionManager")
@Qualifier("transactionManager")
@Primary
public PlatformTransactionManager transactionManager(EntityManagerFactoryBuilder builder) throws SQLException {
return new JpaTransactionManager(entityManageFactory(builder).getObject());
}
}
4.在config包(也可以是其他包名)下创建第二个数据源的配置类BusinessDBConfig.class
package com.bitservice.adi.client.config;
import java.sql.SQLException;
import java.util.Properties;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import com.alibaba.druid.pool.xa.DruidXADataSource;
import com.atomikos.jdbc.AtomikosDataSourceBean;
@Configuration
//basePackages:设置Repository所在的包
//entityManagerFactoryRef:设置实体管理工厂
//transactionManagerRef:设置事务管理器
@EnableJpaRepositories(basePackages = "com.bitservice.adi.business.dao", entityManagerFactoryRef = "businessEntityManagerFactory", transactionManagerRef = "businessTtransactionManager")
public class BusinessDBConfig {
// 中间库连接信息
@Value("${spring.datasource.business.url}")
private String businessDbUrl;
@Value("${spring.datasource.business.username}")
private String businessUsername;
@Value("${spring.datasource.business.password}")
private String businessPassword;
@Value("${spring.datasource.business.driverClassName}")
private String businessDriverClassName;
// 连接池连接信息
@Value("${spring.datasource.initialSize}")
private int initialSize;
@Value("${spring.datasource.minIdle}")
private int minIdle;
@Value("${spring.datasource.maxActive}")
private int maxActive;
@Value("${spring.datasource.maxWait}")
private int maxWait;
@Bean(name = "businessDataSource")
@Qualifier("businessDataSource")
public DataSource businessDataSource() throws SQLException {
DruidXADataSource druidXADataSource = new DruidXADataSource();
// 基础连接信息
druidXADataSource.setUrl(businessDbUrl);
druidXADataSource.setUsername(businessUsername);
druidXADataSource.setPassword(businessPassword);
druidXADataSource.setDriverClassName(businessDriverClassName);
// 连接池连接信息
druidXADataSource.setInitialSize(initialSize);
druidXADataSource.setMinIdle(minIdle);
druidXADataSource.setMaxActive(maxActive);
druidXADataSource.setMaxWait(maxWait);
druidXADataSource.setPoolPreparedStatements(true); // 是否缓存preparedStatement,也就是PSCache。PSCache对支持游标的数据库性能提升巨大,比如说oracle。在mysql下建议关闭。
druidXADataSource.setMaxPoolPreparedStatementPerConnectionSize(50);
druidXADataSource.setConnectionProperties("oracle.net.CONNECT_TIMEOUT=6000;oracle.jdbc.ReadTimeout=60000");// 对于耗时长的查询sql,会受限于ReadTimeout的控制,单位毫秒
druidXADataSource.setTestOnBorrow(true); // 申请连接时执行validationQuery检测连接是否有效,这里建议配置为TRUE,防止取到的连接不可用
druidXADataSource.setTestWhileIdle(true);// 建议配置为true,不影响性能,并且保证安全性。申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。
String validationQuery = "select 1 from dual";
druidXADataSource.setValidationQuery(validationQuery); // 用来检测连接是否有效的sql,要求是一个查询语句。如果validationQuery为null,testOnBorrow、testOnReturn、testWhileIdle都不会起作用。
druidXADataSource.setFilters("stat,wall");// 属性类型是字符串,通过别名的方式配置扩展插件,常用的插件有:监控统计用的filter:stat日志用的filter:log4j防御sql注入的filter:wall
druidXADataSource.setTimeBetweenEvictionRunsMillis(60000); // 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
druidXADataSource.setMinEvictableIdleTimeMillis(180000); // 配置一个连接在池中最小生存的时间,单位是毫秒,这里配置为3分钟180000
druidXADataSource.setKeepAlive(true); // 打开druid.keepAlive之后,当连接池空闲时,池中的minIdle数量以内的连接,空闲时间超过minEvictableIdleTimeMillis,则会执行keepAlive操作,即执行druid.validationQuery指定的查询SQL,一般为select
// * from
// dual,只要minEvictableIdleTimeMillis设置的小于防火墙切断连接时间,就可以保证当连接空闲时自动做保活检测,不会被防火墙切断
druidXADataSource.setRemoveAbandoned(true); // 是否移除泄露的连接/超过时间限制是否回收。
druidXADataSource.setRemoveAbandonedTimeout(3600); // 泄露连接的定义时间(要超过最大事务的处理时间);单位为秒。这里配置为1小时
druidXADataSource.setLogAbandoned(true); 移除泄露连接发生是是否记录日志
AtomikosDataSourceBean xaDataSource=new AtomikosDataSourceBean();
xaDataSource.setXaDataSource(druidXADataSource);
xaDataSource.setUniqueResourceName("businessDataSource");
return xaDataSource;
}
@Bean(name = "businessEntityManagerFactory")
@Qualifier("businessEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean entityManageFactory(EntityManagerFactoryBuilder builder)
throws SQLException {
LocalContainerEntityManagerFactoryBean entityManagerFactory = builder.dataSource(businessDataSource())
.packages("com.bitservice.adi.business.entity").persistenceUnit("businessPersistenceUnit").build();
Properties jpaProperties = new Properties();
jpaProperties.put("hibernate.dialect", "org.hibernate.dialect.Oracle10gDialect");
jpaProperties.put("hibernate.physical_naming_strategy",
"org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy");
jpaProperties.put("hibernate.connection.charSet", "utf-8");
jpaProperties.put("hibernate.ddl-auto", "update");
jpaProperties.put("hibernate.show_sql", "true");
entityManagerFactory.setJpaProperties(jpaProperties);
return entityManagerFactory;
}
@Bean(name = "businessEntityManager")
@Qualifier("businessEntityManager")
public EntityManager entityManager(EntityManagerFactoryBuilder builder) throws SQLException {
return entityManageFactory(builder).getObject().createEntityManager();
}
@Bean(name = "businessJdbcTemplate")
@Qualifier("businessJdbcTemplate")
public NamedParameterJdbcTemplate businessJdbcTemplate(@Qualifier("businessDataSource") DataSource dataSource) {
return new NamedParameterJdbcTemplate(dataSource);
}
/**
* 返回数据源的事务管理器
* @param builder
* @return
* @throws SQLException
*/
@Bean(name = "businessTransactionManager")
@Qualifier("businessTransactionManager")
public PlatformTransactionManager transactionManager(EntityManagerFactoryBuilder builder) throws SQLException {
return new JpaTransactionManager(entityManageFactory(builder).getObject());
}
}
请注意,两个数据源对应的dao和entity不能放在同一个包路径下,例如我这里,数据源一的dao在com.bitservice.adi.client.dao下,数据源二的dao在com.bitservice.adi.business.dao下,entity同理。
- 在config包(也可以是其他包名)下创建Jta事务管理配置类JtaTransactionManagerConfig.class,
要注意设置分布式事务的超时时间,默认是10秒钟。
package com.bitservice.adi.client.config;
import javax.transaction.SystemException;
import javax.transaction.UserTransaction;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.transaction.jta.JtaTransactionManager;
import com.atomikos.icatch.jta.UserTransactionImp;
import com.atomikos.icatch.jta.UserTransactionManager;
/**
* Jta事务管理配置类
* @author lx
*
* 2019年9月6日
*
*/
@Configuration
public class JtaTransactionManagerConfig {
@Bean(name = "jtaTransactionManager")
@Primary
public JtaTransactionManager regTransactionManager() throws SystemException {
UserTransactionManager userTransactionManager = new UserTransactionManager();
userTransactionManager.setTransactionTimeout(600); //设置整个事务的超时时间,单位秒
UserTransaction userTransaction = new UserTransactionImp();
return new JtaTransactionManager(userTransaction, userTransactionManager);
}
}
- 创建数据源一的dao类ClientRepository
package com.bitservice.adi.client.dao;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class ClientRepository {
@Autowired
@Qualifier("namedParameterJdbcTemplate")
private NamedParameterJdbcTemplate jdbcTemplate;
public int update(Map<String, Object> params) {
String sql = "UPDATE ADI_IDESCARTES_SYNLOGS xe SET xe.synflag_shuiwu = 1 WHERE xe.ywbh in (:ywbhs)";
int result = jdbcTemplate.update(sql, params);
return result;
}
}
- 创建数据源二的dao类BusinessRepository
package com.bitservice.adi.business.dao;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class BusinessRepository {
private final Logger logger = Logger.getLogger(getClass());
@Autowired
@Qualifier("businessJdbcTemplate")
private NamedParameterJdbcTemplate jdbcTemplate;
/**
*
* @param hths 准备删除的合同编号
* @param jyfcount 预计要删除交易方数量
* @param params
*/
public void delDbData(List<String> hths, Map<String, Object> params) {
int htcount = hths.size();
String del_fcjycjxx_sql = "delete from SB_ZJB_FCJYCJXX where htbh in :htbhs";
String del_jyf_sql = "delete from SB_ZJB_FCJYCJXX_JYF where fcjycjxxuuid in (select fcjycjxxuuid from SB_ZJB_FCJYCJXX cjxx where cjxx.htbh in :htbhs)";
int a = jdbcTemplate.update(del_jyf_sql, params); //先删除交易方数据
logger.info("删除交易方数量:"+a);
int b = jdbcTemplate.update(del_fcjycjxx_sql, params); //再删除交易信息
if(b != htcount) {
throw new RuntimeException("预计删除的合同数量("+htcount+")与实际删除的合同数量("+b+")不匹配");
}
logger.info("删除合同数量:"+b);
}
}
- 把以上两个dao注入到你的Service类中,并在你的Service方法中分别调用两个dao的方法,唯一要注意的一点,Service的事务注解要像下面这样写
package com.bitservice.adi.business.dao;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;
@Service
@Transactional(value="jtaTransactionManager")
public class DataSynService{
}
Service的具体业务逻辑以及controller我就不写了。
- SpringBoot启动类。要排除掉默认的DataSourceAutoConfiguration和DataSourceTransactionManagerAutoConfiguration
package com.bitservice;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.transaction.annotation.EnableTransactionManagement;
@SpringBootApplication(exclude = { DataSourceAutoConfiguration.class,
DataSourceTransactionManagerAutoConfiguration.class })
// 开启手动配置
@EnableTransactionManagement
// @SpringBootApplication
@EnableScheduling // 定时任务
public class ADIClientApplication {
public static void main(String[] args) {
SpringApplication.run(ADIClientApplication.class, args);
}
}
我在测试的时候,是通过手动抛出运行时异常测试的,无论是在数据源一的dao抛异常,还是在数据源二的dao抛异常,或者是在service中抛出异常,事务都正常回滚了!
谢谢阅读,如果疑问,请给我留言。