Duplicate key violation gets translated to DataIntegrityViolationException instead of DuplicateKeyException in Spring 6

[albertus82]

Affects: 6.0.0

---

Hi, I just upgraded from 5.3.24 to 6.0.0 and noticed a different behaviour of Spring JDBC/TX. I have a class containing simple JDBC statements executed with JdbcTemplate (no JPA, no ORMs), and I wrote some test cases based on H2.

In case of duplicate key errors, I expect DuplicateKeyException, but after the upgrade I'm getting a more generic DataIntegrityViolationException (that obviously breaks the test):

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO "STORAGE" (filename, last_modified, compressed, file_contents) VALUES (?, ?, ?, ?)]; Unique index or primary key violation: "PUBLIC.PRIMARY_KEY_B ON PUBLIC.STORAGE(FILENAME) VALUES ( /* 1 */ 'myfile.txt' )"; SQL statement:
INSERT INTO "STORAGE" (filename, last_modified, compressed, file_contents) VALUES (?, ?, ?, ?) [23505-214]

Looking into the sources, I noticed that v5 instantiates a DuplicateKeyException in SQLErrorCodeSQLExceptionTranslator while v6 instantiates a DataIntegrityViolationException in SQLExceptionSubclassTranslator.

• My code: https://github.com/albertus82/simple-jdbc-filestore/blob/spring6/src/main/java/io/github/albertus82/filestore/jdbc/SimpleJdbcFileStore.java#L273
• Test: https://github.com/albertus82/simple-jdbc-filestore/blob/spring6/src/test/java/io/github/albertus82/filestore/jdbc/SimpleJdbcFileStoreTest.java#L188
• Failure log: https://github.com/albertus82/simple-jdbc-filestore/actions/runs/3492195047/jobs/5845701283#step:4:13616

Thank you.

[sbrannen]

This is to be expected due to the switch to using SQLExceptionSubclassTranslator by default.

See:

• commit 083113d
• Avoid loading XML files for default SQL exception translation #28216

If you absolutely need to differentiate between DataIntegrityViolationException and DuplicateKeyException (which is a subclass of DataIntegrityViolationException), you can configure your JdbcTemplate to use SQLErrorCodeSQLExceptionTranslator instead.

For example:

DataSource dataSource = ...
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.setExceptionTranslator(new SQLErrorCodeSQLExceptionTranslator(dataSource));
jdbcTemplate.afterPropertiesSet();

---

In light of the above, I am closing this issue as "works as designed"; however, I have also created #29518 to update the Upgrading to Spring Framework 6.x wiki page.

[albertus82]

Hi @sbrannen,

I cannot set anything on JdbcTemplate because my class is part of a library that depends on the JdbcOperations provided by the client. However this new behaviour seems to me a slight regression: I could catch DataIntegrityViolationException but how to differentiate a duplicate key violation from other substantially different violations without binding my library to a specific RDBMS?

Thanks a lot.

[sbrannen]

I cannot set anything on JdbcTemplate because my class is part of a library that depends on the JdbcOperations provided by the client.

Understood.

However this new behaviour seems to me a slight regression:

Technically speaking it is indeed a regression in the default behavior, but there are ways to get the old behavior back (the one I mentioned previously and one that I'll mention below).

I could catch DataIntegrityViolationException but how to differentiate a duplicate key violation from other substantially different violations without binding my library to a specific RDBMS?

I believe you should be able to introduce an empty sql-error-codes.xml file in the root of the classpath to achieve the same result.

If neither of these options works for you, please post back here, and we will continue the discussion.

[albertus82]

Fixed adding the following "empty" sql-error-codes.xml file in the root of the classpath:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN 2.0//EN" "https://www.springframework.org/dtd/spring-beans-2.0.dtd">
<beans />

Thank you again.

[lukas-krecan]

As a library author, I can't use any of the mentioned workarounds