多数据源与分库分表

已于 2023-11-27 11:26:34 修改
阅读量327 收藏
点赞数 1
分类专栏： Java 文章标签： spring mysql java
于 2021-09-01 15:51:47 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/a123638/article/details/120041759
版权

一、自定义多数据源方式

多数据源复制配置类即可。

1.1 Java配置类
package com.xx.wholesale.merchant.config.datasource;


import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.zaxxer.hikari.HikariDataSource;
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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.DefaultResourceLoader;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.io.FileNotFoundException;

@Configuration
@MapperScan(value = "com.xx.wholesale.merchant" + MerchantDataSourceConfig.DATA_SOURCE_SUB_NAME + ".dao",
        sqlSessionFactoryRef = MerchantDataSourceConfig.DATA_SOURCE_SUB_NAME + "SqlSessionFactory",
        sqlSessionTemplateRef = MerchantDataSourceConfig.DATA_SOURCE_SUB_NAME + "SqlSessionTemplate"
)
public class MerchantDataSourceConfig {

    static final String DATA_SOURCE_SUB_NAME = "merchant";

    @Bean(value = DATA_SOURCE_SUB_NAME + "DataSource")
    @ConfigurationProperties(prefix = "spring.datasource." + DATA_SOURCE_SUB_NAME)
    public HikariDataSource dataSource() {
        return new HikariDataSource();
    }


    @Bean(value = DATA_SOURCE_SUB_NAME + "SqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier(value = DATA_SOURCE_SUB_NAME + "DataSource") DataSource dataSource) throws Exception {
        DefaultResourceLoader resourceLoader = new DefaultResourceLoader();
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        MybatisSqlSessionFactoryBean factory = new MybatisSqlSessionFactoryBean();
        factory.setDataSource(dataSource);
        factory.setConfigLocation(resourceLoader.getResource("classpath:mybatis-config.xml"));
        Resource[] resources = null;
        try {
            resources = resolver.getResources("classpath:mapper/" + DATA_SOURCE_SUB_NAME + "/*.xml");
        } catch (FileNotFoundException e) {
            resources = new Resource[0];
        }
        factory.setMapperLocations(resources);
        MybatisPlusInterceptor paginationInterceptor = new MybatisPlusInterceptor();
        paginationInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        factory.setPlugins(paginationInterceptor);
        return factory.getObject();
    }

    @Bean(value = DATA_SOURCE_SUB_NAME + "SqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier(value = DATA_SOURCE_SUB_NAME + "SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        SqlSessionTemplate sqlSessionTemplate = new SqlSessionTemplate(sqlSessionFactory);
        return sqlSessionTemplate;
    }

    @Bean(value = DATA_SOURCE_SUB_NAME + "TransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier(value = DATA_SOURCE_SUB_NAME + "DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}
2.2 配置

application.yml

spring:
  datasource:
    merchant:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://10.30.20.205:6033/le_shou?zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=true&useAffectedRows=true&serverTimezone=Asia/Shanghai
      username: root
      password: 123456
      # 连接池最大连接数，默认是10
      maximum-pool-size: 30
      minimum-idle: 2
      max-lifetime: 7200000
      # 空闲连接存活最大时间，默认600000（10分钟）
      idle-timeout: 300000
      validation-timeout: 1000
      # 等待连接池分配连接的最大时长（毫秒），超过这个时长还没可用的连接则发生SQLException， 缺省:30秒
      connection-timeout: 3000
      connection-test-query: select 1
      auto-commit: true

二、结合Sharding-JDBC做多数据源

基础描述
  • springboot: 2.7.x
  • shardingsphere-jdbc-core: 5.4.x
pom.xml配置
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.17</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.xx.wholesale.merchant</groupId>
    <artifactId>wholesale-merchant</artifactId>
    <version>1.0.0-SNAPSHOT</version>
    <name>wholesale-merchant</name>
    <description>wholesale-merchant</description>
    <properties>
        <java.version>1.8</java.version>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <mysql-connector-java.version>8.0.17</mysql-connector-java.version>
        <mybatis-spring-boot-starter.version>2.3.1</mybatis-spring-boot-starter.version>
        <mybatis-plus-boot-starter.version>3.5.4.1</mybatis-plus-boot-starter.version>
        <shardingsphere-jdbc-core.version>5.4.1</shardingsphere-jdbc-core.version>
        <snakeyaml.version>1.33</snakeyaml.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-logging</artifactId>
            <exclusions>
                <exclusion>
                    <groupId>*</groupId>
                    <artifactId>*</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <!-- log4j2 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-log4j2</artifactId>
        </dependency>

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

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core</artifactId>
            <version>${shardingsphere-jdbc-core.version}</version>
            <exclusions>
                <exclusion>
                    <groupId>ch.qos.logback</groupId>
                    <artifactId>logback-classic</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <!-- MySQL Connector/J -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql-connector-java.version}</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis-spring-boot-starter.version}</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis-plus-boot-starter.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>
