performant connection management

[Chris Rudger]

Hi folks, (or should I say Howdy)

i just have th access ea few tables via JDBC and had problems with connection objects being created where not intended to.
My question arising from that experience:
When i open connections, what is the 'best practice'?
Do i:
- create and open one connection object in DBmanagement class and let it be initialized in the constructor?
- create a static connection object and initialize it in constructor for reuse?
- create and close (!) Connection each time i need to launch a query
- something in between or completely other.

However the answer is.
How do i treat Statemant objects within the connection?

An answer to those basic Question is strongly appriciated as I am still in a state of my little project where i could fix this design issue.

Bye Christian

[Stefan Wagner]

If you don't use multiple connections in different threads, you only need one connection due to my experience.

If you don't create nested queries, (statement 1: getEmployee, -> while iterating through employees: getAdress in a second statement) you only need one Statement.

[Jeanne Boyarsky]

Chris,
Welcome to JavaRanch!

Whatever you decide, keep in mind that connections become stale if not used for too long.

[Chris Rudger]

Thanks for your replies.
But what still bothers me, is how do I know how many connections to
the DB are actually established. I recently experienced that my SQL server closed connection due to too many simultaniously logged in users. But I was shurely the only one who was logged in!
I had the Connection object initialized in the constructor of my
DBmanager class which was instantiated by my JUnit Testclass.
But actually (as had debug messages displayed for each constructor call)
many Connection intances have been establihed before running any of the
stmt.execute .. code.

So if I open the connection in the constructor how can I be sure there
is only one connection?

[Jeanne Boyarsky]

Chris,
The problem with putting it in the constructor is that multiple objects can be created (as you've noticed.) Take a look at the singleton design pattern to avoid this.