Sharding-JDBC 实现分库分表

不念不忘少年蓝@ 2022-11-19 07:58 117阅读 0赞

**1、数据库环境**  
ds0：172.31.32.184  
ds1：172.31.32.234  
用作分库

**2、在 ds0 和 ds1 库中建表，t\_order0 和 t\_order1 用做分表**

create table t_order0(
    order_id int primary key,
    user_id int, 
    goods_id int,
    goods_name varchar(200)
    );
    
    create table t_order1(
    order_id int primary key,
    user_id int, 
    goods_id int,
    goods_name varchar(200)
    );

**3、新建 maven 项目，添加依赖**

<dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-core</artifactId>
        <version>4.1.1</version>
    </dependency>
    
    <dependency>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP</artifactId>
        <version>3.4.5</version>
    </dependency>
    
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.21</version>
    </dependency>

**4、数据源连接工具类，使用 HikariCP 数据库连接池**

package constxiong;
    
    import com.zaxxer.hikari.HikariDataSource;
    
    /**
     * 获取 DataSource 工具类，使用了 Hikari 数据库连接池
     */
    import javax.sql.DataSource;
    
    public final class DataSourceUtil {
    
        private static final int PORT = 3306;
    
        /**
         * 通过 Hikari 数据库连接池创建 DataSource
         * @param ip
         * @param username
         * @param password
         * @param dataSourceName
         * @return
         */
        public static DataSource createDataSource(String ip, String username, String password, String dataSourceName) {
            HikariDataSource result = new HikariDataSource();
            result.setDriverClassName(com.mysql.jdbc.Driver.class.getName());
            result.setJdbcUrl(String.format("jdbc:mysql://%s:%s/%s?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8", ip, PORT, dataSourceName));
            result.setUsername(username);
            result.setPassword(password);
            return result;
        }
    }

**5、测试代码**

测试逻辑：

*  使用数据源工具类，新建 ds0 和 ds1 库的数据源
 *  配置 t\_order 表的规则，ds$->\{0..1\}.t\_order$->\{0..1\}
 *  配置分库分表的规则，分库根据 ds  拼接上 t\_order 的 user\_id 字段值 对 2 取模的值（ds$\{user\_id % 2\}）；分表根据 t\_order  拼接上 order\_id 字段值 对 2 取模的值（t\_order$\{order\_id % 2\}）
 *  测试 insert 不同的 user\_id 和 order\_id 是否会按规则插入不同的库和表
 *  查询 user\_id = 2 的数据，看看是否正常查出

package constxiong;
    
    import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
    import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
    import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
    import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
    
    import javax.sql.DataSource;
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.HashMap;
    import java.util.Map;
    import java.util.Properties;
    
    /**
     * 测试 ShardingSphere 分表分库
     */
    public class Test {
    
        //DataSource 0
        private static DataSource ds0 = DataSourceUtil.createDataSource("172.31.32.234", "root", "constxiong@123", "constxiong");
        // DataSource 1
        private static DataSource ds1 = DataSourceUtil.createDataSource("172.31.32.184", "root", "constxiong@123", "constxiong");
    
        public static void main(String[] args) throws SQLException {
            // 配置真实数据源
            Map<String, DataSource> dataSourceMap = new HashMap<>();
            dataSourceMap.put("ds0", ds0);
            dataSourceMap.put("ds1", ds1);
    
            // 配置Order表规则
            TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_order", "ds$->{0..1}.t_order$->{0..1}");
    
            // 配置分库 + 分表策略
            orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds${user_id % 2}"));
            orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order${order_id % 2}"));
    
            // 配置分片规则
            ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
            shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);
    
            // 获取数据源对象
            DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());
            Connection connection = dataSource.getConnection();
            Statement statement = connection.createStatement();
            statement.execute("insert into t_order value(1, 1, 1, '电视机')");
            statement.execute("insert into t_order value(2, 1, 2, '可乐')");
            statement.execute("insert into t_order value(3, 2, 8, '空调')");
            statement.execute("insert into t_order value(4, 2, 9, '手机壳')");
    
            statement.execute("select * from t_order where user_id = 2");
            ResultSet resultSet = statement.getResultSet();
            while (resultSet.next()) {
                System.out.printf("user_id:%d, order_id:%d, goods_id:%d, goods_name:%s\n",
                        resultSet.getInt("user_id"),
                        resultSet.getInt("order_id"),
                        resultSet.getInt("goods_id"),
                        resultSet.getString("goods_name")
                );
            }
        }
    }

**6、结果**

数据按照分库分表的规则插入对应的数据库与表中

[![6433c83e453901e508781e5b93605c85.png][]][6433c83e453901e508781e5b93605c85.png 1]

user\_id = 2 数据代码查询

user_id:2, order_id:4, goods_id:9, goods_name:手机壳
    user_id:2, order_id:3, goods_id:8, goods_name:空调

--------------------

### 【Java学习资源】整理推荐 ###

