- Retry - I don't know if there are any issues with finally clauses - at least you should not wrap all close() in your finally block within _one_ try/catch clause. Use e.g. a static helper class which encloses every close() in a separate try/catch block. I have never had any issues by doing so - especially in case of buggy oracle jdbc drivers.
Jens | -----Original Message----- | From: [EMAIL PROTECTED] | [mailto:[EMAIL PROTECTED]]On Behalf Of Greg Davis | Sent: Tuesday, April 09, 2002 4:56 PM | To: Orion-Interest | Subject: JDBC try/catch Pitfall ***MUST READ*** | | | This deals with try/catch blocks and finallys. I always believed that "no | matter what" a finally block is always called. While this seems to remain | true, it does not seem to necessarily properly call the code within the | block. In the section below I have some code and explanation of how a | finally gets called, but does not execute the code within. | | There seems to be an issue with closing a JDBC connection if a method | returns in the middle of the try/catch block. It seems to call | the finally, | but does not close the connection. It does seem to call static | methods like | System.out.println() perfectly fine. I don't know if it being static has | anything to do with it. I haven't researched it too far since it | was really | hard to find. First off you have to have a Factory like a ConnectionPool | that spawns out inscances of an object for other classes to use. Then it | must need to have the user close it's connection at some point so the | Factory can control resources. The only example I really have on hand is a | JDBC connection pool in a J2EE app server. This is where the | problem occurd | for us. If the methods "returns" are moved outside the try/catch | block, the | Connection.close() happens properly and the ConnectionPool does not | complain. | | I realize the getColumnClose() is the better way to implement the code | ayway, but I wanted to show everyone this problem in case they | accidentally | do it in their code somewhere. Any returns in the try or catches | seemed to | make the finally not be properly executed. I seem to remember some wierd | rule about this at JavaOne last year, but my head is still racing from all | that information. :-) | | Any comments? | | package test; | | import java.sql.*; | import javax.sql.DataSource; | import javax.naming.*; | | /** | * <p>Title: TryCatchTest</p> | * <p>Description: This is a test application for try/catch problem. If | using | * a Connection Pool in JDBC and you try to "return" a value within a | try/catch | * that is inside a method, the finally of that try/catch is not | completely | run. | * It seems to go through the finally(I.E. Sytem.out.println() works, but | the | * Connection.close() is either never called or not executed. If the | "return" | * is moved to the outside of the catch block, the connection is properly | closed. | * <br> | * We found this because a J2EE server which had a connection pool was | telling | * us we were not closing our connections. I dn't know if there is some | rule that | * when a "return" has been called only static methods or something like | that can | * be called.</p> | * <p>Copyright: Copyright (c) 2002</p> | * <p>Company: Western Electronics </p> | * @author <a href="mail:[EMAIL PROTECTED]">Greg Davis</a> | * @version 1.0 | */ | | public class TryCatchTest | { | Context jndiContext; | | public TryCatchTest() | { | try | { | jndiContext = new InitialContext(); | } | catch(NamingException ne) | {} | } | | public String getColumnNoClose() | { | Connection conn = null; | PreparedStatement ps = null; | ResultSet rs = null; | try | { | String sql ="SELECT table_name FROM sequence"; | conn | =((DataSource)jndiContext.lookup("jdbc/mySourceDS")).getConnection(); | ps = conn.prepareStatement(sql); | rs = ps.executeQuery(); | if(rs != null && rs.next()) | { | //This return causes the ?non-static? code within the | finally to not | to be run. | return rs.getString("table_name"); | } | //same here, the finally ?non-static? code is not called. | return null; | } | catch(NamingException ne) | { | //same here, the finally ?non-static? code is not called. | return null; | } | catch(SQLException sqle) | { | sqle.printStackTrace(); | //The same problem occurs here. The finally ?non-static? | code is not | called | return null; | } | finally | { | //This system out works, but the conn.close() does not. | The only way | I | //know how to check this is to create a ConnectionPool yourself that | watches | //the connections it spawns out, or user a pre-built one | that does the | same. | //We use Orion 1.5.4 J2EE App server (free for development). if you | want | //to test this out. | System.out.println("Finally called in noClose()"); | try | { | if(rs != null) | rs.close(); | if(ps != null) | ps.close(); | if(conn != null) | conn.close(); | } | catch(SQLException sqle) | { | sqle.printStackTrace(); | } | } | } | | public String getColumnClose() | { | String returnValue = null; | Connection conn = null; | PreparedStatement ps = null; | ResultSet rs = null; | try | { | String sql ="SELECT table_name FROM sequence"; | conn | =((DataSource)jndiContext.lookup("jdbc/mySourceDS")).getConnection(); | ps = conn.prepareStatement(sql); | rs = ps.executeQuery(); | if(rs != null && rs.next()) | { | //This does call the finally | returnValue = rs.getString("table_name"); | } | | } | catch(NamingException ne) | {} | catch(SQLException sqle) | { | //This allso calles the finally if I break the quey | sqle.printStackTrace(); | } | finally | { | //This one works normally | System.out.println("Finally called in Close()"); | try | { | if(rs != null) | rs.close(); | if(ps != null) | ps.close(); | if(conn != null) | conn.close(); | } | catch(SQLException sqle) | { | sqle.printStackTrace(); | } | } | return returnValue; | } | | public static void main(String[] args) | { | TryCatchTest tct1 = new TryCatchTest(); | System.out.println("tct1.getColumnNoClose():" + | tct1.getColumnNoClose()); | System.out.println("tct1.getColumnClose():" +tct1.getColumnClose()); | } | }
