We help IT Professionals succeed at work.

JDBC Transaction Handling in J2EE

grouper15
grouper15 asked
on
Medium Priority
702 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






Comment
Watch Question

CERTIFIED EXPERT

Commented:
>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.
CERTIFIED EXPERT
Top Expert 2006
Commented:
Also, it is much better to use a DataSource rather than a custom ConnectionPool if you can.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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

CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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
CERTIFIED EXPERT
Commented:
>>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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.