application.yml相关配置
server:
  port: 8899
  tomcat:
    basedir: ./
    uri-encoding: UTF-8
    max-threads: 1000
    min-spare-threads: 30
    accesslog:
      enabled: true
      directory: ${logging.file.path}
      pattern: "%h %l %u %t %r %s %b %D"

spring:
  application:
    name: wholesale-merchant
  profiles:
    include: jdbc

logging:
  config: classpath:log4j2_config.xml
  file:
    name: ${spring.application.name}
    path: logs

mybatis-plus:
  global-config:
    db-config:
      id-type: auto
      table-underline: true

application-jdbc.yml配置

spring:
  datasource:
    bdata:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://127.0.0.1:3306/bus_data?zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&useAffectedRows=true&serverTimezone=Asia/Shanghai
      username: root
      password: 123456
      # 连接池最大连接数，默认是10
      maximum-pool-size: 30
      minimum-idle: 2
      max-lifetime: 7200000
      # 空闲连接存活最大时间，默认600000（10分钟）
      idle-timeout: 300000
      validation-timeout: 1000
      # 等待连接池分配连接的最大时长（毫秒），超过这个时长还没可用的连接则发生SQLException， 缺省:30秒
      connection-timeout: 3000
      connection-test-query: select 1
      auto-commit: true
  shardingsphere:
    busorder:
      mode:
        type: standalone
        repository:
          type: JDBC
      data-sources:
        bus_order_0:
          driver-class-name: com.mysql.cj.jdbc.Driver
          # 此配置使用YamlDataSourceConfigurationSwapper.swapToDataSources()生成Map<String,DataSource>使用
          # dataSourceClassName: com.zaxxer.hikari.HikariDataSource
          jdbc-url: jdbc:mysql://127.0.0.1:3306/bus_order_0?zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&useAffectedRows=true&serverTimezone=Asia/Shanghai
          username: root
          password: 123456
          # 连接池最大连接数，默认是10
          maximum-pool-size: 30
          minimum-idle: 2
          max-lifetime: 7200000
          # 空闲连接存活最大时间，默认600000（10分钟）
          idle-timeout: 300000
          validation-timeout: 1000
          # 等待连接池分配连接的最大时长（毫秒），超过这个时长还没可用的连接则发生SQLException， 缺省:30秒
          connection-timeout: 3000
          connection-test-query: select 1
          auto-commit: true
        bus_order_1:
          driver-class-name: com.mysql.cj.jdbc.Driver
          # dataSourceClassName: com.zaxxer.hikari.HikariDataSource
          jdbc-url: jdbc:mysql://127.0.0.1:3306/bus_order_1?zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&useAffectedRows=true&serverTimezone=Asia/Shanghai
          username: root
          password: 123456
          # 连接池最大连接数，默认是10
          maximum-pool-size: 30
          minimum-idle: 2
          max-lifetime: 7200000
          # 空闲连接存活最大时间，默认600000（10分钟）
          idle-timeout: 300000
          validation-timeout: 1000
          # 等待连接池分配连接的最大时长（毫秒），超过这个时长还没可用的连接则发生SQLException， 缺省:30秒
          connection-timeout: 3000
          connection-test-query: select 1
          auto-commit: true
      rules:
#        - type: org.apache.shardingsphere.sharding.yaml.config.YamlShardingRuleConfiguration
        sharding:
          key-generators:
            snowflake:
              type: SNOWFLAKE
          sharding-algorithms:
            db_choose_bus_order_algorithm:
              type: INLINE
              props:
                algorithm-expression: bus_order_${user_id % 2}
            db_choose_account_algorithm:
              type: INLINE
              props:
                algorithm-expression: bus_order_${user_id % 2}
            tb_order_algorithm:
              type: ORDER_ALGORITHM
