【简单实用】
share.jpg
概述
最近公司要某业务要实现分表分库，根据目前公司的技术栈进行了选型，选择shardingsphere，接下来记录下springboot、mbatisPlus、dynamic-datasource-spring-boot-starter、druid集成shardingsphere的过程，希望对需要做这块业务的开发人员有所帮助。
依赖的框架以及版本
| 框架 | 版本 | |
|---|---|---|
| spring-boot | 2.1.6.RELEASE | |
| mybatis-plus-boot-starter | 3.1.1 | |
| druid | 1.1.21 | |
| dynamic-datasource-spring-boot-starter | 3.4.0 |
实现步骤
1，先添加shardingsphere依赖，shardingsphere的4.x版本较之前变化蛮大的。
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<!-- for spring namespace -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>4.1.1</version>
</dependency>
2，添加依赖后，接下来新建DataSourceConfiguration这个配置类去将这个shardingsphere的数据源交给dynamic-datasource去维护。
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.provider.AbstractDataSourceProvider;
import com.baomidou.dynamic.datasource.provider.DynamicDataSourceProvider;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import lombok.extern.slf4j.Slf4j;
import org.apache.tomcat.util.buf.StringUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.EnvironmentAware;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
@Configuration
@DependsOn("shardingDataSource")
@Slf4j
public class DataSourceConfiguration implements EnvironmentAware {
private final DynamicDataSourceProperties properties;
public DataSourceConfiguration(DynamicDataSourceProperties properties) {
this.properties = properties;
}
@Resource(name = "shardingDataSource")
private DataSource shardingDataSource;
@Value("${spring.shardingsphere.datasource.names:}")
private String shardingDataSourceName;
@Bean
public DynamicDataSourceProvider dynamicDataSourceProvider() {
return new AbstractDataSourceProvider() {
@Override
public Map<String, DataSource> loadDataSources() {
Map<String, DataSource> dataSourceMap = new HashMap<>();
if (org.springframework.util.StringUtils.isEmpty(shardingDataSourceName)){
return dataSourceMap;
}
dataSourceMap.put(shardingDataSourceName, shardingDataSource);
return dataSourceMap;
}
};
}
@Primary
@Bean
public DataSource dataSource() {
DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
dataSource.setPrimary(properties.getPrimary());
dataSource.setStrict(properties.getStrict());
dataSource.setStrategy(properties.getStrategy());
dataSource.setP6spy(properties.getP6spy());
dataSource.setSeata(properties.getSeata());
return dataSource;
}
@Override
public void setEnvironment(Environment environment) {
log.error(StringUtils.join(environment.getActiveProfiles()));
}
}
3，再接下来，在配置文件中添加如下配置，其中可以看到我们使用primary为主数据源，默认的数据库操作都会找这个数据源。其他的分库分表操作走sharding0等等数据源即可。之所以这样配置主要是因为老项目介入发表分库业务，不希望影响太大。
#默认数据源
spring.datasource.dynamic.primary=primary
#mysql1数据源配置
spring.datasource.dynamic.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.dynamic.datasource.primary.url=jdbc:mysql://xxxxx:3306/sample?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.dynamic.datasource.primary.username=xxxxx
spring.datasource.dynamic.datasource.primary.password=xxxxx
# 数据源 sharding0
spring.shardingsphere.datasource.names=sharding0
# 分库分表数据库
spring.shardingsphere.datasource.sharding0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.sharding0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.sharding0.url=jdbc:mysql://xxxxx:3306/sample?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.shardingsphere.datasource.sharding0.username=xxxxx
spring.shardingsphere.datasource.sharding0.password=xxxxx
spring.shardingsphere.sharding.tables.book.actual-data-nodes=sharding0.book,sharding0.book_history
spring.shardingsphere.sharding.tables.book.table-strategy.standard.sharding-column=count1
spring.shardingsphere.sharding.tables.book.table-strategy.standard.precise-algorithm-class-name=com.diligence.shardingsphere.algoritm.MyPreciseShardingAlgorithm
spring.shardingsphere.sharding.binding-tables=book
# 打印执行的数据库以及语句
spring.shardingsphere.props..sql.show=true
spring.main.allow-bean-definition-overriding=true
mybatis-plus.mapper-locations=classpath*:mapper/**/*Mapper.xml
4，增加MyPreciseShardingAlgorithm分片策略实现分表逻辑。
public class MyPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
Optional<Integer> shardingValueOpl = Optional.ofNullable(shardingValue.getValue());
if (shardingValueOpl.get()==1) return "book" ;
return "book_history";
}
}
5，增加ShardDS方便数据源统一维护。
@Target({ElementType.TYPE, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
@DS(value = "sharding0")
public @interface ShardDS {
}
6，至此，集成完毕。使用的时候只需要在相应的需要分表分库的数据库操作的地方使用ShardDS注解做数据源切换。例如：
@ShardDS
@Override
public List<SampleResponse> getBookList() {
return sampleMapper.selectSampleList("88888");
}
异常报错
集成过程中遇到了如下异常：
-
java.lang.IllegalArgumentException: Property 'sqlSessionFactory' or 'sqlSessionTemplate' are required
解决方法：添加上面的DataSourceConfiguration类，注入数据源 -
有时候会遇到，shardingDataSource为空
解决办法：在DataSourceConfiguration类上添加@DependsOn("shardingDataSource")
注意事项
简单测试了shardingsphere，使用上需要注意的地方：
- 通过子查询实现业务查询当前并不能支持；
- 不要使用update去修改分片键，这里可以用delete后insert代替。
网友评论