my dog learned polymorphism
The moose likes JDBC and Relational Databases and the fly likes Reducing the amounts of connections Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Reducing the amounts of connections" Watch "Reducing the amounts of connections" New topic

Reducing the amounts of connections

Christoffer Killander

Joined: Jul 06, 2011
Posts: 5
Hi guys.
I have this code that connects to a DB and either edits the data or retrieves data (the code works as intended).
This code is used by aswing program.
So my question is how can change the way it connects so that it only needs to connect once to the database (while my swing program is running) instead of once everytime it needs to uppdate or retrieve data (see code).

Any constructive comments are greatly appreciated

Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33124

You could store the connection in a variable and not close it each time. This is basically how connection pooling works - you have an open connection and return it to the pool rather than closing it. I'm not sure how long you can keep the same open connection. (the pool implementation usually handles it), but that's something you can test and find out.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
chris webster

Joined: Mar 01, 2009
Posts: 2289

Hi Christoffer,

Slightly different topic, but you should look at how you define the key for your Company table. Right now it looks like your primary key is CompanyName, and you are updating this in your edit method. This means that when you update the value, your RDBMS also needs to update the PK index value, which will take extra time.

In any case, it is generally bad practice to update your key e.g. what do you do with any child records that use the CompanyName as a foreign key to refer back to your Company record?

If you think you will need to modify a key, then it is not really your primary key. Instead, you should probably use a numeric ID as a surrogate primary key. This can be an arbitrary unique value which your DB can generate for you e.g. define the column as "auto-increment", or populate it from a DB sequence (Oracle). Hibernate knows how to handle these options on the Java side.

Then you simply use the CompanyId to find your Company record, and you can also use it anywhere you have a foreign key back to the Company table. If you change the CompanyName, it's just another attribute and does not affect your foreign keys etc.

Of course, you may still need to ensure the CompanyName is unique, which is usually done via a unique index on the column, so changing the name will still cause an update to the index. But at least your FKs will not have to change.

Another advantage of using numeric surrogate keys is that they are small, so there is less data to move around when you fetch them, and you only ever need one PK column on a table rather than having a composite PK of several columns, which also makes it easier to manage foreign keys on child/grandchild tables.

Hope this helps.


No more Blub for me, thank you, Vicar.
Christoffer Killander

Joined: Jul 06, 2011
Posts: 5
Hi Chris.
My table looks like this at the moment:

I changed my code a while ago so it uppdates by matcing the ID's of the selected row and the database.
Anyway is there something i have still missed?

// Best Regards Christoffer

P.S. Also very thankfull for all the help i have gotten so far
I agree. Here's the link:
subject: Reducing the amounts of connections
It's not a secret anymore!