SpringBoot整合ShardingSphere的示例代码

< 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 >

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

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;

     }

}

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;

     }

}

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();

     }

}

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毫秒