I implemented java web service and inside it, I have DbUtil class, which I am using in a thread to connect to Oracle Database. The issue is that it is giving me Closed Connection error when I deploy my web service on Tomcat.
1106] ERROR xac.atws.service - SQL exception, while inserting request to DB. EX :
java.sql.SQLRecoverableException: Closed Connection
at oracle.jdbc.driver.PhysicalConnection.prepareCall(PhysicalConnection.java:3672)
at oracle.jdbc.driver.PhysicalConnection.prepareCall(PhysicalConnection.java:3633)
at xac.ape.at.ws.DbUtil.insertListMwRequestToDb(DbUtil.java:410)
at xac.ape.at.ws.jaxb.list.AtListProcessor.run(AtListProcessor.java:34)
at java.lang.Thread.run(Thread.java:745)
To briefly explain what I am doing in DbUtil class is that I have public class members conn, dbUser, dbPass, connectionUrl which are populated by the class constructor. Then I am using these variables to open and close a new connection in every member function of the class. Image of the Implementation of the dbUtil class is attached in the link. DbUtil class.
And opening and closing connection in every member function like this: public DbUtil(String connectionUrlTemp, String dbUserTemp, String dbPassTemp) throws SQLException {
try {
//set the private connection variables
dbUser = dbUserTemp;
dbPass = dbPassTemp;
connectionUrl = connectionUrlTemp;
//initializing the connection
OracleDataSource ods = new OracleDataSource();
ods.setUser(dbUser);
ods.setPassword(dbPass);
ods.setURL(connectionUrl);
conn = ods.getConnection();
AtServiceImpl.loggerAtws.info("[SYSTEM] Successfully connected to DB");
AtServiceImpl.loggerAtws.debug("[SYSTEM] DB Connection URL : {}", connectionUrl);
} catch (SQLException e) {
AtServiceImpl.loggerAtws.error("[SYSTEM] Exception occured while initializing DB connection. EX : ", e);
}finally {
if (conn != null) {
conn.close();
}
}
}
public Hashtable<String, Integer> sysConfigPull(String systemName, String functionName) throws SQLException {
Hashtable<String, Integer> sysConfig = new Hashtable<>();
try {
//---- added this section ----
//initializing the connection
OracleDataSource ods = new OracleDataSource();
ods.setUser(dbUser);
ods.setPassword(dbPass);
ods.setURL(connectionUrl);
conn = ods.getConnection();
//---- end of added section ----
CallableStatement sqlStatement;
String SQLTEXT = "begin GETSYSCONFIG(?,?,?); end;";
sqlStatement = conn.prepareCall(SQLTEXT);
sqlStatement.setString(1, systemName);
sqlStatement.setString(2, functionName);
sqlStatement.registerOutParameter(3, OracleTypes.CURSOR);
ResultSet rs;
sqlStatement.executeUpdate();
rs = (ResultSet)sqlStatement.getObject(3);
while (rs.next()) {
sysConfig.put("RETRYCOUNT", rs.getInt("RETRYCOUNT"));
sysConfig.put("NOTICEID", rs.getInt("NOTICEID"));
AtServiceImpl.loggerAtws.debug("System Config option: RETRYCOUNT {}, NOTICEID {}",rs.getString("RETRYCOUNT"), rs.getString("NOTICEID"));
}
} catch (SQLException e) {
AtServiceImpl.loggerAtws.error("[SYSTEM] SQL Exception, load system configuration, EX:", e);
}finally {
if (conn != null) {
conn.close();
}
}
return sysConfig;
}
So I'm thinking that opening and closing connection in every member function might be causing the error. And if it so why would it be? Any help would be appreciated.