File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes jdbc transaction mangement and behaviour Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "jdbc transaction mangement and behaviour" Watch "jdbc transaction mangement and behaviour" New topic
Author

jdbc transaction mangement and behaviour

stefano bitossi
Greenhorn

Joined: Oct 06, 2010
Posts: 5
Hello

i'm working on an old application that were using jta for transactions, i not very experienced with java and i don't know jta. Having a short time to complete the task (as usual) and being present only a few simple transaction operations, i have preferred the jdbc setAutocommit(false) method. Yet i have some doubts about connections and transaction enlisting. The scenario is : a method starts a transaction to do a couple of insert with conn1, between conn1.setAutocommit(false) and conn1.commit() a couple of helper methods get called which access the database to read some other data, using conn2 . The question is will conn2 be part of conn1 transaction or will be an indipendent world ? how can i make conn2 be part of conn1's trasaction ? should i use conn1 into the two methods too ? what if inside one of the methods i need to do more nested queries related to master resultset ? below i've added some sample (not real ) code .
Thank you in advance

Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38472
    
  23
Welcome to JavaRanch

That question would sit better on our databases forum, so I shall move this thread.
Abhishek Ralhan
Ranch Hand

Joined: Aug 01, 2010
Posts: 40

Why don't you pass conn1 as argument when you call helper methods??
A new connection according to me is not required at all..


-Abhishek
I came to this world on a Learner's License
Sandeep Sanaboyina
Ranch Hand

Joined: Dec 14, 2009
Posts: 72
The question is will conn2 be part of conn1 transaction or will be an indipendent world ?
Both are independent.
how can i make conn2 be part of conn1's trasaction ?
As Abhishek said, pass conn1 as argument and use it.
should i use conn1 into the two methods too ?
Yes, if that is what you want.
what if inside one of the methods i need to do more nested queries related to master resultset ?
I don't get this, you want to run a query on a resultset. I don't think this is possible.
But in all this, keep in mind that you are opening a single connection to do all the transactions and not commiting/rolling back the previous transactions will hold locks on the DB. It is not advisable to hold them for too long. Keep this in mind especially if your methods take too long to complete.


They say you have to be the first, the best or different. I say, is it too much to ask for all three.
stefano bitossi
Greenhorn

Joined: Oct 06, 2010
Posts: 5
Sandeep Sanaboyina wrote:
what if inside one of the methods i need to do more nested queries related to master resultset ?
I don't get this, you want to run a query on a resultset. I don't think this is possible.
But in all this, keep in mind that you are opening a single connection to do all the transactions and not commiting/rolling back the previous transactions will hold locks on the DB. It is not advisable to hold them for too long. Keep this in mind especially if your methods take too long to complete.


i mean i get a resultset from the 1st query , loop over the records and for each of them eventually make another query using a different connection. Since it's already happened to me to have an error about having to many opened ursors (oracle), due to using the same connection to execute multiple statement while looping on a large resultset, i tend to use a new connection for each query i send to db. I whished to know what's the best strategy in this case : in this case would be to retrieve large resultset outside the transaction , build some java collection and then do the updates/inserts into the transaction ?

thank you for your answers
Sandeep Sanaboyina
Ranch Hand

Joined: Dec 14, 2009
Posts: 72
i mean i get a resultset from the 1st query , loop over the records and for each of them eventually make another query using a different connection. Since it's already happened to me to have an error about having to many opened ursors (oracle), due to using the same connection to execute multiple statement while looping on a large resultset, i tend to use a new connection for each query i send to db. I whished to know what's the best strategy in this case : in this case would be to retrieve large resultset outside the transaction , build some java collection and then do the updates/inserts into the transaction ?
You can do this with a single connection. As you said you can loop over the records of the first resultset and for each record execute another statement. If implemented properly, you will not get the too many open cursors error. You might have got that error because you were creating the Statement/PreparedStatement inside the loop.

Can't say anymore on this without seeing your code.
Sandeep Sanaboyina
Ranch Hand

Joined: Dec 14, 2009
Posts: 72
Also, as you mentioned it is a large result set, it might not be a good idea to store them in collections and processing them as it will eat up a lot of your heap space.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: jdbc transaction mangement and behaviour