Java / MySQL: Inserting row into table from Java Web Application (TomCat v7.0)

Question

Basically, I have two classes in my web application, one class 'FormProcess.java', which takes information inserting into an HTML form, performs a few calculations, then outputs the result as HTML. This works absolutely fine.

I also have a class for adding the data into a new row of a MySQL database, the class is called 'SqlConnect.java'. This also works fine if I run it seperately on the server (ie. it correctly inserts a row on to the table).

What I am struggling with is getting the SqlConnect methods to run from the FormProcess class. When I add the following to FormProcess:

private SqlConnect sql;

and in a method:

sql.doPost(request, response);

I get the following error:

java.lang.NullPointerException
crunch.FormProcess.doGet(FormProcess.java:27)
javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
javax.servlet.http.HttpServlet.service(HttpServlet.java:728)

This is the SqlConnect class at the moment (I left out the imports etc.):

@WebServlet(name="sql",urlPatterns={"/sql"})
public class SqlConnect extends javax.servlet.http.HttpServlet {

    FormProcess process = new FormProcess();

    private static final long serialVersionUID = 1L;

    public SqlConnect() {

    }

    public void doGet(HttpServletRequest request,
            HttpServletResponse response) throws ServletException, IOException {
        performTask(request, response);
    }

    public void doPost(HttpServletRequest request,
            HttpServletResponse response) throws ServletException, IOException {
        performTask(request, response);
    }

    public void performTask(HttpServletRequest request,
            HttpServletResponse response) throws ServletException, IOException {
        testJndiDataSource();
    }

    public void testJndiDataSource() {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            InitialContext ctx = new InitialContext();
            DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/CrunchDB");

            conn = ds.getConnection();

            st = conn.createStatement();
            st.executeUpdate("INSERT INTO log " + "VALUES (20, '1', '1', " + process.salaryInt + ", "+ process.takeHomePAYE +", "+ process.takeHomeContractor +", 2)");


        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try { if (rs != null) rs.close(); } catch (SQLException e) { e.printStackTrace(); }
            try { if (st != null) st.close(); } catch (SQLException e) { e.printStackTrace(); }
            try { if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); }
        }
    }

}

I'm sure there is a simple way of doing this I just can't work it out, as I said the SqlConnect class will run fine on it's own.

Cheers

Your query should like this than that may be work..... "INSERT INTO log (COLUMN_NAME1,COLUMN_NAME2,COLUMN_NAME3,COLUMN_NAME4 ) " + "VALUES (20, '1', '1', " + process.salaryInt + ", "+ process.takeHomePAYE +", "+ process.takeHomeContractor +", 2)" — Bhushankumar Lilapara
– Bhushankumar Lilapara, Commented Oct 6, 2013 at 17:42
The problem doesn't lie in the query, it's in the way I am calling the method from FormProcess, as the query works fine on its own — Tom Haddad
– Tom Haddad, Commented Oct 6, 2013 at 17:43

musical_coder · Accepted Answer · 2013-10-06 18:02:42Z

2

Unfortunately, the class structure you have right now is flawed. SqlConnect should not be a subclass of HttpServlet, because people should never be calling it directly as though it was a webpage. Instead, only FormProcess and your other actual servlets should access SqlConnect.

So now, your code should look something like this:

public void testJndiDataSource(int salaryInt, double takeHomePAYE, String takeHomeContractor) {
    Connection conn = null;
    Statement st = null;
    ResultSet rs = null;
    try {
        InitialContext ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/CrunchDB");

        conn = ds.getConnection();

        st = conn.createStatement();
        st.executeUpdate("INSERT INTO log " + "VALUES (20, '1', '1', " + process.salaryInt + ", "+ process.takeHomePAYE +", "+ process.takeHomeContractor +", 2)");


    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        try { if (rs != null) rs.close(); } catch (SQLException e) { e.printStackTrace(); }
        try { if (st != null) st.close(); } catch (SQLException e) { e.printStackTrace(); }
        try { if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); }
    }
}

Then, in FormProcess:

public void performTask(HttpServletRequest request,
        HttpServletResponse response) throws ServletException, IOException {
    int salaryInt = //extract from form... 
    double takeHomePAYE = //extract from form... 
    String takeHomeContractor = //extract from form... 
    SqlConnect sqlConnect = new SqlConnect();
    sqlConnect.testJndiDataSource(salaryInt, takeHomePAYE, takeHomeContractor);
}

This is a much more logical approach to modeling how your site should work. Note: the query as it is right now is completely vulnerable to SQL injection. Change your Statement to a PreparedStatement per the instructions here: http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

edited Oct 6, 2013 at 18:02

answered Oct 6, 2013 at 17:56

musical_coder

3,8963 gold badges17 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tom Haddad Over a year ago

Thanks for your detailed answer, however I am still getting this error: java.lang.NullPointerException, on the line where I call 'testJndiDataSource', not sure why this is the case?

musical_coder Over a year ago

Made a correction just now in performTask(); does that fix it?

Tom Haddad Over a year ago

Sorted it out myself, needed to remove the initialisation of FormProcess in SqlConnect, thanks a lot for your help!! :)

Collectives™ on Stack Overflow

Java / MySQL: Inserting row into table from Java Web Application (TomCat v7.0)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related