springboot整合shardingjdbc与tomcatjdbc

た入场券 2024-02-19 19:14 62阅读 0赞

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

### springboot配置 ###

1.  配置parent与start依赖

<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.19.RELEASE</version>
        <relativePath/> 
      </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.  依赖配置

<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>
        
        <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.csdn.net/u010597819/article/details/86646085][https_blog.csdn.net_u010597819_article_details_86646085]  
项目源码地址：[https://github.com/GallantKong/sharding-jdbc-pool][https_github.com_GallantKong_sharding-jdbc-pool]

[2.0.0.M3]: https://mvnrepository.com/artifact/io.shardingjdbc/sharding-jdbc-spring-boot-starter/2.0.0.M3
[https_blog.csdn.net_u010597819_article_details_86646085]: https://blog.csdn.net/u010597819/article/details/86646085
[https_github.com_GallantKong_sharding-jdbc-pool]: https://github.com/GallantKong/sharding-jdbc-pool