@Override
public AnswerList readDistinctValuesByCriteria(
String searchTerm, Map<String, List<String>> individualSearch, String columnName) {
AnswerList answer = new AnswerList();
MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));
List<String> distinctValues = new ArrayList<>();
StringBuilder searchSQL = new StringBuilder();
List<String> individalColumnSearchValues = new ArrayList<String>();
StringBuilder query = new StringBuilder();
query.append("SELECT distinct sol.");
query.append(columnName);
query.append(" as distinctValues FROM soaplibrary sol");
query.append(" where 1=1");
if (!StringUtil.isNullOrEmpty(searchTerm)) {
searchSQL.append(" and (sol.Name like ?");
searchSQL.append(" or sol.Type like ?");
searchSQL.append(" or sol.ServicePath like ?");
searchSQL.append(" or sol.Method like ?");
searchSQL.append(" or sol.ParsingAnswer like ?");
searchSQL.append(" or sol.Description like ?");
searchSQL.append(" or sol.Envelope like ?)");
}
if (individualSearch != null && !individualSearch.isEmpty()) {
searchSQL.append(" and ( 1=1 ");
for (Map.Entry<String, List<String>> entry : individualSearch.entrySet()) {
searchSQL.append(" and sol.");
searchSQL.append(
SqlUtil.getInSQLClauseForPreparedStatement(entry.getKey(), entry.getValue()));
individalColumnSearchValues.addAll(entry.getValue());
}
searchSQL.append(" )");
}
query.append(searchSQL);
query.append(" group by ifnull(sol.").append(columnName).append(",'')");
query.append(" order by sol.").append(columnName).append(" asc");
// Debug message on SQL.
if (LOG.isDebugEnabled()) {
LOG.debug("SQL : " + query.toString());
}
try (Connection connection = databaseSpring.connect();
PreparedStatement preStat = connection.prepareStatement(query.toString())) {
int i = 1;
if (!StringUtil.isNullOrEmpty(searchTerm)) {
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
}
for (String individualColumnSearchValue : individalColumnSearchValues) {
preStat.setString(i++, individualColumnSearchValue);
}
ResultSet resultSet = preStat.executeQuery();
// gets the data
while (resultSet.next()) {
distinctValues.add(
resultSet.getString("distinctValues") == null
? ""
: resultSet.getString("distinctValues"));
}
// get the total number of rows
resultSet = preStat.executeQuery("SELECT FOUND_ROWS()");
int nrTotalRows = 0;
if (resultSet != null && resultSet.next()) {
nrTotalRows = resultSet.getInt(1);
}
if (distinctValues.size()
>= MAX_ROW_SELECTED) { // Result of SQl was limited by MAX_ROW_SELECTED constrain. That
// means that we may miss some lines in the resultList.
LOG.error("Partial Result in the query.");
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_WARNING_PARTIAL_RESULT);
msg.setDescription(
msg.getDescription()
.replace("%DESCRIPTION%", "Maximum row reached : " + MAX_ROW_SELECTED));
answer = new AnswerList(distinctValues, nrTotalRows);
} else if (distinctValues.size() <= 0) {
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);
answer = new AnswerList(distinctValues, nrTotalRows);
} else {
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
msg.setDescription(
msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "SELECT"));
answer = new AnswerList(distinctValues, nrTotalRows);
}
} catch (Exception e) {
LOG.warn("Unable to execute query : " + e.toString());
msg =
new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED)
.resolveDescription("DESCRIPTION", e.toString());
} finally {
// We always set the result message
answer.setResultMessage(msg);
}
answer.setResultMessage(msg);
answer.setDataList(distinctValues);
return answer;
}
@Override
public AnswerList readByCriteria(
int start,
int amount,
String column,
String dir,
String searchTerm,
Map<String, List<String>> individualSearch) {
AnswerList response = new AnswerList();
MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));
List<SoapLibrary> objectList = new ArrayList<SoapLibrary>();
StringBuilder searchSQL = new StringBuilder();
List<String> individalColumnSearchValues = new ArrayList<String>();
StringBuilder query = new StringBuilder();
// SQL_CALC_FOUND_ROWS allows to retrieve the total number of columns by disrearding the limit
// clauses that
// were applied -- used for pagination p
query.append("SELECT SQL_CALC_FOUND_ROWS * FROM soaplibrary sol ");
query.append(" WHERE 1=1");
if (!StringUtil.isNullOrEmpty(searchTerm)) {
searchSQL.append(" and (sol.Name like ?");
searchSQL.append(" or sol.Type like ?");
searchSQL.append(" or sol.ServicePath like ?");
searchSQL.append(" or sol.Method like ?");
searchSQL.append(" or sol.ParsingAnswer like ?");
searchSQL.append(" or sol.Description like ?");
searchSQL.append(" or sol.Envelope like ?)");
}
if (individualSearch != null && !individualSearch.isEmpty()) {
searchSQL.append(" and ( 1=1 ");
for (Map.Entry<String, List<String>> entry : individualSearch.entrySet()) {
searchSQL.append(" and ");
String key = "IFNULL(sol." + entry.getKey() + ",'')";
String q = SqlUtil.getInSQLClauseForPreparedStatement(key, entry.getValue());
if (q == null || q == "") {
q = "(sol." + entry.getKey() + " IS NULL OR " + entry.getKey() + " = '')";
}
searchSQL.append(q);
individalColumnSearchValues.addAll(entry.getValue());
}
searchSQL.append(" )");
}
query.append(searchSQL);
if (!StringUtil.isNullOrEmpty(column)) {
query.append(" order by sol.").append(column).append(" ").append(dir);
}
if ((amount <= 0) || (amount >= MAX_ROW_SELECTED)) {
query.append(" limit ").append(start).append(" , ").append(MAX_ROW_SELECTED);
} else {
query.append(" limit ").append(start).append(" , ").append(amount);
}
// Debug message on SQL.
if (LOG.isDebugEnabled()) {
LOG.debug("SQL : " + query.toString());
}
Connection connection = this.databaseSpring.connect();
try {
PreparedStatement preStat = connection.prepareStatement(query.toString());
try {
int i = 1;
if (!StringUtil.isNullOrEmpty(searchTerm)) {
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
}
for (String individualColumnSearchValue : individalColumnSearchValues) {
preStat.setString(i++, individualColumnSearchValue);
}
ResultSet resultSet = preStat.executeQuery();
try {
// gets the data
while (resultSet.next()) {
objectList.add(this.loadFromResultSet(resultSet));
}
// get the total number of rows
resultSet = preStat.executeQuery("SELECT FOUND_ROWS()");
int nrTotalRows = 0;
if (resultSet != null && resultSet.next()) {
nrTotalRows = resultSet.getInt(1);
}
if (objectList.size()
>= MAX_ROW_SELECTED) { // Result of SQl was limited by MAX_ROW_SELECTED constrain.
// That means that we may miss some lines in the resultList.
LOG.error("Partial Result in the query.");
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_WARNING_PARTIAL_RESULT);
msg.setDescription(
msg.getDescription()
.replace("%DESCRIPTION%", "Maximum row reached : " + MAX_ROW_SELECTED));
response = new AnswerList(objectList, nrTotalRows);
} else if (objectList.size() <= 0) {
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);
response = new AnswerList(objectList, nrTotalRows);
} else {
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
msg.setDescription(
msg.getDescription()
.replace("%ITEM%", OBJECT_NAME)
.replace("%OPERATION%", "SELECT"));
response = new AnswerList(objectList, nrTotalRows);
}
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
} finally {
if (resultSet != null) {
resultSet.close();
}
}
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
} finally {
if (preStat != null) {
preStat.close();
}
}
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
} finally {
try {
if (!this.databaseSpring.isOnTransaction()) {
if (connection != null) {
connection.close();
}
}
} catch (SQLException exception) {
LOG.warn("Unable to close connection : " + exception.toString());
}
}
response.setResultMessage(msg);
response.setDataList(objectList);
return response;
}