how to check for duplicate entries in database?

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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;
        }
    }

Answer 4

you may: - make the email field unique, try to insert adn catch the exception or - make a select before each insert

Answer 5

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.