时间戳

首页 > 解决方案 > 为什么即使我添加了 jdbcType，插入也会失败并显示错误代码 17004？

sql - 为什么即使我添加了 jdbcType，插入也会失败并显示错误代码 17004？

问题描述

我一直在处理插入，它一直失败并出现错误代码 17004。

我认为问题是因为“插入”中有一个“选择”，但在 Oracle SQL 开发人员中，它起作用了。错误代码 17004 是由于 null 值问题，所以我还在我提供的每个参数中添加了 jdbcType，但它仍然无法正常工作。

这是我插入失败的 SQL，错误代码为 17004。

<insert id="registerNewPenalty" parameterType="TbAdhPenaltyTreatVO">
INSERT INTO
    TB_ADH_PENALTYTREAT
VALUES (
    (
        SELECT
            TO_CHAR(NVL(MAX(HISTNO), 0) + 1) AS MAXSEQ
        FROM
            TB_ADH_PENALTYTREAT
     ),
    #{penaltytpcd, jdbcType=VARCHAR},
    #{regdt, jdbcType=DATE},
    #{occurdt, jdbcType=DATE},
    #{runvioltpcd, jdbcType=DATE},
    #{compid, jdbcType=INTEGER},
    #{routeid, jdbcType=INTEGER},
    #{busid, jdbcType=INTEGER},
    'bmscenter',
    NULL,
    #{treat_detail, jdbcType=VARCHAR},
    '0'
)
</insert>

这是我的 TbAdhPenaltyTreatVO。

public class TbAdhPenaltyTreatVO {
private String histno;          //  NUMBER(10,0)        Yes     1   
private String penaltytpcd;     //  VARCHAR2(1 BYTE)    Yes     2   
private String regdt;           //  DATE                Yes     3   
private String occurdt;         //  DATE                Yes     4   
private String runvioltpcd;     //  VARCHAR2(2 BYTE)    Yes     5   
private String compid;          //  NUMBER(6,0)         Yes     6   
private String routeid;         //  NUMBER(9,0)         Yes     7   
private String busid;           //  NUMBER(9,0)         Yes     8   
private String treat_userid;    //  VARCHAR2(20 BYTE)   Yes     9   
private String treat_orgnm;     //  VARCHAR2(20 BYTE)   Yes     10  
private String treat_detail;    //  VARCHAR2(100 BYTE)  Yes     11  
private String treatyn;         //  VARCHAR2(1 BYTE)    Yes     12


... getters and setters below

我期望并在 Oracle SQL Developer 中工作的查询是：

INSERT INTO tb_adh_penaltytreat
VALUES   (1,
         '1',
         SYSDATE,
         To_date('2017/11/02 04:40:47', 'YYYY/MM/DD HH24:MI:SS'),
         '13',
         '164003',
         '165000310',
         '7012268',
         'bmscenter',
         NULL,
         '......',
         '0');

对于顶部的查询，查询以以下日志结束：

[2019-01-29 16:03:17] DEBUG: org.mybatis.spring.SqlSessionUtils - Creating a new SqlSession
[2019-01-29 16:03:17] DEBUG: org.mybatis.spring.SqlSessionUtils - Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@777f6077]
[2019-01-29 16:03:17] DEBUG: org.mybatis.spring.transaction.SpringManagedTransaction - JDBC Connection [jdbc:oracle:thin:@10.10.10.20:1521:orcl, UserName=ICBIS_AGO, Oracle JDBC driver] will be managed by Spring
[2019-01-29 16:03:17] DEBUG: PENALTY.registerNewPenalty - ==>  Preparing: INSERT INTO TB_ADH_PENALTYTREAT VALUES ( ( SELECT TO_CHAR(NVL(MAX(HISTNO), 0) + 1) AS MAXSEQ FROM TB_ADH_PENALTYTREAT ), ?, ?, ?, ?, ?, ?, ?, 'bmscenter', NULL, ?, '0' ) 
[2019-01-29 16:03:17] DEBUG: PENALTY.registerNewPenalty - ==> Parameters: 1(String), null, 2017-11-02 05:54:35.0(String), 13(String), 166012(String), 165000067(String), 7016047(String), this is for test.(String)
[2019-01-29 16:03:17] DEBUG: org.mybatis.spring.SqlSessionUtils - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@777f6077]

我不知道问题是什么，但怀疑它可能是“选择”的问题。感谢您的阅读。

更新

