How to re use JDBC Connections?

Question

Im trying to display some data from Oracle DB. Its taking more than 4 sec to obtain the connection.To read the entire data it needs only 1-2 seconds.So how can i improve overall response time.

I tried in this way

public class ConnectionManager {

public static Connection getConnection() {

    Connection conn = null;
    try {

        OracleDataSource ods = new OracleDataSource();

        java.util.Properties prop = new java.util.Properties();
        prop.setProperty("MinLimit", "2");    
        prop.setProperty("MaxLimit", "10");    

        ods.setURL(DBProps.getProperty("oracle.url"));    

        ods.setConnectionCachingEnabled(true); 
        ods.setConnectionCacheProperties (prop);    
        ods.setConnectionCacheName("Cache");    

         conn = ods.getConnection(DBProps.getProperty("oracle.user"), DBProps.getProperty("oracle.password"));

    } catch (Exception e) {
        e.printStackTrace();
    }
    return conn;
    }
}

And tried in traditional way as well

public static Connection getConnection() {

    Connection conn = null;
    try {

        Class.forName("oracle.jdbc.driver.OracleDriver");
        conn = DriverManager.getConnection(DBProps.getProperty("oracle.url"), DBProps.getProperty("oracle.user"), DBProps.getProperty("oracle.password"));

    } catch (SQLException | ClassNotFoundException e) {
        e.printStackTrace();
    }
    return conn;

}

Also see: [High-Performance Oracle JDBC Programming](http://www.oracle.com/technetwork/articles/vasiliev-oracle-jdbc-090470.html) for details of Oracle's UCP (Universal Connection Pool). — MT0, Apr 20 '16 at 18:56

score 0 · Answer 1 · answered Apr 20 '16 at 18:48

0

If you can use Java EE eg Tom EE I would suggest investigating using a JDBC connection pool.

answered Apr 20 '16 at 18:48

ozOli

1,414
1
18
26

score 0 · Answer 2 · answered Apr 20 '16 at 19:05

If you are deploying your code into a webserver or application server try creating connection pool and reuse it between different DB call.

If you are running the code without any server container, try to cache the Connection object of your own as a static variable.

Or you can even think of using spring frame work as it will give you great transaction management with out any Application server

How to re use JDBC Connections?

2 Answers2