Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

JDBC Transaction Handling in J2EE

Posted on 2006-11-02
8
Medium Priority
?
691 Views
Last Modified: 2008-01-09
i am proficient in normal jdbc handling with out connection pooling

     

The structure is like this

                                          JDBC TRANSACTION WITHOUT POOL
                                          ------------------------------------------
try {
con.setAutoCommit(false)

stmt1.execute
stmt2.execute

con.commit()
}
catch(SQLException sqe)
{
con.rollback()
}
finally {
con.close()
}


                                          JDBC TRANSACTION WITH POOL
                                          --------------------------------------


Now when i came across transaction handling with database connection pool i got this code which is making me confuse a bit



try {
con = ConnectionPool.getConnection()   // custom method that returns a pooled connection
con.setAutoCommit(false)

stmt1.execute.....
stmt2.execute.....

con.commit()
}
catch(SQLException sqe)
{
con.rollback()
}
finally {
con.setAutoCommit(true)
con.close()       // return connection to pool

}


Now the statement that worries me here is

con.setAutoCommit(true)

Is the above statement necessary that it makes the "con" behavior back to default i.e auto commit true. Because i had read somewhere that con.setAutoCommit(true) impose an implicit commit so is it feasible to commit the transaction in finally . If i dont use con.setAutoCommit(true) what would be the status of connection when it is returned to pool and then get back from it to use in another transaction will it be autocommit false or true

Please suggest






0
Comment
Question by:grouper15
  • 3
  • 2
8 Comments
 
LVL 10

Expert Comment

by:mbvvsatish
ID: 17865046
>Because i had read somewhere that con.setAutoCommit(true) impose an implicit commit
it is correct
>so is it feasible to commit the transaction in finally.
No, since finally will be executed even if there is an exception in your code.

>If i dont use con.setAutoCommit(true) what would be the status of connection when it is returned to pool and then get back from it to use in another transaction will it be autocommit false or true

We restore autoCommit after using the connection for a create, delete, update, or find because other code might be using the same DataSource object to get connections and might depend on the autoCommit flag being set a certain way.  We set it to false and then restore it when we're done with the connection.
0
 
LVL 29

Accepted Solution

by:
bloodredsun earned 252 total points
ID: 17865092
Also, it is much better to use a DataSource rather than a custom ConnectionPool if you can.
0
 
LVL 3

Author Comment

by:grouper15
ID: 17879094
mbvvsatish
=======

If i remove con.setAutoCommit(true) from finally where should i place it in the above snippet

if you are saying that i put it after con.commit() then if the transaction fails and exception occurs in that case con.setAutoCommit(true) is never executed and the con is returned to pool with its AutoCommit property set to false. ???


bloodredsun
========

Why and how should i use DataSource in this case .
Remember i am not using custom connection pooling but the connection pool of Oracle Application Server itself

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:mbvvsatish
ID: 17879154
No. you should not move con.setAutoCommit(true) from finally block.
what i said above was about con.commit, you should not commit the transaction in finally.
because your question says
>so is it feasible to *commit* the transaction in finally.

if it moved from finally block and placed it after con.commit then what ever you said above is right.

>Why and how should i use DataSource in this case .
>Remember i am not using custom connection pooling but the connection pool of Oracle Application Server itself

Datasource should be created in the application server that you are using to deploy your j2ee application.
for this first you need to create a connection pool to your oracle database from your applcation server and then point this connection pool while creating the datasource and give it a name, which you can use in your code to get the connection.
0
 
LVL 3

Author Comment

by:grouper15
ID: 17936145
i am doing exactly the same thing regarding data sources by creating in app server in a xml file accompanied by a connection pool


One last thing in my mind if i get a connection at random from pool and set its autoCommit to false and return it back to pool. Now if by chance the same connection is get from pool will its autocommit property persist as false or the pool always give a refreshed connection with autocommit true
0
 
LVL 10

Assisted Solution

by:mbvvsatish
mbvvsatish earned 248 total points
ID: 17936192
>>will its autocommit property persist as false or the pool always give a refreshed connection with autocommit true

its autocommit property persist as false, it doesnt give a refreshed connection.
a connection pool is nothing but a set of connections stored in some collection. whenever you request for a connection it picks from the collection and returns to you. once you release it goes back to the collection where it is being stored.
so the autocommit property of that pirticular connection should retain.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Transferring FSMO roles is done when an admin wants to split roles between certain Domain Controllers or the Domain Controller holding the Roles has been forcefully demoted using dcpromo / forceremoval
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question