oracle读写分离多数据源

1、配置数据源

package com.netintech.core.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties;
import com.alibaba.druid.util.Utils;
import com.netintech.common.core.utils.SpringUtils;
import com.netintech.core.aspectj.lang.enums.DataSourceType;
import com.netintech.core.config.properties.DruidProperties;
import com.netintech.core.datasource.DynamicDataSource;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.servlet.*;
import javax.sql.DataSource;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

/**
 * druid 配置多数据源
 * 
 * @author admin
 */
@Configuration
public class DruidConfig
{
    @Bean
    @ConfigurationProperties("spring.datasource.druid.master")
    public DataSource masterDataSource(DruidProperties druidProperties)
    {
        DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
        return druidProperties.dataSource(dataSource);
    }

    @Bean
    @ConfigurationProperties("spring.datasource.druid.slave")
    @ConditionalOnProperty(prefix = "spring.datasource.druid.slave", name = "enabled", havingValue = "true")
    public DataSource slaveDataSource(DruidProperties druidProperties)
    {
        DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
        return druidProperties.dataSource(dataSource);
    }

    @Bean(name = "slave136DataSource")
    @ConfigurationProperties("spring.datasource.druid.slave136")
    @ConditionalOnProperty(prefix = "spring.datasource.druid.slave136", name = "enabled", havingValue = "true")
    public DataSource slave136DataSource(DruidProperties druidProperties)
    {
        DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
        return druidProperties.dataSource(dataSource);
    }

    @Bean(name = "slave110DataSource")
    @ConfigurationProperties("spring.datasource.druid.slave110")
    @ConditionalOnProperty(prefix = "spring.datasource.druid.slave110", name = "enabled", havingValue = "true")
    public DataSource slave110DataSource(DruidProperties druidProperties)
    {
        DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
        return druidProperties.dataSource(dataSource);
    }

    @Bean(name = "slaveGajDataSource")
    @ConfigurationProperties("spring.datasource.druid.slavegaj")
    @ConditionalOnProperty(prefix = "spring.datasource.druid.slavegaj", name = "enabled", havingValue = "true")
    public DataSource slaveGajDataSource(DruidProperties druidProperties) {
        DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
        return druidProperties.dataSource(dataSource);
    }

    @Bean(name = "slaveZhzx36DataSource")
    @ConfigurationProperties("spring.datasource.druid.slavezhzx36")
    @ConditionalOnProperty(prefix = "spring.datasource.druid.slavezhzx36", name = "enabled", havingValue = "true")
    public DataSource slaveZhzx36DataSource(DruidProperties druidProperties) {
        DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
        return druidProperties.dataSource(dataSource);
    }

    @Bean(name = "slaveQzdjDataSource")
    @ConfigurationProperties("spring.datasource.druid.slaveqzdj")
    @ConditionalOnProperty(prefix = "spring.datasource.druid.slaveqzdj", name = "enabled", havingValue = "true")
    public DataSource slaveQzdjDataSource(DruidProperties druidProperties) {
        DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
        return druidProperties.dataSource(dataSource);
    }

    @Bean(name = "dynamicDataSource")
    @Primary
    public DynamicDataSource dataSource(DataSource masterDataSource)
    {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceType.MASTER.name(), masterDataSource);
        setDataSource(targetDataSources, DataSourceType.SLAVE.name(), "slaveDataSource");
        setDataSource(targetDataSources, DataSourceType.SLAVE136.name(), "slave136DataSource");
        setDataSource(targetDataSources, DataSourceType.SLAVE110.name(), "slave110DataSource");
        setDataSource(targetDataSources, DataSourceType.SLAVEGAJ.name(), "slaveGajDataSource");
        setDataSource(targetDataSources, DataSourceType.SLAVEZHZX36.name(), "slaveZhzx36DataSource");
        setDataSource(targetDataSources, DataSourceType.SLAVEQZDJ.name(), "slaveQzdjDataSource");
        return new DynamicDataSource(masterDataSource, targetDataSources);
    }

    /**
     * 设置数据源
     * 
     * @param targetDataSources 备选数据源集合
     * @param sourceName 数据源名称
     * @param beanName bean名称
     */
    public void setDataSource(Map<Object, Object> targetDataSources, String sourceName, String beanName)
    {
        try
        {
            DataSource dataSource = SpringUtils.getBean(beanName);
            targetDataSources.put(sourceName, dataSource);
        }
        catch (Exception e)
        {
        }
    }

    /**
     * 去除监控页面底部的广告
     */
    @SuppressWarnings({ "rawtypes", "unchecked" })
    @Bean
    @ConditionalOnProperty(name = "spring.datasource.druid.statViewServlet.enabled", havingValue = "true")
    public FilterRegistrationBean removeDruidFilterRegistrationBean(DruidStatProperties properties)
    {
        // 获取web监控页面的参数
        DruidStatProperties.StatViewServlet config = properties.getStatViewServlet();
        // 提取common.js的配置路径
        String pattern = config.getUrlPattern() != null ? config.getUrlPattern() : "/druid/*";
        String commonJsPattern = pattern.replaceAll("\\*", "js/common.js");
        final String filePath = "support/http/resources/js/common.js";
        // 创建filter进行过滤
        Filter filter = new Filter()
        {
            @Override
            public void init(javax.servlet.FilterConfig filterConfig) throws ServletException
            {
            }

            @Override
            public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
                    throws IOException, ServletException
            {
                chain.doFilter(request, response);
                // 重置缓冲区，响应头不会被重置
                response.resetBuffer();
                // 获取common.js
                String text = Utils.readFromResource(filePath);
                // 正则替换banner, 除去底部的广告信息
                text = text.replaceAll("<a.*?banner\"></a><br/>", "");
                text = text.replaceAll("powered.*?shrek.wang</a>", "");
                response.getWriter().write(text);
            }

            @Override
            public void destroy()
            {
            }
        };
        FilterRegistrationBean registrationBean = new FilterRegistrationBean();
        registrationBean.setFilter(filter);
        registrationBean.addUrlPatterns(commonJsPattern);
        return registrationBean;
    }
}

