Tony Smith wrote:

>I am runing tomcat 5.0 + postgresql. I set my
>connection pool in server.xml as:
>
>   <Resource
>    name="jdbc/mysource"
>    type="javax.sql.DataSource"
>    password="xxxx"
>    driverClassName="org.postgresql.Driver"
>    maxIdle="100"
>    maxWait="5000"
>    validationQuery="select * from test"
>
>

For PostgreSQL (and MySQL, too), this is better done via "SELECT
version()", just an observation.

>    username="xxxx"
>    url="jdbc:postgresql://localhost:5432/mydb"
>    maxActive="100"/>
>
>

You can add parameters, like "removeAbandoned" to remove hanging
connections.

>I call it from my servlet as:
>
>public Connection getConnection(){
>     try{
>        Context initCtx = new InitialContext();
>        Context envCtx =
>(Context)initCtx.lookup("java:comp/env");
>       DataSource ds =
>(DataSource)envCtx.lookup("jdbc/mysource");
>       DatabaseManager.initDataSource(ds);
>       return ds.getConnection();
>     }catch(Exception e){
>        e.printStackTrace();
>     }
>
>   return null;
>}
>
>
>I use the connection as:
>
>Connection connection = getConnection();
>
>....//jdbc
>
>//I did not call connection.close(). Should I?
>
>

YES! The Connection you get from this is a wrapper class, that will
actually return the connection to the pool, when you call "close()" on it.

>Then, I can run my web app. But not for long. I got
>the following exception after browse a few pages:
>
>org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot
>get a connection, pool exhausted
>
>

Always close ResultSets, Statements and Connections - in that order.

This code will illustrate it for you:

PreparedStatement pstat = null;
ResultSet rs = null;
try {
  pstat = conn.prepareStatement( "SELECT ..." );
  pstat.setInt( 1, x );
  ...
  rs = pstat.execute();
  ...
} catch (SQLException ex) {
  ...
} finally {
  if (rs != null) {
    try {
      rs.close();
    } catch (SQLException ex1) {}
  }
  if (pstat != null) {
    try {
      pstat.close();
    } catch (SQLException ex1) {}
  }
  if (conn != null) {
    try {
      conn.close();
    catch (SQLException ex1) {}
  }
}

Nix.

---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@(protected)
For additional commands, e-mail: tomcat-user-help@(protected)