SpringBoot + Mybatis-Plus + Sharding-JDBC实现数据库分表以及读写分离

原创

小小土豆dev

发布于 2024-01-19 16:39:56

5120

发布于 2024-01-19 16:39:56

文章被收录于专栏：Java之家

本文使用的数据库是一主两从，实现数据分表，以及读写分离功能。

本文Java工程使用Maven搭建，基于SpringBoot框架，ORM框架使用Mybatis-Plus（建议自己先搭建下Demo工程）。

Sharding-JDBC支持Mybatis-Plus，不需要针对Mybatis-Plus进行额外的配置，只需要配置自身即可。

第1步：创建数据库表

先创建t_student表，然后再创建t_student_0 ... t_student_9 10张分表。

DROP TABLE if EXISTS t_student;
CREATE TABLE t_student (
`id` BIGINT NOT NULL auto_increment COMMENT '主键', 
`name` VARCHAR(20) COMMENT '姓名', 
`number` VARCHAR(10) COMMENT '学号',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_number` (`number`) USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE = utf8_general_ci ROW_FORMAT=DYNAMIC COMMENT '学生表';

第2步：工程配置

pom.xml文件配置

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.6</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <groupId>com.devpotato.sharding-jdbc</groupId>
    <artifactId>sharding-jdbc</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <name>sharding-jdbc</name>
    <description>sharding-jdbc</description>

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

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

        <!--   Sharding-jdbc的spring-boot依赖   -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>4.1.1</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.21</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.25</version>
        </dependency>
    </dependencies>
</project>

application.yml文件配置

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      initial-size: 5
      min-idle: 5
      max-active: 20
      max-wait: 60000
      min-evictable-idle-time-millis: 300000
      time-between-eviction-runs-millis: 60000
      validation-query: SELECT 1
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=1000
      default-auto-commit: true

jdbc:
  ds_master_0:
    driver: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
    username: root
    password: 12345678
  ds_master_0_slave_0:
    driver: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
    username: root
    password: 12345678
  ds_master_0_slave_1:
    driver: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
    username: root
    password: 12345678

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

第4步：Sharding-JDBC配置（基于JavaConfig格式配置）

1.使用 @ConfigurationProperties 读取datasource配置

@Data
@Component
@ConfigurationProperties(prefix = "spring.datasource.druid")
public class DataSourceProperties {
    private Integer initialSize;

    private Integer minIdle;

    private Integer maxActive;

    private Integer maxWait;

    private Integer minEvictableIdleTimeMillis;

    private Integer timeBetweenEvictionRunsMillis;

    private String validationQuery;

    private Boolean testWhileIdle;

    private Boolean testOnBorrow;

    private Boolean testOnReturn;

    private Boolean poolPreparedStatements;

    private Integer maxPoolPreparedStatementPerConnectionSize;

    private String filter;

    private String connectionProperties;

    private Boolean defaultAutoCommit;
}

2.配置 Sharding-JDBC

@Configuration
public class ShardingJdbcConfiguration {

    // ds_master_0
    @Value("${jdbc.ds_master_0.driver}")
    private String master0DriverName;

    @Value("${jdbc.ds_master_0.url}")
    private String master0Url;

    @Value("${jdbc.ds_master_0.username}")
    private String master0UserName;

    @Value("${jdbc.ds_master_0.password}")
    private String master0Password;

    // ds_master_0_slave_0
    @Value("${jdbc.ds_master_0_slave_0.driver}")
    private String master0Slave0DriverName;

    @Value("${jdbc.ds_master_0_slave_0.url}")
    private String master0Slave0Url;

    @Value("${jdbc.ds_master_0_slave_0.username}")
    private String master0Slave0UserName;

    @Value("${jdbc.ds_master_0_slave_0.password}")
    private String master0Slave0Password;

    // ds_master_0_slave_1
    @Value("${jdbc.ds_master_0_slave_1.driver}")
    private String master0Slave1DriverName;

    @Value("${jdbc.ds_master_0_slave_1.url}")
    private String master0Slave1Url;

    @Value("${jdbc.ds_master_0_slave_1.username}")
    private String master0Slave1UserName;

    @Value("${jdbc.ds_master_0_slave_1.password}")
    private String master0Slave1Password;

    @Autowired
    private DataSourceProperties dataSourceProperties;

    @Bean
    public DataSource getDataSource() throws SQLException {
        // 配置分片规则（数据分片 + 读写分离）
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(getStudentTableRuleConfiguration());
        shardingRuleConfig.setMasterSlaveRuleConfigs(getMasterSlaveRuleConfigurations());

        return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, getProperties());
    }

    // 配置读写分离规则
    private List<MasterSlaveRuleConfiguration> getMasterSlaveRuleConfigurations() {
        MasterSlaveRuleConfiguration masterSlaveRuleConfig1 = new MasterSlaveRuleConfiguration(
                "ds_0",
                "ds_master_0",
                Arrays.asList("ds_master_0_slave_0", "ds_master_0_slave_1"));
        return Lists.newArrayList(masterSlaveRuleConfig1);
    }

    private Map<String, DataSource> createDataSourceMap() throws SQLException {
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        dataSourceMap.put("ds_master_0", getMasterDataSource());
        dataSourceMap.put("ds_master_0_slave_0", getSlaveDataSource1());
        dataSourceMap.put("ds_master_0_slave_1", getSlaveDataSource2());

        return dataSourceMap;
    }

    // 配置t_student表规则（配置分库 + 分表策略）
    // 基于number字段进行分表（10张表）
    private TableRuleConfiguration getStudentTableRuleConfiguration() {
        TableRuleConfiguration tableRuleConfiguration = new TableRuleConfiguration(
                "t_student",
                "ds_${0}.t_student_${0..9}");
        tableRuleConfiguration.setTableShardingStrategyConfig(
                new InlineShardingStrategyConfiguration(
                "number",
                "t_student_${(number.hashCode() & Integer.MAX_VALUE) % 10}"));
        tableRuleConfiguration.setKeyGeneratorConfig(getKeyGeneratorConfiguration());

        return tableRuleConfiguration;
    }

    // 主键的生成策略（雪花算法）
    private KeyGeneratorConfiguration getKeyGeneratorConfiguration() {
        KeyGeneratorConfiguration result = new KeyGeneratorConfiguration("SNOWFLAKE", "id");
        return result;
    }

    private Properties getProperties() {
        Properties properties = new Properties();
        //是否打印SQL解析和改写日志
        properties.put("sql.show", true);

        return properties;
    }

    private DruidDataSource getMasterDataSource() throws SQLException {
        DruidDataSource dataSource = getDruidDataSource();
        dataSource.setUrl(master0Url);
        dataSource.setDriverClassName(master0DriverName);
        dataSource.setUsername(master0UserName);
        dataSource.setPassword(master0Password);

        return dataSource;
    }

    private DruidDataSource getSlaveDataSource1() throws SQLException {
        DruidDataSource dataSource = getDruidDataSource();
        dataSource.setUrl(master0Slave0Url);
        dataSource.setDriverClassName(master0Slave0DriverName);
        dataSource.setUsername(master0Slave0UserName);
        dataSource.setPassword(master0Slave0Password);

        return dataSource;
    }

    private DruidDataSource getSlaveDataSource2() throws SQLException {
        DruidDataSource dataSource = getDruidDataSource();
        dataSource.setUrl(master0Slave1Url);
        dataSource.setDriverClassName(master0Slave1DriverName);
        dataSource.setUsername(master0Slave1UserName);
        dataSource.setPassword(master0Slave1Password);

        return dataSource;
    }

    private DruidDataSource getDruidDataSource() throws SQLException {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setInitialSize(dataSourceProperties.getInitialSize());
        dataSource.setMinIdle(dataSourceProperties.getMinIdle());
        dataSource.setMaxActive(dataSourceProperties.getMaxActive());
        dataSource.setMaxWait(dataSourceProperties.getMaxWait());
        dataSource.setTimeBetweenEvictionRunsMillis(dataSourceProperties.getTimeBetweenEvictionRunsMillis());
        dataSource.setMinEvictableIdleTimeMillis(dataSourceProperties.getMinEvictableIdleTimeMillis());
        dataSource.setValidationQuery(dataSourceProperties.getValidationQuery());
        dataSource.setTestWhileIdle(dataSourceProperties.getTestWhileIdle());
        dataSource.setTestOnBorrow(dataSourceProperties.getTestOnBorrow());
        dataSource.setTestOnReturn(dataSourceProperties.getTestOnReturn());
        dataSource.setPoolPreparedStatements(dataSourceProperties.getPoolPreparedStatements());
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(dataSourceProperties.getMaxPoolPreparedStatementPerConnectionSize());
        dataSource.setFilters(dataSourceProperties.getFilter());
        dataSource.setConnectionProperties(dataSourceProperties.getConnectionProperties());
        dataSource.setDefaultAutoCommit(dataSourceProperties.getDefaultAutoCommit());

        return dataSource;
    }
}