springboot+shardingjdbc+yml单库分表(按月分表)

1，引入相应jar

<dependency>
        <groupId>com.dangdang</groupId>
        <artifactId>sharding-jdbc-config-spring</artifactId>
        <version>1.5.4.1</version>
    </dependency>
    <dependency>
        <groupId>io.shardingjdbc</groupId>
        <artifactId>sharding-jdbc-core</artifactId>
        <version>2.0.3</version>
    </dependency>

2，新建数据源配置类（这里我们用的是

com.zaxxer.hikari.HikariDataSource）

package com.digigd.marketadmin.config;//package com.digigd.marketadmin.config;

import cn.hutool.core.date.DateUtil;
import com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.TableRule;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy;
import com.digigd.marketadmin.common.ShardingAlgorithm.SingleKeyDynamicModuloTableShardingAlgorithm;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;

/**
 * @author 邓
 * @Title: ${file_name}
 * @Package ${package_name}
 * @Description: ${todo}
 * @date 2020/7/814:53
 */
@Configuration
@EnableTransactionManagement
@ConditionalOnClass(HikariDataSource.class)
 @EnableConfigurationProperties(ShardDataSourceProperties.class)
public class ShardDataSourceConfig {
    private final static String log_base_info = "log_base_info";

    @Autowired
    private ShardDataSourceProperties shardDataSourceProperties;

    private ShardingRule shardingRule() throws SQLException {
        return ShardingRule.builder()
                .dataSourceRule(getDataSourceRule())
                .tableRules(Arrays.asList(getTableRule())).tableShardingStrategy(new TableShardingStrategy("create_time", new SingleKeyDynamicModuloTableShardingAlgorithm("log_base_info")))
                .build();
    }

    private DataSourceRule getDataSourceRule() throws SQLException {
        Map<String, DataSource> dataSourceMap = new HashMap<>(2);
        dataSourceMap.put("ds", ds());
        DataSourceRule dataSourceRule = new DataSourceRule(dataSourceMap);
        return dataSourceRule;
    }

    /**
     * @desc:获取数据源
     * @return
     * @throws SQLException
     */
    private HikariDataSource ds() throws SQLException {
        HikariDataSource ds = parentDs();
        return ds;
    }


    /**
     * 获取表的定义规则
     * @return
     * @throws SQLException
     */
    private TableRule getTableRule() throws SQLException {

        String date = DateUtil.format(new Date(),"yyyyMM");
        // 按月动态分表
        TableRule logBaseInfo = TableRule.builder(log_base_info).
                tableShardingStrategy(new TableShardingStrategy("create_time",
                        new SingleKeyDynamicModuloTableShardingAlgorithm("log_base_info_"))).
                dataSourceRule(getDataSourceRule()).actualTables(Arrays.asList("log_base_info_"+date)).dynamic(false).build();
    return logBaseInfo;
    }

    private HikariDataSource parentDs() throws SQLException {
        HikariDataSource ds = new HikariDataSource();
        ds.setDriverClassName(shardDataSourceProperties.getDriverClassName());
        ds.setJdbcUrl(shardDataSourceProperties.getUrl());
        ds.setUsername(shardDataSourceProperties.getUsername());
        ds.setPassword(shardDataSourceProperties.getPassword());
        ds.setAutoCommit(shardDataSourceProperties.getAutoCommit());
        ds.setConnectionTestQuery(shardDataSourceProperties.getConnectionTestQuery());
        ds.setConnectionTimeout(shardDataSourceProperties.getConnectionTimeOut());
        ds.setIdleTimeout(shardDataSourceProperties.getIdleTimeout());
        ds.setPoolName(shardDataSourceProperties.getPoolName());
        ds.setMaxLifetime(shardDataSourceProperties.getMaxLifeTime());
        return ds;
    }

    @Bean
    public DataSource dataSource() throws SQLException {
        return com.dangdang.ddframe.rdb.sharding.api.ShardingDataSourceFactory.createDataSource(shardingRule());
    }
}

3，新建配置项属性类

package com.digigd.marketadmin.config;//package com.digigd.marketadmin.config;

import lombok.Data;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;


/**
 * @author 邓
 * @Title: ${file_name}
 * @Package ${package_name}
 * @Description: ${todo}
 * @date 2020/7/814:53
 * @Copyright 广东省xxxx有限公司
 */


