When must I close database connections? (Java)

So I have a Java process that runs indefinitely as a TCP server (receives messages from another process, and has onMsg handlers).

One of the things I want to do with the message in my Java program is to write it to disk using a database connection to postgres. Right now, I have one single static connection object which I call every time a message comes in. I do NOT close and reopen the connection for each message.

I am still a bit new to Java, I wanted to know 1) whether there are any pitfalls or dangers with using one connection object open indefinitely, and 2) Are there performance benefits to never closing the connection, as opposed to reopening/closing every time I want to hit the database?

Thanks for the help!

Jon Skeet
people
quotationmark

I do NOT close and reopen the connection for each message.

Yes you do... at least as far as the plain Connection object is concerned. Otherwise, if you ever end up with a broken connection, it'll be broken forever, and if you ever need to perform multiple operations concurrently, you'll have problems.

What you want is a connection pool to manage the "real" connections to the database, and you just ask for a connection from the pool for each operation and close it when you're done with it. Closing the "logical" connection just returns the "real" connection to the pool for another operation. (The pool can handle keeping the connection alive with a heartbeat, retiring connections over time etc.)

There are lots of connection pool technologies available, and it's been a long time since I've used "plain" JDBC so I wouldn't like to say where the state of the art is at the moment - but that's research you can do for yourself :)

people

See more on this question at Stackoverflow