java - 如何使 SQL 删除方法正确验证数据

Question

我有一个方法，可以在插入标签值时删除数据库中的记录。当删除一条记录时，控制台屏幕上会弹出一条消息:“该记录已被删除”。插入有效标签值时它工作正常。但是，当我插入数据库中不存在的无效标记值时，它的行为就像已删除它并显示上一条消息。虽然在我的方法中说如果结果不等于 1(这不是 true)则返回 false，但它显然没有验证插入的数据。谁能告诉我出了什么问题

public boolean DeleteWallet(String Tag) throws SQLException {
    System.out.println("Deleting wallet");
    Connection dbConnection = null;
    Statement statement = null;
    int result = 0;
    String query = "DELETE FROM wallets WHERE Tag = '" + Tag + "';";
    try {
        dbConnection = getDBConnection();
        statement = dbConnection.createStatement();
        System.out.println("The record has been deleted successfully");
        // execute SQL query
        result = statement.executeUpdate(query);
    } finally {
        if (statement != null) {
            statement.close();
        }
        if (dbConnection != null) {
            dbConnection.close();
        }
    }
    if (result == 1) {
        return true;
    } else {
        return false;
    }
}

Answer 1

声明System.out.println("该记录已删除成功");

在实际执行任何数据库操作之前打印statement.executeUpdate(query);

相反，您应该在 try 语句中执行数据库操作，然后打印成功输出。如果语句失败(即抛出异常)，则将跳过成功语句。

此外，我不会依赖 executeUpdate(query) 的输出来确定查询是否成功，而是始终假设您的查询或查询失败之前的某些操作，并且只返回 true如果所有数据库处理都成功。

最后，使用准备好的语句将有助于使您的查询更易于阅读和使用，并且可以更好地抵御 SQLInjection 攻击。

示例:

public class DatabaseOperations {
    public boolean DeleteWallet(String Tag) {
        //Query used for prepared statement
        static final String DELETE_QUERY = "DELETE FROM wallets WHERE Tag=?"; 
        System.out.println("Attempting to delete wallet using query:" + DELETE_QUERY);
        //assume DELETE operation fails due to exection at any stage
        Boolean result = false; 

        try (
            //Objects that can automatically be closed at the end of the TRY block
            //This is known as AutoCloseable
            Connection dbConnection = getDBConnection();
            PreparedStatement statment = dbConnection.preparedStatement(DELETE_QUERY)) 
        {
            //replace ? with Tag
            statement.setString(1, Tag);
            int row = preparedStatement.executeUpdate();
            //If statement fails skip to catch block

            result = true;
            System.out.println("The record in row " + row + " has been deleted successfully");
        } catch (SQLException sqle) {
            //likely thrown due to "Record Not Found"
            //TODO investigate further for the specific exception thrown from the database implementation you are using.
            //TODO print helpful message to help user of this method resolve this issue
        } catch (Exception) {
            //TODO handle any other exceptions that may happen
        }

        return result;
    }
}