Sam's Notes | Sam Blog

梦想还是要有的，万一实现了呢

0%

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 (javax.sql.DataSource)
  • EntityManagerFactory (javax.persistence.EntityManagerFactory)
  • EntityManager ( javax.persistence.EntityManager)
  • TransactionManager (org.springframework.transaction.TransactionManager)
  • JdbcTemplate (org.springframework.jdbc.core.JdbcTemplate)

源码

pom

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<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

1
2
3
4
5
6
7
8
@SpringBootApplication(exclude = JtaAutoConfiguration.class) //使用ShardingSphere的分布式管理
public class Application {

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

}

DataSource

Apache ShardingSphere 的数据源作为主DataSource

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@Configuration
public class DataSourceConfig {

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

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

}

多数据源配置

  • 优先数据源

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64

    @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;
        }
    }

  • 其他数据源

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57

    @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文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
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}

使用

  • 主数据源
    和普通jpa 一样， 无区别

  • 其他数据源

    1
    2
    3
    4
    5
    @Service
    @Transactional(rollbackFor = Exception.class, transactionManager = "transactionManageryixing")  //特别指定 transactionManager
    public class YxUploadService {
    xxxx
    }