Examples with Parameter org.cerberus.crud.entity.Parameter used on opensource projects

Example 11 with Parameter

use of org.cerberus.crud.entity.Parameter in project cerberus-source by cerberustesting.

the class GetParameter method doPost.

@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    String echo = request.getParameter("sEcho");
    String mySystem = request.getParameter("system");
    LOG.debug("System : '" + mySystem + "'.");
    // data that will be shown in the table
    JSONArray data = new JSONArray();
    ApplicationContext appContext = WebApplicationContextUtils.getWebApplicationContext(this.getServletContext());
    IParameterService parameterService = appContext.getBean(ParameterService.class);
    try {
        JSONObject jsonResponse = new JSONObject();
        try {
            for (Parameter myParameter : parameterService.findAllParameter()) {
                JSONArray row = new JSONArray();
                row.put(myParameter.getParam()).put(myParameter.getValue()).put(myParameter.getValue()).put(myParameter.getDescription());
                data.put(row);
            }
        } catch (CerberusException ex) {
            response.setContentType("text/html");
            response.getWriter().print(ex.getMessageError().getDescription());
        }
        jsonResponse.put("aaData", data);
        jsonResponse.put("sEcho", echo);
        jsonResponse.put("iTotalRecords", data.length());
        jsonResponse.put("iTotalDisplayRecords", data.length());
        response.setContentType("application/json");
        response.getWriter().print(jsonResponse.toString());
    } catch (JSONException e) {
        LOG.warn(e);
        response.setContentType("text/html");
        response.getWriter().print(e.getMessage());
    }
}

Example 12 with Parameter

use of org.cerberus.crud.entity.Parameter in project cerberus-source by cerberustesting.

the class DataLibService method getDataObjectList.

/**
 * Get the dataObject List depending on the type
 *
 * @param lib
 * @param columnList
 * @return
 */
