使用executeUpdate()更新数据库报错：权限不足或对象未找到

Hey there, let's figure out why you're hitting this confusing error and fix it up!

That "user lacks privilege or object not found" message is a red herring here—your SQL syntax is broken, and the database is misinterpreting your input as a database object (like a column name) instead of a value. Here's the breakdown:

1. String Values Aren't Wrapped in Single Quotes #

When inserting string data into a database, you must wrap the values in single quotes ('). Right now, if a user enters Aspirin as the note, your generated SQL looks like this:

INSERT INTO Pharmacy (drug_name) VALUES (Aspirin)

The database sees Aspirin and thinks it's a column name (an object) to look up, not a value. Since that column doesn't exist, it throws the "object not found" error.

2. You're Wide Open to SQL Injection #

Directly concatenating user input into your SQL statement isn't just a syntax issue—it's a huge security risk. A malicious user could enter something like '); DROP TABLE Pharmacy; -- and wipe out your entire table. Yikes.

(Note: If fixing the SQL syntax doesn't resolve the error, then you can check if your database user has INSERT privileges, but that's unlikely to be the main issue here.)

The right way to handle this is with PreparedStatement. It automatically handles string quoting and eliminates SQL injection risks. Here's how to rewrite your code:

add.addActionListener(new ActionListener() {
    @Override 
    public void actionPerformed(ActionEvent e) {
        // Use ? as placeholders for dynamic values
        String strInsert = "INSERT INTO Pharmacy (?) VALUES (?)";
        // Try-with-resources ensures the statement gets closed automatically
        try (PreparedStatement pstmt = conn.prepareStatement(strInsert)) {
            // Set values for the placeholders (positions start at 1)
            pstmt.setString(1, txtField.getText());
            pstmt.setString(2, txtStr.getText());
            // Execute the insert
            pstmt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(NewDataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
});

Quick Side Note #

If you're letting users input column names (via txtField), that's risky too—users might enter invalid or malicious column names. Consider replacing the text field with a dropdown menu of valid column names for your Pharmacy table instead.

• Don't run database operations directly in the UI listener thread—this will freeze your app while the database works. Move the DB code to a background thread (like using SwingWorker).
• Use JFrame.DISPOSE_ON_CLOSE instead of EXIT_ON_CLOSE for your popup window—right now, closing the popup will shut down your entire application, which is probably not what you want.

内容的提问来源于stack exchange，提问作者Olya Zhulanova