Spring DataIntegrityViolationException

  • PrettyHandling

    PrettyHandling - 2004-07-29

    Hi guys,

       I got username field as Primary key and email field as UNIQUE in MySQL DB. When user enter a duplicate username or email,

    will be called. But if user entered non-duplicate username but a duplicate email address, how can I catch if it's the duplicate username or email?

    catch (DataIntegrityViolationException de) {
    String msg = "duplicate user id";
    this.logger.error(msg, de);
    throw new DuplicateUserIdException(msg);

    Appreciate any advice, Thanks !

    • jason poley

      jason poley - 2004-07-30

      just create simple queries to check for dupes before you try to insert.  thats pretty simple.  that way you only hit the exception if you get 2 submitted that are equal, between check points and insert point.

    • Keller

      Keller - 2004-07-30

      I personally don't think it is good practice to use data integrity violations to drive business rules.  I agree with Jason in that you should probably have separate methods for these.

      The reason I feel this is incorrect is because your business rules should be represented by the implement ion of interfaces and not driven by the underlying technology.  Let's use your situation as an example.

      Your rules are:
      - IF (username doesn't exist) AND  (email doesn't exist) THEN (create user)

      So now you're DAO interface will be:

      public boolean userNameExists(User user);
      public boolean emailExists(User user);
      public void create(User user);

      Notice all the methods coincide with your business rules.

      At this point, you can create a mock object that implements that interface as well a unit test.  This will allow you to verify how your UI will react before writing any database code.

      Your service/manager bean will have this method or something similar:

        public void create(User user);

      People will have different opinions on how you should handle the "user name exists" and "email exists" situations but I would suggest creating a ServiceException and deriving a UserNameExistsException and EmailAddressExsitsException.  I prefer unchecked exception which again, some people may have different opinions about.

      You can then implement the create() method in your service/manager object.

      public void create(User user) {
        try {
          if (daoUser.userNameExists(user)) {
             throw new UserNameException();

          if (daoUser.emailAddressExists(user)) {
             throw new EmailAddressException();

        } catch (DataAccessException dae) {
           ... log, etc.
           throw new ServiceException("Your message here", dae);
        catch ... whatever else you need to

      You can now write a unit test for this object.  If you're using programmatic transaction management, you would place code around these methods so that they execute inside the same transaction.  A better way would be to use Spring's support declarative transaction management so that you can specify transaction properties for each method.  Spring makes it very easy to do either.

      In your client code (i.e. Struts action) you can now simply call the service method:

      try {

      } catch (UserNameException e) {
          ... log, retrieve get localized message, etc.

      } catch (EmailAddressException) {
          ... log, retrieve get localized message, etc.

      } catch (ServiceExcetpion e) {
          // Database error, etc.

      The advantage here is that if the business rules change, you can update your code very quickly and easily.  For example, let's say a new requirement that stated only users that specified that their favorite animal was a Jackalope.  You then add the code to your create() service method:
        if(!user.getFavoriteAnimal().equals("Jackalope")) {
          throw new FavoriteAnimalException("Your message here (i.e. Fav animal must be a Jackalope in order to register)");

      Create a new method in your unit test and verify and catch the exception in your client code so you can display the error to the user. This not a very realistic example but you get the idea.  This is the basic architecture that I use (I mainly develop web apps) and I have been very successful thus far and have been able to develop high quality applications in very short periods of time.  It allows you to adhere to a test driven approach and allows you to react to requirement changes in a very efficient manner.   

    • PrettyHandling

      PrettyHandling - 2004-08-03

      Hi keller,

         Really Thanks a lot for your advice.


    • Anonymous - 2004-08-04

      A consequence of the suggestion made is that when a user is added, the overhead of additional queries is incurred. In the approach recommended above, 2 additional select queries are run : one for checking if user name already exists and the other to check for duplicate email address. And this is primarily done only to display a meaningful error message to the end user. For example 'user name <user_id> already exists' or 'email address <email_id> already exists'. If it were not simply to get a meaningful error message, the above logic could be changed to check for existence of either duplicate user id or email address in a single select query. This will improve the efficiency of adding users by reducing the number of queries from 3 to 2 but now we lose information as to whether a duplicate user id or email address already existed and we'd only be able to display an error message such as 'user name or email address already exists'. This in most cases will not be acceptable as the end user needs to know whether the user id or email address is already taken.

      The approach in the original post has its advantages as by programming by exception, we do not incur the overhead of additional queries when adding a new entities. It would be nice if the DataIntegrityViolationException exception captured information in a database independent manner the field that caused the violation so that an error message can be returned to the user with details on the field that caused violation. However this does not seem to be the case with the DataIntegrityViolationException exception raised.

      In certain circumstances (like yours) where it is required to give the end user a detailed error message, doing an explicit check for duplicates makes sense.

      However in some high volume transactional systems where performance is prime importance and the back end engine has exception handling logic that does not require to know exactly which field caused the error, then the overhead of additional select queries may be unacceptable and programming by exception may be more appropriate.


Log in to post a comment.