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

Question

基本上，我的 Web 应用程序中有两个类，一个类“FormProcess.java”，它将信息插入到 HTML 表单中，执行一些计算，然后将结果输出为 HTML。这绝对没问题。

我还有一个用于将数据添加到 MySQL 数据库的新行中的类，该类称为“SqlConnect.java”。如果我在服务器上单独运行它，这也能正常工作(即，它正确地将一行插入到表中)。

我正在努力解决的问题是让 SqlConnect 方法从 FormProcess 类运行。当我将以下内容添加到 FormProcess 时:

private SqlConnect sql;

在一个方法中:

sql.doPost(request, response);

我收到以下错误:

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)

目前这是 SqlConnect 类(我省略了导入等):

@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(); }
        }
    }

}

我敢肯定有一种简单的方法可以做到这一点，但我无法解决它，正如我所说的，SqlConnect 类将自行运行良好。

干杯

Answer 1

不幸的是，您现在的类结构存在缺陷。 SqlConnect 不应该是 HttpServlet 的子类，因为人们不应该像网页一样直接调用它。相反，只有 FormProcess 和您的其他实际 servlet 应该访问 SqlConnect。

现在，您的代码应如下所示:

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(); }
    }
}

然后，在 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);
}

这是一种更合乎逻辑的方法来模拟您的网站应该如何工作。注意:现在的查询完全容易受到 SQL 注入(inject)的攻击。按照此处的说明将您的 Statement 更改为 PreparedStatement:http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html