#              type: INLINE
#              props:
#                algorithm-expression: t_order_${order_id % 2}
          default-database-strategy:
            standard:
              # 分片列名称
              sharding-column: user_id
              # 分片算法名称
              sharding-algorithm-name: db_choose_bus_order_algorithm
          tables:
            t_order:
              actual-data-nodes: bus_order_${0..1}.t_order_${0..1}
              tableStrategy:
                standard:
                  shardingColumn: order_id
                  shardingAlgorithmName: tb_order_algorithm
            t_account:
              actual-data-nodes: bus_order_${0..1}.t_account
              databaseStrategy:
                shardingColumn: user_id
                shardingAlgorithmName: db_choose_account_algorithm
      # 展示修改以后的sql语句
      props:
        sql-show: true
DataSource相关配置
  • BusDataDataSourceConfig.java
package com.xx.wholesale.merchant.config.datasource;


import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.zaxxer.hikari.HikariDataSource;
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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.DefaultResourceLoader;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.io.FileNotFoundException;

@Configuration
@MapperScan(value = "com.xx.wholesale.merchant." + BusDataDataSourceConfig.DATA_SOURCE_SUB_NAME + ".dao",
        sqlSessionFactoryRef = BusDataDataSourceConfig.DATA_SOURCE_SUB_NAME + "SqlSessionFactory",
        sqlSessionTemplateRef = BusDataDataSourceConfig.DATA_SOURCE_SUB_NAME + "SqlSessionTemplate"
)
public class BusDataDataSourceConfig {

    static final String DATA_SOURCE_SUB_NAME = "busdata";

    @Bean(value = DATA_SOURCE_SUB_NAME + "DataSource")
    @ConfigurationProperties(prefix = "spring.datasource." + DATA_SOURCE_SUB_NAME)
    public HikariDataSource dataSource() {
        return new HikariDataSource();
    }


    @Bean(value = DATA_SOURCE_SUB_NAME + "SqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier(value = DATA_SOURCE_SUB_NAME + "DataSource") DataSource dataSource) throws Exception {
        DefaultResourceLoader resourceLoader = new DefaultResourceLoader();
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        MybatisSqlSessionFactoryBean factory = new MybatisSqlSessionFactoryBean();
        factory.setDataSource(dataSource);
        factory.setConfigLocation(resourceLoader.getResource("classpath:mybatis-config.xml"));
        Resource[] resources = null;
        try {
            resources = resolver.getResources("classpath:mapper/" + DATA_SOURCE_SUB_NAME + "/*.xml");
        } catch (FileNotFoundException e) {
            resources = new Resource[0];
        }
        factory.setMapperLocations(resources);
        MybatisPlusInterceptor paginationInterceptor = new MybatisPlusInterceptor();
        paginationInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        factory.setPlugins(paginationInterceptor);
        return factory.getObject();
    }

    @Bean(value = DATA_SOURCE_SUB_NAME + "SqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier(value = DATA_SOURCE_SUB_NAME + "SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        SqlSessionTemplate sqlSessionTemplate = new SqlSessionTemplate(sqlSessionFactory);
        return sqlSessionTemplate;
    }

    @Bean(value = DATA_SOURCE_SUB_NAME + "TransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier(value = DATA_SOURCE_SUB_NAME + "DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}
  • BusOrderNodeDataSourceConfig.java
package com.xx.wholesale.merchant.config.datasource;


import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.shardingsphere.driver.api.yaml.YamlJDBCConfiguration;
import org.apache.shardingsphere.driver.api.yaml.YamlShardingSphereDataSourceFactory;
import org.apache.shardingsphere.infra.yaml.config.swapper.resource.YamlDataSourceConfigurationSwapper;
import org.apache.shardingsphere.sharding.yaml.config.YamlShardingRuleConfiguration;
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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.DefaultResourceLoader;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.io.FileNotFoundException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.LinkedHashMap;
import java.util.Map;

@Configuration
@MapperScan(value = "com.xx.wholesale.merchant." + BusOrderNodeDataSourceConfig.DATA_SOURCE_SUB_NAME + ".dao",
        sqlSessionFactoryRef = BusOrderNodeDataSourceConfig.DATA_SOURCE_SUB_NAME + "SqlSessionFactory",
        sqlSessionTemplateRef = BusOrderNodeDataSourceConfig.DATA_SOURCE_SUB_NAME + "SqlSessionTemplate"
)
public class BusOrderNodeDataSourceConfig {

