Thread: Is a right behaviour on inserting a duplicate key?

I have the following code segment to insert a sign on data which the userid is the primary key of the table:

        PreparedStatement ps = null;
        try {
          getDBConnection();
          dbConnection.setAutoCommit(false); // The first step of new user sign up
          ps = dbConnection.prepareStatement(CREATE_USER_QUERY);
          ps.setString(1, userName.trim());
          ps.setString(2, password.trim());
          int result = ps.executeUpdate();
          if(result != 1) {
                logger.error("Unable to create user. Duplicate Key: " + userName);
              throw new SignOnDAODupKeyException("Unable to create user. " +
                                "Duplicate Key : " + userName);
          }
        } catch (SQLException se) {
               logger.error("SQLException: "+se.getMessage());
          throw new DAOSysException("SQLException: "+se.getMessage());
        } finally {
          closeAll(ps);
        }

It seems to me that inserting a duplicate user would result an invalide retured value. Instead, the action leads to a
SQL 
expection as

ERROR:  Cannot insert a duplicate key into unique index pk_signon

Can some clarify the 7.3.1 JDBC driver specification or implement in this regards?

Thanks,

Vernon

> It seems to me that inserting a duplicate user would result an invalide
retured value. Instead, the action leads to a SQL
> expection as
>
> ERROR:  Cannot insert a duplicate key into unique index pk_signon
>
> Can some clarify the 7.3.1 JDBC driver specification or implement in this
regards?

I think throwing an exception is the correct thing to do and is consistent
with Oracle.  The only downside is that detecting a duplicate key, something
that seems so ordinary in the database world that one has to wonder why a
special exception was not devised by JDBC to ensure a portable way to detect
it.  We use some hacked code like the following to do so in our connection
pool code (obviously, only the public method is invoked by our mainline
code):

    protected boolean isPostgresqlDuplicateKey(SQLException e)
    {
        String msg = e.getMessage();
        if ( msg == null )
            return false;
        return msg.indexOf("duplicate key") > 0;
    }

    protected boolean isOracleDuplicateKey(SQLException e)
    {
        return e.getErrorCode() == 1;
    }

    public boolean isDuplicateKey(SQLException e)
    {
        if ( e == null )
            return false;
        if ( isOracle() )
            return isOracleDuplicateKey(e);
        return isPostgresqlDuplicateKey(e);
    }

The behavior is not consist across all DBs' JDBC. The reason I posted the question is that I saw some sample code
from Java.Sun site using the result value to detect whether inserting a duplicate key or not.

I guess that I shall a select statement before a insertion one to find out whether the key exists or not.

Thanks David for sharing your strategy.

Vernon


09/02/2003 12:19:20 PM, "David Wall" <d.wall@computer.org> wrote:

>> It seems to me that inserting a duplicate user would result an invalide
>retured value. Instead, the action leads to a SQL
>> expection as
>>
>> ERROR:  Cannot insert a duplicate key into unique index pk_signon
>>
>> Can some clarify the 7.3.1 JDBC driver specification or implement in this
>regards?
>
>I think throwing an exception is the correct thing to do and is consistent
>with Oracle.  The only downside is that detecting a duplicate key, something
>that seems so ordinary in the database world that one has to wonder why a
>special exception was not devised by JDBC to ensure a portable way to detect
>it.  We use some hacked code like the following to do so in our connection
>pool code (obviously, only the public method is invoked by our mainline
>code):
>
>    protected boolean isPostgresqlDuplicateKey(SQLException e)
>    {
>        String msg = e.getMessage();
>        if ( msg == null )
>            return false;
>        return msg.indexOf("duplicate key") > 0;
>    }
>
>    protected boolean isOracleDuplicateKey(SQLException e)
>    {
>        return e.getErrorCode() == 1;
>    }
>
>    public boolean isDuplicateKey(SQLException e)
>    {
>        if ( e == null )
>            return false;
>        if ( isOracle() )
>            return isOracleDuplicateKey(e);
>        return isPostgresqlDuplicateKey(e);
>    }
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>