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
    }