I try to understand why, as opposed to what's stated by the Spring documentation , a SQLException is not translated into a DataAccessException.
This is my Spring configuration:
@Configuration
@ComponentScan
public class AppConfig {
@Bean
DataSource getDataSource() {
String dbUrl = mySqlUrl;
String dbUser = "user";
String dbPassword = "password";
MysqlDataSource mysqlDS = null;
mysqlDS = new MysqlDataSource();
mysqlDS.setURL(dbUrl);
mysqlDS.setUser(dbUser);
mysqlDS.setPassword(dbPassword);
return mysqlDS;
}
@Bean
static public PersistenceExceptionTranslationPostProcessor translation() {
return new PersistenceExceptionTranslationPostProcessor();
}
}
A @Repository class:
@Repository
public class UsersDAO {
DataSource dataSource;
@Autowired
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
void insertUser(User user) {
try(Connection conn = this.dataSource.getConnection()) {
try(PreparedStatement pstmt = conn.prepareStatement("insert into users (id, name) values (?, ?)")) {
pstmt.setInt(1, user.getId());
pstmt.setString(2, user.getName());
int result = pstmt.executeUpdate();
}
} catch(SQLException e) {
throw new RuntimeException(e);
}
}
}
I also tried without catch and rethrow:
void insertUser(User user) throws SQLException {
try(Connection conn = this.dataSource.getConnection()) {
try(PreparedStatement pstmt = conn.prepareStatement("insert into users (id, name) values (?, ?)")) {
pstmt.setInt(1, user.getId());
pstmt.setString(2, user.getName());
int result = pstmt.executeUpdate();
}
}
And the application:
public class App {
public static void main(String[] args) throws SQLException {
ApplicationContext context = new AnnotationConfigApplicationContext(AppConfig.class);
UsersDAO dao = (UsersDAO)context.getBean("usersDAO");
User john = new User(5, "John");
dao.insertUser(john);
}
}
When I run this twice, (so that the same user key is added and I get a DuplicateKey exception) - I get a java.sql. exception:
Exception in thread "main" java.lang.RuntimeException: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '5' for key 'users.PRIMARY'
at org.example.UsersDAO.insertUser(UsersDAO.java:58)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:354)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:768)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:138)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:768)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:720)
at org.example.UsersDAO$$SpringCGLIB$$0.insertUser(<generated>)
at org.example.App.main(App.java:27)
Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '5' for key 'users.PRIMARY'
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:118)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1009)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1320)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:994)
at org.example.UsersDAO.insertUser(UsersDAO.java:54)
... 14 more
Before you ask - I don't use JdbcTemplate since I have a use-case where I need to use raw JDBC, and I'm exercising a POC to see if Spring translates possible SQL exceptions.
Does anyone know why Spring doesn't translate the SQLException (or subtype in that case) to DataAcccessException (or subtype).
At first glance just use JdbcTemplate as that will do the conversion. Just use the JdbcTemplate.execute which takes a ConnectionCallback if you want access to the underlying JDBC Connection.
@Repository
public class UsersDAO {
private final JdbcTemplate jdbc;
public UsersDao(JdbcTemplate jdbc) {
this.jdbc=jdbc;
}
void insertUser(User user) {
this.jdbc.execute((Connection conn) -> {
try(PreparedStatement pstmt = conn.prepareStatement("insert into users (id, name) values (?, ?)")) {
pstmt.setInt(1, user.getId());
pstmt.setString(2, user.getName());
int result = pstmt.executeUpdate();
}
});
}
}
This would achieve what you want to convert the exception and still access the underlying JDBC resources. Depending on your needs you could also use one of the other execute methods.
If you really want to refrain from using either JdbcTemplate or JdbcClient, although I see no reason for not using it. You would need to create an adapter for the PersistenceExceptionTranslator to the SQLExceptionTranslator as there is non. The PersistenceExceptionTranslator is for converting RuntimeExceptions from persistence providers, like JPA, to the DataAccessException hierarchy. There is none by default.
public PersistenceExceptionTranslatorAdapter implements PersistenceExceptionTranslator {
private final SQLExceptionTranslator translator;
public PersistenceExceptionTranslatorAdapter() {
this(new SQLExceptionSubclassTranslator());
}
public PersistenceExceptionTranslatorAdapter(SQLExceptionTranslator translator) {
this.translator=translator;
}
DataAccessException translateExceptionIfPossible(RuntimeException ex) {
var cause = ex.getCause();
if (cause instanceof SQLException sqlEx) {
return translator.translate("task", null, sqlEx);
}
return null;
}
}
Then in your config create a bean for this so it will be detected and used by the PersistenceExceptionTranslationPostProcessor.
@Bean
public PersistenceExceptionTranslatorAdapter persistenceExceptionTranslator() {
return new PersistenceExceptionTranslatorAdapter();
}
The final option is to use a bit more of Spring and use @Transactional combined with a DataSourceTransactionManager, @EnableTransactionManagement and DataSourceUtils.
@Configuration
@ComponentScan
@EnableTransactionManagement
public class AppConfig {
@Bean
DataSource getDataSource() {
String dbUrl = mySqlUrl;
String dbUser = "user";
String dbPassword = "password";
MysqlDataSource mysqlDS = null;
mysqlDS = new MysqlDataSource();
mysqlDS.setURL(dbUrl);
mysqlDS.setUser(dbUser);
mysqlDS.setPassword(dbPassword);
return mysqlDS;
}
@Bean
public DataSourceTransactionManager transactionManager(DataSource ds) {
return new DataSourceTransactionManager(ds);
}
}
Now change your UsersDao to use the DataSourceUtils and @Transactional.
@Repository
public class UsersDAO {
DataSource dataSource;
@Autowired
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
@Transactional
public void insertUser(User user) throws SQLException {
try(Connection conn = DataSourceUtils.getConnection(dataSource)) {
try(PreparedStatement pstmt = conn.prepareStatement("insert into users (id, name) values (?, ?)")) {
pstmt.setInt(1, user.getId());
pstmt.setString(2, user.getName());
int result = pstmt.executeUpdate();
}
}
}
}
Now when an exception occurs it will be converted through the DataSourceTransactionManager, which will delegate that to a SQLExceptionTranslator as well (just as the JdbcTemplate).