说明
网上搜索shardingsphere配置教程,绝大部分都是使用springboot来配置,但是在springmvc架构中配置很少,唯一看到的一个篇章需要收费,便通过官方文档以及查询相关资料,终于完成了该篇文章。
注意:在该文章中,使用了多数据源配置,因为不是所有表都需要分表,由于shardingsphere对部分sql语法不支持,为了不影响不分表的sql能正常执行,减少代码修改,在执行sql时切换数据源
1.pom配置
<!-- shardingsphere核心依赖 -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>4.1.1</version>
</dependency>
<!-- shardingsphere命名,用户springxml文件配置 -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>4.1.1</version>
</dependency>
<!-- mybatisPlus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring</artifactId>
<version>4.2.0</version>
</dependency>
2.sharding.xml配置
该配置文件包含分库分表数据源配置,分库分表策略规则
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:sharding="http://shardingsphere.apache.org/schema/shardingsphere/sharding"
xmlns:p="http://www.springframework.org/schema/p"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://shardingsphere.apache.org/schema/shardingsphere/sharding
http://shardingsphere.apache.org/schema/shardingsphere/sharding/sharding.xsd
">
<context:annotation-config />
-->
<!-- 配置官方文档 -->
<!-- https://shardingsphere.apache.org/document/legacy/3.x/document/cn/manual/sharding-jdbc/configuration/config-spring-namespace/-->
<!-- 数据源,id唯一,用于下面的数据源配置 -->
<bean id="ds0" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="driverClassName" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
<!-- 如果需要分库,则配置多个库数据源,id唯一 -->
<!-- <bean id="ds0" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="driverClassName" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean> -->
<!-- 数据库配置文件 -->
<context:property-placeholder location="classpath:properties/config.properties" ignore-unresolvable="true"/>
<!-- 分表精准查询策略,这里官方提供了多种策略,根据具体业务需要 -->
<bean id="preciseModuloTableShardingAlgorithm" class="com.framework.config.PreciseTableShardingAlogorithm" />
<!-- 项目表分表规则
sharding-column 具体分表字段,
precise-algorithm-ref 分表规则,我这里是根据分表字段值取模,来判定分到哪张表
-->
<sharding:standard-strategy id="infoTableShardingStrategy" sharding-column="bid_code" precise-algorithm-ref="preciseModuloTableShardingAlgorithm" />
<!-- 数据源配置 -->
<sharding:data-source id="shardingDataSource">
<sharding:props>
<!-- 打印sql -->
<prop key="sql.show">true</prop>
</sharding:props>
<!-- 默认数据源 -->
<sharding:sharding-rule data-source-names="ds0">
<!-- 分表规则 -->
<sharding:table-rules>
<sharding:table-rule logic-table="tb_e_witness_infor" actual-data-nodes="ds0.tb_e_witness_infor_$->{1..${shardingNum}}" table-strategy-ref="infoTableShardingStrategy"/>
</sharding:table-rules>
<!-- 广播表,当存在多个库时,只需修改其中一个库,其他库会自动同步 -->
<sharding:broadcast-table-rules>
<sharding:broadcast-table-rule table="tb_e_witness_parameter" />
</sharding:broadcast-table-rules>
</sharding:sharding-rule>
</sharding:data-source>
</beans>
3.多数据源配置
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
xmlns:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd">
<!-- Service包(自动注入) -->
<context:component-scan base-package="com.*.service"/>
<!--属性占位文件引入,可以通过${属性名}获得属性文件中的内容-->
<context:property-placeholder location="classpath:dbConnection/db.properties" ignore-unresolvable="true"/>
<!-- 配置数据源 ,用于不分表查询 -->
<bean id="ds1" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
<property name="driverClassName" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
<!-- 多数据源配置 -->
<bean id="dataSource" class="com.framework.DataSource.DynamicDataSource" >
<property name="targetDataSources">
<map key-type="java.lang.String">
<!-- 动态数据源使用baomidou mybatisPlus管理, 支持shardingsphere和Druid连接池 -->
<entry value-ref="shardingDataSource" key="ds0"></entry>
<entry value-ref="ds1" key="ds1"></entry>
</map>
</property>
<property name="defaultTargetDataSource" ref="ds1"></property>
</bean>
<!-- 配置事务管理 -->
<bean name="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 其他配置 -->
......
</beans>
4.分表策略
分表字段库里是字符串,所以这里取其hash值,然后在取模,SHARDING_NUM看你需要分几张表,这个值与sharding.xml配置里面的${shardingNum}一致
package com.framework.config;
/**
* 精准查询分表策略
* @class: PreciseTableShardingAlogorithm
* @author:
* @date: 2024/2/24 21:34
* @description: 精准查询分表策略
* @version 1.0
*/
public class PreciseTableShardingAlogorithm implements PreciseShardingAlgorithm<String> {
/**
*<p>分表策略</p>
* @param collection 所有分片库的集合
* @param preciseShardingValue 分片属性
* @return: java.lang.String
* @author
* @date 2024/2/24 21:36
* version 1.0
*/
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
// 逻辑表名
String logicTableName = preciseShardingValue.getLogicTableName();
// 字段名
String columnName = preciseShardingValue.getColumnName();
// 字段值
String value = preciseShardingValue.getValue();
// 获取字符串hash值
int hashCode = value.replaceAll("-", "").hashCode();
// 取模
int i = Math.abs(hashCode % CommonConsts.SHARDING_NUM);
// 获取表名
String key = logicTableName + "_" + i;
// 判断分片库是否存在
if (collection.contains(key)) {
// 返回表名
return key;
}
// 返回空
return null;
}
}
5.数据源切面配置
package com.framework.DataSource;
/**
* @Title: Springboot快速开发框架</br>
* @Description: 切面 </br>
* @Copyright: Copyright (c) 2022</br>
* @Company: </br>
* @Author: </br>
* @Version: 1.0</br>
**/
@Aspect
@Order(Ordered.LOWEST_PRECEDENCE-1)
public class DataSourceAspect {
@Pointcut("@annotation(ds)")
public void useDataSource(DS ds){
}
@Around(value = "useDataSource(ds)")
public Object dataSourceSwitcher(ProceedingJoinPoint joinPoint, DS ds) throws Throwable {
// 设置数据源
DataSourceContextHolder.setDbType(ds.source().getSource());
try {
Object result = joinPoint.proceed();
return result;
}catch (Exception e){
throw e;
}finally {
// 清除数据源
DataSourceContextHolder.clearDbType();
}
}
}
6.数据源切换
package com.framework.DataSource;
import lombok.extern.slf4j.Slf4j;
/**
* @Title: Springboot快速开发框架</br>
* @Description: 动态数据源切换 </br>
* @Copyright: Copyright (c) 2022</br>
* @Company: </br>
* @Author: </br>
* @Version: 1.0</br>
**/
@Slf4j
public class DataSourceContextHolder {
// 线程
private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
/**
* 设置数据源
* @param dbType
*/
public static void setDbType(String dbType) {
log.info("切换到{}数据源", dbType);
contextHolder.set(dbType);
}
/**
* 获取数据源
* @return
*/
public static String getDbType() {
return ((String) contextHolder.get());
}
/**
* 清除所有数据源
*/
public static void clearDbType() {
log.info("移除所有数据源");
contextHolder.remove();
}
}
7.数据源类型
package com.framework.DataSource;
/**
* @Title: Springboot快速开发框架</br>
* @Description: 数据源类型 </br>
* @Copyright: Copyright (c) 2022</br>
* @Company: </br>
* @Author: </br>
* @Version: 1.0</br>
**/
public enum DataType {
// 名称要与配置的id一致
SHARDING_DATA_SOURCE("ds0", "sharding-jdbc数据源"),
DRUID_DATA_SOURCE("ds1", "druid数据源");
private String source;
private String desc;
DataType(String source, String desc) {
this.source = source;
this.desc = desc;
}
public String getSource() {
return source;
}
public void setSource(String source) {
this.source = source;
}
public String getDesc() {
return desc;
}
public void setDesc(String desc) {
this.desc = desc;
}
}
8.数据源切换注解
package com.framework.DataSource;
import java.lang.annotation.*;
/**
* @Title: Springboot快速开发框架
* @Description: 多数据源注解
* @Copyright: Copyright (c) 2022
* @Company:
* @Author:
* @Version: 1.0
**/
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DS {
/**
* 数据源
* @return
*/
DataType source();
}
9.动态数据源配置
package com.framework.DataSource;
/**
* @Title: Springboot快速开发框架
* @Description: 动态数据源配置
* @Copyright: Copyright (c) 2022
* @Company:
* @Author:
* @Version: 1.0<br>
**/
public class DynamicDataSource extends AbstractRoutingDataSource {
/**
* 获取数据源
* @author
* @date 2024/3/4
* @return {@link Object}
*/
@Override
protected Object determineCurrentLookupKey() {
// 获取数据源类型
String dbType = DataSourceContextHolder.getDbType();
// 判断是否存在类型
if (StrUtil.isBlank(dbType)) {
// 返回默认数据源
return DataType.DRUID_DATA_SOURCE.getSource();
}
return dbType;
}
}
10.使用示例
/**
* 查询getPageList列表
* @param model 查询实体
* @return IPage 返回分页集合
* @author
*/
@DS(source = DataType.SHARDING_DATA_SOURCE)
public List getPageList){
return baseMapper.getPageList();
}