1.      项目遇到瓶颈，A库获取标识数据，新增到B库的同时要更新A库标识。

1）项目初期，考虑不采用事务，如果按照这样设计存在重复新增的可能以及批量数据的拆分处理。

Db.use("B").batchSave("",new ArrayList<Record>(),100);
Db.use("A").update("");

2）后期采用jfinal中的单数据库事务，按照正常情況事务报错B库会正常回滚，A库数据库操作为最后更新sql失败不会提交。

Db.use("B").tx(()->{
    Db.use("B").batchSave("",new ArrayList<>(),100);
    Db.use("A").update("");
    return true;
});

但经过时间验证后发现：2）的方法update到return true;之间存在运行报错的可能性。

 2.      尝试使用数据库分布式事务来解决多数据库操作问题。

1）建立测试库

create database test1;
create database test2;

2）建立测试表(test1和test2)

CREATE TABLE `user` (
   `id` int(11) NOT NULL,
   `name` varchar(255) DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3）加入ShardingSphere的jar

<dependency>
     <groupId>io.shardingsphere</groupId>
     <artifactId>sharding-jdbc-core</artifactId>
     <version>3.1.0.M1</version>
 </dependency>
 <dependency>
     <groupId>io.shardingsphere</groupId>
     <artifactId>sharding-transaction-2pc-xa</artifactId>
     <version>3.1.0.M1</version>
 </dependency>

4）定义插件ShardDrudPlugin类

import com.jfinal.plugin.IPlugin;
 import com.jfinal.plugin.activerecord.IDataSourceProvider;
 import com.jfinal.plugin.druid.DruidPlugin;
 import io.shardingsphere.api.config.rule.ShardingRuleConfiguration;
 import io.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
 
 import javax.sql.DataSource;
 import java.sql.SQLException;
 import java.util.HashMap;
 import java.util.Map;
 import java.util.Properties;
 import java.util.concurrent.ConcurrentHashMap;
 
 public class ShardDrudPlugin implements IPlugin, IDataSourceProvider {
 
     //分表分库的rule
     ShardingRuleConfiguration shardingRuleConfiguration;
     //数据源map
     Map<String, DruidPlugin> druidPlugins;
     //原数据库连接源map
     Map<String, DataSource> dataSourceMap;
     //最终sharding-jdbc封装后的数据库连接源
     DataSource dataSource;
 
     public ShardDrudPlugin(ShardingRuleConfiguration shardingRuleConfiguration, Map<String, DruidPlugin> druidPlugins) {
         this.shardingRuleConfiguration = shardingRuleConfiguration;
         this.druidPlugins = druidPlugins;
         dataSourceMap=new HashMap<>();
     }
 
     @Override
     public boolean start() {
         //遍历数据源 ，将数据源加入sharding jdbc
         druidPlugins.forEach((s,dp)->{
             dp.start();
             dataSourceMap.put(s,dp.getDataSource());
         });
         try {
             //获得数据库连接类
             dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap,shardingRuleConfiguration, new ConcurrentHashMap(), new Properties());
         } catch (SQLException e) {
             e.printStackTrace();
         }
         return true;
     }
 
     @Override
     public boolean stop() {
         //遍历数据源 ，将数据源移除sharding jdbc
         druidPlugins.forEach((s,dp)->{
             dataSourceMap.remove(s,dp.getDataSource());
             dp.stop();
         });
         return true;
     }
 
     @Override
     public DataSource getDataSource() {
         return dataSource;
     }
 }

4）设置配置信息

private TableRuleConfiguration getTableRuleConfig(TableList table) {
         TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();
         orderTableRuleConfig.setLogicTable(table.getLogicTable());
         orderTableRuleConfig.setActualDataNodes(table.getActualDataNodes());
         return orderTableRuleConfig;
     }
 
     @Override
     public void configPlugin(Plugins me) {
         loadPropertyFile("config.properties");
 
 //        TransactionTypeHolder.set(TransactionType.XA);
         TransactionTypeHolder.set(TransactionType.BASE);
         DruidPlugin dp1 = new DruidPlugin(
                 getProperty("test1_jdbcUrl"),
                 getProperty("test1_user"),
                 getProperty("test1_password"),
                 getProperty("test1_driver"));
 
         DruidPlugin dp2 = new DruidPlugin(
                 getProperty("test2_jdbcUrl"),
                 getProperty("test2_user"),
                 getProperty("test2_password"),
                 getProperty("test2_driver"));
 
         Map<String,DruidPlugin> drudMap=new HashMap();
         drudMap.put("test1",dp1);
         drudMap.put("test2",dp2);
 
 
         // 配置规则
         ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
         // 配置表规则
         Collection<TableRuleConfiguration> tableRuleConfigs = shardingRuleConfig.getTableRuleConfigs();
         tableRuleConfigs.add(getTableRuleConfig(TableList.test1_user));
         tableRuleConfigs.add(getTableRuleConfig(TableList.test2_user));
 
         //获得自定义的扩展
         ShardDrudPlugin drudPlugin=new ShardDrudPlugin(shardingRuleConfig,drudMap);
         me.add(drudPlugin);
         ActiveRecordPlugin targetARP = new ActiveRecordPlugin("shard",drudPlugin);
         targetARP.setContainerFactory(new CaseInsensitiveContainerFactory());
         targetARP.setShowSql(true);
         me.add(targetARP);
     }

使用的TableList枚举类

public enum TableList {
     //映射test1的user表
     test1_user("user1","test1.user"),
     //映射test2的user表
     test2_user("user2","test2.user");
 
     private final String logicTable;
     private final String actualDataNodes;
 
     TableList(String logicTable, String actualDataNodes) {
         this.logicTable = logicTable;
         this.actualDataNodes = actualDataNodes;
     }
 
     public String getLogicTable() {
         return logicTable;
     }
 
     public String getActualDataNodes() {
         return actualDataNodes;
     }
 }

5）测试代码

Record  record1=new Record();
 record1.set("id",1);
 record1.set("name","test");
 
 Record  record2=new Record();
 record2.set("id",2);
 record2.set("name","test");
 
 Db.use("shard").tx(()->{
     Db.use("shard").save(TableList.test1_user.getLogicTable(),record1);
     Db.use("shard").save(TableList.test2_user.getLogicTable(),record2);
     return false;
 });
 
 Db.use("shard").save(TableList.test1_user.getLogicTable(),record1);
 Db.use("shard").save(TableList.test2_user.getLogicTable(),record2);

6）待完善问题

能否通用设置为特定的表前缀下走特定的数据库。

参考资料：

配置参考：http://www.jfinal.com/share/793?p=2#reply_start

描述参考：https://www.jianshu.com/p/f9d461ef857b?utm_source=oschina-app

ShardingSphere：http://shardingsphere.io/document/current/cn/manual/sharding-jdbc/usage/transaction/