connect to MySQL using Tomcat 3.2.1 and Poolman 2.0

[Bruno Grossniklaus]

I have problems to connect to MySQL using Tomcat 3.2.1 and Poolman 2.0.
I use Java Beans to
encapsulate information from the JSP's. Formerly I have been using JRun 
from Allaire and have now changed to Tomcat 3.2.1. JRun was running
using the same code.

Tomcat Servlet Engine does not allow the same programming style in my 
Java Beans as JRun did - bad luck. Formerly I had some JB producing
result sets
within the classes constructor. Tomcat does not seem to allow making a
connection, then
a statement and then query within the constructor's body. If I call a
method from inside 
the constructor then it works. However, if I want to make a query not at
the classes
constructor time then I cannot make a connection to the db!!! So I began
studying and thought, 
maybe a pool manager would help and came to Poolman 2.0. No I have
problems to make this 
config running. Please help me and thank you :-)

=====================================================================
In Tomcat's server.xml I use the following configuration:

        <RequestInterceptor 
            className="org.apache.tomcat.request.JDBCRealm" 
            debug="99" 
            driverName="org.gjt.mm.mysql.Driver"
            connectionURL="jdbc:mysql://localhost/myapp" />

The org and its subdirectories for the jdbc driver are placed under
/webapps/classes which automatically integrates this into classpath
=====================================================================
In Poolman's poolman.xml I have use the following conf:

    <dbname>myappdb</dbname>
    <jndiName>jndi_myapp</jndiName>
    <driver>org.gjt.mm.mysql.Driver</driver>
   
<url>jdbc:mysql://localhost/myapp?user=myuser&password=mypassword</url>

Poolman's files are under /lib ... the /lib/poolman.jar is explicitely 
in the classpath. If I place the org/ for the jdbc driver under lib too
it does also not help.

=====================================================================
Here a typical Java Bean without using Poolman:

public class Meta {
...
  private String CONNECTION_URL =
"jdbc:mysql://localhost/myapp?user=myuser&password=mypassword";
...
  public Meta( ) {
    getMetaData();
  }
...
  private void getMetaData() {
    try {
      // The newInstance() call is a work around for some
      // broken Java implementations
      Class.forName("org.gjt.mm.mysql.Driver").newInstance();
    }
    catch (Exception E) {
      System.err.println("Unable to load JDBC driver:
org.gjt.mm.mysql.Driver !");
      E.printStackTrace();
    }

    // load all main Subjects into memory
    try {
      Connection conn = DriverManager.getConnection( CONNECTION_URL );
      try {
        Statement qs = conn.createStatement();

        ResultSet rs = qs.executeQuery( GET_MAINSUBJECTS );

        mainSubjectId = new Vector();
        mainSubjectName = new Vector();
        mainSubjectMap = new HashMap();

        while ( rs.next() ) {
          mainSubjectId.addElement( rs.getString( MSN_SUBJECT_ID ) );
          mainSubjectName.addElement( rs.getString( MSN_NAME ) );
          mainSubjectMap.put( rs.getString( MSN_SUBJECT_ID ),
rs.getString( MSN_NAME ) );
        }
        // Clean up after ourselves
        rs.close();
        qs.close();
        conn.close();
      }
      catch (SQLException E) {
        System.out.println("1-SQLException: " + E.getMessage());
        System.out.println("1-SQLState:     " + E.getSQLState());
        System.out.println("1-VendorError:  " + E.getErrorCode());
        E.printStackTrace();
      }
    }
    catch (Exception E) {
      E.printStackTrace();
    }
  }


o=====================================================================
Here a typical Java Bean using Poolman:

public class Meta {
...
  private String CONNECTION_URL =
"jdbc:mysql://localhost/myapp?user=myuser&password=mypassword";
...
  public Meta( ) {
    getMetaData();
  }
...
  private void getMetaData() {

    try {
        // load the PoolMan JDBC Driver
        Class.forName("com.codestudio.sql.PoolMan").newInstance();
    } catch (Exception ex) {
            System.out.println("Could Not Find the PoolMan Driver. Is
PoolMan.jar in your CLASSPATH?");
    }

    Connection con = null;
    try {

        // establish a Connection to the database with
<dbname>mygsk</dbname>
        //in the poolman.xml file
        con = DriverManager.getConnection("jdbc:poolman://mygsk");

        // Use the Connection to create Statements and do work, per the
JDBC API
        Statement stm = con.createStatement();


        // Get a Connection
        DataSource ds = PoolMan.findDataSource("jndi_mysql");
        con = ds.getConnection();

        Statement qs = con.createStatement();

        ResultSet rs = qs.executeQuery( GET_MAINSUBJECTS );

        mainSubjectId = new Vector();
        mainSubjectName = new Vector();
        mainSubjectMap = new HashMap();

        while ( rs.next() ) {
          mainSubjectId.addElement( rs.getString( MSN_SUBJECT_ID ) );
          mainSubjectName.addElement( rs.getString( MSN_NAME ) );
          mainSubjectMap.put( rs.getString( MSN_SUBJECT_ID ),
rs.getString( MSN_NAME ) );
        }
        // Clean up after ourselves
        rs.close();
        qs.close();

    } catch (SQLException sqle) { sqle.printStackTrace(); }
    finally {
        // "closing" the Connection closes its handle and
        // returns the actual connection to its pool:
        try { con.close(); } catch (SQLException csqle) {}
    }
  }