@Override
public String getUpdateCommandString(
String tabName, List<SqlField> updateItems, SqlCondition... p) throws Exception {
if (updateItems == null || updateItems.size() == 0) return "";
StringBuilder sbCMD = new StringBuilder(400);
StringBuilder sbFV = new StringBuilder(400);
sbCMD.append("UPDATE ");
sbCMD.append(tabName.replaceAll("([\\[]|[\\]])+", ""));
sbCMD.append(" SET ");
for (SqlField item : updateItems) {
if (item != null && !StringUtil.isNullOrEmpty(item.getFieldName())) {
if (!this.getIgnoreSecurity() && !isSecurityField(item)) continue;
if (item.getFieldValue() == null) {
sbFV.append(String.format("%s=NULL,", item.getFieldName()));
} else {
if (item.getSqlParamType() == SqlParamType.String) {
sbFV.append(
String.format(
"%s='%s',", item.getFieldName(), item.getFieldValue().replace("'", "''")));
} else if (item.getSqlParamType() == SqlParamType.Text) {
sbFV.append(String.format("%s=?,", item.getFieldName()));
} else if (item.getSqlParamType() == SqlParamType.DateTime) {
sbFV.append(
String.format(
"%s=%s,",
item.getFieldName(),
this.formatDateTimeString(DateUtil.parseDate(item.getFieldValue()))));
} else if (item.getSqlParamType() == SqlParamType.Numeric) {
sbFV.append(
String.format(
"%s=%s,", item.getFieldName(), item.getFieldValue().replace("'", "")));
} else {
sbFV.append(String.format("%s=%s,", item.getFieldName(), item.getFieldValue()));
}
}
}
}
sbCMD.append(sbFV.deleteCharAt(sbFV.length() - 1).toString());
sbCMD.append(" ");
String strCondition = parseSqlCondition(p);
if (!this.getIgnoreSecurity() && StringUtil.isNullOrEmpty(strCondition))
throw new Exception("SqlCommand Security Warnning.");
sbCMD.append(strCondition);
return sbCMD.toString();
}
@Override
public String getInsertCommandString(String tabName, List<SqlField> insertItems) {
if (insertItems == null || insertItems.size() == 0) return "";
StringBuilder sbCMD = new StringBuilder(200);
StringBuilder sbField = new StringBuilder(200);
StringBuilder sbValue = new StringBuilder(200);
for (SqlField item : insertItems) {
if (item != null && !StringUtil.isNullOrEmpty(item.getFieldName())) {
if (!this.getIgnoreSecurity() && !isSecurityField(item)) continue;
sbField.append(item.getFieldName());
sbField.append(",");
if (item.getFieldValue() == null) {
sbValue.append("NULL,");
} else {
if (item.getSqlParamType() == SqlParamType.Numeric) {
sbValue.append(item.getFieldValue().replace("'", ""));
sbValue.append(",");
} else if (item.getSqlParamType() == SqlParamType.String) {
sbValue.append("'");
sbValue.append(item.getFieldValue().replace("'", "''"));
sbValue.append("',");
} else if (item.getSqlParamType() == SqlParamType.Text) {
sbValue.append("?,");
} else if (item.getSqlParamType() == SqlParamType.DateTime) {
sbValue.append(this.formatDateTimeString(DateUtil.parseDate(item.getFieldValue())));
sbValue.append(",");
} else {
sbValue.append(item.getFieldValue());
sbValue.append(",");
}
}
}
}
sbCMD.append("INSERT INTO ");
sbCMD.append(tabName.replaceAll("([\\[]|[\\]])+", ""));
sbCMD.append(" (");
sbCMD.append(sbField.deleteCharAt(sbField.length() - 1).toString());
sbCMD.append(") VALUES (");
sbCMD.append(sbValue.deleteCharAt(sbValue.length() - 1).toString());
sbCMD.append(")");
return sbCMD.toString();
}
@Override
public String formatDateTimeString(Date datetime) {
return "to_date(\'"
+ DateUtil.formatDateString(datetime, "yyyy-MM-dd HH:mm:ss")
+ "\','YYYY-MM-DD HH24:MI:SS')";
}