java - 创建表并写入 Servlet

Question

我在数据库中创建表时遇到问题。该代码似乎没有任何错误，但也没有输出任何内容。我尝试过调试它，似乎问题出在连接或语句对象上，我认为......

如果有人可以提供帮助，那就太好了，谢谢。

import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import sun.jdbc.odbc.JdbcOdbcDriver;

/**
 * @author 
 */
@WebServlet(urlPatterns = {"/FoodServlet"})
public class FoodServlet extends HttpServlet {

    private Connection con;
    private Statement stmt;
    private ResultSet rs;
    private PreparedStatement pstmt;

    //Servlet initialization method creates con/stmt objects and loads jdbc drivers
    @Override
    public void init() throws ServletException {
        try {
            new JdbcOdbcDriver();
            String url = "jdbc:odbc:Food";
            String user = "";
            String password = "";
            con = DriverManager.getConnection(url, user, password);
            stmt = con.createStatement();

        } catch (SQLException e) {
            e.printStackTrace();
            System.exit(1);
        }
    }

    /**
     * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
     * methods.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     * @throws java.sql.SQLException
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException, SQLException {
        response.setContentType("text/html;charset=UTF-8");
        try (PrintWriter out = response.getWriter()) {

            //delete the database
            stmt.executeUpdate("DROP TABLE IF EXISTS Foods");

            //set up the database
            stmt.executeUpdate("CREATE TABLE Foods (ItemID Integer PRIMARY KEY AUTOINCREMENT, ItemName VARCHAR(20), Likes Integer)");
            stmt.executeUpdate("INSERT INTO Foods VALUES ('Ice Cream', 0)");
            stmt.executeUpdate("INSERT INTO Foods VALUES ('Hamburger', 0)");
            stmt.executeUpdate("INSERT INTO Foods VALUES ('Pizza', 0)");

            //set likeValues for data storage
            int likeValue;

            //set strings for queries and update statements
            String query = "SELECT Likes FROM Foods WHERE ItemName = ?";
            String update = "UPDATE Foods SET Likes = ? WHERE ItemName = ?";

            //set food to request parameter from html
            String food = request.getParameter("food");

            //get current likes and increase by one, store in 'likeValue'
            pstmt = con.prepareStatement(query);
            pstmt.setString(1, food);
            rs = pstmt.executeQuery();
            likeValue = rs.getInt(1) + 1;

            //write updated 'likeValue' to the database
            pstmt = con.prepareStatement(update);
            pstmt.setInt(1, likeValue);
            pstmt.setString(2, food);
            pstmt.executeUpdate();

            //output the data from the database
            rs = stmt.executeQuery("SELECT ItemID, ItemName, Likes FROM Foods");
            out.println("ItemID ItemName Likes");
            while (rs.next()) {
                out.print(rs.getInt(1) + "\t\t" + rs.getString(2) + "\t\t"
                        + rs.getInt(3));
            }

        } finally {

        }
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP <code>GET</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            processRequest(request, response);
        } catch (SQLException ex) {
            Logger.getLogger(FoodServlet.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Handles the HTTP <code>POST</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            processRequest(request, response);
        } catch (SQLException ex) {
            Logger.getLogger(FoodServlet.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>

}

Answer 1

因此，经过大量工作后，我发现连接对象是第一个问题，我不确定如何修复它...然后我遇到了空指针异常问题，我通过将 exceuteUpdate() 放入其中解决了这个问题一个 if 语句，用于检查 html 页面中的值。然后我遇到了一个错误，其中涉及我的表值之一的拼写不正确。

这是更新后的代码，现在可以工作了，它不太漂亮，但可以工作。谢谢大家的帮助!

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.DriverManager;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import sun.jdbc.odbc.*;

/**
 *
 * @author 
 */
@WebServlet(urlPatterns = {"/FoodServlet"})
public class FoodServlet extends HttpServlet {

    private Connection con;
    private Statement stmt;
    private ResultSet rs;
    private PreparedStatement pstmt;
    private DatabaseMetaData dbmd;

    //Servlet initialization method creates con/stmt objects and loads jdbc drivers
    public void init() throws ServletException {
        try {
            new JdbcOdbcDriver();
            String url = "jdbc:odbc:Java3DB";
            String user = "";
            String pword = "";
            con = DriverManager.getConnection(url, user, pword);
            stmt = con.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
            System.exit(1);
        }
    }

    /**
     * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
     * methods.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     * @throws java.sql.SQLException
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException, SQLException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        try {
            //open html tags
            out.println("<!DOCTYPE html>");
            out.println("<html>");
            out.println("<head>");
            out.println("<title>FoodServlet</title>");
            out.println("</head>");
            out.println("<body>");

            //set up the table
            createTable();

            //set strings for queries and update statements
            String update = "UPDATE Foods SET likes = (likes + 1) WHERE itemName = ?";

            //set foodValue to request parameter from html
            String foodValue = null;
            foodValue = request.getParameter("food");
            //update only if there is a value stored in the food parameter
            if (foodValue != null) {
                //out.println(foodValue);
                pstmt = con.prepareStatement(update);
                pstmt.setString(1, foodValue);
                pstmt.executeUpdate();
            }

            //output the data from the database
            rs = stmt.executeQuery("SELECT itemID, itemName, likes FROM Foods");
            out.println("ItemID&nbsp&nbspItemName&nbsp&nbspLikes <BR>");

            while (rs.next()) {
                out.print(+rs.getInt(1) + "&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp" 
                        + rs.getString(2) + "&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp" 
                        + rs.getInt(3) + "<br>");
            }

            out.println("</body>");
            out.println("</html>");
        } catch (SQLException e) {
            e.printStackTrace();
            out.print(e.getMessage());
        }
    }

    //destroy method to close the servlet
    public void destroy() {
        try {
            stmt.close();
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(FoodServlet.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void createTable() throws SQLException {
        dbmd = con.getMetaData();
        rs = dbmd.getTypeInfo();
        //checks for the Foods table, create only if not table is found
        if (rs.next() == false) {
            //set up the database
            stmt.executeUpdate("CREATE TABLE Foods (itemID INTEGER, itemName VARCHAR(20), likes Integer)");
            stmt.executeUpdate("INSERT INTO Foods VALUES (1, 'IceCream', 0)");
            stmt.executeUpdate("INSERT INTO Foods VALUES (2, 'Hamburger', 0)");
            stmt.executeUpdate("INSERT INTO Foods VALUES (3, 'Pizza', 0)");
        }
    }

// <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP <code>GET</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            processRequest(request, response);
        } catch (SQLException ex) {
            Logger.getLogger(FoodServlet.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Handles the HTTP <code>POST</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            processRequest(request, response);
        } catch (SQLException ex) {
            Logger.getLogger(FoodServlet.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>

}