时间戳

首页 > 解决方案 > 如何使 SQL 删除方法正确验证数据

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

问题描述

我有一种方法可以在插入标记值时删除数据库中的记录。当一条记录被删除时，控制台屏幕中会弹出一条消息说“这条记录已被删除”。插入有效的标签值时它工作正常。但是，当我插入一个在我的数据库中不存在的无效标签值时，它就像它已将其删除并显示之前的消息一样。虽然在我的方法中说如果结果不等于 1（这不是真的）返回 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;
    }
}

标签： javasqldatabasemethods

解决方案

该声明 System.out.println("The record has been deleted successfully");

在您实际执行任何数据库操作之前打印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;
    }
}

推荐阅读