aspose file tools*
The moose likes JDBC and the fly likes Constructing Database Update Statements based on html form data Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Constructing Database Update Statements based on html form data" Watch "Constructing Database Update Statements based on html form data" New topic
Author

Constructing Database Update Statements based on html form data

Brian Smith
Ranch Hand

Joined: May 20, 2005
Posts: 63
I am wondering how other people handle this situation. I have a jsp where I let a user edit a database record. Lets say there are 10 fields in my table. The data is stored in a JavaBean that corresponds to the table structure (getters and setters for each field). Lets say the user only wants to change one field. The whole "record" of form data is submitted to a servlet that calls some DML class to actually do the updating. How does one construct the Update statement. Do you update every field in the record eventhough only one field actually changed just because every field was submitted via form data and its easy to do it that way? Do you try to figure out what changed by sending the original bean that was sent to the jsp (by setting it as a request attribute) to the servlet as well as the new request parameters and compare them? Then try to dynamically structure your DML statement based on that? It just does not seem right to update the whole record if only one or two fields changed. This could also skew any auditing done on the database.

Thanks everyone.
Scott Dunbar
Ranch Hand

Joined: Sep 23, 2004
Posts: 245
I have seen two methods, neither of which are free:

  • Just update everything in the row blindly. As you said for trigger based logging/auditing this can produce garbage. The DB overhead is not too bad but you're still passing alot of data.
  • Maintain a list of "dirty" flags in the bean. The bean and whatever persists it to the database interact to ultimately say "is this field dirty?" and, if so, update it. This requires a bit more framework to implement.


  • <a href="http://forums.hotjoe.com/forums/list.page" target="_blank" rel="nofollow">Java forums using Java software</a> - Come and help get them started.
    stu derby
    Ranch Hand

    Joined: Dec 15, 2005
    Posts: 333
    Originally posted by Scott Dunbar:
    I have seen two methods, neither of which are free:

  • Just update everything in the row blindly. As you said for trigger based logging/auditing this can produce garbage. The DB overhead is not too bad but you're still passing alot of data.
  • Maintain a list of "dirty" flags in the bean. The bean and whatever persists it to the database interact to ultimately say "is this field dirty?" and, if so, update it. This requires a bit more framework to implement.


  • The DB overhead of the first choice can vary quite a bit, depending on the DB implementation. If the table doesn't have any indexes or foreign keys, then the waste is limited to a little extra CPU time and a significantly bigger transaction log. If there are indexes or foreign keys, they will sometimes be checked/maintained even when the value isn't actually changing (depends on the DB implementation). For tables that are updated very very very frequently, the increased size of the transaction log can be very significant. For very low frequency updates, I wouldn't worry about the DB efficiency particularly. If you intend to program generically, I'd suggest the extra effort to write only the "dirty" columns; it'll pay off in the long run.
    Brian Smith
    Ranch Hand

    Joined: May 20, 2005
    Posts: 63
    Thanks to both of you for the suggestions. I guess to maintain a dirty flag in the bean, I would have to pass back the orginal bean as a request attribute to the servlet in order to make the comparison/set flags? Is there another way that would be better?

    Again I appreciate you taking the time to reply.

    -Brian
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Constructing Database Update Statements based on html form data