从数据库中删除行 [英] 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:"where子句"中的未知列"vipin"

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

vipin是表中的值. 谁能帮我吗?

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.");
    }
}

推荐答案

由于您已经在使用PreparedStatement，因此请正确使用并传递username作为参数，而不仅仅是连接字符串:

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();

使用此方法，您将不会有任何串联问题，而且更好的是，您的代码也不会容易 SQL注入.

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

与您的问题没有直接关系，但是将代码移动到一个单一的方法来执行INSERT，UPDATE和DELETE语句会更好.

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);

请注意，您可以基于此方法创建一个新方法来执行SELECT语句.

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);
    }
}

请注意，Connection，Statement(及其子级PreparedStatement和CallableStatement)和ResultSet接口已经从AutoCloseable扩展.

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

这篇关于从数据库中删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！