springboot2.0.4 整合Shardingsphere（4.0.1），分库分表

1.引用pom依赖

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

<dependency>
   <groupId>org.apache.shardingsphere</groupId>
   <artifactId>sharding-jdbc-spring-namespace</artifactId>
   <version>4.0.1</version>
</dependency>

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


<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.1.46</version>
</dependency>


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

2. 读写分离

# 设置数据源的名称 一写两读
spring.shardingsphere.datasource.names: write-ds,read-ds-0,read-ds-1

# 定义写数据源
spring.shardingsphere.datasource.write-ds.jdbc-url: jdbc:mysql://localhost:3306/demo_write_ds?serverTimezone： UTC&useSSL： false&useUnicode： true&characterEncoding： UTF-8
spring.shardingsphere.datasource.write-ds.type: com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.write-ds.driver-class-name: com.mysql.jdbc.Driver
spring.shardingsphere.datasource.write-ds.username: root
spring.shardingsphere.datasource.write-ds.password: root

# 定义读数据源
spring.shardingsphere.datasource.read-ds-0.jdbc-url: jdbc:mysql://localhost:3306/demo_read_ds_0?serverTimezone： UTC&useSSL： false&useUnicode： true&characterEncoding： UTF-8
spring.shardingsphere.datasource.read-ds-0.type: com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.read-ds-0.driver-class-name: com.mysql.jdbc.Driver
spring.shardingsphere.datasource.read-ds-0.username: root
spring.shardingsphere.datasource.read-ds-0.password: root

# 定义读数据源
spring.shardingsphere.datasource.read-ds-1.jdbc-url: jdbc:mysql://localhost:3306/demo_read_ds_1?serverTimezone： UTC&useSSL： false&useUnicode： true&characterEncoding： UTF-8
spring.shardingsphere.datasource.read-ds-1.type: com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.read-ds-1.driver-class-name: com.mysql.jdbc.Driver
spring.shardingsphere.datasource.read-ds-1.username: root
spring.shardingsphere.datasource.read-ds-1.password: root

# 设置名称
spring.shardingsphere.masterslave.name: ds_ms
# 设置写库
spring.shardingsphere.masterslave.master-data-source-name: write-ds
# 设置读库
spring.shardingsphere.masterslave.slave-data-source-names: read-ds-0,read-ds-1

# 数据库的负载均衡策略，轮询
spring.shardingsphere.masterslave.load-balance-algorithm-type: ROUND_ROBIN

spring.shardingsphere.masterslave.props.k1: v1

# 显示sql语句
spring.shardingsphere.props.sql.show: true

3.单库分表

# 设置数据源的名称 
spring.shardingsphere.datasource.names: write-ds

# 定义数据源
spring.shardingsphere.datasource.write-ds.jdbc-url: jdbc:mysql://localhost:3306/demo_write_ds?serverTimezone： UTC&useSSL： false&useUnicode： true&characterEncoding： UTF-8
spring.shardingsphere.datasource.write-ds.type: com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.write-ds.driver-class-name: com.mysql.jdbc.Driver
spring.shardingsphere.datasource.write-ds.username: root
spring.shardingsphere.datasource.write-ds.password: root

# 设置默认数据源
spring.shardingsphere.sharding.default-data-source-name: write-ds

#数据分表规则
#指定所需分的表
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes: write-ds.t_order$->{0..2}
#指定主键
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column: order_id
#分表规则为主键除以3取模
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression: t_order$->{order_id % 3}

# 设置绑定的表
spring.shardingsphere.sharding.binding-tables: t_order

# 显示sql语句
spring.shardingsphere.props.sql.show: true

 4.分库不分表

# 设置数据源的名称
spring.shardingsphere.datasource.names: write-ds,read-ds-0,read-ds-1

# 定义写数据源
spring.shardingsphere.datasource.write-ds.jdbc-url: jdbc:mysql://localhost:3306/demo_write_ds?serverTimezone： UTC&useSSL： false&useUnicode： true&characterEncoding： UTF-8
spring.shardingsphere.datasource.write-ds.type: com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.write-ds.driver-class-name: com.mysql.jdbc.Driver
spring.shardingsphere.datasource.write-ds.username: root
spring.shardingsphere.datasource.write-ds.password: root

# 定义读数据源
spring.shardingsphere.datasource.read-ds-0.jdbc-url: jdbc:mysql://localhost:3306/demo_read_ds_0?serverTimezone： UTC&useSSL： false&useUnicode： true&characterEncoding： UTF-8
spring.shardingsphere.datasource.read-ds-0.type: com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.read-ds-0.driver-class-name: com.mysql.jdbc.Driver
spring.shardingsphere.datasource.read-ds-0.username: root
spring.shardingsphere.datasource.read-ds-0.password: root

# 定义读数据源
spring.shardingsphere.datasource.read-ds-1.jdbc-url: jdbc:mysql://localhost:3306/demo_read_ds_1?serverTimezone： UTC&useSSL： false&useUnicode： true&characterEncoding： UTF-8
spring.shardingsphere.datasource.read-ds-1.type: com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.read-ds-1.driver-class-name: com.mysql.jdbc.Driver
spring.shardingsphere.datasource.read-ds-1.username: root
spring.shardingsphere.datasource.read-ds-1.password: root