2、创建枚举类

package com.netintech.core.aspectj.lang.enums;

/**
 * 数据源
 * 
 * @author admin
 */
public enum DataSourceType
{
    /**
     * 主库
     */
    MASTER,

    /**
     * 从库
     */
    SLAVE,

    /**
     * 从库(136 网格在线)
     */
    SLAVE136,
    /**
     * 从库(对接110)
     */
    SLAVE110,

    /**
     * 公安局
     */
    SLAVEGAJ,

    /**
     * 从库(指挥中心)
     */
    SLAVEZHZX36,

    /**
     * 从库(中间表区镇对接)
     */
    SLAVEQZDJ

}

3、创建多数据源切换注解

package com.netintech.core.aspectj.lang.annotation;

import com.netintech.core.aspectj.lang.enums.DataSourceType;

import java.lang.annotation.*;

/**
 * 自定义多数据源切换注解
 * 
 * @author admin
 */
@Target({ ElementType.METHOD, ElementType.TYPE })
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
public @interface DataSource
{
    /**
     * 切换数据源名称
     */
    public DataSourceType value() default DataSourceType.MASTER;
}

4、数据源注解配置

package com.netintech.core.aspectj;

import com.netintech.common.core.utils.StringUtils;
import com.netintech.core.aspectj.lang.annotation.DataSource;
import com.netintech.core.datasource.DynamicDataSourceContextHolder;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

import java.lang.reflect.Method;

/**
 * 多数据源处理
 * 
 * @author admin
 */
@Aspect
@Order(1)
@Component
public class DataSourceAspect
{
    protected Logger logger = LoggerFactory.getLogger(getClass());

    @Pointcut("@annotation(com.netintech.core.aspectj.lang.annotation.DataSource)"
            + "|| @within(com.netintech.core.aspectj.lang.annotation.DataSource)")
    public void dsPointCut()
    {

    }

    @Around("dsPointCut()")
    public Object around(ProceedingJoinPoint point) throws Throwable
    {
        DataSource dataSource = getDataSource(point);

        if (StringUtils.isNotNull(dataSource))
        {
            DynamicDataSourceContextHolder.setDataSourceType(dataSource.value().name());
        }

        try
        {
            return point.proceed();
        }
        finally
        {
            // 销毁数据源 在执行方法之后
            DynamicDataSourceContextHolder.clearDataSourceType();
        }
    }

    /**
     * 获取需要切换的数据源
     */
    public DataSource getDataSource(ProceedingJoinPoint point)
    {
        MethodSignature signature = (MethodSignature) point.getSignature();
        Method method = signature.getMethod();
        DataSource dataSource = method.getAnnotation(DataSource.class);

        Class<? extends Object> targetClass = point.getTarget().getClass();
        DataSource targetDataSource = targetClass.getAnnotation(DataSource.class);
        if (StringUtils.isNotNull(dataSource))
        {
            return dataSource;
        }
        else
        {
            return targetDataSource;
        }
    }
}

5、数据库配置

