美文网首页
Sharding-JDBC读写分离

Sharding-JDBC读写分离

作者: AnyL8023 | 来源:发表于2016-10-31 14:42 被阅读0次

    本文内容参考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.主库双写或多写。

    相关文章

      网友评论

          本文标题：Sharding-JDBC读写分离

          本文链接：https://www.haomeiwen.com/subject/dhlputtx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|Sharding-JDBC读写分离|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！