Oracle Java 准备语句插入(如果不存在)

Question

我在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( 违反

也许我错了？ 谢谢！

Answer 1

代码的"不存在"版本应该可以工作。

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

我用自己的表尝试了您的代码，它奏效了。我使用选择"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) {
}
}
}  }