Managed Connection Pools

[mastrivens]

I am getting the 'NoManagedConnections' Exception this only happens after a while, but I confess I am confused by the JBoss documentation and the few examples of servlets working with JBoss don't seem to be much help.

I get a new connection object when I call the init method of each of my servlets (there are about 20), that is the only connection object I use for that servlet (I pass it as a parameter to the various factory classes I use within the servlet to do database access).

But if I understand correctly, there is only one instance of each servlet - so it shoud only be using one connection? So I should only be using the same number of connections as servlets?

Do I really need to close and create connections whenever I use JDBC?
Is there somewhere I can put a close command that wouldn't be too much rework? Or is there a timeout for spotting dead connections?

I already have a fairly large application and this would be painful to put it mildly... I thought that was the point of a managed connection pool? I have included the datasource XML file and also the code for getting a connection.

Thank-you in advance

Mark

I have project-ds.xml file to set-up a connection pool with Postgres:

<datasources>
 <local-tx-datasource>
 <jndi-name>MtrdbDS</jndi-name>
 <connection-url>jdbc:postgresql://myserver:5434/myDb</connection-url>
 <driver-class>org.postgresql.Driver</driver-class>
 <user-name>someUser</user-name>
 <password>somepassword</password>
 <metadata>
 <type-mapping>PostgreSQL 8.0</type-mapping>
 </metadata>
 </local-tx-datasource>

I use a JNDI call to get a connection from that pool:

 public Connection getConnection() throws SQLException
 {
 Connection conn = null;
 Context ctx = null;
 DataSource ds = null;
 String sourceName = "";

 try
 {
 ctx = new InitialContext();

 if(ctx == null )
 throw new NamingException("Error 'Db.getConnection' - No Context");

 sourceName = this.getDbName(ctx);

 ds = (DataSource)ctx.lookup(sourceName);
 }
 catch (NamingException e)
 {
 MtrErr.writeExc(MtrErr.OTHER_SOURCE,this.getClass().toString(),"getConnection",e);
 }
 catch (Exception e)
 {
 MtrErr.writeExc(MtrErr.OTHER_SOURCE,this.getClass().toString(),"getConnection",e);
 }

 if (ds != null)
 {
 conn = ds.getConnection();
 }

 return(conn);
 }

[jaikiran]

Have you tried using the max-pool-size setting on your datasource:

<min-pool-size>5</min-pool-size>
 <max-pool-size>100</max-pool-size>

By default, i guess the max-pool-size is 20(not sure though)
Here you will find the details:
http://wiki.jboss.org/wiki/Wiki.jsp?page=ConfigDataSources

I get a new connection object when I call the init method of each of my servlets (there are about 20), that is the only connection object I use for that servlet (I pass it as a parameter to the various factory classes I use within the servlet to do database access).

Is this really required to be done this way. Cant you create a new connection whenever you need it? If you are going by the approach you mention, then the Connection will be held by the servlet during its entire lifetime(i.e. until destroy() is called, where you can call Connection.close()). This would mean that the Connection will *NOT* be released till the servlet is destroyed.
Ideally, you can lookup the datasource object in your init() method and maintain that datasource object through out the lifetime of the servlet. Then whenever an connection is required, use this datasource object and create a new connection.

Something like:

MyServlet.java:

init() {

DataSource ds = null;
 String sourceName = "";

 try
 {
 ctx = new InitialContext();

 if(ctx == null )
 throw new NamingException("Error 'Db.getConnection' - No Context");

 sourceName = this.getDbName(ctx);

 ds = (DataSource)ctx.lookup(sourceName);
 }
 catch (NamingException e)
 {
 MtrErr.writeExc(MtrErr.OTHER_SOURCE,this.getClass().toString(),"getConnection",e);
 }
 catch (Exception e)
 {
 MtrErr.writeExc(MtrErr.OTHER_SOURCE,this.getClass().toString(),"getConnection",e);
 }


}


doGet() {
 Connection conn = null;
 try {
 conn = this.ds.getConnection();
 //use this connection object

 } finally {
 //Dont forget to close the connection
 if (conn != null) {
 conn.close();
 }
 }