java - 在数据库中搜索名称并删除条目

Question

我正在制作一个 Java 程序，我可以在其中使用 Java 制作本地食谱并使用 SQL 命令访问数据库。我试图通过在文本字段中输入名称来删除数据库条目，然后删除数据库中具有相同名称的匹配条目。我遇到一个奇怪的错误，它说“executeQuery()”不能用作更新，但我仍然有更新方法。如果有人能找出我需要做什么来解决这个问题，我将非常感激。

谢谢!

    /** Creates new form deleteRecipe */
    public deleteRecipe() {
        initComponents();
    }

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">                          
    private void initComponents() {

        txtName = new javax.swing.JTextField();
        jLabel1 = new javax.swing.JLabel();
        btnDelete = new javax.swing.JButton();
        btnBack = new javax.swing.JButton();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

        txtName.setFont(new java.awt.Font("Lucida Grande", 0, 18)); // NOI18N
        txtName.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                txtNameActionPerformed(evt);
            }
        });

        jLabel1.setFont(new java.awt.Font("Lucida Grande", 1, 18)); // NOI18N
        jLabel1.setText("What Recipe do you want to delete?");

        btnDelete.setText("Delete!");
        btnDelete.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnDeleteActionPerformed(evt);
            }
        });

        btnBack.setText("Back");
        btnBack.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnBackActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addGap(87, 87, 87)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                            .addComponent(jLabel1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .addComponent(txtName)))
                    .addGroup(layout.createSequentialGroup()
                        .addGap(199, 199, 199)
                        .addComponent(btnDelete, javax.swing.GroupLayout.PREFERRED_SIZE, 101, javax.swing.GroupLayout.PREFERRED_SIZE))
                    .addGroup(layout.createSequentialGroup()
                        .addContainerGap()
                        .addComponent(btnBack)))
                .addContainerGap(80, Short.MAX_VALUE))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                .addComponent(btnBack)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 69, Short.MAX_VALUE)
                .addComponent(jLabel1)
                .addGap(30, 30, 30)
                .addComponent(txtName, javax.swing.GroupLayout.PREFERRED_SIZE, 47, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(38, 38, 38)
                .addComponent(btnDelete, javax.swing.GroupLayout.PREFERRED_SIZE, 45, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(34, 34, 34))
        );

        pack();
    }// </editor-fold>                        

    private void txtNameActionPerformed(java.awt.event.ActionEvent evt) {                                        
        // TODO add your handling code here:
    }                                       

    private void btnBackActionPerformed(java.awt.event.ActionEvent evt) {                                        
        this.dispose(); 
        CookbookApp ca = new CookbookApp();
        ca.setVisible(true);
    }                                       

    private void btnDeleteActionPerformed(java.awt.event.ActionEvent evt) {                                          
        ArrayList<Cookbook  > recipes = new ArrayList();
        String name = txtName.getText();
        try {
            String url = "jdbc:derby://localhost:1527/Cookbook";
            Connection conn = DriverManager.getConnection(url);
            String query = "INSERT into RECIPES(NAME, SUBCATEGORY, CATEGORY, INGREDIENTS, INSTRUCTIONS, MODIFICATIONS) values(?,?,?,?,?,?)";
            PreparedStatement pst = conn.prepareStatement(query);
            pst.setString(1, name);
            ResultSet rs = pst.executeQuery();
            Cookbook recipe;

            while (rs.next()) {
                recipe = new Cookbook(rs.getString("NAME"), rs.getString("SUBCATEGORY"), rs.getString("CATEGORY"), rs.getString("INGREDIENTS"), rs.getString("INSTRUCTIONS"), rs.getString("MODIFICATIONS"));
                if (rs.getString(1).equalsIgnoreCase(name))
                recipes.remove(recipe);
            }
            pst.executeUpdate();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);
        }

//    }
//     public ArrayList<Cookbook> recipes() {
//        ArrayList<Cookbook  > recipes = new ArrayList();
//        
//        try {
//            String url = "jdbc:derby://localhost:1527/Cookbook";
//            Connection conn = DriverManager.getConnection(url);
//            String query = "SELECT * FROM RECIPES";
//            PreparedStatement pst = conn.prepareStatement(query);
//            ResultSet rs = pst.executeQuery();
//
//            Cookbook recipe;
//            
//            pst.setString(1, name);
//            
//            
//            while (rs.next()) {
//                recipe = new Cookbook(rs.getString("NAME"), rs.getString("SUBCATEGORY"), rs.getString("CATEGORY"), rs.getString("INGREDIENTS"), rs.getString("INSTRUCTIONS"), rs.getString("MODIFICATIONS"));
//                if (rs.getString(1).equalsIgnoreCase(name))
//                recipes.remove(recipe);
//            }
//        } catch (Exception e) {
//            JOptionPane.showMessageDialog(null, e);
//        }
//        return recipes;
//    
    }                                         

    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        /* Set the Nimbus look and feel */
        //<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) ">
        /* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and feel.
         * For details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html 
         */
        try {
            for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
                if ("Nimbus".equals(info.getName())) {
                    javax.swing.UIManager.setLookAndFeel(info.getClassName());
                    break;
                }
            }
        } catch (ClassNotFoundException ex) {
            java.util.logging.Logger.getLogger(deleteRecipe.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            java.util.logging.Logger.getLogger(deleteRecipe.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            java.util.logging.Logger.getLogger(deleteRecipe.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (javax.swing.UnsupportedLookAndFeelException ex) {
            java.util.logging.Logger.getLogger(deleteRecipe.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        }
        //</editor-fold>

        /* Create and display the form */
        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                new deleteRecipe().setVisible(true);
            }
        });
    }

    // Variables declaration - do not modify                     
    private javax.swing.JButton btnBack;
    private javax.swing.JButton btnDelete;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JTextField txtName;
    // End of variables declaration                   

}

Answer 1

这里的问题是，您尝试使用 executeQuery 方法执行 INSERT SQL 语句。

当您想要更新数据库(创建、更新、删除)时，您必须调用executeUpdate。

如果您想从数据库中读取，可以使用executeQuery方法来实现。

另外，我不认为您的删除方法正在执行您想要的操作。它会向数据库插入一个新条目，而不是删除一个条目。