Unable to update mysql database through servlets

Question

I wrote a servlet file SignUp.java and a helper file Updater.java. SignUp gathers the user info from a form filled by the user. Then it sends the info to Updater so that Updater updates the table in the database using the info. But when I fill the form and click submit, nothing changes. The weird thing is that when I wrote a test file and used it to supply the Updater with some dummy values, the table was updated with those dummy values. -
SignUp.java

package com.onlineshopping.web;

import com.onlineshopping.model.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class SignUp extends HttpServlet
{
public void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException
{
    String[] month = {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"};

    InfoHolder userInfo = new InfoHolder();
    userInfo.name = req.getParameter("name");
    userInfo.email = req.getParameter("email");
    userInfo.date = Integer.parseInt(req.getParameter("date"));
    userInfo.month = Arrays.asList(month).indexOf(req.getParameter("month")) + 1;
    userInfo.year = Integer.parseInt(req.getParameter("year"));
    userInfo.pwd = req.getParameter("pwd");
    userInfo.subscribe = "n";
    try
    {
        userInfo.subscribe = req.getParameter("subscribe");
    }
    catch(Exception e)
    {}

    Updater u = new Updater();
    u.update(userInfo);
}
}

InfoHolder just holds the information.

Updater.java

package com.onlineshopping.model;

import java.io.*;
import java.sql.*;
import java.util.*;
public class Updater
{
private static final String DRIVER = "com.mysql.jdbc.Driver";

private static final String CONNECTION = "jdbc:mysql://localhost/onlineshopping";

public void update(InfoHolder userInfo)
{

    try
    {
        Class.forName(DRIVER);

        Properties p = new Properties();
        p.put("user","****");
        p.put("password","****");

        Connection c = DriverManager.getConnection(CONNECTION,p);

        Statement s = c.createStatement();

        ResultSet rs = s.executeQuery("SELECT MAX(userid)+1 FROM user_info");
        rs.next();
        int id = rs.getInt(1);

        PreparedStatement ps = c.prepareStatement("INSERT INTO user_info VALUES (?,?,?,?,?,?,?)");
        ps.setInt(1,id);
        ps.setString(2,userInfo.name);
        ps.setString(3,userInfo.email);
        ps.setInt(4,userInfo.date);
        ps.setInt(5,userInfo.month);
        ps.setInt(6,userInfo.year);
        ps.setString(7,userInfo.subscribe);
        ps.executeUpdate();

        c.close();
    }

    catch(Exception e)
    {
        try
        {
            PrintWriter out = new PrintWriter(new FileWriter("err", true));
            out.println("Error: "+ e);
            out.close();
        }
        catch(IOException e)
        {}
    }
}
}

test.java

import com.onlineshopping.model.*;

public class test
{

public static void main(String[] args)
{
    Updater u = new Updater();

    InfoHolder xyz = new InfoHolder();
    xyz.name = "check";
    xyz.email = "checkmail";
    xyz.date = 1;
    xyz.month = 1;
    xyz.year = 2;
    xyz.pwd = "pw";
    xyz.subscribe = "n";

    u.update(xyz);
}
}

The Updater class file keeps throwing - java.lang.ClassNotFoundException: com.mysql.jdbc.Driver. But when I run the test class file from the terminal the same Updater class runs smoothly.

Is there a problem with my CLASSPATH variable:
It has this value - .:/usr/share/java/mysql.jar:/usr/share/java/mysql-connector-java-5.1.16.jar:/usr/lib/tomcat6/lib/servlet-api.jar:classes.
Please Help.

Front end of the application is some JSP/JSF ? Do you create your application as a Java EE app or Java SE app ?:) Because the fact that your test class works and servlet don't could be in the fact that you use Java SE when you query database with test class. — Reshi, Feb 21 '13 at 08:31
You should use a connection pool through a JNDI datasource, instead of hardwiring your connection parameters into your model. — , Feb 21 '13 at 08:54
@Reshi I'm not sure wether it's java SE or EE. All I've done is write those files, compile them and run them using tomcat6. Theoretically, if the test file can run the updater file without a problem the container tomcat6 should be able to as well. Anyway can you tell me how I should correct this problem? — Yasser Hussain, Feb 21 '13 at 09:56

score 0 · Accepted Answer · answered Feb 21 '13 at 08:36

0

Try to copy mysql-connector-java-5.1.16.jar into /usr/lib/tomcat6/lib/

answered Feb 21 '13 at 08:36

Damiano

360
1
8

Yes your answer works. And I guess the problem was that tomcat doesn't look into the CLASSPATH variable (because my classpath variable does mention the path of the Driver package) to find all the sources. All it does is look in its own libraries for finding the required classes. But if you could tell me how to make tomcat look into the classpath variable and find packages from there, then that would be better. – Yasser Hussain Feb 21 '13 at 10:20
I've never had the need to do something like that, so I can't answer on the fly. Try to look at Tomcat documentation: http://tomcat.apache.org/tomcat-7.0-doc/class-loader-howto.html – Damiano Feb 25 '13 at 10:52

Unable to update mysql database through servlets

1 Answers1