SpringBoot整合Sharding-JDBC案例

数据表

t_user表为需要分库分表的表，user为其他表，user放在nestdr1中。对数据库实行读写分离nestdr1为从数据库。

 查询

 代码

pom

        <!-- web -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

        <!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>

        <!-- druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.12</version>
        </dependency>

        <!-- sharding-jdbc -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>

        <!-- test -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>    

 配置（简单举例）

spring.application.name=eth-service-main
server.port=38082

spring.mvc.static-path-pattern=static/**
spring.mvc.async.request-timeout=200000
spring.main.allow-bean-definition-overriding=true

# spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
# spring.datasource.url=jdbc:mysql://121.36.206.127:3306/nestdr?useUnicode=true&characterEncoding=utf-8&useSSL=false
# spring.datasource.username=nsd
# spring.datasource.password=caokang@2020

mybatis.type-aliases-package=tech.cknsd.eth.service.main.mapper
mybatis.mapper-locations=classpath:/mapper/*.xml
# 开启驼峰自动转大写
mybatis.configuration.mapUnderscoreToCamelCase=true

#数据源名称，多数据源以逗号分隔
spring.shardingsphere.datasource.names=nestdr0,nestdr1,nestdr0slave
#未配置分片规则的表将通过默认数据源定位-适用于单库单表，该表无需配置分片规则
spring.shardingsphere.sharding.default-data-source-name=nestdr0slave
# nestdr0
spring.shardingsphere.datasource.nestdr0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.nestdr0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.nestdr0.jdbc-url=jdbc:mysql://121.36.206.127:3306/nestdr0?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.nestdr0.username=nsd
spring.shardingsphere.datasource.nestdr0.password=caokang@2020
spring.shardingsphere.datasource.nestdr0.connect-timeout=2000
# nestdr1
spring.shardingsphere.datasource.nestdr1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.nestdr1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.nestdr1.jdbc-url=jdbc:mysql://121.36.206.127:3306/nestdr1?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.nestdr1.username=nsd
spring.shardingsphere.datasource.nestdr1.password=caokang@2020
spring.shardingsphere.datasource.nestdr1.connect-timeout=2000
# nestdr0slave
spring.shardingsphere.datasource.nestdr0slave.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.nestdr0slave.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.nestdr0slave.jdbc-url=jdbc:mysql://121.36.206.127:3306/nestdr1?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.nestdr0slave.username=nsd
spring.shardingsphere.datasource.nestdr0slave.password=caokang@2020
spring.shardingsphere.datasource.nestdr0slave.connect-timeout=2000
#指定t_order表的配置主键生成策略SNOWFLAKE
spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
# 分库 以 user_id 为分片键，因为分四个库，所以取模 4，虚拟表名为 t_user
spring.shardingsphere.sharding.tables.t_user.database-strategy.inline.algorithm-expression=nestdr$->{user_id % 2}
spring.shardingsphere.sharding.tables.t_user.database-strategy.inline.sharding-column=user_id
# 分表 分片键位user_id,每个库分为两表，所以取模2
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user_$->{user_id % 2}
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=user_id
# 这里由于分库分表字段不相同配置，不然会导致使用user_id 查询找不到相应的表，如果我们分库分表都使用 user_id 则不需要这个配置
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=nestdr$->{0..1}.t_user_$->{0..1}
#是否开启SQL显示，默认值: false
spring.shardingsphere.props.sql.show=true

# 读写分离配置
# 从库负载均衡算法：round_rabin 轮询，random 随机
spring.shardingsphere.sharding.master-slave-rules.nestdr0.load-balance-algorithm-type=ROUND_ROBIN
spring.shardingsphere.sharding.master-slave-rules.nestdr0.master-data-source-name=nestdr0
spring.shardingsphere.sharding.master-slave-rules.nestdr0.slave-data-source-names=nestdr0slave