    static final String DATA_SOURCE_SUB_NAME = "busorder";


    @ConfigurationProperties(prefix = "spring.shardingsphere." + DATA_SOURCE_SUB_NAME + ".rules.sharding")
    @Bean(value = DATA_SOURCE_SUB_NAME + "YamlShardingRuleConfiguration")
    public YamlShardingRuleConfiguration yamlShardingRuleConfiguration() {
        return new YamlShardingRuleConfiguration();
    }

    @ConfigurationProperties(prefix = "spring.shardingsphere." + DATA_SOURCE_SUB_NAME)
    @Bean(value = DATA_SOURCE_SUB_NAME + "YamlJDBCConfiguration")
    public YamlJDBCConfiguration yamlJDBCConfiguration(@Qualifier(value = DATA_SOURCE_SUB_NAME + "YamlShardingRuleConfiguration") YamlShardingRuleConfiguration yamlShardingRuleConfiguration) {
        YamlJDBCConfiguration yamlJDBCConfiguration = new YamlJDBCConfiguration();
        yamlJDBCConfiguration.getRules().add(yamlShardingRuleConfiguration);
        return yamlJDBCConfiguration;
    }

    @ConfigurationProperties(prefix = "spring.shardingsphere." + DATA_SOURCE_SUB_NAME + ".data-sources")
    @Bean(value = DATA_SOURCE_SUB_NAME + "HikariDataSourceMap")
    public Map<String, HikariDataSource> dataSourcesMap() {
        return new LinkedHashMap<>();
    }

    @Bean(value = DATA_SOURCE_SUB_NAME + "DataSource")
    public DataSource dataSource(@Qualifier(value = DATA_SOURCE_SUB_NAME + "HikariDataSourceMap") Map<String, HikariDataSource> dataSourcesMap
            ,@Qualifier( DATA_SOURCE_SUB_NAME + "YamlJDBCConfiguration") YamlJDBCConfiguration yamlJDBCConfiguration) throws Exception {
        Class<YamlShardingSphereDataSourceFactory> calzz = YamlShardingSphereDataSourceFactory.class;
        Field field = calzz.getDeclaredField("DATA_SOURCE_SWAPPER");
        Method method = calzz.getDeclaredMethod("createDataSource", Map.class, YamlJDBCConfiguration.class);
        field.setAccessible(true);
        method.setAccessible(true);
        YamlDataSourceConfigurationSwapper dataSourceConfigurationSwapper = (YamlDataSourceConfigurationSwapper) field.get(null);
        // 如用官方生成DataSource，需注意类HikariDataSourcePoolMetaData中配置映射和StorageResourceUtils，特别是url和jdbcUrl
        //return (DataSource) method.invoke(null, dataSourceConfigurationSwapper.swapToDataSources(yamlJDBCConfiguration.getDataSources()), yamlJDBCConfiguration);
        return (DataSource) method.invoke(null, dataSourcesMap, yamlJDBCConfiguration);
    }


    @Bean(value = DATA_SOURCE_SUB_NAME + "SqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier(value = DATA_SOURCE_SUB_NAME + "DataSource") DataSource dataSource) throws Exception {
        DefaultResourceLoader resourceLoader = new DefaultResourceLoader();
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        MybatisSqlSessionFactoryBean factory = new MybatisSqlSessionFactoryBean();
        factory.setDataSource(dataSource);
        factory.setConfigLocation(resourceLoader.getResource("classpath:mybatis-config.xml"));
        Resource[] resources = null;
        try {
            resources = resolver.getResources("classpath:mapper/" + DATA_SOURCE_SUB_NAME + "/*.xml");
        } catch (FileNotFoundException e) {
            resources = new Resource[0];
        }
        factory.setMapperLocations(resources);
        MybatisPlusInterceptor paginationInterceptor = new MybatisPlusInterceptor();
        paginationInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        factory.setPlugins(paginationInterceptor);
        return factory.getObject();
    }

