sharding-jdbc基本使用

ShardingSphere

shardingsphere简介

Apache ShardingSphere 是一款分布式的数据库生态系统，可以将任意数据库转换为分布式数据库，并 通过数据分片、弹性伸缩、加密等能力对原有数据库进行增强。 Apache ShardingSphere 设计哲学为 Database Plus，旨在构建异构数据库上层的标准和生态。它关注如 何充分合理地利用数据库的计算和存储能力，而并非实现一个全新的数据库。它站在数据库的上层视角， 关注它们之间的协作多于数据库自身。

ShardingSphere-JDBC

ShardingSphere‐JDBC 定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。

ShardingSphere-Proxy

ShardingSphere‐Proxy 定位为透明化的数据库代理端，通过实现数据库二进制协议，对异构语言提供支 持。

Mysql主从搭建：

master配置

创建slave账号

CREATE USER ‘slave’@‘%’ IDENTIFIED BY ‘123456’;

给slave账号配置从站复制权限

GRANT REPLICATION SLAVE ON *.* TO ‘slave’@‘%’;

show master status;

reset master; #删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件。用于第一次进行搭建主从库时，进行主库binlog初始化工作

slave配置

change master to master_host=‘192.168.100.160’,

master_user=‘slave’,

master_password=‘123456’,

master_port=3306,

master_log_file=‘mysql-bin.000024’, #通过show master status

master_log_pos=589, #通过show master status

master_connect_retry=30;

start slave; #开启从站

show slave status; #显示从站状态 如果Slave_IO_Running 和 Slave_SQL_Running为Yes则说明主从配置到成功

stop slave; #关闭从站

reset slave; #用于删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件；当原来的主从关系被破坏之后，从库经过重新初始化后直接连接会报 ERROR 1201的错误，运行reset slave后，重新配置主从连接就可以了；

pom文件版本

<parent>
<groupId>org.springframework.boot</groupId>

        <artifactId>spring-boot-starter-parent</artifactId>

        <version>2.3.12.RELEASE</version>

    </parent>

    <dependencies>

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter</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>

            <scope>test</scope>

        </dependency>

        <dependency>

            <groupId>com.baomidou</groupId>

            <artifactId>mybatis-plus-boot-starter</artifactId>

            <version>3.4.3</version>

        </dependency>

        <dependency>

            <groupId>org.projectlombok</groupId>

            <artifactId>lombok</artifactId>

            <version>1.18.20</version>

        </dependency>

        <dependency>

            <groupId>org.apache.shardingsphere</groupId>

            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>

            <version>5.1.1</version>

        </dependency>

        <dependency>

            <groupId>com.alibaba</groupId>

            <artifactId>druid</artifactId>

            <version>1.2.8</version>

        </dependency>

        <dependency>

            <groupId>mysql</groupId>

            <artifactId>mysql-connector-java</artifactId>

            <version>8.0.26</version>

        </dependency>

</dependencies>

配置模式

内存模式

spring.shardingsphere.mode.type=Memory

单机模式

spring.shardingsphere.mode.type=Standalone
spring.shardingsphere.mode.repository.type=File
spring.shardingsphere.mode.repository.props.path=C:/Users/allblue/Desktop/sql
spring.shardingsphere.mode.overwrite=true

集群模式

spring.shardingsphere.mode.type=Cluster
spring.shardingsphere.mode.repository.type=Zookeeper
spring.shardingsphere.mode.repository.props.namespace=shardingsphere
spring.shardingsphere.mode.repository.props.server-lists=192.168.100.154:2181
spring.shardingsphere.mode.overwrite=true
spring.shardingsphere.mode.repository.props.<key>=

存储元数据信息

元数据是表示数据的数据。从数据库角度而言，则概括为数据库的任何数据都是元数据，因此如列名、数据库名、用户名、表名等以及数据自定义库表存储的关于数据库对象的信息都是元数据

sharding-jdbc实现读写分离

面对日益增加的系统访问量，数据库的吞吐量面临着巨大瓶颈。 对于同一时刻有大量并发读操作和较少写操作类型的应用系统来说，将数据库拆分为主库和从库，主库负责处理事务性的增删改操作，从库负责处理查询操作，能够有效的避免由数据更新导致的行锁，使得整个系统的查询性能得到极大的改善。

