• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Reducing the amounts of connections

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
And then the flying monkeys attacked. My only defense was this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic