search

spring-projects / spring-framework

Spring Framework
https://spring.io/projects/spring-framework
Apache License 2.0
56.7k stars 38.14k forks source link

Add optional Api to extract detailed information from jdbc SQLException #32120

Closed AlwaysNoobCoder closed 9 months ago

AlwaysNoobCoder commented 9 months ago

Affects: Srping 5

I am using mybatis + spring against postgresql

when updating a column with unique constraints, the exception is like below:

Caused by: org.springframework.dao.DuplicateKeyException: 
### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "users_email_key"
  详细：Key (email)=(fanguogang@itps.bgzchina.com) already exists.
### The error may exist in com/wxt/itps/services/pub/mappers/UserMapper.xml
### The error may involve com.wxt.itps.services.pub.mappers.UserMapper.updateByPrimaryKeySelective-Inline
### The error occurred while setting parameters
### SQL: update users      SET email = ?      where id = ?
### Cause: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "users_email_key"
  详细：Key (email)=(xxx@itps.xxx.com) already exists.
; ERROR: duplicate key value violates unique constraint "users_email_key"
  详细：Key (email)=(xxx@itps.xxx.com) already exists.
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:254) ~[spring-jdbc-6.1.1.jar:6.1.1]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107) ~[spring-jdbc-6.1.1.jar:6.1.1]
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92) ~[mybatis-spring-3.0.3.jar:3.0.3]
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:439) ~[mybatis-spring-3.0.3.jar:3.0.3]
    at jdk.proxy2/jdk.proxy2.$Proxy96.update(Unknown Source) ~[na:na]
    at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:288) ~[mybatis-spring-3.0.3.jar:3.0.3]
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:67) ~[mybatis-3.5.14.jar:3.5.14]
    at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:141) ~[mybatis-3.5.14.jar:3.5.14]
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86) ~[mybatis-3.5.14.jar:3.5.14]
    at jdk.proxy2/jdk.proxy2.$Proxy119.updateByPrimaryKeySelective(Unknown Source) ~[na:na]
    ... 58 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "users_email_key"
  详细：Key (email)=(xxx@itps.xxx.com) already exists.
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713) ~[postgresql-42.6.0.jar:42.6.0]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401) ~[postgresql-42.6.0.jar:42.6.0]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368) ~[postgresql-42.6.0.jar:42.6.0]
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498) ~[postgresql-42.6.0.jar:42.6.0]
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415) ~[postgresql-42.6.0.jar:42.6.0]
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190) ~[postgresql-42.6.0.jar:42.6.0]
    at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:177) ~[postgresql-42.6.0.jar:42.6.0]
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-5.0.1.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-5.0.1.jar:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
    at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:58) ~[mybatis-3.5.14.jar:3.5.14]
    at jdk.proxy3/jdk.proxy3.$Proxy120.execute(Unknown Source) ~[na:na]
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:48) ~[mybatis-3.5.14.jar:3.5.14]
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:75) ~[mybatis-3.5.14.jar:3.5.14]
    at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[mybatis-3.5.14.jar:3.5.14]
    at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.5.14.jar:3.5.14]
    at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) ~[mybatis-3.5.14.jar:3.5.14]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ~[mybatis-3.5.14.jar:3.5.14]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:425) ~[mybatis-spring-3.0.3.jar:3.0.3]
    ... 64 common frames omitted

I want to extract the column name which caused this exception, but with current API, it's not possible, except directly cast to org.postgresql.util.PSQLException, but that's a vendor specific way..

I want the column name to display user friendly error message to end user, the front-end can using the column name to highlight a input box in a form

can we add some a method to DataAccessingException to get causing column ?, it's a optional feature, if underlying jdbc driver support this, then return column name, if not return null...

snicoll commented 9 months ago

want to extract the column name which caused this exception, but with current API, it's not possible, except directly cast to org.postgresql.util.PSQLException

Even if you did that, you still need to parse the error message to find it is about a column. The exception itself does not expose that information.

can we add some a method to DataAccessingException to get causing column ?, it's a optional feature, if underlying jdbc driver support this, then return column name, if not return null...

I am afraid not. We're not going to add database-specific exception message parsing to provide a higher-level API as it's far too error prone and us parsing error messages like that is not a good idea. Thanks for the suggestion.

AlwaysNoobCoder commented 9 months ago

@snicoll thanks, totally understand.

just want to mention this: with postgresql's org.postgresql.util.PSQLException, you can get a ServerErrorMessage Object, which already expose many method to extract useful information, including causing column