JAVA handling lots of JDBC pool connections

Question

I've a problem when creating a batch in JAVA, this is that i might have to call a certain connection every second to process one block of rows, reaching the limit of the pool size.

How can i handle lots of connections without increase pool size ?

using (var connection = ExternalDBConnectionManager.getConnection()){
        using (var prepStatement = connection.prepareStatement(queryToExecute)){
        ...
    }
}

This function is called every 100 rows or more, but for an example i'd like to run this every row (i.e 500 rows, one call for each) - (this means call this function 500 times with the same connection)

Answer 1

Universal Connection Pool (UCP) is a Java connection pool which is rich in features and is tightly integrated with Oracle RAC, Oracle DG, Oracle ADG database architectures leveraging high availability, failover and performance features.

It is a standalone ucp.jar that needs to be used with JDBC driver.
There are some whitepapers on UCP and its capabilities on OTN.

Answer 2

You should read up on connection pool to get an idea of how they work. There's hundreds of topics here on SO that pertain to this. A quick google search of "Understanding Java connection pools" brings up a small description of how one of the libraries might work: How Connection Pooling Works

Some more info on this SO thread: How to establish a connection pool

Basically, your client requests come in, you grab a connection from the pool, you do your sql operation, and when you close the connection, it returns it back to the pool. That's the high level.

If you want to combine that with batching, it gets a little trickier, since you would grab the data, insert it into the batch and then trigger the send/commit when you have some predetermined amount of data in the batch.

It really depends on what you want your clients to see, do they need acknowledgement when the record is committed?

With pooling, it's less likely that you will need to use batching, you just need to adjust the connection pool size for your environment.

There's a number of standalone pool libraries (C3PO and DBCP), and a number of the various services (like websphere) already have pooling built in. I believe the Oracle jdbc driver as the ability to do it for you.

Answer 3

You mentioned "batch". Usually batches don't use a connection pool which are reserved to OLTP applications. That's because a connection pool works well if connections are borrowed and released fairly quickly. Batches usually create their own connections which stay opened during the lifetime of the batch (which could be hours).