V2AS问路
Sharding-JDBC 按日期时间分库分表
阅读原文时间：2022年04月04日阅读：1

Sharding-JDBC

定位为轻量级Java框架，在Java的JDBC层提供的额外服务。 它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。

  • 适用于任何基于Java的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
  • 基于任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。
  • 支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer和PostgreSQL。

集成sharding-jdbc

首先创建创建数据库和表这是sharding-jdbc所要求的。

create database db_201906;
create database db_201907;

use db_201906;
create table t_order_20190614(id int not null auto_increment,order_no varchar(16) NOT NULL,sys_time datetime,PRIMARY KEY (id));
create table t_order_20190615(id int not null auto_increment,order_no varchar(16) NOT NULL,sys_time datetime,PRIMARY KEY (id));
insert into t_order_20190614 values(0,'','2019-06-14 0:0:0');
insert into t_order_20190615 values(0,'','2019-06-15 0:0:0');

use db_201907;
create table t_order_20190714(id int not null auto_increment,order_no varchar(16) NOT NULL,sys_time datetime,PRIMARY KEY (id));
create table t_order_20190715(id int not null auto_increment,order_no varchar(16) NOT NULL,sys_time datetime,PRIMARY KEY (id));
insert into t_order_20190714 values(0,'','2019-07-14 0:0:0');
insert into t_order_20190715 values(0,'','2019-07-15 0:0:0');

接着是pom.xml文件，添加sharding-jdbc到工程中，主要是下面两个依赖：

http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
4.0.0

org.osource.aurora
shardingjdbc
0.0.1-SNAPSHOT
jar

shardingjdbc
http://maven.apache.org

org.springframework.boot spring-boot-starter-parent 2.0.5.RELEASE

UTF-8 UTF-8 1.8 

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

<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>1.3.2</version>  
</dependency>

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

<dependency>  
    <groupId>org.springframework.boot</groupId>  
    <artifactId>spring-boot-starter-test</artifactId>  
    <scope>test</scope>  
</dependency>

<!--sharding-jdbc -->  
<dependency>  
    <groupId>io.shardingjdbc</groupId>  
    <artifactId>sharding-jdbc-core</artifactId>  
    <version>2.0.3</version>  
</dependency>

<dependency>  
    <groupId>commons-dbcp</groupId>  
    <artifactId>commons-dbcp</artifactId>  
    <version>1.3</version>  
</dependency>



      <plugin>  
          <groupId>org.apache.maven.plugins</groupId>  
          <artifactId>maven-scm-plugin</artifactId>  
          <version>1.9.4</version>  
          <configuration>  
              <connectionType>developerConnection</connectionType>  
          </configuration>  
      </plugin>

      <plugin>  
          <groupId>org.apache.maven.plugins</groupId>  
          <artifactId>maven-release-plugin</artifactId>  
          <version>2.5.3</version>  
          <configuration>  
              <releaseProfiles>release</releaseProfiles>  
              <autoVersionSubmodules>true</autoVersionSubmodules>  
              <tagBase>https://github.com/sharding/shardingjdbc-framework.git</tagBase>  
              <tagNameFormat>v@{project.version}</tagNameFormat>  
          </configuration>  
      </plugin>

      <plugin>  
          <groupId>org.apache.maven.plugins</groupId>  
          <artifactId>maven-javadoc-plugin</artifactId>  
          <configuration>  
              <skip>true</skip>  
              <aggregate>true</aggregate>  
              <charset>UTF-8</charset>  
              <encoding>UTF-8</encoding>  
              <docencoding>UTF-8</docencoding>  
          </configuration>  
      </plugin>

      <plugin>  
          <groupId>org.apache.maven.plugins</groupId>  
          <artifactId>maven-jar-plugin</artifactId>  
          <configuration>  
              <excludes>  
                  <exclude>\*\*/\*.xml</exclude>  
              </excludes>  
          </configuration>  
      </plugin>

      <plugin>  
          <groupId>org.apache.maven.plugins</groupId>  
          <artifactId>maven-compiler-plugin</artifactId>  
          <configuration>  
              <source>1.8</source>  
              <target>1.8</target>  
              <encoding>UTF-8</encoding>  
          </configuration>  
      </plugin>

      <plugin>  
          <groupId>org.apache.maven.plugins</groupId>  
          <artifactId>maven-surefire-plugin</artifactId>  
          <configuration>  
              <!-- 设置成true在使用maven打包发布时不做junit测试 -->  
              <skip>true</skip>  
          </configuration>  
      </plugin>

  </plugins>

首先是数据源配置和库策略、表策略：

import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import io.shardingjdbc.core.api.config.ShardingRuleConfiguration;
import io.shardingjdbc.core.api.config.TableRuleConfiguration;
import io.shardingjdbc.core.api.config.strategy.StandardShardingStrategyConfiguration;
import io.shardingjdbc.core.jdbc.core.datasource.ShardingDataSource;

