Hi guys! I was wondering... I've used this method before in Connection object.
//do some stuff
This was possible with a single Connection alive. But what if you're using a Connection pool? And between the setAutoCommit(false) and the commit() method, we can't be sure if there would be any other Connection object being used. Is Connection's autoCommit() effective only on the object you called it in? Or would it be safe to use it for the rest of the transaction, even if other Connection objects are used? Thanks!
I think its only for that connection object on which the auto commit has been called. Because by default if we get a new connection its in auto commit mode. In the sun specs its given as follows:
public void setAutoCommit(boolean autoCommit) throws SQLExceptionSets this connection's auto-commit mode. If a connection is in auto-commit mode, then all its SQL statements will be executed and committed as individual transactions. Otherwise, its SQL statements are grouped into transactions that are terminated by a call to either the method commit or the method rollback. By default, new connections are in auto-commit mode. The commit occurs when the statement completes or the next execute occurs, whichever comes first. In the case of statements returning a ResultSet, the statement completes when the last row of the ResultSet has been retrieved or the ResultSet has been closed. In advanced cases, a single statement may return multiple results as well as output parameter values. In these cases the commit occurs when all results and output parameter values have been retrieved. Parameters: autoCommit - true enables auto-commit; false disables auto-commit. Throws: SQLException - if a database access error occurs
Hope you have understood.
[changed code tag to quote so doesn't wrap page] [ February 21, 2007: Message edited by: Jeanne Boyarsky ]
Joined: Sep 18, 2005
Oh no... How can I possibly achieve atomicity here then? I need to rollback if an error ever occurs...
To use database transactions, you have to do all the operations of the transaction on a single Connection object. No other threads should try to use that Connection at the same time, unless they want to participate in the same transaction.
Once that transaction has finished (rollback or commit), you can use the same Connection for another transaction, and for efficiency, you would often wish to do so, rather than close that Connection and open another.
If you're using connection pools from javax.sql, the PooledConnection.getConnection() method is supposed to return you "a temporary handle" to refer to the real physical Connection underneath. You are supposed to be able to call close() on this Connection, without necessarily closing the underlying real physical Connection. The implementation of the temporary Connection ought to ensure that it behaves as if it were a real physical Connection, from the client code's perspective; this should include transactional behaviour. If the pool is properly implemented, you shouldn't find your code getting messed-up by transactions that some previous temporary Connection performed on the same real physical Connection.
That said, the noddy pool that I just wrote doesn't quite achieve these things. Don't buy a connection pool from me!
Betty Rubble? Well, I would go with Betty... but I'd be thinking of Wilma.
Joined: Sep 18, 2005
Thanks! Hmmm... I think we're in a lot of trouble now...
rohini gundu, could you please try to edit your post(the java doc part) so it would be easier to read? Thanks!
Joined: Sep 18, 2005
So I where should I be getting connection from the pool? I did this for every method in my DAO.
As you can see, for every method I try to acquire a Connection if there's not already one available.
It seems like, based on your point... My idea of doing something like this in the controller is wrong...
Should I get a reference to a Connection first, in case I have to make multiple transactions? Like this?
Awwwww... Were we doing it all wrong all these time...!?
I've used the Spring-framework for situations like yours:
-it has classes to get and automatically release connections, statements en resultsets. -you can define that a method is part of a transaction instead of having to code commits or rollbacks yourself. -Spring's Exception handling is better than standard SQLExceptions