3

I set unique constraint for username using @Column(unique=true) what would be the best practice to check duplicate value before inserting the data to database?

  1. By catching the exception which will throw by the program when you try to insert data having same username.
  2. Check database if the provided username is already exists before trying to insert.
luksch
  • 11,497
  • 6
  • 38
  • 53
Dipesh
  • 305
  • 4
  • 13
  • Is the expectation that this could happen a lot? Or only very infrequently (even just in case of errors)? – Thilo Apr 30 '13 at 10:18
  • 3
    @ling.s if multiple clients will be trying to insert that might fail – ratchet freak Apr 30 '13 at 10:19
  • 2
    @ling.s: Second option will likely not be 100% safe, though, given concurrent database updates. – Thilo Apr 30 '13 at 10:19
  • 2
    @MarounMaroun I think that catching a unique index violation exception is reasonable in this case. You need to catch it anyway since approach 2 is not atomic, hence not safe when done from multiple clients. – Andreas Fester Apr 30 '13 at 10:20

3 Answers3

5

I think solution 1 (try-catch) is fine. For solution 2 you still need exception handling in case you have concurrency in your database.

luksch
  • 11,497
  • 6
  • 38
  • 53
  • 1
    +1. That argument makes a lot of sense. An extra check on the server does not buy you much, since you need the exception handling (messy as it may be in Java, maybe with JPA it's easier) anyway. But maybe an extra check on the web client (Ajax) can provide for an improved user experience. – Thilo Apr 30 '13 at 10:24
2

It depends on your design. If the check in question is not critical in performance or it is not costly, it does not really matter. An exception thrown from a DB abstraction layer could reduce noise in the code when compared to sometimes numerous checks (depending on issue at hand) that need to be done before we know that the operation can be done (and usually error handling is required anyways).

There is also an another aspect: is unique really what you want (read on)? If you're using MS SQL server, it does not support ANSI-nulls, that is a column defined as unique may contain only one null value. I suspect a username is never null, but this might not be the case on some other unique column - when on MS SQL Server, you need to apply uniquely filtered indexes (or such) or do the check beforehand in the application.

I vote for throwing an exception + what @eis suggests: checking of username availability in the UI.

heikkim
  • 2,955
  • 2
  • 24
  • 34
  • is there any relation between memory usage and exception thrown. Will my application becomes slow if I throw exception? – Dipesh Apr 30 '13 at 10:55
  • Generally throwing exceptions are not a bad idea and they do not hinder performance significantly unless misused. I'd suggest you read: http://stackoverflow.com/questions/299068/how-slow-are-java-exceptions – heikkim Apr 30 '13 at 11:05
0

Check for duplicates of the username immediately when such username data is entered in the UI, assuming such UI exists. With javascript/XHR, for example.

And recheck after submit, since javascript validations can be skipped and the situation could've changed.

If after all this there still exists a duplicate username (due to either checks failing or somebody inserting it concurrently), it sounds like an exceptional case to me, so catch an exception then.

So in short, both options, and the second check twice.

eis
  • 51,991
  • 13
  • 150
  • 199
  • 1
    This does not remove the need to check the username in the backend. – heikkim Apr 30 '13 at 10:21
  • If you need to have code to handle the exception, and you have the XHR client-side check, what is the point of the second server-side validation? – Thilo Apr 30 '13 at 10:25
  • @Thilo distinguishing the cases. If there is an exception even with pre-check, it is an exceptional case, as having duplicate usernames entered at the exact same time might be an indication of somebody trying to mess with your system. It is something you want to be aware about. In that case it should be logged separately. However, somebody having javascript off is no exceptional case and should not cause exceptions. – eis Apr 30 '13 at 10:28
  • Why not if you already have the code in place to handle the exception nicely? – Thilo Apr 30 '13 at 10:30
  • Also, you anyway have to write logic for catching exceptions on the database. You want that code to be present, but you don't want to enter that code in a normal use flow. – eis Apr 30 '13 at 10:31
  • @Thilo you don't want to handle the exception in the same way in case something is truly wrong on the database or somebody is trying to mess with your system - you want exceptional cases to be flagged as such. – eis Apr 30 '13 at 10:32