private AnswerList<HashMap<String, String>> getDataObjectList(TestDataLib lib, HashMap<String, String> columnList, int rowLimit, TestCaseExecution tCExecution, TestCaseExecutionData testCaseExecutionData) {
    AnswerList result = new AnswerList();
    MessageEvent msg = new MessageEvent(MessageEventEnum.PROPERTY_SUCCESS);
    CountryEnvironmentDatabase countryEnvironmentDatabase;
    AnswerList responseList;
    String system = tCExecution.getApplicationObj().getSystem();
    String country = tCExecution.getCountry();
    String environment = tCExecution.getEnvironment();
    Pattern pattern;
    Matcher matcher;
    Parameter p;
    List<HashMap<String, String>> list;
    switch(lib.getType()) {
        case TestDataLib.TYPE_CSV:
            /**
             * Before making the call we check if the Service Path is
             * already a proper URL. If it is not, we prefix with the CsvUrl
             * defined from corresponding database. This is used to get the
             * data from the correct environment.
             */
            String servicePathCsv = lib.getCsvUrl();
            LOG.debug("Service Path (Csv) : " + lib.getCsvUrl());
            // Trying making an URL with database context path.
            if (!StringUtil.isURL(servicePathCsv)) {
                // Url is not valid, we try to get the corresponding DatabaseURL CsvURL to prefix.
                if (!(StringUtil.isNullOrEmpty(lib.getDatabaseCsv()))) {
                    try {
                        countryEnvironmentDatabase = countryEnvironmentDatabaseService.convert(this.countryEnvironmentDatabaseService.readByKey(system, country, environment, lib.getDatabaseCsv()));
                        if (countryEnvironmentDatabase == null) {
                            msg = new MessageEvent(MessageEventEnum.PROPERTY_FAILED_GETFROMDATALIB_CSV_URLKOANDDATABASECSVURLNOTEXIST);
                            msg.setDescription(msg.getDescription().replace("%SERVICEURL%", lib.getCsvUrl()).replace("%SYSTEM%", system).replace("%COUNTRY%", country).replace("%ENV%", environment).replace("%DATABASE%", lib.getDatabaseCsv()));
                            result.setResultMessage(msg);
                            return result;
                        } else {
                            String csvURL = countryEnvironmentDatabase.getCsvUrl();
                            if (StringUtil.isNullOrEmpty(csvURL)) {
                                msg = new MessageEvent(MessageEventEnum.PROPERTY_FAILED_GETFROMDATALIB_CSV_URLKOANDDATABASECSVURLEMPTY);
                                msg.setDescription(msg.getDescription().replace("%SERVICEURL%", lib.getCsvUrl()).replace("%SYSTEM%", system).replace("%COUNTRY%", country).replace("%ENV%", environment).replace("%DATABASE%", lib.getDatabaseCsv()));
                                result.setResultMessage(msg);
                                return result;
                            }
                            // soapURL from database is not empty so we prefix the Service URL with it.
                            servicePathCsv = csvURL + lib.getCsvUrl();
                            if (!StringUtil.isURL(servicePathCsv)) {
                                msg = new MessageEvent(MessageEventEnum.PROPERTY_FAILED_GETFROMDATALIB_CSV_URLKO);
                                msg.setDescription(msg.getDescription().replace("%SERVICEURL%", servicePathCsv).replace("%SOAPURL%", csvURL).replace("%SERVICEPATH%", lib.getCsvUrl()).replace("%ENTRY%", lib.getName()).replace("%ENTRYID%", lib.getTestDataLibID().toString()));
                                result.setResultMessage(msg);
                                return result;
                            }
                        }
                    } catch (CerberusException ex) {
                        msg = new MessageEvent(MessageEventEnum.PROPERTY_FAILED_GETFROMDATALIB_CSV_URLKOANDDATABASECSVURLNOTEXIST);
                        msg.setDescription(msg.getDescription().replace("%SERVICEURL%", lib.getCsvUrl()).replace("%SYSTEM%", system).replace("%COUNTRY%", country).replace("%ENV%", environment).replace("%DATABASE%", lib.getDatabaseCsv()));
                        result.setResultMessage(msg);
                        return result;
                    }
                }
            }
            // Trying make a valid path with csv parameter path.
            if (!StringUtil.isURL(servicePathCsv)) {
                // Url is still not valid. We try to add the path from csv parameter.
                String csv_path = parameterService.getParameterStringByKey("cerberus_testdatalibcsv_path", "", "");
                csv_path = StringUtil.addSuffixIfNotAlready(csv_path, File.separator);
                servicePathCsv = csv_path + servicePathCsv;
            }
            // CSV Call is made here.
            responseList = fileService.parseCSVFile(servicePathCsv, lib.getSeparator(), columnList);
            list = responseList.getDataList();
            // if the query returns sucess then we can get the data
            if (responseList.getResultMessage().getCode() == MessageEventEnum.PROPERTY_SUCCESS_CSV.getCode()) {
                if (list != null && !list.isEmpty()) {
                    result.setDataList(list);
                    msg = new MessageEvent(MessageEventEnum.PROPERTY_SUCCESS_GETFROMDATALIB_CSV);
                    msg.setDescription(msg.getDescription().replace("%NBROW%", String.valueOf(result.getDataList().size())).replace("%CSVURL%", servicePathCsv));
                } else {
                    msg = new MessageEvent(MessageEventEnum.PROPERTY_FAILED_GETFROMDATALIB_CSVDATABASENODATA);
                    msg.setDescription(msg.getDescription().replace("%CSVURL%", servicePathCsv));
                }
            } else {
                msg = responseList.getResultMessage();
            }
            result.setResultMessage(msg);
            break;
        case TestDataLib.TYPE_SQL:
            String connectionName;
            String db = lib.getDatabase();
            try {
                if (StringUtil.isNullOrEmpty(db)) {
                    msg = new MessageEvent(MessageEventEnum.PROPERTY_FAILED_GETFROMDATALIB_SQLDATABASEEMPTY);
                } else {
                    countryEnvironmentDatabase = countryEnvironmentDatabaseService.convert(countryEnvironmentDatabaseService.readByKey(system, country, environment, db));
                    if (countryEnvironmentDatabase == null) {
                        msg = new MessageEvent(MessageEventEnum.PROPERTY_FAILED_GETFROMDATALIB_SQLDATABASENOTCONFIGURED);
                        msg.setDescription(msg.getDescription().replace("%SYSTEM%", system).replace("%COUNTRY%", country).replace("%ENV%", environment).replace("%DATABASE%", db));
                    } else {
                        connectionName = countryEnvironmentDatabase.getConnectionPoolName();
                        if (!(StringUtil.isNullOrEmpty(connectionName))) {
                            Integer sqlTimeout = parameterService.getParameterIntegerByKey("cerberus_propertyexternalsql_timeout", system, 60);
                            // performs a query that returns several rows containing n columns
                            responseList = sqlService.queryDatabaseNColumns(connectionName, lib.getScript(), rowLimit, sqlTimeout, system, columnList);
                            // if the query returns sucess then we can get the data
                            if (responseList.getResultMessage().getCode() == MessageEventEnum.PROPERTY_SUCCESS_SQL.getCode()) {
                                list = responseList.getDataList();
                                if (list != null && !list.isEmpty()) {
                                    result.setDataList(list);
                                    msg = new MessageEvent(MessageEventEnum.PROPERTY_SUCCESS_GETFROMDATALIB_SQL);
                                    msg.setDescription(msg.getDescription().replace("%NBROW%", String.valueOf(result.getDataList().size())));
                                } else {
                                    msg = new MessageEvent(MessageEventEnum.PROPERTY_FAILED_GETFROMDATALIB_SQLDATABASENODATA);
                                }
                            } else {
                                msg = responseList.getResultMessage();
                            }
                            msg.setDescription(msg.getDescription().replace("%DATABASE%", db));
                            msg.setDescription(msg.getDescription().replace("%SQL%", lib.getScript()));
                            msg.setDescription(msg.getDescription().replace("%JDBCPOOLNAME%", connectionName));
                        } else {
                            msg = new MessageEvent(MessageEventEnum.PROPERTY_FAILED_GETFROMDATALIB_SQLDATABASEJDBCRESSOURCEMPTY);
                            msg.setDescription(msg.getDescription().replace("%SYSTEM%", system).replace("%COUNTRY%", country).replace("%ENV%", environment).replace("%DATABASE%", db));
                        }
                    }
                }
            } catch (CerberusException ex) {
                msg = new MessageEvent(MessageEventEnum.PROPERTY_FAILED_GETFROMDATALIB_SQLDATABASENOTCONFIGURED);
                msg.setDescription(msg.getDescription().replace("%SYSTEM%", system).replace("%COUNTRY%", country).replace("%ENV%", environment).replace("%DATABASE%", db));
            }
            result.setResultMessage(msg);
            break;
        case TestDataLib.TYPE_SERVICE:
            AppService appService = new AppService();
            HashMap<String, String> resultHash = new HashMap<>();
            List<HashMap<String, String>> listResult = new ArrayList<HashMap<String, String>>();
            // Temporary list of string.
            List<String> listTemp1 = null;
            // String containing the XML
            String responseString = "";
            /**
             * Before making the call we check if the Service Path is
             * already a propper URL. If it is not, we prefix with the
             * SoapUrl defined from corresponding database. This is used to
             * get the data from the correct environment.
             */
            String servicePath = lib.getServicePath();
            LOG.debug("Service Path : " + lib.getServicePath());
            // Service Call is made here.
            AnswerItem ai = serviceService.callService(lib.getService(), lib.getDatabaseUrl(), lib.getEnvelope(), lib.getServicePath(), lib.getMethod(), tCExecution);
            msg = ai.getResultMessage();
            // if the call returns success then we can process the soap ressponse
            if (msg.getCode() == MessageEventEnum.ACTION_SUCCESS_CALLSERVICE.getCode()) {
                appService = (AppService) ai.getItem();
                // Record result in filessytem.
                // testCaseExecutionData.addFileList(recorderService.recordServiceCall(tCExecution, null, 0, testCaseExecutionData.getProperty(), appService));
                recorderService.recordServiceCall(tCExecution, null, 0, testCaseExecutionData.getProperty(), appService);
                // Call successful so we can start to parse the result and build RawData per columns from subdata entries.
                /**
                 * This Step will calculate hashTemp1 : Hash of List from
                 * the Service response.
                 */
                // Will contain the nb of row of the target list of Hash.
                int finalnbRow = 0;
                // Will contain the result of the XML parsing.
                HashMap<String, List<String>> hashTemp1 = new HashMap<>();
                if (columnList.isEmpty()) {
                    // No subdata could be found on the testdatalib.
                    msg = new MessageEvent(MessageEventEnum.PROPERTY_FAILED_GETFROMDATALIB_SERVICE_NOSUBDATA);
                    msg.setDescription(msg.getDescription().replace("%ENTRY%", lib.getName()).replace("%ENTRYID%", lib.getTestDataLibID().toString()));
                } else {
                    switch(appService.getResponseHTTPBodyContentType()) {
                        case AppService.RESPONSEHTTPBODYCONTENTTYPE_XML:
                            Document xmlDocument = xmlUnitService.getXmlDocument(appService.getResponseHTTPBody());
                            // We get the content of the XML in order to report it log messages.
                            responseString = appService.getResponseHTTPBody();
                            for (Map.Entry<String, String> entry : columnList.entrySet()) {
                                // SubData
                                String subDataColumnToTreat = entry.getKey();
                                // Parsing Answer
                                String subDataParsingAnswer = entry.getValue();
                                listTemp1 = new ArrayList<>();
                                try {
                                    // We try to parse the XML with the subdata Parsing Answer.
                                    NodeList candidates = XmlUtil.evaluate(xmlDocument, subDataParsingAnswer);
                                    if (candidates.getLength() > 0) {
                                        for (int i = 0; i < candidates.getLength(); i++) {
                                            // Loop on all Values that match in XML.
                                            // We get the value from XML
                                            String value = candidates.item(i).getNodeValue();
                                            if (value == null) {
                                                // No value found.
                                                if (candidates.item(i) != null) {
                                                    msg = new MessageEvent(MessageEventEnum.PROPERTY_FAILED_GETFROMDATALIB_SERVICE_CHECK_XPATH);
                                                    msg.setDescription(msg.getDescription().replace("%XPATH%", subDataParsingAnswer).replace("%SUBDATA%", subDataColumnToTreat).replace("%ENTRY%", lib.getName()).replace("%ENTRYID%", lib.getTestDataLibID().toString()));
                                                } else {
                                                    // no elements were returned by the XPATH expression
                                                    msg = new MessageEvent(MessageEventEnum.PROPERTY_FAILED_GETFROMDATALIB_SERVICE_XML_NOTFOUND);
                                                    msg.setDescription(msg.getDescription().replace("%XPATH%", subDataParsingAnswer).replace("%SUBDATA%", subDataColumnToTreat).replace("%ENTRY%", lib.getName()).replace("%XMLCONTENT%", responseString).replace("%ENTRYID%", lib.getTestDataLibID().toString()));
                                                }
                                            } else {
                                                // Value were found we add it to the current list.
                                                listTemp1.add(value);
                                            }
                                        }
                                        // Add the Subdata with associated list in the HashMap.
                                        hashTemp1.put(subDataColumnToTreat, listTemp1);
                                        // Getting the nb of row of the final result. (Max of all the Subdata retrieved from the XML)
                                        if (listTemp1.size() > finalnbRow) {
                                            finalnbRow = listTemp1.size();
                                        }
                                    } else {
                                        // no elements were returned by the XPATH expression
                                        msg = new MessageEvent(MessageEventEnum.PROPERTY_FAILED_GETFROMDATALIB_SERVICE_XML_NOTFOUND);
                                        msg.setDescription(msg.getDescription().replace("%XPATH%", subDataParsingAnswer).replace("%SUBDATA%", subDataColumnToTreat).replace("%ENTRY%", lib.getName()).replace("%XMLCONTENT%", responseString).replace("%ENTRYID%", lib.getTestDataLibID().toString()));
                                    }
                                } catch (XmlUtilException ex) {
                                    msg = new MessageEvent(MessageEventEnum.PROPERTY_FAILED_GETFROMDATALIB_SERVICE_XMLEXCEPTION);
                                    msg.setDescription(msg.getDescription().replace("%XPATH%", subDataParsingAnswer).replace("%SUBDATA%", subDataColumnToTreat).replace("%ENTRY%", lib.getName()).replace("%ENTRYID%", lib.getTestDataLibID().toString()).replace("%REASON%", ex.toString() + " Detail answer " + responseString));
                                } catch (Exception ex) {
                                    msg = new MessageEvent(MessageEventEnum.PROPERTY_FAILED_GETFROMDATALIB_SERVICE_XMLEXCEPTION);
                                    msg.setDescription(msg.getDescription().replace("%XPATH%", lib.getSubDataParsingAnswer()).replace("%SUBDATA%", "").replace("%REASON%", ex.toString()));
                                }
                            }
                            /**
                             * This Step will convert hashTemp1 (Hash of
                             * List) to target listResult (list of Hash).
                             */
                            if (msg.getCode() == MessageEventEnum.ACTION_SUCCESS_CALLSERVICE.getCode()) {
                                for (int i = 0; i < finalnbRow; i++) {
                                    // Loop on all Values that match in XML.
                                    resultHash = new HashMap<String, String>();
                                    for (Map.Entry<String, String> entry : columnList.entrySet()) {
                                        // Loop on all SubData of the TestDataLib.
                                        listTemp1 = hashTemp1.get(entry.getKey());
                                        if (listTemp1 != null) {
                                            if (i < listTemp1.size()) {
                                                resultHash.put(entry.getKey(), listTemp1.get(i));
                                            } else {
                                                resultHash.put(entry.getKey(), "");
                                            }
                                        }
                                    }
                                    listResult.add(resultHash);
                                }
                            }
                            /**
                             * This Step will pick the correct listResult
                             * (list of Hash) from the type of Property.
                             */
                            if (msg.getCode() == MessageEventEnum.ACTION_SUCCESS_CALLSERVICE.getCode()) {
                                result.setDataList(listResult);
                                msg = new MessageEvent(MessageEventEnum.PROPERTY_SUCCESS_GETFROMDATALIB_SOAP);
                                msg.setDescription(msg.getDescription().replace("%NBROW%", String.valueOf(result.getDataList().size())).replace("%URL%", servicePath).replace("%OPER%", lib.getMethod()));
                            }
                            break;
                        case AppService.RESPONSEHTTPBODYCONTENTTYPE_JSON:
                            // We get the content of the XML in order to report it log messages.
                            responseString = appService.getResponseHTTPBody();
                            for (Map.Entry<String, String> entry : columnList.entrySet()) {
                                // SubData
                                String subDataColumnToTreat = entry.getKey();
                                // Parsing Answer
                                String subDataParsingAnswer = entry.getValue();
                                listTemp1 = new ArrayList<>();
                                try {
                                    // We try to parse the XML with the subdata Parsing Answer.
                                    listTemp1 = jsonService.getFromJson(responseString, subDataParsingAnswer);
                                    if (listTemp1.size() > 0) {
                                        // Add the Subdata with associated list in the HashMap.
                                        hashTemp1.put(subDataColumnToTreat, listTemp1);
                                        // Getting the nb of row of the final result. (Max of all the Subdata retrieved from the XML)
                                        if (listTemp1.size() > finalnbRow) {
                                            finalnbRow = listTemp1.size();
                                        }
                                    } else {
                                        // no elements were returned by the XPATH expression
                                        msg = new MessageEvent(MessageEventEnum.PROPERTY_FAILED_GETFROMDATALIB_SERVICE_JSON_NOTFOUND);
                                        msg.setDescription(msg.getDescription().replace("%XPATH%", subDataParsingAnswer).replace("%SUBDATA%", subDataColumnToTreat).replace("%ENTRY%", lib.getName()).replace("%XMLCONTENT%", responseString).replace("%ENTRYID%", lib.getTestDataLibID().toString()));
                                    }
                                } catch (Exception ex) {
                                    msg = new MessageEvent(MessageEventEnum.PROPERTY_FAILED_GETFROMDATALIB_SERVICE_JSONEXCEPTION);
                                    msg.setDescription(msg.getDescription().replace("%XPATH%", lib.getSubDataParsingAnswer()).replace("%SUBDATA%", "").replace("%REASON%", ex.toString()));
                                }
                            }
                            /**
                             * This Step will convert hashTemp1 (Hash of
                             * List) to target listResult (list of Hash).
                             */
                            if (msg.getCode() == MessageEventEnum.ACTION_SUCCESS_CALLSERVICE.getCode()) {
                                for (int i = 0; i < finalnbRow; i++) {
                                    // Loop on all Values that match in XML.
                                    resultHash = new HashMap<String, String>();
                                    for (Map.Entry<String, String> entry : columnList.entrySet()) {
                                        // Loop on all SubData of the TestDataLib.
                                        listTemp1 = hashTemp1.get(entry.getKey());
                                        if (listTemp1 != null) {
                                            if (i < listTemp1.size()) {
                                                resultHash.put(entry.getKey(), listTemp1.get(i));
                                            } else {
                                                resultHash.put(entry.getKey(), "");
                                            }
                                        }
                                    }
                                    listResult.add(resultHash);
                                }
                            }
                            /**
                             * This Step will pick the correct listResult
                             * (list of Hash) from the type of Property.
                             */
                            if (msg.getCode() == MessageEventEnum.ACTION_SUCCESS_CALLSERVICE.getCode()) {
                                result.setDataList(listResult);
                                msg = new MessageEvent(MessageEventEnum.PROPERTY_SUCCESS_GETFROMDATALIB_SOAP);
                                msg.setDescription(msg.getDescription().replace("%NBROW%", String.valueOf(result.getDataList().size())).replace("%URL%", servicePath).replace("%OPER%", lib.getMethod()));
                            }
                            break;
                        default:
                            msg = new MessageEvent(MessageEventEnum.PROPERTY_FAILED_GETFROMDATALIB_NOTSUPPORTEDSERVICERESULT);
                            msg.setDescription(msg.getDescription().replace("%FORMAT%", appService.getResponseHTTPBodyContentType()));
                    }
                }
            } else {
                String soapError = msg.getDescription();
                msg = new MessageEvent(MessageEventEnum.PROPERTY_FAILED_GETFROMDATALIB_SERVICE_SOAPCALLFAILED);
                msg.setDescription(msg.getDescription().replace("%SOAPERROR%", soapError));
            }
            msg.setDescription(msg.getDescription().replace("%SERVICE%", servicePath).replace("%OPERATION%", lib.getMethod()));
            result.setResultMessage(msg);
            break;
        case TestDataLib.TYPE_INTERNAL:
            result = testDataLibService.readINTERNALWithSubdataByCriteria(lib.getName(), lib.getSystem(), lib.getCountry(), lib.getEnvironment(), rowLimit, system);
            // if the sql service returns a success message then we can process it
            if ((result.getResultMessage().getCode() == MessageEventEnum.DATA_OPERATION_OK.getCode()) && !result.getDataList().isEmpty()) {
                msg = new MessageEvent(MessageEventEnum.PROPERTY_SUCCESS_GETFROMDATALIB_INTERNAL);
                msg.setDescription(msg.getDescription().replace("%NBROW%", String.valueOf(result.getDataList().size())));
                result.setResultMessage(msg);
            } else if ((result.getResultMessage().getCode() == MessageEventEnum.DATA_OPERATION_OK.getCode()) && result.getDataList().isEmpty()) {
                msg = new MessageEvent(MessageEventEnum.PROPERTY_FAILED_GETFROMDATALIB_INTERNALNODATA);
                msg.setDescription(msg.getDescription().replace("%SYSTEM%", lib.getSystem()).replace("%ENV%", lib.getEnvironment()).replace("%COUNTRY%", lib.getCountry()));
                result.setResultMessage(msg);
            } else {
                msg = new MessageEvent(MessageEventEnum.PROPERTY_FAILED_GETFROMDATALIB_INTERNAL);
                msg.setDescription(msg.getDescription().replace("%SYSTEM%", lib.getSystem()).replace("%ENV%", lib.getEnvironment()).replace("%COUNTRY%", lib.getCountry()));
                result.setResultMessage(msg);
            }
            break;
    }
    return result;
}