    @Bean(value = DATA_SOURCE_SUB_NAME + "SqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier(value = DATA_SOURCE_SUB_NAME + "SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        SqlSessionTemplate sqlSessionTemplate = new SqlSessionTemplate(sqlSessionFactory);
        return sqlSessionTemplate;
    }

    @Bean(value = DATA_SOURCE_SUB_NAME + "TransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier(value = DATA_SOURCE_SUB_NAME + "DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

}
  • OrderShardingAlgorithm.java
package com.xx.wholesale.merchant.config.util;

import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;

import java.util.Collection;

/**
 * 自定义订单分表策略实现
 */
public class OrderShardingAlgorithm implements StandardShardingAlgorithm<String> {
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
        return "t_order_" + Long.valueOf(preciseShardingValue.getValue()) % 2;
    }

    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
        return null;
    }

    @Override
    public String getType() {
        return "ORDER_ALGORITHM";
    }
}
  • SPI配置
    在src/main/resources/下创建文件夹META-INF/services，并创建文org.apache.shardingsphere.sharding.spi.ShardingAlgorithm，内容如下：
com.xx.wholesale.merchant.config.util.OrderShardingAlgorithm
示例
  • 数据库
CREATE DATABASE bus_data CHARACTER SET = utf8;
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `user_id` bigint(20) NOT NULL,
  `user_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
  `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `t_user` VALUES (1, 'jon', '2023-11-25 19:59:55');
INSERT INTO `t_user` VALUES (2, '第三眼的思绪', '2023-11-25 20:12:32');


