title: Database connection stops after period of time for no apparent reason
body: <p>I deployed my first Java web application a couple of days ago and realized a strange thing was happening. After a period of time all the dynamic content and functionality that relied on a connection to my database (testimonial submission, admin login) stopped working. It seems like this is happening every 24 hours or so. Every morning I realize it isn't working again.</p>
  
  <p>I solve the issue by going in to the Tomcat web application manager and clicking "reload" on the web app in question. Immediately the dynamic features of the website work again.</p>
  
  <p>My server is running <code>Tomcat</code> 7 and <code>MySQL</code> and the web app uses the <code>JDBC</code> driver to establish the connection to the database. I've made no alterations to Apache or Tomcat settings.</p>
  
  <p>I have other web apps written in PHP that work persistently without fault it just seems to be this Java web app that has this problem.</p>
  
  <p>What would cause this to happen and how can I make it so the web app doesn't need to be reloaded before it can establish a database connection again?</p>
  
  <p>EDIT: attached some code for database connection</p>
  
  <p>Database connection</p>
  
  <pre><code>public class DBConnection {
      private static Connection conn; 
      private static final Configuration conf     = new Configuration();
      private static final String dbDriver        = conf.getDbDriver();
      private static final String dbHostName      = conf.getDbHostname();
      private static final String dbDatabaseName  = conf.getDbDatabaseName();
      private static final String dbUsername      = conf.getDbUsername();
      private static final String dbPassword      = conf.getDbPassword();
  
      public Connection getConnection(){
          try{
              Class.forName(dbDriver);
              Connection conn = (Connection) DriverManager.getConnection(dbHostName + dbDatabaseName, dbUsername, dbPassword);
              return conn;
          } catch(Exception e){
              e.printStackTrace();
          }
          return conn;
      }
      public void disconnect(){
  
          try{
              conn.close();
          } catch (Exception e){}
      }
  }
  </code></pre>
  
  <p>Controller for login form:</p>
  
  <pre><code>    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
          String form         = request.getParameter("form");
  // check login details
  if(form.equals("loginForm")){
      String username = request.getParameter("username").trim();
      String password = request.getParameter("password").trim();
  
      password = loginService.hashPassword(password);
      boolean isValidUser = loginService.checkUser(username, password);
  
      if(isValidUser){
  
          Cookie loggedIn = new Cookie("loggedIn", "true");
          loggedIn.setMaxAge(60*60*24);
          response.addCookie(loggedIn);
  
          out.print("success");
  
      }else{
          out.print("nope");
      }
  }
  }
  </code></pre>
  
  <p>Login service checks login details are correct:</p>
  
  <pre><code>public boolean checkUser(String username, String password){
      boolean isValid = false;
      try{
          sql = "SELECT username, password FROM morleys_user WHERE username=? AND password=? AND isActive=1 LIMIT 1";
          prep = conn.prepareStatement(sql);
          prep.setString(1, username);
          prep.setString(2, password);
          rs = prep.executeQuery();
          if(rs.next()){
              return true;
          }
      }catch(Exception e){
          e.printStackTrace();
      }finally{
          connection.disconnect();
          }
      return isValid;
  }
  </code></pre>
  
  <p><strong>UPDATE</strong></p>
  
  <p>If I understand correctly I should not be handling a direct connection to a database and instead be using a service that will manage connections for me.</p>
  
  <p>This is my example of establishing a DataSource connection to a MysQL database.</p>
  
  <p>Establish a new DataSource instance of this class:</p>
  
  <pre><code>package uk.co.morleys;
  
  import java.io.FileInputStream;
  import java.io.IOException;
  import java.util.Properties;
  
  import javax.sql.DataSource;
  
  import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
  
  public class DataSourceFactory {
  
      public static DataSource getMySQLDataSource() {
          Properties props = new Properties();
          FileInputStream fis = null;
          MysqlDataSource mysqlDS = null;
          try {
              fis = new FileInputStream("db.properties");
              props.load(fis);
              mysqlDS = new MysqlDataSource();
              mysqlDS.setURL(props.getProperty("MYSQL_DB_URL"));
              mysqlDS.setUser(props.getProperty("MYSQL_DB_USERNAME"));
              mysqlDS.setPassword(props.getProperty("MYSQL_DB_PASSWORD"));
          } catch (IOException e) {
              e.printStackTrace();
          }
          return mysqlDS;
      }
  
  }
  </code></pre>
  
  <p>Instantiating a new DataSource for checking user login details</p>
  
  <pre><code>public boolean checkUser(String username, String password){
      boolean isValid = false;
      DataSource ds = DataSourceFactory.getMySQLDataSource();
      Connection con = null;
      ResultSet rs = null;
      PreparedStatement ps = null;
  
      try{
          con = ds.getConnection();
          sql = "SELECT username, password FROM morleys_user WHERE username=? AND password=? AND isActive=1 LIMIT ";
          ps = con.prepareStatement(sql);
          ps.setString(1, username);
          ps.setString(2, password);
          rs = ps.executeQuery();
          if(rs.next()){
              return true;
          }
  
      }catch(SQLException e){
          e.printStackTrace();
      }finally{
          try {
              if(rs != null) rs.close();
              if(ps != null) ps.close();
              if(con != null) con.close();
          } catch (SQLException e) {
              e.printStackTrace();
          }
  }
      return isValid;
  }
  </code></pre>