Oracle DB Connection Pooling in Multi Threaded Java Program

Question

I have a java process which is multi threaded using ExecutorService (15 threads). Each thread calls store procedure to insert data to table, my connection to be pooled across 15 threads so that I could see multiple commits on the table at the same time, but i only see one connection established for one active thread even through 15 threads are ready and waiting.

Driver: oracle.jdbc.driver.OracleDriver

Following are the connection details I have in my properties file url, username, password

Class.forName(DB_DRIVER); 

DataSource oracleDataSource = new DriverManagerDataSource(DB_CONNECTION, DB_USER,DB_PASSWORD); 

ObjectPool objectPool = new GenericObjectPool(); 

DataSourceConnectionFactory datasourceConnectionFactory = new DataSourceConnectionFactory(oracleDataSource); 

PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(datasourceConnectionFactory, objectPool, null, null, false, true); 
objectPool.setFactory(poolableConnectionFactory); 

PoolingDataSource datasource = new PoolingDataSource(objectPool)

Nirmala · Accepted Answer · 2015-02-06 23:36:04Z

2

Oracle has Universal Connection Pool (ucp.jar) which is easy to use and is from oracle. All you need is to include ucp.jar in the classpath along with ojdbc6.jar or ojdbc7.jar.

Refer to the UCP Reference Guide: http://docs.oracle.com/database/121/JJUCP/toc.htm

PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setMinPoolSize(10);
pds.setMaxPoolSize(50);
Connection conn=pds.getConnection();

answered Feb 6, 2015 at 23:36

Nirmala

1,3521 gold badge10 silver badges12 bronze badges

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

Comments

duffymo · Accepted Answer · 2015-02-03 21:13:42Z

1

You need a connection pool.

Either the object that manages the executor pool will check out a connection, give it to the ExecutorService, and close it when the task completes OR the ExecutorService will manage it. Make sure that you pay careful attention to connection management and SQL resource cleanup or you'll quickly have problems under high request volume.

Usually it's Java EE app servers that manage connection pools for you, but it sounds like you aren't using one. If that's true, perhaps Apache Database Connection Pool will suit your purpose.

answered Feb 3, 2015 at 21:13

duffymo

310k46 gold badges376 silver badges570 bronze badges

4 Comments

user3723562 Over a year ago

How do i set the connection when i am managing the connection using DriverManager.getConnection(url, username, password). Is it a different manger i need to use

duffymo Over a year ago

You won't be managing the connection that way. That's what gave you one connection. You'll tell the pool to create several connections (one per executor service in your pool?) and have them on hand. They give you an example to show you how: svn.apache.org/viewvc/commons/proper/dbcp/trunk/doc

user3723562 Over a year ago

Tried using the below code to establish connection and pooled across all threads

duffymo Over a year ago

Code doesn't belong in comments. Edit your original question to show what you're doing. I don't have much interest in doing this for you.

Collectives™ on Stack Overflow

Oracle DB Connection Pooling in Multi Threaded Java Program

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related