aspose file tools*
The moose likes JDBC and the fly likes update/ or delete & insert, question of attitude Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "update/ or delete & insert, question of attitude" Watch "update/ or delete & insert, question of attitude" New topic
Author

update/ or delete & insert, question of attitude

Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
Hi guys,
I have a question of attitude:
I have a list of empl (group, name, address) and they all belong to a group (eg Accounting).

GUI: the user sees on the screen a list of all empl belong to accounting (there are ~ 50) now he wants to click update (cause for some of them he made some changes in address)

DB: Should he do this:
(1) *delete* all accounting group AND *insert* all NEW empl to NEW accounting
OR
(2) check if the group exist � if yes � *update*, no � insert.

It will be logic to perform number 2 but I just wonder why? Is it expensive to do insert? Is it bad programming?

Thanks for any pointers.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

Yes, it is expensive and it is bad programming to #1 although surprisingly I have seen #1 actually done in practice. It's usually discovered when long numeric have reached their overflow limit (which in most systems should be impossible) or when performance grinds to a halt (due to constant record locking). And depending on the size of the table and the number of indexes it could be extremely expensive.

While it may be far easier to implement #1, since its a lot closer to saving/loading a data file, its not maintainable in the long term from a debugging perspective (how do you debug a problem with record 491 if record 491 was deleted?), nor does is even allow concurrency (what if one user was reading data while the other was erasing all the records).

Again, if your system has data access patterns similar to that of a file system, then it doesn't make sense to even use a database other than to enable users to find lots of bugs when they try to open more than one user at once.


My Blog: Down Home Country Coding with Scott Selikoff
Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
Scott thanks for the quick educated reply.
I'll implement number 2.
Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
mmmm...here's something to consider:

1. When user deletes an empl from the list
2. When user adds an empl to the list in the middle (and order is important)

I am using an arraylist to store all empls (location is important)
so it looks like this:

1. update: update groupX set name=?, title=?... where groupName=? and ID=id
2. loop thru arraylist
3. get empl
4. executeUpdate

1. what happens if the arraylist doesnt contain an empl (the user removed him) how will I perform delete?

2. what happens if the user adds another empl to the list in the middle - for this I'll have to add anohter field - index.

any idea?
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

Hi Peter,

What you're referring to is trying to precisely store a java object in a database. The thing is, there's no 1-to-1 mapping. Generally in a database you never have all of the data (imagine constantly keeping track of millions of records in a single array), therefore the comparison you gave isn't really applicable to databases.

You did mention inserting at random parts of the list which in a way relates primary key generation. Most times, a database gives a primary key (think of it as your array index in this situation) for every new record created. The thing about databases though, is that the condition is usually such that the key be unique, order is not important. So you may have records 1-5 (6 was deleted), 7-10, 194, 3958, etc. The common situation, though, is to use ever increasing keys so that higher numbers imply (but never guarentee) at least that many records have been by the system over the lifetime.

And yes, users can try to insert two records with the same primary key (or array index in your case). That's a non-trivial scenario with a number of solutions. The simplist is you let the database enforce the constraint (the db will never allow two records with the same key so one user will fail and the other will suceed). Another solution is to manage the primary key on the application server and have a static synchronized object or service that gives out only one key at a time.

Good primary key management can be hard especially in gigantic systems. Often times, recovery is the key (if one user fails and one succeeds,,, well then lets try the user that failed 2-3 more times) but then you have starvation and livelock issues best left to the performance forum...
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: update/ or delete & insert, question of attitude