springboot整合shardingjdbc与tomcatjdbc

文章基于：sharding-jdbc 3.0.0，tomcat-jdbc 8.5.32

springboot配置

1. 配置parent与start依赖

<!--spring boot-->
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.19.RELEASE</version>
    <relativePath/> <!-- lookup parent from repository -->
  </parent>
  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter</artifactId>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>

1. AppContext配置扫描路径以及spring配置等配置

@Configuration
@ImportResource(locations = { "classpath:spring/*" })
@PropertySource("classpath:config/tomcat-jdbc.properties")
@ComponentScan(basePackages = "org.gallant.data")
@MapperScan("org.gallant.data.sharding.jdbc.pool.mapper")
public class AppContext 

1. 启动入口类：Application

@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
public class Application {

    public static void main(String[] args) throws InterruptedException {
        SpringApplication.run(Application.class, args);
        Thread.sleep(Integer.MAX_VALUE);
    }
}

至此一个简单springboot应用已经完成啦

sharding-jdbc配置

根据官方文档操作一通，采用properties的方式配置，需要引入：sharding-jdbc-spring-boot-starter依赖，但是该jar包目前最新是2.0.0.M3版本，且与3.0.0是有冲突，只能使用yaml配置方式
由于是多数据源方式，所以此处仅使用yaml配置分库分表规则，数据源在构建ShardingDataSource时传入即可

1. 规则配置：yaml，官方文档的配置案例是老版本结构，会报错，因为结构问题，博主亲测改配置可行

shardingRule:
  tables:
    gallant_order:
      actualDataNodes: ds${0..1}.gallant_order${0..1}
      databaseStrategy:
        inline:
          shardingColumn: user_id
          algorithmExpression: ds${user_id % 2}
      tableStrategy:
        inline:
          shardingColumn: city_id
          algorithmExpression: gallant_order${city_id % 2}

1. 依赖配置

<!--sharding-jdbc-->
    <dependency>
      <groupId>io.shardingsphere</groupId>
      <artifactId>sharding-jdbc-core</artifactId>
      <version>3.0.0</version>
    </dependency>

1. ShardingDataSource数据源构建

		@Bean
    public DataSource dataSource() throws SQLException, IOException {
        Resource resource = new ClassPathResource("config/sharding-jdbc.yml");
        Map<String, DataSource> dataSourceMap = new HashMap<>(2);
        // 数据源配置，也就是我们所使用的tomcat-jdbc连接池
        dataSourceMap.put("ds0", dataSource0());
        dataSourceMap.put("ds1", dataSource1());
        return YamlShardingDataSourceFactory.createDataSource(dataSourceMap, resource.getFile());
    }

tomcat-jdbc配置

1. 连接池依赖，当然要结合使用spring-boot整合的jdbc

		<dependency>
      <groupId>org.apache.tomcat</groupId>
      <artifactId>tomcat-jdbc</artifactId>
      <version>8.5.32</version>
    </dependency>
    <!--spring-data-jdbc-->
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-jdbc</artifactId>
      <version>1.5.4.RELEASE</version>
    </dependency>

1. 连接池配置

mybatis.mapper-locations=classpath*:mapper-gallant/*Mapper*.xml

spring.datasource0.url=jdbc:mysql://localhost:3306/gallant?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&logger=com.mysql.jdbc.log.Slf4JLogger
spring.datasource0.username=gallant
spring.datasource0.password=gallant
spring.datasource0.driver-class-name=com.mysql.jdbc.Driver
spring.datasource0.timeBetweenEvictionRunsMillis=5000
spring.datasource0.removeAbandoned=true
spring.datasource0.removeAbandonedTimeout=5000
spring.datasource0.abandonWhenPercentageFull=0
spring.datasource0.logAbandoned=0
spring.datasource0.minEvictableIdleTimeMillis=0
spring.datasource0.testOnBorrow=false
spring.datasource0.testOnConnect=false
spring.datasource0.testOnReturn=false
spring.datasource0.testWhileIdle=true
spring.datasource0.validationQuery=SELECT 1
spring.datasource0.validationQueryTimeout=1
spring.datasource0.validationInterval=5000
spring.datasource0.logValidationErrors=true
spring.datasource0.maxActive=100
spring.datasource0.maxIdle=100
spring.datasource0.initialSize=10
spring.datasource0.maxWait=0
spring.datasource0.maxAge=3600000
spring.datasource0.jdbcInterceptors=org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor(queryTimeout=1)

spring.datasource1.url=jdbc:mysql://localhost:3306/gallant1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&logger=com.mysql.jdbc.log.Slf4JLogger
spring.datasource1.username=gallant
spring.datasource1.password=gallant
spring.datasource1.driver-class-name=com.mysql.jdbc.Driver
spring.datasource1.timeBetweenEvictionRunsMillis=5000
spring.datasource1.removeAbandoned=true
spring.datasource1.removeAbandonedTimeout=5000
spring.datasource1.abandonWhenPercentageFull=0
spring.datasource1.logAbandoned=0
spring.datasource1.minEvictableIdleTimeMillis=0
spring.datasource1.testOnBorrow=false
spring.datasource1.testOnConnect=false
spring.datasource1.testOnReturn=false
spring.datasource1.testWhileIdle=true
spring.datasource1.validationQuery=SELECT 1
spring.datasource1.validationQueryTimeout=1
spring.datasource1.validationInterval=5000
spring.datasource1.logValidationErrors=true
spring.datasource1.maxActive=100
spring.datasource1.maxIdle=100
spring.datasource1.initialSize=10
spring.datasource1.maxWait=0
spring.datasource1.maxAge=3600000
spring.datasource1.jdbcInterceptors=org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor(queryTimeout=1)

1. 创建连接池数据源

		@ConfigurationProperties(prefix = "spring.datasource0")
    @Bean
    public DataSource dataSource0(){
        return DataSourceBuilder.create().build();
    }

    @ConfigurationProperties(prefix = "spring.datasource1")
    @Bean
    public DataSource dataSource1(){
        return DataSourceBuilder.create().build();
    }

ok大功告成，该案例是一个简单的示范，sharding-jdbc与tomcat-jdbc还有更加详细的配置可以去官网查看，具体的tomcat-jdbc实现原理可以参考博主的另一篇文章：https://blog.youkuaiyun.com/u010597819/article/details/86646085
项目源码地址：https://github.com/GallantKong/sharding-jdbc-pool