How to combine a simple Servlet with JDBC? Code results in ClassNotFoundException

Question

I have written a simple JDBC and it works well (I run it in command prompt using java and javac).

I also have written a simple HelloworldServlet and it works well (using Apache Tomcat 7.0). I access the servlet using browser, http://www.myhost.com:8088/projecthello/servlethello, and it displays the Helloworld.

Now I'm trying to combine a simple servlet and JDBC. I want to make a query SELECT to a table, and display it in my browser using this URL: http://www.myhost.com:8088/projectmovie/directors-view

In folder /WEB-INF/classes, I have 2 files: DirectorsViewServlet.java and DirectorSelect.java

Here's DirectorsViewServlet.java:

 import java.io.*;
 import javax.servlet.*;
 import javax.servlet.http.*;
 import java.sql.*;

 public class DirectorsViewServlet extends HttpServlet {

  public void doGet(HttpServletRequest req, HttpServletResponse res) throws IOException, ServletException {
    res.setContentType("text/html");
    PrintWriter out = res.getWriter();
    DirectorSelect ds = new DirectorSelect();
    out.println(ds.selectDirectors());
  }

  public void doPost(HttpServletRequest req, HttpServletResponse res) throws IOException, ServletException {
    doGet(req, res);
  }

}

And here's the DirectorSelect.java:

 import java.sql.*;

 class DirectorSelect{
  public String selectDirectors() {
    Connection koneksi = null;
    Statement stat = null;
    String str = "";
    try{
        Class.forName("com.mysql.jdbc.Driver");
        koneksi = DriverManager.getConnection("jdbc:mysql://localhost/dbizza","root","");
        stat = koneksi.createStatement();
        ResultSet hasil = stat.executeQuery("SELECT * FROM directors");
        while (hasil.next()) {
            str = str + (hasil.getInt(1) + "\t" + hasil.getString(2)+ "\t" + hasil.getString(3)+ "\t" + hasil.getDate(4)+ "\t" + hasil.getString(5));
        }
        stat.close();
        koneksi.close();
    } catch (SQLException sqle) {
        str = "SQLException error";
    } catch (ClassNotFoundException cnfe) {
        str = "ClassNotFoundException error";
    }
    return str;
 }
}

And here's my web.xml file:

<?xml version="1.0" encoding="ISO-8859-1"?>
<web-app xmlns="http://java.sun.com/xml/ns/javaee"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 
 http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
 version="2.5"> 
<servlet>
    <servlet-name>DirViewServlet</servlet-name>
    <servlet-class>DirectorsViewServlet</servlet-class>
</servlet>
<servlet-mapping>
    <servlet-name>DirViewServlet</servlet-name>
    <url-pattern>/directors-view</url-pattern>
</servlet-mapping>
</web-app>

The problem is, when I access it in my browser with this local URL http://www.myhost.com:8088/projectmovie/directors-view

The result is "ClassNotFoundException error". How to solve it?

I read someone saying servlet and JDBC are somewhat orthogonal tecnology. Any further explanation of what is that mean? And why?

Thanks.

Solution

A ClassNotFoundException is a rather simple exception, unrelated to servlets/JDBC (look, it's from java.lang package, indicating basic Java, not from javax.servlet nor java.sql package which would otherwise indicate Servlet or JDBC problem). It just means that the in the exception message mentioned class is missing in the runtime classpath.

Unfortunately, the in the exception message mentioned class is in your case nowhere printed/logged. The exception handling in the given code is terribly poor. The code is completely suppressing the exception and returning a custom message as if it's a result from the DB. The caller of that code couldn't even distinguish if the DB interaction has succeeded or not. If you rewrite the code in such way that it properly throws the exception, or at least prints its stack trace, then you should see the missing class in the exception message like so

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
    at ....

This is in turn self-explaining: the JDBC driver class (or in this particular case, the JAR file containing the JDBC driver class) is missing in the runtime classpath.

Just drop the JDBC driver JAR file in webapp's runtime classpath. The /WEB-INF/lib folder participates in the webapp's runtime classpath. Just drop the JAR file straight in there and rebuild/redeploy/restart.

See also:

• java.lang.ClassNotFoundException in spite of using CLASSPATH environment variable
• How to add JAR libraries to WAR project without facing java.lang.ClassNotFoundException? Classpath vs Build Path vs /WEB-INF/lib

---

Unrelated to the concrete problem, I recommend to carefully read this post for a proper kickoff example of JSP+Servlet+JDBC: Show JDBC ResultSet in HTML in JSP page using MVC and DAO pattern. Not only is the code poor in handling of exceptions, it's also poor in handling of DB resources. Those may leak away in case of exceptions.

Comments

[Montre]

"orthogonal" means that servlets do something completely different than JDBC, and that inherently servlets don't need JDBC for anything and JDBC doesn't need servlets anything. The concerns that they solve are independent. The consequence is that to combine one and the other, you shouldn't need to do anything in particular. In general, any working JDBC code can be included anywhere in working servlet code. (This of course doesn't imply it makes sense to do whatever you want to do with JDBC just anywhere, just that you can.)

[izza]

@ BalusC: Thanks for your answer. You just saved me a lot of time! I know my code is really poor. I'm on my way in learning how to write a better code.

[izza]

@ BalusC: Also, this simple servlet code is just my kickoff to start learning to know how servlet works. After this, I'll move to JSP and MVC (Spring, JSF, Hibernate, etc).