ShardingSphere-JDBC5.2多个数据库水平分表

才_先生

已于 2022-11-05 19:27:06 修改

阅读量1k

点赞数

分类专栏： sharding-jdbc 文章标签： java mysql

于 2022-11-05 19:25:18 首次发布

本文链接：https://blog.csdn.net/qq_28227405/article/details/127479634

版权

sharding-jdbc 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

本文演示的是ShardingSphere-JDBC5.2整合mybatisPlus实现多个数据库的水平分表功能，项目需要了解mybatisPlus, 基本上是在mybatisPlus的基础上加上 sharding-jdbc的水平分片的配置实现了多个数据库每个数据库只有一张表的水平分表需求。

1.父pom依赖，由于本人项目情况就不做简化了，实际上这个依赖可以不用管，可以根据自己项目情况做调整，主要是springboot, springCloud Alibaba的依赖：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
 
    <groupId>com.xmc</groupId>
    <artifactId>springcloud-alibaba</artifactId>
    <packaging>pom</packaging>
    <version>1.0-SNAPSHOT</version>
    <modules>
        <!--这里的依赖实际上可以不管，是我在测试时建的模块-->
        <module>shop-common</module>
        <module>core</module>
        <module>sharding</module>
    </modules>
 
    <!--父工程-->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.3.RELEASE</version>
    </parent>
 
    <!-- 依赖版本-->
    <properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <spring-cloud.version>Greenwich.RELEASE</spring-cloud.version>
        <spring-cloud-alibaba.version>2.1.0.RELEASE</spring-cloud-alibaba.version>
    </properties>
    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-dependencies</artifactId>
                <version>${spring-cloud.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
            <dependency>
                <groupId>com.alibaba.cloud</groupId>
                <artifactId>spring-cloud-alibaba-dependencies</artifactId>
                <version>${spring-cloud-alibaba.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>
</project>

子模块的pom依赖，核心依赖文件，必须引入sharding-jdbc和mybatisPlus的依赖。

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>springcloud-alibaba</artifactId>
        <groupId>com.xmc</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>
 
    <artifactId>sharding</artifactId>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-autoconfigure</artifactId>
        </dependency>
 
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
            </dependency>
            <!--核心依赖shardingjdbc5.2-->
            <dependency>
                <groupId>org.apache.shardingsphere</groupId>
                <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
                <version>5.2.0</version>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
            </dependency>
            <!--mybatisPlus依赖-->
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>3.3.2</version>
            </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
 
    </dependencies>
 
</project>

核心配置文件application.properties如下，只演示多个数据库每个数据库只有1张表的水平分表示例：

#多库单表水平分片
server.port= 8991
spring.application.name = service-sharding
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
## shardingjdbc 分片策略
## 配置数据源，给数据源起名称
spring.shardingsphere.datasource.names=d1,d2
#spring.shardingsphere.mode.type=Standalone
## 一个实体类对应多张表，覆盖
spring.main.allow-bean-definition-overriding=true
#
##配置数据源shop具体内容，包含连接池，驱动，地址，用户名和密码
spring.shardingsphere.datasource.d1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.d1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.d1.jdbc-url=jdbc:mysql://127.0.0.1:3306/shop?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.shardingsphere.datasource.d1.username=root
spring.shardingsphere.datasource.d1.password=abcd1234
##配置数据源shop2具体内容，包含连接池，驱动，地址，用户名和密码
spring.shardingsphere.datasource.d2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.d2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.d2.jdbc-url=jdbc:mysql://127.0.0.1:3306/shop2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.shardingsphere.datasource.d2.username=root
spring.shardingsphere.datasource.d2.password=abcd1234

#指定逻辑表 shop_user 表分布情况，d1数据库里面有真实表shop_user,d2数据库有真是表shop_user
spring.shardingsphere.rules.sharding.tables.shop_user.actual-data-nodes=d1.shop_user,d2.shop_user
#
##指定表分片策略的分片列名称为id
spring.shardingsphere.rules.sharding.tables.shop_user.database-strategy.standard.sharding-column=id
## 数据库分片算法配置
#数据库分片算法名称
spring.shardingsphere.rules.sharding.tables.shop_user.database-strategy.standard.sharding-algorithm-name=database-user-inline

# 数据库分片算法类型
spring.shardingsphere.rules.sharding.sharding-algorithms.database-user-inline.type=INLINE
# 数据库分片算法属性 注意这里的表达式一定是和数据库有关的而不是和表相关的，否则会抛ShardingRouteAlgorithmException异常。
spring.shardingsphere.rules.sharding.sharding-algorithms.database-user-inline.props.algorithm-expression=d$->{id%2 + 1}
# 打开 sql 输出日志
spring.shardingsphere.props.sql-show=true

配置说明：

spring.shardingsphere.rules.sharding.sharding-algorithms.database-user-inline.props.algorithm-expression=d$->{id%2 + 1} 一定要配置为和数据库相关的inline表达式，如果配置为表相关的inline表达式，则会抛出 ShardingRouteAlgorithmException 异常。d$->{id%2 + 1}是因为数据源是d1,d2,因此需要取模后加1，否则取模后是d0,d1会因为d0数据源未配置导致的sql执行异常。

这里就不再写列出controller,service，dao层代码了，如果需要可以参考前面两篇文章。毕竟sharding-jdbc的核心是分片配置。

测试结果：

当查询条件的id=1时，可以看到查询的是d2数据源。

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@f7a063c] was not registered for synchronization because synchronization is not active
JDBC Connection [org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@1bde9a3] will not be managed by Spring
==>  Preparing: SELECT id,username,password,telephone FROM shop_user WHERE id=? 
==> Parameters: 1(Integer)
2022-10-23 20:09:18.500  INFO 51176 --- [nio-8991-exec-7] ShardingSphere-SQL                       : Logic SQL: SELECT id,username,password,telephone FROM shop_user WHERE id=? 
2022-10-23 20:09:18.501  INFO 51176 --- [nio-8991-exec-7] ShardingSphere-SQL                       : SQLStatement: MySQLSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=1, parameterMarkerSegments=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty)], commentSegments=[]), projections=ProjectionsSegment(startIndex=7, stopIndex=36, projections=[ColumnProjectionSegment(column=ColumnSegment(startIndex=7, stopIndex=8, identifier=IdentifierValue(value=id, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=10, stopIndex=17, identifier=IdentifierValue(value=username, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=19, stopIndex=26, identifier=IdentifierValue(value=password, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=28, stopIndex=36, identifier=IdentifierValue(value=telephone, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty)], distinctRow=false), from=SimpleTableSegment(tableName=TableNameSegment(startIndex=43, stopIndex=51, identifier=IdentifierValue(value=shop_user, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), where=Optional[WhereSegment(startIndex=53, stopIndex=62, expr=BinaryOperationExpression(startIndex=59, stopIndex=62, left=ColumnSegment(startIndex=59, stopIndex=60, identifier=IdentifierValue(value=id, quoteCharacter=NONE), owner=Optional.empty), right=ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), operator==, text=id=?))], groupBy=Optional.empty, having=Optional.empty, orderBy=Optional.empty, combines=[]), table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-10-23 20:09:18.502  INFO 51176 --- [nio-8991-exec-7] ShardingSphere-SQL                       : Actual SQL: d2 ::: SELECT id,username,password,telephone FROM shop_user WHERE id=?  ::: [1]
<==    Columns: id, username, password, telephone
<==        Row: 1, 数据库2, 1, 1
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@f7a063c]

当查询条件的id=2时，可以看到查询的是d1数据源。

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@18ec19cd] was not registered for synchronization because synchronization is not active
JDBC Connection [org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@162be4a0] will not be managed by Spring
==>  Preparing: SELECT id,username,password,telephone FROM shop_user WHERE id=? 
==> Parameters: 2(Integer)
2022-10-23 20:10:21.626  INFO 51176 --- [nio-8991-exec-9] ShardingSphere-SQL                       : Logic SQL: SELECT id,username,password,telephone FROM shop_user WHERE id=? 
2022-10-23 20:10:21.626  INFO 51176 --- [nio-8991-exec-9] ShardingSphere-SQL                       : SQLStatement: MySQLSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=1, parameterMarkerSegments=[ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty)], commentSegments=[]), projections=ProjectionsSegment(startIndex=7, stopIndex=36, projections=[ColumnProjectionSegment(column=ColumnSegment(startIndex=7, stopIndex=8, identifier=IdentifierValue(value=id, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=10, stopIndex=17, identifier=IdentifierValue(value=username, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=19, stopIndex=26, identifier=IdentifierValue(value=password, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=28, stopIndex=36, identifier=IdentifierValue(value=telephone, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty)], distinctRow=false), from=SimpleTableSegment(tableName=TableNameSegment(startIndex=43, stopIndex=51, identifier=IdentifierValue(value=shop_user, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), where=Optional[WhereSegment(startIndex=53, stopIndex=62, expr=BinaryOperationExpression(startIndex=59, stopIndex=62, left=ColumnSegment(startIndex=59, stopIndex=60, identifier=IdentifierValue(value=id, quoteCharacter=NONE), owner=Optional.empty), right=ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), operator==, text=id=?))], groupBy=Optional.empty, having=Optional.empty, orderBy=Optional.empty, combines=[]), table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-10-23 20:10:21.627  INFO 51176 --- [nio-8991-exec-9] ShardingSphere-SQL                       : Actual SQL: d1 ::: SELECT id,username,password,telephone FROM shop_user WHERE id=?  ::: [2]
<==    Columns: id, username, password, telephone
<==        Row: 2, 2, 22, 111
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@18ec19cd]

才_先生

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
ShardingSphere-JDBC5.2多个数据库水平分表

ShardingSphere-JDBC5.2整合mybatisPlus实现多个数据库的水平分表功能，项目需要了解mybatisPlus, 基本上是在mybatisPlus的基础上加上 sharding-jdbc的水平分片的配置实现了多个数据库每个数据库只有一张表的水平分表需求。
复制链接

扫一扫