shareding-jdbc实现自定义规则分表

矫情吗；* 2022-05-25 03:08 171阅读 0赞

<table> 
 <tbody> 
 <tr> 
 <td>需求:现在有order表，因为订单较多，如果都放在一个表里面进行，则会出现查询很慢，使用shareding-jdbc进行改进，先分为8张表，后续如果有需求就扩张为16张表，然后32张表，依次类推依赖:&lt;dependencies&gt;&lt;dependency&gt;&lt;groupId&gt;junit&lt;/groupId&gt;&lt;artifactId&gt;junit&lt;/artifactId&gt;&lt;version&gt;4.0&lt;/version&gt;&lt;scope&gt;test&lt;/scope&gt;&lt;/dependency&gt;&nbsp;&lt;dependency&gt;&lt;groupId&gt;io.shardingjdbc&lt;/groupId&gt;&lt;artifactId&gt;sharding-jdbc-core&lt;/artifactId&gt;&lt;version&gt;2.0.0.M2&lt;/version&gt;&lt;/dependency&gt;&nbsp;&lt;dependency&gt;&lt;groupId&gt;com.alibaba&lt;/groupId&gt;&lt;artifactId&gt;druid&lt;/artifactId&gt;&lt;version&gt;1.0.13&lt;/version&gt;&lt;/dependency&gt;&lt;dependency&gt;&lt;groupId&gt;mysql&lt;/groupId&gt;&lt;artifactId&gt;mysql-connector-java&lt;/artifactId&gt;&lt;version&gt;5.1.28&lt;/version&gt;&lt;/dependency&gt;&nbsp;&lt;dependency&gt;&lt;groupId&gt;org.slf4j&lt;/groupId&gt;&lt;artifactId&gt;slf4j-api&lt;/artifactId&gt;&lt;version&gt;1.7.7&lt;/version&gt;&lt;/dependency&gt;&nbsp;&lt;dependency&gt;&lt;groupId&gt;ch.qos.logback&lt;/groupId&gt;&lt;artifactId&gt;logback-classic&lt;/artifactId&gt;&lt;version&gt;1.0.13&lt;/version&gt;&lt;/dependency&gt;&lt;/dependencies&gt;数据库创建CREATE TABLE `t_order_x` (&nbsp;&nbsp;`order_id` varchar(64) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8&nbsp;代码数据源配置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&lt;String, DataSource&gt; dataSourceMap = new HashMap&lt;&gt;();// 配置第一个数据源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 { @Overridepublic Collection&lt;String&gt; doSharding(Collection&lt;String&gt; availableTargetNames,Collection&lt;ShardingValue&gt; 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]; &nbsp;&nbsp;//获取order_id的值System.out.println("val"+val);Collection&lt;String&gt; result = new LinkedHashSet&lt;&gt;(availableTargetNames.size());for (String tableName : availableTargetNames) { int i = Math.abs(val.hashCode() % 1024); &nbsp;&nbsp;&nbsp;&nbsp;//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 7if (i &gt;= 0 &amp;&amp; i &lt;= 127) { if (tableName.endsWith("0")) { result.add(tableName);}&nbsp;} else if (i &gt;= 128 &amp;&amp; i &lt;= 255) { if (tableName.endsWith("1")) { result.add(tableName);}&nbsp;} else if (i &gt;= 256 &amp;&amp; i &lt;= 383) { if (tableName.endsWith("2")) { result.add(tableName);}&nbsp;} else if (i &gt;= 384 &amp;&amp; i &lt;= 511) { if (tableName.endsWith("3")) { result.add(tableName);}&nbsp;} else if (i &gt;= 512 &amp;&amp; i &lt;= 639) { if (tableName.endsWith("4")) { result.add(tableName);}&nbsp;} else if (i &gt;= 640 &amp;&amp; i &lt;= 767) { if (tableName.endsWith("5")) { result.add(tableName);}&nbsp;} else if (i &gt;= 768 &amp;&amp; i &lt;= 895) { if (tableName.endsWith("6")) { result.add(tableName);}&nbsp;} else if (i &gt;= 896 &amp;&amp; i &lt;= 1023) { if (tableName.endsWith("7")) { result.add(tableName);}&nbsp;}}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 { &nbsp;/**&nbsp;* 插入测试&nbsp;* @param args&nbsp;* @throws SQLException&nbsp;*/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();}&nbsp;/**&nbsp;* 查询&nbsp;* @param args&nbsp;* @throws SQLException&nbsp;*/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));}}&nbsp;}&nbsp;分表规则改进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;&nbsp;public class OrderShardingTableAlgorithm implements ComplexKeysShardingAlgorithm { &nbsp;@Overridepublic Collection&lt;String&gt; doSharding(Collection&lt;String&gt; availableTargetNames,Collection&lt;ShardingValue&gt; 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&lt;String&gt; result = new LinkedHashSet&lt;&gt;(availableTargetNames.size());Object[] tableNameObj = availableTargetNames.toArray();//获取表名的数组int i = Math.abs(val.hashCode() % 1024);System.err.println(i);if (i &gt;= 0 &amp;&amp; i &lt;= 127) { result.add((String)tableNameObj[0]);} else if (i &gt;= 128 &amp;&amp; i &lt;= 255) { result.add((String)tableNameObj[1]);&nbsp;} else if (i &gt;= 256 &amp;&amp; i &lt;= 383) { result.add((String)tableNameObj[2]);&nbsp;} else if (i &gt;= 384 &amp;&amp; i &lt;= 511) { result.add((String)tableNameObj[3]);&nbsp;} else if (i &gt;= 512 &amp;&amp; i &lt;= 639) { result.add((String)tableNameObj[4]);&nbsp;} else if (i &gt;= 640 &amp;&amp; i &lt;= 767) { result.add((String)tableNameObj[5]);&nbsp;} else if (i &gt;= 768 &amp;&amp; i &lt;= 895) { result.add((String)tableNameObj[6]);&nbsp;} else if (i &gt;= 896 &amp;&amp; i &lt;= 1023) { result.add((String)tableNameObj[7]);}return result;}&nbsp;}</td> 
 </tr> 
 </tbody> 
</table>