CODEWITHSUNDEEP
javatomcatservletsjdbcconnection-pooling
iLearn
iLearn

Reputation: 1189

Creating & Closing Database Connection in ServletContextListener vs Filter

I am creating a database connection in a web project in Java. However, one thing I got confused of;

  1. Which is the best way for creating database connections and closing the connection? Using Filter or ServletContextListener?
  2. 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 contextDestroyed method to close the connection, but I think it will only work when the Tomcat Server is stopped.
  3. Or, do I need to open and close the connection in Filter?

Here are the steps how I am working

  1. Create a DataSource in the Tomcat Server's Context.xml file by using <resource> tag
  2. Reference the resource in the web.xml by using the <resource-ref> tag
  3. Create the connection in the contextInitialized of the ServletContextListener
  4. Do the database operation in the servlet's doPost or doGet methods

contextInitialized method:

    @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();
    }
}

servlet's doGet method:

    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();
        }
    }

contextDestroyed method:

    @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();
    }
}

Upvotes: 1

Views: 2945

Answers (3)

David Levesque
David Levesque

Reputation: 22451

In a Web app, each http request from a user runs in its own thread (or through a thread pool). Having a global connection object shared by all requests is not a good idea because only one thread at a time will be able to use it and it will become a bottleneck for your app.

The recommended setup is to use a connection pool (e.g. C3P0) that is initialized on server startup (either automatically through config or manually in your ServletContextListener). The connection pool will create and close the connections as needed. When you receive an http request (servlet's doPost or doGet), you then just have to get a connection from the pool and return it to the pool once you're done processing that request.

You can use a ServletFilter to automate that part. In your filter, before the call to chain.doFilter(), you get a connection from the pool and store it in a request attribute. After the call to doFilter(), you return it to the pool.

Upvotes: 2

Andreas
Andreas

Reputation: 159185

You should never create a Connection when using a pool. The pool is responsible for the actual creation of connections.

You should never put an open Connection in global memory, e.g. ServletContext. Connections should never be used concurrently by multiple threads. See "Is java.sql.Connection thread safe?".

So, for what you're trying to do, use a Filter, and store the Connection as a Request attribute.

Better yet, use a try-with-resources block in the servlet method, as shown in answer by JB Nizet, so you don't takes connections from the pool unless your servlet actually needs it.

Upvotes: 2

JB Nizet
JB Nizet

Reputation: 692121

1, 2 and 3: none of the above.

You get a connection from the DataSource when you need to start a transaction. You pass that connection around in all method calls participating in that transaction. Then you commit and close the connection. Use try-with-resources to make sure it's always closed, even in case of an exception:

protected void doGet(HttpServletRequest request, HttpServletResponse response) {
    try (Connection connnection = dataSource.getConnection()) {
        // use the connection

        connection.commit();
    }
}

Upvotes: 4

Related Questions