aspose file tools*
The moose likes JDBC and the fly likes Nested Insert/Updates in DAOs Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Nested Insert/Updates in DAOs" Watch "Nested Insert/Updates in DAOs" New topic
Author

Nested Insert/Updates in DAOs

Andres Delrotti
Ranch Hand

Joined: Aug 11, 2005
Posts: 140
Hi everyone hows it going?

We have an application that generates dynamic reports. It could also generate different report templates. For every type of report, there are sections, for every section there are items, for every times there are sub-items and other fields.

So a create/update report template method in our business object class looks like this:





public void createReportTemplate(ReportData data){

Connection con = datasourceProxy.getConnection()


..dao.savedata(data)

List list1 = data.getSections()

..iterate list1
{
Section section = (Section)list.get(i)
dao.saveSection(section)

List list2 = section.getItems()
...iterate list2
{
Item item = (Item)list2.get(i)
dao.saveItem(item)
}


con.commit();


}


The same pattern basically happens in the items and other sub-items/sections as it goes into the lower sub classification of parts. This causes performance issues when there are a lot of users trying to do this action. Would you suggest we do it in one big complex sql or divide each into separate DAOs like what we did here? All daos use the same con boject in the business method.

I'd appreciate any form of help. Thanks
Herman Schelti
Ranch Hand

Joined: Jul 17, 2006
Posts: 387
hi Andres,

do you close your connections? (I don't see in in your code)

Herman
Andres Delrotti
Ranch Hand

Joined: Aug 11, 2005
Posts: 140
Yes. I just forgot to write it after con.commit();
Herman Schelti
Ranch Hand

Joined: Jul 17, 2006
Posts: 387
Anres,

in general:
-1 update statement is faster than more update-statements
-if you do need more statements: using addBatch() is faster than using executeUpdate() for every statement
-PreparedStatement is faster than Statement

Do you use a connectionpool to get a connection
(if yes: how are the settings of the pool)

I don't see any exception-handling in your code: make sure you always close your connection (in a finally clause)

And then there's you database. It's possible that your database locks a whole table instead of a few rows. A DBA should be able help you with that.

I'm curious: how many concurrent users and how many sql-statements per user do you have when there are "a lot of users"?

Herman
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Nested Insert/Updates in DAOs