Java,Springboot。 JSON数字类型中的字符串值无法保存Oracle

发布于 2025-02-13 19:07:17 字数 2552 浏览 0 评论 0原文

我正在以JSON格式将数据合并到Oracle数据库中。 但是只有这些家伙无法插入价值。有什么问题?

查询是用XML编写的吗?

控制器

grade_json_path是加载下面JSON文件的变量的名称。

@PostMapping("/grade")
    public ResponseEntity<Void> mergeCommonCodeGrade() {
        ObjectMapper obj = new ObjectMapper();

        InputStream gradeJsonInput = TypeReference.class.getResourceAsStream(GRADE_JSON_PATH);

        try {
            CommonCodeDTO[] commonCodeDTO = obj.readValue(gradeJsonInput, CommonCodeDTO[].class);

            mapper.innoMergeIntoCommonCodeGrade(commonCodeDTO);
        } catch (IOException e) {
            e.getMessage();
        }

        return new ResponseEntity<>(HttpStatus.NO_CONTENT);
    }

grade_json_path

x1 x1存储在DB中,但13和15不是。

[
  {
    "code" : "X1",
    "codeName" : "GOOD"
  },
  {
    "code" : "13",
    "codeName" : "MB5"
  },
  {
    "code" : "15",
    "codeName" : "SAN"
  }
]

xml

<update id="innoMergeIntoCommonCodeGrade" parameterType="java.util.List">
        MERGE INTO inno.B_COMMON_CODE A1
        USING (
        <foreach collection="commonCodeDTO" item="item" index="index" separator="UNION" open="" close="">
            SELECT
            'GRADE' AS MASTER_CODE
            , #{item.code} AS CODE
            , #{item.codeName} AS CODE_NAME
            , 'Y' AS STATUS
            , 'admin' AS REG_ID
            , TO_CHAR(SYSDATE,'YYYYMMDD') AS REG_DATE
            , TO_CHAR(SYSDATE, 'HH24MISS') AS REG_TIME
            , 'admin' AS MOD_ID
            , TO_CHAR(SYSDATE,'YYYYMMDD') AS MOD_DATE
            , TO_CHAR(SYSDATE, 'HH24MISS') AS MOD_TIME
            FROM DUAL
        </foreach>
        ) T1
        ON (A1.CODE = T1.CODE)
        WHEN MATCHED THEN
        UPDATE SET
        A1.CODE_NAME = T1.CODE_NAME,
        A1.MOD_ID = T1.MOD_ID,
        A1.MOD_DATE = T1.MOD_DATE,
        A1.MOD_TIME = T1.MOD_TIME
        WHEN NOT MATCHED THEN
        INSERT (
          A1.MASTER_CODE
        , A1.CODE
        , A1.CODE_NAME
        , A1.STATUS
        , A1.REG_ID
        , A1.REG_DATE
        , A1.REG_TIME
        , A1.MOD_ID
        , A1.MOD_DATE
        , A1.MOD_TIME
        )
        VALUES (
          T1.MASTER_CODE
        , T1.CODE
        , T1.CODE_NAME
        , T1.STATUS
        , T1.REG_ID
        , T1.REG_DATE
        , T1.REG_TIME
        , T1.MOD_ID
        , T1.MOD_DATE
        , T1.MOD_TIME
        )
    </update>

I am in the process of merging data in json format into oracle database.
But only these guys can't insert the value. What's the problem?

Is the query written in xml the problem?

Controller

GRADE_JSON_PATH is the name of the variable that loads the json file below.

@PostMapping("/grade")
    public ResponseEntity<Void> mergeCommonCodeGrade() {
        ObjectMapper obj = new ObjectMapper();

        InputStream gradeJsonInput = TypeReference.class.getResourceAsStream(GRADE_JSON_PATH);

        try {
            CommonCodeDTO[] commonCodeDTO = obj.readValue(gradeJsonInput, CommonCodeDTO[].class);

            mapper.innoMergeIntoCommonCodeGrade(commonCodeDTO);
        } catch (IOException e) {
            e.getMessage();
        }

        return new ResponseEntity<>(HttpStatus.NO_CONTENT);
    }

GRADE_JSON_PATH

X1 is stored in DB, but 13 and 15 are not.

[
  {
    "code" : "X1",
    "codeName" : "GOOD"
  },
  {
    "code" : "13",
    "codeName" : "MB5"
  },
  {
    "code" : "15",
    "codeName" : "SAN"
  }
]

XML

<update id="innoMergeIntoCommonCodeGrade" parameterType="java.util.List">
        MERGE INTO inno.B_COMMON_CODE A1
        USING (
        <foreach collection="commonCodeDTO" item="item" index="index" separator="UNION" open="" close="">
            SELECT
            'GRADE' AS MASTER_CODE
            , #{item.code} AS CODE
            , #{item.codeName} AS CODE_NAME
            , 'Y' AS STATUS
            , 'admin' AS REG_ID
            , TO_CHAR(SYSDATE,'YYYYMMDD') AS REG_DATE
            , TO_CHAR(SYSDATE, 'HH24MISS') AS REG_TIME
            , 'admin' AS MOD_ID
            , TO_CHAR(SYSDATE,'YYYYMMDD') AS MOD_DATE
            , TO_CHAR(SYSDATE, 'HH24MISS') AS MOD_TIME
            FROM DUAL
        </foreach>
        ) T1
        ON (A1.CODE = T1.CODE)
        WHEN MATCHED THEN
        UPDATE SET
        A1.CODE_NAME = T1.CODE_NAME,
        A1.MOD_ID = T1.MOD_ID,
        A1.MOD_DATE = T1.MOD_DATE,
        A1.MOD_TIME = T1.MOD_TIME
        WHEN NOT MATCHED THEN
        INSERT (
          A1.MASTER_CODE
        , A1.CODE
        , A1.CODE_NAME
        , A1.STATUS
        , A1.REG_ID
        , A1.REG_DATE
        , A1.REG_TIME
        , A1.MOD_ID
        , A1.MOD_DATE
        , A1.MOD_TIME
        )
        VALUES (
          T1.MASTER_CODE
        , T1.CODE
        , T1.CODE_NAME
        , T1.STATUS
        , T1.REG_ID
        , T1.REG_DATE
        , T1.REG_TIME
        , T1.MOD_ID
        , T1.MOD_DATE
        , T1.MOD_TIME
        )
    </update>

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。
列表为空,暂无数据
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文