CODEWITHSUNDEEP
javamysqlsqlswing
user3763216
user3763216

Reputation: 489

check for duplicate data before insert

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;

}

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

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!

Upvotes: 0

Views: 6822

Answers (3)

Joop Eggen
Joop Eggen

Reputation: 109613

There is a choice for

  • INSERT IGNORE ("IF NOT EXISTS")
  • INSERT ON DUPLICATE KEY UPDATE

In the CREATE TABLE there must be a constraint, unique key on the unique fields. Otherwise ALTER TABLE.

I leave it to you to make a choice and inspect the mysql reference documentation.

I assume reqId is an AUTOINCREMENT primary key.

In the INSERT leave it out, to be generated by the database, in order to not have concurrency problems, of two people at the same time getting a new reqId.

Furthermore use PreparedStatement; this is really almost obligatory, as it escapes apostrophe and backslashes; prevents SQL injection, looks better.

try (PreparedStatement stm = conn.prepareStatement(
        "INSERT IGNORE INTO assignrequests (dutyName, volNric) VALUES(?, ?)")) {
    stm.setString(1, dutyName);
    stm.setString(2, volNric);
    int updateCount = stm.executeUpdate();
    if (updateCount != 0) {
        try (ResultSet genKeys = stm.getGeneratedKeys()) {
            if (genKeys.next()) { // At most 1 record inserted
                // Normally only one key generated per record.
                int reqId = genKeys.getInt(0);
                ...
            }
        } // Close result set.
    }
} // Closes stm

Upvotes: 0

Hardik Parmar
Hardik Parmar

Reputation: 1051

I am completely agree with the @hd1 answer you need to apply this condition for the query after creating the unique constraint.

You need to create unique constraint first to any of your column that you need as a unique column.

EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        DBMS_OUTPUT.PUT_LINE('This is duplicate Value ')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270893

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.

Upvotes: 2

Related Questions