1 package net.sumaris.core.extraction.dao.technical.schema;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25 import com.google.common.base.Joiner;
26 import com.google.common.base.Preconditions;
27 import net.sumaris.core.dao.technical.SortDirection;
28 import net.sumaris.core.dao.technical.schema.SumarisColumnMetadata;
29 import net.sumaris.core.dao.technical.schema.SumarisTableMetadata;
30 import net.sumaris.core.exception.SumarisTechnicalException;
31 import net.sumaris.core.extraction.vo.ExtractionFilterCriterionVO;
32 import net.sumaris.core.extraction.vo.ExtractionFilterOperatorEnum;
33 import net.sumaris.core.extraction.vo.ExtractionFilterVO;
34 import org.apache.commons.collections4.CollectionUtils;
35 import org.apache.commons.lang3.ArrayUtils;
36 import org.apache.commons.lang3.StringUtils;
37
38 import javax.annotation.Nullable;
39 import java.sql.Types;
40 import java.util.Collection;
41 import java.util.stream.Collectors;
42
43
44
45
46
47
48 public class SumarisTableMetadatas {
49
50 public static String getSqlWhereClause(SumarisTableMetadata table, ExtractionFilterVO filter) {
51
52 String whereContent = getSqlWhereClauseContent(table, filter);
53
54
55 if (StringUtils.isBlank(whereContent) || "1=1".equals(whereContent)) return "";
56 return " WHERE " + whereContent;
57 }
58
59 public static String getSqlWhereClauseContent(SumarisTableMetadata table, ExtractionFilterVO filter) {
60 return getSqlWhereClauseContent(table, filter, filter != null ? filter.getSheetName() : null);
61 }
62
63 public static String getSqlWhereClauseContent(SumarisTableMetadata table, ExtractionFilterVO filter, String sheetName) {
64 return getSqlWhereClauseContent(table, filter, sheetName, table != null ? table.getAlias() : null);
65 }
66
67 public static String getSqlWhereClauseContent(SumarisTableMetadata table, ExtractionFilterVO filter, String sheetName, String tableAlias) {
68
69 if (filter == null || CollectionUtils.isEmpty(filter.getCriteria())) return "";
70
71 StringBuilder sql = new StringBuilder();
72
73 StringBuilder logicalOperator = new StringBuilder();
74 String aliasWithPoint = tableAlias != null ? (tableAlias + ".") : "";
75
76 filter.getCriteria().stream()
77 .filter(criterion -> sheetName == null || sheetName.equals(criterion.getSheetName()))
78 .forEach(criterion -> {
79
80
81 Preconditions.checkNotNull(criterion.getName());
82 SumarisColumnMetadata column = table != null ? table.getColumnMetadata(criterion.getName().toLowerCase()) : null;
83 if (column == null) {
84 if (sheetName != null) {
85 throw new SumarisTechnicalException(String.format("Invalid criterion: column '%s' not found", criterion.getName()));
86 } else {
87
88 }
89 } else {
90 sql.append(logicalOperator.toString())
91 .append(aliasWithPoint)
92 .append(column.getName());
93
94
95 if (logicalOperator.length() == 0) {
96 if ("OR".equalsIgnoreCase(StringUtils.trim(filter.getOperator()))) {
97 logicalOperator.append(" OR ");
98 } else {
99 logicalOperator.append(" AND ");
100 }
101 }
102
103 ExtractionFilterOperatorEnum operator = criterion.getOperator() == null ? ExtractionFilterOperatorEnum.EQUALS : ExtractionFilterOperatorEnum.fromSymbol(criterion.getOperator());
104
105 if (criterion.getValue() == null && ArrayUtils.isEmpty(criterion.getValues())) {
106 switch (operator) {
107 case NOT_IN:
108 case NOT_EQUALS:
109 sql.append(" IS NOT NULL");
110 break;
111 default:
112 sql.append(" IS NULL");
113 }
114 } else {
115 switch (operator) {
116 case IN:
117 sql.append(String.format(" IN (%s)", getInValues(column, criterion)));
118 break;
119 case NOT_IN:
120 sql.append(String.format(" NOT IN (%s)", getInValues(column, criterion)));
121 break;
122 case EQUALS:
123 sql.append(String.format(" = %s", getSingleValue(column, criterion)));
124 break;
125 case NOT_EQUALS:
126 sql.append(String.format(" <> %s", getSingleValue(column, criterion)));
127 break;
128 case LESS_THAN:
129 sql.append(String.format(" < %s", getSingleValue(column, criterion)));
130 break;
131 case LESS_THAN_OR_EQUALS:
132 sql.append(String.format(" <= %s", getSingleValue(column, criterion)));
133 break;
134 case GREATER_THAN:
135 sql.append(String.format(" > %s", getSingleValue(column, criterion)));
136 break;
137 case GREATER_THAN_OR_EQUALS:
138 sql.append(String.format(" >= %s", getSingleValue(column, criterion)));
139 break;
140 case BETWEEN:
141 sql.append(String.format(" BETWEEN %s AND %s", getBetweenValueByIndex(column, criterion, 0), getBetweenValueByIndex(column, criterion, 1)));
142 break;
143 }
144 }
145 }
146 });
147
148 return sql.toString();
149 }
150
151 public static String getSingleValue(SumarisColumnMetadata column, ExtractionFilterCriterionVO criterion) {
152 return isNumericColumn(column) ? criterion.getValue() : ("'" + criterion.getValue() + "'");
153 }
154
155 public static String getInValues(SumarisColumnMetadata column, ExtractionFilterCriterionVO criterion) {
156 if (ArrayUtils.isEmpty(criterion.getValues())) {
157 if (criterion.getValue() != null) {
158 return getSingleValue(column, criterion);
159 }
160 Preconditions.checkArgument(false, "Invalid criterion: 'values' is required for operator 'IN' or 'NOT IN'");
161 }
162 return isNumericColumn(column) ?
163 Joiner.on(',').join(criterion.getValues()) :
164 "'" + Joiner.on("','").skipNulls().join(criterion.getValues()) + "'";
165 }
166
167 public static String getBetweenValueByIndex(SumarisColumnMetadata column, ExtractionFilterCriterionVO criterion, int index) {
168 Preconditions.checkNotNull(criterion.getValues(), "Invalid criterion: 'values' is required for operator 'BETWEEN'");
169 Preconditions.checkArgument(criterion.getValues().length == 2, "Invalid criterion: 'values' array must have 2 values, for operator 'BETWEEN'");
170 Preconditions.checkArgument(index == 0 || index == 1);
171 String value = criterion.getValues()[index];
172 return isNumericColumn(column) ? value : ("'" + value + "'");
173 }
174
175
176 public static boolean isNumericColumn(SumarisColumnMetadata column) {
177 return column.getTypeCode() == Types.NUMERIC
178 || column.getTypeCode() == Types.INTEGER
179 || column.getTypeCode() == Types.DOUBLE
180 || column.getTypeCode() == Types.BIGINT
181 || column.getTypeCode() == Types.DECIMAL
182 || column.getTypeCode() == Types.FLOAT;
183 }
184
185 public static boolean isNotNumericColumn(SumarisColumnMetadata column) {
186 return !isNumericColumn(column);
187 }
188
189 public static String getAliasedColumnName(@Nullable String alias, String columnName) {
190 return StringUtils.isNotBlank(alias) ? alias + "." + columnName : columnName;
191 }
192
193 public static String getAliasedTableName(@Nullable String alias, String tableName) {
194 return StringUtils.isNotBlank(alias) ? tableName + " AS " + alias : tableName;
195 }
196
197 public static String getSelectGroupByQuery(String tableName,
198 Collection<String> columnNames,
199 String whereClause,
200 Collection<String> groupByColumnNames,
201 Collection<String> sortColumnNames,
202 SortDirection direction) {
203 StringBuilder sb = new StringBuilder();
204 sb.append("SELECT ")
205 .append(Joiner.on(',').join(columnNames))
206 .append(" FROM ")
207 .append(tableName);
208
209
210 if (StringUtils.isNotBlank(whereClause)) {
211 if (!whereClause.trim().startsWith("WHERE")) {
212 sb.append(" WHERE ").append(whereClause.trim());
213 }
214 else {
215 sb.append(" ").append(whereClause.trim());
216 }
217 }
218
219
220 if (org.apache.commons.collections.CollectionUtils.isNotEmpty(groupByColumnNames)) {
221 sb.append(" GROUP BY ").append(Joiner.on(',').join(groupByColumnNames));
222 }
223
224 if (org.apache.commons.collections.CollectionUtils.isNotEmpty(sortColumnNames)) {
225 String directionStr = direction != null ? (" " + direction.name()) : "";
226 sb.append(" ORDER BY ")
227 .append(Joiner.on(directionStr + ",").join(sortColumnNames))
228 .append(directionStr);
229 }
230
231 return sb.toString();
232 }
233
234 public static Collection<String> getAliasedColumns(String tableAlias,
235 Collection<String> columnNames) {
236 if (StringUtils.isBlank(tableAlias)) return columnNames;
237 return columnNames.stream().map(c -> tableAlias + "." + c).collect(Collectors.toList());
238 }
239 }