通过一主多从的配置方式，可以将查询请求均匀的分散到多个数据副本，能够进一步的提升系统的处理能力。

与将数据根据分片键打散至各个数据节点的水平分片不同，读写分离则是根据 SQL 语义的分析，将读操作和写操作分别路由至主库与从库。

存在问题：读写分离虽然可以提升系统的吞吐量和可用性，但同时也带来了数据不一致的问题。主库与从库之间的数据一致性的问题。

主库

添加、更新以及删除数据操作所使用的数据库，5.1.1仅支持单主库。

从库

查询数据操作所使用的数据库，可支持多从库。

主从同步

将主库的数据异步的同步到从库的操作。 由于主从同步的异步性，从库与主库的数据会短时间内不一致。

负载均衡策略

通过负载均衡策略将查询请求疏导至不同从库。

支持项

• 提供一主多从的读写分离配置，可独立使用，也可配合数据分片使用；
• 事务中的数据读写均用主库；
• 基于 Hint 的强制主库路由。

不支持项

• 主库和从库的数据同步；
• 主库和从库的数据同步延迟导致的数据不一致；
• 主库多写；
• 主从库间的事务一致性。主从模型中，事务中的数据读写均用主库。

主从延迟问题解决思路：

1、强制主库路由：

HintManager hintManager = HintManager.getInstance();
hintManager.setWriteRouteOnly();

2、通过redis缓存数据，设置数据过期时间为主从数据同步时间，如果数据没有失效则通过主库查询，若失效则通过从库查询。

server.port=8088

spring.application.name=sharding-jdbc-test

spring.shardingsphere.mode.type=Standalone

spring.shardingsphere.mode.repository.type=File

spring.shardingsphere.mode.repository.props.path=C:/Users/allblue/Desktop/sql

spring.shardingsphere.mode.overwrite=true

读写分离配置

# 数据源名称
spring.shardingsphere.datasource.names=master01,slave01,slave02

# 主数据库连接池

spring.shardingsphere.datasource.master01.type=com.alibaba.druid.pool.DruidDataSource

# 主数据库连接驱动

spring.shardingsphere.datasource.master01.driver-class-name=com.mysql.jdbc.Driver

# 主数据库URL地址

spring.shardingsphere.datasource.master01.url=jdbc:mysql://192.168.100.160:3308/test?useSSL=false

# 主数据源用户名

spring.shardingsphere.datasource.master01.username=root

# 主数据源密码

spring.shardingsphere.datasource.master01.password=123456

 

#从数据库slave01配置

spring.shardingsphere.datasource.slave01.type=com.alibaba.druid.pool.DruidDataSource

spring.shardingsphere.datasource.slave01.driver-class-name=com.mysql.jdbc.Driver

spring.shardingsphere.datasource.slave01.url=jdbc:mysql://192.168.100.160:3309/test?useSSL=false

spring.shardingsphere.datasource.slave01.username=root

spring.shardingsphere.datasource.slave01.password=123456

 

#从数据库slave02配置

spring.shardingsphere.datasource.slave02.type=com.alibaba.druid.pool.DruidDataSource

spring.shardingsphere.datasource.slave02.driver-class-name=com.mysql.jdbc.Driver

spring.shardingsphere.datasource.slave02.url=jdbc:mysql://192.168.100.160:3310/test?useSSL=false

spring.shardingsphere.datasource.slave02.username=root

spring.shardingsphere.datasource.slave02.password=123456

 

# 数据源为动态还是静态

spring.shardingsphere.rules.readwrite-splitting.data-sources.ds_01.type=Static

 

#spring.shardingsphere.rules.readwrite-splitting.data-sources.pr_ds.props.auto-aware-data-source-name=

# 写库配置

spring.shardingsphere.rules.readwrite-splitting.data-sources.ds\_01.props.write-data-source-name=master01

# 读库配置
spring.shardingsphere.rules.readwrite-splitting.data-sources.ds_01.props.read-data-source-names=slave01,slave02

 

#负载均衡名称

spring.shardingsphere.rules.readwrite-splitting.data-sources.ds_01.load-balancer-name=we

# 负载均衡(轮询)

spring.shardingsphere.rules.readwrite-splitting.load-balancers.rr.type=ROUND_ROBIN

# 负载均衡(权重)

