美文网首页
Spring Data JPA 使用主从数据源

Spring Data JPA 使用主从数据源

作者: lz做过前端 | 来源:发表于2021-12-23 19:38 被阅读0次

    Mysql 配置主从复制

    参考：Mysql主从复制-半同步复制
    这里我配置了master主库，slave从库slave0和slave1

    创建 Spring boot工程

    maven pom

    <properties>
    <maven.compiler.source>8</maven.compiler.source>
    <maven.compiler.target>8</maven.compiler.target>
    <lombok.maven.plugin.encoding>UTF-8</lombok.maven.plugin.encoding>
    <lombok.version>1.18.20</lombok.version>
    <lombok.maven.plugin.version>1.18.20.0</lombok.maven.plugin.version>
</properties>

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.5.1</version>
    <relativePath/>
</parent>

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-aop</artifactId>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.25</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>${lombok.version}</version>
    </dependency>
</dependencies>

    application.yml

    spring:
  jpa:
    properties:
      hibernate:
        enable_lazy_load_no_trans: true
        show_sql: true
        use_sql_comments: true
        format_sql: true
    hibernate:
      ddl-auto: update
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
    database-platform: org.hibernate.dialect.MySQL8Dialect
    open-in-view: false

server:
  port: 9300

app:
  datasource:
    parameters: useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true
    username: &username root
    password: &password 123456
    driver-class-name: &driver-class-name com.mysql.cj.jdbc.Driver
    master: &default-db-config
      url: jdbc:mysql://localhost:3201/test?${app.datasource.parameters}
      username: *username
      password: *password
      driver-class-name: *driver-class-name
      configuration:
        maximum-pool-size: 30
    slave0:
      <<: *default-db-config
      url: jdbc:mysql://localhost:3202/test?${app.datasource.parameters}
    slave1:
      <<: *default-db-config
      url: jdbc:mysql://localhost:3203/test?${app.datasource.parameters}

    数据源相关配置

    DataSource

    import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;

/**
 * 多数据源配置
 */
@Configuration
public class DataSourceConfig {
    /**
     * master
     */
    @Bean
    @Primary
    @ConfigurationProperties(prefix = "app.datasource.master")
    public DataSourceProperties masterDataSourceProperties() {
        return new DataSourceProperties();
    }

    /**
     * slave0
     */
    @Bean("slave0DataSourceProperties")
    @ConfigurationProperties(prefix = "app.datasource.slave0")
    public DataSourceProperties slave0DataSourceProperties() {
        return new DataSourceProperties();
    }

    /**
     * slave1
     */
    @Bean("slave1DataSourceProperties")
    @ConfigurationProperties(prefix = "app.datasource.slave1")
    public DataSourceProperties slave1DataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    @ConfigurationProperties(prefix = "app.datasource.master.configuration")
    public DataSource masterDataSource(DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }

    @Bean("slave0DataSource")
    @ConfigurationProperties(prefix = "app.datasource.slave0.configuration")
    public DataSource slave0DataSource(@Qualifier("slave0DataSourceProperties") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }

    @Bean("slave1DataSource")
    @ConfigurationProperties(prefix = "app.datasource.slave1.configuration")
    public DataSource slave1DataSource(@Qualifier("slave1DataSourceProperties") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }
}

    数据源切换思路

    动态切换数据源时序
    • 利用ThreadLocal作为单个请求(每个请求单独一个线程)的全局容器连接Service方法控制使用哪个数据源和EntityManager使用的数据源，这样EntityManager使用的数据源就是在Service方法上要求的数据源，即可做到写Service方法时决定使用哪个数据源
    • 利用org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource::determineCurrentLookupKey()暴露获取DataSource的逻辑
    • 在service方法上通过配置Annotation，告诉EntityManager使用哪个数据源
      • 自定义Annotation
      • 利用Spring AOP识别Annotation，织入设置数据源逻辑

    数据源切换配置

    • Annotation
    import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * 主数据源
 */
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface Master {
}

    import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * 从数据源
 */
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface Slave {
}
    • 多数据源 key
    public enum DBInstanceEnum {
    MASTER, SLAVE0, SLAVE1;
}
    • 数据源容器
    import lombok.extern.slf4j.Slf4j;
import lombok.val;

import java.util.concurrent.atomic.AtomicInteger;

/**
 * 数据源选择器
 */
@Slf4j
public final class DBContextHolder {

    private static final ThreadLocal<DBInstanceEnum> contextHolder = ThreadLocal.withInitial(() -> DBInstanceEnum.MASTER);

    private static final AtomicInteger router = new AtomicInteger(-1);

    public static void switchToMaster() {
        contextHolder.set(DBInstanceEnum.MASTER);
        log.info("switch to master db");
    }

    public static void switchToSlave() {
        // 1.3改进：支持多个从库的负载均衡
        val next = router.incrementAndGet();
        val index = next % 2;
        if (index == 0) {
            contextHolder.set(DBInstanceEnum.SLAVE0);
            log.info("switch to slave0 db");
        } else {
            contextHolder.set(DBInstanceEnum.SLAVE1);
            log.info("switch to slave1 db");
        }
        if (next > 9999) {
            router.set(-1);
        }
    }

    public static DBInstanceEnum get() {
        val db = contextHolder.get();
        log.info("get db{} from contextHolder", db);
        return db;
    }
}
    • 多数据源AOP配置
    import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.annotation.*;
import org.springframework.stereotype.Component;

/**
 * 多数据源切换AOP
 */
@Aspect
@Component
@Slf4j
public class DataSourceSwitchAspect {

