Spring Boot中如何配置只读Oracle数据库（仅查询，不执行MySQL迁移脚本）

1. 配置文件新增Oracle数据源 #

在application.yml或application.properties中添加Oracle的配置项，与原有MySQL配置区分开，同时开启连接池只读属性：

spring:
  datasource:
    # 原有MySQL主数据源
    mysql:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhost:3306/your_mysql_db?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
      username: root
      password: your_mysql_pwd
    # 新增只读Oracle数据源
    oracle:
      driver-class-name: oracle.jdbc.OracleDriver
      url: jdbc:oracle:thin:@//localhost:1521/your_oracle_db
      username: oracle_user
      password: oracle_pwd
      hikari:
        read-only: true

2. 注册数据源Bean #

创建配置类，分别注册两个数据源，用@Primary标记MySQL为主数据源（确保迁移脚本、原有业务默认使用它）：

import com.zaxxer.hikari.HikariDataSource;
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 org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;

@Configuration
public class MultiDataSourceConfig {

    @Primary
    @Bean(name = "mysqlDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.mysql")
    public DataSource mysqlDataSource() {
        return new HikariDataSource();
    }

    @Bean(name = "oracleDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.oracle")
    public DataSource oracleDataSource() {
        return new HikariDataSource();
    }

    @Primary
    @Bean(name = "transactionManager")
    public PlatformTransactionManager mysqlTransactionManager() {
        return new DataSourceTransactionManager(mysqlDataSource());
    }
}

3. 配置Oracle专属数据操作实例 #

若使用JdbcTemplate #

创建专属的Oracle JdbcTemplate实例：

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.beans.factory.annotation.Qualifier;

import javax.sql.DataSource;

@Configuration
public class OracleJdbcConfig {

    @Bean(name = "oracleJdbcTemplate")
    public JdbcTemplate oracleJdbcTemplate(@Qualifier("oracleDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

若使用MyBatis #

为Oracle单独配置SqlSessionFactory和Mapper扫描：

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.yourpackage.mapper.oracle", sqlSessionFactoryRef = "oracleSqlSessionFactory")
public class OracleMyBatisConfig {

    @Bean(name = "oracleSqlSessionFactory")
    public SqlSessionFactory oracleSqlSessionFactory(@Qualifier("oracleDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapper/oracle/*.xml"));
        return sessionFactory.getObject();
    }
}

4. 确保迁移脚本仅作用于MySQL #

如果使用Flyway/Liquibase，默认会绑定@Primary标记的MySQL数据源。若需显式指定，以Flyway为例：

spring:
  flyway:
    datasource: ${spring.datasource.mysql}
    locations: classpath:db/migration/mysql

5. 业务中使用只读Oracle数据源 #

在业务类中注入专属的JdbcTemplate或Mapper即可：

@Service
public class OracleQueryService {

    private final JdbcTemplate oracleJdbcTemplate;

    public OracleQueryService(@Qualifier("oracleJdbcTemplate") JdbcTemplate oracleJdbcTemplate) {
        this.oracleJdbcTemplate = oracleJdbcTemplate;
    }

    public List<Map<String, Object>> queryOracleTable() {
        String sql = "SELECT * FROM YOUR_ORACLE_TABLE";
        return oracleJdbcTemplate.queryForList(sql);
    }
}

内容的提问来源于stack exchange，提问作者ABHINAV.DEEP