Example 13 with Parameter

use of org.cerberus.crud.entity.Parameter in project cerberus-source by cerberustesting.

the class ParameterDAO method readWithSystem1BySystemByCriteria.

@Override
public AnswerList readWithSystem1BySystemByCriteria(String system, String system1, int start, int amount, String column, String dir, String searchTerm, Map<String, List<String>> individualSearch) {
    AnswerList response = new AnswerList();
    MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));
    List<Parameter> objectList = new ArrayList<Parameter>();
    StringBuilder searchSQL = new StringBuilder();
    List<String> individalColumnSearchValues = new ArrayList<String>();
    StringBuilder query = new StringBuilder();
    // SQL_CALC_FOUND_ROWS allows to retrieve the total number of columns by disrearding the limit clauses that
    // were applied -- used for pagination p
    query.append("SELECT SQL_CALC_FOUND_ROWS par.param, par.`value`, par.description, ? system1, par1.`value` system1Value FROM parameter par ");
    query.append(" LEFT OUTER JOIN ( SELECT * from parameter WHERE system = ? ) as par1 ON par1.`param` = par.`param` ");
    query.append(" WHERE par.system = ?");
    if (!StringUtil.isNullOrEmpty(searchTerm)) {
        searchSQL.append(" and (par.param like ?");
        searchSQL.append(" or par.`value` like ?");
        searchSQL.append(" or par1.`value` like ?");
        searchSQL.append(" or par.description like ?)");
    }
    if (individualSearch != null && !individualSearch.isEmpty()) {
        searchSQL.append(" and ( 1=1 ");
        for (Map.Entry<String, List<String>> entry : individualSearch.entrySet()) {
            searchSQL.append(" and ");
            String q = SqlUtil.getInSQLClauseForPreparedStatement(entry.getKey(), entry.getValue());
            if (q == null || "".equals(q)) {
                q = "(" + entry.getKey() + " IS NULL OR " + entry.getKey() + " = '')";
            }
            searchSQL.append(q);
            individalColumnSearchValues.addAll(entry.getValue());
        }
        searchSQL.append(" )");
    }
    query.append(searchSQL);
    if (!StringUtil.isNullOrEmpty(column)) {
        query.append(" order by ").append(column).append(" ").append(dir);
    }
    if ((amount <= 0) || (amount >= MAX_ROW_SELECTED)) {
        query.append(" limit ").append(start).append(" , ").append(MAX_ROW_SELECTED);
    } else {
        query.append(" limit ").append(start).append(" , ").append(amount);
    }
    // Debug message on SQL.
    if (LOG.isDebugEnabled()) {
        LOG.debug("SQL : " + query.toString());
    }
    Connection connection = this.databaseSpring.connect();
    try {
        PreparedStatement preStat = connection.prepareStatement(query.toString());
        try {
            int i = 1;
            preStat.setString(i++, system1);
            preStat.setString(i++, system1);
            preStat.setString(i++, system);
            if (!StringUtil.isNullOrEmpty(searchTerm)) {
                preStat.setString(i++, "%" + searchTerm + "%");
                preStat.setString(i++, "%" + searchTerm + "%");
                preStat.setString(i++, "%" + searchTerm + "%");
                preStat.setString(i++, "%" + searchTerm + "%");
            }
            for (String individualColumnSearchValue : individalColumnSearchValues) {
                preStat.setString(i++, individualColumnSearchValue);
            }
            ResultSet resultSet = preStat.executeQuery();
            try {
                // gets the data
                while (resultSet.next()) {
                    objectList.add(this.loadFromResultSetWithSystem1(resultSet));
                }
                // get the total number of rows
                resultSet = preStat.executeQuery("SELECT FOUND_ROWS()");
                int nrTotalRows = 0;
                if (resultSet != null && resultSet.next()) {
                    nrTotalRows = resultSet.getInt(1);
                }
                if (objectList.size() >= MAX_ROW_SELECTED) {
                    // Result of SQl was limited by MAX_ROW_SELECTED constrain. That means that we may miss some lines in the resultList.
                    LOG.error("Partial Result in the query.");
                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_WARNING_PARTIAL_RESULT);
                    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", "Maximum row reached : " + MAX_ROW_SELECTED));
                    response = new AnswerList(objectList, nrTotalRows);
                } else if (objectList.size() <= 0) {
                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);
                    response = new AnswerList(objectList, nrTotalRows);
                } else {
                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
                    msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "SELECT"));
                    response = new AnswerList(objectList, nrTotalRows);
                }
            } catch (SQLException exception) {
                LOG.error("Unable to execute query : " + exception.toString());
                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
            } finally {
                if (resultSet != null) {
                    resultSet.close();
                }
            }
        } catch (SQLException exception) {
            LOG.error("Unable to execute query : " + exception.toString());
            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
            msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
        } finally {
            if (preStat != null) {
                preStat.close();
            }
        }
    } catch (SQLException exception) {
        LOG.error("Unable to execute query : " + exception.toString());
        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
        msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
    } finally {
        try {
            if (!this.databaseSpring.isOnTransaction()) {
                if (connection != null) {
                    connection.close();
                }
            }
        } catch (SQLException exception) {
            LOG.warn("Unable to close connection : " + exception.toString());
        }
    }
    response.setResultMessage(msg);
    response.setDataList(objectList);
    return response;
}

