Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Reducing the amounts of connections

 
Christoffer Killander
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Christoffer Killander
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic