CODEWITHSUNDEEP
javadatabasejdbc
bhavna raghuvanshi
bhavna raghuvanshi

Reputation: 1075

how to check for duplicate entries in database?

I need to apply a check so that a user cannot register using an email id which already exists in the database.

Upvotes: 0

Views: 7392

Answers (5)

Sergey Eremin
Sergey Eremin

Reputation: 11076

You may:

  • make the email field unique, try to insert and catch the exception

or

  • make a select before each insert

Upvotes: 0

BalusC
BalusC

Reputation: 1109570

There are indeed basically two ways to achieve this:

  1. Test if record exists before inserting, inside the same transaction. The ResultSet#next() of the SELECT should return false. Then do INSERT.

  2. Just do INSERT anyway and determine if SQLException#getSQLState() of any catched SQLException starts with 23 which is a constraint violation as per the SQL specification. It can namely be caused by more factors than "just" a constraint violation. You should namely not handle every SQLException as a constraint violation.

    public static boolean isConstraintViolation(SQLException e) {
    return e.getSQLState().startsWith("23");
}

I would opt for the first way as it is semantically more correct. It is in fact not an exceptional circumstance. You namely know that it is potentially going to happen. But it may potentially fail in heavy concurrent environment where transactions are not synchronized (either unawarely or to optimize performance). You may then want to determine the exception instead.

That said, you normally don't want to put a PK on an email field. They are namely subject to changes. Rather use a DB-managed autogenerated PK (MySQL: BIGINT UNSIGNED AUTO_INCREMENT, Oracle/PostgreSQL: SERIAL, SQLServer: IDENTITY) and give the email field an UNIQUE key.

Upvotes: 3

ant
ant

Reputation: 22948

Probably something like this DAO method :

public boolean isDuplicateEntry(String email) {
        Session session = getSession();
        try {
            User user = (User) session.get(User.class, email);
            session.close();

            return (null != user);

        } catch (RuntimeException e) {
            log.error("get failed", e);
            session.close();
            throw e;
        }
    }

Upvotes: 1

Jason
Jason

Reputation: 847

Put a constraint on the email column, or select before insert.

Upvotes: 10

Jesper
Jesper

Reputation: 206996

Put a unique constraint on the relevant column in the database table. For example (MySQL):

ALTER TABLE Users ADD UNIQUE (Email)

edit - If the e-mail field is already a primary key as you write in a comment above, then you don't need this, because primary keys are by definition unique. Then in Java you could catch the SQLException that you get if you'd insert a record with a primary key that already exists, or you can do a SELECT ... WHERE Email=? before you try the insert to see if there is already a record with that e-mail address.

Upvotes: 1

Related Questions