您现在的位置是：首页 >技术交流 >JDK11+mybatis-plus+shardingsphere分库分表网站首页技术交流

1、引入jar
dynamic-datasource-spring-boot-starter：2.5.6
sharding-jdbc-spring-boot-starter：4.1.1

<dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
        </dependency>
        <!-- 分库分表-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <exclusions>
                <exclusion>
                    <groupId>org.codehaus.groovy</groupId>
                    <artifactId>groovy</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

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

        <dependency>
            <groupId>org.codehaus.groovy</groupId>
            <artifactId>groovy</artifactId>
        </dependency>

2、配置

#mysql数据库com.baomidou.dynamic.datasource.spring.boot.autoconfigure.hikari.HikariCpConfig（需要引入book-mysql.yml属性信息）
# 配置默认数据源
spring:
  datasource:
    dynamic:
      primary: system
      datasource:
        # 数据源-1，名称为 system 用户名密码：com.book.modules.helper.EncryptionHelper
        system:
          username: ENC(vZaezUSyvM1a75RjyRUpRZM/lASLKLbn)
          password: ENC(gzHe0K7Bdn0/C5fXvBkOhubOWiuQ9G8v)
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://10.0.0.63:3306/book_system?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2b8        
  #分库分表配置
  shardingsphere:
    datasource:
      names: sharding-system
      props:
        # 日志显示SQL
        sql:
          show: true
      sharding-system:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://10.0.0.63:3306/book_system?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2b8 
        username: ENC(vZaezUSyvM1a75RjyRUpRZM/lASLKLbn)
        password: ENC(gzHe0K7Bdn0/C5fXvBkOhubOWiuQ9G8v)
    sharding:
      tables:
        # 日志名称表 分表：5
        s_log_info:
          # 真实表 s_log_info_5 - s_log_info_5
          actual-data-nodes: sharding-system.s_log_info_$->{0..5}
          # 分库策略
          database-strategy:
            none:
          # 分表策略
          table-strategy:
            inline:
              #分表字段，日志系统来源服务器
              shardingColumn: application_name
              # 分片算法行表达式，需符合groovy语法 '& Integer.MAX_VALUE' 位运算使hash值为正数
              algorithmExpression: s_log_info_$->{(application_name.hashCode() & Integer.MAX_VALUE) % 5}

3、建表

4、把创建的DataSource加入动态数据源Map，让@DS可以获取ShardingDataSource

package com.book.shard.config;

import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.provider.AbstractDataSourceProvider;
import com.baomidou.dynamic.datasource.provider.DynamicDataSourceProvider;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceAutoConfiguration;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import com.book.common.constant.Constant;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.shardingsphere.shardingjdbc.jdbc.adapter.AbstractDataSourceAdapter;
import org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.jdbc.metadata.DataSourcePoolMetadata;
import org.springframework.boot.jdbc.metadata.DataSourcePoolMetadataProvider;
import org.springframework.boot.jdbc.metadata.HikariDataSourcePoolMetadata;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.Map;

/**
 * Description：
 * <p>
 * 需要存在 spring.shardingsphere.datasource.names属性
 * </p>
 *
 * @Author： leo.xiong
 * @CreateDate： 2023/5/25 16:47
 * @Email： leo.xiong@suyun360.com
 * @Since：
 */
@Configuration(value = "shardingConfiguration")
public class ShardingConfig {
    /**
     * 测试SQL，默认使用Mysql
     */
    @Value("${spring.datasource.dynamic.hikari.connection-test-query:select 1}")
    private String testSql;
    /**
     * 动态数据源配置项
     */
    @Autowired
    private DynamicDataSourceProperties properties;
    /**
     * shardingjdbc有四种数据源，需要根据业务注入不同的数据源
     *
     * <p>1. 未使用分片, 脱敏的名称(默认): shardingDataSource;
     * <p>2. 主从数据源: masterSlaveDataSource;
     * <p>3. 脱敏数据源：encryptDataSource;
     * <p>4. 影子数据源：shadowDataSource
     * 实现 ${@link SpringBootConfiguration} 加载Bean对象
     */
    @Resource(name = Constant.BeanName.SHARDING_DATASOURCE)
    private AbstractDataSourceAdapter shardingDataSource;

