Problems with DataSources using JNDI and MySQL

[Jon Dellaria]

I have been using MySql as the database using JSP's and JavaBeans but
recently I have wanted to start using the database connection pooling

mechanism built into TomCat. I think I am having a problem with
defining my JDBC resource within Tomcat. I am pretty sure that my code
is

correct (but I am not certain), and the problem is with defining the
resource in Tomcat. Has anyone done this before? Any advice would be
helpful.

The error in the TomCat Window is:

java.sql.SQLException: Cannot create JDBC driver of class
'org.gjt.mm.mysql.Driver' for connect URL 'null'

The error I am getting in the web browser is:

HTTP Status 500 -

--------------------------------------------------------------------------------

type Exception report

message

description The server encountered an internal error () that prevented
it from fulfilling this request.

exception

java.lang.NullPointerException
at jspbook.ch5.Main.authenticate(Main.java:126)
at jspbook.ch5.Main.doPost(Main.java:49)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:853)
.....
Here is my Main.java:

-----------------

package jspbook.ch5;

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

import jspbook.ch5.CustomerBean;

public class Main extends HttpServlet {

// Connection dbCon;
DataSource ds;
HttpSession session;

/* Initialize servlet. Use JNDI to look up a DataSource */
public void init() {

try {
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
ds = (DataSource) envCtx.lookup("jdbc/QuotingDB");
// dbCon = ds.getConnection();
System.out.println("executed lookup for jdbc. JON");
}
catch (javax.naming.NamingException e) {
System.out.println("A problem occurred while retrieving a
DataSource object");
System.out.println(e.toString());
}
}

public void doPost (HttpServletRequest _req, HttpServletResponse
_res)
throws ServletException, IOException {

/* Refresh session attributes */
session = _req.getSession();
session.removeAttribute("loginError");
session.removeAttribute("submitError");

String action = _req.getParameter("action");

/* Authenticate user if request comes from login page */
if (action.equals("login")) {
String uid = _req.getParameter("UID");
String pwd = _req.getParameter("PWD");
if (authenticate(uid, pwd)) {
session.setAttribute("validUser", "y");
session.setAttribute("loginError", "n");
session.setAttribute("uid", uid);
gotoPage("/WEB-INF/jsp/ch5/census.jsp", _req, _res);
}
/* If the user login fails, then return them to the login page
to retry */
else {
loginError(_req, _res);
}
}

/* Record the survey data if the request comes from the survey
form */
else if (action.equals("submit")) {
/* Make sure the user has logged in before recording the data */
String validUser = (String) session.getAttribute("validUser");
if (validUser.equals("y")) {
if (recordSurvey(_req)) {
/* Reset validUser flag and forward to ThankYou page */
session.removeAttribute("validUser");
gotoPage("/WEB-INF/jsp/ch5/thankyou.jsp", _req, _res);
}
else {
session.setAttribute("submitError", "y");
gotoPage("/ch5/login.jsp", _req, _res);
}
}
/* If the user did not login, then send them to the login page
*/
else {
loginError(_req, _res);
}
}
}

/* Send request to a different page */
private void gotoPage(String _page, HttpServletRequest _req,
HttpServletResponse _res)
throws IOException, ServletException {

RequestDispatcher dispatcher = _req.getRequestDispatcher(_page);
if (dispatcher != null)
dispatcher.forward(_req, _res);

}

/* Set error attributes in session and return to Login page */
private void loginError(HttpServletRequest _req, HttpServletResponse
_res)
throws IOException, ServletException {

session.setAttribute("validUser", "n");
session.setAttribute("loginError", "y");
gotoPage("/ch5/login.jsp", _req, _res);

}

/* Check if the user is valid */
private boolean authenticate(String _uid, String _pwd) {

Connection dbCon = null;
ResultSet rs = null;
try {
System.out.println("try");
dbCon = ds.getConnection();
System.out.println("dbCon = ds.getConnection()");
Statement s = dbCon.createStatement();
System.out.println("Statement s = dbCon.createStatement()");
rs = s.executeQuery("select * from user where id = '"
+ _uid + "' and pwd = '" + _pwd + "'");
System.out.println("rs = s.executeQuery");
return (rs.next());
}
catch (java.sql.SQLException e) {
System.out.println("A problem occurred while accessing the
database.");
System.out.println(e.toString());
}
finally {
try {
dbCon.close();
}
catch (SQLException e) {
System.out.println("A problem occurred while closing the
database.");
System.out.println(e.toString());
}
}

return false;

}

/* Using the CustomerBean, record the data */
public boolean recordSurvey(HttpServletRequest _req) {

Connection dbCon = null;
try {
dbCon = ds.getConnection();
CustomerBean cBean = new CustomerBean();
cBean.populateFromParms(_req);
return cBean.submit(dbCon);
}
catch (java.sql.SQLException e) {
System.out.println("A problem occurred while accessing the
database.");
System.out.println(e.toString());
}
finally {
try {
dbCon.close();
}
catch (SQLException e) {
System.out.println("A problem occurred while closing the
database.");
System.out.println(e.toString());
}
}

return false;
}

public void destroy() {}

}
--------

Here is my web.xml
<?xml version="1.0" encoding="ISO-8859-1"?>

<!DOCTYPE web-app
PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.2//EN"
"http://java.sun.com/j2ee/dtds/web-app_2_2.dtd">

<web-app>
<servlet>
<servlet-name>
Main
</servlet-name>
<servlet-class>
jspbook.ch5.Main
</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>
Main
</servlet-name>
<url-pattern>
/ch5/Main
</url-pattern>
</servlet-mapping>
<taglib>
<taglib-uri>/simple</taglib-uri>
<taglib-location>/WEB-INF/tlds/simple.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>/tableUtils</taglib-uri>
<taglib-location>/WEB-INF/tlds/utils.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>/groceries</taglib-uri>
<taglib-location>/WEB-INF/tlds/groceries.tld</taglib-location>
</taglib>
<resource-ref>
<description>
Resource reference to a factory for java.sql.Connection
instances that may be used for talking to a particular
database that is configured in the server.xml file.
</description>
<res-ref-name>
jdbc/QuotingDB
</res-ref-name>
<res-type>
javax.sql.DataSource
</res-type>
<res-auth>
SERVLET
</res-auth>
</resource-ref>
</web-app>

------------

Any help or direction will be greatly appreciated.
Regards,

Jon Dellaria

[Shyamal Prasad]

"Jon" == jdellaria <jd*******@filenet.com> writes:

Jon> I have been using MySql as the database using JSP's and
Jon> JavaBeans but recently I have wanted to start using the
Jon> database connection pooling

Jon> [...snip....]

Jon> Here is my web.xml

Jon> [...snip....]

Personally, I have had no luck trying to configure the JNDI resources
in web.xml and I believe that it cannot be done (but cannot point to
an authoritative reference). The JNDI resources must be configured in
server.xml AFAIK. If it is possible to do it web.xml I would love to
know how.

What does work for me is to put the Resource definition in the
server.xml file in a context definition for my webapp. This is
somewhat more unwieldy, but it has worked for me for PostgreSQL and
Oracle.

Also, in your sample web.xml you did not provide parameters for the
database user, password and name or a url. Was that intentional?

Here is a Context definition that works for me with PostgreSQL with
the DBCP pooling library (I use a very similar set up for Oracle for
the same application). Make some modifications (like using the MySQL
class instead of org.postgresql.Driver and so on) and it should get
you going:

<Context path="/myapp"
docBase="myapp"
debug="1"
reloadable="true"
crossContext="false">

<Resource name="jdbc/postgresql"
auth="Container"
type="javax.sql.DataSource" />

<ResourceParams name="jdbc/postgresql">

<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFact ory</value>
</parameter>

<parameter>
<name>driverClassName</name>
<value>org.postgresql.Driver</value>
</parameter>

<parameter>
<name>url</name>
<value>jdbc:postgresql://hostname/dbname</value>
</parameter>

<parameter>
<name>username</name>
<value>user</value>
</parameter>

<parameter>
<name>password</name>
<value>password</value>
</parameter>

<parameter>
<name>maxActive</name>
<value>20</value>
</parameter>

<parameter>
<name>maxIdle</name>
<value>10</value>
</parameter>

<parameter>
<name>maxWait</name>
<value>-1</value>
</parameter>

<parameter>
<name>testOnBorrow</name>
<value>true</value>
</parameter>

<parameter>
<name>testOnIdle</name>
<value>true</value>
</parameter>

<parameter>
<name>validationQuery</name>
<value>select current_date</value>
</parameter>

</ResourceParams>
</Context>

You will certainly want to change the validationQuery value to
something valid for MySQL.

Cheers!
Shyamal

[steve souza]

Here is a link that explains how to set up tomcat to work with mysql.

http://www.java-internals.com/code/r...ry/readme.html

steve - http://www.jamonapi.com - a fast, free, open source performance tuning API.