spring shardingSphere 多数据源

主要内容

Apache ShardingSphere

Apache ShardingSphere 是一套开源的分布式数据库解决方案组成的生态圈。Spring集成Apache ShardingSphere 是一套目前比较好的分库分表的方案。

Apache ShardingSphere JDBC 目前只支持同构数据库，本文通用自定义数据源实现异构数据库（spring boot + jpa + oracle + mysql）的分库分表。

版本：

spring boot V2.6.1

Apache ShardingSphere V5.1.0

更新历史

无

Apache ShardingSphere JDBC 目前只支持同构数据库, 可通用自定义数据源实现异构数据库（oracle + mysql）的分库分表。

以 oracle 数据库 作为 ShardingSphere JDBC 支持的主数据库。

主要步骤：

DataSource
EntityManagerFactory
EntityManager
TransactionManager
JdbcTemplate

源码

pom

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.6.1</version>
    <relativePath/> <!-- lookup parent from repository -->
</parent>

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
        <version>5.1.0</version>
    </dependency>
</dependencies>

Application

@SpringBootApplication(exclude = JtaAutoConfiguration.class) //使用ShardingSphere的分布式管理
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(XgfyBridgeApplication.class, args);
    }

}

DataSource

Apache ShardingSphere 的数据源作为主DataSource

@Configuration
public class DataSourceConfig {

    @Autowired
    @Qualifier("shardingSphereDataSource")
    private DataSource shardingSphereDataSource;  // 使用ShardingSphere 自动装载的 DataSource

    @Bean(name = "primaryDataSource")
    @Primary
    @Qualifier("primaryDataSource")
    public DataSource primaryDatasource() {
        return shardingSphereDataSource;
    }

}

多数据源配置

• 优先数据源

  @Configuration
  @EnableTransactionManagement
  @EnableJpaRepositories(
          entityManagerFactoryRef = "entityManagerFactoryPrimary",
          transactionManagerRef = "transactionManagerPrimary",
          basePackages = {"xxx.xxxx"}) //设置Repository所在位置
  public class PrimaryConfig {
  
      @Autowired
      private JpaProperties jpaProperties;
  
  
      @Autowired
      @Qualifier("primaryDataSource")
      private DataSource primaryDataSource;
  
  
      // 2) EntityManagerFactory
      @Primary
      @Bean(name = "entityManagerFactoryPrimary")
      public EntityManagerFactory entityManagerFactory() {
          HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
          vendorAdapter.setDatabase(Database.ORACLE);
          vendorAdapter.setGenerateDdl(false);
          vendorAdapter.setShowSql(true);
          LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
          factory.setJpaVendorAdapter(vendorAdapter);
          factory.setPersistenceUnitName("default");
          factory.setPackagesToScan("xxxxxxxxx.entity");
          factory.setDataSource(primaryDataSource);
          factory.setJpaPropertyMap(jpaProperties.getProperties());
          factory.afterPropertiesSet();
          return factory.getObject();
      }
  
      
      // 3） EntityManager
      @Bean(name = "entityManagerPrimary")
      @Primary
      public EntityManager entityManager(@Qualifier("entityManagerFactoryPrimary") EntityManagerFactory entityManagerFactory){
          return SharedEntityManagerCreator.createSharedEntityManager(entityManagerFactory);
      }
  
      
      // 4） TransactionManager
      @Bean(name = "transactionManagerPrimary")
      @Primary
      public PlatformTransactionManager transactionManager(@Qualifier("entityManagerFactoryPrimary") EntityManagerFactory entityManagerFactory){
          JpaTransactionManager txManager = new JpaTransactionManager();
          txManager.setEntityManagerFactory(entityManagerFactory);
          return txManager;
      }
  
      
      // 5） JdbcTemplate
      @Bean(name = "jdbcTemplateSharding")
      @Primary
      public JdbcTemplate jdbcTemplateSharding() {
          JdbcTemplate jdbcTemplate = new JdbcTemplate();
          jdbcTemplate.setDataSource(primaryDataSource);
          return jdbcTemplate;
      }
  }
  

