Monday 27 March 06
Have spent some time this morning looking at linking up a MySQL database up to Tomcat 4.1, using database connection pooling (DBCP). I had a fair bit of teething troubles getting it to work (using the instructions that I’d found to edit the tomcat server.xml) as I kept getting the message that:
org.apache.commons.dbcp.SQLNestedException: Cannot create JDBC driver of class ” for connect URL ‘null’
It seems that this is quite a common problem (from all the forum postings about it), and different solutions appear to work for different people (depending I guess on exactly what version of tomcat, which database etc etc). So anyway, here’s what worked for me in the end…. (for info, here I’m using Tomcat v4.1 on Windows, and the MySQL connector in mysql-connector-java-3.1.12.jar – which I put in $CATALINA_HOME/common/lib/)
I did not edit the $CATALINA_HOME/conf/server.xml file, instead I created an xml file in the webapps directory named the same as my webapp that would like to use the database (joinindb.xml). This file looks contains this (for info, this is the entire file)
debug="5" reloadable="true" crossContext="true">
<Logger className="org.apache.catalina.logger.FileLogger"
prefix="localhost_JoinInDB_log." suffix=".txt"
timestamp="true"/>
<Resource name="jdbc/JoinInDB"
auth="Container"
type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/JoinInDB">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<!– Maximum number of dB connections in pool. Make sure you
configure your mysqld max_connections large enough to handle
all of your db connections. Set to 0 for no limit.
–>
<parameter>
<name>maxActive</name>
<value>100</value>
</parameter>
<!– Maximum number of idle dB connections to retain in pool.
Set to 0 for no limit.
–>
<parameter>
<name>maxIdle</name>
<value>30</value>
</parameter>
<!– Maximum time to wait for a dB connection to become available
in ms, in this example 10 seconds. An Exception is thrown if
this timeout is exceeded. Set to -1 to wait indefinitely.
–>
<parameter>
<name>maxWait</name>
<value>10000</value>
</parameter>
<!– MySQL dB username and password for dB connections –>
<parameter>
<name>username</name>
<value>myusername</value>
</parameter>
<parameter>
<name>password</name>
<value>xxxxxxxxxx</value>
</parameter>
<!– Class name for mm.mysql JDBC driver –>
<parameter>
<name>driverClassName</name>
<value>org.gjt.mm.mysql.Driver</value>
</parameter>
<!– The JDBC connection url for connecting to your MySQL dB.
The autoReconnect=true argument to the url makes sure that the
mm.mysql JDBC Driver will automatically reconnect if mysqld closed the
connection. mysqld by default closes idle connections after 8 hours.
–>
<parameter>
<name>url</name>
<value>jdbc:mysql://localhost:3306/joinin?autoReconnect=true</value>
</parameter>
</ResourceParams>
</Context>
I then added the following to my webapp web.xml file (this was added just before the closing </web-app> tag):
<description>DB Connection</description>
<res-ref-name>jdbc/JoinInDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
I created a class to test the connection and the method that is used is as follows… (it just returns the name of the class):
String retvar="nothing here";
try {
Context context = new InitialContext();
if (context == null ) {
retvar = "Boom – No Context";
}
DataSource datasource = (DataSource)context.lookup("java:comp/env/jdbc/JoinInDB");
if (datasource == null ) {
retvar= "Boom – No DataSource";
}
retvar= "Database connected!";
retvar = datasource.getConnection().getClass().getName();
} catch (Exception e) {
e.printStackTrace() ;
retvar = "Database Not Available…. aarrgh …. why? – see the stack trace" ;
}
return retvar;
}
When I called this method from a JSP in my webapp, the code run without error returned the value: org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper as the classname.
