Oracle Java Prepared Statement Insert if not exists

Question

i have a problem with Java PreparedStatement and Oracle.

In short I want to create a Batch Insert using Java in my Oracle DB. I try to do it with this code:

       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){ ...

However when some record already exist in the table, I've this error:

ORA-00001: unique constraint (AFM.UNIQUE_EFM_CLEAN_NUM) violated

cause I've a constraint on this fields.

So, looking on line, I've find many solution.

I tried with this query:

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";

or with:

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=? )";

and finally with:

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)";

But while the execution of query in AquaData Studio ever work (or rather when is a new record, it is inserted and when record already exists, it sn't inserted, without errors), on app running, I still have the same error:

ORA-00001: unique constraint (AFM.UNIQUE_EFM_CLEAN_NUM) violated

maybe I'm wrong? Thanks!

Answer 1

The 'where not exists' version of your code should have worked.

I would double check that you are setting the ? values in your java code correctly, so that your insert values are the same as your 'where not exists' values.

I tried your code with my own tables and it worked. I used select 'X' instead of *, but that shouldn't matter. My sorydct_cert_key is a unique 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) {
        }
    }
}  }