This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes prepStat.executeUpdate() in a loop, any problem? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "prepStat.executeUpdate() in a loop, any problem?" Watch "prepStat.executeUpdate() in a loop, any problem?" New topic
Author

prepStat.executeUpdate() in a loop, any problem?

William Fernandes
Greenhorn

Joined: Jul 21, 2008
Posts: 16
Hi there guys.

I got this inconvenient problem, I'm trying to save lots of Objects that are in an ArrayList to the database. No sucess though.

Here's the code:



It's just saving the first object when I try two on the list, and none when I try only one object on the list.
Any solution?

Thanks anyway


SCJP 6
"A peaceful man stands tall" — "I tremble for my country when I reflect that God is just."
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30076
    
149

William,
Can you try adding a println (or debugger breakpoint) to see how many times cadastrarResponsavel() is being executed. I suspect there is an off by one error.

Not directly relevant to your problem, but consider using executeBatchUpdate() so you don't have to go to the database each time. Also, you'll want to close the PreparedStatement object.


[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
William Fernandes
Greenhorn

Joined: Jul 21, 2008
Posts: 16
Hi Jeanne,

thanks very much for your answer.
Yes I added some println statements, and when I got a list (the ArrayList) of 2 objects, it print 2 times, so it's being called twice. But only one is being saved effectively(only the first one). That's very strange to me.

Oh nice, I really like suggestions from the experts
I never tried using this executeBatch() method, is it the same? Instead of executeUpdate I put executeBatch() ?

I know in lots of applications they use Hibernate (and I'd really wanted to use...) but do you know any good "architecture" of this DAO thing. Something like creating a Singleton of Connection, etc. Something to optimize the performance.

Thank you once again.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30076
    
149

William,
If it is printing twice, it is surprising that it isn't saving twice. Two thoughts:
1) The two entries have different values right? One thing you can do is look at the result of executeUpdate(). It returns an int array with a value for each requested update.
2) Is it possible some other method is using ps since it is instance variable? That could mess things up.

Here's an example of batching:



Using executeBatch does optimize the use of the connection because it passes the data in one trip.
William Fernandes
Greenhorn

Joined: Jul 21, 2008
Posts: 16
Hmm, I tried that two items you suggested, no changes at all.

Both times it prints 1.
But still, only one line is saved in the database.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30076
    
149

William,
Can you try outputting the result of this.ps.executeBatch() or executeQuery(). This will tell you if there was an error on insertion or earlier in the process.
William Fernandes
Greenhorn

Joined: Jul 21, 2008
Posts: 16
Jeanne,

again it prints 1 and 1. It means it's doing correctly, I guess.

What I did find out is that it's not saving the last method I try to execute.

For example:
saveCustomer(cust);
saveLogin(login);
Login is never saved. The table stays there, unmodified.

But if try:
saveLogin(login);
saveCustomer(cust);
Customer stays the same.

Is there any explanation for this?
I guess I'm doing something wrong. Well, I'm not closing the connection, can be it? Or there's any method I should always use like flush()?
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30076
    
149

William,
It sounds like a problem with committing. Are you using auto-commit set the true (default) or false.

Either way - you should be closing both the prepared statement and connection. If you don't "unpredictable things may happen." I recommend adding in the close before troubleshooting further.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: prepStat.executeUpdate() in a loop, any problem?
 
Similar Threads
Could not locate DB driver
Datasource Connection
Error with user defined object datatypes
How to get particular method of DAO class through struts-config.xml and Action clas.
Insert into db does not work, no errors doh !?