Validation: check for uniqueness before saving

Question

I am developing a web app with Play Framework. My app has a User class, which in turn has a username and an email property. Pretty standard stuff. However...

When a new user signs up via a form (signup,html), I want to check if the username and password are unique and if not, redirect them back to the form and display a message there.

I have already thought of some solutions, none of which are particularly graceful, It seems to me there must be an easy way of checking whether a username/email is unique in the database without putting constraints on the table itself (which leads to runtime errors when a duplicate value is inserted).

So... anyone ran into this same problem? And how did you fix it?

Answer 1

I did something similar in a scheduling system to prevent overlapping shifts for the same person. In the model it uses a @PrePersist method to query the DB and throws a custom exception if it finds anything that would conflict.

Then in the controller I catch the exception (which will be thrown when saving the object) and redirect back to the form.

In the model:

@PrePersist
public void prepareToInsert() {
    List<AgentShift> conflicts = find("agent=?1 and (start_time between ?2 and ?3 or end_time between ?2 and ?3)",
        agent,
        sql_datetime_formatter.print(scheduled.getStart()),
        sql_datetime_formatter.print(scheduled.getEnd())).fetch();
    if (!conflicts.isEmpty()) {
        throw new SchedulingException("New shift for " + agent +
            " overlaps existing shift which goes from " +
            sql_datetime_formatter.print(conflicts.get(0).scheduled.getStart()) + " to " +
            sql_datetime_formatter.print(conflicts.get(0).scheduled.getEnd()), conflicts);
    }

}

In the controller:

public static void create(@Valid AgentShift object) {
    ...
    try {
        object._save();
        ...
    }
    catch (SchedulingException e) {
        flash.error("conflict with existing shift", e.getConflicts());
        redirect(request.controller + ".blank", object._key());
    }
}

Answer 2

You have to use either a database constraint - and handle the exception appropriately, or use locking (which may sacrifice performance). If you try to do a last minute check, you'll never know if another request snuck in the user name between the check and your commit.

While not common, it can happen (imagine the network is slow and a user submits many times). IMHO, you're best off just dealing with the issue correctly instead of hoping the unlikely never occurs.

I can elaborate with code, if you like. Although it sounds like you might already know how to do it (but were just looking for a more elegant solution).