• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4211
  • Last Modified:

JDBC ORACLE Connection using JNDI

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.
0
rospcc
Asked:
rospcc
  • 6
  • 5
1 Solution
 
objectsCommented:
con.close();
0
 
rospccAuthor 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.
0
 
rospccAuthor Commented:
Sorry..con = ConnectionBean.getConnection() should be con = dbPool.getConnection();
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
objectsCommented:
how it behaves depends on the implementation and configuration of your pool.
0
 
rospccAuthor 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

0
 
objectsCommented:
> 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.

0
 
rospccAuthor Commented:
InitialContext context = new InitialContext();
             //Look up our data source
            ConnectionPoolDataSource ds = (ConnectionPoolDataSource) context.lookup("Oracle");
           //Allocate and use a connection from the pool
0
 
rospccAuthor 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?

0
 
objectsCommented:
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.
0
 
rospccAuthor 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.
0
 
objectsCommented:
> 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?

Sorry haven't use oracle pool so not sure.

> I still think that it's better if we know how to release those connections,

typically you can't, its the job of the connection pool.
Check its api to see if it gives you the option.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now