java - 自定义 SqlErrorCodes

Question

我遇到了一个小问题 - 自定义 Oracle DB 错误代码。

我认为可以通过以下方式映射我的错误代码:

• 添加“CustomSQLErrorCodesTranslation”

• 添加注册“CustomSQLErrorCodesTranslation”的“SQLErrorCodes”

• 添加“SQLErrorCodeSQLExceptionTranslator”，并设置我的“SqlErrorCodes”

并使用 DI 获取“SQLErrorCodeSqlExceptionTranslator”对象，调用方法translate(String task, String sql, SQLException ex)。

这是我的作品。

<bean id="sqlExceptionTranslator" class="org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator">
        <property name="sqlErrorCodes" ref="OracleSqlErrorCodes" />
    </bean>

    <bean id="OracleSqlErrorCodes" class="org.springframework.jdbc.support.SQLErrorCodes">
        <property name="badSqlGrammarCodes">
            <value>900,903,904,917,936,942,17006</value>
        </property>
        <property name="invalidResultSetAccessCodes">
            <value>17003</value>
        </property>
        <property name="duplicateKeyCodes">
            <value>1</value>
        </property>
        <property name="dataIntegrityViolationCodes">
            <value>1400,1722,2291,2292</value>
        </property>
        <property name="dataAccessResourceFailureCodes">
            <value>17002,17447</value>
        </property>
        <property name="cannotAcquireLockCodes">
            <value>54</value>
        </property>
        <property name="cannotSerializeTransactionCodes">
            <value>8177</value>
        </property>
        <property name="deadlockLoserCodes">
            <value>60</value>
        </property>
        <property name="customTranslations">
            <list>
                <bean id="oracleCustomTranslations" class="org.springframework.jdbc.support.CustomSQLErrorCodesTranslation">
                    <property name="errorCodes" value="12899" />
                    <property name="exceptionClass" value="com.musicovery.bookervery.db.exception.SampleException" />   
                </bean>             
            </list> 
        </property> 
    </bean>

在JAVA区，

@Test(expected=SampleException.class)
public void addMemberWithLongValue(){
    memberCommand1.setName("very very very very very very very very very long text");

    try{
        memberService.addMember(memberCommand1);
    }catch(DataAccessException e){


        SQLException sqlEx = new SQLException(e);

        DataAccessException dae = sqlExceptionTranslator.translate("task? what is it?", null, sqlEx);
        System.err.println("Derived from DataAccessException : " + dae.getClass().getName());

        throw dae;
    }

}

运行该代码时，redbar显示

映射代码加不加错没有区别。

向Oracle DB插入值过长的数据时，返回错误码12899。

Caused by: java.sql.SQLException: ORA-12899: value too large for column "MUSICOVERY"."MEMBERS"."NAME" (actual: 54, maximum: 12)

我想将 12899 错误代码映射到特定的异常类。

有什么想法吗？

提前致谢。

另外，对不起，我的英语不好。

---

以下内容会定期附加以帮助您找到正确答案。

///////////////////////////////示例异常///////////////////////////////

package com.musicovery.bookervery.db.exception;
import org.springframework.dao.DataAccessException;

public class SampleException extends DataAccessException{
    private static final long serialVersionUID = 1L;

    public SampleException(String msg, Throwable cause) {
        super(msg, cause);
        System.out.println("SampleException initialized");
    }
}

---

///////////////////////////////编辑 - printStackTrace////////////////////////////////

org.springframework.jdbc.UncategorizedSQLException: task? what is it?; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; org.springframework.jdbc.UncategorizedSQLException: 
### Error updating database.  Cause: java.sql.SQLException: ORA-12899: value too large for column "MUSICOVERY"."MEMBERS"."NAME" (actual: 54, maximum: 12)

### The error may involve com.musicovery.bookervery.dao.MemberDao.addMember-Inline
### The error occurred while setting parameters
### Cause: java.sql.SQLException: ORA-12899: value too large for column "MUSICOVERY"."MEMBERS"."NAME" (actual: 54, maximum: 12)

; uncategorized SQLException for SQL []; SQL state [72000]; error code [12899]; ORA-12899: value too large for column "MUSICOVERY"."MEMBERS"."NAME" (actual: 54, maximum: 12)
; nested exception is java.sql.SQLException: ORA-12899: value too large for column "MUSICOVERY"."MEMBERS"."NAME" (actual: 54, maximum: 12)
; nested exception is java.sql.SQLException: org.springframework.jdbc.UncategorizedSQLException: 
### Error updating database.  Cause: java.sql.SQLException: ORA-12899: value too large for column "MUSICOVERY"."MEMBERS"."NAME" (actual: 54, maximum: 12)

