SQL AddBatch and ExecuteBatch Example in Scheduled Task

Using addBatch and executeBatch methods of a preparedStatement can increase performance. While it might be possible to simply execute the SQL to update all the records in one statement, should that take longer than the scheduled task allows, it would timeout. Therefore we need to iterate over a result set updating records and calling the isInterrupted method to insure that task does not time out. Also calling commit every once in a while prevents the SQL has not committed error.

private void updateTeamSupervisorDates(DBConnection conn, Map<String, Object> parameters)

throws Exception
{

  PreparedStatement psUpdate = null;

  Date domesticStartDate = getDateParameter(PARAM_DOMESTIC_START_DATE, parameters);
  Date intlStartDate = getDateParameter(PARAM_INTERNATIONAL_START_DATE, parameters);

  if (domesticStartDate == null || intlStartDate == null) {
    return;
  }

  if (logger.isDebugEnabled()) {
    logger.debug("domesticStartDate=" + domesticStartDate.toString());
  logger.debug("intlStartDate=" + intlStartDate.toString());
}

PreparedStatement ps = null;

ResultSet rs = null;

try {

String domesticDate = conn.encodeDate(domesticStartDate);
String intlDate = conn.encodeDate(intlStartDate);
StringBuilder b = new StringBuilder();

b.append("select WBUT_ID, WBG_FLAG5, ");
b.append("case WBG_FLAG5 ");
b.append("when 'I' then ").append(intlDate).append(" ");
b.append("when 'D' then ").append(domesticDate).append(" ");
b.append("else null end as NEW_SUPR_DATE ");
b.append("from workbrain_user_team wut with (nolock) ");
b.append("join workbrain_user wu with (nolock) on wut.WBU_ID = wu.WBU_ID ");
b.append("join WORKBRAIN_GROUP wg with (nolock) on wu.WBG_ID = wg.WBG_ID ");
b.append("where (WBG_FLAG5 = 'I' ");
b.append("and WBUT_START_DATE <> ").append(intlDate).append(" ");
b.append("and WBUT_END_DATE >= ").append(intlDate).append(") ");
b.append("or (WBG_FLAG5 = 'D' ");
b.append("and WBUT_START_DATE <> ").append(domesticDate).append(" ");
b.append("and WBUT_END_DATE >= ").append(domesticDate).append(") ");
b.append("and wut.WBU_ID > 3 ");

  if (logger.isDebugEnabled()) {
    logger.debug("SQL: " + b.toString());
  }

ps = conn.prepareStatement(b.toString());
psUpdate = conn.prepareStatement("UPDATE workbrain_user_team set WBUT_START_DATE = ?       WHERE WBUT_ID = ?");

int batchCount = 0;
rs = ps.executeQuery();

while(rs.next() && !isInterrupted()) {
  long wbutId = rs.getLong(1);
  Timestamp newStartDate = rs.getTimestamp(3);

  if`(newStartDate != null) {
    psUpdate.setTimestamp(1, newStartDate);
    psUpdate.setLong(2, wbutId);
    psUpdate.addBatch();

    if (!isInterrupted() && ((++batchCount) >= 100)) {
      psUpdate.executeBatch();
      conn.commit();
      batchCount = 0;
    }
  }
}

if (!isInterrupted() && (batchCount > 0)) {
  psUpdate.executeBatch();
  batchCount = 0;
  conn.commit();
}

  } finally {
    SQLUtil.cleanUp(ps, rs);
    SQLUtil.cleanUp(psUpdate);
  }
}