需求:现在有order表,因为订单较多,如果都放在一个表里面进行,则会出现查询很慢,使用shareding-jdbc进行改进,先分为8张表,后续如果有需求就扩张为16张表,然后32张表,依次类推 依赖: <dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.0</version> <scope>test</scope> </dependency>
<dependency> <groupId>io.shardingjdbc</groupId> <artifactId>sharding-jdbc-core</artifactId> <version>2.0.0.M2</version> </dependency>
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.0.13</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.28</version> </dependency>
<dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-api</artifactId> <version>1.7.7</version> </dependency>
<dependency> <groupId>ch.qos.logback</groupId> <artifactId>logback-classic</artifactId> <version>1.0.13</version> </dependency> </dependencies> 数据库创建 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<String, DataSource> 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<String> doSharding(Collection<String> availableTargetNames, Collection<ShardingValue> 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<String> 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<String> doSharding(Collection<String> availableTargetNames, Collection<ShardingValue> 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<String> 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; }
} |
shareding-jdbc实现自定义规则分表
最新推荐文章于 2024-06-15 09:13:15 发布