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

當(dāng)項(xiàng)目中配置了多數(shù)據(jù)源，并且其中一個數(shù)據(jù)源需要使用sharding-jdbc分表時，配置如下

1. 導(dǎo)入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差別不大，詳細(xì)配置大伙可以上官網(wǎng)查閱。

2. 多數(shù)據(jù)源配置類

shardingsphere數(shù)據(jù)源初始化

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 // 表示這個數(shù)據(jù)源是默認(rèn)數(shù)據(jù)源, 這個注解必須要加，因?yàn)椴患拥脑抯pring將分不清楚那個為主數(shù)據(jù)源（默認(rèn)數(shù)據(jù)源）
//    @Bean(name="db1DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db1") //讀取application.yml中的配置參數(shù)映射成為一個對象
//    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形式文件位置必須要配置，不然將報(bào)錯：no statement （這種錯誤也可能是mapper的xml中，namespace與項(xiàng)目的路徑不一致導(dǎo)致）
        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();
        // 配置分表規(guī)則
        shardingRuleConfig.getTableRuleConfigs().add(getRealTableRuleConfiguration());
        shardingRuleConfig.getBindingTableGroups().add("ef_uav_realtimedata");

        Properties props = new Properties();
        // 配置shardingsphere是否打印日志
        props.setProperty("sql.show", "true");
        // 創(chuàng)建數(shù)據(jù)源
        return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, props);
    }

    // 返回ef_uav_realtimedata表的分表規(guī)則配置
    TableRuleConfiguration getRealTableRuleConfiguration() {
        // 指定數(shù)據(jù)庫及表配置規(guī)則
        TableRuleConfiguration result = new TableRuleConfiguration("ef_uav_realtimedata", "efuavsystem.ef_uav_realtimedata$->{1..5}");
        // 指定分表字段及分表規(guī)則類  TaTable2Algorithm為自定義分表規(guī)則類
        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;
    }
}

普通數(shù)據(jù)源初始化

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. 分表自定義配置規(guī)則（上篇文章有解釋）

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;
//分表規(guī)則
@Slf4j
public class UavPreciseShardingAlgorithm implements PreciseShardingAlgorithm<String> {
    /**
     * 插入數(shù)據(jù) 改寫表的名稱
     * 查詢 改寫表的名稱
     *
     * @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  #數(shù)據(jù)庫鏈接驅(qū)動
      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 否則會回到默認(rèn)的1800秒, 用來設(shè)置一個connection在連接池中的存活時間，默認(rèn)是1800000，即30分鐘。如果設(shè)置為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  #數(shù)據(jù)庫鏈接驅(qū)動
        type: com.zaxxer.hikari.HikariDataSource
        # 水平拆分的數(shù)據(jù)庫（表） 配置分庫 + 分表策略 行表達(dá)式分片策略
#    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
    # 打印執(zhí)行的數(shù)據(jù)庫
#    props:
#      sql:
#        show: true

這樣就不會沖突，以上就是sharding-jdbc多數(shù)據(jù)源配置了。

記錄....文章來源地址http://www.zghlxwxcb.cn/news/detail-624651.html