Using jdbc from jsp with MySQL

[Tom Joiner]

Hi, I am attempting to connect to a MySQL database I just created and I believe I am missing some java piece. I have installed the NetBeans IDE 4.1 and MySQL 5.0. I have created users for the database, created tables, and can access the tables from query tools. Now I am hoping to query the database from a jsp page.

 
 
<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>
<%--
The taglib directive below imports the JSTL library. If you uncomment it,
you must also add the JSTL library to the project. The Add Library... action
on Libraries node in Projects view can be used to add the JSTL 1.1 library.
--%>
<%@page import="java.sql.*" %>
<%--
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> 
--%>
 
<%
   String connectionURL = "jdbc:mysql://localhost:3306/database01?user=myuser;password='mypassword'";
   Connection connection = null;
   Statement statement = null;
   ResultSet rs = null;
   
 
%>
 
 <% Class.forName("com.mysql.jdbc.Driver").newInstance();
 
       connection = DriverManager.getConnection(connectionURL, "","");
       statement = connection.createStatement();
       rs = statement.executeQuery("SELECT * FROM table01");
       while (rs.next()) {
        out.println(rs.getString("field01")+"<br>");
       }
  %>
 
   

However, when I try to run this jsp page it yields the following error:

type Exception report
 
message
 
description The server encountered an internal error () that prevented it from fulfilling this request.
 
exception
 
javax.servlet.ServletException: com.mysql.jdbc.Driver
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:846)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:779)
org.apache.jsp.index_jsp._jspService(index_jsp.java:182)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:99)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:325)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:245)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:362)
 
root cause
 
java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1332)
org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1181)
org.apache.jasper.servlet.JasperLoader.loadClass(JasperLoader.java:150)
org.apache.jasper.servlet.JasperLoader.loadClass(JasperLoader.java:71)
java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319)
java.lang.Class.forName0(Native Method)
java.lang.Class.forName(Class.java:164)
org.apache.jsp.index_jsp._jspService(index_jsp.java:60)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:99)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:325)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:245)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:362)
    

Any ideas?

[Shailesh Chandra]

I think you have forgot to place jar file of mysql driver in WEB-INF/lib jar file


Shailesh

[Tom Joiner]

That is some great advice!

Where do I obtain the JAR file? I have been looking all over the web for hours on getting this thing going and I really appreciate any pointers you might have.

[Shailesh Chandra]

I haven't worked on mysql so I am not quite sure about jar file, But look for mysql-connector-java-3.1.12-bin.jar

shailesh

[Tom Joiner]

Thanks Shailesh,

Okay, I found a link where I can get the jdbc driver for the mysql server. It is located here for anyone following this thread:

http://www.mysql.com/products/connector/j/

It unzips into a ton of help files, and then also the file

mysql-connector-java-3.1.13-bar.jar

I tried copying this into my Web-INF folder so I placed

mysql-connector-java-3.1.13-bar.jar into the folder
c:\Dev\Web\Netbeans_projects\Web\WEB-INF

I am still getting this error when running the web page

HTTP Status 500 -
 
type Exception report
 
message
 
description The server encountered an internal error () that prevented it from fulfilling this request.
 
exception
 
javax.servlet.ServletException: com.mysql.jdbc.Driver
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:846)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:779)
org.apache.jsp.index_jsp._jspService(index_jsp.java:183)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:99)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:325)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:245)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:362)
 
root cause
 
java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1332)
org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1181)
org.apache.jasper.servlet.JasperLoader.loadClass(JasperLoader.java:150)
org.apache.jasper.servlet.JasperLoader.loadClass(JasperLoader.java:71)
java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319)
java.lang.Class.forName0(Native Method)
java.lang.Class.forName(Class.java:164)
org.apache.jsp.index_jsp._jspService(index_jsp.java:60)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:99)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:325)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:245)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:362)
 
note The full stack trace of the root cause is available in the Apache Tomcat/5.5.7 logs.
 

Do I need to register the jar file somehow? Change the classpath perhaps? Any ideas?
[ September 26, 2006: Message edited by: Tom Joiner ]

[Rajesh Agarwal]

Add the full path to the classpath variable of the server startup script.

[George Stoianov]

When you run a jsp in a servlet container the servlet container compiles the jsp into a servlet and runs it. That is why you should put it where the servlet container can find it so that it can compile your code.

If you are using Tomcat you can place it in <JAKARTA_HOME>/common/lib so that it is available for all applications you will write using mysql or in the lib folder of the respective webapp. If you are not using Tomcat the solution is pretty much the same find the manual and you will figure it out.

Good luck!
George

[Tom Joiner]

Thanks for the idea. I am not sure where the startup script is, I assume it is accessible from the IDE. So I have now tried the following:

going to the web project, right click for properties.
Choose libraries
Choose the tab that says "compile tests"

Then, I click "add jar/folder" and browse to the jar file

Then choose the tab that says "run tests"

Then I click "add jar/folder" and browse to the jar file

Now I would assume this would be added to my class path for startup

but it doesnt work

ideas?

[Deyu Wang]

I think you should do this :
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
}

replace to:

{
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
}

[Tom Joiner]

Thanks for all the ideas.

After much experimentation, it turned out I was putting the jar file into
WEB-INF/Lib/Jar

and it needed to go into
WEB-INF/Lib

So now I can query my database! It is time to start constructing accounts, and putting together some actual content, servlets, and so forth.

Thank you all for your assistance in this. It is very frustrating to come against a large toolkit which has many layers of possibilities of functioning. Your answers provided me with the needed context with which to effectively search on google.