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
– Thilo, Commented Aug 1, 2014 at 6:13
@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
– wali, Commented Aug 1, 2014 at 6:19
you could issue a "ping" SQL (select * from dual). Connection pools handle this for you. — Thilo
– Thilo, Commented Aug 1, 2014 at 6: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
– wali, Commented Aug 1, 2014 at 6:25

prashant thakre · Accepted Answer · 2014-08-01 06:16:37Z

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 1, 2014 at 6:16

prashant thakre

5,1753 gold badges28 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

wali · Accepted Answer · 2014-08-06 00:18:04Z

0

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();
        }

answered Aug 6, 2014 at 0:18

wali

2394 gold badges8 silver badges21 bronze badges

1 Comment

kayhan yüksel Over a year ago

are you using the methods to close or free connection?

Collectives™ on Stack Overflow

Java Oracle JDBC Connection Timeout

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related