views:

71

answers:

2

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
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
+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
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
@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
@BalusC: Thanks for the tip! I will post the result in edit shortly.
masato-san
@masato: Are you using Tomcat plugin in Eclipse? Everything get logged into Eclipse console. You may need to switch the displayed console.
BalusC
@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
@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
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