1 |
417c1106
|
Vojtech Danisik
|
package vldc.aswi.service;
|
2 |
|
|
|
3 |
|
|
import lombok.extern.slf4j.Slf4j;
|
4 |
|
|
import org.springframework.beans.factory.annotation.Autowired;
|
5 |
|
|
import org.springframework.stereotype.Service;
|
6 |
|
|
import vldc.aswi.database.DatabaseInterface;
|
7 |
cc39413d
|
mlinha
|
import vldc.aswi.domain.Configuration;
|
8 |
|
|
import vldc.aswi.domain.Function;
|
9 |
|
|
import vldc.aswi.domain.parameter.ParameterInConfiguration;
|
10 |
efcabc4d
|
mlinha
|
import vldc.aswi.model.table.NameUserName;
|
11 |
417c1106
|
Vojtech Danisik
|
import vldc.aswi.model.table.TableColumn;
|
12 |
efcabc4d
|
mlinha
|
import vldc.aswi.model.table.ValueFunction;
|
13 |
417c1106
|
Vojtech Danisik
|
import vldc.aswi.model.table.contingencyTable.ContingencyTableRow;
|
14 |
|
|
import vldc.aswi.utils.Converter;
|
15 |
|
|
|
16 |
cc39413d
|
mlinha
|
import java.util.*;
|
17 |
|
|
import java.util.concurrent.TimeUnit;
|
18 |
417c1106
|
Vojtech Danisik
|
|
19 |
ba8b306a
|
Vojtěch Danišík
|
/**
|
20 |
|
|
* Manager for SqlQuery.
|
21 |
|
|
*/
|
22 |
417c1106
|
Vojtech Danisik
|
@Service
|
23 |
|
|
@Slf4j
|
24 |
f30ebb74
|
mlinha
|
public class SqlQueryManagerImpl implements SqlQueryManager {
|
25 |
417c1106
|
Vojtech Danisik
|
|
26 |
f30ebb74
|
mlinha
|
/**
|
27 |
|
|
* Autowired databaseInterface component.
|
28 |
|
|
*/
|
29 |
417c1106
|
Vojtech Danisik
|
@Autowired
|
30 |
|
|
private DatabaseInterface databaseInterface;
|
31 |
f30ebb74
|
mlinha
|
|
32 |
ba8b306a
|
Vojtěch Danišík
|
/**
|
33 |
|
|
* Get list of contingencyTableRow.
|
34 |
f30ebb74
|
mlinha
|
*
|
35 |
|
|
* @param configuration - configuration from which table will be created.
|
36 |
ba8b306a
|
Vojtěch Danišík
|
* @return List of contingencyTableRow.
|
37 |
|
|
*/
|
38 |
417c1106
|
Vojtech Danisik
|
@Override
|
39 |
f30ebb74
|
mlinha
|
public List<ContingencyTableRow> getContingencyTableRow(Configuration configuration) {
|
40 |
cc39413d
|
mlinha
|
long startTime = System.nanoTime();
|
41 |
f30ebb74
|
mlinha
|
|
42 |
|
|
Map<String, TableColumn> tableColumns = this.databaseInterface.executeQueryAndReturnTableColumns(
|
43 |
|
|
generateFullSQLQuery(configuration.getAssembly().getSQLQuery(), configuration.getParametersInConfiguration())
|
44 |
|
|
);
|
45 |
|
|
|
46 |
cc39413d
|
mlinha
|
long stopTime = System.nanoTime();
|
47 |
|
|
System.out.println("Select:" + TimeUnit.MILLISECONDS.convert((stopTime - startTime), TimeUnit.NANOSECONDS));
|
48 |
efcabc4d
|
mlinha
|
List<NameUserName> rowNames = new ArrayList<>();
|
49 |
|
|
List<NameUserName> columnNames = new ArrayList<>();
|
50 |
|
|
List<ValueFunction> valueFunctions = new ArrayList<>();
|
51 |
cc39413d
|
mlinha
|
|
52 |
26c4caa0
|
Vojtěch Danišík
|
// Create a copy of list.
|
53 |
f30ebb74
|
mlinha
|
List<ParameterInConfiguration> copiedParametersInConfiguration = new ArrayList<>(configuration.getParametersInConfiguration());
|
54 |
26c4caa0
|
Vojtěch Danišík
|
|
55 |
cc39413d
|
mlinha
|
// sort parameters in configuration to have them in order set by user
|
56 |
26c4caa0
|
Vojtěch Danišík
|
Collections.sort(copiedParametersInConfiguration);
|
57 |
cc39413d
|
mlinha
|
|
58 |
851ab2cf
|
mlinha
|
initializeContingencyTableDataLists(rowNames, columnNames, valueFunctions, copiedParametersInConfiguration);
|
59 |
cc39413d
|
mlinha
|
|
60 |
f30ebb74
|
mlinha
|
Converter converter = new Converter(tableColumns, rowNames, columnNames, valueFunctions, true);
|
61 |
|
|
return converter.convertTableColumnsToContingencyTableRows();
|
62 |
417c1106
|
Vojtech Danisik
|
}
|
63 |
ba8b306a
|
Vojtěch Danišík
|
|
64 |
|
|
/**
|
65 |
|
|
* Validate given SQL query.
|
66 |
|
|
* @param sqlQuery - Validated SQL query.
|
67 |
|
|
* @return true if SQL query is ok, false if not.
|
68 |
|
|
*/
|
69 |
|
|
@Override
|
70 |
|
|
public boolean validateSQLQuery(String sqlQuery) {
|
71 |
7638be72
|
Vojtěch Danišík
|
return this.databaseInterface.validateSQLQuery(sqlQuery);
|
72 |
|
|
}
|
73 |
|
|
|
74 |
|
|
/**
|
75 |
|
|
* Get list of table column names from SQL query.
|
76 |
|
|
* @param sqlQuery - SQL query.
|
77 |
|
|
* @return List of table column names.
|
78 |
|
|
*/
|
79 |
|
|
public List<String> getNameOfColumnsFromQuery(String sqlQuery) {
|
80 |
|
|
return this.databaseInterface.getNameOfColumnsFromQuery(sqlQuery);
|
81 |
ba8b306a
|
Vojtěch Danišík
|
}
|
82 |
f30ebb74
|
mlinha
|
|
83 |
|
|
private void initializeContingencyTableDataLists(List<NameUserName> rowNames, List<NameUserName> columnNames,
|
84 |
|
|
List<ValueFunction> valueFunctions, List<ParameterInConfiguration> parametersInConfiguration) {
|
85 |
|
|
for (ParameterInConfiguration parameterInConfiguration : parametersInConfiguration) {
|
86 |
|
|
if (parameterInConfiguration.getLocation() != null && parameterInConfiguration.getLocation().getName().equals("Řádek")) {
|
87 |
|
|
rowNames.add(new NameUserName(parameterInConfiguration.getParameter().getNameOfSelect().toUpperCase(),
|
88 |
|
|
parameterInConfiguration.getColumnName()));
|
89 |
|
|
} else if (parameterInConfiguration.getLocation() != null && parameterInConfiguration.getLocation().getName().equals("Sloupec")) {
|
90 |
|
|
columnNames.add(new NameUserName(parameterInConfiguration.getParameter().getNameOfSelect().toUpperCase(),
|
91 |
|
|
parameterInConfiguration.getColumnName()));
|
92 |
|
|
} else if (parameterInConfiguration.getLocation() != null && parameterInConfiguration.getLocation().getName().equals("Hodnota")) {
|
93 |
|
|
for (Function function : parameterInConfiguration.getFunctions()) {
|
94 |
|
|
valueFunctions.add(new ValueFunction(parameterInConfiguration.getParameter().getNameOfSelect().toUpperCase(),
|
95 |
|
|
parameterInConfiguration.getParameter().getName(), function.getName().toUpperCase(),
|
96 |
|
|
parameterInConfiguration.getParameter().getParameterType().getName()));
|
97 |
|
|
}
|
98 |
55515be9
|
mlinha
|
if (valueFunctions.size() == 0) {
|
99 |
|
|
valueFunctions.add(new ValueFunction(parameterInConfiguration.getParameter().getNameOfSelect().toUpperCase(),
|
100 |
|
|
parameterInConfiguration.getParameter().getName(), "",
|
101 |
|
|
parameterInConfiguration.getParameter().getParameterType().getName()));
|
102 |
|
|
}
|
103 |
f30ebb74
|
mlinha
|
}
|
104 |
|
|
}
|
105 |
2da8e5b7
|
mlinha
|
|
106 |
|
|
if (valueFunctions.size() == 0) {
|
107 |
55515be9
|
mlinha
|
valueFunctions.add(new ValueFunction("", "nic", "NIC", ""));
|
108 |
2da8e5b7
|
mlinha
|
}
|
109 |
f30ebb74
|
mlinha
|
}
|
110 |
|
|
|
111 |
|
|
private String generateFullSQLQuery(String basicSQLQuery, List<ParameterInConfiguration> parametersInConfiguration) {
|
112 |
|
|
StringBuilder finalSQLQuery = new StringBuilder("SELECT * FROM (");
|
113 |
|
|
finalSQLQuery.append(basicSQLQuery);
|
114 |
|
|
finalSQLQuery.append(")");
|
115 |
|
|
|
116 |
|
|
boolean isWhereDefined = false;
|
117 |
|
|
|
118 |
|
|
for (ParameterInConfiguration parameterInConfiguration : parametersInConfiguration) {
|
119 |
3309eb3e
|
mlinha
|
if (parameterInConfiguration.getParameter().getParameterType().getName().equals("Výčet") && false) {
|
120 |
|
|
isWhereDefined = processListing(parameterInConfiguration, finalSQLQuery, isWhereDefined);
|
121 |
|
|
}
|
122 |
|
|
else if (parameterInConfiguration.getOperator() != null && !parameterInConfiguration.getOperator().getName().equals("zadny") &&
|
123 |
f30ebb74
|
mlinha
|
parameterInConfiguration.getOperatorValue() != null && !parameterInConfiguration.getOperatorValue().equals("")) {
|
124 |
3309eb3e
|
mlinha
|
isWhereDefined = processFilter(parameterInConfiguration, finalSQLQuery, isWhereDefined);
|
125 |
f30ebb74
|
mlinha
|
}
|
126 |
|
|
}
|
127 |
|
|
|
128 |
|
|
return finalSQLQuery.toString();
|
129 |
|
|
}
|
130 |
|
|
|
131 |
3309eb3e
|
mlinha
|
private boolean processListing(ParameterInConfiguration parameterInConfiguration, StringBuilder finalSQLQuery, boolean isWhereDefined) {
|
132 |
|
|
if (!isWhereDefined) {
|
133 |
|
|
finalSQLQuery.append(" WHERE ");
|
134 |
|
|
} else {
|
135 |
|
|
finalSQLQuery.append(" AND ");
|
136 |
|
|
}
|
137 |
|
|
|
138 |
|
|
finalSQLQuery.append(parameterInConfiguration.getParameter().getNameOfSelect());
|
139 |
|
|
finalSQLQuery.append(" IN ");
|
140 |
|
|
inNotIn(null, "Text");
|
141 |
|
|
|
142 |
|
|
return true;
|
143 |
|
|
}
|
144 |
|
|
|
145 |
|
|
private boolean processFilter(ParameterInConfiguration parameterInConfiguration, StringBuilder finalSQLQuery, boolean isWhereDefined) {
|
146 |
|
|
String[] values = parameterInConfiguration.getOperatorValue().split(";");
|
147 |
|
|
if (!isWhereDefined) {
|
148 |
|
|
finalSQLQuery.append(" WHERE ");
|
149 |
|
|
} else {
|
150 |
|
|
finalSQLQuery.append(" AND ");
|
151 |
|
|
}
|
152 |
|
|
|
153 |
|
|
finalSQLQuery.append(parameterInConfiguration.getParameter().getNameOfSelect());
|
154 |
|
|
finalSQLQuery.append(" ");
|
155 |
|
|
finalSQLQuery.append(parameterInConfiguration.getOperator().getName());
|
156 |
|
|
if (parameterInConfiguration.getOperator().getName().equals("IS NULL") ||
|
157 |
|
|
parameterInConfiguration.getOperator().getName().equals("IS NOT NULL")) {
|
158 |
|
|
return true;
|
159 |
|
|
}
|
160 |
|
|
finalSQLQuery.append(" ");
|
161 |
|
|
switch (parameterInConfiguration.getOperator().getName()) {
|
162 |
|
|
case "BETWEEN":
|
163 |
|
|
case "NOT BETWEEN":
|
164 |
|
|
finalSQLQuery.append(betweenNotBetween(values, parameterInConfiguration.getParameter().getParameterType().getName()));
|
165 |
|
|
break;
|
166 |
|
|
case "IN":
|
167 |
|
|
case "NOT IN":
|
168 |
|
|
finalSQLQuery.append(inNotIn(values, parameterInConfiguration.getParameter().getParameterType().getName()));
|
169 |
|
|
break;
|
170 |
|
|
case "LIKE":
|
171 |
|
|
case "NOT LIKE":
|
172 |
|
|
finalSQLQuery.append(likeNotLike(values, parameterInConfiguration.getParameter().getParameterType().getName()));
|
173 |
|
|
break;
|
174 |
|
|
default:
|
175 |
|
|
finalSQLQuery.append(otherOperators(values, parameterInConfiguration.getParameter().getParameterType().getName()));
|
176 |
|
|
break;
|
177 |
|
|
}
|
178 |
|
|
|
179 |
|
|
return true;
|
180 |
|
|
}
|
181 |
|
|
|
182 |
f30ebb74
|
mlinha
|
private String likeNotLike(String[] values, String type) {
|
183 |
|
|
StringBuilder SQLQueryPart = new StringBuilder();
|
184 |
|
|
addQuotes(type, SQLQueryPart);
|
185 |
|
|
SQLQueryPart.append(values[0]);
|
186 |
|
|
addQuotes(type, SQLQueryPart);
|
187 |
|
|
|
188 |
|
|
return SQLQueryPart.toString();
|
189 |
|
|
}
|
190 |
|
|
|
191 |
|
|
private String inNotIn(String[] values, String type) {
|
192 |
|
|
StringBuilder SQLQueryPart = new StringBuilder();
|
193 |
|
|
SQLQueryPart.append("(");
|
194 |
|
|
for (int i = 0; i < values.length; i++) {
|
195 |
|
|
addQuotes(type, SQLQueryPart);
|
196 |
|
|
SQLQueryPart.append(values[i]);
|
197 |
|
|
addQuotes(type, SQLQueryPart);
|
198 |
|
|
if (i < values.length - 1) {
|
199 |
|
|
SQLQueryPart.append(",");
|
200 |
|
|
}
|
201 |
|
|
}
|
202 |
|
|
SQLQueryPart.append(")");
|
203 |
|
|
|
204 |
|
|
return SQLQueryPart.toString();
|
205 |
|
|
}
|
206 |
|
|
|
207 |
|
|
private String betweenNotBetween(String[] values, String type) {
|
208 |
|
|
StringBuilder SQLQueryPart = new StringBuilder();
|
209 |
|
|
|
210 |
|
|
addQuotes(type, SQLQueryPart);
|
211 |
|
|
SQLQueryPart.append(values[0]);
|
212 |
|
|
addQuotes(type, SQLQueryPart);
|
213 |
|
|
|
214 |
|
|
SQLQueryPart.append(" AND ");
|
215 |
|
|
|
216 |
|
|
addQuotes(type, SQLQueryPart);
|
217 |
|
|
SQLQueryPart.append(values[1]);
|
218 |
|
|
addQuotes(type, SQLQueryPart);
|
219 |
|
|
|
220 |
|
|
return SQLQueryPart.toString();
|
221 |
|
|
}
|
222 |
|
|
|
223 |
|
|
private String otherOperators(String[] values, String type) {
|
224 |
|
|
StringBuilder SQLQueryPart = new StringBuilder();
|
225 |
|
|
|
226 |
|
|
addQuotes(type, SQLQueryPart);
|
227 |
|
|
SQLQueryPart.append(values[0]);
|
228 |
|
|
addQuotes(type, SQLQueryPart);
|
229 |
|
|
|
230 |
|
|
return SQLQueryPart.toString();
|
231 |
|
|
}
|
232 |
|
|
|
233 |
|
|
private void addQuotes(String type, StringBuilder SQLQueryPart) {
|
234 |
|
|
if (type.equals("Text") || type.equals("Datum")) {
|
235 |
|
|
SQLQueryPart.append("'");
|
236 |
|
|
}
|
237 |
|
|
}
|
238 |
417c1106
|
Vojtech Danisik
|
}
|