Exception Handling using while loop

Question

i am using postgreSql and Java , here is the error shown in eclipse console

org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "km_rel_user_test_details_pkey",

Here the cause of the error is(i guess ) due to duplicate key present in database , I have written a while loop to handle this exception, i just want to know, whether this approach is correct and will work for every situation, any error found/ ask for improvement will be appreciated, thanks in advance, here is my java method.

 public String saveConceptTestDetails(String testId,String userId,String selected_ans, String check_user_save, String conceptTestOrder , int ans) {

    boolean isInserted=false;
    String newId1=null;
    while(!isInserted){
        try{
            newId1=getNextVal(DBKeys.SEQ_REL_USER_TEST_DETAILS); // this is what generatres new Id
            Hashtable resultHash=executeUpdate(new StringBuffer(" insert into "+DBKeys.TABLENAME_REL_USER_TEST_DETAILS+" ("+DBKeys.COLUMNNAME_REL_USER_TEST_DETAILS_ID+","+DBKeys.COLUMNNAME_REL_USER_TEST_DETAILS_TESTID+","+DBKeys.COLUMNNAME_REL_USER_TEST_DETAILS_USERID+","+DBKeys.COLUMNNAME_REL_USER_TEST_DETAILS_CREATEDDT+","+DBKeys.COLUMNNAME_REL_USER_TEST_DETAILS_MODIFIEDDT+","+DBKeys.COLUMNNAME_REL_USER_TEST_DETAILS_SELECTED_ANS+","+DBKeys.COLUMNNAME_REL_USER_TEST_DETAILS_CHECK_USER_SAVE_TEST+","+DBKeys.COLUMNNAME_REL_USER_TEST_DETAILS_TEST_TYPEORDER+","+DBKeys.COLUMNNAME_REL_USER_TEST_DETAILS_TEST_VERSION+","+DBKeys.COLUMNNAME_REL_USER_TEST_DETAILS_SAVEANS+")values ("+newId1+","+testId+","+userId+",current_date,current_date,"+selected_ans+","+check_user_save+","+conceptTestOrder+",0,"+ans+")"));
            isInserted=true;
        }
        catch (Exception e) {
            System.out.println("Exception");
            isInserted=false;
        }
    }
    return newId1;
  }

The correct approach is to check why getNextVal is returning duplicate key values. — M A
– M A, Commented Sep 17, 2014 at 14:16
i googled but the code and all for the sequence generation is correct, donno y its not working . — Faizy
– Faizy, Commented Sep 17, 2014 at 14:19
its a sequence generator, which generates the next id, same like autoincrement which we have in mysql. — Faizy
– Faizy, Commented Sep 18, 2014 at 6:09

Nathan Hughes · Accepted Answer · 2014-09-18 15:06:03Z

2

The reason for using a generator is to avoid this problem, it's supposed to make sure you get only unused ids. If you can't rely on that here, you could add a workaround:

You could query first to find the maximum id in the table. If the generator allows setting a nextvalue then change it so it starts from there, otherwise call the generator over and over until you get a key higher than that maximum.
You could write a query that gets the existing row numbers and have insert code check that the id is not in the list (assumes this is the only thing doing inserts).
You could execute a select count before each insert to make sure the id is unused.

Reusing IDs used by previously-deleted rows, just so you can fill in the gaps, has the potential to cause confusion and should be avoided. It seems like a lot of trouble to go to with not much benefit. If you want sequential numbers then add a specific field for that.

Your exception-handling is not distinguishing between different kinds of SQLExceptions. Not all your SQLExceptions will be from constraint violations; if you have something go wrong that is not related to your generated key (such as having the database go down, or filling up the transaction log) then your code will loop endlessly.

You can distinguish between different kinds of SQLExceptions based on their SQLState property. For Postgres, SqlStates starting with 23 refer to constraint issues.

If you want to catch constraint exceptions then you should check the sqlstate and rethrow the exceptions that don't match, so that they will end the process (because something went wrong that is nonrecoverable).

For SQLExceptions that extend SQLTransientException it might be worthwhile to retry them. That is about the only case where using a loop for inserts like this would be justified.

Your insert SQL does not seem to have its values quoted, where you are concatenating a value where are no single quotes surrounding it, so the result will not be correct (unless you're counting on single-quotes being included as part of the passed-in string). You'd be better off using PreparedStatements, they not only protect against SQL injection but also handle quoting of parameter values for you, so they are much less error-prone.

edited Sep 18, 2014 at 15:06

answered Sep 17, 2014 at 14:21

Nathan Hughes

96.7k21 gold badges193 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

NoDataFound Over a year ago

+1 for using PreparedStatement, and I would add that you should avoid StringBuffer unless you are sure you need its thread-safe (synchronized) behavior.

Nathan Hughes Over a year ago

@NoData: it is odd to use StringBuffer like this, but since uncontended synchronizations aren't too expensive (esp compared to jdbc overhead) it seemed relatively inconsequential.

Faizy Over a year ago

guys our company has their own framework(which is bit similar to struts 1.3) ,and the project is more than 10 years old, and executedUpdate only takes StringBuffer as a parameter , and i cannot avoid StringBuffer

Nikola Dimitrovski · Accepted Answer · 2014-09-17 14:21:31Z

1

You will have endless loop if you have the same exception.

check which exceptions can be thrown by executeUpdate and add the appropriate catch blocks with appropriate messages.

Example:

   catch (PSQLException) {
        System.out.println("The key is already exist");
        isInserted=true;
    }
    catch (SQLException e) {
        System.out.println(e);
        isInserted=false;
    }catch (SQLTimeoutException e) {
        System.out.println(e);
        isInserted=false;
    }

edited Sep 17, 2014 at 14:21

answered Sep 17, 2014 at 14:15

Nikola Dimitrovski

7081 gold badge10 silver badges23 bronze badges

Collectives™ on Stack Overflow

Exception Handling using while loop

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related