I have 2 JDBC Datasources defined in a Spring Boot application utilizing used in a Spring Batch job. However, after autowiring the datasources, only one gets used. The one used is the one annotated @Primary. If I place the annotation on the other JDBC datasource that gets used instead. In a nutshell only one of the JDBC datasources ever gets used. I use Lombok in some places but I'm unsure if that is playing a part.

Here are the datasources:

application.yml

symphony:
   datasource:
   driver-class-name: oracle.jdbc.OracleDriver
   url: ...
   type: com.zaxxer.hikari.HikariDataSource
   username: <USR>
   password: <PWD>
   jndi-name: false

repo:
  datasource:
    driver-class-name: org.h2.Driver
    url: jdbc:h2:mem:db;DB_CLOSE_DELAY=-1
    username: sa
    password: sa
    jndi-name: false
  

Here is the first datasource:

    @Configuration
    public class RepoDbConfig {


        @Bean
        @ConfigurationProperties("repo.datasource")
        public DataSourceProperties repoDataProperties() {
            return new DataSourceProperties();
        }


        @Bean(name = "repoDataSource")
        public DataSource dataSourcerepo() {
            DataSource dataSource = repoDataProperties().initializeDataSourceBuilder().type(BasicDataSource.class)
                    .build();
            return dataSource;
        }

        @Bean(name = "repoJdbcTemplate")
        public JdbcTemplate repoJdbcTemplate(DataSource repoDataSource) {
            return new JdbcTemplate(repoDataSource);
        }
  
    }

Here is the second datasource:

    @Configuration
    public class SymphonyDbConfig {
        @Primary
        @Bean
        @ConfigurationProperties("symphony.datasource")
        public DataSourceProperties symphonyDataSourceProperties() {
            return new DataSourceProperties();
        }

        @Primary
        @Bean(name = "symphonyDataSource")
        public DataSource dataSourcesymphony() {
            HikariDataSource dataSource = symphonyDataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class)
                    .build();
            return dataSource;
        }

        @Primary
        @Bean(name = "symphonyJdbcTemplate")
        public JdbcTemplate symphonyJdbcTemplate(DataSource symphonyDataSource) {
            return new JdbcTemplate(symphonyDataSource);
        }

    }

The JobRepository beans are configured like this:

        @Configuration
        @RequiredArgsConstructor
        public class JobRepositoryConfig {

            final @Qualifier("repoDataSource")
            DataSource repoDataSource;

            @Bean("repoTransactionManager")
            AbstractPlatformTransactionManager repoTransactionManager() {
                return new ResourcelessTransactionManager();
            }

        

            @Bean("repoJobRepository")
            public JobRepository repoJobRepository(DataSource repoDataSource) throws Exception {
                JobRepositoryFactoryBean jobRepositoryFactoryBean = new JobRepositoryFactoryBean();
                jobRepositoryFactoryBean.setDataSource(repoDataSource);
                jobRepositoryFactoryBean.setTransactionManager(repoTransactionManager());
                jobRepositoryFactoryBean.setDatabaseType(DatabaseType.H2.getProductName());
                return jobRepositoryFactoryBean.getObject();
            }

            @Bean("repoAppJobLauncher")
            public JobLauncher careLocationAppJobLauncher(JobRepository repoJobRepository) {
                SimpleJobLauncher simpleJobLauncher = new SimpleJobLauncher();
                simpleJobLauncher.setJobRepository(repoJobRepository);
                return simpleJobLauncher;
            }
        }

Finally the Batch Job beans used for the Job are configured here: The only part not shown is the launching of the job. All the required beans used are shown here:

@Configuration
        @EnableBatchProcessing
        @EnableScheduling
        @RequiredArgsConstructor
        @Slf4j
        public class CellBatchConfig {
        
            private final JobBuilderFactory jobBuilderFactory;
            @Qualifier("repoAppJobLauncher")
            private final JobLauncher repoAppJobLauncher;
            private final StepBuilderFactory stepBuilderFactory;


            @Value("${chunk-size}")
            private int chunkSize;

            @Qualifier("symphonyDataSource")
            final DataSource symphonyDataSource;

            @Qualifier("repoDataSource")
            final DataSource symphonyDataSource;

            @Bean
            public JdbcPagingItemReader<CenterDto> cellItemReader(PagingQueryProvider pagingQueryProvider) {
                return new JdbcPagingItemReaderBuilder<CenterDto>()
                        .name("cellItemReader")
                        .dataSource(symphonyDataSource)
                        .queryProvider(pagingQueryProvider)
                        .pageSize(chunkSize)
                        .rowMapper(new CellRowMapper())
                        .build();
            }

            @Bean
            public PagingQueryProvider pagingQueryProvider() {
                OraclePagingQueryProvider pagingQueryProvider = new OraclePagingQueryProvider();
                final Map<String, Order> sortKeys = new HashMap<>();
                sortKeys.put("ID", Order.ASCENDING);
                pagingQueryProvider.setSortKeys(sortKeys);
                pagingQueryProvider.setSelectClause(" ID, CELL_NO, MAT_VO ");
                pagingQueryProvider.setFromClause(" from pvc.cells");
                return pagingQueryProvider;
            }


            .......
        }

The error results from only one of the datasources being used. That results that being used to query the Spring Batch job repository resulting in it failing: Here is the key portion of the stacktrace. It is trying to use the oracle datasource to query for the JobRespository resources and fails as a result:

  Caused by: org.springframework.jdbc.BadSqlGrammarException: 
  PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME from 
  BATCH_JOB_INSTANCE 
   where JOB_NAME = ? and JOB_KEY = ?]; nested exception is 
 java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

CodePudding user response：

In the class JobRepositoryConfig: In the bean:

@Bean("symphonyJobRepository")
    public JobRepository symphonyJobRepository(DataSource repoDataSource) throws Exception {
        JobRepositoryFactoryBean jobRepositoryFactoryBean = new JobRepositoryFactoryBean();
        jobRepositoryFactoryBean.setDataSource(repoDataSource);
        jobRepositoryFactoryBean.setTransactionManager(repoTransactionManager());
        jobRepositoryFactoryBean.setDatabaseType(DatabaseType.H2.getProductName());
        return jobRepositoryFactoryBean.getObject();
    }

You didn't use the variable:

final @Qualifier("repoDataSource") DataSource repoDataSource;

So Spring uses a DataSource object which is annotated with @Primary annotation

CodePudding user response：

I fixed it by making one bean the primary and also adding the qualifiers on the specific beans which had been missing, an omission on my part. For example, here I added the @Qualifier:

        @Primary

        @Bean(name = "symphonyDataSource")
        @Qualifier("symphonyDataSource") // This was missing
        public DataSource dataSourcesymphony() {
        HikariDataSource dataSource = symphonyDataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class)
                .build();
        return dataSource;
    }