Java Database Connectivity (JDBC)

Dear all,
I have normally used the Apache's commons-dbcp (version 1.2.1) for connection pooling.

My MySQL5 is set to have a maximum of 50 connections and the OS is Windows 2003 server
and the servlet container is Tomcat5.5 and the JDK is JDK1.5.0

My understanding of connection pooling and the terms is that:

1. If 10 different users are accessing the database (from the website, probably a search, delete, add or update)
the we have 10 active connections.
Is that right?

2. If 2 of those 10 users leave then we have 2 idle connections and 8 active connections.
Is that right?

3. If a connection is closed then the connection will be returned to the pool

If that is all correct then why do the active connections just increase eventhough no one is using the website?

I have checked through the whole code and all connections are closed after usage.

Here is a typical log from my website:
The max active connections are : 17

After 23:00 til 17:00 (next day), there are hardly anyone visiting the site.
I would expect the 17 connections to be returned back to the pool, but it is not.

17:00 (next day) til 23:00 I get another say 23 visitors, this sums it up to 40 active connections.

And since the maxActive connections is set to 40 the site gets stuck,
no one can visit the pages that requests data from the database.

So I restart Tomcat as usual, but I know something is wrong with my code probably.



Question:

What is actually wrong with my code (see bottom) when using commons-dbcp (version 1.2.1)


Data.java

import org.apache.commons.dbcp.BasicDataSource;
.... more imports

public class Data {

	private static final Logger LOG = Logger.getLogger(Data.class);
	private static final Logger SQL = Logger.getLogger("sql");
	private static final Logger DATASOURCE = Logger.getLogger("datasource");
	private static final Logger MANY_CONNECTIONS = Logger.getLogger("manyconnections");
	private static BasicDataSource ds = null;
	

	public static BasicDataSource getDataSource() throws SQLException {
		if (ds == null) {
			ds = new BasicDataSource();
			ds.setDriverClassName(ApplicationProperties.DATABASE_DRIVER);
			ds.setUsername(ApplicationProperties.DATABASE_USERNAME);
			ds.setPassword(ApplicationProperties.DATABASE_PASSWORD);
			ds.setUrl(ApplicationProperties.DATABASE_URL);
			ds.setMaxActive(40);
			ds.setMaxWait(-1);
			ds.setMaxIdle(20);
			DATASOURCE.info("Datasource is null");
		}
		DATASOURCE.info("The max active connections are : " + ds.getMaxActive());
		DATASOURCE.info("The max idle connections are : " + ds.getMaxIdle());
		DATASOURCE.info("The max wait is : " + ds.getMaxWait());
		DATASOURCE.info("The max opening prepared statements are : " + ds.getMaxOpenPreparedStatements());
		DATASOURCE.info("The number of active connections are : " + ds.getNumActive());
		DATASOURCE.info("The number of idle connections are : " + ds.getNumIdle());
		DATASOURCE.info("\n====================================\n");
		if (ds.getMaxActive() >= 20 || ds.getNumIdle() >= 10) {
			MANY_CONNECTIONS.info("The max active connections are : " + ds.getMaxActive());
			MANY_CONNECTIONS.info("The max idle connections are : " + ds.getMaxIdle());
			MANY_CONNECTIONS.info("The max wait is : " + ds.getMaxWait());
			MANY_CONNECTIONS.info("The max opening prepared statements are : " + ds.getMaxOpenPreparedStatements());
			MANY_CONNECTIONS.info("The number of active connections are : " + ds.getNumActive());
			MANY_CONNECTIONS.info("The number of idle connections are : " + ds.getNumIdle());
			MANY_CONNECTIONS.info("\n====================================\n");	
		}
        return ds;
    }

}

ProductData.java

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.apache.log4j.Logger;

import com.practicasystems.ordersmart.model.Product;

public class ProductData {

	private static final Logger SQL = Logger.getLogger(ProductData.class);

	public static Product getProductById(final int productid, final DataSource datasource) throws SQLException {

		final String sql = "select * from product" +
				" where pk_product_id = "+ productid;
		
		SQL.info(sql);
		
		final Connection conn = datasource.getConnection();
		try {
			final Statement stmt = conn.createStatement();
			try {
				final ResultSet rs = stmt.executeQuery(sql);
				try {
			           if(rs.next()){
			              
			              final Product product   = new Product();
			        	   
			              product.setId(rs.getInt("pk_product_id"));
			              product.setCode(rs.getString("code"));
			              product.setName(rs.getString("name"));
			              product.setDescription(rs.getString("description"));
			              product.setStock(rs.getInt("stock"));
			              product.setSpicelevel(rs.getInt("spicelevel"));
			              product.setImageurl(rs.getString("imageurl"));
			              product.setPrice(rs.getBigDecimal("price"));
			              product.setChoicetypeid(rs.getInt("fk_choicetype_id"));
			              product.setCategoryid(rs.getInt("fk_category_id"));
			              product.setMenuid(rs.getInt("fk_menu_id"));
			              
			              return product;
				   }
			           
			           return null;
			           
				} finally {
					rs.close();
				}
			           
			} finally {
				stmt.close();
			}
           
		} finally {
			conn.close();
		}
	}
 }

This is an example of how I use the code to interact with the database

final int id = 1;   
final Product product = ProductData.getProductById(id, Data.getDataSource());