jfinal+sharding-jdbc实现数据分库，分表

工具及环境：idea 2016.2(非必须) 、jdk1.8、jfinal3.3 、sharding-jdbc 3.0.3

简介：JFinal  是基于 Java 语言的极速 WEB + ORM 框架，其核心设计目标是开发迅速、代码量少、学习简单、功能强大、轻量 级、易扩展、Restful。在拥有Java语言所有优势的同时再拥有ruby、python、php等动态语言的开发效率！

         Sharding-JDBC 采用在 JDBC 协议层扩展分库分表，是一个以 jar 形式提供服务的轻量级组件，其核心思路是小而美地完成最核心的事情。

原理：通过自己构造jfinal的数据库plugin包装sharing-jdbc的datasource来，让sharding-jdbc来实现数据库的分表分库操作，从而简单实现欲达到的目的。

一、建立测试数据库环境

1、建立数据

create database sharding_0;  

create database sharding_1;  

2、建测试表

SET FOREIGN_KEY_CHECKS=0;  

-- ----------------------------  

-- Table structure for t_user_0  

-- ----------------------------  

DROP TABLE IF EXISTS `t_user_0`;  

CREATE TABLE `t_user_0` (  

  `id` int(11) NOT NULL AUTO_INCREMENT,  

  `user_id` int(11) NOT NULL,  

  `name` varchar(255) NOT NULL,  

  `age` int(11) NOT NULL,  

  PRIMARY KEY (`id`)  

) ENGINE=InnoDB DEFAULT CHARSET=utf8;  

  

-- ----------------------------  

-- Table structure for t_user_1  

-- ----------------------------  

DROP TABLE IF EXISTS `t_user_1`;  

CREATE TABLE `t_user_1` (  

  `id` int(11) NOT NULL AUTO_INCREMENT,  

  `user_id` int(11) NOT NULL,  

  `name` varchar(255) NOT NULL,  

  `age` int(11) NOT NULL,  

  PRIMARY KEY (`id`)  

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;  

  

-- ----------------------------  

-- Table structure for t_user_02  

-- ----------------------------  

DROP TABLE IF EXISTS `t_user_2`;  

CREATE TABLE `t_user_2` (  

  `id` int(11) NOT NULL AUTO_INCREMENT,  

  `user_id` int(11) NOT NULL,  

  `name` varchar(255) NOT NULL,  

  `age` int(11) NOT NULL,  

  PRIMARY KEY (`id`)  

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;  

二、引入sharding-jdbc的jar包或者maven。本处使用maven

<dependency>

  <groupId>io.shardingjdbc</groupId>

  <artifactId>sharding-jdbc-core</artifactId>

  <version>2.0.3</version>

</dependency>

三、自定义ShardDrudPlugin类（基于drud数据库连接池的，其他数据库连接池类似）.

package cn.qcsy.framework.util;

import com.alibaba.druid.filter.Filter;

import com.alibaba.druid.pool.DruidDataSource;

import com.jfinal.kit.StrKit;

import com.jfinal.plugin.IPlugin;

import com.jfinal.plugin.activerecord.IDataSourceProvider;

import com.jfinal.plugin.druid.DruidPlugin;

import io.shardingjdbc.core.api.ShardingDataSourceFactory;

import io.shardingjdbc.core.api.config.ShardingRuleConfiguration;

import io.shardingjdbc.core.api.config.strategy.InlineShardingStrategyConfiguration;

 

import javax.sql.DataSource;

import java.sql.SQLException;

import java.util.*;

import java.util.concurrent.ConcurrentHashMap;

 

/**

 *Description:自定义的sharding扩展类

 *Copyright: qcsy studio. All rights reserved.

 *

 * @author qcsy

 * @version 1.0

 * @timestamp 2018/4/3.

 */

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(ShardingRuleConfigurationshardingRuleConfiguration, Map<String, DruidPlugin> druidPlugins) {

        this.shardingRuleConfiguration =shardingRuleConfiguration;

        this.druidPlugins = druidPlugins;

        dataSourceMap=new HashMap<String,DataSource>();

    }

 

    public boolean start() {

        //遍历数据源 ，将数据源加入sharding jdbc

       for(Map.Entry<String,DruidPlugin>entry:druidPlugins.entrySet()){

            entry.getValue().start();

            dataSourceMap.put(entry.getKey(),entry.getValue().getDataSource());

        }

        try {

            //获得数据库连接类

            dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap,shardingRuleConfiguration,new ConcurrentHashMap(),new Properties());

        } catch (SQLException e) {

            e.printStackTrace();

        }

        return true;

    }

 

    public boolean stop() {

        for(Map.Entry<String,DruidPlugin> entry:druidPlugins.entrySet()){

            entry.getValue().stop();

            dataSourceMap.put(entry.getKey(),entry.getValue().getDataSource());

        }

        return true;

    }

 

    public DataSource getDataSource() {

        return dataSource;

    }

}

