Java Oracle JDBC Connection Timeout

Question

I have developed some JSON web services using Servlets for my mobile app. I'm using (Oracle + Private Tomcat) hosting. I have one single class DBOperations.java which has a lot of static functions which are called in Servets for database operation. I use getConnection() method in each function to get Connection Object, create statement and execute queries. Issue is after some time connection get lost. I'm using the following code to re-establish the connection.

public static Connection conn;
Statement stmt;

public static Connection getConnection() throws SQLException {
    if (conn == null || conn.isClosed() ) {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(DB_URL, "username", "password");
            return conn;
        } catch (ClassNotFoundException | SQLException ex) {
        }
    } else {
        return conn;
    }
    return conn;
}

I'm unable to figure out how I can handle the timeout/closed connection issue as the above code isn't re-establishing the connection. I need to restart Tomcat to get it back in working state. Any suggestions or help is highly appreciated.

You should use a connection pool. A single static Connection object for the whole application sounds like it could lead to all kinds of concurrency issues. — Thilo, Aug 01 '14 at 06:13
http://stackoverflow.com/questions/2835090/how-to-establish-a-connection-pool-in-jdbc?lq=1 — Thilo, Aug 01 '14 at 06:14
@Thilo Problem is everything works fine for sometime, then if it stops working, a restart to server make it work again. Is there any other verification check that should be performed to check either it's dead or alive? — wali, Aug 01 '14 at 06:19
you could issue a "ping" SQL (select * from dual). Connection pools handle this for you. — Thilo, Aug 01 '14 at 06:20
A server restart fix that, which mean connection is re-established. I'm trying to understand the problem, how can I re-establish that without restarting server? — wali, Aug 01 '14 at 06:25

score 1 · Accepted Answer · answered Aug 01 '14 at 06:16

1

You must use connection pooling, And let Tomcat server to handle everything. Create a JNDI datasource to achieve the same and you will never face such issue.

answered Aug 01 '14 at 06:16

prashant thakre

5,061
3
26
39

score 0 · Answer 2 · answered Aug 06 '14 at 00:18

Used OraceDataSource for connection pooling and it's working perfectly.

public static OracleDataSource ods;

       public static Connection getConnection() throws SQLException {
            if (ods == null) {
                ods = new OracleDataSource();
                ods.setURL(DB_URL);
                ods.setUser("username");
                ods.setPassword("password");
            }
            return ods.getConnection();
        }

are you using the methods to close or free connection? – kayhan yüksel Dec 13 '16 at 09:14 — kayhan yüksel, Dec 13 '16 at 09:14

Java Oracle JDBC Connection Timeout

2 Answers2