1
|
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
|
import vldc.aswi.domain.Configuration;
|
8
|
import vldc.aswi.domain.Function;
|
9
|
import vldc.aswi.domain.parameter.ParameterInConfiguration;
|
10
|
import vldc.aswi.model.table.NameUserName;
|
11
|
import vldc.aswi.model.table.TableColumn;
|
12
|
import vldc.aswi.model.table.ValueFunction;
|
13
|
import vldc.aswi.model.table.contingencyTable.ContingencyTableRow;
|
14
|
import vldc.aswi.utils.Converter;
|
15
|
|
16
|
import java.util.*;
|
17
|
import java.util.concurrent.TimeUnit;
|
18
|
|
19
|
/**
|
20
|
* Manager for SqlQuery.
|
21
|
*/
|
22
|
@Service
|
23
|
@Slf4j
|
24
|
public class SqlQueryManagerImpl implements SqlQueryManager {
|
25
|
|
26
|
/**
|
27
|
* Autowired databaseInterface component.
|
28
|
*/
|
29
|
@Autowired
|
30
|
private DatabaseInterface databaseInterface;
|
31
|
|
32
|
/**
|
33
|
* Get list of contingencyTableRow.
|
34
|
*
|
35
|
* @param configuration - configuration from which table will be created.
|
36
|
* @return List of contingencyTableRow.
|
37
|
*/
|
38
|
@Override
|
39
|
public List<ContingencyTableRow> getContingencyTableRow(Configuration configuration) {
|
40
|
long startTime = System.nanoTime();
|
41
|
|
42
|
Map<String, TableColumn> tableColumns = this.databaseInterface.executeQueryAndReturnTableColumns(
|
43
|
generateFullSQLQuery(configuration.getAssembly().getSQLQuery(), configuration.getParametersInConfiguration())
|
44
|
);
|
45
|
|
46
|
long stopTime = System.nanoTime();
|
47
|
System.out.println("Select:" + TimeUnit.MILLISECONDS.convert((stopTime - startTime), TimeUnit.NANOSECONDS));
|
48
|
List<NameUserName> rowNames = new ArrayList<>();
|
49
|
List<NameUserName> columnNames = new ArrayList<>();
|
50
|
List<ValueFunction> valueFunctions = new ArrayList<>();
|
51
|
|
52
|
// Create a copy of list.
|
53
|
List<ParameterInConfiguration> copiedParametersInConfiguration = new ArrayList<>(configuration.getParametersInConfiguration());
|
54
|
|
55
|
// sort parameters in configuration to have them in order set by user
|
56
|
Collections.sort(copiedParametersInConfiguration);
|
57
|
|
58
|
initializeContingencyTableDataLists(rowNames, columnNames, valueFunctions, configuration.getParametersInConfiguration());
|
59
|
|
60
|
Converter converter = new Converter(tableColumns, rowNames, columnNames, valueFunctions, true);
|
61
|
return converter.convertTableColumnsToContingencyTableRows();
|
62
|
}
|
63
|
|
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
|
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
|
}
|
82
|
|
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
|
}
|
99
|
}
|
100
|
|
101
|
if (valueFunctions.size() == 0) {
|
102
|
valueFunctions.add(new ValueFunction("", "", "", "Seznam"));
|
103
|
}
|
104
|
}
|
105
|
|
106
|
private String generateFullSQLQuery(String basicSQLQuery, List<ParameterInConfiguration> parametersInConfiguration) {
|
107
|
StringBuilder finalSQLQuery = new StringBuilder("SELECT * FROM (");
|
108
|
finalSQLQuery.append(basicSQLQuery);
|
109
|
finalSQLQuery.append(")");
|
110
|
|
111
|
boolean isWhereDefined = false;
|
112
|
|
113
|
for (ParameterInConfiguration parameterInConfiguration : parametersInConfiguration) {
|
114
|
if (parameterInConfiguration.getOperator() != null && !parameterInConfiguration.getOperator().getName().equals("zadny") &&
|
115
|
parameterInConfiguration.getOperatorValue() != null && !parameterInConfiguration.getOperatorValue().equals("")) {
|
116
|
String[] values = parameterInConfiguration.getOperatorValue().split(";");
|
117
|
if (!isWhereDefined) {
|
118
|
finalSQLQuery.append(" WHERE ");
|
119
|
isWhereDefined = true;
|
120
|
} else {
|
121
|
finalSQLQuery.append(" AND ");
|
122
|
}
|
123
|
|
124
|
finalSQLQuery.append(parameterInConfiguration.getParameter().getNameOfSelect());
|
125
|
finalSQLQuery.append(" ");
|
126
|
finalSQLQuery.append(parameterInConfiguration.getOperator().getName());
|
127
|
finalSQLQuery.append(" ");
|
128
|
switch (parameterInConfiguration.getOperator().getName()) {
|
129
|
case "BETWEEN":
|
130
|
case "NOT BETWEEN":
|
131
|
finalSQLQuery.append(betweenNotBetween(values, parameterInConfiguration.getParameter().getParameterType().getName()));
|
132
|
break;
|
133
|
case "IN":
|
134
|
case "NOT IN":
|
135
|
finalSQLQuery.append(inNotIn(values, parameterInConfiguration.getParameter().getParameterType().getName()));
|
136
|
break;
|
137
|
case "LIKE":
|
138
|
case "NOT LIKE":
|
139
|
finalSQLQuery.append(likeNotLike(values, parameterInConfiguration.getParameter().getParameterType().getName()));
|
140
|
break;
|
141
|
default:
|
142
|
finalSQLQuery.append(otherOperators(values, parameterInConfiguration.getParameter().getParameterType().getName()));
|
143
|
break;
|
144
|
}
|
145
|
|
146
|
}
|
147
|
}
|
148
|
|
149
|
return finalSQLQuery.toString();
|
150
|
}
|
151
|
|
152
|
private String likeNotLike(String[] values, String type) {
|
153
|
StringBuilder SQLQueryPart = new StringBuilder();
|
154
|
addQuotes(type, SQLQueryPart);
|
155
|
SQLQueryPart.append(values[0]);
|
156
|
addQuotes(type, SQLQueryPart);
|
157
|
|
158
|
return SQLQueryPart.toString();
|
159
|
}
|
160
|
|
161
|
private String inNotIn(String[] values, String type) {
|
162
|
StringBuilder SQLQueryPart = new StringBuilder();
|
163
|
SQLQueryPart.append("(");
|
164
|
for (int i = 0; i < values.length; i++) {
|
165
|
addQuotes(type, SQLQueryPart);
|
166
|
SQLQueryPart.append(values[i]);
|
167
|
addQuotes(type, SQLQueryPart);
|
168
|
if (i < values.length - 1) {
|
169
|
SQLQueryPart.append(",");
|
170
|
}
|
171
|
}
|
172
|
SQLQueryPart.append(")");
|
173
|
|
174
|
return SQLQueryPart.toString();
|
175
|
}
|
176
|
|
177
|
private String betweenNotBetween(String[] values, String type) {
|
178
|
StringBuilder SQLQueryPart = new StringBuilder();
|
179
|
|
180
|
addQuotes(type, SQLQueryPart);
|
181
|
SQLQueryPart.append(values[0]);
|
182
|
addQuotes(type, SQLQueryPart);
|
183
|
|
184
|
SQLQueryPart.append(" AND ");
|
185
|
|
186
|
addQuotes(type, SQLQueryPart);
|
187
|
SQLQueryPart.append(values[1]);
|
188
|
addQuotes(type, SQLQueryPart);
|
189
|
|
190
|
return SQLQueryPart.toString();
|
191
|
}
|
192
|
|
193
|
private String otherOperators(String[] values, String type) {
|
194
|
StringBuilder SQLQueryPart = new StringBuilder();
|
195
|
|
196
|
addQuotes(type, SQLQueryPart);
|
197
|
SQLQueryPart.append(values[0]);
|
198
|
addQuotes(type, SQLQueryPart);
|
199
|
|
200
|
return SQLQueryPart.toString();
|
201
|
}
|
202
|
|
203
|
private void addQuotes(String type, StringBuilder SQLQueryPart) {
|
204
|
if (type.equals("Text") || type.equals("Datum")) {
|
205
|
SQLQueryPart.append("'");
|
206
|
}
|
207
|
}
|
208
|
}
|