溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
這篇文章主要講解了“springboot怎么配置sharding-jdbc水平分表”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“springboot怎么配置sharding-jdbc水平分表”吧！
官方給出了Spring Boot Starter配置
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>${shardingsphere.version}</version>
</dependency>但是基于已有項(xiàng)目，添加shardingsphere自動(dòng)配置是很惡心的事
為什么配置了某個(gè)數(shù)據(jù)連接池的spring-boot-starter（比如druid）和 shardingsphere-jdbc-spring-boot-starter 時(shí)，系統(tǒng)啟動(dòng)會(huì)報(bào)錯(cuò)？
回答：
1. 因?yàn)閿?shù)據(jù)連接池的starter（比如druid）可能會(huì)先加載并且其創(chuàng)建一個(gè)默認(rèn)數(shù)據(jù)源，這將會(huì)使得 ShardingSphere‐JDBC 創(chuàng)建數(shù)據(jù)源時(shí)發(fā)生沖突。
2. 解決辦法為，去掉數(shù)據(jù)連接池的starter 即可，sharing‐jdbc 自己會(huì)創(chuàng)建數(shù)據(jù)連接池。
一般項(xiàng)目已經(jīng)有自己的DataSource了，如果使用shardingsphere-jdbc的自動(dòng)配置，就必須舍棄原有的DataSource。
為了不放棄原有的DataSource配置，我們只引入shardingsphere-jdbc-core依賴
<dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-core</artifactId> <version>4.1.1</version> </dependency>
如果只水平分表，只支持mysql,可以排除一些無(wú)用的依賴
<dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-core</artifactId> <version>4.1.1</version> <exclusions> <exclusion> <groupId>org.apache.shardingsphere</groupId> <artifactId>shardingsphere-sql-parser-postgresql</artifactId> </exclusion> <exclusion> <groupId>org.apache.shardingsphere</groupId> <artifactId>shardingsphere-sql-parser-oracle</artifactId> </exclusion> <exclusion> <groupId>org.apache.shardingsphere</groupId> <artifactId>shardingsphere-sql-parser-sqlserver</artifactId> </exclusion> <exclusion> <groupId>org.apache.shardingsphere</groupId> <artifactId>encrypt-core-rewrite</artifactId> </exclusion> <exclusion> <groupId>org.apache.shardingsphere</groupId> <artifactId>shadow-core-rewrite</artifactId> </exclusion> <exclusion> <groupId>org.apache.shardingsphere</groupId> <artifactId>encrypt-core-merge</artifactId> </exclusion> <exclusion> <!-- 數(shù)據(jù)庫(kù)連接池，一般原有項(xiàng)目已引入其他的連接池 --> <groupId>com.zaxxer</groupId> <artifactId>HikariCP</artifactId> </exclusion> <exclusion> <!-- 也是數(shù)據(jù)庫(kù)連接池，一般原有項(xiàng)目已引入其他的連接池 --> <groupId>org.apache.commons</groupId> <artifactId>commons-dbcp2</artifactId> </exclusion> <exclusion> <!-- 對(duì)象池，可以不排除 --> <groupId>commons-pool</groupId> <artifactId>commons-pool</artifactId> </exclusion> <exclusion> <groupId>com.h3database</groupId> <artifactId>h3</artifactId> </exclusion> <exclusion> <!-- mysql驅(qū)動(dòng)，原項(xiàng)目已引入，為了避免改變?cè)邪姹咎?hào)，排除了吧 --> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </exclusion> <exclusion> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> </exclusion> <exclusion> <groupId>com.microsoft.sqlserver</groupId> <artifactId>mssql-jdbc</artifactId> </exclusion> </exclusions> </dependency>
以Druid為例，原配置為
package com.xxx.common.autoConfiguration;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.logging.Slf4jLogFilter;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import lombok.extern.slf4j.Slf4j;
/**
* @ClassName: DruidConfiguration
* @Description: Druid連接池配置
*/
@Configuration
@Slf4j
public class DruidConfiguration {
@Value("${spring.datasource.driver-class-name}")
private String driver;
@Value("${spring.datasource.url}")
private String url;
@Value("${spring.datasource.username}")
private String username;
@Value("${spring.datasource.password}")
private String password;
@Value("${datasource.druid.initialsize}")
private Integer druid_initialsize = 0;
@Value("${datasource.druid.maxactive}")
private Integer druid_maxactive = 20;
@Value("${datasource.druid.minidle}")
private Integer druid_minidle = 0;
@Value("${datasource.druid.maxwait}")
private Integer druid_maxwait = 30000;
@Bean
public ServletRegistrationBean druidServlet() {
ServletRegistrationBean reg = new ServletRegistrationBean();
reg.setServlet(new StatViewServlet());
reg.addUrlMappings("/druid/*");
reg.addInitParameter("loginUsername", "root");
reg.addInitParameter("loginPassword", "root!@#");
//reg.addInitParameter("logSlowSql", "");
return reg;
}
/**
*
* @Title: druidDataSource
* @Description: 數(shù)據(jù)庫(kù)源Bean
* @param @return 參數(shù)說(shuō)明
* @return DataSource 返回類型
* @throws
*/
@Bean
public DataSource druidDataSource() {
// 數(shù)據(jù)源
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setDriverClassName(driver); // 驅(qū)動(dòng)
druidDataSource.setUrl(url); // 數(shù)據(jù)庫(kù)連接地址
druidDataSource.setUsername(username); // 數(shù)據(jù)庫(kù)用戶名
druidDataSource.setPassword(password); // 數(shù)據(jù)庫(kù)密碼
druidDataSource.setInitialSize(druid_initialsize);// 初始化連接大小
druidDataSource.setMaxActive(druid_maxactive); // 連接池最大使用連接數(shù)量
druidDataSource.setMinIdle(druid_minidle); // 連接池最小空閑
druidDataSource.setMaxWait(druid_maxwait); // 獲取連接最大等待時(shí)間
// 打開PSCache，并且指定每個(gè)連接上PSCache的大小
druidDataSource.setPoolPreparedStatements(false);
druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(33);
//druidDataSource.setValidationQuery("SELECT 1"); // 用來(lái)檢測(cè)連接是否有效的sql
druidDataSource.setTestOnBorrow(false); // 申請(qǐng)連接時(shí)執(zhí)行validationQuery檢測(cè)連接是否有效，做了這個(gè)配置會(huì)降低性能。
druidDataSource.setTestOnReturn(false); // 歸還連接時(shí)執(zhí)行validationQuery檢測(cè)連接是否有效，做了這個(gè)配置會(huì)降低性能
druidDataSource.setTestWhileIdle(false); // 建議配置為true，不影響性能，并且保證安全性。申請(qǐng)連接的時(shí)候檢測(cè)，如果空閑時(shí)間大于timeBetweenEvictionRunsMillis，執(zhí)行validationQuery檢測(cè)連接是否有效
druidDataSource.setTimeBetweenLogStatsMillis(60000); // 配置間隔多久才進(jìn)行一次檢測(cè)，檢測(cè)需要關(guān)閉的空閑連接，單位是毫秒
druidDataSource.setMinEvictableIdleTimeMillis(1800000); // 配置一個(gè)連接在池中最小生存的時(shí)間，單位是毫秒
// 當(dāng)程序存在缺陷時(shí)，申請(qǐng)的連接忘記關(guān)閉，這時(shí)候，就存在連接泄漏
// 配置removeAbandoned對(duì)性能會(huì)有一些影響，建議懷疑存在泄漏之后再打開。在上面的配置中，如果連接超過(guò)30分鐘未關(guān)閉，就會(huì)被強(qiáng)行回收，并且日志記錄連接申請(qǐng)時(shí)的調(diào)用堆棧。
druidDataSource.setRemoveAbandoned(false); // 打開removeAbandoned功能
druidDataSource.setRemoveAbandonedTimeout(1800); // 1800秒，也就是30分鐘
druidDataSource.setLogAbandoned(false); // 關(guān)閉abanded連接時(shí)輸出錯(cuò)誤日志
// 過(guò)濾器
List<Filter> filters = new ArrayList<Filter>();
filters.add(this.getStatFilter()); // 監(jiān)控
//filters.add(this.getSlf4jLogFilter()); // 日志
filters.add(this.getWallFilter()); // 防火墻
druidDataSource.setProxyFilters(filters);
log.info("連接池配置信息："+druidDataSource.getUrl());
return druidDataSource;
}
@Bean
public FilterRegistrationBean filterRegistrationBean() {
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
WebStatFilter webStatFilter = new WebStatFilter();
filterRegistrationBean.setFilter(webStatFilter);
filterRegistrationBean.addUrlPatterns("/*");
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
return filterRegistrationBean;
}
/**
*
* @Title: getStatFilter
* @Description: 監(jiān)控過(guò)濾器
* @param @return 參數(shù)說(shuō)明
* @return StatFilter 返回類型
* @throws
*/
public StatFilter getStatFilter(){
StatFilter sFilter = new StatFilter();
//sFilter.setSlowSqlMillis(2000); // 慢sql，毫秒時(shí)間
sFilter.setLogSlowSql(false); // 慢sql日志
sFilter.setMergeSql(true); // sql合并優(yōu)化處理
return sFilter;
}
/**
*
* @Title: getSlf4jLogFilter
* @Description: 監(jiān)控日志過(guò)濾器
* @param @return 參數(shù)說(shuō)明
* @return Slf4jLogFilter 返回類型
* @throws
*/
public Slf4jLogFilter getSlf4jLogFilter(){
Slf4jLogFilter slFilter = new Slf4jLogFilter();
slFilter.setResultSetLogEnabled(false);
slFilter.setStatementExecutableSqlLogEnable(false);
return slFilter;
}
/**
*
* @Title: getWallFilter
* @Description: 防火墻過(guò)濾器
* @param @return 參數(shù)說(shuō)明
* @return WallFilter 返回類型
* @throws
*/
public WallFilter getWallFilter(){
WallFilter wFilter = new WallFilter();
wFilter.setDbType("mysql");
wFilter.setConfig(this.getWallConfig());
wFilter.setLogViolation(true); // 對(duì)被認(rèn)為是攻擊的SQL進(jìn)行LOG.error輸出
wFilter.setThrowException(true); // 對(duì)被認(rèn)為是攻擊的SQL拋出SQLExcepton
return wFilter;
}
/**
*
* @Title: getWallConfig
* @Description: 數(shù)據(jù)防火墻配置
* @param @return 參數(shù)說(shuō)明
* @return WallConfig 返回類型
* @throws
*/
public WallConfig getWallConfig(){
WallConfig wConfig = new WallConfig();
wConfig.setDir("META-INF/druid/wall/mysql"); // 指定配置裝載的目錄
// 攔截配置－語(yǔ)句
wConfig.setTruncateAllow(false); // truncate語(yǔ)句是危險(xiǎn)，缺省打開，若需要自行關(guān)閉
wConfig.setCreateTableAllow(true); // 是否允許創(chuàng)建表
wConfig.setAlterTableAllow(false); // 是否允許執(zhí)行Alter Table語(yǔ)句
wConfig.setDropTableAllow(false); // 是否允許修改表
// 其他攔截配置
wConfig.setStrictSyntaxCheck(true); // 是否進(jìn)行嚴(yán)格的語(yǔ)法檢測(cè)，Druid SQL Parser在某些場(chǎng)景不能覆蓋所有的SQL語(yǔ)法，出現(xiàn)解析SQL出錯(cuò)，可以臨時(shí)把這個(gè)選項(xiàng)設(shè)置為false，同時(shí)把SQL反饋給Druid的開發(fā)者
wConfig.setConditionOpBitwseAllow(true); // 查詢條件中是否允許有"&"、"~"、"|"、"^"運(yùn)算符。
wConfig.setMinusAllow(true); // 是否允許SELECT * FROM A MINUS SELECT * FROM B這樣的語(yǔ)句
wConfig.setIntersectAllow(true); // 是否允許SELECT * FROM A INTERSECT SELECT * FROM B這樣的語(yǔ)句
//wConfig.setMetadataAllow(false); // 是否允許調(diào)用Connection.getMetadata方法，這個(gè)方法調(diào)用會(huì)暴露數(shù)據(jù)庫(kù)的表信息
return wConfig;
}
}可見，如果用自動(dòng)配置的方式放棄這些原有的配置風(fēng)險(xiǎn)有多大
怎么改呢？
第一步，創(chuàng)建一個(gè)interface，用以加載自定義的分表策略
可以在各個(gè)子項(xiàng)目中創(chuàng)建bean，實(shí)現(xiàn)此接口
public interface ShardingRuleSupport {
void configRule(ShardingRuleConfiguration shardingRuleConfig);
}第二步，在DruidConfiguration.class中注入所有的ShardingRuleSupport
@Autowired(required = false) private List<ShardingRuleSupport> shardingRuleSupport;
第三步，創(chuàng)建sharding-jdbc分表數(shù)據(jù)源
//包裝Druid數(shù)據(jù)源
Map<String, DataSource> dataSourceMap = new HashMap<>();
//自定義一個(gè)名稱為ds0的數(shù)據(jù)源名稱，包裝原有的Druid數(shù)據(jù)源,還可以再定義多個(gè)數(shù)據(jù)源
//因?yàn)橹环直聿环謳?kù)，所有定義一個(gè)數(shù)據(jù)源就夠了
dataSourceMap.put("ds0", druidDataSource);
//加載分表配置
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
//要加載所有的ShardingRuleSupport實(shí)現(xiàn)bean，所以用for循環(huán)加載
for (ShardingRuleSupport support : shardingRuleSupport) {
support.configRule(shardingRuleConfig);
}
//加載其他配置
Properties properties = new Properties();
//由于未使用starter的自動(dòng)裝配，所以手動(dòng)設(shè)置，是否顯示分表sql
properties.put("sql.show", sqlShow);
//返回ShardingDataSource包裝的數(shù)據(jù)源
return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, properties);package com.xxx.common.autoConfiguration;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.logging.Slf4jLogFilter;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import lombok.extern.slf4j.Slf4j;
/**
* @ClassName: DruidConfiguration
* @Description: Druid連接池配置
*/
@Configuration
@Slf4j
public class DruidConfiguration {
@Value("${spring.datasource.driver-class-name}")
private String driver;
@Value("${spring.datasource.url}")
private String url;
@Value("${spring.datasource.username}")
private String username;
@Value("${spring.datasource.password}")
private String password;
@Value("${datasource.druid.initialsize}")
private Integer druid_initialsize = 0;
@Value("${datasource.druid.maxactive}")
private Integer druid_maxactive = 20;
@Value("${datasource.druid.minidle}")
private Integer druid_minidle = 0;
@Value("${datasource.druid.maxwait}")
private Integer druid_maxwait = 30000;
/**
* 默認(rèn)不顯示分表SQL
*/
@Value("${spring.shardingsphere.props.sql.show:false}")
private boolean sqlShow;
@Autowired(required = false)
private List<ShardingRuleSupport> shardingRuleSupport;
@Bean
public ServletRegistrationBean druidServlet() {
ServletRegistrationBean reg = new ServletRegistrationBean();
reg.setServlet(new StatViewServlet());
reg.addUrlMappings("/druid/*");
reg.addInitParameter("loginUsername", "root");
reg.addInitParameter("loginPassword", "root!@#");
//reg.addInitParameter("logSlowSql", "");
return reg;
}
/**
*
* @Title: druidDataSource
* @Description: 數(shù)據(jù)庫(kù)源Bean
* @param @return 參數(shù)說(shuō)明
* @return DataSource 返回類型
* @throws
*/
@Bean
public DataSource druidDataSource() {
// 數(shù)據(jù)源
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setDriverClassName(driver); // 驅(qū)動(dòng)
druidDataSource.setUrl(url); // 數(shù)據(jù)庫(kù)連接地址
druidDataSource.setUsername(username); // 數(shù)據(jù)庫(kù)用戶名
druidDataSource.setPassword(password); // 數(shù)據(jù)庫(kù)密碼
druidDataSource.setInitialSize(druid_initialsize);// 初始化連接大小
druidDataSource.setMaxActive(druid_maxactive); // 連接池最大使用連接數(shù)量
druidDataSource.setMinIdle(druid_minidle); // 連接池最小空閑
druidDataSource.setMaxWait(druid_maxwait); // 獲取連接最大等待時(shí)間
// 打開PSCache，并且指定每個(gè)連接上PSCache的大小
druidDataSource.setPoolPreparedStatements(false);
druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(33);
//druidDataSource.setValidationQuery("SELECT 1"); // 用來(lái)檢測(cè)連接是否有效的sql
druidDataSource.setTestOnBorrow(false); // 申請(qǐng)連接時(shí)執(zhí)行validationQuery檢測(cè)連接是否有效，做了這個(gè)配置會(huì)降低性能。
druidDataSource.setTestOnReturn(false); // 歸還連接時(shí)執(zhí)行validationQuery檢測(cè)連接是否有效，做了這個(gè)配置會(huì)降低性能
druidDataSource.setTestWhileIdle(false); // 建議配置為true，不影響性能，并且保證安全性。申請(qǐng)連接的時(shí)候檢測(cè)，如果空閑時(shí)間大于timeBetweenEvictionRunsMillis，執(zhí)行validationQuery檢測(cè)連接是否有效
druidDataSource.setTimeBetweenLogStatsMillis(60000); // 配置間隔多久才進(jìn)行一次檢測(cè)，檢測(cè)需要關(guān)閉的空閑連接，單位是毫秒
druidDataSource.setMinEvictableIdleTimeMillis(1800000); // 配置一個(gè)連接在池中最小生存的時(shí)間，單位是毫秒
// 當(dāng)程序存在缺陷時(shí)，申請(qǐng)的連接忘記關(guān)閉，這時(shí)候，就存在連接泄漏
// 配置removeAbandoned對(duì)性能會(huì)有一些影響，建議懷疑存在泄漏之后再打開。在上面的配置中，如果連接超過(guò)30分鐘未關(guān)閉，就會(huì)被強(qiáng)行回收，并且日志記錄連接申請(qǐng)時(shí)的調(diào)用堆棧。
druidDataSource.setRemoveAbandoned(false); // 打開removeAbandoned功能
druidDataSource.setRemoveAbandonedTimeout(1800); // 1800秒，也就是30分鐘
druidDataSource.setLogAbandoned(false); // 關(guān)閉abanded連接時(shí)輸出錯(cuò)誤日志
// 過(guò)濾器
List<Filter> filters = new ArrayList<Filter>();
filters.add(this.getStatFilter()); // 監(jiān)控
//filters.add(this.getSlf4jLogFilter()); // 日志
filters.add(this.getWallFilter()); // 防火墻
druidDataSource.setProxyFilters(filters);
log.info("連接池配置信息："+druidDataSource.getUrl());
if (shardingRuleSupport == null || shardingRuleSupport.isEmpty()) {
log.info("............分表配置為空，使用默認(rèn)的數(shù)據(jù)源............");
return druidDataSource;
}
log.info("++++++++++++加載sharding jdbc配置++++++++++++");
//包裝Druid數(shù)據(jù)源
Map<String, DataSource> dataSourceMap = new HashMap<>();
//自定義一個(gè)名稱為ds0的數(shù)據(jù)源名稱，包裝原有的Druid數(shù)據(jù)源,還可以再定義多個(gè)數(shù)據(jù)源
//因?yàn)橹环直聿环謳?kù)，所有定義一個(gè)數(shù)據(jù)源就夠了
dataSourceMap.put("ds0", druidDataSource);
//加載分表配置
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
//要加載所有的ShardingRuleSupport實(shí)現(xiàn)bean，所以用for循環(huán)加載
for (ShardingRuleSupport support : shardingRuleSupport) {
support.configRule(shardingRuleConfig);
}
//加載其他配置
Properties properties = new Properties();
//由于未使用starter的自動(dòng)裝配，所以手動(dòng)設(shè)置，是否顯示分表sql
properties.put("sql.show", sqlShow);
//返回ShardingDataSource包裝的數(shù)據(jù)源
return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, properties);
}
@Bean
public FilterRegistrationBean filterRegistrationBean() {
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
WebStatFilter webStatFilter = new WebStatFilter();
filterRegistrationBean.setFilter(webStatFilter);
filterRegistrationBean.addUrlPatterns("/*");
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
return filterRegistrationBean;
}
/**
*
* @Title: getStatFilter
* @Description: 監(jiān)控過(guò)濾器
* @param @return 參數(shù)說(shuō)明
* @return StatFilter 返回類型
* @throws
*/
public StatFilter getStatFilter(){
StatFilter sFilter = new StatFilter();
//sFilter.setSlowSqlMillis(2000); // 慢sql，毫秒時(shí)間
sFilter.setLogSlowSql(false); // 慢sql日志
sFilter.setMergeSql(true); // sql合并優(yōu)化處理
return sFilter;
}
/**
*
* @Title: getSlf4jLogFilter
* @Description: 監(jiān)控日志過(guò)濾器
* @param @return 參數(shù)說(shuō)明
* @return Slf4jLogFilter 返回類型
* @throws
*/
public Slf4jLogFilter getSlf4jLogFilter(){
Slf4jLogFilter slFilter = new Slf4jLogFilter();
slFilter.setResultSetLogEnabled(false);
slFilter.setStatementExecutableSqlLogEnable(false);
return slFilter;
}
/**
*
* @Title: getWallFilter
* @Description: 防火墻過(guò)濾器
* @param @return 參數(shù)說(shuō)明
* @return WallFilter 返回類型
* @throws
*/
public WallFilter getWallFilter(){
WallFilter wFilter = new WallFilter();
wFilter.setDbType("mysql");
wFilter.setConfig(this.getWallConfig());
wFilter.setLogViolation(true); // 對(duì)被認(rèn)為是攻擊的SQL進(jìn)行LOG.error輸出
wFilter.setThrowException(true); // 對(duì)被認(rèn)為是攻擊的SQL拋出SQLExcepton
return wFilter;
}
/**
*
* @Title: getWallConfig
* @Description: 數(shù)據(jù)防火墻配置
* @param @return 參數(shù)說(shuō)明
* @return WallConfig 返回類型
* @throws
*/
public WallConfig getWallConfig(){
WallConfig wConfig = new WallConfig();
wConfig.setDir("META-INF/druid/wall/mysql"); // 指定配置裝載的目錄
// 攔截配置－語(yǔ)句
wConfig.setTruncateAllow(false); // truncate語(yǔ)句是危險(xiǎn)，缺省打開，若需要自行關(guān)閉
wConfig.setCreateTableAllow(true); // 是否允許創(chuàng)建表
wConfig.setAlterTableAllow(false); // 是否允許執(zhí)行Alter Table語(yǔ)句
wConfig.setDropTableAllow(false); // 是否允許修改表
// 其他攔截配置
wConfig.setStrictSyntaxCheck(true); // 是否進(jìn)行嚴(yán)格的語(yǔ)法檢測(cè)，Druid SQL Parser在某些場(chǎng)景不能覆蓋所有的SQL語(yǔ)法，出現(xiàn)解析SQL出錯(cuò)，可以臨時(shí)把這個(gè)選項(xiàng)設(shè)置為false，同時(shí)把SQL反饋給Druid的開發(fā)者
wConfig.setConditionOpBitwseAllow(true); // 查詢條件中是否允許有"&"、"~"、"|"、"^"運(yùn)算符。
wConfig.setMinusAllow(true); // 是否允許SELECT * FROM A MINUS SELECT * FROM B這樣的語(yǔ)句
wConfig.setIntersectAllow(true); // 是否允許SELECT * FROM A INTERSECT SELECT * FROM B這樣的語(yǔ)句
//wConfig.setMetadataAllow(false); // 是否允許調(diào)用Connection.getMetadata方法，這個(gè)方法調(diào)用會(huì)暴露數(shù)據(jù)庫(kù)的表信息
return wConfig;
}
}創(chuàng)建幾個(gè)ShardingRuleSupport接口的實(shí)現(xiàn)Bean
@Component
public class DefaultShardingRuleAdapter implements ShardingRuleSupport {
@Override
public void configRule(ShardingRuleConfiguration shardingRuleConfiguration) {
Collection<TableRuleConfiguration> tableRuleConfigs = shardingRuleConfiguration.getTableRuleConfigs();
TableRuleConfiguration ruleConfig1 = new TableRuleConfiguration("table_one", "ds0.table_one_$->{0..9}");
ComplexShardingStrategyConfiguration strategyConfig1 = new ComplexShardingStrategyConfiguration("column_id", new MyDefaultShardingAlgorithm());
ruleConfig1.setTableShardingStrategyConfig(strategyConfig1);
tableRuleConfigs.add(ruleConfig1);
TableRuleConfiguration ruleConfig2 = new TableRuleConfiguration("table_two", "ds0.table_two_$->{0..9}");
ComplexShardingStrategyConfiguration strategyConfig2 = new ComplexShardingStrategyConfiguration("column_id", new MyDefaultShardingAlgorithm());
ruleConfig2.setTableShardingStrategyConfig(strategyConfig2);
tableRuleConfigs.add(ruleConfig2);
}
}@Component
public class CustomShardingRuleAdapter implements ShardingRuleSupport {
@Override
public void configRule(ShardingRuleConfiguration shardingRuleConfiguration) {
Collection<TableRuleConfiguration> tableRuleConfigs = shardingRuleConfiguration.getTableRuleConfigs();
TableRuleConfiguration ruleConfig1 = new TableRuleConfiguration(MyCustomShardingUtil.LOGIC_TABLE_NAME, MyCustomShardingUtil.ACTUAL_DATA_NODES);
ComplexShardingStrategyConfiguration strategyConfig1 = new ComplexShardingStrategyConfiguration(MyCustomShardingUtil.SHARDING_COLUMNS, new MyCustomShardingAlgorithm());
ruleConfig1.setTableShardingStrategyConfig(strategyConfig1);
tableRuleConfigs.add(ruleConfig1);
}
}public class MyDefaultShardingAlgorithm implements ComplexKeysShardingAlgorithm<String> {
public String getShardingKey () {
return "column_id";
}
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<String> shardingValue) {
Collection<String> col = new ArrayList<>();
String logicTableName = shardingValue.getLogicTableName() + "_";
Map<String, String> availableTargetNameMap = new HashMap<>();
for (String targetName : availableTargetNameMap) {
String endStr = StringUtils.substringAfter(targetName, logicTableName);
availableTargetNameMap.put(endStr, targetName);
}
int size = availableTargetNames.size();
//=,in
Collection<String> shardingColumnValues = shardingValue.getColumnNameAndShardingValuesMap().get(this.getShardingKey());
if (shardingColumnValues != null) {
for (String shardingColumnValue : shardingColumnValues) {
String modStr = Integer.toString(Math.abs(shardingColumnValue .hashCode()) % size);
String actualTableName = availableTargetNameMap.get(modStr);
if (StringUtils.isNotEmpty(actualTableName)) {
col.add(actualTableName);
}
}
}
//between and
//shardingValue.getColumnNameAndRangeValuesMap().get(this.getShardingKey());
... ...
//如果分表列不是有序的，則between and無(wú)意義，沒(méi)有必要實(shí)現(xiàn)
return col;
}
}public class MyCustomShardingAlgorithm extends MyDefaultShardingAlgorithm implements ComplexKeysShardingAlgorithm<String> {
@Override
public String getShardingKey () {
return MyCustomShardingUtil.SHARDING_COLUMNS;
}
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<String> shardingValue) {
Collection<String> col = new ArrayList<>();
String logicTableName = shardingValue.getLogicTableName() + "_";
Map<String, String> availableTargetNameMap = new HashMap<>();
for (String targetName : availableTargetNameMap) {
String endStr = StringUtils.substringAfter(targetName, logicTableName);
availableTargetNameMap.put(endStr, targetName);
}
Map<String, String> specialActualTableNameMap = MyCustomShardingUtil.getSpecialActualTableNameMap();
int count = (int) specialActualTableNameMap.values().stream().distinct().count();
int size = availableTargetNames.size() - count;
//=,in
Collection<String> shardingColumnValues = shardingValue.getColumnNameAndShardingValuesMap().get(this.getShardingKey());
if (shardingColumnValues != null) {
for (String shardingColumnValue : shardingColumnValues) {
String specialActualTableName = specialActualTableNameMap.get(shardingColumnValue);
if (StringUtils.isNotEmpty(specialActualTableName)) {
col.add(specialActualTableName);
continue;
}
String modStr = Integer.toString(Math.abs(shardingColumnValue .hashCode()) % size);
String actualTableName = availableTargetNameMap.get(modStr);
if (StringUtils.isNotEmpty(actualTableName)) {
col.add(actualTableName);
}
}
}
//between and
//shardingValue.getColumnNameAndRangeValuesMap().get(this.getShardingKey());
... ...
//如果分表列不是有序的，則between and無(wú)意義，沒(méi)有必要實(shí)現(xiàn)
return col;
}
}@Component
public class MyCustomShardingUtil {
/**
* 邏輯表名
*/
public static final String LOGIC_TABLE_NAME = "table_three";
/**
* 分片字段
*/
public static final String SHARDING_COLUMNS = "column_name";
/**
* 添加指定分片表的后綴
*/
private static final String[] SPECIAL_NODES = new String[]{"0sp", "1sp"};
// ds0.table_three_$->{((0..9).collect{t -> t.toString()} << ['0sp','1sp']).flatten()}
public static final String ACTUAL_DATA_NODES = "ds0." + LOGIC_TABLE_NAME + "_$->{((0..9).collect{t -> t.toString()} << "
+ "['" + SPECIAL_NODES[0] + "','" + SPECIAL_NODES[1] + "']"
+ ").flatten()}";
private static final List<String> specialList0 = new ArrayList<>();
@Value("${special.table_three.sp0.ids:null}")
private void setSpecialList0(String ids) {
if (StringUtils.isBlank(ids)) {
return;
}
String[] idSplit = StringUtils.split(ids, ",");
for (String id : idSplit) {
String trimId = StringUtils.trim(id);
if (StringUtils.isEmpty(trimId)) {
continue;
}
specialList0.add(trimId);
}
}
private static final List<String> specialList1 = new ArrayList<>();
@Value("${special.table_three.sp1.ids:null}")
private void setSpecialList1(String ids) {
if (StringUtils.isBlank(ids)) {
return;
}
String[] idSplit = StringUtils.split(ids, ",");
for (String id : idSplit) {
String trimId = StringUtils.trim(id);
if (StringUtils.isEmpty(trimId)) {
continue;
}
specialList1.add(trimId);
}
}
private static class SpecialActualTableNameHolder {
private static volatile Map<String, String> specialActualTableNameMap = new HashMap<>();
static {
for (String specialId : specialList0) {
specialActualTableNameMap.put(specialId, LOGIC_TABLE_NAME + "_" + SPECIAL_NODES[0]);
}
for (String specialId : specialList1) {
specialActualTableNameMap.put(specialId, LOGIC_TABLE_NAME + "_" + SPECIAL_NODES[1]);
}
}
}
/**
* @return 指定ID的表名映射
*/
public static Map<String, String> getSpecialActualTableNameMap() {
return SpecialActualTableNameHolder.specialActualTableNameMap;
}
}ShardingAlgorithm接口的子接口除了ComplexKeysShardingAlgorithm，還有HintShardingAlgorithm，PreciseShardingAlgorithm，RangeShardingAlgorithm；本教程使用了更通用的ComplexKeysShardingAlgorithm接口。
配置TableRuleConfiguration類時(shí)，使用了兩個(gè)參數(shù)的構(gòu)造器
public TableRuleConfiguration(String logicTable, String actualDataNodes) {}TableRuleConfiguration類還有一個(gè)參數(shù)的的構(gòu)造器，沒(méi)有實(shí)際數(shù)據(jù)節(jié)點(diǎn)，是給廣播表用的
public TableRuleConfiguration(String logicTable) {}ds0.table_three_$->{((0…9).collect{t -> t.toString()} << [‘0sp',‘1sp']).flatten()}
sharding-jdbc的groovy行表達(dá)式支持$->{…}或${…}，為了避免與spring的占位符混淆，官方推薦使用$->{…}
(0..9) 獲得0到9的集合
(0..9).collect{t -> t.toString()} 數(shù)值0到9的集合轉(zhuǎn)換成字符串0到9的數(shù)組
(0..9).collect{t -> t.toString()} << ['0sp','1sp'] 字符串0到9的數(shù)組合并['0sp','1sp']數(shù)組，結(jié)果為 ['0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ['0sp','1sp']]
flatten() 扁平化數(shù)組，結(jié)果為 ['0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '0sp', '1sp']
#是否顯示分表SQL,默認(rèn)為false spring.shardingsphere.props.sql.show=true #指定哪些列值入指定的分片表，多個(gè)列值以“,”分隔 #column_name為9997,9998,9999的記錄存入表table_three_0sp中 #column_name為1111,2222,3333,4444,5555的記錄存入表table_three_1sp中 #其余的值哈希取模后，存入對(duì)應(yīng)的table_three_模數(shù)表中 special.table_three.sp0.ids=9997,9998,9999 special.table_three.sp1.ids=1111,2222,3333,4444,5555
任何SQL，只要select子句中包含動(dòng)態(tài)參數(shù)，則拋出類型強(qiáng)轉(zhuǎn)異常
禁止修改分片鍵，如果update的set子句中存在分片鍵，則不能執(zhí)行sql
感謝各位的閱讀，以上就是“springboot怎么配置sharding-jdbc水平分表”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)springboot怎么配置sharding-jdbc水平分表這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