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?
- 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;
}