花了两天时间学习了分库分表Sharding-JDBC,在bilibili看了教学视频,源码主要是博客https://blog.csdn.net/yangxiang_Younger/article/details/113122070#commentBox的,博客内存在bug,主要是注解“@Primary”的问题,@Primary注解在ds0上则使用的是原生的dataSource,此时使用JPA不可分库分表,排查了一段时间“@Primary”需注解在 shardingDataSource。修改后亲测可用。针对多数据源情况,需要在工程SpringBootApplication注解中排除自动配置类,如下
@SpringBootApplication(exclude={
DataSourceAutoConfiguration.class,
// HibernateJpaAutoConfiguration.class, //(如果使用Hibernate时,需要加)
DataSourceTransactionManagerAutoConfiguration.class,
})
public class DemoApplication {
public static void main(String[] args) {
SpringApplication.run(DemoApplication.class, args);
}
}
此Demo使用pom.xml配置如下
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.6.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.funtest</groupId>
<artifactId>demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.21</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</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-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/sharding-jdbc-core -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>4.0.1</version>
</dependency>
</dependencies>
代码正确的配置如下:
package com.funtest.shard.database.policy;
import com.alibaba.druid.pool.DruidDataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Primary;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
/**
* @Author: flathead
* @Date: 2021/4/30 17:20
*/
@Component
@Slf4j
public class CustomerDataSourceFactory {
@Value("${shard.jdbc.ds0.url}")
private String dsOUrl;
@Value("${shard.jdbc.ds1.url}")
private String ds1Url;
@Value("${mysql.username}")
private String mysqlUserName;
@Value("${mysql.password}")
private String mysqlPassword;
@Value("${mysql.driver.class}")
private String driverClassName;
@Value("${spring.jpa.hibernate.ddl-auto}")
private String ddlDef;
/**
* 设置数据源ds0
*/
@Bean(name = "ds0", initMethod = "init", destroyMethod = "close")
public DruidDataSource getDs0Source() throws SQLException {
DruidDataSource dataSource0 = new DruidDataSource();
dataSource0.setDriverClassName(driverClassName);
dataSource0.setUrl(dsOUrl);
dataSource0.setUsername(mysqlUserName);
dataSource0.setPassword(mysqlPassword);
dataSource0.setInitialSize(5);
dataSource0.setMaxActive(20);
dataSource0.setMinIdle(10);
dataSource0.setMaxWait(30000);
dataSource0.setFilters("stat,wall");
Properties properties = new Properties();
properties.setProperty("connectTimeout", "2000");
properties.setProperty("socketTimeout", "600000");
properties.setProperty("spring.jpa.hibernate.ddl-auto", ddlDef);
dataSource0.setConnectProperties(properties);
return dataSource0;
}
/**
* 设置数据源ds1
*/
@Bean(name = "ds1", initMethod = "init", destroyMethod = "close")
public DruidDataSource getDs1Source() throws SQLException {
DruidDataSource dataSource1 = new DruidDataSource();
dataSource1.setDriverClassName(driverClassName);
dataSource1.setUrl(ds1Url);
dataSource1.setUsername(mysqlUserName);
dataSource1.setPassword(mysqlPassword);
dataSource1.setInitialSize(5);
dataSource1.setMaxActive(20);
dataSource1.setMinIdle(10);
dataSource1.setMaxWait(30000);
dataSource1.setFilters("stat,wall");
Properties properties = new Properties();
properties.setProperty("connectTimeout", "2000");
properties.setProperty("socketTimeout", "600000");
properties.setProperty("spring.jpa.hibernate.ddl-auto", ddlDef);
dataSource1.setConnectProperties(properties);
return dataSource1;
}
/**
* 分片逻辑名称前缀 shard 分表
*/
@Primary
@Bean(name = "shardingDataSource")
public DataSource shardingDataSource(@Qualifier("ds0") DruidDataSource ds0, @Qualifier("ds1") DruidDataSource ds1) {
// 获取数据源对象
Properties properties = new Properties();
properties.setProperty("max.connections.size.per.query", "10");
// if (devMode.equals("dao-dev")) {
// // dev 环境开启此参数
// properties.setProperty("sql.show", "true");
// }
DataSource dataSource = null;
try {
Map<String, DataSource> targetDataSource = new HashMap<>();
targetDataSource.put("ds0", ds0);
targetDataSource.put("ds1", ds1);
//设置分表规则,订单后两位,路由至0-1,2张表
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
//必须设置默认数据源
shardingRuleConfig.setDefaultDataSourceName("ds0");
shardingRuleConfig.getTableRuleConfigs().add(orderRuleConfig());
//shardingRuleConfig.getTableRuleConfigs().add(orderItemRuleConfig());
//设置分库规则,订单/2,偶數ds0,奇數ds1
shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("order_id", ShardingDBAlgorithm.DATABASE_SHARDING_ALGORITHM));
//绑定表
//shardingRuleConfig.getBindingTableGroups().add("t_order, t_order_item");
shardingRuleConfig.getBindingTableGroups().add("t_order");
//创建sharding数据源
dataSource = ShardingDataSourceFactory.createDataSource(targetDataSource, shardingRuleConfig, properties);
} catch (SQLException e) {
e.printStackTrace();
log.error("MYSQL shardconfig 数据源初始化失败");
}
log.info("MYSQL shardconfig 数据源初始化成功");
return dataSource;
}
/**
* 设置订单表分表规则
*/
private TableRuleConfiguration orderRuleConfig() {
TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration("t_order", "ds${0..1}.t_order_${0..1}");
StandardShardingStrategyConfiguration tableRule = new StandardShardingStrategyConfiguration("order_id", new OrderCheckShardingTableAlgorithm());
tableRuleConfig.setTableShardingStrategyConfig(tableRule);
return tableRuleConfig;
}
/**
* 设置订单商品表分表规则(该规则未使用)
*/
private TableRuleConfiguration orderItemRuleConfig() {
TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration("t_order_item", "ds0.t_order_item_${0..1}");
StandardShardingStrategyConfiguration tableRule = new StandardShardingStrategyConfiguration("order_id", new OrderCheckShardingTableAlgorithm());
tableRuleConfig.setTableShardingStrategyConfig(tableRule);
return tableRuleConfig;
}
}
分库规则
package com.funtest.shard.database.policy;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.util.Collection;
/**
* @Author: flathead
* @Date: 2021/5/3 11:01
*/
@Slf4j
public class ShardingDBAlgorithm {
public static final DatabaseShardingAlgorithm DATABASE_SHARDING_ALGORITHM = new DatabaseShardingAlgorithm();
/**
* 分库规则
*/
static class DatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
@Override
public String doSharding(Collection<String> databaseNames, PreciseShardingValue<Long> shardingValue) {
Long orderId = shardingValue.getValue();
long index = orderId % 2;
String dataSource = "ds"+index;
// dataSource="ds0";
log.info("数据库:{}",dataSource);
return dataSource;
}
}
}
分表规则
package com.funtest.shard.database.policy;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.util.Collection;
/**
* @Author: flathead
* @Date: 2021/5/3 10:52
*/
@Slf4j
public class OrderCheckShardingTableAlgorithm implements PreciseShardingAlgorithm<Long> {
@Override
public String doSharding(Collection<String> tableNames, PreciseShardingValue<Long> shardingValue) {
StringBuilder buf = new StringBuilder();
Long orderId = shardingValue.getValue();
String orderIdStr = orderId.toString();
// String orderIdSuffix = orderIdStr.substring(orderIdStr.length() - 2);
// String logicTableName = shardingValue.getLogicTableName();
// buf.append(logicTableName).append("_").append(orderIdSuffix);
// if(tableNames.contains(buf.toString())) {
// return buf.toString();
// } else {
// String errMsg = String.format("取模精确分片策略:没找到与分片键匹配的表名! %s : %s = %s", shardingValue.getLogicTableName(), shardingValue.getColumnName(), shardingValue.getValue());
// log.info(errMsg);
// throw new UnsupportedOperationException(errMsg);
// }
// if(tableNames.contains(orderId%2)) {
// return buf.toString();
// } else {
// String errMsg = String.format("取模精确分片策略:没找到与分片键匹配的表名! %s : %s = %s", shardingValue.getLogicTableName(), shardingValue.getColumnName(), shardingValue.getValue());
// log.info(errMsg);
// throw new UnsupportedOperationException(errMsg);
// }
for (String tableName:tableNames){
if (tableName.endsWith(String.valueOf(orderId%2))){
return tableName;
}
}
throw new UnsupportedOperationException("xxxx");
}
}
代码放在了 https://gitee.com/flathead/sharding-jdbc-demo.git (git clone 可直接下载)
数据库需要手动创建(看下源码不难的)
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIwczX0xiRGZkRGZ0Xy9GbvNGL2EzXlpXazxCbO1mYsxmMZpHeXlFMsJjWwJlMMBjVtJWd0ckW65UbM5WOHJWa5kHT20ESjBjUIF2X0hXZ0xCMx81dvRWYoNHLrdEZwZ1Rh5WNXp1bwNjW1ZUba9VZwlHdssmch1mclRXY39CXldWYtlWPzNXZj9mcw1ycz9WL49zZuBnLzgDN1ITN0YTMzATNwEjMwIzLc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)