Re: [JBoss-user] Closing database connections

[Brian McSweeney]

Thanks Enrique, that's what I suspected. Could you tell me if it is better practice to pass the connection in to the method - for example:     //------------- session facade methods-----------------------------       /**      * @ejb.interface-method      * @ejb.transaction      *      type="NotSupported"      */     public Page searchProducts( String key, int start, int count )     {         try         {             Connection conn = getConnection( );             ProductDAO dao = new ProductDAO( );             return dao.findByKey( conn, "%" + key + "%", start, count );         }         catch( SQLException sql )         {             throw new EJBException( sql );         }         finally{             conn.close( );             }     }       private Connection getConnection()     {         try         {             InitialContext ic = new InitialContext();             DataSource     ds = ( DataSource )ic.lookup( JNDINames.DATASOURCE );             return ds.getConnection();         }         catch( Exception e )         {             throw new EJBException();         }     }                 //------------- DAO method -----------------------------                   public ProductDAO( )                 {                                     }       public Page findByKey( Connection conn, String key, int start, int count )         throws SQLException     {         PreparedStatement stmt = conn.prepareStatement ( SQL_FIND_BY_KEY,                 ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );           stmt.setString ( 1, key );         stmt.setString ( 2, key );         stmt.setString ( 3, key );           ResultSet rs = stmt.executeQuery (  );           return toPage ( rs, start, count );     }   thanks for your help, Brian     ----- Original Message ----- From: Enrique Rodr�guez To: [EMAIL PROTECTED] Sent: Thursday, June 12, 2003 11:12 AM Subject: RE: [JBoss-user] Closing database connections You have to close the connection in every methods       public Page findByKey( String key, int start, int count )         throws SQLException     {         PreparedStatement stmt = _cnn.prepareStatement ( SQL_FIND_BY_KEY,                 ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );           stmt.setString ( 1, key );         stmt.setString ( 2, key );         stmt.setString ( 3, key );           ResultSet rs = stmt.executeQuery (  );             Cnn.close()           return toPage ( rs, start, count );     }   So.. is not good idea to have the connection as a field of this class.   HTH       -----Mensaje original----- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] En nombre de Brian McSweeney Enviado el: jueves, 12 de junio de 2003 11:45 Para: [EMAIL PROTECTED] CC: Herve Tchepannou Asunto: [JBoss-user] Closing database connections   Hi all,   I'm writing a DAO and using the xpetstore application for some guidance. However, I think I may have found a bug.   The xpetstore uses a session facade to search for particular products. It contains two methods used for this - one to get the database connection, and the other to invoke the a DAO with the search parameters - really nice and simple:   //------------- session facade methods-----------------------------       /**      * @ejb.interface-method      * @ejb.transaction      *      type="NotSupported"      */     public Page searchProducts( String key, int start, int count )     {         try         {             ProductDAO dao = new ProductDAO( getConnection() );             return dao.findByKey( "%" + key + "%", start, count );         }         catch( SQLException sql )         {             throw new EJBException( sql );         }     }       private Connection getConnection()     {         try         {             InitialContext ic = new InitialContext();             DataSource     ds = ( DataSource )ic.lookup( JNDINames.DATASOURCE );             return ds.getConnection();         }         catch( Exception e )         {             throw new EJBException();         }     }   //------------- DAO method -----------------------------       public ProductDAO( Connection cnn )     {         _cnn = cnn;     }       public Page findByKey( String key, int start, int count )         throws SQLException     {         PreparedStatement stmt = _cnn.prepareStatement ( SQL_FIND_BY_KEY,                 ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );           stmt.setString ( 1, key );         stmt.setString ( 2, key );         stmt.setString ( 3, key );           ResultSet rs = stmt.executeQuery (  );           return toPage ( rs, start, count );     }     However, my question is - why isn't the database connection getting closed? Should it be getting closed?   thanks very much, Brian