• 其他数据源

  @Configuration
  @EnableTransactionManagement
  @EnableJpaRepositories(
          entityManagerFactoryRef = "entityManagerFactoryYixing",
          transactionManagerRef = "transactionManagerYixing",
          basePackages = {"xx.xxxx"}) //设置Repository所在位置
  public class SecondConfig {
      @Autowired
      private JpaProperties jpaProperties;
  
  
      @Bean(name = "yixingDataSource")
      @ConfigurationProperties(prefix = "spring.datasource.yixing")
      public DataSource yixingDataSource() {
          return DataSourceBuilder.create().build();
      }
  
  
      @Bean(name = "entityManagerYixing")
      public EntityManager entityManager() {
          return entityManagerFactoryyixing().createEntityManager();
      }
  
  
  
      @Bean(name = "entityManagerFactoryYixing")
      public EntityManagerFactory entityManagerFactoryyixing() {
          HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
          vendorAdapter.setDatabase(Database.MYSQL);
          vendorAdapter.setGenerateDdl(false);
          vendorAdapter.setShowSql(true);
          LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
          factory.setJpaVendorAdapter(vendorAdapter);
          factory.setPersistenceUnitName("yixing");
          factory.setPackagesToScan("xxxx.entity");
          factory.setDataSource(yixingDataSource());
          factory.setJpaPropertyMap(jpaProperties.getProperties());
          factory.afterPropertiesSet();
          return factory.getObject();
      }
  
  
      @Bean(name = "transactionManageryixing")
      public PlatformTransactionManager transactionManageryixing(@Qualifier("entityManagerFactoryYixing") EntityManagerFactory entityManagerFactory){
          JpaTransactionManager txManager = new JpaTransactionManager();
          txManager.setEntityManagerFactory(entityManagerFactory);
          return txManager;
      }
  
      @Bean(name = "jdbcTemplateYixing")
      public JdbcTemplate jdbcTemplateYixing() {
          JdbcTemplate jdbcTemplate = new JdbcTemplate();
          jdbcTemplate.setDataSource(yixingDataSource());
          return jdbcTemplate;
      }
  }
  

yml文件

spring:
  thymeleaf:
    cache: false
  jpa:
    hibernate:
      ddl-auto: none
      database-platform: org.hibernate.dialect.Oracle12cDialect
    open-in-view: false
    show-sql: true

  datasource:
    yixing:
      database-platform: org.hibernate.dialect.MySQL8Dialect
      type: com.zaxxer.hikari.HikariDataSource
      jdbc-url: jdbc:mysql://192.168.1.x:3306/qz?serverTimezone=UTC&useSSL=false
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: "xxx"
      password: "xxx"
      hikari:
        maximumPoolSize: 50
        poolName: yixing_HikariPool

  shardingsphere:
    enabled: true
    datasource:
      names: ds-master, ds-dr
      ds-master:
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.x)(PORT = 1521) )  (CONNECT_DATA =(SERVICE_NAME = orcl)) )
        driver-class-name: oracle.jdbc.OracleDriver
        username: xxx
        password: xxx
        hikari:
          maximumPoolSize: 50
          poolName: master_HikariPool
      ds-dr:
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.x)(PORT = 1521) )  (CONNECT_DATA =(SERVICE_NAME = ORCL)) )
        driver-class-name: oracle.jdbc.OracleDriver
        username: "xxx"
        password: "xxx"
        hikari:
          maximumPoolSize: 50
          poolName: dr_HikariPool
    rules:
      sharding:
        # tables -----------------------------------------------------
        tables:
          t_acid_result_dr:
            actual-data-nodes: ds-master.t_acid_result_dr_$->{0..31}
            table-strategy:
              standard:
                sharding-column: id_card_hash
                sharding-algorithm-name: acid-inline
            key-generate-strategy:
              column: id
              key-generator-name: mysnowflake
          t_acid_result_yx:
            actual-data-nodes: ds-master.t_acid_result_yx_$->{0..31}
            table-strategy:
              standard:
                sharding-column: id_card_hash
                sharding-algorithm-name: acid-yx-inline
            key-generate-strategy:
              column: id
              key-generator-name: yxsnowflake
          t_machan_check_dr:
            actual-data-nodes: ds-master.t_machan_check_dr


        # key-generators ------------------------
        key-generators:
          mysnowflake:
            type: SNOWFLAKE
            props:
              worker-id: 11
          yxsnowflake:
            type: SNOWFLAKE
            props:
              worker-id: 12
        # sharding-algorithms ------------------------------------------------
        sharding-algorithms:
          acid-inline:
            type: INLINE
            props:
              algorithm-expression: t_acid_result_dr_$->{id_card_hash % 32}
          acid-yx-inline:
            type: INLINE
            props:
              algorithm-expression: t_acid_result_yx_$->{id_card_hash % 32}