gbcbr
asked on
Disconnecting from DB after getting data
There are two classes:
and SelectData which extract data from this DB:
The problem is that when ConnectDB connecting to Oracle and get data it doesn't close sessions, and for next query open new session, so in 3 hours it's open all 300 sessions and stack Oracle.
I try to call destroy(conn); like in insert method but when I put it above return rs; I have error - Closed connection.
When I put it after - unreachable statement.
Please advice how to close connection for select method after each session.
public class ConnectDB {
private static String databaseurl = "jdbc:oracle:thin:@10.1.1.7:1521:orcl";
private static String user = "LIONFX";
private static String password = "";
private static String driverName = "oracle.jdbc.OracleDriver";
Connection conn;
ResultSet rs;
Statement statement;
PreparedStatement ps;
public void insert(String sql) throws SQLException {
try {
Class.forName(driverName).newInstance();
conn = (Connection) DriverManager.getConnection(databaseurl, user, password);
statement = (Statement) conn.createStatement();
int st = statement.executeUpdate(sql);
destroy(conn);
statement.close();
} catch (InstantiationException e) {
} catch (IllegalAccessException e) {
} catch (ClassNotFoundException e) {
}
}
public ResultSet select(String sql) throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
Class.forName(driverName).newInstance();
conn = (Connection) DriverManager.getConnection(databaseurl, user, password);
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
// destroy(conn);
return rs;
}
public void destroy(Connection conn) {
try {
conn.close();
} catch (SQLException e) {
}
}
which connecting to DBand SelectData which extract data from this DB:
public class SelectData extends TimerTask {
private ConnectDB conn = new ConnectDB();
String symbol[] = new String[3];
double openBid[] = new double[3];
double openAsk[] = new double[3];
Timestamp timest[] = new Timestamp[3];
public void run() {
try {
ResultSet rs = conn.select("SELECT * FROM EURUSD_1C where timest = ( select max( timest ) from EURUSD_1C ) and rownum = 1 union all "
+ "SELECT * FROM EURJPY_1C where timest = ( select max( timest ) from EURJPY_1C ) and rownum = 1 union all "
+ "SELECT * FROM USDJPY_1C where timest = ( select max( timest ) from USDJPY_1C ) and rownum = 1");
while (rs.next()) {
if (rs.getString("SYMBOL").equals("EUR/USD")) {
symbol[0] = rs.getString("SYMBOL");
timest[0] = rs.getTimestamp("TIMEST");
openBid[0] = rs.getDouble("BIDPX");
openAsk[0] = rs.getDouble("ASKPX");
} else if (rs.getString("SYMBOL").equals("EUR/JPY")) {
symbol[1] = rs.getString("SYMBOL");
timest[1] = rs.getTimestamp("TIMEST");
openBid[1] = rs.getDouble("BIDPX");
openAsk[1] = rs.getDouble("ASKPX");
} else if (rs.getString("SYMBOL").equals("USD/JPY")) {
symbol[2] = rs.getString("SYMBOL");
timest[2] = rs.getTimestamp("TIMEST");
openBid[2] = rs.getDouble("BIDPX");
openAsk[2] = rs.getDouble("ASKPX");
}
else {
System.out.println("No new rows");
}
}
rs.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
For insert method it close sessions well.The problem is that when ConnectDB connecting to Oracle and get data it doesn't close sessions, and for next query open new session, so in 3 hours it's open all 300 sessions and stack Oracle.
I try to call destroy(conn); like in insert method but when I put it above return rs; I have error - Closed connection.
When I put it after - unreachable statement.
Please advice how to close connection for select method after each session.
Isn't there a method called .close() on your connection object?
You need to call ConnectDB.destroy at the end of SelectData.run in a finally block
>>public void destroy(Connection conn) {
The parameter is redundant and you should get rid of it
The parameter is redundant and you should get rid of it
Or: leave things as they are and uncomment the call to destroy in ConnectDB.select, which you've commented out
ASKER
Please give me more details where to place conn.destroy(conn); into Select Data
because it doesn't accept this statement.
} else if (rs.getString("SYMBOL").eq uals("USD/ JPY")) {
symbol[2] = rs.getString("SYMBOL");
timest[2] = rs.getTimestamp("TIMEST");
openBid[2] = rs.getDouble("BIDPX");
openAsk[2] = rs.getDouble("ASKPX");
conn.destroy((Connection) conn);
java.lang.ClassCastExcepti on: lionfx.dbfx.db.ConnectDB cannot be cast to java.sql.Connection
at lionfx.dbfx.db.SelectData. run(Select Data.java: 47)
at java.util.TimerThread.main Loop(Timer .java:512)
at java.util.TimerThread.run( Timer.java :462)
because it doesn't accept this statement.
} else if (rs.getString("SYMBOL").eq
symbol[2] = rs.getString("SYMBOL");
timest[2] = rs.getTimestamp("TIMEST");
openBid[2] = rs.getDouble("BIDPX");
openAsk[2] = rs.getDouble("ASKPX");
conn.destroy((Connection) conn);
java.lang.ClassCastExcepti
at lionfx.dbfx.db.SelectData.
at java.util.TimerThread.main
at java.util.TimerThread.run(
Forget my last comment: it won't work
ASKER
I know, because I try to put this destroy in all places of select, but it doesn't accept anywhere.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect! Thank you very much for the help!
:)