Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle JDBC UCP and Java Connection Pooling experiment.

mlov83Apr 21 2017 — edited Apr 26 2017

I'm wondering if anyone can shed some light on this topic, as I have been racking my brain for days and can't quite understand why this does not work. I have three classes, each class with a method main, RetrieveDBVersion,GetOracleConnection .I've been doing some testing with oracle JDBC, UCP and Java 1.7. According to the Oracle documentation, If I use connection pooling the connection will be returned to the pool as soon as I close the connection, Invalidate it and set it to null See Here. So I decided to give it a whirl and see if it would perform just like the documentation says it should. In my Main application I have a simple loop which makes a connection 200 times by calling RetrieveDBVersion. RetrieveDBVersion is simply performing a query and returning the driver version. My loop works fine until I hit the magic number of 68 and then I receive an error which states

java.sql.SQLException: Exception occurred while getting connection:  
oracle
.ucp.UniversalConnectionPoolException:  
Cannot get Connection from Datasource: java.sql.SQLException:
Listener refused the connection with the following error:
ORA
-12516, TNS:listener could not find available handler with matching protocol stack

Now, please be mindful that this is not production ready code I'm simply testing a concept and trying to understand if it works as I think it works. I have tried closing the connection and not invalidating it and get the same results I have also tried creating a singleton of the connection and than retrieving the connection from the singleton and that worked fine. Though, I think creating a singleton defeats the purpose of pooling.  If I were to create a web app and have a `getConnection()` every time a user logs in, would I not run out of available connections after 68 users?

These are the detail of the 3 methods. These methods are not in a server environment. They are simply calling a local oracle express database and I'm running them from my desktop. Why would I keep getting this error? If I'm returning the connections back to the pool?

  1. Main

import com.jam.DB.JDBCVersion;
import static java.lang.System.out;
public class MainApp {

  
public static void main(String[] args) {
  
String myMainJDBCVar;
  
try{

  
for(int i=1; i<200; i++ )
  
{
  myMainJDBCVar
= JDBCVersion.RetrieveDBVersion();
  
out.println(myMainJDBCVar + " " + i);
  
}

  
out.println("this is Done!");

  
}
  
catch (Exception e) {
  
System.out.println(e.getMessage());
  
}
  
}
}
  

2.RetrieveDBVersion

import java.sql.*;
 
import oracle.ucp.jdbc.ValidConnection;
 