spring:
  redis:
    host: 172.6.1.1
    port: 6379
    password: Netma213123rch@2022#
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: oracle.jdbc.OracleDriver
    druid:
      # 主库数据源
      master: 
        url: jdbc:oracle:thin:@1.1.1.3:1521/zhzx
        username: KSSHZL_GD
        password: Netma1231rch_2022#
      # 从库数据源
      slave:
        # 从数据源开关/默认关闭
        enabled: true
        url: jdbc:oracle:thin:@17.1.1.3:1521/zhzx
        username: KSSHZL_OPERENTION
        password: Netm1231arch_2022#
      # 136
      slave136:
        enabled: true
        url: jdbc:oracle:thin:@17.1.9.1:1521:orcl
        username: kssm_141llt
        password: kssm_lQWEQlt
      slave110:
        enabled: true
        url: jdbc:oracle:thin:@2.3.3.1:7110:dsdb
        username: hxeQEQcs
        password: dsdSXADb#2021
      # gaj
      slavegaj:
        enabled: true
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://2.36.25.162:3306/ks_shzlxdhzhzhzx_push?useUnicode=true&characterEncoding=utf-8&character_set_client=utf8mb4&useSSL=false&serverTimezone=Asia/Shanghai
        username: ks_shzlxdhzhzQWEQhzx
        password: Ks@Sh231zl!QAZ2wsx
        connectionInitSqls: set names utf8mb4;
      # zhzx36
      slavezhzx36:
        # 从数据源开关/默认关闭
        enabled: true
        url: jdbc:oracle:thin:@17.1.19.35:1521/zhzx
        username: KSSHZLWEACSZ_GD
        password: Netmar1132ch_2022#
      # 初始连接数
      initialSize: 5
      # 最小连接池数量
      minIdle: 10
      # 最大连接池数量
      maxActive: 20
      # 配置获取连接等待超时的时间
      maxWait: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      timeBetweenEvictionRunsMillis: 60000
      # 配置一个连接在池中最小生存的时间，单位是毫秒
      minEvictableIdleTimeMillis: 300000
      # 配置一个连接在池中最大生存的时间，单位是毫秒
      maxEvictableIdleTimeMillis: 900000
      # 配置检测连接是否有效
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      webStatFilter:
        enabled: true
      statViewServlet:
        enabled: true
        # 设置白名单，不填则允许所有访问
        allow:
        url-pattern: /druid/*
        # 控制台管理用户名和密码
        login-username:
        login-password:
      filter:
        stat:
          enabled: true
          # 慢SQL记录
          log-slow-sql: true
          slow-sql-millis: 1000
          merge-sql: false
        wall:
            config:
                multi-statement-allow: true
    

6、注解切换数据源

package com.netintech.bussiness.mapper.gaj;

import com.netintech.api.domain.operationsystemapplication.workflow.domain.GdJbxxCommon;
import com.netintech.api.domain.operationsystemapplication.workflow.gaj.GajCaseHfResult;
import com.netintech.api.domain.operationsystemapplication.workflow.gaj.GajCaseInfo;
import com.netintech.api.domain.operationsystemapplication.workflow.gaj.GajCaseResult;
import com.netintech.api.domain.operationsystemapplication.workflow.gaj.GajCaseVisit;
import com.netintech.core.aspectj.lang.annotation.DataSource;
import com.netintech.core.aspectj.lang.enums.DataSourceType;
import org.apache.ibatis.annotations.Param;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * @author: lh
 * @CreateDate: 2023/3/16 16:31  lh
 * @UpdateBy:
 * @UpdateDate:
 * @Description:
 * @Group 政法组
 */
public interface GajCaseMapper {

   
    @DataSource(value = DataSourceType.SLAVEZHZX36)
    @Transactional(propagation = Propagation.NOT_SUPPORTED)
    List<GdJbxxCommon> getCaseInfo();

    @DataSource(value = DataSourceType.SLAVEZHZX36)
    @Transactional(propagation = Propagation.NOT_SUPPORTED)
    List<GdJbxxCommon> getCaseResult(@Param(value = "bjStatus") String bjStatus);

    @DataSource(value = DataSourceType.SLAVEZHZX36)
    @Transactional(propagation = Propagation.NOT_SUPPORTED)
    List<GajCaseHfResult> getCaseVisit();

    @DataSource(value = DataSourceType.SLAVEZHZX36)
    @Transactional(propagation = Propagation.NOT_SUPPORTED)
    int testSlave36();
    @DataSource(value = DataSourceType.SLAVE)
    @Transactional(propagation = Propagation.NOT_SUPPORTED)
    int testSlave();
    int testSlave36_2();
    @DataSource(value = DataSourceType.SLAVE)
    @Transactional(propagation = Propagation.NOT_SUPPORTED)
    int updateSlave36();

}