SpringBoot整合ShardingSphere的示例代碼
概要: ShardingSphere是一套開源的分佈式數據庫中間件解決方案組成的生態圈,它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar(計劃中)這3款相互獨立的產品組成。 他們均提供標準化的數據分片、分佈式事務和數據庫治理功能,可適用於如Java同構、異構語言、雲原生等各種多樣化的應用場景。
官網地址:https://shardingsphere.apache.org/
一、相關依賴
<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數據源配置
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
三、項目配置
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 { // 實例SessionFactory sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean(); // 配置數據源 sqlSessionFactoryBean.setDataSource(dataSource); // 設置 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("創建SqlSession連接工廠錯誤:{}", 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(){ // 獲取數據源對象 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 { /* 獲取環境變量 */ @Value("${spring.profiles.active}") private String profile; @Bean("shardingConfig") public ShardingRuleConfiguration getShardingRuleConfig() throws Exception { // 獲取yml路由規則配置文件 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}
註:修復相同路由字段導致部分分表無法落地數據,可以自定義相應規則,例如修改為以下配置:
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}
四、驗證
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) 【請求參數】:[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 : 執行結果: 【響應結果】:"ok" 【執行耗時】:343毫秒
到此這篇關於SpringBoot整合ShardingSphere的示例代碼的文章就介紹到這瞭,更多相關SpringBoot整合ShardingSphere內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- SharedingSphere 自定義脫敏規則介紹
- springboot如何連接兩個數據庫(多個)
- 使用sharding-jdbc實現水平分庫+水平分表的示例代碼
- 關於springboot2.4跨域配置問題
- @MapperScan和@ComponentScan一塊使用導致沖突的解決