    /**
     * 动态数据源Bean
     * {@link DynamicDataSourceAutoConfiguration}
     *
     * @return
     */
    @Bean
    public DynamicDataSourceProvider dynamicDataSourceProvider() {
        Map<String, DataSourceProperty> datasourceMap = properties.getDatasource();
        return new AbstractDataSourceProvider() {
            @Override
            public Map<String, DataSource> loadDataSources() {
                Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
                //把分库分表的数据源加入到动态数据源的map中，使之可以通过@DS(数据源名称加载)
                for (Map.Entry<String, DataSource> dataSourceEntry : shardingDataSource.getDataSourceMap().entrySet()) {
                    String key = dataSourceEntry.getKey();
                    dataSourceMap.put(key, shardingDataSource);
                }
                return dataSourceMap;
            }
        };
    }

    /**
     * 将动态数据源设置为首选的
     * 当spring存在多个数据源时, 自动注入的是首选的对象
     * 设置为主要的数据源之后，就可以支持shardingjdbc原生的配置方式了
     *
     * @return
     */
    @Primary
    @Bean
    public DataSource dataSource(DynamicDataSourceProvider dynamicDataSourceProvider) {
        DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
        dataSource.setPrimary(properties.getPrimary());
        dataSource.setStrict(properties.getStrict());
        dataSource.setStrategy(properties.getStrategy());
        dataSource.setProvider(dynamicDataSourceProvider);
        dataSource.setP6spy(properties.getP6spy());
//        dataSource.setSeata(properties.getSeata());
        return dataSource;
    }

    /**
     * 新版Spring中,Spring数据源健康检查用到 sharding jdbc 时,该组件没有完全实现MySQL驱动导致的问题
     * 是由于 ShardingDataSource 内部是封装了真实数据源的，所以 ShardingDataSource 并不需要进行健康检查
     *
     * @return
     */
    @Bean
    @ConditionalOnProperty(prefix = "spring.shardingsphere.datasource", name = "names")
    DataSourcePoolMetadataProvider dataSourcePoolMetadataProvider() {
        return dataSource -> dataSource instanceof HikariDataSource
                // 这里如果所使用的数据源没有对应的 DataSourcePoolMetadata 实现的话也可以全部使用 NotAvailableDataSourcePoolMetadata
                ? new HikariDataSourcePoolMetadata((HikariDataSource) dataSource)
                : new NotAvailableDataSourcePoolMetadata(testSql);
    }

    /**
     * 不可用的数据源池元数据.
     */
    private static class NotAvailableDataSourcePoolMetadata implements DataSourcePoolMetadata {

        private String testSql;

        public NotAvailableDataSourcePoolMetadata(String testSql) {
            this.testSql = testSql;
        }

        @Override
        public Float getUsage() {
            return null;
        }

        @Override
        public Integer getActive() {
            return null;
        }

        @Override
        public Integer getMax() {
            return null;
        }

        @Override
        public Integer getMin() {
            return null;
        }

        @Override
        public String getValidationQuery() {
            return testSql;
        }

        @Override
        public Boolean getDefaultAutoCommit() {
            return null;
        }
    }
}

5、使用动态数据源指定分表数据源

6、调试
打开org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration类，断点在，查看shardingRule规则是否正常加载，之后看代码是否正常启动


7、结果

8、总结：
1、先加载分库分表规则
2、根据@DS的值获取数据源，数据源就是配置的name，会作为map的key
3、获取指定的数据源后，会包装获取对应org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.ShardingConnection
4、根据ShardingConnection.prepareStatement开始更换表信息；
5、根据数据库语言获取对应的解析，如MYSQL等；
6、开始根据规则进行路由到对应的节点，对应的数据库和表；
7、替换SQL；
8、执行SQL；

多种配置方式

