Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

JDBC Transaction Handling in J2EE

Posted on 2006-11-02
8
Medium Priority
?
689 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
While opting for any web-to-print solution, you need to discuss with your team and some of your end users and know their opinions about your decisions. In this article we list down some questions you need to ask yourself.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

730 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