插入前检查重复数据 [英] check for duplicate data before insert

问题描述

我正在创建一个java swing程序，用户可以在其中创建记录，但我希望能够在允许用户创建记录之前检查重复项。这是我的代码：

I'm creating a java swing program where users can create a record, but I want to be able to check for duplicates before allowing the users to create the record. Here's my code:

public boolean createAssignRequest(AssignmentRequests assignReq) {

    int id = assignReq.getReqId();
    String dutyName = assignReq.getDutyName();
    String volNric = assignReq.getVolNric();

    boolean success = false;
    DBController db = new DBController();
    String dbQuery = "";

    db.getConnection();

    dbQuery = "INSERT into assignrequests (dutyName, volNric)"
            + " VALUES ('" + dutyName + "','" + volNric + "')";

    if (db.updateRequest(dbQuery) == 1) {
        success = true;
    }
    db.terminate();

    return success;

}

如果一行具有相同的行，则用户无法创建记录dutyName和volNric已经存在。
如果我要执行这行sql语句

whereby users cannot create the record if a row with the same dutyName and volNric already exists. If I were to execute this line of sql statement

ALTER TABLE `assignrequests` ADD UNIQUE `uniqueindex`(`dutyName`, `volNric`);

我会在mySQL中实现它（即使我截断我的数据也只有一次）或在我的程序中代码？ （我正在使用eclipse）
我也使用三层架构:-)
最后，如果已经检测到重复记录，我将如何实现显示JOptionPane的代码？谢谢你的帮助！

would I implement it in mySQL (just one time even if i truncate my data) or in my program codes? (I'm using eclipse) I'm also using a three-tier architecture :-) Finally, how would I implement the code to show a JOptionPane if a duplicate record is already detected? Thanks for your help!

推荐答案

如果你想要 DutyName 和 volNric 具有唯一值，然后使用唯一约束/索引执行此操作：

If you want DutyName and volNric to have unique values, then do so with a unique constraint/index:

create index idx_assignrequests_dutyname_volnric on assignrequests(dutyname, volnric);

然后，当你执行插入时，你可以让它失败。或者，你可以在重复密钥更新时使用忽略它：

Then, when you do the insert, you can let it fail. Or, you can just ignore it using on duplicate key update:

INSERT into assignrequests(reqId, dutyName, volNric)"
    VALUES ('" + id + "','" + dutyName + "','" + volNric + "')
    ON DUPLICATE KEY UPDATE dutyName = VALUES(dutyName);

正在更新的列被设置为自身 - 因此操作不会什么都不做。

The column being updated is being set to itself -- so the operation doesn't do anything.

这篇关于插入前检查重复数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！