GeeCON Prague 2014*
The moose likes JDBC 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
Bookmark "Reducing the amounts of connections" Watch "Reducing the amounts of connections" New topic
Author

Reducing the amounts of connections

Christoffer Killander
Greenhorn

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

//Christoffer
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30595
    
154

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1726
    
  14

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.

Chris

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

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
 
GeeCON Prague 2014
 
subject: Reducing the amounts of connections