title: Spring 4 JDBC - How to load DB Properties and optimize (using Cache or DB Connection Pool)
body: <p>Am maintaining a codebase which was written in Spring MVC 4.3.9.RELEASE (not Spring Boot)...</p>
  
  <p>Under src/main/resources:</p>
  
  <p>There are two different database configuration files:</p>
  
  <p>sampledb.xml</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
  xsi:schemaLocation="
      http://www.springframework.org/schema/beans
      http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
      http://www.springframework.org/schema/context
      http://www.springframework.org/schema/context/spring-context-3.0.xsd"&gt;
  
      &lt;!-- Initialization for data source  dbcp  --&gt;
  
      &lt;bean id="sampleDatabase"  class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"&gt;
          &lt;property name="driverClassName"&gt;&lt;value&gt;com.mysql.jdbc.Driver&lt;/value&gt;&lt;/property&gt;
          &lt;property name="url"&gt;&lt;value&gt;jdbc:mysql://localhost/sampledb?zeroDateTimeBehavior=convertToNull&lt;/value&gt;&lt;/property&gt;
          &lt;property name="username"&gt;&lt;value&gt;root/value&gt;&lt;/property&gt;
          &lt;property name="password"&gt;&lt;value&gt;&lt;/value&gt;&lt;/property&gt;
          &lt;property name="maxIdle" value="10"/&gt;
          &lt;property name="maxActive" value="50"/&gt;
          &lt;property name="maxWait" value="100"/&gt; 
          &lt;property name="defaultAutoCommit" value="false"/&gt;
          &lt;property name="removeAbandoned" value="true"/&gt;
          &lt;property name="removeAbandonedTimeout" value="1"/&gt;
          &lt;property name="minIdle" value="0"&gt;&lt;/property&gt;
          &lt;property name="timeBetweenEvictionRunsMillis" value="1000"&gt;&lt;/property&gt;
          &lt;property name="minEvictableIdleTimeMillis" value="1000"&gt;&lt;/property&gt;
       &lt;/bean&gt; 
  &lt;/beans&gt;
  </code></pre>
  
  <p>eventsdb.xml</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
  xsi:schemaLocation="
      http://www.springframework.org/schema/beans
      http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
      http://www.springframework.org/schema/context
      http://www.springframework.org/schema/context/spring-context-3.0.xsd"&gt;
  
      &lt;!-- Initialization for data source  dbcp  --&gt;
  
      &lt;bean id="eventsDatabase"  class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"&gt;
          &lt;property name="driverClassName"&gt;&lt;value&gt;com.mysql.jdbc.Driver&lt;/value&gt;&lt;/property&gt;
          &lt;property name="url"&gt;&lt;value&gt;jdbc:mysql://localhost/eventsdb?zeroDateTimeBehavior=convertToNull&lt;/value&gt;&lt;/property&gt;
          &lt;property name="username"&gt;&lt;value&gt;root&lt;/value&gt;&lt;/property&gt;
          &lt;property name="password"&gt;&lt;value&gt;&lt;/value&gt;&lt;/property&gt;
          &lt;property name="maxIdle" value="10"/&gt;
          &lt;property name="maxActive" value="50"/&gt;
          &lt;property name="maxWait" value="100"/&gt; 
          &lt;property name="defaultAutoCommit" value="false"/&gt;
          &lt;property name="removeAbandoned" value="true"/&gt;
          &lt;property name="removeAbandonedTimeout" value="1"/&gt;
          &lt;property name="minIdle" value="0"&gt;&lt;/property&gt;
          &lt;property name="timeBetweenEvictionRunsMillis" value="1000"&gt;&lt;/property&gt;
          &lt;property name="minEvictableIdleTimeMillis" value="1000"&gt;&lt;/property&gt;
      &lt;/bean&gt; 
  &lt;/beans&gt;
  </code></pre>
  
  <p>WEB-INF/web.xml:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;web-app id="WebApp_ID" version="2.4" 
           xmlns="http://java.sun.com/xml/ns/j2ee" 
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
           xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"&gt;
      &lt;display-name&gt;MyApp&lt;/display-name&gt;
      &lt;welcome-file-list&gt;
            &lt;welcome-file&gt;index.html&lt;/welcome-file&gt;
            &lt;welcome-file&gt;index.jsp&lt;/welcome-file&gt;
      &lt;/welcome-file-list&gt;
      &lt;servlet&gt;
              &lt;servlet-name&gt;mvc-dispatcher&lt;/servlet-name&gt;
              &lt;servlet-class&gt;org.springframework.web.servlet.DispatcherServlet&lt;/servlet-class&gt;
              &lt;load-on-startup&gt;1&lt;/load-on-startup&gt;
      &lt;/servlet&gt;
      &lt;servlet-mapping&gt;
              &lt;servlet-name&gt;mvc-dispatcher&lt;/servlet-name&gt;
              &lt;url-pattern&gt;/&lt;/url-pattern&gt;
      &lt;/servlet-mapping&gt;
      &lt;context-param&gt;
           &lt;param-name&gt;contextConfigLocation&lt;/param-name&gt;
           &lt;param-value&gt;/WEB-INF/mvc-dispatcher-servlet.xml&lt;/param-value&gt;
      &lt;/context-param&gt;
      &lt;listener&gt;
             &lt;listener-class&gt;org.springframework.web.context.ContextLoaderListener&lt;/listener-class&gt;
      &lt;/listener&gt;
  &lt;/web-app&gt;
  </code></pre>
  
  <p>WEB-INF/mvc-dispatcher-servlet.xml:</p>
  
  <pre><code>&lt;beans xmlns="http://www.springframework.org/schema/beans"
      xmlns:context="http://www.springframework.org/schema/context"
      xmlns:mvc="http://www.springframework.org/schema/mvc"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xmlns:util="http://www.springframework.org/schema/util"
      xsi:schemaLocation="
          http://www.springframework.org/schema/beans
          http://www.springframework.org/schema/beans/spring-beans.xsd
          http://www.springframework.org/schema/context
          http://www.springframework.org/schema/context/spring-context.xsd
          http://www.springframework.org/schema/mvc
          http://www.springframework.org/schema/mvc/spring-mvc.xsd
          http://www.springframework.org/schema/util
          http://www.springframework.org/schema/util/spring-util.xsd"&gt;
      &lt;context:component-scan base-package="com.myapp.rest.controllers" /&gt;
      &lt;mvc:annotation-driven /&gt;
  
      &lt;bean id="viewResolver"
          class="org.springframework.web.servlet.view.InternalResourceViewResolver"&gt;
          &lt;property name="prefix"&gt;
              &lt;value&gt;/WEB-INF/pages/&lt;/value&gt;
          &lt;/property&gt;
          &lt;property name="suffix"&gt;
              &lt;value&gt;.jsp&lt;/value&gt;
          &lt;/property&gt;
      &lt;/bean&gt;
  &lt;/beans&gt;
  </code></pre>
  
  <p>Sample webservice code:</p>
  
  <pre><code>package com.myapp.rest.controllers;
  
  @Controller
  @RequestMapping("/v2")
  public class MyController {
  
          @RequestMapping(value="users/{userId}",method=RequestMethod.GET)
          public @ResponseBody Object getUserDetails(@PathVariable String userId){
              Object response=null;
              UserDAO dao = UserDAO.getInstance();
              response=dao.getUser(userId);
              return response;
          }
  }
  </code></pre>
  
  <p>UserDao:</p>
  
  <pre><code>public class UserDAO {
  
      private static UserDAO instance = null;
      private JdbcTemplate jdbcTemplateObject = null;
  
      public static UserDAO getInstance() {
          if(instance == null) {
              synchronized(UserDAO.class) {
                  if(instance == null) {
                      instance = new UserDAO();
                  }
              }
          }
          return instance ;
      }
  
      UserDAO() {
          try {
              initializeDB();
          }
          catch(Exception e) {
              e.printStackTrace();
          }
      }
  
      private void initializeDB() {
          try {
              ApplicationContext context = new ClassPathXmlApplicationContext("sampledb.xml");
              DataSource dataSource = (DataSource) context.getBean("sampleDatabase");
              this.jdbcTemplateObject = new JdbcTemplate(dataSource);
          } 
          catch (Exception e) {
              e.printStackTrace();
          }
      }
  
      // others methods which do the actual queries using Spring JDBC
  }
  </code></pre>
  
  <p>The previous author has used this pattern (initializing the DB using ApplicationContext) in every single DAO (there's like 20 different ones in the codebase each doing the same thing with the same two database config files)!</p>
  
  <p>Question(s):</p>
  
  <ol>
  <li><p>This seems very inadequate (seems like should be done once), how can this (loading Spring based DB config files) be done once as soon as the war file loadings into Tomcat?</p></li>
  <li><p>What's the best techniques for performance gains (e.g. should I use a caching system or a database connection pool)?</p></li>
  </ol>
  
  <p>Any advice is greatly appreciated...</p>