oracle实现跨库查询

1.properties
注意：里面的primary和secondary是自己定义的名称

spring.datasource.primary.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.primary.jdbc-url=jdbc:oracle:thin:@//192.168.1.168:1521/GJHDFZJCPDB
spring.datasource.primary.username=gjhdfzjc
spring.datasource.primary.password=gjhdfzjc

spring.datasource.secondary.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.secondary.jdbc-url=jdbc:oracle:thin:@//192.168.1.168:1521/GJHDFZJCPDB
spring.datasource.secondary.username=sde
spring.datasource.secondary.password=sde

如果是yml

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    primary:
      driver-class-name: oracle.jdbc.OracleDriver
      jdbc-url: jdbc:oracle:thin:@//192.168.1.168:1521/HYSTTPT
      username: sde
      password: sde
    secondary:
      driver-class-name: oracle.jdbc.OracleDriver
      jdbc-url: jdbc:oracle:thin:@//192.168.1.168:1521/STHJYJJCPDBTEST
      username: sthjyjjc
      password: sthjyjjc
    thirddatasource:
      driver-class-name: oracle.jdbc.OracleDriver
      jdbc-url: jdbc:oracle:thin:@//192.168.1.168:1521/pdbjglw
      username: jglw
      password: jglw
    # 前端界面默认路径配置
    thymeleaf:
      prefix: classpath:/templates/
    # 静态资源默认路径配置
    mvc:
      static-path-pattern: /static/**
    druid:
      test-on-borrow: true
      test-while-idle: true

2.DataSourceConfig.java

package com.hjkj.config;

import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

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

/**
 * @version 1.0
 * @author zhangqinggang
 * @date 2020/10/19
 * 配置数据源
 */
@Configuration
// 自定义数据源一定要排除SpringBoot自动配置数据源，不然会出现循环引用的问题
@EnableAutoConfiguration(exclude = {DataSourceAutoConfiguration.class})
public class DataSourceConfig {

    @Bean(name = "primary")
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "secondary")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }
    @Bean(name = "thirddatasource")
    @ConfigurationProperties(prefix = "spring.datasource.thirddatasource")
    public DataSource thirddatasource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 动态数据源
     * 通过AOP+注解实现动态切换
     *
     * @return
     */
    @Primary
    @Bean(name = "dynamicDataSource")
    public DataSource dataSource() {
        DynamicDataSourceRouter dynamicDataSource = new DynamicDataSourceRouter();
        // 默认数据源
        dynamicDataSource.setDefaultTargetDataSource(primaryDataSource());
        // 配置多数据源
        Map<Object, Object> dataSourceMap = new HashMap(5);
        dataSourceMap.put("primary", primaryDataSource());
        dataSourceMap.put("secondary", secondaryDataSource());
        dataSourceMap.put("thirddatasource", thirddatasource());
        dynamicDataSource.setTargetDataSources(dataSourceMap);
        return dynamicDataSource;
    }

    /**
     * 配置@Transactional注解事物
     *
     * @return
     */
    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dataSource());
    }

}

3.DataSourceNameContextHolder.java

package com.hjkj.niadss.config;

import lombok.extern.slf4j.Slf4j;
import org.springframework.core.NamedThreadLocal;

/**
 * @author zhangqinggang
 * @version 1.0
 * @Date 2020/10/19
 */
@Slf4j
public class DataSourceNameContextHolder {
    private static final ThreadLocal<String> dataSourceNameContextHolder = new NamedThreadLocal<>("DataSourceContext");

    /** 默认数据源名称 */
    public static final String DEFAULT_DATASOURCE_NAME = "primary";

    public static void setDataSourceName(String dataSourceName) {
        log.info("切换到[{}]数据源", dataSourceName);
        dataSourceNameContextHolder.set(dataSourceName);
    }

    public static String getDataSourceName() {

        return dataSourceNameContextHolder.get() != null ? dataSourceNameContextHolder.get() : DEFAULT_DATASOURCE_NAME;
    }

    public static void resetDataSourceName() {
        dataSourceNameContextHolder.remove();
    }
}

4.DynamicDataSourceRouter.java

package com.hjkj.niadss.config;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import java.io.PrintWriter;
import java.sql.SQLException;
/**
 * @version 1.0
 * @author zhangqinggang
 * @date 2020/10/19
 */
public class DynamicDataSourceRouter extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceNameContextHolder.getDataSourceName();
    }

    @Override
    public void setLogWriter(PrintWriter pw) throws SQLException {
        super.setLogWriter(pw);
    }
}

5.DataSourceName.java

package com.hjkj.niadss.annotation;

import java.lang.annotation.*;

/**
 * @version 1.0
 * @author zhangqinggang
 * @date 2020/10/19
 */
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD})
@Documented
public @interface DataSourceName {
    /**
     * 指定数据源名称
     * @return dataSourceName
     */
    String value() default "primary";
}

6.DynamicDataSourceAspect

package com.ehualu.datasource;

import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.stereotype.Component;

/**
 * @author
 * @version 1.0
 * @Date 2020/10/19
 */
@Aspect
@Component
public class DynamicDataSourceAspect {
    @Before("@annotation(dataSourceName)")
    public void beforeSwitchDataSource(DataSourceName dataSourceName){
        // 切换数据源
        DataSourceNameContextHolder.setDataSourceName(dataSourceName.value());
    }

    @After("@annotation(com.ehualu.datasource.DataSourceName)")
    public void afterSwitchDataSource(){
        DataSourceNameContextHolder.resetDataSourceName();
    }
}

7.使用

package com.ehualu.se.dao;


import com.ehualu.datasource.DataSourceName;

public interface TestDao {

    String know();

    @DataSourceName("secondary")
    String tag();

}

8.结构