I have a prepared statement that I wish to run until an insert or update replacing a NULL value occurs. The prepared statement is:

String insertQryCAD = ("INSERT INTO at_cub_award_date "  
    "(ca_id, ad_id, cad_task_completion_date) "  
    "VALUES (?, ?, ?) "  
    "ON DUPLICATE KEY UPDATE cad_task_completion_date = IF(at_cub_award_date IS NULL, ?, STR_TO_DATE(cad_task_completion_date, '%Y-%m-%d'));");

I then want to run something like:

ps = c.prepareStatement(insertQryCAD);
int i = 0;
while (i < 4) {
  ps.setString(1, caId);
  ps.setString(2, adId);
  ps.setString(3, attendance);
  ps.setString(4, attendance);
  ps.executeUpdate();
  i  ;
}

How do I stop the while loop when an (insert) or (update replacing a NULL value) occurs?

Example 1:

|Row|Val 1| Val 2| Val 3|Action|
________________________________
| 1 |  1  |  5   |  A   | skip  
________________________________
| 2 |  1  |  6   |  B   | skip  
________________________________
| 3 |  1  |  7   | null | update and stop  
________________________________
| 4 |  1  |  8   |   C  |  
________________________________

Example 2:

|Row|Val 1| Val 2| Val 3|Action|
________________________________
| 1 |  1  |  5   |  A   | skip  
________________________________
| 2 |  1  |  6   |  B   | skip  
________________________________
No row so Insert and stop  
________________________________

I tried:

int returnValue = ps.executeUpdate(); 
if (returnValue == 1) { 
    i = 7; 
}else{
    i  ;
}

The trouble is this always updates in the "ON DUPLICATE KEY UPDATE".

CodePudding user response：

There won't be a way to distinguish the number of inserts from the number updates via the result of an executeUpdate. It returns the number of rows affected. That will be 1 row affected in both the insert and update cases.

So if you want to distinguish the insert and update cases, you will need to write the SQL another way. For example:

• You could do the following in Java

  • start a transaction
  • perform the INSERT without the ON DUPLICATE KEY part
  • catch a duplicate key exception and perform the UPDATE
  • commit the transaction

  keeping track of which path you took in Java code.

• You could call an SQL stored procedure that does the same thing all on the server side.

Note that ON DUPLICATE KEY appears to be a MySQL-ism.