Spring中为DB2与Oracle配置独立事务管理器及JdbcTemplate可行吗？

Absolutely! You can absolutely set up separate JdbcTemplate instances and transaction managers for DB2 and Oracle in Spring—this is a standard approach when working with multiple independent databases. Let me break down the implementation step by step:

1. Configure Separate Data Sources #

First, define two distinct DataSource beans for your DB2 and Oracle databases. Use @Configuration to create a configuration class, and inject the respective database credentials (pull these from application properties for flexibility):

@Configuration
public class MultiDbConfig {

    // DB2 Data Source
    @Bean(name = "db2DataSource")
    @ConfigurationProperties(prefix = "db.db2")
    public DataSource db2DataSource() {
        return DataSourceBuilder.create().build();
    }

    // Oracle Data Source
    @Bean(name = "oracleDataSource")
    @ConfigurationProperties(prefix = "db.oracle")
    public DataSource oracleDataSource() {
        return DataSourceBuilder.create().build();
    }
}

In your application.properties (or application.yml), add the configs for both databases:

# DB2 Config
db.db2.url=jdbc:db2://your-db2-host:50000/your-db-name
db.db2.username=db2-user
db.db2.password=db2-pass
db.db2.driver-class-name=com.ibm.db2.jcc.DB2Driver

# Oracle Config
db.oracle.url=jdbc:oracle:thin:@your-oracle-host:1521/your-sid
db.oracle.username=oracle-user
db.oracle.password=oracle-pass
db.oracle.driver-class-name=oracle.jdbc.OracleDriver

2. Create Independent JdbcTemplate Beans #

Next, create a JdbcTemplate for each data source, linking them via @Qualifier to ensure the right data source is used:

@Configuration
public class MultiDbConfig {

    // ... (previous data source beans)

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

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

3. Set Up Separate Transaction Managers #

Each database needs its own transaction manager to handle transactions independently. Use DataSourceTransactionManager for each data source:

@Configuration
public class MultiDbConfig {

    // ... (previous data source and JdbcTemplate beans)

    @Bean(name = "db2TransactionManager")
    public PlatformTransactionManager db2TransactionManager(@Qualifier("db2DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "oracleTransactionManager")
    public PlatformTransactionManager oracleTransactionManager(@Qualifier("oracleDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

4. Use Them in Your Business Logic #

In your service layer, inject the specific JdbcTemplate you need, and use @Transactional with the value attribute to specify which transaction manager to use for the method:

@Service
public class MultiDbService {

    private final JdbcTemplate db2JdbcTemplate;
    private final JdbcTemplate oracleJdbcTemplate;

    // Constructor injection (preferred over @Autowired)
    public MultiDbService(@Qualifier("db2JdbcTemplate") JdbcTemplate db2JdbcTemplate,
                          @Qualifier("oracleJdbcTemplate") JdbcTemplate oracleJdbcTemplate) {
        this.db2JdbcTemplate = db2JdbcTemplate;
        this.oracleJdbcTemplate = oracleJdbcTemplate;
    }

    // Batch update for DB2, using DB2's transaction manager
    @Transactional(value = "db2TransactionManager", rollbackFor = Exception.class)
    public void batchUpdateDb2(List<Object[]> batchArgs) {
        String sql = "INSERT INTO your_db2_table (col1, col2) VALUES (?, ?)";
        db2JdbcTemplate.batchUpdate(sql, batchArgs);
    }

    // Batch update for Oracle, using Oracle's transaction manager
    @Transactional(value = "oracleTransactionManager", rollbackFor = Exception.class)
    public void batchUpdateOracle(List<Object[]> batchArgs) {
        String sql = "INSERT INTO your_oracle_table (col1, col2) VALUES (?, ?)";
        oracleJdbcTemplate.batchUpdate(sql, batchArgs);
    }
}

Key Notes #

• If you ever need distributed transactions (where a single method needs to commit/rollback changes across both databases), DataSourceTransactionManager won't suffice—you'll need to use a JTA-compliant transaction manager like JtaTransactionManager (and ensure your databases/JTA provider support this). But for independent operations (like separate methods updating each DB), the above setup works perfectly.
• Always use @Qualifier to avoid ambiguity when injecting beans of the same type (like multiple DataSource, JdbcTemplate, or PlatformTransactionManager instances).

内容的提问来源于stack exchange，提问作者newcoder