Oracle JDBC UCP和Java [英] Oracle JDBC UCP and Java

问题描述

我想知道是否有人可以阐明这个话题，因为我已经绞尽脑汁好几天了，还不太明白为什么这行不通.我有三节课 main，RetrieveDBVersion，GetOracleConnection我一直在使用oracle JDBC，UCP和Java 1.7进行一些测试. 根据Oracle文档，如果我使用连接池，则关闭连接后，连接将立即返回到池中，使该连接无效并将其设置为null

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 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

这些是这三种方法的详细信息.这些方法不在服务器环境中.他们只是在调用本地oracle express数据库，而我正在从桌面运行它们.为什么我会不断收到此错误?如果我将连接返回到池中?

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. 主要

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());
        }
     }
 }      

RetrieveDBVersion

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

GetOracleConnection

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

推荐答案

因此，请仔细查看并从Oracle论坛获得一些额外的帮助.我终于明白为什么上面引用的代码给出了我收到的错误消息. 请参阅此处以获取回复 因为每次循环时都要设置数据源，所以实际上创建了多个池.实现此目的的方法是创建一个池，然后从该池中拉出连接. 替换GetOracleConnection的新代码我为数据源创建了一个单例类，并且在代码中，我仅从数据源中检索连接，例如

So after careful though and getting a little extra help from the Oracle forum. I finally understand why the above referenced code is giving the error message that I'm receiving. See Here For Response Because I'm setting the data source everytime the loop goes around, I'm essentially creating more than one pool. The way to do this, is create one pool and than pull connections from that pool. New code to replace the GetOracleConnection I created a singleton class for datasource and in code I simply retrieve the connection from the data source like such

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

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("xxxx");
            pds.setMaxStatements(15);
            return pds;

        } catch (Exception e) {

        }
        return pds;
    }

    return pds;

  }
}

这篇关于Oracle JDBC UCP和Java的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！