### The error may involve com.musicovery.bookervery.dao.MemberDao.addMember-Inline
### The error occurred while setting parameters
### Cause: java.sql.SQLException: ORA-12899: value too large for column "MUSICOVERY"."MEMBERS"."NAME" (actual: 54, maximum: 12)

; uncategorized SQLException for SQL []; SQL state [72000]; error code [12899]; ORA-12899: value too large for column "MUSICOVERY"."MEMBERS"."NAME" (actual: 54, maximum: 12)
; nested exception is java.sql.SQLException: ORA-12899: value too large for column "MUSICOVERY"."MEMBERS"."NAME" (actual: 54, maximum: 12)

    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at com.musicovery.bookervery.serviceTest.MemberServiceTest.addMemberWithLongValue(MemberServiceTest.java:87)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
    at org.junit.internal.runners.statements.ExpectException.evaluate(ExpectException.java:21)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Caused by: java.sql.SQLException: org.springframework.jdbc.UncategorizedSQLException: 
### Error updating database.  Cause: java.sql.SQLException: ORA-12899: value too large for column "MUSICOVERY"."MEMBERS"."NAME" (actual: 54, maximum: 12)

### The error may involve com.musicovery.bookervery.dao.MemberDao.addMember-Inline
### The error occurred while setting parameters
### Cause: java.sql.SQLException: ORA-12899: value too large for column "MUSICOVERY"."MEMBERS"."NAME" (actual: 54, maximum: 12)

; uncategorized SQLException for SQL []; SQL state [72000]; error code [12899]; ORA-12899: value too large for column "MUSICOVERY"."MEMBERS"."NAME" (actual: 54, maximum: 12)
; nested exception is java.sql.SQLException: ORA-12899: value too large for column "MUSICOVERY"."MEMBERS"."NAME" (actual: 54, maximum: 12)

    at com.musicovery.bookervery.serviceTest.MemberServiceTest.addMemberWithLongValue(MemberServiceTest.java:85)
    ... 30 more
Caused by: org.springframework.jdbc.UncategorizedSQLException: 
### Error updating database.  Cause: java.sql.SQLException: ORA-12899: value too large for column "MUSICOVERY"."MEMBERS"."NAME" (actual: 54, maximum: 12)

### The error may involve com.musicovery.bookervery.dao.MemberDao.addMember-Inline
### The error occurred while setting parameters
### Cause: java.sql.SQLException: ORA-12899: value too large for column "MUSICOVERY"."MEMBERS"."NAME" (actual: 54, maximum: 12)

; uncategorized SQLException for SQL []; SQL state [72000]; error code [12899]; ORA-12899: value too large for column "MUSICOVERY"."MEMBERS"."NAME" (actual: 54, maximum: 12)
; nested exception is java.sql.SQLException: ORA-12899: value too large for column "MUSICOVERY"."MEMBERS"."NAME" (actual: 54, maximum: 12)

    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:346)
    at com.sun.proxy.$Proxy14.insert(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:231)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:59)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:38)
    at com.sun.proxy.$Proxy18.addMember(Unknown Source)
    at com.musicovery.bookervery.service.MemberServiceImpl.addMember(MemberServiceImpl.java:17)
    at com.musicovery.bookervery.serviceTest.MemberServiceTest.addMemberWithLongValue(MemberServiceTest.java:82)
    ... 30 more
Caused by: java.sql.SQLException: ORA-12899: value too large for column "MUSICOVERY"."MEMBERS"."NAME" (actual: 54, maximum: 12)

    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
    at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:953)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3488)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:22)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:51)
    at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:29)
    at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:88)
    at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:43)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:121)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:110)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:338)
    ... 37 more

---

我检查了异常的错误代码。

SQLException sqlEx = new SQLException(e);           
System.out.println("sqlEx error code : " + sqlEx.getErrorCode());

控制台:

sqlEx error code : 0

为什么是0？应该是 12899。

---

我正在使用 MyBatis 进行开发。我读了getting started documentation而且 API 似乎不会抛出任何异常。所以我猜 sql 错误代码是 0。

---

请告诉我你想要的任何来源，我将编辑这篇文章。

Answer 1

问题已解决。

我向翻译器传递了错误的异常。

SQLException sqlEx = new SQLException(e);

应该是这样的

SQLException sqlEx = (SQLException)e.getCause();
// System.out.println("exception code : " + sqlEx.getErrorCode());