title: Access multiple datasources with jdbcTemplates in one Service class
body: <p>Here is my case:
  I have two databases: one sybase and one mssql. I wish to access both of the database in a single service class. For example, I want to get some data from sybase, then I need do some update on mssql.</p>
  
  <p>I have setup two datasources based on multiple samples found online, but Im unable to access my second database (sybase).</p>
  
  <p>Here is my code:</p>
  
  <blockquote>
    <p>pom.xml</p>
  </blockquote>
  
  <pre><code>spring.mvc.view.prefix: /WEB-INF/jsp/
  spring.mvc.view.suffix: .jsp
  
  
  # Database
  # spring.datasource.jndi-name=jdbc/database1
  spring.datasource.driver-class-name=net.sourceforge.jtds.jdbc.Driver
  spring.datasource.url=jdbc:jtds:sqlserver://database1/db_aes
  spring.datasource.username=user1
  spring.datasource.password=password1
  
  # Keep the connection alive if idle for a long time (needed in production)
  spring.datasource.testWhileIdle = true
  spring.datasource.validationQuery = SELECT 1
  
  # 2nd Database
  spring.secondDatasource.driver-class-name=net.sourceforge.jtds.jdbc.Driver
  spring.secondDatasource.url=jdbc:jtds:sybase://database2/aidcconfig
  spring.secondDatasource.username=user2
  spring.secondDatasource.password=password2
  spring.secondDatasource.hibernate.dialect = org.hibernate.dialect.SybaseASE15Dialect
  spring.secondDatasource.testWhileIdle = true
  spring.secondDatasource.validationQuery = SELECT 1
  
  
  # Show or not log for each sql query
  spring.jpa.show-sql = false
  
  # Hibernate ddl auto (create, create-drop, update, validate)
  spring.jpa.hibernate.ddl-auto = validate
  
  # Naming strategy
  spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.EJB3NamingStrategy
  
  # Use spring.jpa.properties.* for Hibernate native properties (the prefix is
  # stripped before adding them to the entity manager)
  
  # The SQL dialect makes Hibernate generate better SQL for the chosen database
  spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.SQLServerDialect
  
  com.ibm.websphere.persistence.ApplicationsExcludedFromJpaProcessing=*
  </code></pre>
  
  <blockquote>
    <p>fileUploadServiceImpl</p>
  </blockquote>
  
  <pre><code>@Component("fileUploadService")
  @Transactional
  public class FileUploadServiceImpl implements FileUploadService {
  
      @Autowired
      @Qualifier("dbAesJdbcTemplate")
      JdbcTemplate dbAesJdbcTemplate;
  
      @Autowired
      @Qualifier("aidcconfigJdbcTemplate")
      JdbcTemplate aidcconfigJdbcTemplate;
  
      private int uploadId = 1;
  
      private void testDB(){
              String db = aidcconfigJdbcTemplate.queryForObject("select db_name()", String.class);
              System.out.println("database name: " + db);
      }
  ...
  }
  </code></pre>
  
  <blockquote>
    <p>DbAesDataSource</p>
  </blockquote>
  
  <pre><code>package config.database;
  
  @Configuration
  @EnableTransactionManagement
  @EnableJpaRepositories(
          entityManagerFactoryRef = "dbAesEntityManagerFactory",
          transactionManagerRef = "dbAesTransactionManager",
          basePackages = {"web.fileUpload.repo.db_aes.dao"}
          )
  public class DbAesDataSource {
  
      @Primary
      @Bean(name="dbAesDataSource")
      @ConfigurationProperties(prefix = "spring.datasource")
      public DataSource dbAesDataSource(){
          return DataSourceBuilder.create().build();
      }
  
      @Bean(name="dbAesJdbcTemplate")
      public JdbcTemplate dbAesJdbcTemplate(@Qualifier("dbAesDataSource") DataSource dbAesDataSource)
      {
          return new JdbcTemplate(dbAesDataSource);
      }
  
      @Bean(name="dbAesEntityManagerFactory")
      public LocalContainerEntityManagerFactoryBean dbAesEntityManagerFactory(
              EntityManagerFactoryBuilder builder,
              @Qualifier("dbAesDataSource") DataSource dbAesDataSource) {
          return builder
                  .dataSource(dbAesDataSource)
                  .packages("web.fileUpload.repo.db_aes.models")
                  .build();
      }
  
      @Bean(name = "dbAesTransactionManager")
      public PlatformTransactionManager dbAesTransactionManager(
              @Qualifier("dbAesEntityManagerFactory") EntityManagerFactory dbAesEntityManagerFactory) {
          return new JpaTransactionManager(dbAesEntityManagerFactory);
      }
  }
  </code></pre>
  
  <blockquote>
    <p>AidcconfigDataSource</p>
  </blockquote>
  
  <pre><code>@Configuration
  @EnableTransactionManagement
  @EnableJpaRepositories(
          entityManagerFactoryRef = "aidcconfigEntityManagerFactory",
          transactionManagerRef = "aidcconfigTransactionManager",
          basePackages = {"web.fileUpload.repo.aidcconfig.dao"}
          )
  public class AidcconfigDataSource {
  
      @Bean(name="aidcconfigDataSource")
      @ConfigurationProperties(prefix = "spring.secondDatasource")
      public DataSource aidcconfigDataSource(){
          return DataSourceBuilder.create().build();
      }
  
      @Bean(name="aidcconfigJdbcTemplate")
      public JdbcTemplate aidcconfigJdbcTemplate(@Qualifier("aidcconfigDataSource") DataSource aidcconfigDataSource)
      {
          return new JdbcTemplate(aidcconfigDataSource);
      }
  
      @Bean(name="aidcconfigEntityManagerFactory")
      public LocalContainerEntityManagerFactoryBean aidcconfigEntityManagerFactory(
              EntityManagerFactoryBuilder builder,
              @Qualifier("aidcconfigDataSource") DataSource aidcconfigDataSource) {
          return builder
                  .dataSource(aidcconfigDataSource)
                  .packages("web.fileUpload.repo.aidcconfig.models")
                  .build();
      }
  
      @Bean(name = "aidcconfigTransactionManager")
      public PlatformTransactionManager aidcconfigTransactionManager(
              @Qualifier("aidcconfigEntityManagerFactory") EntityManagerFactory aidcconfigEntityManagerFactory) {
          return new JpaTransactionManager(aidcconfigEntityManagerFactory);
      }
  }
  </code></pre>
  
  <p>Here is my error:</p>
  
  <pre><code>[ERROR] Failed to execute goal org.springframework.boot:spring-boot-maven-plugin:1.3.5.RELEASE:run (default-cli) on
   project testUpload: An exception occurred while running. null: InvocationTargetException: Error creating bean with
   name 'fileDownloadController': Injection of autowired dependencies failed; nested exception is org.springframework
  .beans.factory.BeanCreationException: Could not autowire field: private web.fileUpload.services.FileUploadService w
  eb.fileUpload.controller.FileDownloadController.fileUploadService; nested exception is org.springframework.beans.fa
  ctory.BeanCreationException: Error creating bean with name 'fileUploadService': Injection of autowired dependencies
   failed; nested exception is org.springframework.beans.factory.BeanCreationException: Could not autowire field: org
  .springframework.jdbc.core.JdbcTemplate web.fileUpload.services.FileUploadServiceImpl.dbAesJdbcTemplate; nested exc
  eption is org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type [org.springfr
  amework.jdbc.core.JdbcTemplate] found for dependency: expected at least 1 bean which qualifies as autowire candidat
  e for this dependency. Dependency annotations: {@org.springframework.beans.factory.annotation.Autowired(required=tr
  ue), @org.springframework.beans.factory.annotation.Qualifier(value=dbAesJdbcTemplate)} -&gt; [Help 1]
  </code></pre>
  
  <p>If I removed the Qualifier in the FileUploadServiceImpl, then any jdbcTemplate will only connect to my Primary database which is db_aes. How can I access to my second datasource using jdbcTemplate?</p>
  
  <p>Following are some of the references I used:
  <a href="https://stackoverflow.com/questions/26308035/spring-boot-spring-data-jpa-with-multiple-datasources">Spring Boot, Spring Data JPA with multiple DataSources</a></p>
  
  <p><a href="https://www.infoq.com/articles/Multiple-Databases-with-Spring-Boot" rel="nofollow noreferrer">https://www.infoq.com/articles/Multiple-Databases-with-Spring-Boot</a></p>
  
  <p><a href="https://stackoverflow.com/questions/24755429/multiple-datasource-and-jdbctemplate-in-spring-boot-1-1-0">Multiple DataSource and JdbcTemplate in Spring Boot (&gt; 1.1.0)</a></p>
  
  <p><strong>Trial#1</strong>
  I noticed that it is unable to create the bean, and I placed some logger in the AidcconfigDataSource class. As a result, I didn't see my method is being executed. Thus, I assumed that the application is not reading my AidcconfigDataSource class.</p>
  
  <p>I relocated the config folder as such, from java/config to java/web/config:</p>
  
  <p><a href="https://i.stack.imgur.com/8IakJ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8IakJ.png" alt="enter image description here"></a></p>
  
  <p>now I have another error:</p>
  
  <pre><code>[ERROR] Failed to execute goal org.springframework.boot:spring-boot-maven-plugin:1.3.5.RELEASE:run (default-cli) on
   project testUpload: An exception occurred while running. null: InvocationTargetException: Error creating bean with
   name 'dataSourceInitializerPostProcessor': Injection of autowired dependencies failed; nested exception is org.spr
  ingframework.beans.factory.BeanCreationException: Could not autowire field: private org.springframework.beans.facto
  ry.BeanFactory org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerPostProcessor.beanFactory; nested e
  xception is org.springframework.beans.factory.BeanDefinitionStoreException: Invalid bean definition with name 'aidc
  configDataSource' defined in class path resource [web/config/database/AidcconfigDataSource.class]: factory-bean ref
  erence points back to the same bean definition -&gt; [Help 1]
  </code></pre>
  
  <p><strong>Trial#2</strong></p>
  
  <p>I have changed my bean name from aidcconfigDataSource to aidcconfigDS and same to the primary datasource. Plus I have added "spring.jpa.open_in_view = false" in my application.properties. However another error happens.  How to do this the right way?</p>
  
  <pre><code>2016-11-03 09:28:16.118 ERROR 11412 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.servic
  e() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exce
  ption is org.springframework.beans.factory.NoUniqueBeanDefinitionException: No qualifying bean of type [org.springf
  ramework.transaction.PlatformTransactionManager] is defined: expected single matching bean but found 2: dbAesTransa
  ctionManager,aidcconfigTransactionManager] with root cause
  
  org.springframework.beans.factory.NoUniqueBeanDefinitionException: No qualifying bean of type [org.springframework.
  transaction.PlatformTransactionManager] is defined: expected single matching bean but found 2: dbAesTransactionMana
  ger,aidcconfigTransactionManager
  </code></pre>