tags:

views:

71

answers:

2
+2  Q: 

tomcat 6.0 with JDBC throws ClassNotFoundException com.mysql.jdbc.Connection

I've successfully connected to MySQL database through Eclipse without getting tomcat involved, so at lease it's some good progress. But when I try from my web page (Tomcat 6.0), it throws an error. I followed the tutorials, read documentations and looked countless forums but cannot figure out for 2 days now.

Let's look at the step one by one. I'm running tomcat 6.0.26 and I have MySQL installed and up and running fine.

1. Place connectorj to CATALINA_HOME/lib

makun /home/makun/tomcat/apache-tomcat-6.0.26/lib ->ls | grep -i mysql*
mysql-connector-java-5.1.13-bin.jar

2. Tell Tomcat about my MySQL info. (declare resource requirements) and also map the servlet used to invoke db test (i.e. /tmp_db_test)

(/WEB-INF/web.xml)

<?xml version="1.0" encoding="ISO-8859-1"?>
<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
    version="2.4">

    <servlet>
        <servlet-name>DatabaseTest</servlet-name>
        <servlet-class>com.masatosan.tmp.Tmp</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>DatabaseTest</servlet-name>
        <url-pattern>/tmp_db_test</url-pattern>
    </servlet-mapping>

    <resource-ref>
      <description>DB Connection</description>
      <res-ref-name>jdbc/MasatoDB</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
    </resource-ref>


</web-app>

3. Configure tomcat resource factory.

(CATALINE_HOME/conf/context.xml)

<!-- Default set of monitored resources -->
<WatchedResource>WEB-INF/web.xml</WatchedResource>

<!-- Masato added the line below to setup JDBC -->
<Resource 
    name="jdbc/MasatoDB" 
    auth="Container" 
    type="javax.sql.DataSource"
    maxActive="100" 
    maxIdle="30" 
    maxWait="10000"
    username="masato" 
    password="mypass" 
    driverClassName="com.mysql.jdbc.Driver"
    url="jdbc:mysql://localhost:3306/masatosan"/>

4. create JSP page that invoke servlet
This is just a page with one button that send POST request.

 <html>
    <head>
    </head>
    <body>
    <!-- click button to send request to servlet -->
    <form method="POST" action="tmp_db_test">
    <p><input type="submit" value="Submit" name="submit_button"></p>
    </form>
    </body>
    </html>

5. Finally implement the servlet that tries to connect to MySQL database.
(Tmp.java)

package com.masatosan.tmp;

import java.io.IOException;
//import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import com.masatosan.dateformatter.DateFormatter;
import com.masatosan.logger.Logger;
import com.mysql.jdbc.Connection;


public class Tmp extends HttpServlet {


    private Connection conn;

    public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {
            Context initCtx = new InitialContext();
            Context envCtx = (Context) initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/MasatoDB");

            conn = (Connection) ds.getConnection();

            String template = "INSERT INTO users(username, email, password, created_date) VALUES (?, ?, ?, ?);";
            PreparedStatement inserter = conn.prepareStatement(template);
            inserter.setString(1, "test_username");
            inserter.setString(2, "[email protected]");
            inserter.setString(3, "test_pass");
            inserter.setString(4, DateFormatter.formatToSqlDate(null));

            inserter.executeUpdate();
        }
        catch(Exception e) {
            Logger.log(e.getMessage());
        }
        finally {
            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.log(e.getMessage());
                }
            }
        }
    }
}

I complied Tmp.javaand put in /WEB-INF/classes/com/masatosan/tmp/Tmp.class

Then redeployed Tomcat server and tested on browser. When I click submit button, I get the error:

javax.servlet.ServletException: Error instantiating servlet class com.masatosan.tmp.Tmp
    org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
    org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:852)
    org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
    org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    java.lang.Thread.run(Thread.java:619)

root cause

java.lang.NoClassDefFoundError: Lcom/mysql/jdbc/Connection;
    java.lang.Class.getDeclaredFields0(Native Method)
    java.lang.Class.privateGetDeclaredFields(Class.java:2291)
    java.lang.Class.getDeclaredFields(Class.java:1743)
    org.apache.catalina.util.DefaultAnnotationProcessor.processAnnotations(DefaultAnnotationProcessor.java:181)
    org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
    org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:852)
    org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
    org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    java.lang.Thread.run(Thread.java:619)

root cause

java.lang.ClassNotFoundException: com.mysql.jdbc.Connection
    org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1516)
    org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1361)
    java.lang.Class.getDeclaredFields0(Native Method)
    java.lang.Class.privateGetDeclaredFields(Class.java:2291)
    java.lang.Class.getDeclaredFields(Class.java:1743)
    org.apache.catalina.util.DefaultAnnotationProcessor.processAnnotations(DefaultAnnotationProcessor.java:181)
    org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
    org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:852)
    org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
    org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    java.lang.Thread.run(Thread.java:619)

Please let me know if you need more clarification.

EDIT

As per suggestion, connectorj jar stays in CATALINA_HOME/lib

I've fixed import line to in Tmp.java to:

import java.sql.Connection instead of com.mysql.jdbc.Connection

