0

I have a Spring Boot application that uses 2 datasources. It can connect to the first one fine. When I try to use the second data source, I am getting the SQL error: Table or view does not exist because it is using the first data source as its connection.

This is my application property file:

#Property for both DBs spring.datasource.driver.class.name=oracle.jdbc.driver.OracleDriver ## Database Properties DB #1 spring.datasource.tms.url=jdbc:oracle:thin:@ldap:<connection properties have been removed but are present> spring.datasource.tms.username=own_app spring.datasource.tms.password=own_app_l1 spring.datasource.tms.jmx.enabled=true spring.main.allow-bean-definition-overriding=true ## Database Properties DB #2 spring.datasource.lhl.url=jdbc:oracle:thin:@ldap:<connection string has been removed but is correct> spring.datasource.lhl.username=LHL_PURCH_APP spring.datasource.lhl.password=ChangemeChangemeChangeme$$2019 spring.datasource.lhl.jmx-enabled=true

This is my Configuration file for both data sources:

@Configuration @PropertySource("classpath:application-local.properties") public class FxgLhlPurchasedItineraryAdapterDataSourceConfiguration { @Value("${spring.datasource.driver.class.name}") private String driverClassName; //TMS properties @Value("${spring.datasource.tms.url}") private String tmsUrl; @Value("${spring.datasource.tms.username}") private String tmsUsername; @Value("${spring.datasource.tms.password}") private String tmsPassword; //LHL Properties @Value("${spring.datasource.lhl.url}") private String lhlUrl; @Value("${spring.datasource.lhl.username}") private String lhlUsername; @Value("${spring.datasource.lhl.password}") private String lhlPassword; @Primary @Bean(name = "tmsDataSource") @ConfigurationProperties(prefix = "spring.datasource.tms") public DataSource tmsDataSource() { DataSourceBuilder factory = DataSourceBuilder.create(this.getClass().getClassLoader()) .driverClassName(driverClassName).url(tmsUrl) .username(tmsUsername) .password(tmsPassword); return factory.build(); } @Bean(name = "lhlDataSource") @ConfigurationProperties(prefix = "spring.datasource.lhl") public DataSource lhlDataSource() { DataSourceBuilder factory = DataSourceBuilder.create(this.getClass().getClassLoader()) .driverClassName(driverClassName).url(lhlUrl) .username(lhlUsername) .password(lhlPassword); return factory.build(); } @Bean(name = "tmsJdbcTemplate") public JdbcTemplate tmsJdbcTemplate(final DataSource tmsDataSource) { return new JdbcTemplate(tmsDataSource); } @Bean(name = "lhlJdbcTemplate") public JdbcTemplate lhlJdbcTemplate(final DataSource lhlDataSource) { return new JdbcTemplate(lhlDataSource); } }

I had to put in the @Primary annotation if the service will not run.
When I try to do a simple SELECT of a table in that is not the Primary database, I get the error that the table does not exist. This is the code that calls the select statement:

 private JdbcTemplate lhlJdbcTemplate; DataSource ds = lhlJdbcTemplate.getDataSource(); Connection con = ds.getConnection(); LOGGER.info("Connection info: {}", con.getSchema()); lhlParmSqIdModelList = lhlJdbcTemplate.query( selectSequenceNbrSQLStatement, new LhlParmSqIdModelRowMapper());

The logger statement returns the schema for the Primary database. How can I get the connection to use the Second database

Improve this question
4
  • By default application will be connecting only one db. You have to configure manually to have multiple db connection. Commented Aug 8, 2019 at 16:57
  • Could you add the code where you declare lhlJdbcTemplate used in lhlJdbcTemplate.query ? Commented Aug 8, 2019 at 17:07
  • Added the declaration for the jdbcTemplate. It is just private JdbcTemplate lhlJdbcTemplate; Commented Aug 8, 2019 at 17:11
  • Possible duplicate of Use two datasources with jdbc template Commented Aug 8, 2019 at 17:26

1 Answer 1

Reset to default
2

Because you have multiple DataSource beans, normally Spring will fail because it doesn't know how to automatically decide which of multiple equivalent beans it should use, it puts that responsibility on you as the programmer.

By adding the @Primary annotation, you are telling Spring "If there are multiple candidate beans of this type, use this one."

Your bean methods aren't asking Spring for a particular DataSource, they just want any DataSource, so Spring gives each of them the one marked with @Primary.

Instead, you'll want to use @Qualifier to indicate exactly which named DataSource they want:

@Bean(name = "tmsJdbcTemplate") public JdbcTemplate tmsJdbcTemplate(@Qualifier("tmsDataSource") final DataSource tmsDataSource) { return new JdbcTemplate(tmsDataSource); } @Bean(name = "lhlJdbcTemplate") public JdbcTemplate lhlJdbcTemplate(@Qualifier("lhlDataSource") final DataSource lhlDataSource) { return new JdbcTemplate(lhlDataSource); }

I don't guarantee this syntax is exactly right, but something like that.

You'll also need to qualify the JdbcTemplate injection point. (Credit to Sherif Behna in the comments)

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

You will also need to use @Qualifier when injecting the JdbcTemplate in the classes that need it.
Exactly, was typing the same answer. Qualifier should be used to distinguish which DS should be used by the JDBCTemplate. Otherwise, it defaults to Primary.
Wonderful! That Worked! Also used the link to the duplicate that showed how to qualify the JDBCTemplate. Thanks so much!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.