Example 14 with Parameter

use of org.cerberus.crud.entity.Parameter in project cerberus-source by cerberustesting.

the class ParameterDAO method findParameterByKey.

@Override
public Parameter findParameterByKey(String system, String key) throws CerberusException {
    boolean throwExep = false;
    Parameter result = null;
    final String query = "SELECT * FROM parameter p WHERE p.`system` = ? and p.param = ? ";
    Connection connection = this.databaseSpring.connect();
    try {
        PreparedStatement preStat = connection.prepareStatement(query);
        try {
            preStat.setString(1, system);
            preStat.setString(2, key);
            ResultSet resultSet = preStat.executeQuery();
            try {
                if (resultSet.first()) {
                    String value = resultSet.getString("value");
                    String desc = resultSet.getString("description");
                    result = factoryParameter.create(system, key, value, desc);
                } else {
                    throwExep = true;
                }
            } catch (SQLException exception) {
                LOG.warn("Unable to execute query : " + exception.toString());
            } finally {
                resultSet.close();
            }
        } catch (SQLException exception) {
            LOG.warn("Unable to execute query : " + exception.toString());
        } finally {
            preStat.close();
        }
    } catch (SQLException exception) {
        LOG.warn("Unable to execute query : " + exception.toString());
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            LOG.warn(e.toString());
        }
    }
    if (throwExep) {
        MessageGeneral mes = new MessageGeneral(MessageGeneralEnum.NO_DATA_FOUND);
        mes.setDescription(mes.getDescription() + " Parameter not defined : " + key);
        throw new CerberusException(mes);
    }
    return result;
}

