title: Spring MVC with multiple database connections
body: <p>Hello I am building my first Spring MVC and Thymeleaf web app and I do not know how to connect to two different Oracle databases. I managed to connect to just one.</p>
  <p>Here is the code:</p>
  <p>Repository code:</p>
  <pre><code>    package com.example.demo;
  
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.util.ArrayList;
  import java.util.Collection;
  import java.util.List;
  import java.util.Map;
  import java.util.Set;
  import javax.sql.DataSource;
   
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.beans.factory.annotation.Qualifier;
  import org.springframework.dao.DataAccessException;
  import org.springframework.jdbc.core.JdbcTemplate;
  import org.springframework.jdbc.core.RowMapper;
  import org.springframework.stereotype.Repository;
  
  /**
   *
   * @author 40723
   */
  @Repository
  public class TelefonMailRepositoryImpl implements TelefonMailRepository {
      
      
   private final DataSource dataSourceMail;
   private final DataSource inregIvg;   
   
   public TelefonMailRepositoryImpl(DataSource dataSourceMail, @Qualifier(&quot;inregIvg&quot;) DataSource inregIvg) {
          this.dataSourceMail = dataSourceMail;
          this.inregIvg = inregIvg;
  
      }
      
  
   
   
    public Map&lt;String, Object&gt; findByNumber(String telefon) {
        
          Map&lt;String, Object&gt; toateTel;
        
          String query = &quot;select cnp, nume, prenume from utilizatori where telefon=?&quot;;
         JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSourceMail);
          jdbcTemplate.queryForMap(query,telefon);
          Map&lt;String, Object&gt; info1 = jdbcTemplate.queryForMap(query,telefon);
         // return info1;
        
         String query2 = &quot;select cif, den_client from b101_conturi where telefon=?&quot;;
         JdbcTemplate jdbcTemplate2 = new JdbcTemplate(this.inregIvg);
         Map&lt;String, Object&gt; info2 = jdbcTemplate.queryForMap(query2,telefon);
         
        if(info2.isEmpty()) {
            return info1;
        } else {
            return info2;
        }
         
      
         }
      
      
      public Map&lt;String, Object&gt; findByEmail(String email) {
          
          
          
          String query = &quot;select cnp, nume, prenume from utilizatori where upper(email)=upper(?)&quot;;
          JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSourceMail);
          Map&lt;String, Object&gt; info1 = jdbcTemplate.queryForMap(query, email);
          
          String query2 = &quot;select cif, den_client from b101_conturi where upper(email)=upper(?)&quot;;
          JdbcTemplate jdbcTemplate2 = new JdbcTemplate(this.inregIvg);
          Map&lt;String, Object&gt; info2 = jdbcTemplate2.queryForMap(query2,email);
          
          if(info2.isEmpty()) {
              return info1;
          } else {
              return info1;
          }
  
  
      }
      
      
      public DataSource getDataSourceMail() {
          return dataSourceMail;
      }
      
      
      
  }
  </code></pre>
  <p>This is the DbConfig file:</p>
  <pre><code>    package com.example.demo;
  
  import com.zaxxer.hikari.HikariDataSource;
  import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
  import org.springframework.boot.context.properties.ConfigurationProperties;
  import org.springframework.context.annotation.Bean;
  import org.springframework.context.annotation.Configuration;
  import org.springframework.context.annotation.Primary;
  
  /**
   *
   * @author 40723
   */
  @Configuration
  public class DbConfig {
  
     @Bean
      @Primary
      @ConfigurationProperties(&quot;app.datasource.dsmail&quot;)
      public DataSourceProperties dsmailDataSourceProperties() {
          return new DataSourceProperties();
      }
  
      @Bean(name=&quot;DSMail&quot;)
      @Primary
      @ConfigurationProperties(&quot;app.datasource.dsmail.configuration&quot;)
      public HikariDataSource dataSourceMail() {
          return dsmailDataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class).build();
      }
  
      @Bean
      @ConfigurationProperties(&quot;app.datasource.inregivg&quot;)
      public DataSourceProperties inregIvgDataSourceProperties() {
          return new DataSourceProperties();
      }
  
      @Bean(name = &quot;inregIvg&quot;)
      @ConfigurationProperties(&quot;app.datasource.inregivg.configuration&quot;)
      public HikariDataSource dataSourceInregIvg() {
          return inregIvgDataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class).build();
      }
      
  
  }
  </code></pre>
  <p>And here are the application properties:</p>
  <pre><code>app.datasource.dsmail.username=xxx
  app.datasource.dsmail.password=xxx
  app.datasource.dsmail.url=dburl:123
  app.datasource.dsmail.driver-class-name=oracle.jdbc.OracleDriver
  app.datasource.dsmail.configuration.pool-name=DSMailPool
  app.datasource.dsmail.configuration.auto-commit=false
  
  app.datasource.inregivg.username=yyy
  app.datasource.inregivg.password=yyy
  app.datasource.inregivg.url=dburl2:321
  app.datasource.inregivg.driver-class-name=oracle.jdbc.OracleDriver
  app.datasource.inregivg.configuration.pool-name=DSInregivgPool
  app.datasource.inregivg.configuration.auto-commit=false
  </code></pre>
  <p>I have changed the user, pass, and url of application props because of security reasons.</p>
  <p>Can you tell me what I am doing wrong ?</p>
  <p>The error that I get is:</p>
  <pre><code>    This application has no explicit mapping for /error, so you are seeing this as a fallback.
  
  Mon Mar 21 12:00:20 EET 2022
  There was an unexpected error (type=Internal Server Error, status=500).
  PreparedStatementCallback; bad SQL grammar [select cif, den_client from b101_conturi where telefon=?]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
  org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select cif, den_client from b101_conturi where telefon=?]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
  
      at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
      at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541)
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667)
      at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:713)
      at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:744)
      at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:757)
      at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:879)
      at org.springframework.jdbc.core.JdbcTemplate.queryForMap(JdbcTemplate.java:916)
      at com.example.demo.TelefonMailRepositoryImpl.findByNumber(TelefonMailRepositoryImpl.java:56)
      at com.example.demo.TelefonMailRepositoryImpl$$FastClassBySpringCGLIB$$ecef6002.invoke(&lt;generated&gt;)
      at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
      at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:783)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
      at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:753)
      at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
      at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:753)
      at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:698)
      at com.example.demo.TelefonMailRepositoryImpl$$EnhancerBySpringCGLIB$$637cc014.findByNumber(&lt;generated&gt;)
      at com.example.demo.TelefonMailService.getRaspuns(TelefonMailService.java:25)
      at com.example.demo.TelefonMailController.postDbRequest(TelefonMailController.java:36)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:498)
      at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
      at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150)
      at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117)
      at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)
      at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)
      at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
      at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067)
      at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963)
      at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
      at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:681)
      at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
      at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
      at org.springframework.web.servlet.resource.ResourceUrlEncodingFilter.doFilter(ResourceUrlEncodingFilter.java:67)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
      at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
      at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
      at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
      at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
      at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
      at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
      at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:359)
      at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399)
      at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
      at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:889)
      at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1735)
      at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
      at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
      at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
      at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
      at java.lang.Thread.run(Thread.java:748)
  Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
  
      at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:630)
      at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:564)
      at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1151)
      at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:771)
      at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:299)
      at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:498)
      at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:152)
      at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:937)
      at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1172)
      at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1101)
      at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1426)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1309)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3746)
      at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3855)
      at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1098)
      at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
      at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
      at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:722)
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:651)
      ... 71 more
  Caused by: Error : 942, Position : 28, Sql = select cif, den_client from b101_conturi where telefon=:1 , OriginalSql = select cif, den_client from b101_conturi where telefon=?, Error Msg = ORA-00942: table or view does not exist
  
      at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:637)
      ... 89 more
  </code></pre>
  <p>Just as a reminder with one connection it works it retrieves the desired info but with two datasources it does not work. I made the select that spits the error in Toad and it works so the tabvle exists. Thanks in advance. Have a nice day !</p>