0

I currently learn the JSP & Servlet in java web with tomcat 9.0,

  • I saw some websites say that a servlet will be initialized only once, and the destroy() method will be called when the server want to terminate the life of that servlet,

  • and a servlet will be used for multiple same type requests, http request for example,

  • and database connection resources are important.

So, I think, I should make the Connection object as a member of my Servlet so that I won't need to establish a new connection every time when there is a new http request, and close the connection when the servlet is destroyed.

like this:

public class LoginServlet extends HttpServlet {
    private Connection conn;

    // init, get mysql Connection
    public TeamBlueServlet() {
        initiate();
    }

    private void initiate() {
        // do something and get connection...
        this.conn = controller.getConnection();
       
    }

    // close connection in this method
    @Override
    public void destroy() {
         // try, catch
         this.conn.close();
    }

    @Override
    public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException {
        // do some query
        try {
            ResultSet resultSet = ...;
            PreparedStatement statement = ...;
        } catch (SQLException e) {
            ...
        } finally {
            close(statement, resultSet);
        }
    }

    private void close(PreparedStatement sta, ResultSet rs) {
        try {
            if(sta !=null) sta.close();
        } catch (SQLException e) {
            logger.error(e.getMessage());
        }

        try {
            if(rs !=null) rs.close();
        } catch (SQLException e) {
            logger.error(e.getMessage());
        }
    }
}

But I also saw some say that we should, open connections as late as possible & close connections as soon as possible.

Therefore I get confused, I want to know if I need to make the connection and close it with each query or should I keep the connection open? I know there is something called connection pool probably can solve this problem, but I want to know in this case, which would be better.

Any help would appreciate, thanks.

Shaowen Zhu
  • 51
  • 1
  • 6
  • 2
    If you're not using a connection pool, making your code much more fragile, then you'll need to create and destroy the connection every time. You cannot guarantee that the DB server connection will remain usable throughout the life cycle of your servlet. Note that this is a terrible way to do it and will slow down your application but it will work. – stdunbar Mar 22 '23 at 21:01
  • 3
    Just use a connection pool. The lowest number of users I would need to be planning for in order to decide to use a connection pool is 1. – Nathan Hughes Mar 22 '23 at 21:08
  • @stdunbar, thanks for your reply, does this mean that I'm just establishing connection only once for many http requests, I cannot guarantee that this connection is in a "good" condition at all time, and if this connection is broken, my application will crush because I just established db connection only once at the begin of my Servlet. – Shaowen Zhu Mar 22 '23 at 21:11
  • 1
    It’s worse than that. This example will share the same connection between multiple concurrent requests, but connection is not threadsafe. So, this will cause problems even if the connection to the server is fine. – Tim Moore Mar 22 '23 at 21:25
  • 1
    As soon as you are in doGet or display take the arts in immediately into a new processing method using the synchronized keyword on the method. All methods called in that must then use the synchronized keyword If you are using classForName() you load it once in the server but must close your queries , resultsets e.t.c. , the Main problems with connections is timeout and statelessness. You should close connection each query only because it is stateless with classForName() in that situation unlike connection pools. – Samuel Marchant Mar 22 '23 at 23:30
  • The recommended way is to configure your database connection as a connection pool in the container (in your case Tomcat) and provide it to the web application via JNDI. the web application should not care about opening and closing the database connection at all but obtain the pre-configured database connection via JNDI. – vanje Mar 23 '23 at 10:00

0 Answers0