How to run mysql insert without duplicates in java

Question

I have a method which insert a record to mysql database as bellow,

public boolean addOrganization(OrganizationDTO organizationDTO) {
    Connection con = null;
    try {
        String insertOrganizationSQL = "INSERT INTO organizations (org_id, org_name) VALUES(?, ?)";
        con = JDBCConnectionPool.getInstance().checkOut();
        PreparedStatement insertOrgPS = (PreparedStatement) con.prepareStatement(insertOrganizationSQL);
        insertOrgPS.setString(1, organizationDTO.getOrg_id());
        insertOrgPS.execute();
        return true;
    } catch (Exception e) {
        JDBCConnectionPool.getInstance().checkIn(con);
        logger.error(e.getLocalizedMessage());
        e.printStackTrace();
        return false;
    } finally {
        JDBCConnectionPool.getInstance().checkIn(con);
    }
}

database table,

CREATE TABLE `organizations` (
  `org_id` varchar(5) NOT NULL,
  `org_name` varchar(100) DEFAULT NULL,
  `sys_dat_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user` varchar(100) NOT NULL,
  PRIMARY KEY (`org_id`)
)

what I need is, when I insert a new record if, that is a duplicate exit the method without trying to insert and inform the user that it is a duplicate record. Is it possible to do without writing another method to search the record before inserting?

You can do an INSERT IGNORE and then check how many rows were affectd. If it's zero, it was a duplicate. — Eli, May 02 '12 at 03:28
I found a solution for my problem from [this][1] qestion [1]: http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update — Harsha, May 02 '12 at 03:51

score 0 · Answer 1 · answered May 02 '12 at 03:37

I would add a UNIQUE constraint to your table. For example, if org_name needs to be unique:

ALTER TABLE organizations ADD UNIQUE (org_name);

Then observe what's returned when you try to insert a duplicate record through Java. Add code to check for this, and if it occurs, display the message to your user.

Here is the reference documentation for ALTER TABLE.

score 0 · Answer 2 · answered May 02 '12 at 06:02

0

Thats right, Alter table will surely help.

In your case, let say, both org_id and org_name is there, I would add unique in both, just avoid any confusion later.

answered May 02 '12 at 06:02

Yogesh A Sakurikar

1,731
1
11
13

How to run mysql insert without duplicates in java

2 Answers2