spring boot + mybatisplus + mysql + druid 实现数据分表操作

一，maven引入jar包

<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.1.21</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.23</version>
    </dependency>

    <!-- mybatis plus 代码生成器 -->
    <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-boot-starter</artifactId>
      <version>3.1.1</version>
    </dependency>
    <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-generator</artifactId>
      <version>3.2.0</version>
    </dependency>
<!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/sharding-jdbc-spring-boot-starter -->
    <dependency>
      <groupId>org.apache.shardingsphere</groupId>
      <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
      <version>4.1.1</version>
    </dependency>

二，properties配置

spring.shardingsphere.datasource.names=ds
spring.shardingsphere.datasource.ds.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds.url=jdbc:mysql://localhost:3306/ds?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
spring.shardingsphere.datasource.ds.username=root
spring.shardingsphere.datasource.ds.password=123123

#spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
#spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
#spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/bank1?useUnicode=true&characterEncoding=UTF-8
#spring.shardingsphere.datasource.ds1.username=root
#spring.shardingsphere.datasource.ds1.password=

spring.shardingsphere.sharding.tables.account_info.actual-data-nodes=ds.account_info$->{0..1}
spring.shardingsphere.sharding.tables.account_info.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.account_info.table-strategy.inline.algorithm-expression=account_info$->{id % 2}
spring.shardingsphere.sharding.tables.account_info.key-generator.column=id
spring.shardingsphere.sharding.tables.account_info.key-generator.type=SNOWFLAKE

#spring.shardingsphere.sharding.tables.account_info_item.actual-data-nodes=ds$->{0..1}.account_info_item$->{0..1}
#spring.shardingsphere.sharding.tables.account_info_item.table-strategy.inline.sharding-column=order_id
#spring.shardingsphere.sharding.tables.account_info_item.table-strategy.inline.algorithm-expression=account_info_item$->{order_id % 2}
#spring.shardingsphere.sharding.tables.account_info_item.key-generator.column=order_item_id
#spring.shardingsphere.sharding.tables.account_info_item.key-generator.type=SNOWFLAKE

#spring.shardingsphere.sharding.binding-tables=account_info
#spring.shardingsphere.sharding.broadcast-tables=t_config
spring.shardingsphere.props.sql.show=true

#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2}


#spring.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
#spring.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
#spring.datasource.ds0.url=jdbc:mysql://localhost:3306/bank1?useUnicode=true&characterEncoding=UTF-8
#spring.datasource.ds0.username=root
#spring.datasource.ds0.password=123123
#spring.datasource.ds0.initialSize=5
#spring.datasource.ds0.minIdle=5
#spring.datasource.ds0.maxActive=20
#spring.datasource.ds0.maxWait=60000
#spring.datasource.ds0.timeBetweenEvictionRunsMillis=60000
#spring.datasource.ds0.minEvictableIdleTimeMillis=300000
#spring.datasource.ds0.validationQuery=SELECT 1 FROM DUAL
#spring.datasource.ds0.testWhileIdle=true
#spring.datasource.ds0.testOnBorrow=false
#spring.datasource.ds0.testOnReturn=false
#spring.datasource.ds0.poolPreparedStatements=true
#spring.datasource.ds0.maxPoolPreparedStatementPerConnectionSize=20
#spring.datasource.ds0.filters=stat,wall,log4j
#spring.datasource.ds0.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
#spring.datasource.ds0.useGlobalDataSourceStat=true

mybatis-plus.configuration.map-underscore-to-camel-case=true
mybatis-plus.configuration.auto-mapping-behavior=full
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
mybatis-plus.mapper-locations=classpath*:/mapper/**/*.xml
mybatis-plus.global-config.db-config.logic-not-delete-value=1
mybatis-plus.global-config.db-config.logic-delete-value=0

三，重写DataSourceHealthContributorAutoConfiguration    AbstractHealthIndicator方法避开spring boot健康检查，避免后续isValid错误

package com.tp.user_server2.config;

import org.springframework.beans.factory.ObjectProvider;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.actuate.autoconfigure.jdbc.DataSourceHealthContributorAutoConfiguration;
import org.springframework.boot.actuate.health.AbstractHealthIndicator;
import org.springframework.boot.actuate.jdbc.DataSourceHealthIndicator;
import org.springframework.boot.jdbc.metadata.DataSourcePoolMetadataProvider;
import org.springframework.context.annotation.Configuration;
import org.springframework.util.StringUtils;

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

@Configuration
public class ShardingJdbcDataSourceHealthConfig extends DataSourceHealthContributorAutoConfiguration {

    @Value("${spring.datasource.dbcp2.validation-query:select 1}")
    private String defaultQuery;

    public ShardingJdbcDataSourceHealthConfig(Map<String, DataSource> dataSources, ObjectProvider<DataSourcePoolMetadataProvider> metadataProviders) {
        super(dataSources, metadataProviders);
    }


    @Override
    protected AbstractHealthIndicator createIndicator(DataSource source) {
        DataSourceHealthIndicator indicator = (DataSourceHealthIndicator) super.createIndicator(source);
        if (!StringUtils.hasText(indicator.getQuery())) {
            indicator.setQuery(defaultQuery);
        }
        return indicator;
    }
}

四，控制类做业务测试

@ResponseBody
    @GetMapping(value = "/saveAccount")
    public String saveAccount(){

        AccountInfo accountInfo = new AccountInfo();
        accountInfo.setAccountName("王五");
        accountInfoService.save(accountInfo);

        AccountInfo accountInfo1 = new AccountInfo();
        accountInfo1.setAccountName("赵六");
        accountInfoService.save(accountInfo1);
        return "1";
    }

五，结果展示，后续做分库分表实验