JSP/ DAO error : The method getInstance() is undefined for the type ConnectionPool

Question

As you can see, this code is part of my Data Access Object Layer. I've never used the ConnectionPool Object before because i'm still studying Java. Anyway, i'm getting an error message stating :

The method getInstance() is undefined for the type ConnectionPool. (at line 5)

Should any of you have experienced this before, help would be appreciated.

import java.sql.*;
import java.util.*;

import org.apache.tomcat.jdbc.pool.ConnectionPool;

import music.business.*;

public class ProductDB
{
    //This method returns null if a product isn't found.
    public static Product selectProduct(String productCode)
    {
        ConnectionPool pool = ConnectionPool.getInstance(); //<===<====<====<=================
        Connection connection = pool.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        String query = "SELECT * FROM Product " +
                "WHERE ProductCode = ?";
        try
        {
            ps = connection.prepareStatement(query);
            ps.setString(1, productCode);
            rs = ps.executeQuery();
            if (rs.next())
            {
                Product p = new Product();
                p.setCode(rs.getString("ProductCode"));
                p.setDescription(rs.getString("ProductDescription"));
                p.setPrice(rs.getDouble("ProductPrice"));
                return p;
            }
            else
            {
                return null;
            }
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            return null;
        }
        finally
        {
            DBUtil.closeResultSet(rs);
            DBUtil.closePreparedStatement(ps);
            pool.freeConnection(connection);
        }
    }

I just found out that i made a mistake: - the ConnectionPool in my, above mentioned, class was not supposed to be Tomcat imported. Its a JNDI class. See below. The getInstance is actually a method in my JNDI class. Sorry for waisting your time guys. Thank you

import java.sql.*;
import javax.sql.DataSource;
import javax.naming.InitialContext;

public class ConnectionPool
{
    private static ConnectionPool pool = null;
    private static DataSource dataSource = null;

    public synchronized static ConnectionPool getInstance()
    {
        if (pool == null)
        {
            pool = new ConnectionPool();
        }
        return pool;
    }

    private ConnectionPool()
    {
        try
        {
            InitialContext ic = new InitialContext();
            dataSource = (DataSource) ic.lookup("java:/comp/env/jdbc/musicDB");
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }

    public Connection getConnection()
    {
        try
        {
            return dataSource.getConnection();
        }
        catch (SQLException sqle)
        {
            sqle.printStackTrace();
            return null;
        }
    }

    public void freeConnection(Connection c)
    {
        try
        {
            c.close();
        }
        catch (SQLException sqle)
        {
            sqle.printStackTrace();
        }
    }
}

Answer 1

I would have Tomcat manage that pool for you. It has instructions on how to create a JNDI data source. You should do that and get this out of your code.

It'll have the added benefit of externalizing the connection parameters from your app. They'll live in configuration on the app server.