private StringBuffer createMasterSelectQueryBlock( EntitySearchFilter[] filters, boolean selectAll) { String masterTableName = this.getEntityMasterTableName(); StringBuffer query = new StringBuffer("SELECT ").append(masterTableName).append("."); if (selectAll) { query.append("* "); } else { query.append(this.getEntityMasterTableIdFieldName()); } if (this.isForceTextCaseSearch() && filters != null) { String searchTableName = this.getEntitySearchTableName(); for (int i = 0; i < filters.length; i++) { EntitySearchFilter filter = filters[i]; if (!filter.isAttributeFilter() && filter.isLikeOption()) { String tableFieldName = this.getTableFieldName(filter.getKey()); // check for id column already present if (!tableFieldName.equals(this.getMasterTableIdFieldName())) { query.append(", ").append(masterTableName).append(".").append(tableFieldName); } } else if (filter.isAttributeFilter() && filter.isLikeOption()) { String columnName = this.getAttributeFieldColunm(filter); query.append(", ").append(searchTableName).append(i).append(".").append(columnName); query.append(" AS ").append(columnName).append(i).append(" "); } } } query.append(" FROM ").append(masterTableName).append(" "); return query; }
protected boolean verifyLikeFieldFilters(ResultSet result, EntitySearchFilter[] likeFieldFilters) throws SQLException { boolean verify = true; for (int i = 0; i < likeFieldFilters.length; i++) { EntitySearchFilter filter = likeFieldFilters[i]; if (filter.getKey() == null || !filter.isLikeOption() || !this.isForceTextCaseSearch()) { continue; } String fieldName = null; if (filter.isAttributeFilter()) { fieldName = this.getAttributeFieldColunm(filter) + i; } else { fieldName = this.getTableFieldName(filter.getKey()); } String value = result.getString(fieldName); if (null != filter.getValue()) { verify = this.checkText((String) filter.getValue(), value); if (!verify) { break; } } else if (filter.getAllowedValues() != null && filter.getAllowedValues().size() > 0) { List<Object> allowedValues = filter.getAllowedValues(); verify = this.verifyLikeAllowedValuesFilter(value, allowedValues); if (!verify) { break; } } } return verify; }
private boolean appendValuedAttributeFilterQueryBlocks( EntitySearchFilter filter, int index, StringBuffer query, boolean hasAppendWhereClause) { String searchTableNameAlias = this.getEntitySearchTableName() + index; hasAppendWhereClause = this.verifyWhereClauseAppend(query, hasAppendWhereClause); query.append(searchTableNameAlias).append(".attrname = ? "); hasAppendWhereClause = this.addAttributeLangQueryBlock(searchTableNameAlias, query, filter, hasAppendWhereClause); if (filter.isLikeOption() && this.isForceTextCaseSearch()) { return hasAppendWhereClause; } if (filter.getAllowedValues() != null && filter.getAllowedValues().size() > 0) { hasAppendWhereClause = this.verifyWhereClauseAppend(query, hasAppendWhereClause); List<Object> allowedValues = filter.getAllowedValues(); for (int j = 0; j < allowedValues.size(); j++) { Object allowedValue = allowedValues.get(j); if (j == 0) { query.append(" ( "); } else { query.append(" OR "); } String operator = filter.isLikeOption() ? this.getLikeClause() : "= ? "; query .append(searchTableNameAlias) .append(".") .append(this.getAttributeFieldColunm(allowedValue)) .append(" "); query.append(operator); if (j == (allowedValues.size() - 1)) { query.append(" ) "); } } } else if (filter.getValue() != null) { Object object = filter.getValue(); String operator = filter.isLikeOption() ? this.getLikeClause() : "= ? "; hasAppendWhereClause = this.addAttributeObjectSearchQueryBlock( searchTableNameAlias, query, object, operator, hasAppendWhereClause, filter.getLangCode()); } else { // creazione blocco selezione su tabella ricerca if (null != filter.getStart()) { hasAppendWhereClause = this.addAttributeObjectSearchQueryBlock( searchTableNameAlias, query, filter.getStart(), ">= ? ", hasAppendWhereClause, filter.getLangCode()); } if (null != filter.getEnd()) { hasAppendWhereClause = this.addAttributeObjectSearchQueryBlock( searchTableNameAlias, query, filter.getEnd(), "<= ? ", hasAppendWhereClause, filter.getLangCode()); } if (null == filter.getStart() && null == filter.getEnd()) { hasAppendWhereClause = this.verifyWhereClauseAppend(query, hasAppendWhereClause); query .append(" (") .append(searchTableNameAlias) .append(".datevalue IS NOT NULL OR ") .append(searchTableNameAlias) .append(".textvalue IS NOT NULL OR ") .append(searchTableNameAlias) .append(".numvalue IS NOT NULL) "); } } return hasAppendWhereClause; }