shareding分表oracle,shareding-jdbc实现自定义规则分表-CSDN博客

需求:现在有order表，因为订单较多，如果都放在一个表里面进行，则会出现查询很慢，使用shareding-jdbc进行改进，先分为8张表，后续如果有需求就扩张为16张表，然后32张表，依次类推

依赖:

junit

4.0

test

io.shardingjdbc

sharding-jdbc-core

2.0.0.M2

com.alibaba

druid

1.0.13

mysql

mysql-connector-java

5.1.28

org.slf4j

slf4j-api

1.7.7

ch.qos.logback

logback-classic

1.0.13

数据库创建

CREATE TABLE `t_order_x` (

`order_id` varchar(64) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

代码

数据源配置

package com.irisian.sharedjdbc;

import java.sql.SQLException;

import java.util.HashMap;

import java.util.Map;

import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSource;

import io.shardingjdbc.core.api.ShardingDataSourceFactory;

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

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

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

public class DateSourceUtils {

public static DataSource getDataSource() throws SQLException {

// 配置真实数据源

Map dataSourceMap = new HashMap<>();

// 配置第一个数据源

DruidDataSource dataSource1 = new DruidDataSource();

dataSource1.setDriverClassName("com.mysql.jdbc.Driver");

dataSource1.setUrl("jdbc:mysql://localhost:3306/db0");

dataSource1.setUsername("root");

dataSource1.setPassword("123456");

// 将数据库放入到数据库map集合中

dataSourceMap.put("db0", dataSource1);

TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();

orderTableRuleConfig.setLogicTable("t_order");

orderTableRuleConfig.setActualDataNodes(

"db0.t_order_0, db0.t_order_1,db0.t_order_2, db0.t_order_3,db0.t_order_4, db0.t_order_5,db0.t_order_6, db0.t_order_7");

ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();

shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);

shardingRuleConfig.setDefaultTableShardingStrategyConfig(new ComplexShardingStrategyConfiguration("order_id",

"com.irisian.sharedjdbc.OrderShardingTableAlgorithm"));//分表规则，自定义类

// 获取数据源对象

DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig,

new Properties());

return dataSource;

}

分表规则

package com.irisian.sharedjdbc;

import java.util.Collection;

import java.util.LinkedHashSet;

import io.shardingjdbc.core.api.algorithm.sharding.ListShardingValue;

import io.shardingjdbc.core.api.algorithm.sharding.ShardingValue;

import io.shardingjdbc.core.api.algorithm.sharding.complex.ComplexKeysShardingAlgorithm;

public class OrderShardingTableAlgorithm implements ComplexKeysShardingAlgorithm {

@Override

public Collection doSharding(Collection availableTargetNames,

Collection shardingValues) {

System.out.println(availableTargetNames);

//[t_order_0, t_order_1, t_order_2, t_order_3, t_order_4, t_order_5, t_order_6, t_order_7]

System.out.println(shardingValues);

//[ListShardingValue(logicTableName=t_order, columnName=order_id, values=[adsjkgdha])]

Object[] array2 = shardingValues.toArray();

String val=(String)((ListShardingValue)array2[0]).getValues().toArray()[0]; //获取order_id的值

System.out.println("val"+val);

Collection result = new LinkedHashSet<>(availableTargetNames.size());

for (String tableName : availableTargetNames) {

int i = Math.abs(val.hashCode() % 1024); //order_id的值进行hash运算，然后进行绝对值运算

System.err.println(i);

// 0-127 128-255 256-383 384-511 0 1 2 3

// 512-639 640-767 768-895 896-1023 4 5 6 7

if (i >= 0 && i <= 127) {

if (tableName.endsWith("0")) {

result.add(tableName);

}

} else if (i >= 128 && i <= 255) {

if (tableName.endsWith("1")) {

result.add(tableName);

}

} else if (i >= 256 && i <= 383) {

if (tableName.endsWith("2")) {

result.add(tableName);

}

} else if (i >= 384 && i <= 511) {

if (tableName.endsWith("3")) {

result.add(tableName);

}

} else if (i >= 512 && i <= 639) {

if (tableName.endsWith("4")) {

result.add(tableName);

}

} else if (i >= 640 && i <= 767) {

if (tableName.endsWith("5")) {

result.add(tableName);

}

} else if (i >= 768 && i <= 895) {

if (tableName.endsWith("6")) {

result.add(tableName);

}

} else if (i >= 896 && i <= 1023) {

if (tableName.endsWith("7")) {

result.add(tableName);

}

return result;

}

测试类

package com.irisian.sharedjdbc;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import javax.sql.DataSource;

public class Test {

/**

* 插入测试

* @param args

* @throws SQLException

public static void main1(String[] args) throws SQLException {

DataSource dataSource = DateSourceUtils.getDataSource();

Connection conn = dataSource.getConnection();

String sql = "insert into t_order(order_id) values(?)"; // 注意这里使用的是逻辑表

PreparedStatement prep = conn.prepareStatement(sql);

prep.setString(1, "5156564");

prep.execute();

}

/**

* 查询

* @param args

* @throws SQLException

public static void main(String[] args) throws SQLException {

DataSource dataSource = DateSourceUtils.getDataSource();

Connection conn = dataSource.getConnection();

String sql = "select * from t_order"; // 注意这里使用的是逻辑表

PreparedStatement prep = conn.prepareStatement(sql);

ResultSet rst = prep.executeQuery();

while (rst.next()) {

System.out.println(rst.getString(1));

}

分表规则改进

package com.irisian.sharedjdbc;

import java.util.Collection;

import java.util.LinkedHashSet;

import io.shardingjdbc.core.api.algorithm.sharding.ListShardingValue;

import io.shardingjdbc.core.api.algorithm.sharding.ShardingValue;

import io.shardingjdbc.core.api.algorithm.sharding.complex.ComplexKeysShardingAlgorithm;

public class OrderShardingTableAlgorithm implements ComplexKeysShardingAlgorithm {

@Override

public Collection doSharding(Collection availableTargetNames,

Collection shardingValues) {

System.out.println(availableTargetNames);//[t_order_0, t_order_1, t_order_2, t_order_3, t_order_4, t_order_5, t_order_6, t_order_7]

System.out.println(shardingValues);

//[ListShardingValue(logicTableName=t_order, columnName=order_id, values=[adsjkgdha])]

//获取order_id的值

String val=(String)((ListShardingValue)shardingValues.toArray()[0]).getValues().toArray()[0];

//返回表的集合

Collection result = new LinkedHashSet<>(availableTargetNames.size());

Object[] tableNameObj = availableTargetNames.toArray();//获取表名的数组

int i = Math.abs(val.hashCode() % 1024);

System.err.println(i);

if (i >= 0 && i <= 127) {

result.add((String)tableNameObj[0]);

} else if (i >= 128 && i <= 255) {

result.add((String)tableNameObj[1]);

} else if (i >= 256 && i <= 383) {

result.add((String)tableNameObj[2]);

} else if (i >= 384 && i <= 511) {

result.add((String)tableNameObj[3]);

} else if (i >= 512 && i <= 639) {

result.add((String)tableNameObj[4]);

} else if (i >= 640 && i <= 767) {

result.add((String)tableNameObj[5]);

} else if (i >= 768 && i <= 895) {

result.add((String)tableNameObj[6]);

} else if (i >= 896 && i <= 1023) {

result.add((String)tableNameObj[7]);

}

return result;

}