title: JDBC Template Works Slow
body: <p>I am having performance problem while using JDBCTemplate to fetch a row from my database. When I run sql in plsql, I can get the result in 3 milliseconds but same query in the code works around 200 ms. I think that it works slow because before running query, a connection created and I lost too many time in it. I guess I need a connection pool or smt. else</p>
  
  <p>Before writing code here, I want to talk about my spring boot project's flow. Client calls my endpoint and in this call, I am using multiple queries from multiple tables. All queries runs slow because for each query, another connection creates. </p>
  
  <h1>Database Config Class</h1>
  
  <pre><code>@Configuration
  public class DatabaseConfig {
  
      @Autowired
      private Environment env;
  
      @Bean(name = "fraudDb")
      public DataSource masterDataSource() {
          DriverManagerDataSource dataSource = new DriverManagerDataSource();
          dataSource.setDriverClassName(env.getProperty("driver-class-name"));
          dataSource.setUrl(env.getProperty("fraud.url"));
          dataSource.setUsername(env.getProperty("fraud.username"));
          dataSource.setPassword(env.getProperty("fraud.password"));
  
          return dataSource;
      }
  
      @Bean(name = "ndvliveDb")
      public DataSource secondDataSource() {
          DriverManagerDataSource dataSource = new DriverManagerDataSource();
          dataSource.setDriverClassName(env.getProperty("driver-class-name"));
          dataSource.setUrl(env.getProperty("ndvlive.url"));
          dataSource.setUsername(env.getProperty("ndvlive.username"));
          dataSource.setPassword(env.getProperty("ndvlive.password"));
  
          return dataSource;
      }
  
      @Bean(name = "fraudJdbcTemplate")
      @Autowired
      public JdbcTemplate masterJdbcTemplate(@Qualifier("fraudDb") DataSource fraudDb) {
          return new JdbcTemplate(fraudDb);
      }
  
      @Bean(name = "ndvliveJdbcTemplate")
      @Autowired
      public JdbcTemplate secondaryJdbcTemplate(@Qualifier("ndvliveDb") DataSource ndvliveDb) {
          return new JdbcTemplate(ndvliveDb);
      }
  }
  </code></pre>
  
  <h1>Queries In RestController</h1>
  
  <pre><code>private RbtranServiceInputModel services(FraudActionsRestRequest fraudActionsRestRequest) {
  Long start = System.nanoTime();
          String debitSegmentId = ndvliveCustomerInfoService.getSBUCode(Integer.parseInt(cifNoSender));
          Long end = System.nanoTime();
          System.out.println("debitSegmentId " + (end - start) / 1e6);
  
          //10 ms - 20 ms
          start = System.nanoTime();
          String debitName = ndvliveCustomerInfoService.getNameSurname(new BigDecimal(cifNoSender), userId);
          end = System.nanoTime();
          System.out.println("debitName " + (end - start) / 1e6);
  
          //3 ms
          start = System.nanoTime();
          ResponseGetBMSInfo bmsInfo = ndvliveCustomerInfoService.getOnlineCustomerInfo(new BigDecimal(cifNoSender));
          end = System.nanoTime();
          System.out.println("bmsInfo " + (end - start) / 1e6);
  
          //10 MS
          start = System.nanoTime();
          Date passwordChangeDate = ndvliveCustomerInfoService.getPasswordChangeDate(new BigDecimal(cifNoSender), userId);
          end = System.nanoTime();
          System.out.println("passwordChangeDate " + (end - start) / 1e6);
  
          //10ms
          start = System.nanoTime();
          Date smartSmsGsmNoRegistrationDate = ndvliveCustomerInfoService.getSmartSmsGsmNoRegistrationDate(new BigDecimal(cifNoSender), userId);
          end = System.nanoTime();
          System.out.println("smartSmsGsmNoRegistrationDate " + (end - start) / 1e6);
  
          //6 ms
          start = System.nanoTime();
          Date membershipDate = ndvliveCustomerInfoService.getMembershipDate(new BigDecimal(cifNoSender), userId);
          end = System.nanoTime();
          System.out.println("membershipDate " + (end - start) / 1e6);
  
          start = System.nanoTime();
          BigDecimal smartSmsNo = ndvliveCustomerInfoService.getSmsGsmNo(new BigDecimal(cifNoSender));//TODO sms;
          end = System.nanoTime();
          System.out.println("smartSmsNo " + (end - start) / 1e6);
  
          start = System.nanoTime();
          String habitInfo = ndvliveCustomerInfoService.getHabitInfo(new BigDecimal(cifNoSender), channel);
          end = System.nanoTime();
          System.out.println("habitInfo " + (end - start) / 1e6);
  
          start = System.nanoTime();
          Date lastBlockSimDate = ndvliveCustomerInfoService.getLastBlockSimDate(new BigDecimal(cifNoSender), userId);
          end = System.nanoTime();
          System.out.println("lastBlockSimDate " + (end - start) / 1e6);
  
          start = System.nanoTime();
          boolean isFamiliar = ndvliveCustomerAccountInfoService.getFamiliarAccount(new BigDecimal(fraudActionsRestRequest.getDebitCustomerId()), fraudActionsRestRequest.getProcessCode(), fraudActionsRestRequest.getCreditAcctNumber(), fraudActionsRestRequest.getSmartSmsNo());
          end = System.nanoTime();
          System.out.println("isFamiliar " + (end - start) / 1e6);
          rbtranServiceInputModel.setKnownAccount(isFamiliar);
  
          start = System.nanoTime();
          boolean isInWhitelist = ndvliveDeviceInfoService.isInWhiteList(Long.parseLong(fraudActionsRestRequest.getDebitCustomerId()), fraudActionsRestRequest.getUserData02(), fraudActionsRestRequest.getDeviceId());
          end = System.nanoTime();
          System.out.println("isInWhitelist " + (end - start) / 1e6);
          rbtranServiceInputModel.setInWhitelist(isInWhitelist);
      }
  </code></pre>
  
  <p>This function takes up to 2 seconds. </p>
  
  <h1>DAO</h1>
  
  <pre><code>public interface CustomerInformationTempDao {
      String getSbuCodeByClientNo(Integer clientNo);
  }
  </code></pre>
  
  <h1>DAOImpl</h1>
  
  <pre><code>@Repository
  public class CustomerInformationTempDaoImpl implements CustomerInformationTempDao {
  
      @Autowired
      @Qualifier("ndvliveJdbcTemplate")
      private JdbcTemplate ndvliveJdbcTemplate;
  
      public String getSbuCodeByClientNo(Integer clientNo) {
          String query = "SELECT * FROM mytable WHERE client_no=" + clientNo;
          try {
              return ndvliveJdbcTemplate.queryForObject(query, (resultSet, i) -&gt; resultSet.getString("SBU_CODE"));
          } catch (EmptyResultDataAccessException e) {
              return null;
          }
      }
  }
  </code></pre>
  
  <h1>SOLUTION</h1>
  
  <p><strong>Changing DriverManagerDataSource</strong> </p>
  
  <pre><code>@Configuration
  public class DatabaseConfig {
  
      @Autowired
      private Environment env;
  
      @Autowired
      private DataSourceProperties dataSourceProperties;
  
      @Bean(name = "fraudDb")
      public DataSource masterDataSource() {
          DataSourceBuilder factory = DataSourceBuilder
                  .create(this.dataSourceProperties.getClassLoader())
                  .driverClassName(env.getProperty("driver-class-name"))
                  .url(env.getProperty("fraud.url"))
                  .username(env.getProperty("fraud.username"))
                  .password(env.getProperty("fraud.password"));
          return factory.build();
      }
  
      @Bean(name = "ndvliveDb")
      public DataSource secondDataSource() {
          DataSourceBuilder factory = DataSourceBuilder
                  .create(this.dataSourceProperties.getClassLoader())
                  .driverClassName(env.getProperty("driver-class-name"))
                  .url(env.getProperty("ndvlive.url"))
                  .username(env.getProperty("ndvlive.username"))
                  .password(env.getProperty("ndvlive.password"));
          return factory.build();
      }
  
      @Bean(name = "fraudJdbcTemplate")
      @Autowired
      public JdbcTemplate masterJdbcTemplate(@Qualifier("fraudDb") DataSource fraudDb) {
          return new JdbcTemplate(fraudDb);
      }
  
      @Bean(name = "ndvliveJdbcTemplate")
      @Autowired
      public JdbcTemplate secondaryJdbcTemplate(@Qualifier("ndvliveDb") DataSource ndvliveDb) {
          return new JdbcTemplate(ndvliveDb);
      }
  }
  </code></pre>