RE: MYSQL CONNECTION CLASS

[James Carroll]

What you need is connection pooling....

Let me know what you think of this class (I'm pretty happy with it)

All it needs is the URL that represents your connection to the
database...  make the following calls in your init()

 itsConnectionPool = new
VSDBPool("jdbc:mysql://localhost/database?user=user&password=pass");
 itsConnectionPool.sc = getServletContext();

------------------------------------

import java.sql.*;
import java.util.Vector; // as in list
import java.lang.String;

// for debugging
import javax.servlet.*;

class VSDBPool implements Runnable
{
    // connections act as a stack... When it gets too low, we add more
    // and when it gets too high, we remove some.
    protected Vector itsConnections;
    protected Thread itsWorker;

    // for debug logging
    public ServletContext sc;

    private int itsLowWaterCount = 5;
    private int itsHighWaterCount = 25;

    private String itsDBURL;

    public VSDBPool(String dbURL)
    {
        itsDBURL = dbURL; 
        itsConnections = new Vector();

        itsWorker = new Thread(this);
        itsWorker.start();
    }
     
    private synchronized Connection createConnection()
    {
        Connection c = null;
        try
        {
            c = DriverManager.getConnection(itsDBURL);
        }
        catch (Exception e)
        {
            sc.log("SQLException: " + e.getMessage());
        }
        return c;
    }

    private synchronized void closeConnection(Connection c)
    {
        // perform any wrap-up we need
        return;
    }

    public synchronized Connection getConnection()
    {
        Connection c;

        // if there are any in the pool
        if (itsConnections.size() > 0)
        { 
            int last = itsConnections.size() - 1;
            c = (Connection)itsConnections.elementAt(last);
            itsConnections.removeElementAt(last);
        }
        else
        {   // create one right away and return it!
            // this one never sees our collection of Connections
            c = createConnection();
            sc.log("Quickly creating connection: " + c);
        }
        return c;
    }

    public synchronized void returnConnection(Connection c)
    {
        itsConnections.addElement(c);
    }

    // handle background tasks...
    public void run()
    {
        while(true)
        {
            // check once in a while for the need to add or remove
connections
            try
            {
                Thread.sleep(5000);
            } catch (InterruptedException e)
            {
            }
            
            if (itsConnections.size() < itsLowWaterCount)
            {
                Connection c = createConnection();
                if (c != null)
                {
                    itsConnections.addElement(c);
                }
            }

            if (itsConnections.size() > itsHighWaterCount)
            {
                int last = itsConnections.size() - 1;
                Connection c =
(Connection)itsConnections.elementAt(last);
                itsConnections.removeElementAt(last);
                closeConnection(c);
            }

            // check existing connections 
            // to make sure they haven't timed out
            for (int i = 0; i < itsConnections.size(); i++)
            {
                Connection c = (Connection)itsConnections.elementAt(i);
                try
                {
                    if (c.isClosed())
                    {
                        // create a new connection and put it in the
                        // pool
                        itsConnections.setElementAt(createConnection(),
i);
                    }
                } 
                catch (SQLException e)
                {
                    System.out.println(e.toString());
                }
            }
        }
    }
}


-----Original Message-----
From: Guilherme Zambon [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 14, 2001 8:29 PM
To: [EMAIL PROTECTED]; josé placide
Subject: MYSQL CONNECTION CLASS


Can someone who uses TOMCAT send me a sample class that conects to the
database? I have made one that initializes de driver to connect and
returns
the connection, so it is very slow. What kind of solution do you have?