You have two major programming issues:
1) You ignore exceptions
2) You use static variables / singleton pattern without understanding
Get yourself a good debugger, where you can step through the code
line-by-line, and watch what happens to various "connection" variables
during your two procedures. It will be obvious why and where you "released"
the original connection without closing it.
See notes below (in bold) to help you figure out where to focus your
efforts.
David J.
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of
Benjamin.Galaviz@LSGSkyChefs.com
Sent: Wednesday, June 08, 2011 1:37 PM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] PGPoolingDataSource problem.
private static Connection conn = null;
public Connection getConnection(){ ---- You are returning a static
variable from a non-static method...
PGPoolingDataSource pgdb = (PGPoolingDataSource)getInstance();
conn = null; ---- and what if there was already another
connection present..
try{
conn = pgdb.getConnection();
}catch(Exception e){
e.printStackTrace();
}
return conn;
}
public static void closeConnection(){
if (conn != null){
try{conn.close();conn = null;}catch(Exception e){} ----
NEVER IGNORE EXCEPTIONS; ESPECIALLY CATCH (Exception e)
}
}
try{DatabasePool.closeConnection();db=null;}catch(Exception e){}
---- NEVER IGNORE EXCEPTIONS; ESPECIALLY CATCH (Exception e)
!!!!!! You are likely capturing - then ignoring - a NullPointerException
here !!!!!!!!!
Private String getValue(String XX){
PreparedStatement ps =
db.getConnection().preparedStatement(sql);
ResultSet rs = ps.executeQuery();
While (rs.next()){
retVal = rs.getString(1);
}
try{DatabasePool.closeConnection();db=null;}catch(Exception
e){}
}
}
--- Open and close within the same function is OK IF there was no connection
already open
String myValue = getValue(XX); ----Open
and Close
PreparedStatement ps =
db.getConnection().preparedStatement(sql); --- Open
try{DatabasePool.closeConnection();db=null;}catch(Exception
e){} ----Close
