Why we should close the connection in JDBC? If we don't do it, what will happen

Question

Communicate with the database in java, we often follow these steps:

1. load a driver
2. get a connection
3. create a Statement or PreparedStatement
4. get the ResultSet
5. close the connection

I am confused that we should close connection, all say that create a connection is expensive, so why we can't do like this:

static
    {
        try
        {
            connection = DriverManager.getConnection(connectorURL,
                    user, password);
        } catch (SQLException e)
        {
            e.printStackTrace();
        }
    }

We just create a connection as a singleton, and use it everywhere. Couldn't it? If I use it like this, what will happen?

And if I don't close the connection, what will happen?

Also, we will use a connection pool, it will create some connections in the pool, and we get the connection from the pool, the connection in the pool also don't close, why if we don't use pool, we need follow the steps and close the connection if we don't use?

It's so confused and I don't know the what's the principle. Please help me. Thanks.

Firsthand

Even when using a connection pool you must close the connection: that signals to the connection pool it is available for reuse. Your solution of storing in a static variable is really bad: it can cause all kinds of hard to debug concurrency problems. If you worry about the cost of connection creation, then you should use a connection pool, instead of creating connections through DriverManager. – Mohenjodaro

You can do what you've written but the connection isn't thread-safe, so it's pointless, unless you're also going to introduce synchronization, which as a cure is worse than the disease. It's better to use a thread-pool to give you a Connection as a local variable, and close it when the method exits to return it to the pool. If you don't close it, it leaks, and ties up server resources. – Cummine

@EJP The connection itself might be thread-safe (required by JDBC), but the applications use of the connection is probably not threadsafe. Think of things like different transaction isolation, boundaries (commit/rollback/autocommit) etc. – Mohenjodaro

Answer 1

If we don't close the connection, it will lead to connection memory leakage. Until application server/web server is shut down, connection will remain active, even if the user logs out.

There are additional reasons. Suppose database server has 10 connections available and 10 clients request for the connection. If the database sever grants all of them, and after their usage they are not closed, the database server would not be able to provide any other connection for another request. For that reason we need to close them - it is mandatory.

Furthermore, it might lead to some mischievous activities regarding the integrity of the database.

Barncard

Answer 2

We just create a connection as a singleton, and use it everywhere. Couldn't it? If I use it like this, what will happen?

In this case, you will have only a single database connection. If database query is having a longer execution time, then other requests for that connection object will have to wait. So, this is not a recommended approach.

And if I don't close the connection, what will happen?

By closing the connection, objects of Statement and ResultSet will be closed automatically. The close() method is used to close the connection. If you forget to do so, it will lead your app to connection memory leak. For Example: Imagine that your app has 10 database connections and 10 users are active at the same time. Later on, 3 users log out of the app, but because you didn't implement connection closing mechanism, those 3 connections remain active, and as a result, your app will not provide any other connection to some other user. Also, increased number of opened connections, in database server, slows down the app. So, release the Connection object's database and JDBC resources immediately, instead of waiting for them to be automatically released.

Also, we will use a connection pool, it will create some connections in the pool, and we get the connection from the pool, the connection in the pool also don't close, why if we don't use pool, we need follow the steps and close the connection if we don't use?

Connection pooling means that connections are reused rather than created each time a connection is requested.

This source says, that: "If the system provides connection pooling, the lookup returns a connection from the pool if one is available. If the system does not provide connection pooling or if there are no available connections in the pool, the lookup creates a new connection. The application benefits from connection reuse without requiring any code changes. Reused connections from the pool behave the same way as newly created physical connections. The application makes a connection to the database and data access works in the usual way. When the application has finished its work with the connection, the application explicitly closes the connection.

The closing event on a pooled connection signals the pooling module to place the connection back in the connection pool for future reuse."

Your application borrows a connection from the pool, uses it, then returns it to the pool by closing it. A connection in the free pool for a long period of time is not considered an issue.

Justinajustine

By closing the connection, objects of Statement and ResultSet will be closed automatically not every RDBMS can do this... – Chromato

@YoushaAleayoub Old post, but could you elaborate on that a little, please? – Clepsydra