1

This is probably very silly to ask, but I'm facing this situation:

I have a table that I'm populating every time a user sends a request, I'm using the date on one the fields (a date) from the last row I inserted. The problem is, the last row I inserted is always going to try and insert itself because it is in the range I'm considering. I know a solution is probably just adding a minute or two to the last row, but from this my question comes. How can I in java, using a PreparedStatement and a batch to insert multiple rows, skip the ones that send me that PRIMARY_KEY_VIOLATION exception, and keep inserting the ones that are ok? I mean, for instance, I have in my table a row "runner" with the value 25, and that's my primary key, and then I want to insert these rows: (25,26,27) The 25 values cannot be inserted, so I want to catch the exception and do something to keep inserting the 26 and 27 values. Is it possible?

I appreciate your help and comments. Thank you in advance!

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Azu
  • 86
  • 1
  • 10
  • See the `BatchUpdateException` API. What you're suggesting _might_ be possible but will be horribly ugly. Avoid the PK violation. Create a sequence so you can "INSERT INTO TABLE T SELECT MYSEQUENCE.NEXTVAL, OTHER_FIELD1, OHTER_FIELD2 WHERE ... – Andrew S May 30 '17 at 16:17
  • @AndrewS - Using a sequence does not solve the problem. Sure it generates a unique value but you have just inserted a duplicate record with a meaningless technical key. – APC May 30 '17 at 17:28
  • The question is, why are you selecting an already inserted row? Surely the problem you need to fix is getting a valid data source rather than handling exceptions in the data target. – APC May 30 '17 at 17:31
  • JDBC driver implementations can choose to stop on error or continue batch execution on error, but drivers must consistently use the same handling. Oracle chose to bail on error; there is no way to change this. – Mark Rotteveel May 31 '17 at 11:34

1 Answers1

0

It's not fully clear to me what you're trying to achieve. A db schema, code and an example might help. But from what I understand, I have the following hints:

  • Think about your primary key

    • A technical key should be unique without any programming (like trial & error...). A timestamp usually does not satisfy that criteria. The sequence datatype of your database or a GUID is a good bet as they're intended for that use.

    • A non-techical key should represent the whole row and only the specific row. A date/timestamp might be an acceptable key to represent a day in a calendar application or a specific instance in time. A timestamp usually does not represent a "user request".

  • Think in transactions: Databases generally emphasize atomic, transactional operations. If a part of an operations fails, you have to rollback the whole transaction. As AndrewS points out, continuing your batch processing after a BatchUpdateException might be possible (depending on the driver) but that's definitely not a good practice. It's a code smell that indicates you're doing something wrong the first place.

  • Avoid exceptions: Using Exceptions for control flow is also considered a code smell (see e.g. Java: Exceptions as control flow? with reference to Item 57 in Joshua Bloch's "Effective Java"). Checking for conditions that lead to an exception before trying an operation is usually preferred. Or as APC points out: if you know your code returns the last row you inserted, why don't you just leave this out of your insert statement?

sruetti
  • 532
  • 2
  • 7