CREATE DATABASE bus_order_0 CHARACTER SET = utf8;
DROP TABLE IF EXISTS `t_account`;
CREATE TABLE `t_account`  (
  `user_id` bigint(20) NOT NULL,
  `amount` bigint(20) NOT NULL DEFAULT 0 COMMENT '账户余额',
  `consume_amount` bigint(20) NOT NULL COMMENT '消费金额',
  `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户账户表' ROW_FORMAT = Dynamic;

INSERT INTO `t_account` VALUES (1, 10, 20, '2023-11-25 20:16:17');

DROP TABLE IF EXISTS `t_order_0`;
CREATE TABLE `t_order_0`  (
  `order_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `t_order_0` VALUES ('002000', 2, '2023-11-25 20:08:00');

DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE `t_order_1`  (
  `order_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `t_order_1` VALUES ('002001', 2, '2023-11-25 20:09:18');


CREATE DATABASE bus_order_1 CHARACTER SET = utf8;
DROP TABLE IF EXISTS `t_account`;
CREATE TABLE `t_account`  (
  `user_id` bigint(20) NOT NULL,
  `amount` bigint(20) NOT NULL DEFAULT 0 COMMENT '账户余额',
  `consume_amount` bigint(20) NOT NULL COMMENT '消费金额',
  `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户账户表' ROW_FORMAT = DYNAMIC;

INSERT INTO `t_account` VALUES (1, 20, 40, '2023-11-25 20:16:17');

DROP TABLE IF EXISTS `t_order_0`;
CREATE TABLE `t_order_0`  (
  `order_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

INSERT INTO `t_order_0` VALUES ('001000', 1, '2023-11-25 20:08:00');

DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE `t_order_1`  (
  `order_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

INSERT INTO `t_order_1` VALUES ('001001', 1, '2023-11-25 20:09:18');
  • 生成如下com.xx.wholesale.merchant.busorder.dao.OrderDao文件
package com.xx.wholesale.merchant.busorder.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface OrderDao extends BaseMapper {

    @Select(value = "select * from t_order where user_id=#{userId} and order_id=#{orderId} limit 1")
    public List<Map<String, Object>> queryOrder(@Param("userId") Long userId, @Param("orderId") String orderId);

    @Select(value = "select * from t_account where user_id=#{userId} limit 1")
    public Map<String, Object> queryAccount(@Param("userId") Long userId);
}
  • 生成如下com.xx.wholesale.merchant.busdata.dao.UserDao文件
package com.xx.wholesale.merchant.busdata.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Map;

@Mapper
public interface UserDao extends BaseMapper {

    @Select(value = "select * from t_user where user_id=#{userId} limit 1")
    public Map<String, Object> selectOne(@Param("userId") Long userId);
}
  • 测试类
package com.xx.wholesale.merchant;

import com.xx.wholesale.merchant.busdata.dao.UserDao;
import com.xx.wholesale.merchant.busorder.dao.OrderDao;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;
import java.util.Map;

@SpringBootTest
class WholesaleMerchantApplicationTests {

    @Autowired
    private OrderDao orderDao;
    @Autowired
    private UserDao bindDevicerDao;

    /**
     * 【分库分表】
     * 查询既分了库又分了表的订单
     */
    @Test
    void queryOrderShardingLoads() {
        List<Map<String, Object>> map = orderDao.queryOrder(1L, "001000");
        System.out.println(map);
    }

    /**
     * 【分库未分表】
     * 查询用户账户信息，分库但未分表
     */
    @Test
    void queryAccountShardingLoads() {
        Map<String, Object> map = orderDao.queryAccount(1L);
        System.out.println(map);
    }

    /**
     * 【未分库分表】
     * 查询用户基础信息
     */
    @Test
    void queryUserShardingLoads() {
        Map<String, Object> map = bindDevicerDao.selectOne(1L);
        System.out.println(map);
    }

}
第三眼的思绪
关注
专栏目录
分库分表，多数据源的切换
06-16
通过使用sharding-jdbc分库分表，以及多数据源的切换
数据库分库分表(sharding)系列(四) 多数据源的事务处理
nayao-net
07-27 448
系统经sharding改造之后，原来单一的数据库会演变成多个数据库，如何确保多数据源同时操作的原子性和一致性是不得不考虑的一个问题。总体上看，目前对于一个分布式系统的事务处理有三种方式：分布式事务、基于Best Efforts 1PC模式的事务以及事务补偿机制。我们下面对这三种处理方式一一进行分析。本文原文链接：http://blog.csdn.net/bluishglc/article/deta...
SpringBoot多数据源
最新发布
qq_46220373的博客
08-26 126
SpringBoot多数据源
多源分库分表实现
点点的博客
03-26 197
项目架构： 原理：采用哈希取余，然后定位存储或者查询哪张表 依赖： <?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https
分库分表番外：多数据源/动态数据源实现
admin522043032的博客
09-15 1125
比如在电商网站中可能会有这样的需求：根据用户所在城市不同，查询不同城市的商品数据。而在后台，这些不同城市的数据被分配在不同的数据库当中。很多人想当然的就觉得需要使用ShardingJDBC来实现多数据源管理。这种场景就是一个典型的多数据源切换的场景。但是我们仔细分析这样的场景，他跟分库分表其实并不太相同。ShardingJDBC固然可以使用Hint策略实现快速的数据库分库查询。
分库分表——多数据源切换以及读写分离
qq_39246466的博客
11-19 1107
多数据源实现： 读写分离
005 分库分表_多数据源切换读写分离实战
诸般世界
10-26 722
1.数据库层面Mysql（MariaDB），实现读写分离、主从切换、数据源切换： 首先实现读写分离，就意味着需要有两个数据源，当写操作的时候对主库（master）使用，当读操作的时候对从库（slave）使用。那么在启动数据库连接池的时候即要启动两个，在实际使用时可以在方法上加上自定义注解的形式来区分读写。 2.实现解析： （1）配置好两个druid数据源，然后要区分开两个数据源...
多数据源+数据库分库分表
11-08
分库分表采用sharding-jdbc 数据库连接池管理是alibaba的druid-spring-boot-starter 项目使用springboot搭建，junit测试，为了方便实现对数据库操作继承了mybatisplus，为了少些get、set 引入lombok
Mybaits-plus优雅的实现多数据源及分表
08-15
接下来，分库分表是一种常见的数据库水平扩展策略，目的是分散数据存储压力，提高查询效率。在MyBatis-Plus中，我们可以利用动态SQL实现对ID的取模运算，达到分表的效果。例如，我们可以创建一个自定义的拦截器，...
spring动态数据源+mybatis分库分表
03-08
6. **扩展性与可伸缩性**：通过动态数据源和分库分表，系统能够随着数据量的增长而平滑扩展，增加新的数据库实例而不影响现有业务。 7. **事务管理**：在分库分表环境下，跨库事务是一大挑战。Spring提供了分布式...
Mybatis动态数据源实现分库分表实战
大道至简，悟在天成。
03-09 1742
Mybatis动态数据源实现分库分表实战
三、SpringBoot 整合mybatis 多数据源以及分库分表，初级java面试题大全
m0_64867293的博客
12-07 229
MyBatisConfigTwo 类 @Configuration @MapperScan(basePackages = “com.quellan.zlflovemm.dao.mapper2”,sqlSessionFactoryRef = “sqlSessionFactory2”,sqlSessionTemplateRef = “sqlSessionTemplate2”) public class MyBatisConfigTwo { @Resource(name = “dbTwo”) DataSource
java分库分表如何动态配置多个数据源
不断奔跑的轮子
04-23 478
要实现通过传入不同的参数值来操作不同的数据库，可以使用Spring框架中的AbstractRoutingDataSource类。这个类继承自javax.sql.DataSource，并实现了Spring的DataSource接口。它可以根据设置的数据源路由key自动切换不同的数据源。
多数据源项目
Eagle_wuhan的专栏
07-29 997
Spring2.0.1以后的版本已经支持配置多数据源，并且可以在运行的时候动态加载不同的数据源。通过继承 AbstractRoutingDataSource就可以实现多数据源的动态转换。 思路：通过ThreadLocal保存每个数据源所对应的标志，AbstractRoutingDataSource在通过vdetermineCurrentLookupKey()获取对应数据源的键值的时候，直接从ThreadLocal获取当前线程所持有的数据源对应标志然后返回。而至于说什么情况下使用哪个数据源的问题，则由应
项目集成多个数据源
weixin_38738049的博客
05-16 250
项目目前情况是mysql查询，需要添加clickhouse查询。下面的问题就是如何切换数据源做查询了。这里可以使用切面 加注解的方式实现。
Spring Boot 多数据源项目搭建
qq_41001644的博客
01-07 227
一.项目基本结构 二.详细内容 1.项目配置文件（yml）： ps：springboot使用的是1.5.9版本，若使用2.x.x版本，项目地址：server:servlet:context-path: /xxxx 2.配置文件包（config） ...
在Spring boot项目中实现多数据源的方案
架构师修炼道路
02-21 715
定义一个拦截器，在请求时检查Controller类和方法上的注解，根据配置的值设置当前请求用什么数据源。在请求响应回去时清除当前线程绑定的数据，做一个资源的释放动作。在实际项目开发过程中，因为业务发展和技术实现的需要，我们会在项目中同时连接多个数据库，具体该怎么做呢？解决异步执行时上下文传递的问题。
行情多数据源对比项目
ddzyx的博客
03-06 454
问题： 去年我开发了行情多数据源对比项目，线上服务器已经运行1年多了，基本逻辑： 实时抓取多个数据源的行情数据， 对各个行情指标进行汇总计算，实时多数据源对比和计算逻辑， 对比后，把每支股票超过设定的指标偏差的指标数据(offset)数据进行计算/汇总 发邮件模块，即把每支股票的offset数据绘制成excel表格和附件，以邮件的方式每天定时给相关人发出去 这个项目已经上线运行1年多了，其中每天必看的多数据源对比结果邮件按照股票品类每天都会定时发送出来给相关责任人，可是最近突然接收不到美股的对比邮件了，赶紧
后台管理项目的多数据源方案
weixin_58482311的博客
01-03 1009
空闲连接时间过长，数据库就会自动把连接关闭，Druid为了防止从连接池中拿到被数据库关闭的连接，设置了这个参数，超过时间强行关闭连接。注意：如果设置的太小，当客户端连接多的情况下，就需要新创建数据库连接，是一个比较耗时的操作，可能导致客户端连接超时。设置太大会占用系统资源。4、saveW和saveR方法后面的业务发生异常，事务管理器回滚saveW的更新，saveR已提交，数据不一致。3、saveW和saveR方法中间的业务发生异常，事务管理器回滚saveW的更新，saveR未提交，数据一致。
若依框架 多数据源 分库分表
08-16
- *1* [Java如何实现分库分表](https://blog.csdn.net/Kevin_zhai/article/details/131416610)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值