spring.shardingsphere.rules.readwrite-splitting.load-balancers.we.type=WEIGHT

# 负载均衡权重分配

spring.shardingsphere.rules.readwrite-splitting.load-balancers.we.props.slave01=1.0
spring.shardingsphere.rules.readwrite-splitting.load-balancers.we.props.slave02=1.0

#是否显示sql
spring.shardingsphere.props.sql-show=truemybatis-plus.mapper-locations=classpath\*:mapper/\*Mapper.xml

读写分离测试代码（test-readandwrite）

@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class UserServiceImplTest {

    @Autowired
    UserService userService;

    @Test
    public void getAllUser(){
        List<User> list = userService.list();
        List<User> list1 = userService.list();
        List<User> list2 = userService.list();
        List<User> list3 = userService.list();
    }


    @Test
    public void saveUser(){
        for(int i=0;i<=10;i++){
            User user = new User();
            user.setName("张"+i);
            user.setAddr("海尔路"+i);
            user.setCreateTime(LocalDateTime.now());
            user.setUpdateTime(LocalDateTime.now());
            userService.save(user);
        }

    }

    @Test
    public void updateUser(){
        List<User> list = userService.list();
        list.forEach(user -> {
            user.setUpdateTime(LocalDateTime.now());
            userService.updateById(user);
        });
    }

    @Test
    public void deleteUser(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        boolean remove = userService.remove(queryWrapper);
    }

    @Test
    public void getUserById(){
        //强制路由
        //HintManager hintManager = HintManager.getInstance();
        //hintManager.setWriteRouteOnly();
        User byId = userService.getById(871042499328606208L);
        User byId1 = userService.getById(871042499328606208L);
        User byId2 = userService.getById(871042499328606208L);
    }

}

sharding-jdbc 分库分表

水平分表

# 数据源名称
spring.shardingsphere.datasource.names=ds0-master01

 

# ds0-master01连接池

spring.shardingsphere.datasource.ds0-master01.type=com.alibaba.druid.pool.DruidDataSource

# ds0-master01驱动

spring.shardingsphere.datasource.ds0-master01.driver-class-name=com.mysql.jdbc.Driver

# ds0-master01数据库地址

spring.shardingsphere.datasource.ds0-master01.url=jdbc:mysql://192.168.100.171:3306/test?useSSL=false

# ds0-master01数据库用户名

spring.shardingsphere.datasource.ds0-master01.username=root

# ds0-master01数据库密码

spring.shardingsphere.datasource.ds0-master01.password=123456

 

**#自动分片算法（默认 表名_0…. 表名_n）**

 

#############################################数据分片###################################################

#spring.shardingsphere.def

#数据源

spring.shardingsphere.rules.sharding.auto-tables.system1.actual-data-sources=ds0-master01

#主键

spring.shardingsphere.rules.sharding.tables.system1.key-generate-strategy.column=id

#主键算法名称

#spring.shardingsphere.rules.sharding.tables.system1.key-generate-strategy.key-generator-name=key-snowflake

# 雪花算法

#spring.shardingsphere.rules.sharding.key-generators.key-snowflake.type=SNOWFLAKE

# 雪花算法最大抖动上限值（）

#spring.shardingsphere.rules.sharding.key-generators.key-snowflake.props.max-vibration-offset=7

 

# 分片列名称

spring.shardingsphere.rules.sharding.auto-tables.system1.sharding-strategy.standard.sharding-column=project_id

# 分片算法名称

spring.shardingsphere.rules.sharding.auto-tables.system1.sharding-strategy.standard.sharding-algorithm-name=HASH_MOD

 

# 自动分片算法(取模分片算法MOD/哈希取模分片算法HASH_MOD/基于分片容量的范围分片算法VOLUME_RANGE/基于分片边界的范围分片算法BOUNDARY_RANGE/自动时间段分片算法AUTO_INTERVAL)

#取模分片算法MOD

spring.shardingsphere.rules.sharding.sharding-algorithms.method-mod.type=MOD

spring.shardingsphere.rules.sharding.sharding-algorithms.method-mod.props.sharding-count=3

 

#哈希取模分片算法HASH_MOD

spring.shardingsphere.rules.sharding.sharding-algorithms.method-hashmod.type=HASH_MOD

spring.shardingsphere.rules.sharding.sharding-algorithms.method-hashmod.props.sharding-count=3

 

