3

I have a standalone application (no application/web server) with various methods accessing a database via JDBC. So far, a database connection is opened (and closed) in every method. There is no need for multiple connections at the same time.

But:

  • Creating a new connection every time seems a bad idea because of performance
  • Alternatively, using a single connection seems a bad idea as well.

What is the way to go? Connection pooling for just one connection?

TT.
  • 15,774
  • 6
  • 47
  • 88
Sophia
  • 1,821
  • 2
  • 17
  • 19
  • 3
    Why is using a single connection a bad idea? And is that different from a pool with just one connection? (What I can think of is wanting to use re-connect features of a pool. Is this a long-running process?) – Thilo Jan 22 '16 at 06:24
  • See http://stackoverflow.com/questions/14467480/connection-pooling-with-apache-dbcp https://commons.apache.org/proper/commons-dbcp/ – bodo Jan 22 '16 at 06:26
  • 2
    Personally, I'd use one of the pooling libraries, mostly because they take care of all the low-level hassle. That way, I can just throw a properties file at it and be done. – Thilo Jan 22 '16 at 06:26
  • @Thilo The application will run for hours, single database tasks are completed within seconds. Problems might be that the connection is lost in meantime. See [here](http://stackoverflow.com/questions/4209454/a-persistent-connection-with-jdbc-to-mysql) – Sophia Jan 22 '16 at 07:53

3 Answers3

2

If you configure it right, you can gain a lot by using a connection pool, most of all the performance of individual statements - connecting to the DB might be measured in seconds.

At the same time except for the initial pool creation (you might be able to run that parallel to other initialization) you still maintain a very good relaibility - as the pool will check connections on checkout or in between and discard connections that broke down. So you're likely to survive episodes of "not-connected" or similar as well.

I share your view that using a single connection might be a bad idea - because you'd have to deal with connection loss / reconnect all over your code.

Jan
  • 13,738
  • 3
  • 30
  • 55
1

A couple of benefits you could get from connection pooling, even if you only have 1 connection:

  1. A connection pool typically manages the life cycle of it's connections. For instance, if a connection goes stale, a new one will be created in it's place. That prevents you from having to handle life cycle events in your code.

  2. A connection pool can control the opening and closing of connections. Just because you call close() on a connection, doesn't necessarily mean the connection is closed by the pool. It can choose to keep the connection open. This can offer performance benefits if your application is constantly opening and closing connections.

SpeaksBinary
  • 185
  • 1
  • 17
-1

Do not use connection pooling for only one thread.

For a standard JDBC connection pool, a pool of Connection objects is created at the time the application starts. That is when the connection pool server starts, it creates a predetermined number of Connection objects. These objects are then used by a pool manager which disperses them when they are requested by different clients. And returns them to the pool when the client doesn't need that Connection object. A great deal of resources are involved in managing this.

So it's basically wasting a little performance wise bothways if only one connection is being used throughout. IMO, opening and closing individual Connection objects will be a better option. Try sending batch queries to compensate on the performance loss.

Debmalya Banerjee
  • 45
  • 1
  • 7