Now that I'm getting new error when click submit button on browser.

Cannot create JDBC driver of class '' for connect URL 'null'

I attempted to tail the log:

makun /home/makun/tomcat/apache-tomcat-6.0.26/logs ->tail -f localhost.2010-08-30.log

and nothing comes up when I clicked the submit button. (it is correct log I think since other pages that have error will output message in the log)

UPDATE

I'm not sure why but it started working while I was trying to print exception stacktrace to my log. I've been reloading Tomcat from manager page so many times that might caused something weird and getting same error page even though I've made code change (it was freaking out giving me 404 and 500 interchangeably. I wish I could provide exact detail but my steps described in the question section appeared to work (after fixing few things that were suggested)

+1  A: 

The class it can't find "com.mysql.jdbc.Connection" should be in mysql-connector-java-5.1.13-bin.jar. I think tomcat should be able to pick it up from $CATALINE_HOME/lib. So from what you say it "should" be working...

Perhaps check that the user tomcat runs as has read permissions on the file? Could the jar file have got corrupted - perhaps by ftp in text mode? Are you certain that CATALINA_HOME is /home/makun/tomcat/apache-tomcat-6.0.26 - maybe check the tomcat startup scripts to see if it is configured to be somewhere else?

If its still not working you could try putting the jar file into web-inf/lib.

Adam Butler  2010-08-30 21:42:48
Thanks for the tip! Putting in /WEB-INF/lib did make some change but root cause for class not found exception was due to importing wrong connection package (please see the EDIT section)
masato-san  2010-08-30 22:54:32
+2  A: 

This exception is telling you that it cannot find the com.mysql.jdbc.Connection class while investigating the declared fields of the servlet during its loading/instantiation in order to collect any annotations for the annotation cache.

You should be using java.sql.Connection, not com.mysql.jdbc.Connection. In fact, all your JDBC code should be importing/using the java(x).sql interfaces/classes only. Otherwise your JDBC code is tight coupled to the DB and JDBC driver used and not reuseable with other DB's and/or drivers, this violates the whole abstract idea of JDBC.

That said, declaring the Connection as an instance variable of a servlet is an extremely bad idea. A servlet is been instantiated only once and shared among all requests during the webapp's lifetime. You should be acquiring and closing the connection in the shortest possible scope, i.e. already in the very same method block. Better yet, place all the JDBC logic in a reuseable DAO class which you just import in your servlet.

See also:

BalusC  2010-08-30 21:59:54
I noticed there are 2 different Connection that I can import too. Very confusing... I've fixed and got new error so I will take a look what is the error indicate. (also added to my EDIT section)
masato-san  2010-08-30 22:12:19
@Adam: This exception was thrown during loading/instantiating the servlet, not during loading the resource. @masato: That's a wrapped/nested exception. Read the server logs for the **root cause**. It contains detail about the root cause of the problem. Big chance that you should keep the MySQL JDBC driver there in `Tomcat/lib` where it belonged and get rid of the one in `/WEB-INF/lib`.
BalusC  2010-08-30 22:24:56
@BalusC: Thanks for the tip! I will post the result in edit shortly.
masato-san  2010-08-30 22:28:00
@masato: Are you using Tomcat plugin in Eclipse? Everything get logged into Eclipse console. You may need to switch the displayed console.
BalusC  2010-08-30 22:36:57
@BalusC: nope I'm not using the plugin. I compile java classes manually and put them in corresponding WEB-INF, then reload tomcat server from manager page (http://localhost:8080/manager/html/reload?path=/head_first).
masato-san  2010-08-30 22:43:16
@BalusC: meanwhile I will mark this as an answer since topic's subject's exception has been solved and I'm dealing with new error. I'll spend sometime to try plugin and see if I can get some useful log messages. Thanks for the help!
masato-san  2010-08-30 22:53:23
OK, check the `catalina` logfile in `/logs` folder for the exact exception. That's Tomcat's own root logfile. The `localhost` logfile is only for the deployed webapplication(s) on the domain.
BalusC  2010-08-30 22:56:05

related questions

Remove Quotes and Commas from a String in MySQL
What's the best way of converting a mysql database to a sqlite one?
How do you manage databases in development, test, and production?
Multiple foreign keys?
Add 1 to a field (MySQL)
Issues using MS Access as a front-end to a MySQL database back-end?
Bigger than a char but smaller than a blob
How do I retrieve my MySQL username and password?
Long-time LAMP Developer moving to VB.NET
How do I Concatenate entire result sets in MySQL?
Full complete MySQL database replication? Ideas? What do people do?
SQL: Distribution of table in time
SQLite vs MySQL
How do I create a new Ruby on Rails application using MySQL instead of SQLite?
Multiple Updates in MySQL
MySQL/Apache Error in PHP MySQL query
What all do I need to escape when sending a (My)SQL query?
Auto Generate Database Diagram MySQL
Mechanisms for tracking DB schema changes
How big can a MySQL database get before performance starts to degrade.
Python and MySQL
SQL Server 2005 implementation of MySQL REPLACE INTO?
How to export data from SQL Server 2005 to MySQL
Throw Error In MySQL Trigger
Binary Data in MySQL