java - 库存管理系统、MySQL、Netbeans 上的更新按钮出现问题

Question

我有一个简单的库存管理系统，可以通过 mySQL 数据库插入、编辑和删除项目。当我尝试更新任何信息时，它会运行此错误。

Apr 03, 2019 3:49:08 PM java_project_1_2.Main_Window btn_updateActionPerformed
SEVERE: null
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE id = '115'' at line 1
 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
 at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
 at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
 at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
 at com.mysql.jdbc.Util.getInstance(Util.java:386)
.....

我的库存管理系统还处理照片，因此在没有任何照片的情况下更新时，我自然有第二个代码块。但是当我这样做时，它会以弹出窗口的形式抛出另一个错误，提示“没有为参数 7 指定值。

这是我的更新按钮的所有代码。

private void btn_updateActionPerformed(java.awt.event.ActionEvent evt) {

        if(checkInputs() && txt_id.getText() != null)
        {
            String UpdateQuery = null;
            PreparedStatement ps = null;
            Connection con = getConnection();

            //update without image
            if(ImgPath == null)
            {
                try {
                    UpdateQuery = "UPDATE products SET name = ?, price = ?"
                            + ", add_date = ?, category = ?, quantity = ?, WHERE id = ?";
                    ps = con.prepareStatement(UpdateQuery);

                    ps.setString(1, txt_name.getText());
                    ps.setString(2, txt_price.getText());

                    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                    String addDate = dateFormat.format(txt_AddDate.getDate());

                    ps.setString(3, addDate);

                    ps.setInt(4, Integer.parseInt(txt_id.getText()));

                    String value = combo_category.getSelectedItem().toString();
                    ps.setString(5, value);

                    ps.setString(6, txt_quantity.getText());


                    ps.executeUpdate();
                    Show_Products_In_JTable();
                    JOptionPane.showMessageDialog(null, "Product Updated");

                } catch (SQLException ex) {
                    Logger.getLogger(Main_Window.class.getName()).log(Level.SEVERE, null, ex);
                }

            }
            //update with Image
            else{
                try{
                InputStream img = new FileInputStream(new File(ImgPath));

                UpdateQuery = "UPDATE products SET name = ?, price = ?"
                            + ", add_date = ?,image = ?, category = ?, quantity = ?, WHERE id = ?";

                    ps = con.prepareStatement(UpdateQuery);

                    ps.setString(1, txt_name.getText());
                    ps.setString(2, txt_price.getText());

                    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                    String addDate = dateFormat.format(txt_AddDate.getDate());

                    ps.setString(3, addDate);

                    ps.setBlob(4, img);

                    String value = combo_category.getSelectedItem().toString();
                    ps.setString(5, value);


                    ps.setInt(6, Integer.parseInt(txt_id.getText()));

                    ps.executeUpdate();
                    Show_Products_In_JTable();
                    JOptionPane.showMessageDialog(null, "Product Updated");

            }catch(Exception ex)      
            {
                    JOptionPane.showMessageDialog(null, ex.getMessage());
            }
        }
        }else{
            JOptionPane.showMessageDialog(null, "One or More Fields Are Exmpty Or Wrong");
        } 
    }

Answer 1

不应在任何语句关键字前放置逗号。

private void btn_updateActionPerformed(java.awt.event.ActionEvent evt) {

    if(checkInputs() && txt_id.getText() != null)
    {
        String UpdateQuery = null;
        PreparedStatement ps = null;
        Connection con = getConnection();

        //update without image
        if(ImgPath == null)
        {
            try {
                UpdateQuery = "UPDATE products SET name = ?, price = ?"
                        + ", add_date = ?, category = ?, quantity = ? WHERE id = ?";
                ps = con.prepareStatement(UpdateQuery);

                ps.setString(1, txt_name.getText());
                ps.setString(2, txt_price.getText());

                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                String addDate = dateFormat.format(txt_AddDate.getDate());

                ps.setString(3, addDate);

                ps.setInt(4, Integer.parseInt(txt_id.getText()));

                String value = combo_category.getSelectedItem().toString();
                ps.setString(5, value);

                ps.setString(6, txt_quantity.getText());


                ps.executeUpdate();
                Show_Products_In_JTable();
                JOptionPane.showMessageDialog(null, "Product Updated");

            } catch (SQLException ex) {
                Logger.getLogger(Main_Window.class.getName()).log(Level.SEVERE, null, ex);
            }

        }
        //update with Image
        else{
            try{
            InputStream img = new FileInputStream(new File(ImgPath));

            UpdateQuery = "UPDATE products SET name = ?, price = ?"
                        + ", add_date = ?,image = ?, category = ?, quantity = ? WHERE id = ?";

                ps = con.prepareStatement(UpdateQuery);

                ps.setString(1, txt_name.getText());
                ps.setString(2, txt_price.getText());

                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                String addDate = dateFormat.format(txt_AddDate.getDate());

                ps.setString(3, addDate);

                ps.setBlob(4, img);

                String value = combo_category.getSelectedItem().toString();
                ps.setString(5, value);


                ps.setInt(6, Integer.parseInt(txt_id.getText()));

                ps.executeUpdate();
                Show_Products_In_JTable();
                JOptionPane.showMessageDialog(null, "Product Updated");

        }catch(Exception ex)      
        {
                JOptionPane.showMessageDialog(null, ex.getMessage());
        }
    }
    }else{
        JOptionPane.showMessageDialog(null, "One or More Fields Are Exmpty Or Wrong");
    } 
}