CODEWITHSUNDEEP
javadatabaseoracle-databasejdbc
jwa
jwa

Reputation: 3281

Batching "UPDATE vs. INSERT" Queries Against Oracle Database

Let's assume that I have an Oracle database with a table called RUN_LOG I am using to record when jobs have been executed.

The table has a primary key JOB_NAME which uniquely identifies the job that has been executed, and a column called LAST_RUN_TIMESTAMP which reflects when the job was last executed.

When an job starts, I would like to update the existing row for a job (if it exists), or otherwise insert a new row into the table.

Given Oracle does not support a REPLACE INTO-style query, it is necessary to try an UPDATE, and if zero rows are affected follow this up with an INSERT.

This is typically achieved with jdbc using something like the following:

PreparedStatement updateStatement = connection.prepareStatement("UPDATE ...");
PreparedStatement insertStatement = connection.prepareStatement("INSERT ...");

updateStatement.setString(1, "JobName");
updateStatement.setTimestamp(2, timestamp);

// If there are no rows to update, it must be a new job...
if (updateStatement.executeUpdate() == 0) {
    // Follow-up 
    insertStatement.setString(1, "JobName");
    insertStatement.setTimestamp(2, timestamp);
    insertStatement.executeUpdate();
}

This is a fairly well-trodden path, and I am very comfortable with this approach.

However, let's assume my use-case requires me to insert a very large number of these records. Performing individual SQL queries against the database would be far too "chatty". Instead, I would like to start batching these INSERT / UPDATE queries

Given the execution of the UPDATE queries will be deferred until the batch is committed, I cannot observe how many rows are affected until a later date.

What is the best mechanism for achieving this REPLACE INTO-like result?

I'd rather avoid using a stored procedure, as I'd prefer to keep my persistence logic in this one place (class), rather than distributing it between the Java code and the database.

Upvotes: 1

Views: 79

Answers (1)

Michal Vitousek
Michal Vitousek

Reputation: 76

What about the SQL MERGE statement. You can insert large number of records to temporary table, then merge temp table with RUN_LOG For example:

merge into RUN_LOG tgt using (
   select job_name, timestamp 
   from my_new_temp_table 
) src 
on (src.job_name = tgt.job_name) 
when matched then update set
   tgt.timestamp = src.timestamp
when not matched then insert values (src.job_name, src.timestamp)
;

Upvotes: 5

Related Questions