Пример использования: прочитать данные из Oracle и загрузить их в MySQL.
Мы используем Spring Data JDBC и OJDBC8.
Проблема: всегда подключается к основному источнику данных.
**application.properties**
=======================
spring.datasource.mysql.jdbcUrl = jdbc:mysql://localhost:3306/MySQLData?useSSL=false
spring.datasource.mysql.username = root
spring.datasource.mysql.password = root
spring.datasource.mysql.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.oracle.jdbcUrl = jdbc:oracle:thin:@localhost:1521/XE
spring.datasource.oracle.username = root
spring.datasource.oracle.password = ea
spring.datasource.oracle.driverClassName = oracle.jdbc.OracleDriver
**MySqlDataSource.java**
=====================
package com.test.datasource;
@Configuration
@EnableJdbcRepositories(transactionManagerRef = "mysqlJdbcTransactionManager", jdbcOperationsRef = "mysqlJdbcOperationsReference", basePackages = {
"com.test.data.mysql.repository" })
@EnableAutoConfiguration(exclude = { DataSourceAutoConfiguration.class, JdbcRepositoriesAutoConfiguration.class })
public class MySqlDataSource extends AbstractJdbcConfiguration {
@Bean
@Primary
@Qualifier("mysqlDataSource")
@ConfigurationProperties(prefix = "spring.datasource.mysql")
public DataSource dataSourceMySql() {
return DataSourceBuilder.create().build();
}
@Bean
@Primary
@Qualifier("mysqlJdbcOperationsReference")
public NamedParameterJdbcOperations mysqlJdbcOperationsReference(
@Qualifier("mysqlDataSource") DataSource dataSource) {
return new NamedParameterJdbcTemplate(dataSource);
}
@Bean
@Primary
@Qualifier("mysqlJdbcTransactionManager")
public PlatformTransactionManager mysqlJdbcTransactionManager(@Qualifier("mysqlDataSource") final DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
}
**OracleDataSource.java**
======================
package com.test.datasource;
@Configuration
@EnableTransactionManagement
@EnableJdbcRepositories(transactionManagerRef = "oracleJdbcTransactionManager", jdbcOperationsRef = "oracleJdbcOperationsReference", basePackages = {
"com.test.data.oracle.repository" })
@EnableAutoConfiguration(exclude = { DataSourceAutoConfiguration.class, JdbcRepositoriesAutoConfiguration.class })
public class OracleDataSource extends AbstractJdbcConfiguration {
@Bean
@Qualifier("oracleDataSource")
@ConfigurationProperties(prefix = "spring.datasource.oracle")
public DataSource dataSourceOracle() {
return DataSourceBuilder.create().build();
}
@Bean
@Qualifier("oracleJdbcOperationsReference")
public NamedParameterJdbcOperations oracleJdbcOperationsReference(
@Qualifier("oracleDataSource") DataSource dataSource) {
return new NamedParameterJdbcTemplate(dataSource);
}
@Bean
@Qualifier("oracleJdbcTransactionManager")
public PlatformTransactionManager oracleJdbcTransactionManager(
@Qualifier("oracleDataSource") final DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
}
package com.test.data.oracle.repository;
@Repository
public interface ServiceRepository extends CrudRepository<Service, Integer> {
}
**Controller.java**
====================
package com.test.controller;
@RestController
@RequestMapping(value = "/api/v1/bnService")
@Api(tags = { "Business Unit operations" })
public class BNServiceController {
@Autowired
private ServiceRepository attributeGroupRepository;
@RequestMapping(method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON_VALUE)
public SuccessVO retrieveConnectorInfoByCode() {
Optional<Service> pagedOtAttributeGroup = attributeGroupRepository.findById(52);
return null;
}
}
**Logs**
========
Adding transactional method 'org.springframework.data.jdbc.repository.support.SimpleJdbcRepository.findById' with attribute: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,readOnly
Returning cached instance of singleton bean 'oracleJdbcTransactionManager'
Creating new transaction with name [org.springframework.data.jdbc.repository.support.SimpleJdbcRepository.findById]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,readOnly
HikariPool-2 - configuration:
allowPoolSuspension.............false
autoCommit......................true
catalog.........................none
connectionInitSql...............none
connectionTestQuery.............none
connectionTimeout...............30000
dataSource......................none
dataSourceClassName.............none
dataSourceJNDI..................none
dataSourceProperties............{password=<masked>}
driverClassName................."oracle.jdbc.OracleDriver"
exceptionOverrideClassName......none
healthCheckProperties...........{}
healthCheckRegistry.............none
idleTimeout.....................600000
initializationFailTimeout.......1
isolateInternalQueries..........false
jdbcUrl.........................jdbc:oracle:thin:@localhost:1521/XE
leakDetectionThreshold..........0
maxLifetime.....................1800000
maximumPoolSize.................10
metricRegistry..................none
metricsTrackerFactory...........none
minimumIdle.....................10
password........................<masked>
poolName........................"HikariPool-2"
readOnly........................false
registerMbeans..................false
scheduledExecutor...............none
schema..........................none
threadFactory...................internal
transactionIsolation............default
username........................"ea"
validationTimeout...............5000
HikariPool-2 - Starting...
HikariPool-2 - Added connection oracle.jdbc.driver.T4CConnection@1bfa059c
HikariPool-2 - Start completed.
Acquired Connection [HikariProxyConnection@2136532594 wrapping oracle.jdbc.driver.T4CConnection@1bfa059c] for JDBC transaction
Setting JDBC Connection [HikariProxyConnection@2136532594 wrapping oracle.jdbc.driver.T4CConnection@1bfa059c] read-only
Switching JDBC Connection [HikariProxyConnection@2136532594 wrapping oracle.jdbc.driver.T4CConnection@1bfa059c] to manual commit
Bound value [org.springframework.jdbc.datasource.ConnectionHolder@55db8827] for key [HikariDataSource (HikariPool-2)] to thread [http-nio-8081-exec-2]
Initializing transaction synchronization
Getting transaction for [org.springframework.data.jdbc.repository.support.SimpleJdbcRepository.findById]
Executing prepared SQL query
Fetching JDBC Connection from DataSource
Bound value [org.springframework.jdbc.datasource.ConnectionHolder@400a4f] for key [HikariDataSource (HikariPool-1)] to thread [http-nio-8081-exec-2]
Setting SQL statement parameter value: column index 1, parameter value [52], value class [java.lang.Integer], SQL type 4
Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@400a4f] for key [HikariDataSource (HikariPool-1)] bound to thread [http-nio-8081-exec-2]
Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@400a4f] for key [HikariDataSource (HikariPool-1)] bound to thread [http-nio-8081-exec-2]
Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
HikariPool-2 - Pool stats (total=1, active=1, idle=0, waiting=0)
Using JAXP provider [com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderFactoryImpl]
Trying to resolve XML entity with public ID [-//SPRING//DTD BEAN 2.0//EN] and system ID [https://www.springframework.org/dtd/spring-beans-2.0.dtd]
Trying to locate [spring-beans.dtd] in Spring jar on classpath
Found beans DTD [https://www.springframework.org/dtd/spring-beans-2.0.dtd] in classpath: spring-beans.dtd
Alias definition 'Db2' registered for name 'DB2'
Alias definition 'Hana' registered for name 'HDB'
Alias definition 'Hsql' registered for name 'HSQL'
Alias definition 'SqlServer' registered for name 'MS-SQL'
Alias definition 'Postgres' registered for name 'PostgreSQL'
Loaded 11 bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
Caching SQL error codes for DataSource [com.zaxxer.hikari.HikariDataSource@3b332962]: database product name is 'MySQL'
Translating SQLException with SQL state '42S02', error code '1146', message [Table 'MySQLData.service' doesn't exist]; SQL was [SELECT `service`.`SERVICE_ID` AS `SERVICE_ID`, `service`.`SERVICE_NAME` AS `SERVICE_NAME` FROM `service` WHERE `service`.`SERVICE_ID` = ?] for task [PreparedStatementCallback]
Completing transaction for **[org.springframework.data.jdbc.repository.support.SimpleJdbcRepository.findById] after exception: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT `service`.`SERVICE_ID` AS `SERVICE_ID`, `service`.`SERVICE_NAME` AS `SERVICE_NAME` FROM `service` WHERE `service`.`SERVICE_ID` = ?]; nested exception is java.sql.SQLSyntaxErrorException: Table 'MySQLData.service' doesn't exist**
Основываясь на журналах, я понял, что он загружает драйвер оракула и из-за какой-то проблемы не может получить данные и переключить соединение обратно на MySQL. Поскольку в MySQL нет этих таблиц, возникает исключение.
Примечание:
Если я изменю оракул как основной, он будет работать, как ожидалось.
