sharding-jdbc多数据源配置

通过sharding-jdbc做分表这里就不多做介绍了，需要的可以看上一片文章

当项目中配置了多数据源，并且其中一个数据源需要使用sharding-jdbc分表时，配置如下

1. 导入shardingjdbc依赖

  <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>4.0.1</version>
 </dependency>

这里使用的是4.0.1的配置，和上篇的3.1.0差别不大，详细配置大伙可以上官网查阅。

2. 多数据源配置类

shardingsphere数据源初始化

package com.efuav.parkingapron.config;

import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.shardingsphere.api.config.sharding.KeyGeneratorConfiguration;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

@Configuration
@MapperScan(basePackages = "com.efuav.parkingapron.dao.db1", sqlSessionFactoryRef = "db1SqlSessionFactory")
public class DataSourceConfig1 {

    @Value("${spring.shardingsphere.datasource.efuavsystem.username}")
    private String userName;

    @Value("${spring.shardingsphere.datasource.efuavsystem.jdbc-url}")
    private String url;

    @Value("${spring.shardingsphere.datasource.efuavsystem.password}")
    private String userPwd;

    @Value("${spring.shardingsphere.datasource.names}")
    private String dataName;


//    @Primary // 表示这个数据源是默认数据源, 这个注解必须要加，因为不加的话spring将分不清楚那个为主数据源（默认数据源）
//    @Bean(name="db1DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db1") //读取application.yml中的配置参数映射成为一个对象
//    public DataSource getDb1DataSource(){
//
//        return DataSourceBuilder.create().build();
//    }

    @Bean(name = "db1DataSource")
    @Qualifier("db1DataSource")
    public DataSource deviceDataSource() throws SQLException {
        return getShardingDataSource();
    }

    @Primary
    @Bean("db1SqlSessionFactory")
    public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        // mapper的xml形式文件位置必须要配置，不然将报错：no statement （这种错误也可能是mapper的xml中，namespace与项目的路径不一致导致）
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/db1/*.xml"));
        return bean.getObject();
    }

    @Primary
    @Bean("db1SqlSessionTemplate")
    public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }


    DataSource getShardingDataSource() throws SQLException {
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        // 配置分表规则
        shardingRuleConfig.getTableRuleConfigs().add(getRealTableRuleConfiguration());
        shardingRuleConfig.getBindingTableGroups().add("ef_uav_realtimedata");

        Properties props = new Properties();
        // 配置shardingsphere是否打印日志
        props.setProperty("sql.show", "true");
        // 创建数据源
        return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, props);
    }

    // 返回ef_uav_realtimedata表的分表规则配置
    TableRuleConfiguration getRealTableRuleConfiguration() {
        // 指定数据库及表配置规则
        TableRuleConfiguration result = new TableRuleConfiguration("ef_uav_realtimedata", "efuavsystem.ef_uav_realtimedata$->{1..5}");
        // 指定分表字段及分表规则类  TaTable2Algorithm为自定义分表规则类
        result.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("uav_id", new UavPreciseShardingAlgorithm()));
        return result;
    }

    private Map<String, DataSource> createDataSourceMap() {
        Map<String, DataSource> result = new HashMap<>();
        HikariDataSource dbs = new HikariDataSource();
        dbs.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dbs.setJdbcUrl(url);
        dbs.setUsername(userName);
        dbs.setPassword(userPwd);
        result.put(dataName, dbs);
        return result;
    }
}

普通数据源初始化

package com.efuav.parkingapron.config;

import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.efuav.parkingapron.dao.db2", sqlSessionFactoryRef = "db2SqlSessionFactory")
public class DataSourceConfig2 {

    @Bean("db2DataSource")
//    @ConfigurationProperties(prefix = "spring.datasource.db2")
    @ConfigurationProperties(prefix = "spring.datasource.infrared")
    public DataSource getDb1DataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean("db2SqlSessionFactory")
    public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db2DataSource") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/db2/*.xml"));
        return bean.getObject();
    }

    @Bean("db2SqlSessionTemplate")
    public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

3. 分表自定义配置规则（上篇文章有解释）

package com.efuav.parkingapron.config;

import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import javax.annotation.PostConstruct;
import java.util.Collection;
//分表规则
@Slf4j
public class UavPreciseShardingAlgorithm implements PreciseShardingAlgorithm<String> {
    /**
     * 插入数据 改写表的名称
     * 查询 改写表的名称
     *
     * @param collection
     * @param preciseShardingValue
     * @return
     */
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
        //  UAV07JDE6E0020240
        String value = preciseShardingValue.getValue();
        int las = value.charAt(value.length() - 1);
        String tableName = "ef_uav_realtimedata" + ((las % 4)+1);
        log.info("<tableName{}>", tableName);
        return tableName;
    }
}

4.yml配置文件

spring:
  main:
    allow-bean-definition-overriding: true
  datasource:
    infrared:
      jdbc-url: jdbc:mysql://192.168.0.5:13306/infrared?characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true&allowPublicKeyRetrieval=true
      username: root  #用户名
      password: 123456 #密码
      driver-class-name: com.mysql.cj.jdbc.Driver  #数据库链接驱动
      type: com.zaxxer.hikari.HikariDataSource
    hikari.minimum-idle: 5
    hikari.maximum-pool-size: 15
    hikari.auto-commit: true
    hikari.idle-timeout: 30000
    hikari.pool-name: DatebookHikariCP
    # 不小于30s 否则会回到默认的1800秒, 用来设置一个connection在连接池中的存活时间，默认是1800000，即30分钟。如果设置为0，表示存活时间无限大。如果不等于0且小于30秒则会被重置回30分钟。
    hikari.max-lifetime: 28800000
    hikari.connection-timeout: 30000
    hikari.connection-test-query: SELECT 1
  shardingsphere:
    datasource:
      names: efuavsystem
      efuavsystem:
        jdbc-url: jdbc:mysql://127.0.0.1:3306/ef_uav_system?characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true&allowPublicKeyRetrieval=true
        username: root  #用户名
        password: 123456 #密码
        driver-class-name: com.mysql.cj.jdbc.Driver  #数据库链接驱动
        type: com.zaxxer.hikari.HikariDataSource
        # 水平拆分的数据库（表） 配置分库 + 分表策略 行表达式分片策略
#    sharding:
#      tables:
#        ef_uav_realtimedata:
#          actual-data-nodes: efuavsystem.ef_uav_realtimedata$->{1..5}
#          table-strategy:
#            standard:
#              precise-algorithm-class-name: com.efuav.parkingapron.config.UavPreciseShardingAlgorithm
#              sharding-column: uav_id
    # 打印执行的数据库
#    props:
#      sql:
#        show: true

这样就不会冲突，以上就是sharding-jdbc多数据源配置了。

记录....