Solved

JDBC Transaction Handling in J2EE

Posted on 2006-11-02
8
677 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
jason format for rest webservices and sub resourcing 1 89
Java Exception example issues 11 84
JSONSerializer.toJSON in JSP generating error 8 73
web application structure 18 77
This article outlines the process to identify and resolve account lockout in an Active Directory environment.
Data breaches are on the rise, and companies are preparing by boosting their cybersecurity budgets. According to the Cybersecurity Market Report (http://www.cybersecurityventures.com/cybersecurity-market-report), worldwide spending on cybersecurity …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

776 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