File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Java in General and the fly likes UPDATE MySQL table from Java FrontEnd Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Java in General
Bookmark "UPDATE MySQL table from Java FrontEnd" Watch "UPDATE MySQL table from Java FrontEnd" New topic

UPDATE MySQL table from Java FrontEnd

Andrew Geroge Alexander
Ranch Hand

Joined: Feb 14, 2012
Posts: 31
Hello, I am trying to switch from MS Access to a more reliable Java FrontEnd and MySQL backend version. I have managed to design and display records from the table, link tables and display records on the same interface.. Now I am stuck up at UPDATE. Problem is because my Customer table is big.. like 88 columns.. I know how to update table.. but want a better efficient way to do it..

I am thinking of few options,
1. To update the entire row if the button is pressed.. (which is a lot, as sometime they might only change the Title from Ms to Mrs).
2. Create flags, and update columns only if the flag value is true.. (this is highly expensive, as it needs 88 flags declared, 88 if conditions for computing which fields to update)
3. Create tabbed panels, and update only the panels where changes are made. (this is again going to be huge, as we might end up in condition 1, if only one field is changed in the particular tab)

If the above options are all rubbish, can someone give me a better idea for update?? I am worried a bit about this..
Ernest Friedman-Hill
author and iconoclast

Joined: Jul 08, 2003
Posts: 24199

By far the most common, and the simplest, way to do this would be to have just one UPDATE query, and variables to hold values for all 88 columns, originally populated from the database. When you show the GUI, you set the initial values of all of the GUI widgets from those variables; when the user presses OK, you set the values of the all variables from the GUI (although many of them won't actually change.) Then you use that one query, setting all 88 column values in the query from the variables. Most of the columns being updated won't actually change, but that's really OK; a good database is going to optimize those extra column changes away anyway.

Don't worry quite so much about little efficiencies. 88 columns might seem like a lot for a person to run through, but it's nothing for a computer; introducing complicated logical to try to update only changed columns would take a lot of your time, and lead to complicated, hard to maintain code, but save little if any computing resources.

In a real system, by the way, those 88 variables will commonly be member variables of a class; an instance of that class represents one row of the table.

[Jess in Action][AskingGoodQuestions]
Andrew Geroge Alexander
Ranch Hand

Joined: Feb 14, 2012
Posts: 31
Hello Ernest, Thanks for the quick response.. I did feel it was okay to do that.. but just wanted someone to backup what I felt right was actually right.. Reading your comment was encouraging. I am working on that, I will keep this thread open for a while, if someone is giving another opinion.. But thank you so much for the response.. Much appreciated.
I agree. Here's the link:
subject: UPDATE MySQL table from Java FrontEnd
It's not a secret anymore!