# 设置默认数据源
spring.shardingsphere.sharding.default-data-source-name: write-ds

spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column: order_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression: read-ds-$->{order_id % 2}
spring.shardingsphere.sharding.tables.t_order.key-generator.column: order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type: SNOWFLAKE

# 设置绑定的表
spring.shardingsphere.sharding.binding-tables: t_order

# 显示sql语句
spring.shardingsphere.props.sql.show: true

5.分库+分表

# 设置数据源的名称
spring.shardingsphere.datasource.names: write-ds,read-ds-0,read-ds-1

# 定义写数据源
spring.shardingsphere.datasource.write-ds.jdbc-url: jdbc:mysql://localhost:3306/demo_write_ds?serverTimezone： UTC&useSSL： false&useUnicode： true&characterEncoding： UTF-8
spring.shardingsphere.datasource.write-ds.type: com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.write-ds.driver-class-name: com.mysql.jdbc.Driver
spring.shardingsphere.datasource.write-ds.username: root
spring.shardingsphere.datasource.write-ds.password: root

# 定义读数据源
spring.shardingsphere.datasource.read-ds-0.jdbc-url: jdbc:mysql://localhost:3306/demo_read_ds_0?serverTimezone： UTC&useSSL： false&useUnicode： true&characterEncoding： UTF-8
spring.shardingsphere.datasource.read-ds-0.type: com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.read-ds-0.driver-class-name: com.mysql.jdbc.Driver
spring.shardingsphere.datasource.read-ds-0.username: root
spring.shardingsphere.datasource.read-ds-0.password: root

# 定义读数据源
spring.shardingsphere.datasource.read-ds-1.jdbc-url: jdbc:mysql://localhost:3306/demo_read_ds_1?serverTimezone： UTC&useSSL： false&useUnicode： true&characterEncoding： UTF-8
spring.shardingsphere.datasource.read-ds-1.type: com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.read-ds-1.driver-class-name: com.mysql.jdbc.Driver
spring.shardingsphere.datasource.read-ds-1.username: root
spring.shardingsphere.datasource.read-ds-1.password: root

# 设置默认数据源，不分表从这读
spring.shardingsphere.sharding.default-data-source-name: write-ds

#分库又分表
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes: read-ds-$->{0..1}.t_order$->{0..2}
#指定主键
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column: order_id
#分表规则为主键除以3取模
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression: t_order$->{order_id % 3}

spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column: order_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression: read-ds-$->{order_id % 2}
spring.shardingsphere.sharding.tables.t_order.key-generator.column: order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type: SNOWFLAKE

# 设置绑定的表
spring.shardingsphere.sharding.binding-tables: t_order

# 显示sql语句
spring.shardingsphere.props.sql.show: true

6.单库分表+读写分离

spring.shardingsphere.datasource.names=master,slave

## 第一个数据源（主）
spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456

## 第二个数据源（从）
spring.shardingsphere.datasource.slave.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.slave.username=root
spring.shardingsphere.datasource.slave.password=123456

spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds0.t_order$->{0..2}
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=orderId
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{orderId % 3}
spring.shardingsphere.sharding.tables.t_order.key-generator.column=orderId
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds0.t_order_item$->{0..2}
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=orderId
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item$->{orderId % 3}
spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=id
spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE
## 绑定表，t_order和t_order_item 使用相同的分片策略，都是用order_id进行分片，提升查询效率
spring.shardingsphere.sharding.binding-tables=t_order,t_order_item

## 读写分离
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=master
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=slave
## 从库负载均衡，这里只设置了一个从库
spring.shardingsphere.sharding.master-slave-rules.ds0.load-balance-algorithm-type=round_robin
## 显示sharding-jdbc sql
spring.shardingsphere.props.sql.show=true

7.自定义分库分表策略

# 设置数据源的名称
spring.shardingsphere.datasource.names: write-ds

# 定义写数据源
spring.shardingsphere.datasource.write-ds.jdbc-url: jdbc:mysql://localhost:3306/demo_write_ds?serverTimezone： UTC&useSSL： false&useUnicode： true&characterEncoding： UTF-8
spring.shardingsphere.datasource.write-ds.type: com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.write-ds.driver-class-name: com.mysql.jdbc.Driver
spring.shardingsphere.datasource.write-ds.username: root
spring.shardingsphere.datasource.write-ds.password: root

# 设置默认数据源，不分表从这读
spring.shardingsphere.sharding.default-data-source-name: write-ds

spring.shardingsphere.sharding.tables.t_order.actual-data-nodes: write-ds.t_order$->{0..2}
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column: status
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name: com.dhc.areatest.ttt.conf.MyAlgorithm

# 设置绑定的表
spring.shardingsphere.sharding.binding-tables: t_order

# 显示sql语句
spring.shardingsphere.props.sql.show: true

 

类：

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.springframework.stereotype.Component;

import java.util.Collection;

@Component
public class MyAlgorithm implements PreciseShardingAlgorithm<String> {

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
        String value = preciseShardingValue.getValue();
        
        for(String name : collection){
           判断逻辑，根据value,确定对应的name
            return name;
        }
        return null;
    }
}