[jira] [Created] (DERBY-6779) Provide subclass of SQLException for duplicate key insertions

["John English (JIRA)" <ji...@apache.org>]

John English created DERBY-6779:
-----------------------------------

             Summary: Provide subclass of SQLException for duplicate key insertions
                 Key: DERBY-6779
                 URL: https://issues.apache.org/jira/browse/DERBY-6779
             Project: Derby
          Issue Type: Improvement
          Components: SQL
            Reporter: John English
            Priority: Minor


A commonly-occurring situation is to take some special action when an insert fails due to a duplicate key; for example, to update the existing row or to retry the insert with a new key (e.g. mutate "Name" into "Name(1)", "Name(2)" etc. until a unique key is found). At present this requires code similar to:
{noformat}
    try {
       //... insert new row
    }
    catch (SQLException e) {
        if (e.getSQLState().equals(DUPLICATE_KEY)) {
            // ... take recovery action
        }
        else {
            throw e;
        }
    }
{noformat}
It would be more convenient if a subclass of SQLException were used to report this precise error. The SQLIntegrityConstraintViolationException that is currently thrown will also be thrown in other case where a constraint is violated. A new exception subclass for this specific situation would not affect any existing code, and would allow the code above to be simplified to this:
{noformat}
    try {
        //... insert new row
    }
    catch DuplicateKeyException e) {
        // ... take recovery action
    }
{noformat}
This would allow a more elegant, more O-O solution to what is, in my experience, a common use case without having to discriminate based on the value of getSQLState().



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)