#基于分片容量的范围分片算法VOLUME_RANGE

#该算法会根据 (${range-upper} - ${range-lower}) / ${sharding-volume} + 2 来算出实际的分片数量，从而实现自动分片功能

#这个算法之所以 + 2 ，实际上包含了两个隐藏表，用于储存小于 range-lower 和大于 range-upper 这两种情况的数据。

#(-∞..5)  [5,10)  [10,+∞) 3个表

spring.shardingsphere.rules.sharding.sharding-algorithms.method-volumerange.type=VOLUME_RANGE

spring.shardingsphere.rules.sharding.sharding-algorithms.method-volumerange.props.range-lower=5

spring.shardingsphere.rules.sharding.sharding-algorithms.method-volumerange.props.range-upper=10

spring.shardingsphere.rules.sharding.sharding-algorithms.method-volumerange.props.sharding-volume=5

 

#基于分片边界的范围分片算法BOUNDARY_RANGE

#(-∞..6) \[6.9) \[9,+∞)

spring.shardingsphere.rules.sharding.sharding-algorithms.method-boundaryrange.type=BOUNDARY_RANGE

spring.shardingsphere.rules.sharding.sharding-algorithms.method-boundaryrange.props.sharding-ranges=6,9

 

#自动时间段分片算法AUTO_INTERVAL

# ...-2023-05-01 00:00:00/2023-05-01 00:00:00-2023-05-02 00:00:00/2023-05-02 00:00:00-2023-05-03 00:00:00/..../2023-06-01 00:00:00-...

#每天一个表

spring.shardingsphere.rules.sharding.sharding-algorithms.method-autointerval.type=AUTO_INTERVAL

spring.shardingsphere.rules.sharding.sharding-algorithms.method-autointerval.props.datetime-lower=2023-05-01 00:00:00

spring.shardingsphere.rules.sharding.sharding-algorithms.method-autointerval.props.datetime-upper=2023-06-01 00:00:00

spring.shardingsphere.rules.sharding.sharding-algorithms.method-autointerval.props.sharding-seconds=86400

 

**#标准分片算法**

# 由数据源名+表名组成，以小数点分隔。多个表以逗号分隔，支持 inline 表达式。

# 缺省表示使用已知数据源与逻辑表名称生成数据节点，用于广播表（即每个库中都需要一个同样的表用于关联查询，多为字典表）或只分库不分表且所有库的表结构完全一致的情况

spring.shardingsphere.rules.sharding.tables.project.actual-data-nodes=ds0-master01.project_$->{0..2}

#分片键

spring.shardingsphere.rules.sharding.tables.project.key-generate-strategy.column=project_id

#主键生成算法

spring.shardingsphere.rules.sharding.tables.project.key-generate-strategy.key-generator-name=key-snowflake

# 分片列名称

spring.shardingsphere.rules.sharding.tables.project.table-strategy.standard.sharding-column=project_id

# 分片算法名称(标准分片算法INLINE)

spring.shardingsphere.rules.sharding.tables.project.table-strategy.standard.sharding-algorithm-name=HASH_MOD

# 标准分片算法(行表达式分片算法INLINE/时间范围分片算法INTERVAL)

# INLINE（行表达式分片算法）分库算法 method-inline（分库算法名称）

spring.shardingsphere.rules.sharding.sharding-algorithms.method-inline.type=INLINE

spring.shardingsphere.rules.sharding.sharding-algorithms.method-inline.props.algorithm-expression=project_$->{project_id%3}

 

# 时间范围分片算法INTERVAL)

# 该分片算法类似AUTO_INTERVAL，但比它要更加灵活，能够配置日期格式、分片后缀格式等，不过也因此配置更加复杂

spring.shardingsphere.rules.sharding.sharding-algorithms.method-interval.type=INTERVAL

spring.shardingsphere.rules.sharding.sharding-algorithms.method-interval.props.datetime-pattern=yyyy-MM-dd HH:mm:ss

spring.shardingsphere.rules.sharding.sharding-algorithms.method-interval.props.datetime-lower=2022-01-01 00:00:00

spring.shardingsphere.rules.sharding.sharding-algorithms.method-interval.props.datetime-interval-unit=MONTHS

