ResultSet.next() is taking too much time for few records

Question

I'm using OJDBC(v7) to connect to Oracle(11g), in Java. In some cases on some big tables, the Resultset can not fetch the data in appropriate time.

For example the output records are just 2, but on the Resultset.next() java freezes and waits too long!

Note1: The problem is not about setting FetchSize(), RsultSet.TypeX , not using connection pools like c3p0 , ... . I've tested all of those.

Note2: Also when I run the query directly in navicat, the result is shown perfectly!

Getting connection method:

public Connection getDBConnection() throws DBConnectionException {
        Connection conn = null;

        String connectionUrl;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:user/[email protected]:1521:DBNAME");

        } catch (Exception e) {
            e.printStackTrace();
            throw new DBConnectionException();
        }

        return conn;
    }

connectiong to DB part:

    ...
    conn = connectionManager.getDBConnection();
    conn.setAutoCommit(false);
    String query = "{call ...(...)}";
    CallableStatement stmt = conn.prepareCall(query,ResultSet.TYPE_FORWARD_ONLY,
                    ResultSet.CONCUR_READ_ONLY);
    stmt.setFetchSize(10000); 
    .
    .
    .
    stmt.registerOutParameter(x, OracleTypes.CURSOR);
    stmt.execute();
    Resultset rs = (ResultSet) stmt.getObject(x);

    while (rs.next()) {    /** Problem occurs here **/
        ...
    }

WHY?!

Answer 1

use in try catch block:

if ((cnn==null)||cnn.isClosed()){
    cnn=DB.getDBConnection(); //e.g. DB is instance of class where getDBConnection() resides
}

Then call queries. I believe somewhere in your code either you are closing connection or connection is becoming null that's why you are facing this problem.

Cheers