public class JDBCVersion {

 
public static String DBVersion;

 
public static String RetrieveDBVersion()throws SQLException {

  
Connection  conn = JDBCConnection.GetOracleConnection("test");

  
try {
  
DatabaseMetaData meta = conn.getMetaData();
  
//get driver info
  
System.out.println("JDBC driver version is " + meta.getDriverMajorVersion());
  
DBVersion = meta.getDriverVersion();

  
} catch (SQLException e) {
  e
.printStackTrace();
  
DBVersion = e.getMessage();
  
}
  
finally {

  
System.out.println("hit the finally clause");
  
((ValidConnection) conn).setInvalid();
  conn
.close();
  conn
=null;
  
}
  
return DBVersion;
}

3.GetOracleConnection

import oracle.ucp.jdbc.PoolDataSource;
 
import oracle.ucp.jdbc.PoolDataSourceFactory;
 
import java.sql.*;
public class JDBCConnection {

public static Connection GetOracleConnection(String Enviroment) throws SQLException{


  
PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();

  
Connection conn = null; //ora.defaultConnection();
  
try {


  pds
.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
  pds
.setURL("jdbc:oracle:thin:@//localhost:1521/xe");
  pds
.setUser("system");
  
//pds.setInitialPoolSize(5);
  pds
.setPassword("xxx");
  pds
.setMaxStatements(10);

  conn
= pds.getConnection();

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

This post has been answered by unknown-7404 on Apr 21 2017
Jump to Answer

Comments

unknown-7404
Answer

Thanks for providing the detailed info - wish more people would do that. That is what made it easy to help you.

According to the Oracle documentation, If I use connection pooling the connection will be returned to the pool as soon as I close the connection, Invalidate it and set it to null See Here

.Correct - the issue is that your code is NOT really using connection pooling.

In my Main application I have a simple loop which makes a connection 200 times by calling RetrieveDBVersion

Yes - but it also tries to create 200 connection pools - one for each call to GetOracleConnection.

See the second line?

public static Connection GetOracleConnection(String Enviroment) throws SQLException{ 

     PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource(); 

That 'pds' variable goes out of scope as soon as the method ends. So when you call the method a new datasource is obtained from the factory.

Then you create ONE connection for that new datasource and return it. When the method ends that second 'pds' instance goes out of scope.

Going 'out of scope' does NOT mean the 'pds' instance is garbage collected since there is still a reference to it.

On every method call it does the same thing: get a NEW datasource, create one connection and return it.

   Connection  conn = JDBCConnection.GetOracleConnection("test"); 

You try to execute that line 200 times. And each it causes a NEW datasource to be created - specifically it does NOT use the pool previously created because that 'pds' instance was NOT saved in a variable anywhere. Those old 'pds' instances still exist on the stack but you don't have any instance variable to reference them by.

In short your JDBCConnection  class is really mixing two DIFFERENT operations: 1) create a connection pool and 2) get a connection from that pool.

The standard way would be to have one method that creates a connection pool and returns that pool (i.e. returns a 'PoolDataSource  pds'). Then you would use 'pds.getConnection()' to get a connection from the pool. Then you would 'close' that connection and, under the covers, rather than closing it the pool implementation would return the connection to the pool so it could be used the next time.

When you set the connection invalid the pool implementation would actually remove the connection from the pool - next time you want a connection it will need to actually create one if there are none in the pool.

My loop works fine until I hit the magic number of 68 and then I receive an error which states

  1. java.sql.SQLException: Exception occurred while getting connection:   
  2. oracle.ucp.UniversalConnectionPoolException:    
  3. Cannot get Connection from Datasource: java.sql.SQLException:  
  4. Listener refused the connection with the following error: 
  5. ORA-12516, TNS:listener could not find available handler with matching protocol stack 

That exception means you have run out of handlers on the SERVER to create new connections.

If I were to create a web app and have a `getConnection()` every time a user logs in, would I not run out of available connections after 68 users?

Yes - using YOUR flawed code or if you tried to create a connection pool that had 68 open connections

No - using a connection pool with a MAX open connections less than 68.

That limit (e.g.  '68') will depend on how the server is configured. It could be much higher or much lower depending on the MAX SESSIONS configured on the server.

Marked as Answer by mlov83 · Sep 27 2020
mlov83

Thank you so much for that reply. I have been racking my brain for days as to why that was happening. I now understand, I feel silly that I have spend so much time working on this and it did not hit me with that the pool is being created many times over. I guess because all the example I have seen always have a top to bottom approach, my implementation is poor.

Thanks again for clearing me up. Your explanation makes much more sense.

Thank you!

mlov83

Just for the record and in case someone gets confused as I did. Following @"rp0428" suggestion here is another implementation of the GetOracleConnection

method.

package com.jam.DB;

import oracle.ucp.jdbc.PoolDataSource;

import oracle.ucp.jdbc.PoolDataSourceFactory;

public class Database {

  private static Database dbIsntance;

  private static PoolDataSource pds;

    private Database() {

        // private constructor //

      }

    public static Database getInstance(){

        if(dbIsntance==null){

            dbIsntance= new Database();

        }

        return dbIsntance;

        }

    public  PoolDataSource GetPoolSource(){

 

 

        if(pds==null){

         pds = PoolDataSourceFactory.getPoolDataSource();

  try {

  pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");

  pds.setURL("jdbc:oracle:thin:@//localhost:1521/xe");

  pds.setUser("system");

  pds.setPassword("xxx");

  pds.setMaxStatements(15);

  return pds;

  }

  catch(Exception e){

  }

  return pds;

  }

        return pds;

    }

}

Than in your code simply call it like this.

Connection conn = Database.getInstance().GetPoolSource().getConnection();

This is technically call a singleton pattern to retrieve the one and only pool instance.

Please refer to UCPSample for an example. Check out other code samples on Github.

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 24 2017
Added on Apr 21 2017
4 comments
4,237 views