我像这样更改了我的查询：

    <insert id="registerNewPenalty" parameterType="TbAdhPenaltyTreatVO">
    INSERT INTO
    TB_ADH_PENALTYTREAT (
        HISTNO,
        PENALTYTPCD,
        REGDT,
        OCCURDT,
        RUNVIOLTPCD,
        COMPID,
        ROUTEID,
        BUSID,
        TREAT_USERID,
        TREAT_ORGNM,
        TREAT_DETAIL,
        TREATYN
    )
    VALUES (
    (
        SELECT
            TO_CHAR(NVL(MAX(HISTNO), 0) + 1) AS MAXSEQ
        FROM
            TB_ADH_PENALTYTREAT
     ),
    #{penaltytpcd, jdbcType=VARCHAR},
    SYSDATE,
    #{occurdt, jdbcType=DATE},
    #{runvioltpcd, jdbcType=DATE},
    #{compid, jdbcType=INTEGER},
    #{routeid, jdbcType=INTEGER},
    #{busid, jdbcType=INTEGER},
    'bmscenter',
    NULL,
    #{treat_detail, jdbcType=VARCHAR},
    '0'
    )
    </insert>

查询以以下调试日志结束：

[2019-01-30 09:15:47] DEBUG: org.mybatis.spring.SqlSessionUtils - Creating a new SqlSession
[2019-01-30 09:15:47] DEBUG: org.mybatis.spring.SqlSessionUtils - Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5f587204]
[2019-01-30 09:15:47] DEBUG: org.mybatis.spring.transaction.SpringManagedTransaction - JDBC Connection [jdbc:oracle:thin:@10.10.10.20:1521:orcl, UserName=ICBIS_AGO, Oracle JDBC driver] will be managed by Spring
[2019-01-30 09:15:47] DEBUG: PENALTY.registerNewPenalty - ==>  Preparing: INSERT INTO TB_ADH_PENALTYTREAT ( HISTNO, PENALTYTPCD, REGDT, OCCURDT, RUNVIOLTPCD, COMPID, ROUTEID, BUSID, TREAT_USERID, TREAT_ORGNM, TREAT_DETAIL, TREATYN ) VALUES ( ( SELECT TO_CHAR(NVL(MAX(HISTNO), 0) + 1) AS MAXSEQ FROM TB_ADH_PENALTYTREAT ), ?, SYSDATE, ?, ?, ?, ?, ?, 'bmscenter', NULL, ?, '0' ) 
[2019-01-30 09:15:47] DEBUG: PENALTY.registerNewPenalty - ==> Parameters: 1(String), 2017-11-02 05:54:35.0(String), 13(String), 166012(String), 165000067(String), 7016047(String), This data is for test.(String)
[2019-01-30 09:15:47] DEBUG: org.mybatis.spring.SqlSessionUtils - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5f587204]
[2019-01-30 09:15:47] DEBUG: org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator - Unable to translate SQLException with Error code '1861', will now try the fallback translator
[2019-01-30 09:15:47] DEBUG: org.springframework.jdbc.support.SQLStateSQLExceptionTranslator - Extracted SQL state class '22' from value '22008'
[2019-01-30 09:15:47] DEBUG: org.mybatis.spring.SqlSessionUtils - Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5f587204]
[2019-01-30 09:15:47] DEBUG: org.mybatis.spring.SqlSessionUtils - Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5f587204]
[2019-01-30 09:15:47] DEBUG: org.springframework.jdbc.datasource.DataSourceTransactionManager - Initiating transaction rollback
[2019-01-30 09:15:47] DEBUG: org.springframework.jdbc.datasource.DataSourceTransactionManager - Rolling back JDBC transaction on Connection [jdbc:oracle:thin:@10.10.10.20:1521:orcl, UserName=ICBIS_AGO, Oracle JDBC driver]
[2019-01-30 09:15:47] DEBUG: org.springframework.jdbc.datasource.DataSourceTransactionManager - Releasing JDBC Connection [jdbc:oracle:thin:@10.10.10.20:1521:orcl, UserName=ICBIS_AGO, Oracle JDBC driver] after transaction
[2019-01-30 09:15:47] DEBUG: org.springframework.jdbc.datasource.DataSourceUtils - Returning JDBC Connection to DataSource

**第二次更新**

我已经改成#{occurdt, jdbcType=DATE}NULL了，插入成功了。所以我认为问题出在#{occurdt, jdbcType=DATE}.

标签： sqlspringoraclemybatis

解决方案

MyBatis 不会TO_DATE自动添加。您需要在语句中编写函数。

TO_DATE(#{occurdt}, 'YYYY/MM/DD HH24:MI:SS'),

另一种选择是编写一个自定义类型处理程序，将字符串从/转换为 DATE。

#{occurdt,typeHandler=com.xxx.YourCustomTypeHandler},

如果您可以控制 VO 定义，则将属性类型更改为LocalDate或也值得考虑。Date然后#{occurdt}应该工作。

推荐阅读