IDataStore dataStore;
JDBCDataSet dataSet;
JSONDataWriter dataSetWriter;
Query query;
IStatement statement;
Integer resultNumber;
JSONObject gridDataFeed = new JSONObject();
Monitor totalTimeMonitor = null;
Monitor errorHitsMonitor;
logger.debug("IN");
try {
super.service(request, response);
totalTimeMonitor = MonitorFactory.start("QbeEngine.executeQueryAction.totalTime");
start = getAttributeAsInteger( START );
logger.debug("Parameter [" + START + "] is equals to [" + start + "]");
limit = getAttributeAsInteger( LIMIT );
logger.debug("Parameter [" + LIMIT + "] is equals to [" + limit + "]");
groupFields = getAttributeAsJSONArray( GROUPBY_FIELDS );
logger.debug("Parameter [" + GROUPBY_FIELDS + "] is equals to [" + groupFields + "]");
Assert.assertNotNull(groupFields, "Parameter [" + GROUPBY_FIELDS + "] cannot be null");
Assert.assertTrue(groupFields.length() > 0, "GroupBy fileds list cannot be empty");
maxSize = QbeEngineConfig.getInstance().getResultLimit();
logger.debug("Configuration setting [" + "QBE.QBE-SQL-RESULT-LIMIT.value" + "] is equals to [" + (maxSize != null? maxSize: "none") + "]");
isMaxResultsLimitBlocking = QbeEngineConfig.getInstance().isMaxResultLimitBlocking();
logger.debug("Configuration setting [" + "QBE.QBE-SQL-RESULT-LIMIT.isBlocking" + "] is equals to [" + isMaxResultsLimitBlocking + "]");
Assert.assertNotNull(getEngineInstance(), "It's not possible to execute " + this.getActionName() + " service before having properly created an instance of EngineInstance class");
// STEP 1: modify the query according to the input that come from the form
query = getEngineInstance().getQueryCatalogue().getFirstQuery();
// ... query transformation goes here
logger.debug("Making a deep copy of the original query...");
String store = ((JSONObject)SerializerFactory.getSerializer("application/json").serialize(query, getEngineInstance().getDataSource(), getLocale())).toString();
Query copy = SerializerFactory.getDeserializer("application/json").deserializeQuery(store, getEngineInstance().getDataSource());
logger.debug("Deep copy of the original query produced");
String jsonEncodedFormState = getAttributeAsString( FORM_STATE );
logger.debug("Form state retrieved as a string: " + jsonEncodedFormState);
JSONObject formState = new JSONObject(jsonEncodedFormState);
logger.debug("Form state converted into a valid JSONObject: " + formState.toString(3));
JSONObject template = (JSONObject) getEngineInstance().getFormState().getConf();
logger.debug("Form viewer template retrieved.");
FormViewerQueryTransformer formViewerQueryTransformer = new FormViewerQueryTransformer();
formViewerQueryTransformer.setFormState(formState);
formViewerQueryTransformer.setTemplate(template);
logger.debug("Applying Form Viewer query transformation...");
query = formViewerQueryTransformer.execTransformation(copy);
logger.debug("Applying Form Viewer query transformation...");
updatePromptableFiltersValue(query);
getEngineInstance().setActiveQuery(query);
// STEP 2: prepare statment and obtain the corresponding sql query
statement = getEngineInstance().getStatment();
statement.setParameters( getEnv() );
//String jpaQueryStr = statement.getQueryString();
String sqlQuery = statement.getSqlQueryString();
UserProfile userProfile = (UserProfile)getEnv().get(EngineConstants.ENV_USER_PROFILE);
// STEP 3: transform the sql query
GroupByQueryTransformer transformer = new GroupByQueryTransformer();
List selectFields = SqlUtils.getSelectFields(sqlQuery);
for(int i = 0; i < groupFields.length(); i++) {
String groupByField = groupFields.getString(i);
int fieldIndex = query.getSelectFieldIndex(groupByField);
Assert.assertTrue(fieldIndex >= 0 && fieldIndex <selectFields.size(), "Impossible to group on field [" + groupByField + "]");
String[] f = (String[])selectFields.get(fieldIndex);
transformer.addGrouByColumn(f[1]!=null? f[1]:f[0], query.getSelectFieldByIndex(fieldIndex).getAlias());
}
// count column
transformer.addAggregateColumn("*"/*f[1]!=null? f[1]:f[0]*/, "COUNT", "Records");
// aggregate measures
List dataMartSelectFields = query.getDataMartSelectFields(true);
Iterator it = dataMartSelectFields.iterator();
while (it.hasNext()) {
DataMartSelectField field = (DataMartSelectField) it.next();
int fieldIndex = query.getSelectFieldIndex(field.getUniqueName());
String[] f = (String[])selectFields.get(fieldIndex);
IAggregationFunction aggregationFunction = field.getFunction();
if (aggregationFunction != null && aggregationFunction != AggregationFunctions.NONE_FUNCTION) {
transformer.addAggregateColumn(f[1]!=null? f[1]:f[0], aggregationFunction.getName(), field.getAlias());
}
}
sqlQuery = (String)transformer.transformQuery(sqlQuery);
// STEP 4: execute the query
try {
logger.debug("Executing query: [" + sqlQuery + "]");
auditlogger.info("[" + userProfile.getUserId() + "]:: SQL: " + sqlQuery);
dataSet = new JDBCDataSet();
ConnectionDescriptor connection = (ConnectionDescriptor)getDataSource().getConfiguration().loadDataSourceProperties().get("connection");
DataSource dataSource = new DataSource();
dataSource.setJndi(connection.getJndiName());
dataSource.setHibDialectName(connection.getDialect());
dataSource.setUrlConnection(connection.getUrl());
dataSource.setDriver(connection.getDriverClass());
dataSource.setUser(connection.getUsername());
dataSource.setPwd(connection.getPassword());
dataSet.setDataSource(dataSource);
dataSet.setQuery(sqlQuery);
dataSet.loadData(start, limit, -1);
dataStore = dataSet.getDataStore();
} catch (Exception e) {
logger.debug("Query execution aborted because of an internal exceptian");
SpagoBIEngineServiceException exception;
String message;
message = "An error occurred in " + getActionName() + " service while executing query: [" + statement.getQueryString() + "]";
exception = new SpagoBIEngineServiceException(getActionName(), message, e);
exception.addHint("Check if the query is properly formed: [" + statement.getQueryString() + "]");
exception.addHint("Check connection configuration");
exception.addHint("Check the qbe jar file");
throw exception;
}