@Configuration
public class ShardingDataSourceConfiguration {

@Value("${spring.datasource.username:root}")  
private String username;

@Value("${spring.datasource.password:123456}")  
private String password;

@Value("${spring.datasource.url}")  
private String jdbcUrl;

@Value("${spring.datasource.driver-class-name}")  
private String driverClassName;

@Value("${shardingjdbc.sql.show:true}")  
private String sqlShow;

@Value("${mybatis.mapper-locations:mappper/\*\*/\*.xml}")  
private String mapperLocations;

// 配置sharding-jdbc的DataSource，给上层应用使用，这个DataSource包含所有的逻辑库和逻辑表，应用增删改查时，修改对应sql  
// 然后选择合适的数据库继续操作。因此这个DataSource创建很重要。  
@Bean  
@Primary  
@ConfigurationProperties(prefix = "spring.datasource")  
public DataSource shardingDataSource() throws SQLException {

    ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();

    // 订单表配置，可以累计添加多个配置  
    shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());  
    // shardingRuleConfig.getTableRuleConfigs().add(getUserTableRuleConfiguration());

    // 打印SQL  
    Properties props = new Properties();  
    props.put("sql.show", sqlShow);

    return new ShardingDataSource(shardingRuleConfig.build(createDataSourceMap()),  
            new ConcurrentHashMap<String, Object>(), props);

}

// 创建用户表规则  
@Bean  
TableRuleConfiguration getOrderTableRuleConfiguration() {  
    TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();

    orderTableRuleConfig.setLogicTable("t\_order");  
    orderTableRuleConfig.setLogicIndex("sys\_time");

    // 设置数据库策略，传入的是sys\_time  
    orderTableRuleConfig.setDatabaseShardingStrategyConfig(  
            new StandardShardingStrategyConfiguration("sys\_time", DatabaseShardingAlgorithm.class.getName()));  
    // 设置数据表策略，传入的是sys\_time  
    orderTableRuleConfig.setTableShardingStrategyConfig(  
            new StandardShardingStrategyConfiguration("sys\_time", TableShardingAlgorithm.class.getName()));

    // 设置数据节点，格式为dbxx.tablexx。这里的名称要和map的别名一致。下面两种方式都可以  
    // orderTableRuleConfig.setActualDataNodes("db\_${0..1}.t\_order\_${0..1}");  
    orderTableRuleConfig.setActualDataNodes(  
            "db\_201906.t\_order\_20190614,db\_201906.t\_order\_20190615,db\_201907.t\_order\_20190714,db\_201907.t\_order\_20190715");  
    // 设置纵列名称  
    // orderTableRuleConfig.setKeyGeneratorColumnName("ID");  
    return orderTableRuleConfig;  
}

// 下面函数是获取数据源，即包含有多少个数据库，读入到系统中存放于map中  
private Map<String, DataSource> createDataSourceMap() {  
    Map<String, DataSource> result = new HashMap<>();  
    result.put("db\_201906",  
            createDataSource("jdbc:mysql://localhost:3306/db\_201906?characterEncoding=utf8&useSSL=false"));  
    result.put("db\_201907",  
            createDataSource("jdbc:mysql://localhost:3306/db\_201907?characterEncoding=utf8&useSSL=false"));  
    return result;  
}

private DataSource createDataSource(final String jdbcUrl) {  
    // 使用默认连接池  
    BasicDataSource result = new BasicDataSource();  
    // 指定driver的类名，默认从jdbc url中自动探测  
    result.setDriverClassName(com.mysql.jdbc.Driver.class.getName());  
    // 设置数据库路径  
    result.setUrl(jdbcUrl);  
    // 设置数据库用户名  
    result.setUsername(username);  
    // 设置数据密码  
    result.setPassword(password);  
    return result;  
}

@Bean("sqlSessionFactory")  
@Primary  
public SqlSessionFactory sqlSessionFactory(DataSource shardingDataSource) throws Exception {  
    SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();  
    sessionFactory.setDataSource(shardingDataSource);  
    PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();  
    sessionFactory.setMapperLocations(resolver.getResources(mapperLocations));  
    return sessionFactory.getObject();  
}

/\*\*  
 \* - 需要手动配置事务管理器  
 \*/  
@Bean  
public DataSourceTransactionManager transactitonManager(DataSource shardingDataSource) {  
    return new DataSourceTransactionManager(shardingDataSource);  
}

@Bean  
public SqlSessionTemplate sqlSessionTmplate(SqlSessionFactory sqlSessionFactory) {  
    SqlSessionTemplate sqlSessionTmplate = new SqlSessionTemplate(sqlSessionFactory);  
    return sqlSessionTmplate;  
}

}

数据库分库策略 DatabaseShardingAlgorithm

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;

import io.shardingjdbc.core.api.algorithm.sharding.PreciseShardingValue;
import io.shardingjdbc.core.api.algorithm.sharding.standard.PreciseShardingAlgorithm;

