5

I write Java program using JDBC (mysql database). When I violate mysql integrity (f.e. I try to insert same primary key value) I catch SQL exception. Should I write it in way it may never happen (f.e. at first boolean function checking whether primary key value isn't already in DB and then calling insert), or is it okay to handle it just by exception? Example :

catch (SQLException ex) {ex.printStackTrace(); showSomeErrorDialog(); }
Valentin Rocher
  • 11,667
  • 45
  • 59
Michal
  • 53
  • 1
  • 3
  • Correct me if I am wrong, but by default don't you have to perform a `try` and `catch` block for `SQLException` when performing queries to begin with? – Anthony Forloney Feb 04 '10 at 16:13
  • if you are executing query from statement you have to. Statement st = connection.createStatement(); st.executeQuery("SELECT * FROM table"); those needs try and catch – Michal Feb 04 '10 at 16:16
  • How about when doing `INSERT`? My reason for asking is that, if it requires a try-block by default when executing a query, you have to do so anyways, but like I said I could be wrong, I don't work with JDBC at all. – Anthony Forloney Feb 04 '10 at 16:18
  • insert is kind of query, isn't it? I dont see special command for insert, only for update (not executeQuery but executeUpdate) besides every executing throws SQLException – Michal Feb 04 '10 at 16:21

3 Answers3

4

There are indeed basically two ways to achieve this:

  1. Test if record exists before inserting --inside the same transaction.

  2. Determine if SQLException#getSQLState() of the catched SQLException starts with 23 which is a constraint violation as per the SQL specification. It can namely be caused by more factors than "just" a constraint violation. You should not amend every SQLException as a constraint violation.

    public static boolean isConstraintViolation(SQLException e) {
    return e.getSQLState().startsWith("23");
}

I would opt for the first one as it is semantically more correct. It is in fact not an exceptional circumstance. You namely know that it is potentially going to happen. But it may potentially fail in heavy concurrent environment where transactions are not synchronized (either unawarely or to optimize performance). You may then want to determine the exception instead.

That said, you normally shouldn't get a constraint violation on a primary key. In well designed datamodels which uses technical keys as primary keys they are normally to be managed by the database itself. Isn't the field supposed to be an unique key?

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
  • It is unique, so insert will just fail :) nothing bad will actually happen, only error message – Michal Feb 04 '10 at 16:50
2

There are two possible answers :

  • if you know that your application is designed to avoid this kind of behaviour, use the exception
  • if your application can make these errors often, use a test.
Valentin Rocher
  • 11,667
  • 45
  • 59
  • about performance - it is not very probable that this kind of exception will happen in my application. Therefore testing before every insert will consume much more time than just insert itself with occasional exception. – Michal Feb 04 '10 at 16:12
  • 2
    On the other hand, if the application is already believed to be generating unique keys, then it would be quite appropriate to handle a failure for this as an exception - since, after all, it would be considered an exceptional case. – Matthew Wilson Feb 04 '10 at 16:12
  • I see thanx .... I was actually hoping for some1 who will say exact other thing than you bishiboosh :-D But it is just my laziness probably – Michal Feb 04 '10 at 16:23
1

As others have mentioned that there are two possible approaches, one is to test and then insert/update otherwise handle SQL Exception. Both these approaches have their downsides:

  • Caveat for "Test before inserting" is that every transaction will have additional queries which will impact performance. This is specially a bigger issue when such erroneous transactions are few in number.
  • Caveat for "Evaluating SQL Exception" is that such exception messages are very database specific. These messages, most of the time, don't give specific information beyond stating that there is constrain violation.

So, I will propose an approach which is hybrid of the two.

  • Don't perform the test before insert.
  • Let database throw an exception.
  • Catch SQL Exception.
  • In the exception flow (catch block), do additional queries to form very specific error messages to indicate customers what has exactly failed (unique key, primary key, foreign key, specific columns etc).

This may require few additional lines of code but it definitely improves performance and generates friendly error messages.

Bipul
  • 1,564
  • 1
  • 15
  • 16