使用sharding-jdbc来实现数据库的垂直拆分

下载 :: ShardingSphere

Sharding-JDBC 实战（史上最全）_40岁资深老架构师尼恩的博客-CSDN博客_sharding-jdbc

Sharding-JDBC（二）SpringBoot整合Sharding-JDBC实现分库分表_一恍过去的博客-CSDN博客

实现步骤很简单

1.引入依赖

org.apache.shardingspheresharding-jdbc-spring-boot-starter4.0.0-RC1

2.配置文件

#shardingjdbc
spring.shardingsphere.datasource.names=wim-user,wim-orderspring.shardingsphere.datasource.wim-user.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.wim-user.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.wim-user.url=jdbc:mysql://127.0.0.1:3306/wim-user?serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.wim-user.username=root
spring.shardingsphere.datasource.wim-user.password=123456spring.shardingsphere.datasource.wim-order.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.wim-order.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.wim-order.url=jdbc:mysql://127.0.0.1:3306/wim-order?serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.wim-order.username=root
spring.shardingsphere.datasource.wim-order.password=123456spring.shardingsphere.sharding.tables.order_t.actual-data-nodes=wim-order.order_t
spring.shardingsphere.sharding.default-data-source-name=wim-user
spring.shardingsphere.props.sql.show=true
spring.main.allow-bean-definition-overriding=true

简单写两个方法来测试一下

@Transactional
public void saveUserAndOrder() {User user = new User();user.setName("user" + System.currentTimeMillis());user.setAge(0);userMapper.insert(user);log.info("TransactionService saveUserAndOrderTrans user {}", user);Order order = new Order();order.setName("order" + System.currentTimeMillis());order.setDate(new Date());orderMapper.insert(order);log.info("TransactionService saveUserAndOrderTrans order {}", order);
}

正常写入，日志如下：