    /**
     * 切换为 master 数据源织入点
     */
    @Pointcut("@annotation(cc.gegee.study.week7.db.tags.Master) ")
    public void switchToMasterPointcut() {

    }

    /**
     * 切换为 slave 数据源织入点
     */
    @Pointcut("@annotation(cc.gegee.study.week7.db.tags.Slave) ")
    public void switchToSlavePointcut() {

    }

    @Before("switchToSlavePointcut()")
    public void readBefore() {
        DBContextHolder.switchToSlave();
    }

    /**
     * slave 结束后切回到主数据源
     */
    @AfterReturning("switchToSlavePointcut()")
    public void readAfter() {
        DBContextHolder.switchToMaster();
        log.info("after use slave db, set to master db");
    }

    @Before("switchToMasterPointcut()")
    public void writeBefore() {
        DBContextHolder.switchToMaster();
    }
}

    动态数据源配置

    将上面配置的数据源注入AbstractRoutingDataSource，并将数据源容器获取接入

    import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Component
public class RoutingDataSource extends AbstractRoutingDataSource {

    public RoutingDataSource(DataSource masterDataSource, @Qualifier("slave0DataSource") DataSource slave0DataSource, @Qualifier("slave1DataSource") DataSource slave1DataSource) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DBInstanceEnum.MASTER, masterDataSource);
        targetDataSources.put(DBInstanceEnum.SLAVE0, slave0DataSource);
        targetDataSources.put(DBInstanceEnum.SLAVE1, slave1DataSource);
        this.setDefaultTargetDataSource(masterDataSource);
        this.setTargetDataSources(targetDataSources);
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return DBContextHolder.get();
    }
}

    自定义 JPA 的 EntityManager

    将动态数据源注入EntityManager

    import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.SharedEntityManagerCreator;

import javax.persistence.EntityManager;
import java.util.Objects;

import static cc.gegee.study.week7.Application.BASE_PACKAGE;
import static cc.gegee.study.week7.db.JpaEntityManagerMaster.ENTITY_MANAGER_FACTORY_BEAN;

/**
 * 自定义 JPA 的 EntityManager
 */
@Configuration
@EnableJpaRepositories(basePackages = {BASE_PACKAGE}, entityManagerFactoryRef = ENTITY_MANAGER_FACTORY_BEAN)
public class JpaEntityManagerMaster {

    public final static String ENTITY_MANAGER_FACTORY_BEAN = "masterEntityManagerFactoryBean";

    private final RoutingDataSource dataSource;

    private final EntityManagerFactoryBuilder builder;

    private final HibernateConfiguration hibernateConfiguration;

    public JpaEntityManagerMaster(RoutingDataSource dataSource, EntityManagerFactoryBuilder builder, HibernateConfiguration hibernateConfiguration) {
        this.dataSource = dataSource;
        this.builder = builder;
        this.hibernateConfiguration = hibernateConfiguration;
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean masterEntityManagerFactoryBean() {
        return builder
                .dataSource(dataSource)
                .properties(hibernateConfiguration.getVendorProperties(dataSource))
                .packages(BASE_PACKAGE)
                .persistenceUnit("persistenceUnitMaster")
                .build();
    }

    @Bean
    public EntityManager masterEntityManager() {
        return SharedEntityManagerCreator.createSharedEntityManager(Objects.requireNonNull(masterEntityManagerFactoryBean().getObject()));
    }
}

    测试

    service添加CRUD方法

        @Master
    @Override
    public void add(DeviceModel model) {
        val device = Device.builder()
                .serialNumber(model.getSerialNumber())
                .deviceCategoryName(model.getDeviceCategoryName())
                .build();
        deviceRepository.save(device);
    }

    @Master
    @Override
    public void edit(UUID id, DeviceModel model) {
        val device = deviceRepository.findById(id).orElseThrow(() -> new IllegalArgumentException("invalid id"));
        device.setSerialNumber(model.getSerialNumber());
        device.setDeviceCategoryName(model.getDeviceCategoryName());
        deviceRepository.save(device);
    }

    @Slave
    @Override
    public DeviceDto get(UUID id) {
        val device = deviceRepository.findById(id).orElseThrow(() -> new IllegalArgumentException("invalid id"));
        return DeviceDto.builder()
                .serialNumber(device.getSerialNumber())
                .deviceCategoryName(device.getDeviceCategoryName())
                .build();
    }

    @Slave
    @Override
    public Page<DeviceDto> getPage(Pageable pageable) {
        return deviceRepository.findAll(pageable).map(x -> DeviceDto.builder()
                .serialNumber(x.getSerialNumber())
                .deviceCategoryName(x.getDeviceCategoryName())
                .build());
    }

    配置好controller后调用，可以看到如下日志

    2021-12-23 09:11:38.926  INFO 8678 --- [nio-9300-exec-1] cc.gegee.study.week7.db.DBContextHolder  : switch to master db
2021-12-23 09:11:48.976  INFO 8678 --- [nio-9300-exec-3] cc.gegee.study.week7.db.DBContextHolder  : switch to slave1 db
2021-12-23 09:11:48.977  INFO 8678 --- [nio-9300-exec-3] cc.gegee.study.week7.db.DBContextHolder  : get dbSLAVE1 from contextHolder

    github地址

    github地址

    相关文章

      网友评论

          本文标题：Spring Data JPA 使用主从数据源

          本文链接：https://www.haomeiwen.com/subject/mppfqrtx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|Spring Data JPA 使用主从数据源|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！