Delete row from database

问题:

I have a database table with the following layout:

 Columns:
 _________________________
 id | user_name | password

But I can't delete a specified row by using the username. I am receiving the following error:

MySQLSyntaxErrorException: Unknown column 'vipin' in 'where clause'

vipin is a value within the table. Can anyone help me?

public void deleteFclty() {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    String username = removeText.getText();
    ArrayList<String> values = new ArrayList();

    String qry = "SELECT user_name From users ";
    try {
        stmt = (PreparedStatement) connection.prepareStatement(qry);
        rs = stmt.executeQuery();

        while (rs.next()) {
            values.add(0, rs.getString(("user_name")));
            System.out.println(values);
        }
    } catch (SQLException ex) {
        Logger.getLogger(RemoveFaculty.class.getName()).log(Level.SEVERE, null, ex);
    }

    if (values.contains(username)) {
        username = removeText.getText();
        Boolean isAdmin = false;

        try {
            System.out.println(username);
            preparedStatement = (PreparedStatement) connection.prepareStatement("DELETE FROM users WHERE user_name=" + username + "");
            preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(RemoveFaculty.class.getName()).log(Level.SEVERE, null, ex);
        }
    } else {
        Util.showErrorMessageDialog(username + " is not found.Please try again.");
    }
}

回答1:

Since you're already using PreparedStatement, use it right and pass the username as parameter instead of just concatenating the Strings:

//no need to use a cast here
preparedStatement = connection.prepareStatement(
    //note the usage of ? instead of concatenating Strings
    "DELETE FROM users WHERE user_name=?");
//setting the first parameter in the query string to be username
preparedStatement.setString(1, username);
preparedStatement.executeUpdate();

Using this, you won't have any concatenation problems and what's better, your code won't be prone to SQL Injection.

---

Not directly related to your problem, but it would be better if you move the code to execute INSERT, UPDATE and DELETE statements to a single method.

public void executeUpdate(Connection con, String query, Object ... params)
    throws SQLException {
    PreparedStatement pstmt = con.prepareStatement(query);
    if (params != null) {
        int i = 1;
        for(Object param : params) {
            pstmt.setObject(i++, param);
        }
    }
    pstmt.executeUpdate();
    pstmt.close();
}

So your code would be dramatically reduced to:

String deleteSQL = "DELETE FROM users WHERE user_name=?";
executeUpdate(deleteSQL, username);

Note that you can create a new method based on this approach to execute SELECT statements.

Also, don't forget to close your resources. This also can be dramatically reduced using a method like this:

public void closeResource(AutoCloseable res) {
    try {
        if (res != null) {
            res.close();
        }
    } catch (Exception e) {
        //handle this exception...
        //basic example, not meant to be used in production!
        e.printStacktrace(System.out);
    }
}

Note that Connection, Statement (and its children PreparedStatement and CallableStatement) and ResultSet interfaces already extend from AutoCloseable.

回答2:

You haven't quoted the username you're inserting into the query, so it's being treated as a reference to a field name:

DELETE FROM users WHERE user_name='"+username+"'"
                                  ^--          ^--

Note: building queries like this leaves you open to SQL injection attacks. Used prepared statements and placeholders instead.

回答3:

I think you might need some quotes round the username in the where clause

connection.prepareStatement("DELETE FROM users WHERE user_name='"+username+"'");

回答4:

You are going to want to quote your Strings

"DELETE FROM users WHERE user_name="+username+"";

Like this:

"DELETE FROM users WHERE user_name='" + username + "'";

What would be better is just using PreparedStatement as it was intended:

"DELETE FROM users WHERE user_name = ?";

And then using:

preparedStatement.setString(1, username);

before calling executeUpdate

回答5:

The query should look like this

preparedStatement = (PreparedStatement) connection.prepareStatement("DELETE FROM users WHERE user_name='"+username+"'");

Note : Mind the single quotes used for user_name='"+username+"'"