时间戳

首页 > 解决方案 > Oracle Java Prepared Statement 如果不存在则插入

java - Oracle Java Prepared Statement 如果不存在则插入

问题描述

我对 Java PreparedStatement 和 Oracle 有疑问。

简而言之，我想在我的 Oracle DB 中使用 Java 创建一个批量插入。我尝试使用以下代码来做到这一点：

       PreparedStatement preparedStmt = connection.prepareStatement(
                "INSERT INTO EFM_BAS_DATA_CLEAN_NUM (date_measured, time_measured, value_reported, data_point_id) " +
                " VALUES(?,?,?,?)");

       PreparedStatement preparedStmt = connection.prepareStatement(query);
       for (EfmBasDataCleanNum measure : measuresToInsert) {

            preparedStmt.setString(1, new java.sql.Date(measure.getDateMeasured().getTime()));
            preparedStmt.setString(2, measure.getTimeMeasured());
            preparedStmt.setDouble(3, measure.getValueReported());
            preparedStmt.setInt(4, measure.getDataPointId());
            preparedStmt.addBatch();
        }

        try {
            preparedStmt.executeBatch();
        }catch (SQLException e){ ...

但是，当表中已经存在某些记录时，出现此错误：

ORA-00001: 违反唯一约束 (AFM.UNIQUE_EFM_CLEAN_NUM)

因为我对这个字段有限制。

所以，上网找了很多解决办法。

我试过这个查询：

String query = "INSERT INTO EFM_BAS_DATA_CLEAN_NUM (date_measured, time_measured, value_reported, data_point_id) "+ 
                "   SELECT TO_DATE(?,'DD/MM/YYYY HH24:MI:SS'),TO_DATE(?,'DD/MM/YYYY HH24:MI:SS'),?,? FROM DUAL "+
                "   MINUS "+
                "   SELECT date_measured, time_measured, value_reported, data_point_id FROM efm_bas_data_clean_num";

或与：

String query = " INSERT INTO EFM_BAS_DATA_CLEAN_NUM ( date_measured, time_measured, value_reported, data_point_id ) "
            +" SELECT TO_DATE(?, 'DD/MM/YYYY HH24:MI:SS'), TO_DATE(?, 'DD/MM/YYYY HH24:MI:SS'),?,? FROM DUAL "
            +" WHERE not exists("
            +"     SELECT * FROM EFM_BAS_DATA_CLEAN_NUM  "
            +"     WHERE DATE_MEASURED=TO_DATE(?, 'DD/MM/YYYY HH24:MI:SS') "
            +"     AND TIME_MEASURED=TO_DATE(?, 'DD/MM/YYYY HH24:MI:SS') "
            +"     AND VALUE_REPORTED=? "
            +"     AND DATA_POINT_ID=? )";

最后是：

String query = "MERGE INTO EFM_BAS_DATA_CLEAN_NUM bd1 USING ("
                +"    SELECT TO_DATE(?, 'DD/MM/YYYY HH24:MI:SS') as DATE_MEASURED, "
                +"    TO_DATE(?, 'DD/MM/YYYY HH24:MI:SS') as TIME_MEASURED,"
                +"    ? as VALUE_REPORTED,"
                +"    ? as DATA_POINT_ID FROM DUAL "
                +" ) bd2 on (bd1.DATE_MEASURED=bd2.DATE_MEASURED AND"
                +"      bd1.TIME_MEASURED=bd2.TIME_MEASURED AND"
                +"      bd1.VALUE_REPORTED=bd2.VALUE_REPORTED AND"
                +"      bd1.DATA_POINT_ID=bd2.DATA_POINT_ID)"
                +" WHEN NOT MATCHED THEN "
                +"    INSERT (date_measured, time_measured, value_reported, data_point_id)  "
                +"    VALUES(bd2.DATE_MEASURED,bd2.TIME_MEASURED,bd2.VALUE_REPORTED,bd2.DATA_POINT_ID)";

但是，虽然在 AquaData Studio 中执行查询一直有效（或者更确切地说，何时是新记录，它被插入，当记录已经存在时，它没有插入，没有错误），在应用程序运行时，我仍然有同样的错误：

ORA-00001: 违反唯一约束 (AFM.UNIQUE_EFM_CLEAN_NUM)

也许我错了？谢谢！

标签： javasqlspringoracleprepared-statement

解决方案

您的代码的“不存在”版本应该可以工作。

我会仔细检查您是否正在设置？您的 java 代码中的值正确，以便您的插入值与您的“不存在”值相同。

我用我自己的表尝试了你的代码并且它有效。我使用 select 'X' 而不是 *，但这无关紧要。我的 sorydct_cert_key 是唯一的密钥。

private void testInsert() throws SQLException {

    String first = "8ADA";
    Integer second = 8;
    String third = "ADA Failed";
    String fourth = "EXC";
    String sql = "INSERT INTO SORYDCT(SORYDCT_CERT_KEY," +
            " SORYDCT_CERT_CODE," +
            " SORYDCT_CERT_DESC," +
            " SORYDCT_PROGRAM," +
            " SORYDCT_COUNT_CODE)" +
            " SELECT ?,?,?,?, NULL" +
            " FROM DUAL" +
            " WHERE NOT EXISTS ( SELECT 'X'" +
            " FROM SORYDCT" +
            " WHERE SORYDCT_CERT_KEY = ?)";
    PreparedStatement insertStatement = null;
    try {
        insertStatement = conn.prepareStatement(sql);
        insertStatement.setNString(1, first);
        insertStatement.setInt(2, second);
        insertStatement.setString(3, third);
        insertStatement.setString(4, fourth);
        insertStatement.setString(5, first);
        insertStatement.executeUpdate();
        conn.commit();
    } catch (SQLException e) {
        System.out.println(ERROR_STRING);
        System.out.println("Failure while inserting records - 1");
        onException(e);
    } finally {
        try {
            insertStatement.close();
        } catch (SQLException e) {
        }
    }
    first = "TEST";
    second = 0;
    third = "Test";
    fourth = "EXC";
    System.out.println(sql);
    insertStatement = null;
    try {
        insertStatement = conn.prepareStatement(sql);
        insertStatement.setNString(1, first);
        insertStatement.setInt(2, second);
        insertStatement.setString(3, third);
        insertStatement.setString(4, fourth);
        insertStatement.setString(5, first);
        insertStatement.executeUpdate();
        conn.commit();
    } catch (SQLException e) {
        System.out.println(ERROR_STRING);
        System.out.println("Failure while inserting records - 2 ");
        onException(e);
    } finally {
        try {
            insertStatement.close();
        } catch (SQLException e) {
        }
    }
}  }

推荐阅读