Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Disconnecting from DB after getting data

Posted on 2010-11-09
10
Medium Priority
?
357 Views
Last Modified: 2012-05-10
There are two classes:
 
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) {
        }
    }

Open in new window

which connecting to DB
and 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();
            }
      }
}

Open in new window

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.
0
Comment
Question by:gbcbr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
10 Comments
 
LVL 2

Expert Comment

by:Pacane
ID: 34094926
Isn't there a method called .close() on your connection object?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34094929
You need to call ConnectDB.destroy at the end of SelectData.run in a finally block
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34094952
>>public void destroy(Connection conn) {

The parameter is redundant and you should get rid of it
0
The top UI technologies you need to be aware of

An important part of the job as a front-end developer is to stay up to date and in contact with new tools, trends and workflows. That’s why you cannot miss this upcoming webinar to explore the latest trends in UI technologies!

 
LVL 86

Expert Comment

by:CEHJ
ID: 34095017
Or: leave things as they are and uncomment the call to destroy in ConnectDB.select, which you've commented out
0
 

Author Comment

by:gbcbr
ID: 34095055
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").equals("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.ClassCastException: lionfx.dbfx.db.ConnectDB cannot be cast to java.sql.Connection
        at lionfx.dbfx.db.SelectData.run(SelectData.java:47)
        at java.util.TimerThread.mainLoop(Timer.java:512)
        at java.util.TimerThread.run(Timer.java:462)
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34095065
Forget my last comment: it won't work
0
 

Author Comment

by:gbcbr
ID: 34095081
I know, because I try to put this destroy in all places of select, but it doesn't accept anywhere.
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 2000 total points
ID: 34095108
Try the attached
src.zip
0
 

Author Closing Comment

by:gbcbr
ID: 34095210
Perfect! Thank you very much for the help!
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34095394
:)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Suggested Courses

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question