We help IT Professionals succeed at work.

JDBC ORACLE Connection using JNDI

rospcc
rospcc asked
on
4,291 Views
Last Modified: 2013-12-19
Hi All,

We have set up JDBC connection using JNDI as follows.

InitialContext context = new InitialContext();
 //Look up our data source
DataSource ds = (DataSource) context.lookup("Oracle");
//Allocate and use a connection from the pool
con = ds.getConnection();

We are able to get the connection and all but we encounter maximum connection reached after executing several queries. It seemed that we didnt release the connection.

How can we release the connection?

Thanks for the help.
Comment
Watch Question

Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
con.close();

Author

Commented:
we have these classes below,

public class DatabasePool {
    public Connection getConnection() {
             InitialContext context = new InitialContext();
             //Look up our data source
            ConnectionPoolDataSource ds = (ConnectionPoolDataSource) context.lookup("Oracle");
           //Allocate and use a connection from the pool
          Connection con = ds.getConnection();
          return con;
    }
}



public class Grade {
          DatabasePool dbPool = new DatabasePool();

       public Vector get Type() {
          Vector v = new Vector();
          Connection con = null;
          Statement st = null;
           ResultSet rs = null;
       String querySql = "SELECT * FROM QType ORDER BY SEQUENCE";      
       
        try
        {          
            con=ConnectionBean.getConnection();
           st=con.createStatement();
            rs=st.executeQuery(sql);
                       
            while(rs.next())
            {
                  voQType vo = new voQType();
                  vo.setPKQType(rs.getInt("PKQType"));
                  vo.setQType(rs.getString("QType"));
                 
                  v.add(vo);
            }
           rs.close();
           rs = null;
        }
        catch(Exception E)
        {
           
            System.err.println("Q.java - getQTypes - " + E);
        }
        finally
        {
              stmt.close();
              stmt = null;
             con.close();
             close = null;
        }
        return v;
      }
}

Grade object will create new DatabasePool object and call the getConnection() method. We used the close() method to close the connection at the end of the query. Does it close the connection instance or the physical connection the database? When we call the getConnection() method, does it actually create a new physical connection database or simply getting a new connection instance from the connection pool?

Please help to clarify.

Author

Commented:
Sorry..con = ConnectionBean.getConnection() should be con = dbPool.getConnection();
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
how it behaves depends on the implementation and configuration of your pool.

Author

Commented:
Our configuration of the pool is as follows:

In sun app server admin console,

we have setup Datasource Classname as oracle.jdbc.pool.OracleConnectionPoolDataSource
and the resource type as javax.sql.ConnectionPoolDataSource

We followed the exact same pool setting as the sun. i.e.
initial and minimum pool size : 8 connections
maximum pool size : 32 connections
pool resize quantity : 2 connections
idle timeout : 2 connections
idle timeout : 300 seconds
max wait time : 60000 Milliseconds

Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
> Does it close the connection instance or the physical connection the database?

Depends (though it proably doesn't) :) And you shouldn't need to care.

> When we call the getConnection() method, does it actually create a new physical connection database or simply getting a new connection instance from the connection pool?

Again it depends, on whethere there is a free connection in the pool.

Author

Commented:
InitialContext context = new InitialContext();
             //Look up our data source
            ConnectionPoolDataSource ds = (ConnectionPoolDataSource) context.lookup("Oracle");
           //Allocate and use a connection from the pool

Author

Commented:

sorry for the incomplete post above...

Do i need to post this question in a separate post/question?

 
            InitialContext context = new InitialContext();
             //Look up our data source
           DataSource ds = (DataSource) context.lookup("Oracle");
           //Allocate and use a connection from the pool

When i change DataSource to OracleConnectionPoolDataSource, i got ClassCastException.  contect.lookup(oracle) returns com.sun.gjc.spi.DataSource@3abba0.

We setup the connection pool as oracle.jdbc.pool.OracleConnectionPoolDataSource
and the resource type as javax.sql.ConnectionPoolDataSource. How come the object returns DataSource type instead of OracleConnectionPoolDataSource. If we cast the object to DataSource, does it handle the connection pooling or we have to create our own connection pooling?

Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
DataSource is an interface, that all DataSource implementations will implement.
Casting does not change the object you are accessing, just the methods that you can call.

Author

Commented:
>> Does it close the connection instance or the physical connection the database?
> Depends (though it proably doesn't) :) And you shouldn't need to care.

We're only allocated 100 connections. If the connection instance doesn't get closed or returned to the pool, will those connections be returned back on time to serve again? Do you know what is the default time that the connections used will be released automatically? I still think that it's better if we know how to release those connections, so that we can control when is the optimal time to release.
Please advise, thanks.
Java Developer
CERTIFIED EXPERT
Top Expert 2010
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.