java - Spring doesn't translate SQLException to DataAccessException - Stack Overflow

admin管理员组

文章数量:1180541

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);
        }
        
    }
}

EDIT

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).

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);
        }
        
    }
}

EDIT

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).

• java
• sql
• spring

Share Improve this question Follow edited yesterday YoavKlein asked yesterday YoavKleinYoavKlein 2,70522 gold badges2222 silver badges8888 bronze badges 3

• Why, because you are catching it and wrapping it. Spring never sees the exception. What it doesn't see it cannot translate. Next to that your method needs to be public not default access. Finally just use JdbcTemplate that is a lot easier and less erorr prone. You can just use the execute method that takes a ConnectionCallback if you really need the connection. – M. Deinum Commented yesterday
• Take a look at the edit. Also the public is not relevant since it's all in the same package, but added just for the manner of sake. Still the same results. I'll still like to know why translation is not happening, merely for educational pupropses. – YoavKlein Commented yesterday
• Additionally afaik the PersistenceExceptionTranslationPostProcessor will also only work for technologies that throw runtime exceptions like JPA. If you check the available implementations. The SQL translation for the rest is done in the JdbcTemplate and JdbcClient. The public is relevant as the translation is added through AOP and uses proxies which is problematic for non public or protected methods. If you want it to work you need your own implementation of the PersistenceExceptionTranslator which will adapt to SQLExceptionTranslator. But as mentioned just use JdbcTemplate. – M. Deinum Commented yesterday

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

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).

本文标签： javaSpring doesn39t translate SQLException to DataAccessExceptionStack Overflow