spring.shardingsphere.rules.sharding.sharding-algorithms.method-interval.props.sharding-suffix-pattern=yyyyMM

**#复合行表达式分片算法**

**### 复合分片算法**

spring.shardingsphere.rules.sharding.tables.inner_device.actual-data-nodes=ds0-master01.inner_device_$->{0..1}_$->{0..1}

#主键

spring.shardingsphere.rules.sharding.tables.inner_device.key-generate-strategy.column=id

#主键生成算法

spring.shardingsphere.rules.sharding.tables.inner_device.key-generate-strategy.key-generator-name=key-snowflake

# 分片列名称，多个列以逗号分隔

spring.shardingsphere.rules.sharding.tables.inner_device.table-strategy.complex.sharding-columns=id,system_id

# 分片算法名称

spring.shardingsphere.rules.sharding.tables.inner_device.table-strategy.complex.sharding-algorithm-name=method_complex

#复合行表达式分片算法COMPLEX_INLINE

spring.shardingsphere.rules.sharding.sharding-algorithms.method_complex.type=COMPLEX_INLINE

spring.shardingsphere.rules.sharding.sharding-algorithms.method_complex.props.sharding-columns=id,system_id

spring.shardingsphere.rules.sharding.sharding-algorithms.method_complex.props.algorithm-expression=inner_device_$->{id%2}_$->{system_id%2}

spring.shardingsphere.rules.sharding.sharding-algorithms.method_complex.props.allow-range-query-with-inline-sharding=false

 

**#Hint 行表达式分片算法**

spring.shardingsphere.rules.sharding.tables.inner_device.table-strategy.hint.sharding-algorithm-name=method_hint

spring.shardingsphere.rules.sharding.sharding-algorithms.method_hint.type=HINT_INLINE

spring.shardingsphere.rules.sharding.sharding-algorithms.method_hint.props.algorithm-expression=inner_device_$->{value}_$->{value}

 

@Test

    public void testHint() {

        try (HintManager hintManager = HintManager.getInstance()) {

            hintManager.addTableShardingValue("inner_device", 1); // 指定hint注入值

            hintManager.addTableShardingValue("inner_device", 1); // 指定hint注入值

            System.out.println(innerDeviceService.getById(1664541144815554561L));

        }

    }

 

自动分片算法测试代码（test-split-tableonly）（hash）

@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class System1Test {

    @Autowired
    System1Service system1Service;

    @Test
    public void saveSystem(){
        for(int i=0;i<11;i++){
            System1Entity system1Entity = new System1Entity();
            system1Entity.setSystemName(i+"aaa");
            system1Entity.setMac("32423423234");
            system1Entity.setProjectId(Long.parseLong(i+""));
            system1Entity.setCreateTime(LocalDateTime.now());
            system1Service.save(system1Entity);
        }

    }

    @Test
    public void getSystemAll(){
        List<System1Entity> list = system1Service.list();
    }

    @Test
    public void getSystem(){
        QueryWrapper<System1Entity> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("project_id",3);
        List<System1Entity> list1 = system1Service.list(queryWrapper);
        System.out.println(list1);
    }

    @Test
    public void getSystemIn(){
        List<Long> list = new ArrayList<>();
        list.add(1L);
       // list.add(2L);
        QueryWrapper<System1Entity> queryWrapper = new QueryWrapper<>();
        //queryWrapper.ge("project_id",1L)
        //        .le("project_id",2L);
        queryWrapper.in("project_id",list);
        queryWrapper.eq("mac","sdaf");
        List<System1Entity> list1 = system1Service.list(queryWrapper);
        System.out.println(list1);
    }
}

标准分片算法测试代码（test-split-tableonly）（INLINE）

