When NOT to use database connection pooling in Java?

I can find a lot of questions about how to use connection pooling and why it's a good idea, but I'm wondering if I actually need it.

I'm creating a server application and although it is multi-threaded, I've been careful that only a single thread ever accesses my database connection.

So is there any point in using a connection pool?

Can't I just open a connection to my database at the start of the life-cycle and use that one connection forever, or will it time-out if inactive for too long?

Do I absolutely have to call close() on my connection after I do something with it, or is it enough call close() on the ResultSet and/or Statement?

Solution

What is a "database connection", really? It is a session with your database, and as such:

There is a client side and server side session state
There is a transaction associated with that session

Now, since your client application is multi-threaded, I suspect there is only an extremely limited number of cases where your setup makes sense, namely:

You don't use the session state, not even inadvertently (and that may be vendor specific)
You don't use transactions and always auto-commit
Even with auto-committing, there may be race conditions, so you make sure those don't happen
You're very sure that each statement only takes very little time, freeing resources immediately

In all other cases, you want one connection per client thread, or in a reactive/async environment, you at least want one connection per isolated database interaction. And because it's expensive to create new connections (i.e. initialise server side session state, etc.) people simply use connection pools. In fact, a connection pool can have only one connection inside of it (as per your requirement), and it is still a good abstraction for you to use. So why write your own connection pool, instead?

Regarding your specific questions:

So is there any point in using a connection pool?

Except for very trivial cases (see above), it is usually good to have a connection pool.

Can't I just open a connection to my database at the start of the life-cycle and use that one connection forever, or will it time-out if inactive for too long?

You could, of course. There's usually a setting in JDBC drivers or other client libraries to prevent these time outs, or to reconnect.

The perfect use-case for such an approach is a migration script, or a batch script, or a simple test script, or a simple Swing application, etc. All of these don't need a connection pool.

Do I absolutely have to call close() on my connection after I do something with it, or is it enough call close() on the ResultSet and/or Statement?

You should call close() on connections obtained from DataSource.getConnection() (e.g. when the connection pool implements DataSource).

You don't have to call close() on connections whose lifecycle you manage on your own.