Example 15 with Parameter

use of org.cerberus.crud.entity.Parameter in project cerberus-source by cerberustesting.

the class ParameterDAO method readWithSystem1ByKey.

@Override
public AnswerItem readWithSystem1ByKey(String system, String key, String system1) {
    AnswerItem a = new AnswerItem();
    StringBuilder query = new StringBuilder();
    Parameter p = new Parameter();
    MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));
    query.append("SELECT par.param, par.`value`, par.description, ? system1, par1.`value` system1value FROM parameter par " + "LEFT OUTER JOIN (SELECT * FROM parameter WHERE system = ? and param = ?) as par1 ON par.param = par1.param WHERE par.system = ? AND par.param = ?");
    // Debug message on SQL.
    if (LOG.isDebugEnabled()) {
        LOG.debug("SQL : " + query);
        LOG.debug("SQL.param.system1 : " + system1);
        LOG.debug("SQL.param.system : " + system);
        LOG.debug("SQL.param.key : " + key);
    }
    try (Connection connection = this.databaseSpring.connect();
        PreparedStatement preStat = connection.prepareStatement(query.toString())) {
        preStat.setString(1, system1);
        preStat.setString(2, system1);
        preStat.setString(3, key);
        preStat.setString(4, system);
        preStat.setString(5, key);
        try (ResultSet resultSet = preStat.executeQuery()) {
            // gets the data
            while (resultSet.next()) {
                p = this.loadFromResultSetWithSystem1(resultSet);
            }
            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
            msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "SELECT"));
        } catch (SQLException exception) {
            LOG.error("Unable to execute query : " + exception.toString());
            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
            msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
        }
    } catch (SQLException e) {
        LOG.error("Unable to execute query : " + e.toString());
        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
        msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", e.toString()));
    }
    a.setResultMessage(msg);
    a.setItem(p);
    return a;
}