摸索了很久才搞定，现在分享给需要的兄弟们。
pom.xml 引入依赖:
<dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>4.0.0-RC1</version> </dependency>
在数据库里建立6张表：
userbalancechange，userbalancechange0..4
CREATE TABLE `userbalancechange` ( `id` BIGINT(20) NOT NULL, `userId` BIGINT(20) DEFAULT NULL COMMENT '用户id', `gameType` INT(11) DEFAULT NULL COMMENT '游戏类型', `createDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '生成时间', `balance` DECIMAL(20,8) DEFAULT NULL COMMENT '金额变动', PRIMARY KEY (`id`) )
建好后，配置数据源。新建ShardingJdbcConfig:
import com.jfinal.plugin.druid.DruidPlugin;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
public class ShardingJdbcConfig {
//分表分库的rule
private ShardingRuleConfiguration shardingRuleConfiguration;
//数据源map
private Map<String, DruidPlugin> druidPlugins;
//原数据库连接源map
private Map<String, DataSource> dataSourceMap;
//最终sharding-jdbc封装后的数据库连接源
private DataSource dataSource;
public ShardingJdbcConfig(ShardingRuleConfiguration shardingRuleConfiguration, Map<String, DruidPlugin> druidPlugins) {
this.shardingRuleConfiguration =shardingRuleConfiguration;
this.druidPlugins = druidPlugins;
dataSourceMap=new HashMap<>();
}
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 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;
}
}再ActiveRecordConfig：
import com.facebook.sz.yjzj.model.model._MappingKit;
import com.jfinal.plugin.activerecord.ActiveRecordPlugin;
import com.jfinal.plugin.druid.DruidPlugin;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;
import javax.annotation.PostConstruct;
import java.util.HashMap;
import java.util.Map;
@Configuration
public class ActiveRecordConfig {
@Value("${spring.datasource.url}")
private String jdbcUrl;
@Value("${spring.datasource.username}")
private String username;
@Value("${spring.datasource.password}")
private String password;
@PostConstruct
public void initJDBC() {
DruidPlugin dp = new DruidPlugin(jdbcUrl, username, password);
Map<String,DruidPlugin> drudMap = new HashMap();
drudMap.put("ds_0",dp);
// 配置规则
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTableRuleConfigs().add(this.setTableRule("userbalancechange","ds_0.userbalancechange${0..4}"));
//获得自定义的扩展
ShardingJdbcConfig shardingJdbcConfig = new ShardingJdbcConfig(shardingRuleConfig,drudMap);
// 与 jfinal web 环境唯一的不同是要手动调用一次相关插件的start()方法
shardingJdbcConfig.start();
ActiveRecordPlugin arp = new ActiveRecordPlugin(shardingJdbcConfig.getDataSource());
_MappingKit.mapping(arp);
arp.setShowSql(true);
arp.start();
}
private TableRuleConfiguration setTableRule(String tableName,String actualDataNodes){
// 配置Order表规则
TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration(tableName,actualDataNodes);
// 配置分表策略
orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("id",tableName+"${id % 5}"));
return orderTableRuleConfig;
}
}做好上面的配置后，直接_JFinalDemoGenerator生成对应的model类。mappkit的内容：
import com.jfinal.plugin.activerecord.ActiveRecordPlugin;
/**
* Generated by JFinal, do not modify this file.
* <pre>
* Example:
* public void configPlugin(Plugins me) {
* ActiveRecordPlugin arp = new ActiveRecordPlugin(...);
* _MappingKit.mapping(arp);
* me.add(arp);
* }
* </pre>
*/
public class _MappingKit {
public static void mapping(ActiveRecordPlugin arp) {
arp.addMapping("userbalancechange", "id", Userbalancechange.class);
arp.addMapping("userbalancechange0", "id", Userbalancechange0.class);
arp.addMapping("userbalancechange1", "id", Userbalancechange1.class);
arp.addMapping("userbalancechange2", "id", Userbalancechange2.class);
arp.addMapping("userbalancechange3", "id", Userbalancechange3.class);
arp.addMapping("userbalancechange4", "id", Userbalancechange4.class);
}
}这里自动生成的配置不影响使用，不用管。反而多加了个逻辑表对应的空表[userbalancechange]，更方便使用。
测试插入：
for(int i = 0; i < 15; i++){
Userbalancechange ubc = new Userbalancechange();
ubc.setId(idService.genId());
ubc.setUserId(userId);
ubc.setCreateDate(new Date());
ubc.setBalance(balance);
ubc.setGameType(gameType);
ubc.save();
}查看数据库对应的表，发现15条数据已经按照我们配置的分表策略插入到了5张表里。
测试分页：
String from = " from userbalancechange where userId = ? ";
String totalRowSql = "select count(*) " + from;
String findSql = "select * " + from + " order by id";
Page<Userbalancechange> byPage = dao.paginateByFullSql(pageIndex, pageSize, totalRowSql, findSql,new Object[]{user.getId().longValue()});直接封装成了Userbalancechange对象，不必像使用Db.pageinate那样，返回的是Record类型，还要做二次转换。
返回示例：
{
"code": 200,
"msg": "成功",
"data": {
"list": [
{
"id": 2456656055980000253,
"userId": 2455268691349079037,
"gameType": 2,
"balance": 0,
"createDate": "2019-06-14 15:26:36"
},
{
"id": 2456656055980001277,
"userId": 2455268691349079037,
"gameType": 2,
"balance": 0,
"createDate": "2019-06-14 15:26:36"
},
{
"id": 2456656055980002301,
"userId": 2455268691349079037,
"gameType": 2,
"balance": 0,
"createDate": "2019-06-14 15:26:36"
},
{
"id": 2456656055980003325,
"userId": 2455268691349079037,
"gameType": 2,
"balance": 0,
"createDate": "2019-06-14 15:26:36"
},
{
"id": 2456656055980004349,
"userId": 2455268691349079037,
"gameType": 2,
"balance": 0,
"createDate": "2019-06-14 15:26:36"
},
{
"id": 2456656055980005373,
"userId": 2455268691349079037,
"gameType": 2,
"balance": 0,
"createDate": "2019-06-14 15:26:36"
},
{
"id": 2456656055980006397,
"userId": 2455268691349079037,
"gameType": 2,
"balance": 0,
"createDate": "2019-06-14 15:26:36"
},
{
"id": 2456656055980007421,
"userId": 2455268691349079037,
"gameType": 2,
"balance": 0,
"createDate": "2019-06-14 15:26:36"
},
{
"id": 2456656055980008445,
"userId": 2455268691349079037,
"gameType": 2,
"balance": 0,
"createDate": "2019-06-14 15:26:36"
},
{
"id": 2456656055980009469,
"userId": 2455268691349079037,
"gameType": 2,
"balance": 0,
"createDate": "2019-06-14 15:26:36"
}
],
"pageNumber": 1,
"pageSize": 10,
"totalPage": 2,
"totalRow": 14,
"firstPage": true,
"lastPage": false
}
}以上算是一个完整的使用实列，分享给有需要的，正在被分表所折磨的兄弟们。
