title: Creating & Closing Database Connection in ServletContextListener vs Filter
body: <p>I am creating a database connection in a web project in Java. However, one thing I got confused of; </p>
  
  <ol>
  <li>Which is the best way for creating database connections and closing the connection? Using <code>Filter</code> or <code>ServletContextListener</code>?</li>
  <li>Once I perform the database operation in a servlet, how will the JDBC connection close? Do I need to close the connection manually? Where should I close the connection? I do have added <code>contextDestroyed</code> method to close the connection, but I think it will only work when the Tomcat Server is stopped.</li>
  <li>Or, do I need to open and close the connection in <code>Filter</code>?</li>
  </ol>
  
  <p>Here are the steps how I am working</p>
  
  <ol>
  <li>Create a <code>DataSource</code> in the Tomcat Server's <code>Context.xml</code> file by using <code>&lt;resource&gt;</code> tag</li>
  <li>Reference the resource in the <code>web.xml</code> by using the <code>&lt;resource-ref&gt;</code> tag</li>
  <li>Create the connection in the <code>contextInitialized</code> of the <code>ServletContextListener</code></li>
  <li>Do the database operation in the servlet's <code>doPost</code> or <code>doGet</code> methods</li>
  </ol>
  
  <p><code>contextInitialized</code> method:</p>
  
  <pre><code>    @Override
  public void contextInitialized(ServletContextEvent event) {
      System.out.println("START CONNECTION");
      try {
          Context contextEnvironment = (Context) new InitialContext().lookup("java:comp/env/");
          DataSource ds = (DataSource) contextEnvironment.lookup("jdbc/lunaruniversity");
          try {
              Connection con = ds.getConnection();
              ServletContext context = event.getServletContext();
              context.setAttribute("dbConnection", con);
          } catch (SQLException e) {
              e.printStackTrace();
          }
  
      } catch (NamingException e) {
          e.printStackTrace();
      }
  }
  </code></pre>
  
  <p>servlet's <code>doGet</code> method:</p>
  
  <pre><code>    protected void doGet(HttpServletRequest request, HttpServletResponse response)
          throws ServletException, IOException {
      Connection con = (Connection) getServletContext().getAttribute("dbConnection");
  
      String saveStudent = request.getParameter("saveStudent");
      String insertSQL = "INSERT INTO STUDENT (FNAME, LNAME, EMAIL, PHONE, STATE, ZIPCODE) VALUES(?,?,?,?,?,?)";
  
          String fName = request.getParameter("fName");
          String lName = request.getParameter("lName");
          String email = request.getParameter("email");
          String phone = request.getParameter("phone");
          String state = request.getParameter("state");
          String zipCode = request.getParameter("zipCode");
          try {
              java.sql.PreparedStatement ps = con.prepareStatement(insertSQL);
              ps.setString(1, fName);
              ps.setString(2, lName);
              ps.setString(3, email);
              ps.setString(4, phone);
              ps.setString(5, state);
              ps.setString(6, zipCode);
              ps.executeUpdate();
          } catch (SQLException e) {
              e.printStackTrace();
          }
      }
  </code></pre>
  
  <p><code>contextDestroyed</code> method:</p>
  
  <pre><code>    @Override
  public void contextDestroyed(ServletContextEvent event) {
      System.out.println("CLOSE CONNECTION");
      DataSource ds = (DataSource) event.getServletContext().getAttribute("dbConnection");
      try {
          if (!ds.getConnection().isClosed()) {
              System.out.println("CONNECTION IS CLOSED");
              ds.getConnection().close();
          }
      } catch (SQLException e) {
          e.printStackTrace();
      }
  }
  </code></pre>