@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class MachineTest {

    @Autowired
    MachineService machineService;
    
     @Test
    public void saveMachine(){
        for(int i=0;i<=13;i++){
            InnerMachine innerMachine = new InnerMachine();
            innerMachine.setMachineName("sssfsdf0"+i);
            innerMachine.setAddr("海尔路");
            innerMachine.setContactName("wjc");
            innerMachine.setProjectNo(i);
            machineService.save(innerMachine);
            System.out.println(innerMachine);
        }
    }
    /**
     * @Desc 不支持范围查询
     * 使用 Groovy 的表达式，提供对 SQL 语句中的 = 和 IN 的分片操作支持，
     * 只支持单分片键。 对于简单的分片算法，可以通过简单的配置使用，
     * 类型：INLINE
     * @Author allblue
     * @Date 16:51 2023/6/27
     **/
    @Test
    public void getMachineBetween(){
        QueryWrapper<InnerMachine> queryWrapper = new QueryWrapper<>();
        queryWrapper.between("project_no",1L,1L);
        List<InnerMachine> list = machineService.list(queryWrapper);
        System.out.println(list);
    }

    @Test
    public void getMachine(){
        QueryWrapper<InnerMachine> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("project_no",1L);
        List<InnerMachine> list = machineService.list(queryWrapper);
        System.out.println(list);
    }

    @Test
    public void getMachineIn(){
        QueryWrapper<InnerMachine> queryWrapper = new QueryWrapper<>();
        queryWrapper.in("project_no", Arrays.asList(1,2));
        List<InnerMachine> list = machineService.list(queryWrapper);
        System.out.println(list);
    }

}

复合分片算法测试代码（test-split-tableonly）（COMPLEX_INLINE、HINT_INLINE）

@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class InnerTest {

    
    @Autowired
    InnerDeviceService innerDeviceService;
	
     /**
     * @Desc COMPLEX_INLINE
     * @param
     * @Author allblue
     * @Date 17:06 2023/6/27
     **/
    @Test
    public void saveInner(){
        for(int i=0;i<10;i++){
            InnerDeviceEntity innerDeviceEntity = new InnerDeviceEntity();
            innerDeviceEntity.setCreateTime(LocalDateTime.now());
            innerDeviceEntity.setName("内机"+i);
            innerDeviceEntity.setSystemId(Long.parseLong(i+""));
            innerDeviceService.save(innerDeviceEntity);
        }
    }
    /**
     * @Desc COMPLEX_INLINE
     * @param
     * @Author allblue
     * @Date 17:06 2023/6/27
     **/
    @Test
    public void getInnerList(){
        List<InnerDeviceEntity> list = innerDeviceService.list();
    }

    /**
     * @Desc COMPLEX_INLINE
     * @param
     * @Author allblue
     * @Date 17:06 2023/6/27
     **/
    @Test
    public void getInner(){
        QueryWrapper<InnerDeviceEntity> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("id",1664541144769417218L);
        queryWrapper.eq("system_id",1L);
        InnerDeviceEntity one = innerDeviceService.getOne(queryWrapper);
        System.out.println(one);
    }

    /**
     * @Desc 不支持除了=和in的操作
     * COMPLEX_INLINE
     * @param
     * @Author allblue
     * @Date 17:04 2023/6/27
     **/
    @Test
    public void getInnerBetween(){
        QueryWrapper<InnerDeviceEntity> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("id",1664541144769417218L);
        queryWrapper.between("system_id",1L,2L);
        InnerDeviceEntity one = innerDeviceService.getOne(queryWrapper);
        System.out.println(one);
    }

    @Test
    public void testHint() {
        try (HintManager hintManager = HintManager.getInstance()) {
            hintManager.addTableShardingValue("inner_device", 0); // 指定hint注入值
            hintManager.addTableShardingValue("inner_device", 1); // 指定hint注入值
            System.out.println(innerDeviceService.getById(1664541140663193601L));
        }
    }



}

绑定表

使用绑定表进行多表关联查询时，必须使用分片键进行关联，否则会出现笛卡尔积关联或跨库关联，从而影响查询效率。

#绑定表
spring.shardingsphere.rules.sharding.binding-tables[0]=project,system1

绑定表的分片规则要一致

配置完绑定表后再次进行关联查询的测试：

• 如果不配置绑定表：多表关联查询会出现笛卡尔积关联。

• 如果配置绑定表**：** 多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升。

  分片列名称

  spring.shardingsphere.rules.sharding.tables.project.table-strategy.standard.sharding-column=project_id

  分片算法名称(标准分片算法INLINE)

  spring.shardingsphere.rules.sharding.tables.project.table-strategy.standard.sharding-algorithm-name=HASH_MOD

  分片列名称

  spring.shardingsphere.rules.sharding.auto-tables.system1.sharding-strategy.standard.sharding-column=project_id

  分片算法名称

  spring.shardingsphere.rules.sharding.auto-tables.system1.sharding-strategy.standard.sharding-algorithm-name=HASH_MOD

