本文内容参考Sharding-JDBC官方文档
规则配置
| 节点 | IP | 数据库名 |
|---|---|---|
| 主节点 | 192.168.3.112 | ds_master |
| 从节点 | 192.168.3.113 | ds_slave_0 |
| 从节点 | 192.168.3.114 | ds_slave_1 |
private static ShardingDataSource getShardingDataSource() {
DataSourceRule dataSourceRule = new DataSourceRule(createDataSourceMap());
TableRule orderTableRule = TableRule.builder("t_order").actualTables(Arrays.asList("t_order_0", "t_order_1")).dataSourceRule(dataSourceRule).build();
TableRule orderItemTableRule = TableRule.builder("t_order_item").actualTables(Arrays.asList("t_order_item_0", "t_order_item_1")).dataSourceRule(dataSourceRule).build();
ShardingRule shardingRule = ShardingRule.builder().dataSourceRule(dataSourceRule).tableRules(Arrays.asList(orderTableRule, orderItemTableRule))
.bindingTableRules(Collections.singletonList(new BindingTableRule(Arrays.asList(orderTableRule, orderItemTableRule))))
// .databaseShardingStrategy(new DatabaseShardingStrategy("user_id", new ModuloDatabaseShardingAlgorithm()))
.tableShardingStrategy(new TableShardingStrategy("order_id", new ModuloTableShardingAlgorithm())).build();
return new ShardingDataSource(shardingRule);
}
private static Map<String, DataSource> createDataSourceMap() {
Map<String, DataSource> result = new HashMap<>(2);
result.put("ds", MasterSlaveDataSourceFactory.createDataSource("ds", createDataSource("192.168.3.112",3306,"ds_master"), createDataSource("192.168.3.113",3306,"ds_slave_0"), createDataSource("192.168.3.114",3306,"ds_slave_1")));
return result;
}
private static DataSource createDataSource(final String ip,final Integer port,final String database) {
BasicDataSource result = new BasicDataSource();
result.setDriverClassName(com.mysql.jdbc.Driver.class.getName());
result.setUrl(String.format("jdbc:mysql://%s:%d/%s", ip,port,database));
result.setUsername("ubuntu");
result.setPassword("ubuntu");
return result;
}
sql导入
将该段sql放入到3个库中执行
master
CREATE SCHEMA IF NOT EXISTS `ds_master`;
CREATE TABLE IF NOT EXISTS `ds_master`.`t_order_0` (`order_id` INT NOT NULL, `user_id` INT NOT NULL, `status` VARCHAR(50), PRIMARY KEY (`order_id`));
CREATE TABLE IF NOT EXISTS `ds_master`.`t_order_1` (`order_id` INT NOT NULL, `user_id` INT NOT NULL, `status` VARCHAR(50), PRIMARY KEY (`order_id`));
CREATE TABLE IF NOT EXISTS `ds_master`.`t_order_item_0` (`item_id` INT NOT NULL, `order_id` INT NOT NULL, `user_id` INT NOT NULL, PRIMARY KEY (`item_id`));
CREATE TABLE IF NOT EXISTS `ds_master`.`t_order_item_1` (`item_id` INT NOT NULL, `order_id` INT NOT NULL, `user_id` INT NOT NULL, PRIMARY KEY (`item_id`));
INSERT INTO `ds_master`.`t_order_0` VALUES (1000, 10, 'INIT_MASTER');
INSERT INTO `ds_master`.`t_order_0` VALUES (1002, 10, 'INIT_MASTER');
INSERT INTO `ds_master`.`t_order_0` VALUES (1004, 10, 'INIT_MASTER');
INSERT INTO `ds_master`.`t_order_0` VALUES (1006, 10, 'INIT_MASTER');
INSERT INTO `ds_master`.`t_order_0` VALUES (1008, 10, 'INIT_MASTER');
INSERT INTO `ds_master`.`t_order_item_0` VALUES (100001, 1000, 10);
INSERT INTO `ds_master`.`t_order_item_0` VALUES (100201, 1002, 10);
INSERT INTO `ds_master`.`t_order_item_0` VALUES (100401, 1004, 10);
INSERT INTO `ds_master`.`t_order_item_0` VALUES (100601, 1006, 10);
INSERT INTO `ds_master`.`t_order_item_0` VALUES (100801, 1008, 10);
INSERT INTO `ds_master`.`t_order_1` VALUES (1001, 10, 'INIT_MASTER');
INSERT INTO `ds_master`.`t_order_1` VALUES (1003, 10, 'INIT_MASTER');
INSERT INTO `ds_master`.`t_order_1` VALUES (1005, 10, 'INIT_MASTER');
INSERT INTO `ds_master`.`t_order_1` VALUES (1007, 10, 'INIT_MASTER');
INSERT INTO `ds_master`.`t_order_1` VALUES (1009, 10, 'INIT_MASTER');
INSERT INTO `ds_master`.`t_order_item_1` VALUES (100101, 1001, 10);
INSERT INTO `ds_master`.`t_order_item_1` VALUES (100301, 1003, 10);
INSERT INTO `ds_master`.`t_order_item_1` VALUES (100501, 1005, 10);
INSERT INTO `ds_master`.`t_order_item_1` VALUES (100701, 1007, 10);
INSERT INTO `ds_master`.`t_order_item_1` VALUES (100901, 1009, 10);
slave1
CREATE SCHEMA IF NOT EXISTS `ds_slave_0`;
CREATE TABLE IF NOT EXISTS `ds_slave_0`.`t_order_0` (`order_id` INT NOT NULL, `user_id` INT NOT NULL, `status` VARCHAR(50), PRIMARY KEY (`order_id`));
CREATE TABLE IF NOT EXISTS `ds_slave_0`.`t_order_1` (`order_id` INT NOT NULL, `user_id` INT NOT NULL, `status` VARCHAR(50), PRIMARY KEY (`order_id`));
CREATE TABLE IF NOT EXISTS `ds_slave_0`.`t_order_item_0` (`item_id` INT NOT NULL, `order_id` INT NOT NULL, `user_id` INT NOT NULL, PRIMARY KEY (`item_id`));
CREATE TABLE IF NOT EXISTS `ds_slave_0`.`t_order_item_1` (`item_id` INT NOT NULL, `order_id` INT NOT NULL, `user_id` INT NOT NULL, PRIMARY KEY (`item_id`));
INSERT INTO `ds_slave_0`.`t_order_0` VALUES (1000, 10, 'INIT_SLAVE_0');
INSERT INTO `ds_slave_0`.`t_order_0` VALUES (1002, 10, 'INIT_SLAVE_0');
INSERT INTO `ds_slave_0`.`t_order_0` VALUES (1004, 10, 'INIT_SLAVE_0');
INSERT INTO `ds_slave_0`.`t_order_0` VALUES (1006, 10, 'INIT_SLAVE_0');
INSERT INTO `ds_slave_0`.`t_order_0` VALUES (1008, 10, 'INIT_SLAVE_0');
INSERT INTO `ds_slave_0`.`t_order_item_0` VALUES (100001, 1000, 10);
INSERT INTO `ds_slave_0`.`t_order_item_0` VALUES (100201, 1002, 10);
INSERT INTO `ds_slave_0`.`t_order_item_0` VALUES (100401, 1004, 10);
INSERT INTO `ds_slave_0`.`t_order_item_0` VALUES (100601, 1006, 10);
INSERT INTO `ds_slave_0`.`t_order_item_0` VALUES (100801, 1008, 10);
INSERT INTO `ds_slave_0`.`t_order_1` VALUES (1001, 10, 'INIT_SLAVE_0');
INSERT INTO `ds_slave_0`.`t_order_1` VALUES (1003, 10, 'INIT_SLAVE_0');
INSERT INTO `ds_slave_0`.`t_order_1` VALUES (1005, 10, 'INIT_SLAVE_0');
INSERT INTO `ds_slave_0`.`t_order_1` VALUES (1007, 10, 'INIT_SLAVE_0');
INSERT INTO `ds_slave_0`.`t_order_1` VALUES (1009, 10, 'INIT_SLAVE_0');
INSERT INTO `ds_slave_0`.`t_order_item_1` VALUES (100101, 1001, 10);
INSERT INTO `ds_slave_0`.`t_order_item_1` VALUES (100301, 1003, 10);
INSERT INTO `ds_slave_0`.`t_order_item_1` VALUES (100501, 1005, 10);
INSERT INTO `ds_slave_0`.`t_order_item_1` VALUES (100701, 1007, 10);
INSERT INTO `ds_slave_0`.`t_order_item_1` VALUES (100901, 1009, 10);
slave2
CREATE SCHEMA IF NOT EXISTS `ds_slave_1`;
CREATE TABLE IF NOT EXISTS `ds_slave_1`.`t_order_0` (`order_id` INT NOT NULL, `user_id` INT NOT NULL, `status` VARCHAR(50), PRIMARY KEY (`order_id`));
CREATE TABLE IF NOT EXISTS `ds_slave_1`.`t_order_1` (`order_id` INT NOT NULL, `user_id` INT NOT NULL, `status` VARCHAR(50), PRIMARY KEY (`order_id`));
CREATE TABLE IF NOT EXISTS `ds_slave_1`.`t_order_item_0` (`item_id` INT NOT NULL, `order_id` INT NOT NULL, `user_id` INT NOT NULL, PRIMARY KEY (`item_id`));
CREATE TABLE IF NOT EXISTS `ds_slave_1`.`t_order_item_1` (`item_id` INT NOT NULL, `order_id` INT NOT NULL, `user_id` INT NOT NULL, PRIMARY KEY (`item_id`));
INSERT INTO `ds_slave_1`.`t_order_0` VALUES (1100, 11, 'INIT_SLAVE_1');
INSERT INTO `ds_slave_1`.`t_order_0` VALUES (1102, 11, 'INIT_SLAVE_1');
INSERT INTO `ds_slave_1`.`t_order_0` VALUES (1104, 11, 'INIT_SLAVE_1');
INSERT INTO `ds_slave_1`.`t_order_0` VALUES (1106, 11, 'INIT_SLAVE_1');
INSERT INTO `ds_slave_1`.`t_order_0` VALUES (1108, 11, 'INIT_SLAVE_1');
INSERT INTO `ds_slave_1`.`t_order_item_0` VALUES (110001, 1100, 11);
INSERT INTO `ds_slave_1`.`t_order_item_0` VALUES (110201, 1102, 11);
INSERT INTO `ds_slave_1`.`t_order_item_0` VALUES (110401, 1104, 11);
INSERT INTO `ds_slave_1`.`t_order_item_0` VALUES (110601, 1106, 11);
INSERT INTO `ds_slave_1`.`t_order_item_0` VALUES (110801, 1108, 11);
INSERT INTO `ds_slave_1`.`t_order_1` VALUES (1101, 11, 'INIT_SLAVE_1');
INSERT INTO `ds_slave_1`.`t_order_1` VALUES (1103, 11, 'INIT_SLAVE_1');
INSERT INTO `ds_slave_1`.`t_order_1` VALUES (1105, 11, 'INIT_SLAVE_1');
INSERT INTO `ds_slave_1`.`t_order_1` VALUES (1107, 11, 'INIT_SLAVE_1');
INSERT INTO `ds_slave_1`.`t_order_1` VALUES (1109, 11, 'INIT_SLAVE_1');
INSERT INTO `ds_slave_1`.`t_order_item_1` VALUES (110101, 1101, 11);
INSERT INTO `ds_slave_1`.`t_order_item_1` VALUES (110301, 1103, 11);
INSERT INTO `ds_slave_1`.`t_order_item_1` VALUES (110501, 1105, 11);
INSERT INTO `ds_slave_1`.`t_order_item_1` VALUES (110701, 1107, 11);
INSERT INTO `ds_slave_1`.`t_order_item_1` VALUES (110901, 1109, 11);
编写查询代码
select()
DataSource dataSource = getShardingDataSource();
printSimpleSelect(dataSource,10,1001);
printSimpleSelect(dataSource,10,1002);
printSimpleSelect(dataSource,10,1003);
printSimpleSelect(dataSource,10,1004);
private static void printSimpleSelect(final DataSource dataSource,final Integer userId,final Integer orderId) throws SQLException {
String sql = "SELECT i.*,o.status FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.user_id=? AND o.order_id=?";
try (
Connection conn = dataSource.getConnection();
PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
preparedStatement.setInt(1, userId);
preparedStatement.setInt(2, orderId);
try (ResultSet rs = preparedStatement.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getInt(1)+"\t"+rs.getInt(2)+"\t"+rs.getInt(3)+"\t"+rs.getString(4));
}
}
}
}
select.png
gourpby()
DataSource dataSource = getShardingDataSource();
printGroupBy(dataSource);
private static void printGroupBy(final DataSource dataSource) throws SQLException {
String sql = "SELECT o.user_id, COUNT(*) FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id GROUP BY o.user_id";
try (
Connection conn = dataSource.getConnection();
PreparedStatement preparedStatement = conn.prepareStatement(sql)
) {
ResultSet rs = preparedStatement.executeQuery();
while (rs.next()) {
System.out.println("user_id: " + rs.getInt(1) + ", count: " + rs.getInt(2));
}
}
}
groupby.png
hintSimpleSelect()
DataSource dataSource = getShardingDataSource();
printHintSimpleSelect(dataSource,10,1001);
printHintSimpleSelect(dataSource,10,1002);
printHintSimpleSelect(dataSource,10,1003);
printHintSimpleSelect(dataSource,10,1004);
private static void printHintSimpleSelect(final DataSource dataSource,final Integer userId,final Integer orderId) throws SQLException {
String sql = "SELECT i.*,o.status FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id";
try (
HintManager hintManager = HintManager.getInstance();
Connection conn = dataSource.getConnection();
PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
hintManager.addDatabaseShardingValue("t_order", "user_id", userId);
hintManager.addTableShardingValue("t_order", "order_id", orderId);
try (ResultSet rs = preparedStatement.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getInt(1)+"\t"+rs.getInt(2)+"\t"+rs.getInt(3)+"\t"+rs.getString(4));
}
}
}
}
hint_select.png
insert
Main.Order order = new Main.Order();
Main.OrderItem order_item = new Main.OrderItem();
Integer orderId = 2000;
Integer userId = 20;
Integer itemId = 200020;
order.setOrder_id(orderId);
order.setUser_id(userId);
order.setStatus("insert");
order_item.setItem_id(itemId);
order_item.setOrder_id(orderId);
order_item.setUser_id(userId);
insertData(dataSource, order, order_item);
private static void insertData(final DataSource dataSource,final Order order,final OrderItem order_item){
String sql_order = "INSERT INTO `t_order` (`order_id`, `user_id`, `status`) VALUES (?, ?, ?)";
String sql_order_item = "INSERT INTO `t_order_item` (`item_id`, `order_id`, `user_id`) VALUES (?, ?, ?)";
try (Connection connection = dataSource.getConnection()) {
PreparedStatement preparedStatement = connection.prepareStatement(sql_order);
preparedStatement.setInt(1, order.getOrder_id());
preparedStatement.setInt(2, order.getUser_id());
preparedStatement.setString(3, order.getStatus());
preparedStatement.execute();
preparedStatement.close();
preparedStatement = connection.prepareStatement( sql_order_item);
preparedStatement.setInt(1, order_item.getItem_id());
preparedStatement.setInt(2, order_item.getOrder_id());
preparedStatement.setInt(3, order_item.getUser_id());
preparedStatement.execute();
preparedStatement.close();
// CHECKSTYLE:OFF
} catch (final Exception ex) {
// CHECKSTYLE:ON
ex.printStackTrace();
}
}
insert.png
只有在master节点的数据库中插入了数据,slave节点的数据需要数据库同步配置。
官方文档这样说的
支持项
1.提供了一主多从的读写分离配置，可配合分库分表使用。
2.同一线程且同一数据库连接内，如有写入操作，以后的读操作均从主库读取，用于保证数据一致性。
3.Spring命名空间。
4.基于Hint的强制主库路由。
不支持范围
1.主库和从库的数据同步。
2.主库和从库的数据同步延迟导致的数据不一致。
3.主库双写或多写。