Example optimization of implementing existing Java business logic into SQL update statements. The example is of code that moves an 'item' to the correct next 'step' based on if the item is associated to a 'click' of a 'link'.

code.java

Step process(Item item, LinkStep step, DataAccess access) {
  
  boolean result = false;
  
  if (step.getLink() == null) {
    // item associated with any link being clicked
    result = access.userClickedOnAnyLink();
    
  } else {
    // item associated with a specific link being clicked
    result = access.userClickedOnLink(step.getLink());  
  }
  
  // did the item meet the conditions for the step?
  // return the next step the item should advance to in the workflow
  if (result) {
    return step.getSuccessStep();
  } else {
    return step.getFailStep();
  }
}

code.sql

-- any link clicked
-- success logic
update item 
  join step on item.step_id = step.step_id
  join link_click on link_click.item_id = item.item_id
  set item.step_id = step.success_step_id
  where step.step_id = ?;

-- fail logic
update item
  join step on item.step_id = step.step_id
  left join link_click on link_click.item_id = item.item_id
  set item.step_id = step.fail_step_id
  where step.step_id = ? and link_click.link_click_id is null;

-- specific link clicked
-- success logic
update item 
  join step on item.step_id = step.step_id
  join link_click on link_click.item_id = item.item_id and step.link_id = link_click.link_id
  set item.step_id = step.success_step_id
  where step.step_id = ?;

-- fail logic
update item
  join step on item.step_id = step.step_id
  left join link_click on link_click.item_id = item.item_id and step.link_id = link_click.link_id
  set item.step_id = step.fail_step_id
  where step.step_id = ? and link_click.link_click_id is null;