It's good practice to always put the rollback in the catch block and the close in the finally block. Never depend on the Connection to do this for you. It's not really because of defensive programming, it's to ensure the correctness of your data.
An easy way to not forget to do this is to create some kind of wrapper method that calls your implementation method. The impl. method will throw SQLException and not have any catch blocks for database problems. The wrapper method that called the impl. method catches SQLException and calls rollback. It also calls conn.commit() if successful and conn.close in the finally block. This reduces your JDBC code considerably. Dave >From: Bomb Diggy <[EMAIL PROTECTED]> >Reply-To: "Struts Users Mailing List" <[EMAIL PROTECTED]> >To: [EMAIL PROTECTED] >Subject: Proper exception handling of JDBC code? Need to call >connection.rollback()? >Date: Sat, 28 Sep 2002 16:40:57 -0700 (PDT) > >One of the many points I raised earlier was how to >properly close a database connection, and how that >might change depending on whether you were using >connection pooling and/or a DataSource. The example >JDBC code cited was that in the Struts 1.0.2 Javadoc: > >http://jakarta.apache.org/struts/doc-1.0.2/api/org/apache/struts/util/package-summary.html#doc.JDBC > >The part that looked a little funny to me and others >was the call to [myConnection].rollback() in the >catch{} block. I think I finally figured out why this >was put in - defensive programming. > >It seems to me that if *any* part of a transaction is >successful, then [connection].rollback() needs to be >called, else the next piece of application code that >got its hands on that connection object and completed >a transaction successfully would also complete the >previously-uncommitted transaction. > >For example, let's say your updateDB() method has two >separate method calls made within it that each update >a table. If only the first conn.executeUpdate() >method completes successfully, and the second >conn.executeUpdate() throws some kind of SQLException, >that transaction will *not* automatically be rolled >back. The driver won't do it. The database would do >it only when the connection is finally closed. > >But what if that connection is part of a connection >pool, and before the application server (and thus the >pool) goes down for the night, that updateDB() method >is used again, this time with success. Now, the >leftover transaction from the first failed call to >updateDB() has been committed along w/ any changes >from follow-on calls to updateDB(). All of this >happens, if your DataSource/Connection Pooling >Mechanism/Wrapped Connection Object isn't smart enough >to call [connection].rollback() for you. The >GenericConnection object that ships with Struts is >smart enough - and that's why I probably hadn't been >burned in the past while using Struts, and not calling >conn.rollback() myself. > >The GenericConnection object's first task in its >close() method is to call connection.rollback(). So, >as long as I call connection.close() before leaving my >updateDB() method, I'm good to go. > >/* Code snippet from Struts source */ >... >// Clean up any outstanding transaction as best we can >try { > conn.rollback(); >} catch (SQLException e) {} >... > >Hope all this is correct...don't want to mislead >people... > > >__________________________________________________ >Do you Yahoo!? >New DSL Internet Access from SBC & Yahoo! >http://sbc.yahoo.com > >-- >To unsubscribe, e-mail: ><mailto:[EMAIL PROTECTED]> >For additional commands, e-mail: ><mailto:[EMAIL PROTECTED]> _________________________________________________________________ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
