title: Incorrect Use of JDBC Connection Pool
body: <p>I am running a Spring MVC application, backed by a MySQL database which I am accessing using JDBC. I have been using the same code for a while, and have never really taken a look as to whether or not I was using it correctly (using connection pools properly,etc).</p>
  
  <p>I am aware that there is the <b>JDBCTemplate</b> out there, and I have considered using it, but if the only advantage is that I would just have to not write the boilerplate code for, then I am not quite convinced I should be using it. In fact, I prefer the readability of my code over the JDBCTemplate code.</p>
  
  <p>Below is the code in my DAO, for some reason, I feel like I am not using ConnectionPooling correctly. </p>
  
  <pre><code>public Agent get(Integer id){
      ConnectionPool pool = new ConnectionPool();
      Connection connection = pool.getConnection();
      PreparedStatement ps = null;
      try{
          String query = "SELECT * FROM agent where id= ?";
          ps = connection.prepareStatement(query);
          ps.setInt(1,id);
          ResultSet rs = ps.executeQuery();
  
          Agent agent = null;
          if(rs.next()){
              agent = new Agent();
              agent.setFirstName(rs.getString(1));
              agent.setLastName(rs.getString(2));
              agent.setStreet(rs.getString(3));
              agent.setCity(rs.getString(4));
              agent.setZip(rs.getString(5));
              agent.setState(rs.getString(6));
              agent.setUsername(rs.getString(7));
              agent.setPassword(rs.getString(8));
              agent.setId(rs.getInt(9));
              agent.setEmail(rs.getString(10));
          }
          return agent;
      }
      catch(SQLException e)
      {
          e.printStackTrace();
          return null;
      }
      finally{
          ConnectionUtility utility = new ConnectionUtility();
          utility.closePreparedStatement(ps);
          pool.freeConnection(connection);
      }
  }
  </code></pre>
  
  <p>The code above is what I am worried about the most being incorrect, but I have a few utility classes that may also be contributing to bad practice/improper code.</p>
  
  <p>Below is my <b>ConnectionUtility</b> class.</p>
  
  <pre><code>public class ConnectionUtility{
  
  public static void closeStatement(Statement s){
      try{
          if(s != null){
              s.close();
          }
      }
      catch(SQLException e){
          e.printStackTrace();
      }
  }
  public void closePreparedStatement(Statement ps){
      try{
          if(ps != null){
              ps.close();
          }
      }
      catch(SQLException e){
          e.printStackTrace();
      }
  }
  public static void closeResultSet(ResultSet rs){
      try{
          if(rs != null){
              rs.close();
          }
      }
      catch(SQLException e){
  
      }
  }
  
  }
  </code></pre>
  
  <p>Here is my <b>ConnectionPool</b> class,</p>
  
  <pre><code>public class ConnectionPool {
  private static ConnectionPool pool = null;
  
  public ConnectionPool(){
  }
  public static ConnectionPool getInstance(){
      if(pool == null){
          pool = new ConnectionPool();
      }
      return pool;
  }
  
  @SuppressWarnings("static-access")
  public Connection getConnection(){
      try{
  
          return ConnectionFactory.getInstance().getConnection();
      }
      catch(SQLException e){
          e.printStackTrace();
          return null;
      }
  
  }
  public void freeConnection(Connection c){
      try{
          c.close();
      }
      catch(SQLException e){
          e.printStackTrace();
      }
  }
  }
  </code></pre>
  
  <p>Again, I feel like I am really using all of these classes incorrectly, even though everything is working fine, but nothing has been put to the test in production. </p>
  
  <p>I prefer staying with JDBC, so please no advice on making the switch to another.</p>