@ConfigurationProperties
@Data
public class ShardDataSourceProperties {
    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;
    @Value("${spring.datasource.url}")
    public String url;
    @Value("${spring.datasource.username}")
    public String username;
    @Value("${spring.datasource.password}")
    public String password;
    @Value("${spring.datasource.type}")
    public String type;
    @Value("${spring.datasource.hikari.auto-commit}")
    public Boolean autoCommit;
    @Value("${spring.datasource.hikari.connection-test-query}")
    public String connectionTestQuery;
    @Value("${spring.datasource.hikari.connection-timeout}")
    public Long connectionTimeOut;
    @Value("${spring.datasource.hikari.idle-timeout}")
    public Long IdleTimeout;
    @Value("${spring.datasource.hikari.pool-name}")
    public String PoolName;
    @Value("${spring.datasource.hikari.max-lifetime}")
    public Long MaxLifeTime;

}

4,定义分片规则

package com.digigd.marketadmin.common.ShardingAlgorithm;//package com.digigd.marketadmin.common.ShardingAlgorithm;

import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;
import com.google.common.collect.Range;
import io.shardingjdbc.core.api.algorithm.sharding.RangeShardingValue;
import lombok.RequiredArgsConstructor;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author 邓
 * @Title: ${file_name}
 * @Package ${package_name}
 * @Description: 分片算法
 * @date 2020/7/814:55
 * @Copyright 广东省xx有限公司
 */
@RequiredArgsConstructor
public class SingleKeyDynamicModuloTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Date> {
    private final String tablePrefix;

    @Override
    public String doEqualSharding(final Collection<String> availableTargetNames, final ShardingValue<Date> shardingValue) {
        SimpleDateFormat formatter = new SimpleDateFormat("yyyyMM");
        return tablePrefix +formatter.format(shardingValue.getValue());
    }

    @Override
    public Collection<String> doInSharding(final Collection<String> availableTargetNames, final ShardingValue<Date> shardingValue) {
        Collection<String> result = new LinkedHashSet<>(shardingValue.getValues().size());
        SimpleDateFormat formatter = new SimpleDateFormat("yyyyMM");
        for (Date value : shardingValue.getValues()) {
            result.add(tablePrefix + formatter.format(value));
        }
        return result;
    }

    @Override
    public Collection<String> doBetweenSharding(final Collection<String> availableTargetNames, final ShardingValue<Date> shardingValue) {
        Collection<String> result = new LinkedHashSet<>();
        DateFormat sdf = new SimpleDateFormat("yyyyMM");
        Range<Date> ranges = shardingValue.getValueRange();
        Date startTime = ranges.lowerEndpoint();
        Date endTime = ranges.upperEndpoint();
        // range.lowerEndpoint() = 2018-08-01
        // range.upperEndpoint() = 2018-10-01
        // 此处应该返回  tablePrefix+201808 , tablePrefix+201809,tablePrefix+201810,
        Calendar cal = Calendar.getInstance();

        while (startTime.getTime()<=endTime.getTime()){
            result.add(tablePrefix + sdf.format(startTime));
            cal.setTime(startTime);//设置起时间
            cal.add(Calendar.MONTH,1);
            startTime = cal.getTime();
        }
        return result;
    }
}

5，yml的配置项

server:
  port: 5701
spring:
  application:
    name: digigd
  jackson:
    time-zone: Asia/Shanghai
  datasource:
    username: test
    password: test
    url: jdbc:mysql://xxxxxx/test?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&autoReconnect=true
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.zaxxer.hikari.HikariDataSource
    # hikari 连接池
    hikari:
      # 自动提交
      auto-commit: true
      connection-test-query: SELECT 1
      # 等待连接池分配连接的最大时长（毫秒），超过这个时长还没可用的连接则发生SQLException， 默认:30秒
      # 生产环境 connect-time 10 s
      connection-timeout: 9000
      # 一个连接idle状态的最大时长（毫秒），超时则被释放（retired），默认:10分钟
      idle-timeout: 600000
      # 一个连接的生命时长（毫秒），超时而且没被使用则被释放（retired），默认:30分钟 1800000ms，建议设置比数据库超时时长少60秒，参考MySQL wait_timeout 7200s 参数（# 一个连接的生命时长（毫秒），超时而且没被使用则被释放（retired），默认:30分钟 1800000ms，建议设置比数据库超时时长少60秒，参考MySQL wait_timeout参数（show variables like '%timeout%';） -->  ） -->
      max-lifetime: 1800000
      # 连接池中允许的最大连接数。缺省值：10；推荐的公式：((core_count * 2) + effective_spindle_count)
      maximum-pool-size: 15
      # 最小连接数
      minimum-idle: 10
      # 连接池名字
      pool-name: DemoHikariCP