*  [Java 8 Stream Api demo][]
 *  [Druid 使用][Druid]
 *  [借助 libreoffice，word 转 pdf 体验][libreoffice_word _ pdf]
 *  [Java 动态代理][Java]
 *  [openjdk jol 工具打印 Java 对象内存布局][openjdk jol _ Java]
 *  [Spring整合MyBatis][Spring_MyBatis]
 *  [MyBatis 自定义插件][MyBatis]
 *  [MyBatis 延迟加载][MyBatis 1]
 *  [MyBatis 1对1 1对多 关联查询][MyBatis 1_1 1_]
 *  [MyBatis insert 获取自增主键][MyBatis insert]
 *  [MyBatis 批量插入][MyBatis 2]
 *  [分页插件 Mybatis-PageHelper][Mybatis-PageHelper]
 *  [MyBatis 原生使用][MyBatis 3]
 *  [Sharding-JDBC 实现分库分表][Sharding-JDBC]
 *  [JDK1.8 中的日期与时间 API][JDK1.8 _ API]
 *  [Sharding-JDBC 实现读写分离][Sharding-JDBC 1]

--------------------

### 【Java面试题与答案】整理推荐 ###

*  [基础与语法][Link 1]
 *  [集合][Link 2]
 *  [网络编程][Link 3]
 *  [并发编程][Link 4]
 *  [Web][]
 *  [安全][Link 5]
 *  [设计模式][Link 6]
 *  [框架][Link 7]
 *  [算法与数据结构][Link 8]
 *  [异常][Link 9]
 *  [文件解析与生成][Link 10]
 *  [Linux][]
 *  [MySQL][]
 *  [Oracle][]
 *  [Redis][]
 *  [Dubbo][]

[6433c83e453901e508781e5b93605c85.png]: /images/20221119/0603387be8124bbc8442a2735a92fd34.png
[6433c83e453901e508781e5b93605c85.png 1]: https://www.javanav.com/aimgs/image__20200923193236.png
[Java 8 Stream Api demo]: https://www.javanav.com/val/5be3f821d00e4cc5b6177c9d0e5dd30f.html
[Druid]: https://www.javanav.com/val/79860880b84442cfb0f6f5e094d53105.html
[libreoffice_word _ pdf]: https://www.javanav.com/val/501ababe5c48493780ed23dbbc7b2cbc.html
[Java]: https://www.javanav.com/val/ab11038d7de64b4b8c0e384d1d4a9ab0.html
[openjdk jol _ Java]: https://www.javanav.com/val/66e564087e244b2f99f85091de029605.html
[Spring_MyBatis]: https://www.javanav.com/val/687c224b31a34d3c9f99fee67e3d5bcc.html
[MyBatis]: https://www.javanav.com/val/a5535343f9b545eda9665f03d62345ba.html
[MyBatis 1]: https://www.javanav.com/val/973ded541e9244aa8b3169b9fb869d60.html
[MyBatis 1_1 1_]: https://www.javanav.com/val/a9fe4555c1614b40b0da07afeabf2a66.html
[MyBatis insert]: https://www.javanav.com/val/3ac331d2674b4c108469cce54ae126f3.html
[MyBatis 2]: https://www.javanav.com/val/2d21b1463f2e4faeaf0def0c49df34a4.html
[Mybatis-PageHelper]: https://www.javanav.com/val/a8a8009f878d47dc9167bf20b9e0ab57.html
[MyBatis 3]: https://www.javanav.com/val/07f60d7946204b3b893abd5dc8bd2fa3.html
[Sharding-JDBC]: https://www.javanav.com/val/eefddd846ae845668daf9d798755c057.html
[JDK1.8 _ API]: https://www.javanav.com/val/fefecf2f089e48b294708e99954c57a1.html
[Sharding-JDBC 1]: https://www.javanav.com/val/f05fe12cb8fd444aaa59fb7f747a3224.html
[Link 1]: https://www.javanav.com/interview/93b0069472fd479393006c0e73043fc4.html
[Link 2]: https://www.javanav.com/interview/4147d95e693945e7a50345fd33ec817e.html
[Link 3]: https://www.javanav.com/interview/68a57ffc78de4133bb890bdd502ac418.html
[Link 4]: https://www.javanav.com/interview/36a24fa3258e45c89873f474ce914912.html
[Web]: https://www.javanav.com/interview/4e27cb63adb440c6825a159d35cf4010.html
[Link 5]: https://www.javanav.com/interview/03affb2ce3f04fda8853f714e5764906.html
[Link 6]: https://www.javanav.com/interview/07a32a9ff17a4006a53550ce658cafed.html
[Link 7]: https://www.javanav.com/interview/5c466456e9b54ab0a749b5948e51f1d3.html
[Link 8]: https://www.javanav.com/interview/943f917041ee4f05add24539f5b861b2.html
[Link 9]: https://www.javanav.com/interview/d3f058cfc05d4c6dadd821b622e84d36.html
[Link 10]: https://www.javanav.com/interview/67a50996c28f418e83b109ea42fc3eff.html
[Linux]: https://www.javanav.com/interview/00cccb81729540d1923bd0a0ef49533a.html
[MySQL]: https://www.javanav.com/interview/d311e78502a845f1a4bb307bd6aec2b3.html
[Oracle]: https://www.javanav.com/interview/5f29e9677edf4a16be3dce30d257c9ab.html
[Redis]: https://www.javanav.com/interview/066c62b3af4d4827b2243a822c3084a9.html
[Dubbo]: https://www.javanav.com/interview/9a3e7a7740324757abaab953f7ebc455.html