• 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

Constructing Database Update Statements based on html form data

 
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 245
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.

  •  
    Ranch Hand
    Posts: 333
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    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
    Posts: 63
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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
     
    reply
      Bookmark Topic Watch Topic
    • New Topic