四、配置jfinal的扩展信息

 /**

     * 配置Jfinal plugin扩展

     */

    public void configPlugin(Plugins me) {

       //-----------------配置测试库1--------------------------

 

        DruidPluginsource = new DruidPlugin(PropKit.use(CommonAttribute.DATA_BASE_CONFIG).get("jdbc.source.url"),PropKit.use(CommonAttribute.DATA_BASE_CONFIG).get("jdbc.source.user"),PropKit.use(CommonAttribute.DATA_BASE_CONFIG).get("jdbc.source.password").trim());

       String sourceDriverClass=PropKit.use(CommonAttribute.DATA_BASE_CONFIG).get("jdbc.source.driver","com.mysql.jdbc.Driver");

       source.setDriverClass(sourceDriverClass);

 

        //----------------- 配置目测试库2---------------

        DruidPlugintarget = new DruidPlugin(PropKit.use(CommonAttribute.DATA_BASE_CONFIG).get("jdbc.target.url"),PropKit.use(CommonAttribute.DATA_BASE_CONFIG).get("jdbc.target.user"),PropKit.use(CommonAttribute.DATA_BASE_CONFIG).get("jdbc.target.password").trim());

       String targetDriverClass=PropKit.use(CommonAttribute.DATA_BASE_CONFIG).get("jdbc.target.driver","com.mysql.jdbc.Driver");

       target.setDriverClass(targetDriverClass);

 

 

        //------------------------测试分表分库------------------------------------

        Map<String,DruidPlugin>drudMap=new HashMap();

       drudMap.put("ds_0",source);

       drudMap.put("ds_1",source);

 

       // 配置Order表规则

        TableRuleConfigurationorderTableRuleConfig = new TableRuleConfiguration();

// 关键逻辑表名，将与mappingkit中进行映射

       orderTableRuleConfig.setLogicTable("t_user");

       orderTableRuleConfig.setActualDataNodes("ds_${0..1}.t_user_${[0,1]}");

 

       // 配置分库策略

        orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id","ds_${user_id% 2}"));

 

       // 配置分表策略

        orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id","t_user_${user_id% 2}"));

 

       // 配置规则

        ShardingRuleConfigurationshardingRuleConfig = new ShardingRuleConfiguration();

       shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);

//获得自定义的扩展

       ShardDrudPlugin drudPlugin=new ShardDrudPlugin(shardingRuleConfig,drudMap);

       me.add(drudPlugin);

       ActiveRecordPlugin targetARP = new ActiveRecordPlugin("target",drudPlugin);

//配置数据库方言

// targetARP.setDialect(DatabaseUtil.getDialectByDriverName(targetDriverClass));

        _MappingKit.mapping(targetARP);

       me.add(targetARP);

   }

//mappingki中的映射，需映射逻辑表名

public class _MappingKit {

  

  publicstatic void mapping(ActiveRecordPlugin arp) {

      arp.addMapping("t_user", "id",User01.class);

  }

}

五、测试

package cn.qcsy.framework.bizmodule.transport.controller;

 

import cn.qcsy.framework.bizmodule.transport.service.TransService;

import cn.qcsy.framework.model.User01;

import com.jfinal.core.Controller;

 

/**

 *Description:测试

 *Copyright: © 2017  CSTC. All rightsreserved.

 *

 * @author qcsy

 * @version 1.0

 * @timestamp 2018/4/3.

 */

public class TestGetTrans extends Controller {

    //获取服务类

   private TransService transService=new TransService();

    public void getall(){

        User01 user01=new User01();

        user01.setId(2);

        user01.setUserId(2);

        user01.setName("4");

        user01.setAge(4);

        user01.save();

//       transService.transEntry();

       renderJson("操作成功！");

    }

}

据将会根据userid的模自动分库分表

------------------------------------------------------完-------------------------------------------------------

jfinal官网：http://www.jfinal.com/

sharding-jdbc:https://gitee.com/shardingjdbc/sharding-jdbc