Input validation and error handling

exercise No. 46

Q:

The solution of GUI for inserting Person data to a database server does have deficiencies:

  1. There is no input data validation. Even if both fields remain blank hitting Insert will still trigger a database INSERT INTO Person VALUES('', '') attempt. Moreover email addresses should be parsed for correctness as well prior to inserting into the database.

    Tip

    Read about input field validation. Then use setEnabled(...) to activate and de-activate the Insert button accordingly.

  2. Trying to insert a second record having identical email address will fail due to a primary key violation. This exception type should be explained to the end user in a meaningful fashion.

    Tip

    There are at least two ways dealing with primary/unique key constraint violations:

    • Check for an existing database record matching the key value by a SELECT prior to inserting the dataset in question.

    • Analyzing the SQLException's error code / SQLState allows for distinguishing primary key constraint violations from other problem types like e.g. database (link layer) communication failures.

      Examine the exception. Then modify your try {...} catch {...} code. Hint: According to the SQL standard constraint violations cause SQLState values starting with 23. This is honoured by (all??) database vendors. You may safely assume that constraint violations in the current context are indeed unique / primary key constraint violations.

A:

A more general way to deal with this problem is trying to SELECT a dataset having the email address in question. If the result is non-empty the intended INSERT will be inhibited thus avoiding the attempt in the first place rather than giving rise to an exception.