2022-12-11 17:35:20.081 DEBUG 16248 --- [o-8080-Acceptor] o.apache.tomcat.util.threads.LimitLatch  : Counting up[http-nio-8080-Acceptor] latch=1
2022-12-11 17:35:20.081 DEBUG 16248 --- [o-8080-Acceptor] o.apache.tomcat.util.threads.LimitLatch  : Counting up[http-nio-8080-Acceptor] latch=2
2022-12-11 17:35:20.082 DEBUG 16248 --- [nio-8080-exec-5] o.a.coyote.http11.Http11InputBuffer      : Before fill(): [true], parsingRequestLine: [true], parsingRequestLinePhase: [0], parsingRequestLineStart: [0], byteBuffer.position() [0]
2022-12-11 17:35:20.083 DEBUG 16248 --- [nio-8080-exec-5] o.a.tomcat.util.net.SocketWrapperBase    : Socket: [org.apache.tomcat.util.net.NioEndpoint$NioSocketWrapper@1ffd1618:org.apache.tomcat.util.net.NioChannel@29090279:java.nio.channels.SocketChannel[connected local=/127.0.0.1:8080 remote=/127.0.0.1:59934]], Read from buffer: [0]
2022-12-11 17:35:20.083 DEBUG 16248 --- [nio-8080-exec-5] org.apache.tomcat.util.net.NioEndpoint   : Socket: [org.apache.tomcat.util.net.NioEndpoint$NioSocketWrapper@1ffd1618:org.apache.tomcat.util.net.NioChannel@29090279:java.nio.channels.SocketChannel[connected local=/127.0.0.1:8080 remote=/127.0.0.1:59934]], Read direct from socket: [687]
2022-12-11 17:35:20.083 DEBUG 16248 --- [nio-8080-exec-5] o.a.coyote.http11.Http11InputBuffer      : Received [GET /mutildb/trans/saveUserAndOrder HTTP/1.1
Host: 127.0.0.1:8080
Connection: keep-alive
sec-ch-ua: "Not?A_Brand";v="8", "Chromium";v="108", "Google Chrome";v="108"
sec-ch-ua-mobile: ?0
sec-ch-ua-platform: "Windows"
Upgrade-Insecure-Requests: 1
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.0.0 Safari/537.36
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9
Sec-Fetch-Site: none
Sec-Fetch-Mode: navigate
Sec-Fetch-User: ?1
Sec-Fetch-Dest: document
Accept-Encoding: gzip, deflate, br
Accept-Language: zh-CN,zh;q=0.9]
2022-12-11 17:35:20.083 DEBUG 16248 --- [nio-8080-exec-5] o.a.c.authenticator.AuthenticatorBase    : Security checking request GET /mutildb/trans/saveUserAndOrder
2022-12-11 17:35:20.083 DEBUG 16248 --- [nio-8080-exec-5] org.apache.catalina.realm.RealmBase      :   No applicable constraints defined
2022-12-11 17:35:20.083 DEBUG 16248 --- [nio-8080-exec-5] o.a.c.authenticator.AuthenticatorBase    : Not subject to any constraint
2022-12-11 17:35:20.083 DEBUG 16248 --- [nio-8080-exec-5] org.apache.tomcat.util.http.Parameters   : Set encoding to UTF-8
2022-12-11 17:35:20.084 DEBUG 16248 --- [nio-8080-exec-5] o.s.web.servlet.DispatcherServlet        : GET "/mutildb/trans/saveUserAndOrder", parameters={}
2022-12-11 17:35:20.084 DEBUG 16248 --- [nio-8080-exec-5] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped to com.chen.rest.TransRest#saveUserAndOrder()
2022-12-11 17:35:20.084 DEBUG 16248 --- [nio-8080-exec-5] o.s.j.d.DataSourceTransactionManager     : Creating new transaction with name [com.chen.service.TransactionService.saveUserAndOrder]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
2022-12-11 17:35:20.084 DEBUG 16248 --- [nio-8080-exec-5] o.s.j.d.DataSourceTransactionManager     : Acquired Connection [org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.ShardingConnection@113afb97] for JDBC transaction
2022-12-11 17:35:20.084 DEBUG 16248 --- [nio-8080-exec-5] o.s.j.d.DataSourceTransactionManager     : Switching JDBC Connection [org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.ShardingConnection@113afb97] to manual commit
Creating a new SqlSession
Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@15057d68]
JDBC Connection [org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.ShardingConnection@113afb97] will be managed by Spring
==>  Preparing: INSERT INTO user_t ( id, name, age ) VALUES ( ?, ?, ? )
==> Parameters: 1601873255239520257(Long), user1670751320084(String), 0(Integer)
2022-12-11 17:35:20.085  INFO 16248 --- [nio-8080-exec-5] ShardingSphere-SQL                       : Rule Type: sharding
2022-12-11 17:35:20.085  INFO 16248 --- [nio-8080-exec-5] ShardingSphere-SQL                       : Logic SQL: INSERT INTO user_t  ( id,
name,
age )  VALUES  ( ?,
?,
? )
2022-12-11 17:35:20.085  INFO 16248 --- [nio-8080-exec-5] ShardingSphere-SQL                       : SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=user_t, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=user_t, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=20)], parametersIndex=3, logicSQL=INSERT INTO user_t  ( id,
name,
age )  VALUES  ( ?,
?,
? )), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[id, name, age], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@3b0c4e5c, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@5170fffe, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@7ce7e032])])
2022-12-11 17:35:20.085  INFO 16248 --- [nio-8080-exec-5] ShardingSphere-SQL                       : Actual SQL: wim-user ::: INSERT INTO user_t   (id, name, age) VALUES (?, ?, ?) ::: [1601873255239520257, user1670751320084, 0]
2022-12-11 17:35:20.091 DEBUG 16248 --- [nio-8080-exec-5] o.a.c.loader.WebappClassLoaderBase       :     findClass(com.mysql.jdbc.MySQLConnection)
2022-12-11 17:35:20.092 DEBUG 16248 --- [nio-8080-exec-5] o.a.c.loader.WebappClassLoaderBase       :     --> Returning ClassNotFoundException
2022-12-11 17:35:20.095  WARN 16248 --- [nio-8080-exec-5] c.a.druid.pool.DruidAbstractDataSource   : discard long time none received connection. , jdbcUrl : jdbc:mysql://127.0.0.1:3306/wim-user?serverTimezone=UTC&useSSL=false, version : 1.2.8, lastPacketReceivedIdleMillis : 227765
2022-12-11 17:35:20.095 DEBUG 16248 --- [nio-8080-exec-5] com.alibaba.druid.pool.DruidDataSource   : skip not validate connection.
<==    Updates: 1
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@15057d68]
2022-12-11 17:35:20.101  INFO 16248 --- [nio-8080-exec-5] com.chen.service.TransactionService      : TransactionService saveUserAndOrderTrans user User(id=1601873255239520257, name=user1670751320084, age=0)
Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@15057d68] from current transaction
==>  Preparing: INSERT INTO order_t ( id, name, date ) VALUES ( ?, ?, ? )
==> Parameters: 1601873255302434818(Long), order1670751320101(String), 2022-12-11 17:35:20.101(Timestamp)
2022-12-11 17:35:20.102  INFO 16248 --- [nio-8080-exec-5] ShardingSphere-SQL                       : Rule Type: sharding
2022-12-11 17:35:20.102  INFO 16248 --- [nio-8080-exec-5] ShardingSphere-SQL                       : Logic SQL: INSERT INTO order_t  ( id,
name,
date )  VALUES  ( ?,
?,
? )
2022-12-11 17:35:20.102  INFO 16248 --- [nio-8080-exec-5] ShardingSphere-SQL                       : SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=order_t, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=order_t, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=21)], parametersIndex=3, logicSQL=INSERT INTO order_t  ( id,
name,
date )  VALUES  ( ?,
?,
? )), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[id, name, date], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@3c187569, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@67bcb8b8, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@6b9ef97a])])
2022-12-11 17:35:20.102  INFO 16248 --- [nio-8080-exec-5] ShardingSphere-SQL                       : Actual SQL: wim-order ::: INSERT INTO order_t   (id, name, date) VALUES (?, ?, ?) ::: [1601873255302434818, order1670751320101, 2022-12-11 17:35:20.101]
2022-12-11 17:35:20.102  WARN 16248 --- [nio-8080-exec-5] c.a.druid.pool.DruidAbstractDataSource   : discard long time none received connection. , jdbcUrl : jdbc:mysql://127.0.0.1:3306/wim-order?serverTimezone=UTC&useSSL=false, version : 1.2.8, lastPacketReceivedIdleMillis : 227780
2022-12-11 17:35:20.102 DEBUG 16248 --- [nio-8080-exec-5] com.alibaba.druid.pool.DruidDataSource   : skip not validate connection.
<==    Updates: 1
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@15057d68]
2022-12-11 17:35:20.106  INFO 16248 --- [nio-8080-exec-5] com.chen.service.TransactionService      : TransactionService saveUserAndOrderTrans order Order(id=1601873255302434818, name=order1670751320101, date=Sun Dec 11 17:35:20 CST 2022)
Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@15057d68]
Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@15057d68]
Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@15057d68]
2022-12-11 17:35:20.106 DEBUG 16248 --- [nio-8080-exec-5] o.s.j.d.DataSourceTransactionManager     : Initiating transaction commit
2022-12-11 17:35:20.106 DEBUG 16248 --- [nio-8080-exec-5] o.s.j.d.DataSourceTransactionManager     : Committing JDBC transaction on Connection [org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.ShardingConnection@113afb97]
2022-12-11 17:35:20.112 DEBUG 16248 --- [nio-8080-exec-5] o.s.j.d.DataSourceTransactionManager     : Releasing JDBC Connection [org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.ShardingConnection@113afb97] after transaction
2022-12-11 17:35:20.113 DEBUG 16248 --- [nio-8080-exec-5] m.m.a.RequestResponseBodyMethodProcessor : Using 'text/html', given [text/html, application/xhtml+xml, image/avif, image/webp, image/apng, application/xml;q=0.9, application/signed-exchange;v=b3;q=0.9, */*;q=0.8] and supported [text/plain, */*, text/plain, */*, application/json, application/*+json, application/json, application/*+json]
2022-12-11 17:35:20.113 DEBUG 16248 --- [nio-8080-exec-5] m.m.a.RequestResponseBodyMethodProcessor : Writing ["ok"]
2022-12-11 17:35:20.114 DEBUG 16248 --- [nio-8080-exec-5] o.s.web.servlet.DispatcherServlet        : Completed 200 OK
2022-12-11 17:35:20.114 DEBUG 16248 --- [nio-8080-exec-5] o.a.coyote.http11.Http11InputBuffer      : Before fill(): [true], parsingRequestLine: [true], parsingRequestLinePhase: [0], parsingRequestLineStart: [0], byteBuffer.position() [0]
2022-12-11 17:35:20.114 DEBUG 16248 --- [nio-8080-exec-5] o.a.tomcat.util.net.SocketWrapperBase    : Socket: [org.apache.tomcat.util.net.NioEndpoint$NioSocketWrapper@1ffd1618:org.apache.tomcat.util.net.NioChannel@29090279:java.nio.channels.SocketChannel[connected local=/127.0.0.1:8080 remote=/127.0.0.1:59934]], Read from buffer: [0]
2022-12-11 17:35:20.114 DEBUG 16248 --- [nio-8080-exec-5] org.apache.tomcat.util.net.NioEndpoint   : Socket: [org.apache.tomcat.util.net.NioEndpoint$NioSocketWrapper@1ffd1618:org.apache.tomcat.util.net.NioChannel@29090279:java.nio.channels.SocketChannel[connected local=/127.0.0.1:8080 remote=/127.0.0.1:59934]], Read direct from socket: [0]
2022-12-11 17:35:20.114 DEBUG 16248 --- [nio-8080-exec-5] o.a.coyote.http11.Http11InputBuffer      : Received []
2022-12-11 17:35:20.114 DEBUG 16248 --- [nio-8080-exec-5] o.apache.coyote.http11.Http11Processor   : Socket: [org.apache.tomcat.util.net.NioEndpoint$NioSocketWrapper@1ffd1618:org.apache.tomcat.util.net.NioChannel@29090279:java.nio.channels.SocketChannel[connected local=/127.0.0.1:8080 remote=/127.0.0.1:59934]], Status in: [OPEN_READ], State out: [OPEN]
2022-12-11 17:35:20.114 DEBUG 16248 --- [nio-8080-exec-5] org.apache.tomcat.util.net.NioEndpoint   : Registered read interest for [org.apache.tomcat.util.net.NioEndpoint$NioSocketWrapper@1ffd1618:org.apache.tomcat.util.net.NioChannel@29090279:java.nio.channels.SocketChannel[connected local=/127.0.0.1:8080 remote=/127.0.0.1:59934]]

可以查看一下日志  看一下数据源是如何切换的。

@Transactional
public void saveUserAndOrderTrans() {User user = new User();user.setName("user" + System.currentTimeMillis());user.setAge(0);userMapper.insert(user);log.info("TransactionService saveUserAndOrderTrans user {}", user);Order order = new Order();order.setName("order" + System.currentTimeMillis());order.setDate(new Date());orderMapper.insert(order);log.info("TransactionService saveUserAndOrderTrans order {}", order);// 制造异常 事务回滚int x = 1 / 0;log.info("x");
}

测试一下异常情况下  多数据源事务能否回滚

2022-12-11 17:37:03.317 DEBUG 16248 --- [o-8080-Acceptor] o.apache.tomcat.util.threads.LimitLatch  : Counting up[http-nio-8080-Acceptor] latch=2
2022-12-11 17:37:03.318 DEBUG 16248 --- [nio-8080-exec-9] o.a.coyote.http11.Http11InputBuffer      : Before fill(): [true], parsingRequestLine: [true], parsingRequestLinePhase: [0], parsingRequestLineStart: [0], byteBuffer.position() [0]
2022-12-11 17:37:03.320 DEBUG 16248 --- [nio-8080-exec-9] o.a.tomcat.util.net.SocketWrapperBase    : Socket: [org.apache.tomcat.util.net.NioEndpoint$NioSocketWrapper@731b4256:org.apache.tomcat.util.net.NioChannel@29090279:java.nio.channels.SocketChannel[connected local=/127.0.0.1:8080 remote=/127.0.0.1:60052]], Read from buffer: [0]
2022-12-11 17:37:03.320 DEBUG 16248 --- [nio-8080-exec-9] org.apache.tomcat.util.net.NioEndpoint   : Socket: [org.apache.tomcat.util.net.NioEndpoint$NioSocketWrapper@731b4256:org.apache.tomcat.util.net.NioChannel@29090279:java.nio.channels.SocketChannel[connected local=/127.0.0.1:8080 remote=/127.0.0.1:60052]], Read direct from socket: [718]
2022-12-11 17:37:03.320 DEBUG 16248 --- [nio-8080-exec-9] o.a.coyote.http11.Http11InputBuffer      : Received [GET /mutildb/trans/saveUserAndOrderTrans HTTP/1.1
Host: 127.0.0.1:8080
Connection: keep-alive
Cache-Control: max-age=0
sec-ch-ua: "Not?A_Brand";v="8", "Chromium";v="108", "Google Chrome";v="108"
sec-ch-ua-mobile: ?0
sec-ch-ua-platform: "Windows"
Upgrade-Insecure-Requests: 1
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.0.0 Safari/537.36
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9
Sec-Fetch-Site: none
Sec-Fetch-Mode: navigate
Sec-Fetch-User: ?1
Sec-Fetch-Dest: document
Accept-Encoding: gzip, deflate, br
Accept-Language: zh-CN,zh;q=0.9]
2022-12-11 17:37:03.320 DEBUG 16248 --- [nio-8080-exec-9] o.a.c.authenticator.AuthenticatorBase    : Security checking request GET /mutildb/trans/saveUserAndOrderTrans
2022-12-11 17:37:03.320 DEBUG 16248 --- [nio-8080-exec-9] org.apache.catalina.realm.RealmBase      :   No applicable constraints defined
2022-12-11 17:37:03.320 DEBUG 16248 --- [nio-8080-exec-9] o.a.c.authenticator.AuthenticatorBase    : Not subject to any constraint
2022-12-11 17:37:03.320 DEBUG 16248 --- [nio-8080-exec-9] org.apache.tomcat.util.http.Parameters   : Set encoding to UTF-8
2022-12-11 17:37:03.320 DEBUG 16248 --- [nio-8080-exec-9] o.s.web.servlet.DispatcherServlet        : GET "/mutildb/trans/saveUserAndOrderTrans", parameters={}
2022-12-11 17:37:03.320 DEBUG 16248 --- [nio-8080-exec-9] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped to com.chen.rest.TransRest#saveUserAndOrderTrans()
2022-12-11 17:37:03.321 DEBUG 16248 --- [nio-8080-exec-9] o.s.j.d.DataSourceTransactionManager     : Creating new transaction with name [com.chen.service.TransactionService.saveUserAndOrderTrans]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
2022-12-11 17:37:03.321 DEBUG 16248 --- [nio-8080-exec-9] o.s.j.d.DataSourceTransactionManager     : Acquired Connection [org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.ShardingConnection@7ddcb245] for JDBC transaction
2022-12-11 17:37:03.321 DEBUG 16248 --- [nio-8080-exec-9] o.s.j.d.DataSourceTransactionManager     : Switching JDBC Connection [org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.ShardingConnection@7ddcb245] to manual commit
Creating a new SqlSession
Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@79e32926]
JDBC Connection [org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.ShardingConnection@7ddcb245] will be managed by Spring
==>  Preparing: INSERT INTO user_t ( id, name, age ) VALUES ( ?, ?, ? )
==> Parameters: 1601873688238493698(Long), user1670751423321(String), 0(Integer)
2022-12-11 17:37:03.321  INFO 16248 --- [nio-8080-exec-9] ShardingSphere-SQL                       : Rule Type: sharding
2022-12-11 17:37:03.321  INFO 16248 --- [nio-8080-exec-9] ShardingSphere-SQL                       : Logic SQL: INSERT INTO user_t  ( id,
name,
age )  VALUES  ( ?,
?,
? )
2022-12-11 17:37:03.322  INFO 16248 --- [nio-8080-exec-9] ShardingSphere-SQL                       : SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=user_t, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=user_t, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=20)], parametersIndex=3, logicSQL=INSERT INTO user_t  ( id,
name,
age )  VALUES  ( ?,
?,
? )), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[id, name, age], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@3b0c4e5c, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@5170fffe, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@7ce7e032])])
2022-12-11 17:37:03.322  INFO 16248 --- [nio-8080-exec-9] ShardingSphere-SQL                       : Actual SQL: wim-user ::: INSERT INTO user_t   (id, name, age) VALUES (?, ?, ?) ::: [1601873688238493698, user1670751423321, 0]
<==    Updates: 1
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@79e32926]
2022-12-11 17:37:03.323  INFO 16248 --- [nio-8080-exec-9] com.chen.service.TransactionService      : TransactionService saveUserAndOrderTrans user User(id=1601873688238493698, name=user1670751423321, age=0)
Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@79e32926] from current transaction
==>  Preparing: INSERT INTO order_t ( id, name, date ) VALUES ( ?, ?, ? )
==> Parameters: 1601873688305602562(Long), order1670751423323(String), 2022-12-11 17:37:03.323(Timestamp)
2022-12-11 17:37:03.323  INFO 16248 --- [nio-8080-exec-9] ShardingSphere-SQL                       : Rule Type: sharding
2022-12-11 17:37:03.323  INFO 16248 --- [nio-8080-exec-9] ShardingSphere-SQL                       : Logic SQL: INSERT INTO order_t  ( id,
name,
date )  VALUES  ( ?,
?,
? )
2022-12-11 17:37:03.323  INFO 16248 --- [nio-8080-exec-9] ShardingSphere-SQL                       : SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=order_t, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=order_t, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=21)], parametersIndex=3, logicSQL=INSERT INTO order_t  ( id,
name,
date )  VALUES  ( ?,
?,
? )), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[id, name, date], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@3c187569, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@67bcb8b8, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@6b9ef97a])])
2022-12-11 17:37:03.323  INFO 16248 --- [nio-8080-exec-9] ShardingSphere-SQL                       : Actual SQL: wim-order ::: INSERT INTO order_t   (id, name, date) VALUES (?, ?, ?) ::: [1601873688305602562, order1670751423323, 2022-12-11 17:37:03.323]
<==    Updates: 1
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@79e32926]
2022-12-11 17:37:03.324  INFO 16248 --- [nio-8080-exec-9] com.chen.service.TransactionService      : TransactionService saveUserAndOrderTrans order Order(id=1601873688305602562, name=order1670751423323, date=Sun Dec 11 17:37:03 CST 2022)
Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@79e32926]
Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@79e32926]
2022-12-11 17:37:03.324 DEBUG 16248 --- [nio-8080-exec-9] o.s.j.d.DataSourceTransactionManager     : Initiating transaction rollback
2022-12-11 17:37:03.324 DEBUG 16248 --- [nio-8080-exec-9] o.s.j.d.DataSourceTransactionManager     : Rolling back JDBC transaction on Connection [org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.ShardingConnection@7ddcb245]
2022-12-11 17:37:03.325 DEBUG 16248 --- [nio-8080-exec-9] o.s.j.d.DataSourceTransactionManager     : Releasing JDBC Connection [org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.ShardingConnection@7ddcb245] after transaction
2022-12-11 17:37:03.326 DEBUG 16248 --- [nio-8080-exec-9] o.s.web.servlet.DispatcherServlet        : Failed to complete request: java.lang.ArithmeticException: / by zero
2022-12-11 17:37:03.326 ERROR 16248 --- [nio-8080-exec-9] o.a.c.c.C.[.[.[.[dispatcherServlet]      : Servlet.service() for servlet [dispatcherServlet] in context with path [/mutildb] threw exception [Request processing failed; nested exception is java.lang.ArithmeticException: / by zero] with root causejava.lang.ArithmeticException: / by zeroat com.chen.service.TransactionService.saveUserAndOrderTrans(TransactionService.java:39) ~[classes/:na]at com.chen.service.TransactionService$$FastClassBySpringCGLIB$$4d99c3a3.invoke() ~[classes/:na]at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.2.12.RELEASE.jar:5.2.12.RELEASE]at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771) ~[spring-aop-5.2.12.RELEASE.jar:5.2.12.RELEASE]at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.2.12.RELEASE.jar:5.2.12.RELEASE]at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749) ~[spring-aop-5.2.12.RELEASE.jar:5.2.12.RELEASE]at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367) ~[spring-tx-5.2.12.RELEASE.jar:5.2.12.RELEASE]at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118) ~[spring-tx-5.2.12.RELEASE.jar:5.2.12.RELEASE]at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.12.RELEASE.jar:5.2.12.RELEASE]at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749) ~[spring-aop-5.2.12.RELEASE.jar:5.2.12.RELEASE]at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691) ~[spring-aop-5.2.12.RELEASE.jar:5.2.12.RELEASE]at com.chen.service.TransactionService$$EnhancerBySpringCGLIB$$f6cc31e0.saveUserAndOrderTrans() ~[classes/:na]at com.chen.rest.TransRest.saveUserAndOrderTrans(TransRest.java:21) ~[classes/:na]at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_272]at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_272]at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_272]at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_272]at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190) ~[spring-web-5.2.12.RELEASE.jar:5.2.12.RELEASE]at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) ~[spring-web-5.2.12.RELEASE.jar:5.2.12.RELEASE]at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105) ~[spring-webmvc-5.2.12.RELEASE.jar:5.2.12.RELEASE]at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:878) ~[spring-webmvc-5.2.12.RELEASE.jar:5.2.12.RELEASE]at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:792) ~[spring-webmvc-5.2.12.RELEASE.jar:5.2.12.RELEASE]at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.2.12.RELEASE.jar:5.2.12.RELEASE]at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040) ~[spring-webmvc-5.2.12.RELEASE.jar:5.2.12.RELEASE]at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943) ~[spring-webmvc-5.2.12.RELEASE.jar:5.2.12.RELEASE]at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.2.12.RELEASE.jar:5.2.12.RELEASE]at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898) ~[spring-webmvc-5.2.12.RELEASE.jar:5.2.12.RELEASE]at javax.servlet.http.HttpServlet.service(HttpServlet.java:626) ~[tomcat-embed-core-9.0.41.jar:4.0.FR]at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.2.12.RELEASE.jar:5.2.12.RELEASE]at javax.servlet.http.HttpServlet.service(HttpServlet.java:733) ~[tomcat-embed-core-9.0.41.jar:4.0.FR]at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-9.0.41.jar:9.0.41]at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.41.jar:9.0.41]at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.41.jar:9.0.41]at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.41.jar:9.0.41]at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.41.jar:9.0.41]at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.2.12.RELEASE.jar:5.2.12.RELEASE]at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.12.RELEASE.jar:5.2.12.RELEASE]at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.41.jar:9.0.41]at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.41.jar:9.0.41]at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.2.12.RELEASE.jar:5.2.12.RELEASE]at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.12.RELEASE.jar:5.2.12.RELEASE]at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.41.jar:9.0.41]at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.41.jar:9.0.41]at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.2.12.RELEASE.jar:5.2.12.RELEASE]at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.12.RELEASE.jar:5.2.12.RELEASE]at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.41.jar:9.0.41]at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.41.jar:9.0.41]at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) ~[tomcat-embed-core-9.0.41.jar:9.0.41]at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) [tomcat-embed-core-9.0.41.jar:9.0.41]at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542) [tomcat-embed-core-9.0.41.jar:9.0.41]at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143) [tomcat-embed-core-9.0.41.jar:9.0.41]at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [tomcat-embed-core-9.0.41.jar:9.0.41]at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) [tomcat-embed-core-9.0.41.jar:9.0.41]at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) [tomcat-embed-core-9.0.41.jar:9.0.41]at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374) [tomcat-embed-core-9.0.41.jar:9.0.41]at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) [tomcat-embed-core-9.0.41.jar:9.0.41]at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:888) [tomcat-embed-core-9.0.41.jar:9.0.41]at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1597) [tomcat-embed-core-9.0.41.jar:9.0.41]at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-9.0.41.jar:9.0.41]at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_272]at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_272]at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.41.jar:9.0.41]at java.lang.Thread.run(Thread.java:748) [na:1.8.0_272]2022-12-11 17:37:03.326 DEBUG 16248 --- [nio-8080-exec-9] o.a.c.c.C.[Tomcat].[localhost]           : Processing ErrorPage[errorCode=0, location=/error]
2022-12-11 17:37:03.327 DEBUG 16248 --- [nio-8080-exec-9] o.s.web.servlet.DispatcherServlet        : "ERROR" dispatch for GET "/mutildb/error", parameters={}
2022-12-11 17:37:03.327 DEBUG 16248 --- [nio-8080-exec-9] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped to org.springframework.boot.autoconfigure.web.servlet.error.BasicErrorController#errorHtml(HttpServletRequest, HttpServletResponse)
2022-12-11 17:37:03.328 DEBUG 16248 --- [nio-8080-exec-9] o.s.w.s.v.ContentNegotiatingViewResolver : Selected 'text/html' given [text/html, text/html;q=0.8]
2022-12-11 17:37:03.328 DEBUG 16248 --- [nio-8080-exec-9] o.s.web.servlet.DispatcherServlet        : Exiting from "ERROR" dispatch, status 500
2022-12-11 17:37:03.328 DEBUG 16248 --- [nio-8080-exec-9] o.a.c.c.C.[.[.[.[dispatcherServlet]      :  Disabling the response for further output
2022-12-11 17:37:03.329 DEBUG 16248 --- [nio-8080-exec-9] o.apache.coyote.http11.Http11Processor   : Socket: [org.apache.tomcat.util.net.NioEndpoint$NioSocketWrapper@731b4256:org.apache.tomcat.util.net.NioChannel@29090279:java.nio.channels.SocketChannel[connected local=/127.0.0.1:8080 remote=/127.0.0.1:60052]], Status in: [OPEN_READ], State out: [CLOSED]
2022-12-11 17:37:03.329 DEBUG 16248 --- [nio-8080-exec-9] o.apache.tomcat.util.threads.LimitLatch  : Counting down[http-nio-8080-exec-9] latch=2
2022-12-11 17:37:03.329 DEBUG 16248 --- [nio-8080-exec-9] org.apache.tomcat.util.net.NioEndpoint   : Calling [org.apache.tomcat.util.net.NioEndpoint@32bebc54].closeSocket([org.apache.tomcat.util.net.NioEndpoint$NioSocketWrapper@731b4256:org.apache.tomcat.util.net.NioChannel@29090279:java.nio.channels.SocketChannel[connected local=/127.0.0.1:8080 remote=/127.0.0.1:60052]])

由日志可知，事务是OK的 事务回滚了。

sharding-jdbc属于jdbc驱动层级的实现方式，对于原有代码是兼容的，原有代码基本不需要改动。

后面有时间研究一下 多数据源事务是如何实现的 以及sql连接操作。