Solved

JDBC Transaction Handling in J2EE

Posted on 2006-11-02
8
680 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 63 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 62 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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

Learn how to PXE Boot both BIOS & UEFI machines with DHCP Policies and Custom Vendor Classes
In threads here at EE, each comment has a unique Identifier (ID). It is easy to get the full path for an ID via the right-click context menu. However, we often want to post a short link within a thread rather than the full link. This article shows a…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

830 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