分库分表shardingsphere-jdbc+springboot2.x+mybatisplus

版本信息（请特别注意springboot和shardingsphere-jdbc的版本,不同版本区别很大）：

springboot:2.1.5.RELEASE
shardingsphere-jdbc-core-spring-boot-starter:5.0.0-alpha

测试用库表

分两个物理库demo1,demo2;每个库user表分为user_0,user_1,user_2三张表。
每张表结构都相同：

create table demo1.user_0
(
	id bigint not null
		primary key,
	name varchar(25) null comment '姓名',
	age int null comment '年龄',
	user_id int null
)
comment '测试用户表';

maven依赖:

由于想偷懒，不想写mybatis中xml,所以这里使用了mybatis-plus，当然你使用mybatis也是一样的

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

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.0.0-alpha</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>
<!--        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.0</version>
        </dependency>-->
    </dependencies>

核心配置文件application.properties：

如果需要yaml格式，可网上找在线转换工具，转为yaml格式

spring.shardingsphere.datasource.names=demo0,demo1

spring.shardingsphere.datasource.common.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.common.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.common.username=root
spring.shardingsphere.datasource.common.password=123456

spring.shardingsphere.datasource.demo0.jdbc-url=jdbc:mysql://localhost:3306/demo1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.demo1.jdbc-url=jdbc:mysql://localhost:3306/demo2?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
#配置分库策略
spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-column=id
spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-algorithm-name=database-inline
#配置分表策略
spring.shardingsphere.rules.sharding.tables.user.table-strategy.standard.sharding-column=user_id
spring.shardingsphere.rules.sharding.tables.user.table-strategy.standard.sharding-algorithm-name=user-table-inline
#完整分片表规则配置
spring.shardingsphere.rules.sharding.tables.user.actual-data-nodes=demo$->{0..1}.user_$->{0..2}

#配置database-inline数据库分片算法(INLINE:行表达式分片算法)
spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.props.algorithm-expression=demo$->{id % 2}
#配置user-table-inline表的分片算法
spring.shardingsphere.rules.sharding.sharding-algorithms.user-table-inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.user-table-inline.props.algorithm-expression=user_$->{user_id % 3}

#主键生成策略（雪花算法）
spring.shardingsphere.rules.sharding.tables.user.key-generate-strategy.column=id
spring.shardingsphere.rules.sharding.tables.user.key-generate-strategy.key-generator-name=snowflake
spring.shardingsphere.rules.sharding.key-generators.snowflake.type=SNOWFLAKE
spring.shardingsphere.rules.sharding.key-generators.snowflake.props.worker-id=123

#打印sql
spring.shardingsphere.props.sql-show=true

测试

以插入为例，测试结果符合预期，数据先会根据id字段决定是分到demo0(物理库demo1),
还是demo1(物理库demo2)库，然后根据userId字段确定是分到user_0,user_1,user_2那张表里。
例如：

1393104041126420482%2 = 0,所以分到逻辑库demo0(物理库demo1)。然后userId,100%3=1,所以分到user_1表里

---

整合时候注意点：

1. 不同版本shardingsphere-jdbc配置文件名称可能存在很大不同，本案例只适用于shardingsphere-jdbc-core-spring-boot-starter:5.0.0-alpha

2. 如果出现no table route info或者no database route info报错，请检查如下圈出来配置。确保一条数据过来，你能通过计算得到一个具体 database?.table?这样一个结果。例如：id%2是否在.actual-data-nodes中定义的[0,1]区间，user_id%3是否在.actual-data-nodes中定义的[0,2]区间