Oracle Connection Pool Class

Question

I want to setup a connection pool for a Oracle DB in a Helper class.

public class DbConnection {

// Data source for the pooled connection
private static OracleDataSource dataSource;

// Host
private static final String dbHost = "bla";

// Port
private static final String dbPort = "1521";

// DBname
private static final String database = "orcl";

// DBuser
private static final String dbUser = "bla";

// DBpassword
private static final String dbPassword = "bla";

static {
    OracleConnectionPoolDataSource opds;
    try {
        opds = new OracleConnectionPoolDataSource();
        opds.setURL("jdbc:oracle:thin:@" + dbHost + ":" + dbPort + ":"
                + database);
        opds.setUser(dbUser);
        opds.setPassword(dbPassword);
        dataSource = opds;
    } catch (SQLException e1) {
        System.err.println("Connection failed!");
    }
    try {
        // Load driver
        Class.forName("oracle.jdbc.driver.OracleDriver");

    } catch (ClassNotFoundException e) {
        System.out.println("Driver not found!");
    }
}

public static Connection getConnection() throws SQLException {
    return dataSource.getConnection();
}
}

This is working but it is not awfully fast so I think I'm missing something to get the pooling working. Any suggestions?

So my externel classes just invoke the getConnection() method ...

Connection conn = DbConnection.getConnection();
...
conn.close();

What exactly do you mean with "not awfully fast"? Which part exactly is slow? Opening a connection to Oracle can take some time. Did you check out the manual regarding connection caching? docs.oracle.com/cd/B28359_01/java.111/b31224/… — user330315
– user330315, Commented Sep 30, 2012 at 11:55
Read the link I posted. It says the "OracleConnectionCache" is deprecated, not the section I linked to. Btw: why don't you use a connection pool like DBCP or C3P0? — user330315
– user330315, Commented Sep 30, 2012 at 12:46

Community · Accepted Answer · 2017-05-23 11:54:38Z

2

You should not use a ConnectionPoolDataSource directly. It is intended for use by a connection pool in an application server. It does not provide connection pooling itself. See also https://stackoverflow.com/a/12651163/466862

In other words: You need to use an actual connection pool, like DBCP, c3p0 or BoneCP, or the UCP (Universal Connection Pool).

edited May 23, 2017 at 11:54

CommunityBot

11 silver badge

answered Sep 30, 2012 at 11:05

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

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

Comments

Gian Marco · Accepted Answer · 2016-11-28 15:02:21Z

2

oracle.jdbc.pool.OracleDataSource.setConnectionCachingEnabled documentation state the following:

Deprecated. Use Oracle Universal Connection Pool instead.

You can download Oracle Database UCP and create your DataSource as follows:

import oracle.jdbc.pool.OracleDataSource;
import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.PoolDataSourceFactory;

PoolDataSource poolDataSource = PoolDataSourceFactory.getPoolDataSource();
poolDataSource.setConnectionFactoryClassName(OracleDataSource.class.getName());
poolDataSource.setURL("jdbc:oracle:thin:@localhost:1521:XE");
poolDataSource.setUser("SYSTEM");
poolDataSource.setPassword("****");

answered Nov 28, 2016 at 15:02

Gian Marco

23.6k8 gold badges59 silver badges47 bronze badges

2 Comments

Andrew Over a year ago

Why would they deprecate something and then make you download what you're supposed to use...

Andrew Over a year ago

"We expect you to stop using this function and use this other function instead, which we have not provided with the language, for your inconvenience."

sideshowbarker · Accepted Answer · 2023-05-04 02:13:34Z

1

You need you to use OracleDataSource (not OracleConnectionPoolDataSource) and set setConnectionCachingEnabled(true).

     private  static OracleDataSource ods = null;
     ...
     static {
        System.out.println("OracleDataSource Initialization");
        try {
            ods = new OracleDataSource();
            ods.setConnectionCachingEnabled(true);
            ods.setConnectionCacheName("mycache");
            ods.setURL("jdbc:oracle:thin:@//server.local:1521/prod");
            ods.setUser("scott");
            ods.setPassword("tiger");
            Properties cacheProps = new Properties();
            cacheProps.setProperty("MinLimit", "1");
            cacheProps.setProperty("MaxLimit", "4");
            cacheProps.setProperty("InitialLimit", "1");
            cacheProps.setProperty("ConnectionWaitTimeout", "5");
            cacheProps.setProperty("ValidateConnection", "true");
            ods.setConnectionCacheProperties(cacheProps);

        }
        catch (SQLException e) {
            e.printStackTrace();
        }
    }
...
    public static Connection getConnection()
       throws SQLException {
      return ods.getConnection();
    }

edited May 4, 2023 at 2:13

sideshowbarker

89.2k30 gold badges219 silver badges216 bronze badges

answered Sep 30, 2012 at 13:52

RealHowTo

35.5k11 gold badges74 silver badges89 bronze badges

2 Comments

gausss Over a year ago

Eclipse tells me setConnectionCachingEnabled is deprecated?

RealHowTo Over a year ago

It's deprecated but it's still working fine with the current Oracle JDBC driver. If you are not comfortable with that fact then there are many alternatives like Proxool or C3PO.

Collectives™ on Stack Overflow

Oracle Connection Pool Class

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related