需求:现在有order表,因为订单较多,如果都放在一个表里面进行,则会出现查询很慢,使用shareding-jdbc进行改进,先分为8张表,后续如果有需求就扩张为16张表,然后32张表,依次类推
依赖:
junit
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 {
// 配置真实数据源
MapdataSourceMap = 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 CollectiondoSharding(CollectionavailableTargetNames,
CollectionshardingValues) {
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);
Collectionresult = 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 CollectiondoSharding(CollectionavailableTargetNames,
CollectionshardingValues) {
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];
//返回表的集合
Collectionresult = 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;
}
}