绑定表测试代码（test-split-tableonly）

@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class ProjectTest {

    @Autowired
    ProjectService projectService;

    @Autowired
    System1Service system1Service;

    @Resource
    ProjectMapper projectMapper;
    
    @Test
    public void saveProjectAndSystem1() {

        Project project = new Project();
        project.setProjectName("sssfsdf0");
        project.setAddr("海尔路");
        project.setContactName("wjc");
        project.setProjectNo(2);
        project.setCreateTime(LocalDateTime.now());
        boolean save = projectService.save(project);
        LambdaQueryWrapper<Project> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.eq(Project::getProjectNo,2);
        Project one = projectService.getOne(lambdaQueryWrapper);
        if(save){
            System1Entity system1Entity = new System1Entity();
            system1Entity.setSystemName("aaa");
            system1Entity.setMac("32423423234");
            system1Entity.setProjectId(one.getProjectId());
            system1Entity.setCreateTime(LocalDateTime.now());
            system1Service.save(system1Entity);
        }
        System.out.println(project);

    }

    @Test
    public void getProjectAndSystem1() {
        List<ProjectAndSystem1> projectAndSystem1 = projectMapper.getProjectAndSystem1(875034977761230848L);
        System.out.println(projectAndSystem1);
    }
}

垂直分库(不同业务表分库保存)

# 数据源名称
spring.shardingsphere.datasource.names=ds0-master01,ds1-master01

# ds0-master01连接池

spring.shardingsphere.datasource.ds0-master01.type=com.alibaba.druid.pool.DruidDataSource

# ds0-master01驱动

spring.shardingsphere.datasource.ds0-master01.driver-class-name=com.mysql.jdbc.Driver

# ds0-master01数据库地址

spring.shardingsphere.datasource.ds0-master01.url=jdbc:mysql://192.168.100.171:3306/test?useSSL=false

# ds0-master01数据库用户名

spring.shardingsphere.datasource.ds0-master01.username=root

# ds0-master01数据库密码

spring.shardingsphere.datasource.ds0-master01.password=123456

 
# ds0-master01连接池

spring.shardingsphere.datasource.ds1-master01.type=com.alibaba.druid.pool.DruidDataSource

# ds0-master01驱动

spring.shardingsphere.datasource.ds1-master01.driver-class-name=com.mysql.jdbc.Driver

# ds0-master01数据库地址

spring.shardingsphere.datasource.ds1-master01.url=jdbc:mysql://192.168.100.171:3308/test?useSSL=false

# ds0-master01数据库用户名

spring.shardingsphere.datasource.ds1-master01.username=root

# ds0-master01数据库密码

spring.shardingsphere.datasource.ds1-master01.password=123456

 

#############################################数据分片###################################################

#spring.shardingsphere.rules.sharding.tables.test_area.actual-data-nodes=ds0-master01.test_area

#spring.shardingsphere.rules.sharding.tables.test_company.actual-data-nodes=ds1-master01.test_company

数据分片配置可配置可不配置，不配置框架会自动识别表所在的数据库

不需分库分表的配置（test_system表）

shardingjdbc5.x不参与分库分表，不需要配置，框架会自动识别

shardingjdbc5.x以前版本可以配置默认数据源

spring.shardingsphere.sharding.default-data-source-name=

垂直分库测试代码（test-split-dbonly）

@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class Test {

    @Autowired
    AreaService areaService;

    @Autowired
    CompanyService companyService;

    @org.junit.Test
    public void saveArea(){
        Area area = new Area();
        area.setCreateTime(LocalDateTime.now());
        area.setName("区域1");
        area.setLevel(1);
        areaService.save(area);
    }

    @org.junit.Test
    public void saveCompany(){
        Company company = new Company();
        company.setName("海尔");
        company.setAddr("海尔路");
        company.setCreateTime(LocalDateTime.now());
        companyService.save(company);
    }

    @org.junit.Test
    public void saveAreaAndCompany(){
        Area area = new Area();
        area.setCreateTime(LocalDateTime.now());
        area.setName("区域1");
        area.setLevel(1);
        areaService.save(area);
        Company company = new Company();
        company.setName("海尔");
        company.setAddr("海尔路");
        company.setCreateTime(LocalDateTime.now());
        companyService.save(company);
    }
}

