• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

What should be the design strategy for an update with Spring JDBC

 
shukla raghav
Ranch Hand
Posts: 203
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


I am myself surprised that this question never occurred to me before but this time i am bothered since i am creating my own application.

I have a TaskDetail class that has 30 fields. while updating there could be only a small part of TaskDetail that may be updated like just the "endDate" of the task. We can use two strategies to update this field.

APPROACH 1 : Update entire object with all the 30 fields and let the rest fields be overwritten in the database with the same value expect the changed field which will be updated with new value.
OR
APPROACH 2 : Update just the field that has been changed. In this case we would treat Employee as a DTO and just populate the "endDate" field with new value since it is the only field that needs to be changed.


Both the approaches seem to have its pros and cons

APPROACH 1 : (PROS) - Its a cleaner approach. (CONS) - We are unnecessarily over-writing 29 additional field for the sake of one field.

APPROACH 2 : (PROS) - We are just updating the fields that are modified (CONS) - Makes the DAO dirty looking because we would need 30 null checks to identify which of field have to be updated.

This problem is making me a little uncomfortable.

Which of this approach is an accepted one or there is a third approach? I am not very much in favor of using hibernate although prefer Spring JDBC template.
 
Surendra Kumar
Ranch Hand
Posts: 236
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't think there is anything JdbcTemplate can do here as it's just a thin wrapper over JDBC.
Approach 1 should not be used as it's not right to update all values just to avoid some extra lines of code.
Approach 2 is good.

Third approach? Design database well. See if you can reduce number of columns in table.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You also need to consider how the update is performed in SQL underneath e.g. if you update all the columns (not the PK which by definition should not change), then the database may also have to update any index entries for the updated columns, and keep track of a lot of pointless updates until you perform a commit. If multiple sessions are performing similarly inflated operations, you could stsrt to have problems. So make sure you are not forcing your database to perform a lot of unnecessary and resource-consuming updates.

Incidentally, there is nothing wrong with having a table with 30 columns, but you might want to look at how your object model represents them.
 
shukla raghav
Ranch Hand
Posts: 203
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, I have considered writing utility that dynamically generate SQL at runtime with variable number of placeholders. The number of placeholders would always be same as the number of non-null params. This way i can use APPROACH-2 abstracting the logic for dynamic query generation and null-checks. I will post the code as soon as it is done.

What i am surprised is that this is a pretty common use-case. How come i am unable to get some kind of a standard best practice for this ? how do you all handle such a situation. An update is mostly on partial data.

Wish to look for as many replies as possible



 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
shukla raghav wrote:How come i am unable to get some kind of a standard best practice for this ? how do you all handle such a situation. An update is mostly on partial data.

Well, I reckon only writing the data you need to write is the best practice here. Seems to me the only reason to update all the data every time is basically if you're too lazy to do the job properly. But I'd be interested to hear some counter-arguments from other people who know what they're doing with databases.
 
Surendra Kumar
Ranch Hand
Posts: 236
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Either you write the code, or use tools (in this case Hibernate) that write code for you.
 
Jayesh A Lalwani
Rancher
Posts: 2762
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For most databases, updating a complete record will be as costly as updating one field. Databases store data in segments, and accessing one piece of data generally means loading the whole segment. Secondly, any database worth its weight in salt will not index a column unless the column changes. It shouldn't matter if you updated the column with the data that it already has. The database should consider that a no-op.

The advantage of updating all fields is that it makes the code simpler. It allows you to decouple the code that determines what to change (business logic) from the code that actually saves the change (dao) .

Sure you can "optimize" the data access by putting some of your business logic in the Dao layer, or building some sort of signalling mechanism between the layers. In most cases, this will be an overoptimization.

There might be some cases where its benificial to update individual fields. Let's say you have a blob in a table. It makes sense to update the blob seperately.
 
shukla raghav
Ranch Hand
Posts: 203
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So Jayesh you mean to say that if the value in rest of the fields remain unchanged then there wont be any update operation for rest of the fields, Which to me does make some sense. In such a case we may for general purposes consider no additional cost associated with complete update. Do you have reference to any authentic resource that confirms the same. In that case it will save me lot of effort.

 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There will be some costs. Probably negligible for most purposes. Jayesh is probably right that most databases avoid unnecessary update of indexes, but the update statement is still completely logged in undo and redo logs in most databases, constraints on affected columns are evaluated and so on. Also, more data than necessary will travel over the network, and sometimes the network can be the bottleneck. But you almost certainly don't need to care unless your system needs to perform thousands of transactions per second, in which situation every little detail counts.

shukla raghav wrote:Do you have reference to any authentic resource that confirms the same.

This is certainly database dependent. Unless you use an obscure database, you should be able to find this information on the web.
 
Jayesh A Lalwani
Rancher
Posts: 2762
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Shukla,

You probably want to get a book that talks about the internals of the database that you are using. And you might want to prototype to be sure.

There will probably be more network overhead of updating the entire row. However, nowadays when you can have 10g networks, it's unlikely that network overhead will be anything but meager.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic