开始
从零开始利用spring-data-jpa与sharding-jdbc进行动态月表,直接上手。
需求说明
数据量按照分片键(入库时间)进入对应的月表,查询时根据分片键的值查询指定表;但是每次查询都必须带上分片键,这就不是很友好,所以另外后面也有说明在没有指定分片键时如何查询最近的两个月。
前期准备
建表语句
-- 逻辑表,每个月表都根据逻辑表生成
CREATE TABLE `EXAMPLE` (
`ID` bigint(36) NOT NULL AUTO_INCREMENT,
`NAME` varchar(255) NOT NULL,
`CREATED` datetime(3) DEFAULT NULL,
`UPDATED` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 月表
CREATE TABLE `EXAMPLE_201909` (
`ID` bigint(36) NOT NULL AUTO_INCREMENT,
`NAME` varchar(255) NOT NULL,
`CREATED` datetime(3) DEFAULT NULL,
`UPDATED` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `EXAMPLE_201910` (
`ID` bigint(36) NOT NULL AUTO_INCREMENT,
`NAME` varchar(255) NOT NULL,
`CREATED` datetime(3) DEFAULT NULL,
`UPDATED` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
实体类
@Entity
@Data
@Table(name = "EXAMPLE")
public class Example implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID")
private String id;
@Column(name = "NAME")
private String name;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss.SSS", timezone = "GMT+8")
@Column(name = "CREATED")
private Date created;
@Column(name = "UPDATED", insertable = false, updatable = false)
private Date updated;
}
repo
import java.util.Date;
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import com.test.sharding.entity.Example;
public interface ExampleRepo extends JpaRepository<Example, Long>, JpaSpecificationExecutor<Example> {
List<Example> findByCreatedBetween(Date start, Date end);
}
Maven依赖
经过测试,支持springboot 2.0.X+与1.5.X+。
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>3.0.0</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>4.6.7</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.20</version>
</dependency>
分片算法实现
由于选择的分片策略是StandardShardingStrategy
(在后面的配置文件中会配置),所以需要试下下面两个分片算法:
- 精确分片算法
import java.util.Collection;
import java.util.Date;
import cn.hutool.core.date.DateUtil;
import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
public class MyPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Date> {
// 可以优化为全局变量
private static String yearAndMonth = "yyyyMM";
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> shardingValue) {
StringBuffer tableName = new StringBuffer();
tableName.append(shardingValue.getLogicTableName()).append("_")
.append(DateUtil.format(shardingValue.getValue(), yearAndMonth));
return tableName.toString