水平分库（同表不同库保存）

 server.port=8088
spring.application.name=sharding-jdbc-test

spring.shardingsphere.mode.type=Standalone
spring.shardingsphere.mode.repository.type=File
spring.shardingsphere.mode.repository.props.path=C:/Users/allblue/Desktop/sql
spring.shardingsphere.mode.overwrite=true


# 数据源名称
spring.shardingsphere.datasource.names=master01,master02

# 主数据库连接池
spring.shardingsphere.datasource.master01.type=com.alibaba.druid.pool.DruidDataSource
# 主数据库连接驱动
spring.shardingsphere.datasource.master01.driver-class-name=com.mysql.jdbc.Driver
# 主数据库URL地址
spring.shardingsphere.datasource.master01.url=jdbc:mysql://192.168.100.171:3306/test?useSSL=false
# 主数据源用户名
spring.shardingsphere.datasource.master01.username=root
# 主数据源密码
spring.shardingsphere.datasource.master01.password=123456


spring.shardingsphere.datasource.master02.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master02.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master02.url=jdbc:mysql://192.168.100.171:3308/test?useSSL=false
spring.shardingsphere.datasource.master02.username=root
spring.shardingsphere.datasource.master02.password=123456

#分库
spring.shardingsphere.rules.sharding.tables.inner_charge.actual-data-nodes=master0$->{1..2}.inner_charge
spring.shardingsphere.rules.sharding.tables.inner_charge.database-strategy.standard.sharding-column=project_id
spring.shardingsphere.rules.sharding.tables.inner_charge.database-strategy.standard.sharding-algorithm-name= method-inline

# 标准分片算法(行表达式分片算法INLINE）
spring.shardingsphere.rules.sharding.sharding-algorithms.method-inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.method-inline.props.algorithm-expression=master0$->{project_id%2+1}

#是否显示sql
spring.shardingsphere.props.sql-show=true

mybatis-plus.mapper-locations=classpath*:mapper/*Mapper.xml

水平分库测试代码（test-split-dbandtable）

@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class InnerChargeImplTest {

    @Autowired
    InnerChargeService innerChargeService;

    @Test
    //@Transactional(rollbackFor = Exception.class)
    public void saveInnerCharge(){
        for(long i=1;i<=10;i++){
            InnerCharge innerCharge = new InnerCharge();
            innerCharge.setInnerId(i);
            innerCharge.setProjectId(i);
            innerCharge.setRunTime(10);
            innerCharge.setCreateTime(LocalDateTime.now());
            innerChargeService.save(innerCharge);
        }
    }

    @Test
    public void saveInnerChargeBatch(){
        List<InnerCharge> innerCharges = new ArrayList<>();
        for(long i=1;i<=10;i++){
            InnerCharge innerCharge = new InnerCharge();
            innerCharge.setInnerId(i);
            innerCharge.setProjectId(i);
            innerCharge.setRunTime(10);
            innerCharge.setCreateTime(LocalDateTime.now());
            innerCharges.add(innerCharge);
        }
        innerChargeService.saveBatch(innerCharges);
    }


    @Test
    public void getInnerChargeList(){
        List<InnerCharge> list = innerChargeService.list();
        System.out.println(list);
    }

    @Test
    public void getInnerCharge(){
        QueryWrapper<InnerCharge> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("project_id",1);
        InnerCharge one = innerChargeService.getOne(queryWrapper);
        System.out.println(one);
    }

}

广播表

广播表测试代码（test-split-dbandtable）

spring.shardingsphere.rules.sharding.broadcast-tables[0]=t_dict
/**
  *每个服务器中的t_dict同时添加了新数据
**/
@Test
    public void saveDict(){
        Dict dict = new Dict();
        dict.setCode(1);
        dict.setName("山东");
        dict.setCreateTime(LocalDateTime.now());
        dictService.save(dict);
    }
/**
  *只从一个数据库中查询结果
**/
@Test
    public void getDict(){
        LambdaQueryWrapper<Dict> dictLambdaQueryWrapper = new LambdaQueryWrapper<>();
        dictLambdaQueryWrapper.eq(Dict::getCode,1);
        Dict one = dictService.getOne(dictLambdaQueryWrapper);
        System.out.println(one);
    }