检查Java数据库中是否存在值(Accountnumber) [英] check if value (Accountnumber) exist in a java database

问题描述

我在名为Saving的类中具有以下方法，正在使用JDBC数据库保存和查看Java应用程序上的数据.

I have the following method in a class called savings, am using JDBC database to save and view data on java application.

我的数据库 ZaiLab 中有SAVINGS表，其中包含以下字段，

I have SAVINGS table in my database ZaiLab with the following fields,

ID,ACCOUNTNUMBER,CUSTOMERNAME,BALANCE,MINMUM)

，用户将使用JOPtionPane输入以下值.

and the following VALUEs will be entered by the user using JOPtionPane.

(id,accountNumber,customername,balance,minmum);

然后，应用程序应检查用户输入的 accountNumber 是否已存在，如果不存在，则应将记录保存到表SAVINGS中，如果是，则应显示相应的消息. 帐户已经存在".

the application should then check if accountNumber entered by the user already exist, if not it should save the record to a table SAVINGS, if yes it should display the appropriate message. "Account already exist".

public void openSavingsAccount(int Id, int Amount) {

    try {
        String host = "jdbc:derby://localhost:1527/ZaiLab";
        String uname = "siduduzo";
        String upass = "Password01";
        Connection con = DriverManager.getConnection(host, uname, upass);

        Statement stmt = con.createStatement();

        String SQL = "SELECT * FROM SAVINGS";
        ResultSet rs = stmt.executeQuery(SQL);

        int minmum = 1000;
        balance = minmum;

        while (rs.next()) {
            int acc_col = rs.getInt("ACCOUNTNUMBER");
            if (acc_col == accountNumber) {
                JOptionPane.showMessageDialog(null, "Sorry, account " + accountNumber
                        + " aready Exist");
            } else if (Amount < minmum) {
                JOptionPane.showMessageDialog(null, "Can not Open the Account, Minimum amount to deposit must be R1000");
            } else {

                balance = balance + Amount;
                id = Id;
                stmt.executeUpdate("INSERT INTO `SAVINGS`(ID,ACCOUNTNUMBER,CUSTOMERNAME,BALANCE,MINMUM)VALUE ('" + id + "','" + accountNumber + "','" + customername + "'," + balance + ",'" + minmum + "')");

            }

        }

    } catch (SQLException err) {
        System.out.println(err.getMessage());
    }

}

推荐答案

现在，您要从SAVINGS中选择所有行，并尝试为每个没有新"帐号的行插入一个新帐户.

Right now you are selecting all rows from SAVINGS and attempt to insert a new account for each row that doesn't have the 'new' account number.

相反，您应该仅选择具有新帐号的行，并在不存在该行的情况下插入.

Instead, you should select only the row with the new account number, and insert when it doesn't exist.

您还应该使用准备好的语句来防止SQL注入.

You should also use prepared statements to protect you against SQL injection.

例如:

try (PreparedStatement checkAccountExists = con.prepareStatement(
        "SELECT 1 FROM SAVINGS WHERE ACCOUNTNUMBER = ?")) {
    checkAccountExists.setInt(1, accountNumber);

    try (ResultSet rs = checkAccountExists.executeQuery()) {
        if (rs.next()) {
            // handle account already exists
        } else {
            try (PreparedStatement insert = con.prepareStatement(
                    "INSERT INTO SAVINGS(ID, ACCOUNTNUMBER, CUSTOMERNAME, BALANCE, MINMUM) VALUES (?, ?, ?, ? , ?)")) {
                insert.setInt(1, id);
                insert.setInt(2, accountNumber);
                insert.setString(3, customername);
                insert.setInt(4, balance);
                insert.setInt(5, minmum);

                insert.executeUpdate();
            }
        }
    }
}

或者，您可以在数据库中的ACCOUNTNUMBER上定义唯一约束，并且只要记录已经存在，就可以进行插入并处理违反约束的情况.

Alternatively, you could define a unique constraint on ACCOUNTNUMBER in your database and just do the insert and handle the constraint violation if the record already exists.

这篇关于检查Java数据库中是否存在值(Accountnumber)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！