工具及环境：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(ShardingRuleConfiguration shardingRuleConfiguration, 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--------------------------
DruidPlugin source = 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---------------
DruidPlugin target = 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表规则
TableRuleConfiguration orderTableRuleConfig = 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}"));
// 配置规则
ShardingRuleConfiguration shardingRuleConfig = 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 {
public static 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 rights reserved. * * @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(); renderJson("操作成功！"); } }
数据将会根据userid的模自动分库分表
--------------------------完----------------------------------
jfinal官网：http://www.jfinal.com/
sharding-jdbc:https://gitee.com/shardingjdbc/sharding-jdbc
