Use case: Read data from oracle and load the same into MySQL.
We are using Spring Data JDBC and OJDBC8.
Issue: Always connecting to the primary data source.
**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**
Based on the logs, I understood that it is loading the oracle driver and due to some issue it couldn't retrieve the data and switching the connection back to MySQL. As MySQL doesn't have those tables it is throwing the exception.
Note:
If I change oracle as primary it is working as expected.
