SpringBoot整合ShardingSphere 小结

雨点打透心脏的1/2处 2023-03-13 02:26 5阅读 0赞

## **概要：** 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毫秒