Oracle Connection Pool Class
Solution 1
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).
Solution 2
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();
}
Complete example here.
Solution 3
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("****");
gausss
Updated on June 04, 2022Comments
-
gausss almost 2 years
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();
-
gausss over 11 yearsEclipse tells me setConnectionCachingEnabled is deprecated?
-
RealHowTo over 11 yearsIt'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.
-
Andrew about 7 yearsWhy would they deprecate something and then make you download what you're supposed to use...
-
Andrew about 7 years"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."