public class DatabaseShardingAlgorithm implements PreciseShardingAlgorithm {

@Override  
public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {

    String db\_name = "db\_";  
    try {  
        Date date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(preciseShardingValue.getValue());  
        String year = String.format("%tY", date);  
        String mon = String.format("%tm", date);  
        db\_name = db\_name + year + mon;  
        System.out.println("db\_name:" + db\_name);  
    } catch (ParseException e) {  
        e.printStackTrace();  
    }

    for (String each : collection) {  
        System.out.println("db:" + each);  
        if (each.equals(db\_name)) {  
            return each;  
        }  
    }

    throw new IllegalArgumentException();  
}

}

数据表分表策略 TableShardingAlgorithm

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;

import io.shardingjdbc.core.api.algorithm.sharding.PreciseShardingValue;
import io.shardingjdbc.core.api.algorithm.sharding.standard.PreciseShardingAlgorithm;

public class TableShardingAlgorithm implements PreciseShardingAlgorithm {

@Override  
public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {  
    String tb\_name = preciseShardingValue.getLogicTableName() + "\_";  
    try {  
        Date date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(preciseShardingValue.getValue());  
        String year = String.format("%tY", date);  
        String mon = String.format("%tm", date);  
        String dat = String.format("%td", date);  
        tb\_name = tb\_name + year + mon + dat;  
        System.out.println("tb\_name:" + tb\_name);  
    } catch (ParseException e) {  
        e.printStackTrace();  
    }

    for (String each : collection) {  
        System.out.println("t\_order\_:" + each);  
        if (each.equals(tb\_name)) {  
            return each;  
        }  
    }

    throw new IllegalArgumentException();

}

}

测试并使用sharding-jdbc

上面基本已经完成了sharding-jdbc的集成，下面将进行测试

创建实体类 Order

import java.io.Serializable;

public class Order implements Serializable {

/\*\*  
 \*  
 \*/  
private static final long serialVersionUID = -8759492936340749287L;

private String orderNo;

private String sysTime;

public String getOrderNo() {  
    return orderNo;  
}

public void setOrderNo(String orderNo) {  
    this.orderNo = orderNo;  
}

public String getSysTime() {  
    return sysTime;  
}

public void setSysTime(String sysTime) {  
    this.sysTime = sysTime;  
}

@Override  
public String toString() {  
    return "Order \[orderNo=" + orderNo + ", sysTime=" + sysTime + "\]";  
}

}

创建服务接口类OrderService

import java.util.List;

public interface OrderService {

List<Order> getAll(String sysTime);

}

创建服务接口类实现 OrderServiceImpl

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service("orderService")
public class OrderServiceImpl implements OrderService {

@Autowired  
private OrderMapper orderMapper;

@Override  
public List<Order> getAll(String sysTime) {  
    return orderMapper.findAll(sysTime);  
}

}

创建 OrderMapper 数据访问层

import java.util.List;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface OrderMapper {

@Select("select \* from order where sys\_time = #{sysTime}")  
List<Order> findAll(@Param("sys\_time") String sysTime);

}

创建 Spring Boot 启动类 SpringBootApplicationTest

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;

@SpringBootApplication
//开启通用注解扫描
@ComponentScan(basePackages = { "org.shardingjdbc" })
@MapperScan(basePackages = { "org.shardingjdbc.mapper.user,org.shardingjdbc.mapper.order" })
@EnableAutoConfiguration
public class SpringBootApplicationTest {

public static void main(String\[\] args) {  
    SpringApplication.run(SpringBootApplicationTest.class, args);  
}

}

RestController 测试代码

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class OrderController {

@Autowired  
@Qualifier("orderService")  
private OrderService orderService;

@RequestMapping("/get")  
public String getOrder() {  
    List<Order> list = orderService.getAll("2019-06-14 0:0:0");  
    System.out.println(list);  
    return "OK";  
}

}

SpringBootTest 测试方法

import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

/**
* Unit test for simple App.
*/
@RunWith(SpringRunner.class)
@SpringBootTest(classes = SpringBootApplicationTest.class)
@EnableAutoConfiguration
public class AppTest {

@Autowired  
@Qualifier("orderService")  
private OrderService orderService;

@Test  
public void getOrder() {  
    List<Order> list = orderService.getAll("2019-06-14 0:0:0");  
    System.out.println(list);  
}

}

sharding-jdbc 分库分表实现方式到此基本完成

手机扫一扫

移动阅读更方便

阿里云服务器
腾讯云服务器
七牛云服务器

你可能感兴趣的文章

基于Spring MVC + Spring + MyBatis的【超市会员管理系统】
SpringCloud微服务治理技术入门(SCN)
RabbitMQ入门到进阶(Spring整合RabbitMQ&SpringBoot整合RabbitMQ)

4

Spring中毒太深，离开了Spring，我居然连最基本的接口都不会写了¯\_(ツ)_/¯

5

GraphX编程指南

6

SpringCloudAlibaba 微服务讲解（三）Nacos Discovery-服务治理

7

Spring Security实现统一登录与权限控制

8

SpringCloudAlibaba 微服务讲解（二）微服务环境搭建

9

SpringCloud-Consul开发环境配置

10

040_Eureka 服务注册与发现