package com.megatim.fdxcommons.tools.database.queries;
|
|
import com.megatim.fdxcommons.model.enumeration.FonctionAggregation;
|
import com.megatim.fdxcommons.tools.database.queries.metadata.QueryCriterion;
|
import com.megatim.fdxcommons.tools.database.contrat.DefaultPreparedStatementProcessor;
|
import com.megatim.fdxcommons.tools.database.contrat.QueryMetaData;
|
import com.megatim.fdxcommons.tools.database.queries.metadata.WhereQueryParameters;
|
import java.sql.Connection;
|
import java.sql.PreparedStatement;
|
import java.sql.ResultSet;
|
import java.util.ArrayList;
|
import java.util.List;
|
import java.util.stream.IntStream;
|
|
/**
|
*
|
* @author Gabuntu
|
*/
|
public class AggregationQuery implements QueryMetaData<List<String[]>> {
|
|
private final String tableName;
|
private final Connection connection;
|
private final QueryCriterion queryCriterion;
|
private final String targetColumn;
|
private final FonctionAggregation fonctionAggregation;
|
private final List<String> groupingColumns;
|
|
public AggregationQuery(String tableName, Connection connection, QueryCriterion queryCriterion, String targetColumn, List<String> groupingColumns, FonctionAggregation fonctionAggregation) {
|
this.tableName = tableName;
|
this.targetColumn = targetColumn;
|
this.connection = connection;
|
this.queryCriterion = queryCriterion;
|
this.groupingColumns = groupingColumns;
|
this.fonctionAggregation = fonctionAggregation;
|
}
|
|
@Override
|
public List<String[]> execute() throws Exception {
|
|
System.out.println("<------------------------------------------------------------------------------------------------------------------------------->");
|
System.out.println(aggregationQuery());
|
System.out.println("<------------------------------------------------------------------------------------------------------------------------------->");
|
|
try (PreparedStatement statement = connection.prepareStatement(aggregationQuery())) {
|
|
new DefaultPreparedStatementProcessor().process(statement, new WhereQueryParameters(queryCriterion).parameters());
|
|
try ( ResultSet resultSet = statement.executeQuery()) {
|
|
List<String[]> results = new ArrayList<>();
|
|
while (resultSet.next()) {
|
|
String[] result = new String[groupingColumns.size() + 1];
|
result[0] = resultSet.getString(aggregationColumnName());
|
for (int i = 0; i < groupingColumns.size(); i++) {
|
String r = resultSet.getString(groupingColumns.get(i));
|
result[i + 1] = r != null ? r : "";
|
}
|
|
results.add(result);
|
}
|
|
return results;
|
|
}
|
}
|
}
|
|
private String aggregationQuery() {
|
StringBuilder query = new StringBuilder("SELECT COALESCE(" + fonctionAggregation.toString() + "(" + targetColumn + "), 0) AS " + aggregationColumnName()
|
+ groupingColumnsSelection() + " FROM ").append(tableName);
|
query.append(new WhereQueryString(queryCriterion).query());
|
query.append(new GroupByQuery(groupingColumns).query());
|
return query.toString();
|
}
|
|
private String aggregationColumnName() {
|
return tableName + "_" + fonctionAggregation.toString();
|
}
|
|
private String groupingColumnsSelection() {
|
StringBuilder query = new StringBuilder();
|
|
int size = groupingColumns.size();
|
if (size > 0) {
|
query.append(", ");
|
}
|
IntStream.range(0, size).forEach(index -> {
|
String column = groupingColumns.get(index);
|
query.append(column);
|
if ((index + 1) < size) {
|
query.append(",");
|
}
|
});
|
|
return query.toString();
|
}
|
}
|
