wood burning stoves 2.0*
The moose likes JDBC and the fly likes How to persist a Collection using JDBC? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to persist a Collection using JDBC?" Watch "How to persist a Collection using JDBC?" New topic
Author

How to persist a Collection using JDBC?

Mateus Lucio
Ranch Hand

Joined: Jul 27, 2006
Posts: 57
Hi folks, while coding I realized something interesting, I dont have a clue about how to persist a collection using JDBC

for exemple, to persist the object within a List, do I have to go through all of the objects and make a "creative" SQL statement to insert or update them?

considering that some of the objects may be already in the database and don't need to be updated

I'd appreciate if you guys pointed me a way out!
Thanks in advance!


Studying ...
Prabhu Venkatachalam
Ranch Hand

Joined: Nov 16, 2005
Posts: 502

Here is how CMP Entity Bean do persistence (this is one of the model. there may be lots available):

1. Lock the database rows.

Locking prevents users from reading data that is being changed by other users, and prevents multiple users from changing the same data at the same time. If locking is not used, data within the database might become logically incorrect, and queries run against that data might produce unexpected results.

2. Allow user to access and modify data.

3. After user done with his change, Container will update database and Release the Database rows it locked before.

Hope this gives some Idea to do persistence.


Prabhu Venkatachalam<br />SCJP 1.4,SCWCD 1.4<br />prabhu.venkatachalam@gmail.com
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41115
    
  45
Originally posted by Prabhu venkatachalam:
Here is how CMP Entity Bean do persistence (this is one of the model. there may be lots available):

1. Lock the database rows.

Locking prevents users from reading data that is being changed by other users, and prevents multiple users from changing the same data at the same time. If locking is not used, data within the database might become logically incorrect, and queries run against that data might produce unexpected results.

2. Allow user to access and modify data.

3. After user done with his change, Container will update database and Release the Database rows it locked before.


This doesn't sound right to me. Firstly, one should never lock a DB during a user interaction. The interaction could take a long time (minutes or more), or the user might even walk for away from lunch, thus prohibiting everyone else from changing those rows.
Secondly, locking generally doesn't prevent read access to the locked objects. It is the responsibility of the application to deal with a situation where 1) first user reads row x, 2) then second user reads row x, 3) then first user writes row x, and 4) now the second user is looking at an outdated row x, and should not be allowed to wrote it back to the DB without first reading it first.

Getting back to the original question, if you don't want to use some ORM tool like Hibernate (which would handle most of the issues for you), then, yes, you need to keep track of which objects are new and which ones are updated. You don't need to issue separate statements for each INSERT or UPDATE; research batch updating.


Ping & DNS - my free Android networking tools app
Prabhu Venkatachalam
Ranch Hand

Joined: Nov 16, 2005
Posts: 502

Ulf,



one should never lock a DB during a user interaction. The interaction could take a long time (minutes or more), or the user might even walk for away from lunch, thus prohibiting everyone else from changing those rows.



There is Time Out, which should be taken care in this case.



locking generally doesn't prevent read access to the locked objects. It is the responsibility of the application to deal with a situation where 1) first user reads row x, 2) then second user reads row x, 3) then first user writes row x, and 4) now the second user is looking at an outdated row x, and should not be allowed to wrote it back to the DB without first reading it first.



1) first user reads row x, 2) then second user reads row x, 3) then first user writes row x, and 4) now the second user is looking at an outdated row x There should be a logic to look whether data is updated, before user gets the Data

The above step I gave is just a three simple step to understand about persistence.

There might more than one application accessing the database. without Row Locking how would you prevent other application to write in DB?
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30123
    
150

There are two types of row locking:
1) Locking with a transaction - this is essentially what protects the rows from being written by someone else while still in a database transaction.
2) Pure row locking - even if the database supports this, it should be avoided at all costs due to the reasons described above.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Mateus Lucio
Ranch Hand

Joined: Jul 27, 2006
Posts: 57
Cool, batch update seems to make things a little easier.
Since I can't use an ORM tool in this project I'll have to do it by hand, but keep track of the objects that need to be updated will be easy (i just took a first look of how to do it).

I'll design some factories do create the objects retrieved from the database, then I can worry about objects instead of data in a resultset

By the way, i think locking the data only during a transaction is a best alternative!
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41115
    
  45
Prabhu venkatachalam said:
There is Time Out, which should be taken care in this case.

Yes it would, but that is a bad way to handle this, leaving data locked for too long. I would strongly advise against it.

There should be a logic to look whether data is updated, before user gets the Data

That doesn't help. The application must handle the case where data has been updated after the second user got the data. If the data got updated before the second user got it then there is no problem.

without Row Locking how would you prevent other application to write in DB?

Through transaction locking, which does not span user action; see Jeannes post.
[ November 12, 2006: Message edited by: Ulf Dittmer ]
 
jQuery in Action, 2nd edition
 
subject: How to persist a Collection using JDBC?
 
Similar Threads
ORM Mapping, Best practices?
Update in master table Child Table problem
Problem using html:option
Problem using html:option
How Portal stores user data using PortletData?