#分库分表配置，找到对应的库对应的表
spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
      #数据库名称
      names: ds0,ds1
      ds0:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://xxx:3300/edu_db_2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
        password: root
        username: root
      ds1:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://127.0.0.1:3306/edu_db_1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
        password: root
        username: root
    #打印sql
    props:
      sql:
        show: true
    sharding:
      #分表
      tables:
        #数据分表规则
        #指定所需分的数据库和表的分布情况
        course:    #表前缀
          actual-data-nodes: ds$->{0..1}.course_$->{1..2}
          # 使用SNOWFLAKE算法生成主键
          key-generator:
            column: cid
            type: SNOWFLAKE
          table-strategy:
            inline:
              #分表规则
              algorithm-expression: course_$->{cid % 2 + 1}
              #指定主键
              sharding-column: cid
          #分库
          database-strategy:
            inline:
              algorithm-expression: ds$->{user_id % 2}
              sharding-column: user_id
			  
#垂直分库			  
spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
      #数据库名称
      names: ds0,ds1,ds2
      ds0:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://8.131.119.145:3300/edu_db_2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
        password: root
        username: root
      ds1:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://127.0.0.1:3306/edu_db_1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
        password: root
        username: root
      ds2:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://127.0.0.1:3306/user_db?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
        password: root
        username: root
    #打印sql
    props:
      sql:
        show: true
    sharding:
      #分表
      tables:
        #数据分表规则
        #指定所需分的数据库和表的分布情况
        t_user: #表前缀
          actual-data-nodes: ds$->{2}.t_user
          # 使用SNOWFLAKE算法生成主键
          key-generator:
            column: user_id
            type: SNOWFLAKE
          table-strategy:
            inline:
              #分表规则(无规则就是这样，专库专表)
              algorithm-expression: t_user
              #指定主键
              sharding-column: user_id

        course:    #表前缀
          actual-data-nodes: ds$->{0..1}.course_$->{1..2}
          # 使用SNOWFLAKE算法生成主键
          key-generator:
            column: cid
            type: SNOWFLAKE
          table-strategy:
            inline:
              #分表规则
              algorithm-expression: course_$->{cid % 2 + 1}
              #指定主键
              sharding-column: cid

          #分库
          database-strategy:
            inline:
              algorithm-expression: ds$->{user_id % 2}
              sharding-column: user_id			  
#公共表操作			  
spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
      #数据库名称
      names: ds0,ds1,ds2
      ds0:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://8.131.119.145:3300/edu_db_2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
        password: root
        username: root
      ds1:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://127.0.0.1:3306/edu_db_1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
        password: root
        username: root
      ds2:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://127.0.0.1:3306/user_db?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
        password: root
        username: root
    #打印sql
    props:
      sql:
        show: true
    sharding:
      #分表
      tables:
        #数据分表规则
        #指定所需分的数据库和表的分布情况
        t_user: #表前缀
          actual-data-nodes: ds$->{2}.t_user
          # 使用SNOWFLAKE算法生成主键
          key-generator:
            column: user_id
            type: SNOWFLAKE
          table-strategy:
            inline:
              #分表规则(无规则就是这样，专库专表)
              algorithm-expression: t_user
              #指定主键
              sharding-column: user_id

        course:    #表前缀
          actual-data-nodes: ds$->{0..1}.course_$->{1..2}
          # 使用SNOWFLAKE算法生成主键
          key-generator:
            column: cid
            type: SNOWFLAKE
          table-strategy:
            inline:
              #分表规则
              algorithm-expression: course_$->{cid % 2 + 1}
              #指定主键
              sharding-column: cid

          #分库
          database-strategy:
            inline:
              algorithm-expression: ds$->{user_id % 2}
              sharding-column: user_id

        #公共表配置
        t_udict:
            # 使用SNOWFLAKE算法生成主键
            key-generator:
              column: dictid
              type: SNOWFLAKE
      #公共表配置
      broadcast-tables: t_udict		
	  
#读写分离	  
spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
      #数据库名称
      names: ds0,s0
      #主服务器
      ds0:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://8.131.119.145:3300/user_db?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
        password: root
        username: root
      #从服务器
      s0:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://127.0.0.1:3306/user_db?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
        password: root
        username: root
    #打印sql
    props:
      sql:
        show: true
    sharding:
      master-slave-rules:
        #load-balance-algorithm-type: round_robin

        #指向的主数据库名称
        ds0:
          master-data-source-name: ds0
          #多个丛用逗号隔开
          slave-data-source-names: s0
      tables:
        t_user:
          actual-data-nodes: ds0.t_user