SpringBoot整合ShardingSphere的示例代碼
概要: ShardingSphere是一套開源的分布式數(shù)據(jù)庫中間件解決方案組成的生態(tài)圈,它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar(計(jì)劃中)這3款相互獨(dú)立的產(chǎn)品組成。 他們均提供標(biāo)準(zhǔn)化的數(shù)據(jù)分片、分布式事務(wù)和數(shù)據(jù)庫治理功能,可適用于如Java同構(gòu)、異構(gòu)語言、云原生等各種多樣化的應(yīng)用場景。
官網(wǎng)地址:https://shardingsphere.apache.org/
一、相關(guān)依賴
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-core</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>3.1.0</version>
</dependency>
二、Nacos數(shù)據(jù)源配置
sharding:
dataSource:
db0:
driverClassName: com.mysql.cj.jdbc.Driver
url: mysql://127.0.0.1:3306/demo0
username: root
password: 123456
db1:
driverClassName: com.mysql.cj.jdbc.Driver
url: mysql://127.0.0.1:3306/demo1
username: root
password: 123456
三、項(xiàng)目配置
bootstrap-dev.properties
spring:
application:
name: demo
cloud:
nacos:
server-addr: 127.0.0.1:8848
config:
namespace: 9c6b8156-d045-463d-8fe6-4658ce78d0cc
file-extension: yml
SqlSessionConfig
package com.example.demo.config;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.plugin.Interceptor;
import org.mybatis.spring.mapper.MapperScannerConfigurer;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import javax.sql.DataSource;
@Configuration
public class SqlSessionConfig {
private Logger logger = LoggerFactory.getLogger(SqlSessionConfig.class);
@Bean("mySqlSessionFactoryBean")
public MybatisSqlSessionFactoryBean createSqlSessionFactory(@Qualifier("datasource") DataSource dataSource,
@Qualifier("paginationInterceptor") PaginationInterceptor paginationInterceptor) {
// MybatisSqlSessionFactory
MybatisSqlSessionFactoryBean sqlSessionFactoryBean = null;
try {
// 實(shí)例SessionFactory
sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
// 配置數(shù)據(jù)源
sqlSessionFactoryBean.setDataSource(dataSource);
// 設(shè)置 MyBatis-Plus 分頁插件
Interceptor [] plugins = {paginationInterceptor};
sqlSessionFactoryBean.setPlugins(plugins);
// 加載MyBatis配置文件
PathMatchingResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
sqlSessionFactoryBean.setMapperLocations(resourcePatternResolver.getResources("classpath*:mapper/*.xml"));
} catch (Exception e) {
logger.error("創(chuàng)建SqlSession連接工廠錯(cuò)誤:{}", e.getMessage());
}
return sqlSessionFactoryBean;
}
@Bean
public MapperScannerConfigurer myGetMapperScannerConfigurer() {
MapperScannerConfigurer myMapperScannerConfigurer = new MapperScannerConfigurer();
myMapperScannerConfigurer.setBasePackage("com.example.demo.mapper");
myMapperScannerConfigurer.setSqlSessionFactoryBeanName("mySqlSessionFactoryBean");
return myMapperScannerConfigurer;
}
}
DataSourceConfig
package com.example.demo.config;
import com.alibaba.druid.pool.DruidDataSource;
import io.shardingsphere.api.config.rule.ShardingRuleConfiguration;
import io.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
@Configuration
@ConfigurationProperties(prefix = "sharding")
public class DataSourceConfig {
private Map<String, DruidDataSource> dataSource;
public Map<String, DruidDataSource> getDataSource() {
return dataSource;
}
public void setDataSource(Map<String, DruidDataSource> dataSource) {
this.dataSource = dataSource;
}
@Bean("datasource")
public DataSource getDataSource(@Qualifier("shardingConfig") ShardingRuleConfiguration shardingRuleConfig,
@Qualifier("properties") Properties properties) throws SQLException {
Map<String, DataSource> dataSourceMap = new HashMap<>();
dataSource.forEach(dataSourceMap::put);
return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new HashMap<>(), properties);
}
@Bean("properties")
public Properties getProperties(){
// 獲取數(shù)據(jù)源對象
Properties props=new Properties();
/*
* ==== Properties取值范圍 ====
*
* SQL_SHOW("sql.show", String.valueOf(Boolean.FALSE), Boolean.TYPE),
* ACCEPTOR_SIZE("acceptor.size", String.valueOf(Runtime.getRuntime().availableProcessors() * 2), Integer.TYPE),
* EXECUTOR_SIZE("executor.size", String.valueOf(0), Integer.TYPE),
* MAX_CONNECTIONS_SIZE_PER_QUERY("max.connections.size.per.query", String.valueOf(1), Integer.TYPE),
* PROXY_FRONTEND_FLUSH_THRESHOLD("proxy.frontend.flush.threshold", String.valueOf(128), Integer.TYPE),
* PROXY_TRANSACTION_TYPE("proxy.transaction.type", "LOCAL", String.class),
* PROXY_OPENTRACING_ENABLED("proxy.opentracing.enabled", String.valueOf(Boolean.FALSE), Boolean.TYPE),
* PROXY_BACKEND_USE_NIO("proxy.backend.use.nio", String.valueOf(Boolean.FALSE), Boolean.TYPE),
* PROXY_BACKEND_MAX_CONNECTIONS("proxy.backend.max.connections", String.valueOf(8), Integer.TYPE),
* PROXY_BACKEND_CONNECTION_TIMEOUT_SECONDS("proxy.backend.connection.timeout.seconds", String.valueOf(60), Integer.TYPE),
* CHECK_TABLE_METADATA_ENABLED("check.table.metadata.enabled", String.valueOf(Boolean.FALSE), Boolean.TYPE);
*/
props.put("sql.show", "true");
return props;
}
}
ShardingRuleConfig
package com.example.demo.config;
import io.shardingsphere.api.config.rule.ShardingRuleConfiguration;
import io.shardingsphere.core.yaml.sharding.YamlShardingConfiguration;
import io.shardingsphere.core.yaml.sharding.YamlShardingRuleConfiguration;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.beans.factory.annotation.Value;
import java.io.File;
@Configuration
public class ShardingRuleConfig implements ApplicationContextAware {
/* 獲取環(huán)境變量 */
@Value("${spring.profiles.active}")
private String profile;
@Bean("shardingConfig")
public ShardingRuleConfiguration getShardingRuleConfig() throws Exception {
// 獲取yml路由規(guī)則配置文件
File yamlFile = new File("src/main/resources/sharding/" + profile + "/sharding.yml");
YamlShardingConfiguration yamlShardingRuleConfiguration = YamlShardingConfiguration.unmarshal(yamlFile);
YamlShardingRuleConfiguration shardingRule = yamlShardingRuleConfiguration.getShardingRule();
if (null == shardingRule) {
throw new Exception("YamlShardingRuleConfiguration is Null!");
}
return shardingRule.getShardingRuleConfiguration();
}
}
src/main/resources/dev/sharding.yml
shardingRule:
tables:
user:
actualDataNodes: db${0..1}.user${0..1}
databaseStrategy:
inline:
shardingColumn: id
algorithmExpression: db${id % 2}
tableStrategy:
inline:
shardingColumn: id
algorithmExpression: user${id % 2}
注:修復(fù)相同路由字段導(dǎo)致部分分表無法落地?cái)?shù)據(jù),可以自定義相應(yīng)規(guī)則,例如修改為以下配置:
shardingRule:
tables:
user:
actualDataNodes: db${0..1}.user${0..1}
databaseStrategy:
inline:
shardingColumn: id
algorithmExpression: db${Math.round(id / 2) % 2}
tableStrategy:
inline:
shardingColumn: id
algorithmExpression: user${id % 2}
四、驗(yàn)證
2020-05-11 09:51:09.239 INFO 6352 --- [ main] trationDelegate$BeanPostProcessorChecker : Bean 'org.springframework.cloud.autoconfigure.ConfigurationPropertiesRebinderAutoConfiguration' of type [org.springframework.cloud.autoconfigure.ConfigurationPropertiesRebinderAutoConfiguration$$EnhancerBySpringCGLIB$$dd8e22ae] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
. ____ _ __ _ _
/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v2.2.6.RELEASE)
2020-05-11 09:51:09.479 INFO 6352 --- [ main] c.a.c.n.c.NacosPropertySourceBuilder : Loading nacos data, dataId: 'demo', group: 'DEFAULT_GROUP', data: spring:
profiles:
active: dev
sharding:
datasource:
db0:
driverClassName: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://106.13.181.6:3306/demo0
username: root
password: 123456
db1:
driverClassName: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://106.13.181.6:3306/demo1
username: root
password: 123456
2020-05-11 09:51:09.489 WARN 6352 --- [ main] c.a.c.n.c.NacosPropertySourceBuilder : Ignore the empty nacos configuration and get it based on dataId[demo.yml] & group[DEFAULT_GROUP]
2020-05-11 09:51:09.495 WARN 6352 --- [ main] c.a.c.n.c.NacosPropertySourceBuilder : Ignore the empty nacos configuration and get it based on dataId[demo-dev.yml] & group[DEFAULT_GROUP]
2020-05-11 09:51:09.495 INFO 6352 --- [ main] b.c.PropertySourceBootstrapConfiguration : Located property source: CompositePropertySource {name='NACOS', propertySources=[NacosPropertySource {name='demo-dev.yml'}, NacosPropertySource {name='demo.yml'}, NacosPropertySource {name='demo'}]}
2020-05-11 09:51:09.499 INFO 6352 --- [ main] com.example.demo.DemoApplication : The following profiles are active: dev
2020-05-11 09:51:09.965 WARN 6352 --- [ main] o.m.s.mapper.ClassPathMapperScanner : Skipping MapperFactoryBean with name 'userMapper' and 'com.example.demo.mapper.UserMapper' mapperInterface. Bean already defined with the same name!
2020-05-11 09:51:09.965 WARN 6352 --- [ main] o.m.s.mapper.ClassPathMapperScanner : No MyBatis mapper was found in '[com.example.demo.mapper]' package. Please check your configuration.
2020-05-11 09:51:09.966 INFO 6352 --- [ main] o.s.c.a.ConfigurationClassPostProcessor : Cannot enhance @Configuration bean definition 'sqlSessionConfig' since its singleton instance has been created too early. The typical cause is a non-static @Bean method with a BeanDefinitionRegistryPostProcessor return type: Consider declaring such methods as 'static'.
2020-05-11 09:51:09.989 INFO 6352 --- [ main] o.s.cloud.context.scope.GenericScope : BeanFactory id=3955a554-148e-313a-91f9-d6a10f2dc8c3
2020-05-11 09:51:10.150 INFO 6352 --- [ main] trationDelegate$BeanPostProcessorChecker : Bean 'org.springframework.cloud.autoconfigure.ConfigurationPropertiesRebinderAutoConfiguration' of type [org.springframework.cloud.autoconfigure.ConfigurationPropertiesRebinderAutoConfiguration$$EnhancerBySpringCGLIB$$dd8e22ae] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2020-05-11 09:51:10.380 INFO 6352 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat initialized with port(s): 8080 (http)
2020-05-11 09:51:10.386 INFO 6352 --- [ main] o.a.coyote.http11.Http11NioProtocol : Initializing ProtocolHandler ["http-nio-8080"]
2020-05-11 09:51:10.387 INFO 6352 --- [ main] o.apache.catalina.core.StandardService : Starting service [Tomcat]
2020-05-11 09:51:10.387 INFO 6352 --- [ main] org.apache.catalina.core.StandardEngine : Starting Servlet engine: [Apache Tomcat/9.0.33]
2020-05-11 09:51:10.507 INFO 6352 --- [ main] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring embedded WebApplicationContext
2020-05-11 09:51:10.508 INFO 6352 --- [ main] o.s.web.context.ContextLoader : Root WebApplicationContext: initialization completed in 994 ms
2020-05-11 09:51:10.770 INFO 6352 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2020-05-11 09:51:11.562 INFO 6352 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
2020-05-11 09:51:11.570 INFO 6352 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Starting...
2020-05-11 09:51:12.226 INFO 6352 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Start completed.
_ _ |_ _ _|_. ___ _ | _
| | |\/|_)(_| | |_\ |_)||_|_\
/ |
3.3.1
2020-05-11 09:51:12.876 WARN 6352 --- [ main] c.n.c.sources.URLConfigurationSource : No URLs will be polled as dynamic configuration sources.
2020-05-11 09:51:12.877 INFO 6352 --- [ main] c.n.c.sources.URLConfigurationSource : To enable URLs as dynamic configuration sources, define System property archaius.configurationSource.additionalUrls or make config.properties available on classpath.
2020-05-11 09:51:12.880 WARN 6352 --- [ main] c.n.c.sources.URLConfigurationSource : No URLs will be polled as dynamic configuration sources.
2020-05-11 09:51:12.880 INFO 6352 --- [ main] c.n.c.sources.URLConfigurationSource : To enable URLs as dynamic configuration sources, define System property archaius.configurationSource.additionalUrls or make config.properties available on classpath.
2020-05-11 09:51:13.019 INFO 6352 --- [ main] o.s.s.concurrent.ThreadPoolTaskExecutor : Initializing ExecutorService 'applicationTaskExecutor'
2020-05-11 09:51:13.257 INFO 6352 --- [ main] o.s.s.c.ThreadPoolTaskScheduler : Initializing ExecutorService
2020-05-11 09:51:13.477 INFO 6352 --- [ main] o.a.coyote.http11.Http11NioProtocol : Starting ProtocolHandler ["http-nio-8080"]
2020-05-11 09:51:13.495 INFO 6352 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8080 (http) with context path ''
2020-05-11 09:51:13.554 INFO 6352 --- [ main] c.a.c.n.registry.NacosServiceRegistry : nacos registry, DEFAULT_GROUP demo 10.118.37.75:8080 register finished
2020-05-11 09:51:13.621 INFO 6352 --- [ main] com.example.demo.DemoApplication : Started DemoApplication in 5.276 seconds (JVM running for 6.226)
2020-05-11 09:51:16.719 INFO 6352 --- [nio-8080-exec-2] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring DispatcherServlet 'dispatcherServlet'
2020-05-11 09:51:16.720 INFO 6352 --- [nio-8080-exec-2] o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet'
2020-05-11 09:51:16.730 INFO 6352 --- [nio-8080-exec-2] o.s.web.servlet.DispatcherServlet : Completed initialization in 10 ms
2020-05-11 09:51:16.792 INFO 6352 --- [nio-8080-exec-2] com.example.demo.config.LogAspect :
請求信息:
【請求地址】:/demo/create
【請求頭】:content-type = application/json, user-agent = PostmanRuntime/7.24.0, accept = */*, postman-token = 25dbfb89-782d-45b2-bbb1-b41380c27af7, host = localhost:8080, accept-encoding = gzip, deflate, br, connection = keep-alive, content-length = 61
【請求方法】:String com.example.demo.controller.UserController.create(UserDTO)
【請求參數(shù)】:[UserDTO(id=123458, name=zhangsan, phone=17751033130, sex=1)]
2020-05-11 09:51:16.832 DEBUG 6352 --- [nio-8080-exec-2] c.example.demo.mapper.UserMapper.insert : ==> Preparing: INSERT INTO user ( id, name, sex, phone, create_time, enable, version ) VALUES ( ?, ?, ?, ?, ?, ?, ? )
2020-05-11 09:51:16.848 DEBUG 6352 --- [nio-8080-exec-2] c.example.demo.mapper.UserMapper.insert : ==> Parameters: 123458(Long), zhangsan(String), MAN(String), 17751033130(String), 2020-05-11T09:51:16.797(LocalDateTime), true(Boolean), 1(Long)
2020-05-11 09:51:16.905 INFO 6352 --- [nio-8080-exec-2] ShardingSphere-SQL : Rule Type: sharding
2020-05-11 09:51:16.905 INFO 6352 --- [nio-8080-exec-2] ShardingSphere-SQL : Logic SQL: INSERT INTO user ( id,
name,
sex,
phone,
create_time,
enable,
version ) VALUES ( ?,
?,
?,
?,
?,
?,
? )
2020-05-11 09:51:16.905 INFO 6352 --- [nio-8080-exec-2] ShardingSphere-SQL : SQLStatement: InsertStatement(super=DMLStatement(super=io.shardingsphere.core.parsing.parser.sql.dml.insert.InsertStatement@362afd05), columns=[Column(name=id, tableName=user), Column(name=name, tableName=user), Column(name=sex, tableName=user), Column(name=phone, tableName=user), Column(name=create_time, tableName=user), Column(name=enable, tableName=user), Column(name=version, tableName=user)], generatedKeyConditions=[], insertValues=InsertValues(insertValues=[InsertValue(type=VALUES, expression=( ?,
?,
?,
?,
?,
?,
? ), parametersCount=7)]), columnsListLastPosition=71, generateKeyColumnIndex=-1, insertValuesListLastPosition=105)
2020-05-11 09:51:16.905 INFO 6352 --- [nio-8080-exec-2] ShardingSphere-SQL : Actual SQL: db0 ::: INSERT INTO user0 ( id,
name,
sex,
phone,
create_time,
enable,
version ) VALUES ( ?,
?,
?,
?,
?,
?,
? ) ::: [[123458, zhangsan, MAN, 17751033130, 2020-05-11T09:51:16.797, true, 1]]
2020-05-11 09:51:17.132 DEBUG 6352 --- [nio-8080-exec-2] c.example.demo.mapper.UserMapper.insert : <== Updates: 1
2020-05-11 09:51:17.135 INFO 6352 --- [nio-8080-exec-2] com.example.demo.config.LogAspect :
執(zhí)行結(jié)果:
【響應(yīng)結(jié)果】:"ok"
【執(zhí)行耗時(shí)】:343毫秒
到此這篇關(guān)于SpringBoot整合ShardingSphere的示例代碼的文章就介紹到這了,更多相關(guān)SpringBoot整合ShardingSphere內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
- SpringBoot3和ShardingSphere5框架實(shí)現(xiàn)數(shù)據(jù)分庫分表
- Springboot2.x+ShardingSphere實(shí)現(xiàn)分庫分表的示例代碼
- springboot如何使用yml文件方式配置shardingsphere
- SpringBoot整合ShardingSphere5.x實(shí)現(xiàn)數(shù)據(jù)加解密功能(最新推薦)
- SpringBoot+ShardingSphereJDBC實(shí)現(xiàn)讀寫分離詳情
- springboot整合shardingsphere和seata實(shí)現(xiàn)分布式事務(wù)的實(shí)踐
- SpringBoot?整合?ShardingSphere4.1.1實(shí)現(xiàn)分庫分表功能
相關(guān)文章
Spring MVC 基于URL的映射規(guī)則(注解版)
這篇文章主要介紹了Spring MVC 基于URL的映射規(guī)則(注解版) ,詳細(xì)的介紹了幾種方式,有興趣的可以了解一下2017-05-05
關(guān)于java連接池/線程池/內(nèi)存池/進(jìn)程池等匯總分析
這篇文章主要介紹了關(guān)于java連接池/線程池/內(nèi)存池/進(jìn)程池等匯總分析,本文將介紹池技術(shù)的由來、原理、優(yōu)缺點(diǎn)以及常見的池技術(shù)類型,需要的朋友可以參考下2023-04-04
MyBatis如何進(jìn)行雙重foreach循環(huán)
這篇文章主要介紹了MyBatis如何進(jìn)行雙重foreach循環(huán),具有很好的參考價(jià)值,希望對大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2022-02-02
Java使用Instant時(shí)輸出的時(shí)間比預(yù)期少了八個(gè)小時(shí)
在Java中,LocalDateTime表示沒有時(shí)區(qū)信息的日期和時(shí)間,而Instant表示基于UTC的時(shí)間點(diǎn),本文主要介紹了Java使用Instant時(shí)輸出的時(shí)間比預(yù)期少了八個(gè)小時(shí)的問題解決,感興趣的可以了解一下2024-09-09
maven升級(jí)版本后報(bào)錯(cuò):Blocked mirror for repositories
本文主要介紹了maven升級(jí)版本后報(bào)錯(cuò):Blocked mirror for repositories,文中的解決方法非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-09-09

