Optimized Java SQL Insert or Update Statements

Published: November 20, 2016

---

private static final String INSERT_UPDATE_TABLE = "INSERT INTO "
  + TableName.TABLE_NAME
  + " (" + TableName.LINE_ID
  + " ," + TableName.PERIOD
  + " ," + TableName.CALLS
  + " ," + TableName.ANSWERED
  + " ," + TableName.UNANSWERED
  + " ," + TableName.UNIQUE
  + " ," + TableName.CUSTOMER_ID + ") VALUES "
  + "({0},{1},{2},{3},{4},{5},{6}) ON DUPLICATE KEY UPDATE "
  + TableName.CALLS + "=" + TableName.CALLS + "+{2}, "
  + TableName.ANSWERED + "=" + TableName.ANSWERED + "+{3}, "
  + TableName.UNANSWERED + "=" + TableName.UNANSWERED + "+{4} ";



and then

Object[] args = {"'"+lineNumber+"'",period+"",(ansCalls + unAnsCalls)+"",ansCalls+"",unAnsCalls+"",0};

linesMeteredLineCalls.add(MessageFormat.format(INSERT_UPDATE_TABLE, args));