ShardingSphere RAW JDBC 分布式事務XA 代碼示例

項目工程在：transaction-2pc-xa-raw-jdbc-example

代碼簡介

源代碼示例。????基于ShardingSphere的Atomikos XA實現一個簡單的分布式事務應用demo

環境配置

????啟動兩個MySQL5.7的docker鏡像（8.0的一直報事務相關的錯的，不知啥原因，換成5.7就好了），下面命令直接復制運行即可：

# 啟動兩個mysql
docker run --name mysql11 -p 3311:3306 -e MYSQL_ROOT_PASSWORD=root -e MYSQL_ROOT_HOST=% -d mysql:5.7
docker run --name mysql12 -p 3312:3306 -e MYSQL_ROOT_PASSWORD=root -e MYSQL_ROOT_HOST=% -d mysql:5.7# 在11上創建數據庫demo_ds_0，運行下面的SQL語句初始化數據庫和表
docker exec -ti mysql11 mysql -u root -pcreate database demo_ds_0;
use demo_ds_0;
CREATE TABLE IF NOT EXISTS t_order_0 (order_id BIGINT NOT NULL, user_id INT NOT NULL, PRIMARY KEY (order_id));
CREATE TABLE IF NOT EXISTS t_order_1 (order_id BIGINT NOT NULL, user_id INT NOT NULL, PRIMARY KEY (order_id));# 在12上創建數據庫demo_ds_1，運行下面的SQL語句初始化數據庫和表
docker exec -ti mysql12 mysql -u root -pcreate database demo_ds_1;
use demo_ds_1;
CREATE TABLE IF NOT EXISTS t_order_0 (order_id BIGINT NOT NULL, user_id INT NOT NULL, PRIMARY KEY (order_id));
CREATE TABLE IF NOT EXISTS t_order_1 (order_id BIGINT NOT NULL, user_id INT NOT NULL, PRIMARY KEY (order_id));

程序配置

Maven依賴

????核心依賴下面幾個：

<?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><groupId>com.example</groupId><artifactId>demo</artifactId><version>0.0.1-SNAPSHOT</version><name>transaction-2pc-xa-raw-jdbc-example</name><description>Demo project for Spring Boot</description><properties><java.version>1.8</java.version></properties><dependencies><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId> <!-- Use 'netty-all' for 4.0 or above --><version>8.0.14</version></dependency><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>shardingsphere-jdbc-core</artifactId><version>5.0.0-alpha</version></dependency><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>shardingsphere-transaction-xa-core</artifactId><version>5.0.0-alpha</version></dependency><dependency><groupId>com.zaxxer</groupId><artifactId>HikariCP</artifactId><version>2.2.5</version></dependency></dependencies><build><plugins><plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-compiler-plugin</artifactId><configuration><source>7</source><target>7</target></configuration></plugin></plugins></build></project>

shardingSphere數據庫配置文件

????如上面的docker數據庫配置，這里設置了兩個數據庫，各自有兩張表

dataSources:ds_0: !!com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.cj.jdbc.DriverjdbcUrl: jdbc:mysql://localhost:3311/demo_ds_0?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=trueusername: rootpassword: rootautoCommit: falseds_1: !!com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.cj.jdbc.DriverjdbcUrl: jdbc:mysql://localhost:3312/demo_ds_1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=trueusername: rootpassword: rootautoCommit: falserules:
- !SHARDINGtables:t_order:actualDataNodes: ds_${0..1}.t_order_${0..1}databaseStrategy:standard:shardingColumn: user_idshardingAlgorithmName: database_inlinetableStrategy:standard:shardingColumn: order_idshardingAlgorithmName: t_order_inlinebindingTables:- t_ordershardingAlgorithms:database_inline:type: INLINEprops:algorithm-expression: ds_${user_id % 2}t_order_inline:type: INLINEprops:algorithm-expression: t_order_${order_id % 2}props:sql-show: true

主程序

????暴力直接的原生測試，代碼如下：

package com.example.demo;import org.apache.shardingsphere.driver.api.yaml.YamlShardingSphereDataSourceFactory;
import org.apache.shardingsphere.transaction.core.TransactionType;
import org.apache.shardingsphere.transaction.core.TransactionTypeHolder;import javax.sql.DataSource;
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;public class Transaction2pcXaRawJdbcExampleApplication {/*** 第一次插入數據正常運行成功* 第二次插入數據由于主鍵沖突，導致回滾*/public static void main(String[] args) throws IOException, SQLException {DataSource dataSource = getShardingDatasource();cleanupData(dataSource);TransactionTypeHolder.set(TransactionType.XA);Connection conn = dataSource.getConnection();String sql = "insert into t_order (user_id, order_id) VALUES (?, ?);";System.out.println("First XA Start insert data");try (PreparedStatement statement = conn.prepareStatement(sql)) {conn.setAutoCommit(false);for (int i = 1; i < 11; i++) {statement.setLong(1, i);statement.setLong(2, i);statement.executeUpdate();}conn.commit();}System.out.println("First XA insert successful");// 設置id+5，如果設置XA事務成功，則所有的數據都不會插入// 設置id+5，如果設置XA事務不成功，則id大于10的數據就會插入到數據庫// 程序運行完畢后，查看數據庫，沒有id大于10的數據，所以XA設置成功System.out.println("Second XA Start insert data");try (PreparedStatement statement = conn.prepareStatement(sql)) {conn.setAutoCommit(false);for (int i = 1; i < 11; i++) {statement.setLong(1, i+5);statement.setLong(2, i+5);statement.executeUpdate();}conn.commit();} catch (Exception e) {System.out.println("Second XA insert failed");conn.rollback();} finally {conn.close();}System.out.println("Second XA insert successful");}private static void cleanupData(DataSource dataSource) {System.out.println("Delete all Data");try (Connection conn = dataSource.getConnection(); Statement statement = conn.createStatement()) {statement.execute("delete from t_order;");conn.commit();} catch (SQLException e) {e.printStackTrace();}System.out.println("Delete all Data successful");}/*** 生成DataSource，文件路徑自行替換* @return* @throws IOException* @throws SQLException*/static private DataSource getShardingDatasource() throws IOException, SQLException {String fileName = "F:\\Code\\Java\\JAVA-000\\homework\\shardingSphere-jdbc-example\\transaction-example\\transaction-2pc-xa-raw-jdbc-example\\src\\main\\resources\\sharding-databases-tables.yaml";File yamlFile = new File(fileName);return YamlShardingSphereDataSourceFactory.createDataSource(yamlFile);}}