How to use loadFromResultSet method of org.cerberus.crud.dao.impl.ParameterDAO class

Best Cerberus-source code snippet using org.cerberus.crud.dao.impl.ParameterDAO.loadFromResultSet

Source:ParameterDAO.java

...362                ResultSet resultSet = preStat.executeQuery();363                try {364                    //gets the data365                    while (resultSet.next()) {366                        objectList.add(this.loadFromResultSetWithSystem1(resultSet));367                    }368                    //get the total number of rows369                    resultSet = preStat.executeQuery("SELECT FOUND_ROWS()");370                    int nrTotalRows = 0;371                    if (resultSet != null && resultSet.next()) {372                        nrTotalRows = resultSet.getInt(1);373                    }374                    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.375                        LOG.error("Partial Result in the query.");376                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_WARNING_PARTIAL_RESULT);377                        msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", "Maximum row reached : " + MAX_ROW_SELECTED));378                        response = new AnswerList(objectList, nrTotalRows);379                    } else if (objectList.size() <= 0) {380                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);381                        response = new AnswerList(objectList, nrTotalRows);382                    } else {383                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);384                        msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "SELECT"));385                        response = new AnswerList(objectList, nrTotalRows);386                    }387                } catch (SQLException exception) {388                    LOG.error("Unable to execute query : " + exception.toString());389                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);390                    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));391                } finally {392                    if (resultSet != null) {393                        resultSet.close();394                    }395                }396            } catch (SQLException exception) {397                LOG.error("Unable to execute query : " + exception.toString());398                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);399                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));400            } finally {401                if (preStat != null) {402                    preStat.close();403                }404            }405        } catch (SQLException exception) {406            LOG.error("Unable to execute query : " + exception.toString());407            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);408            msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));409        } finally {410            try {411                if (!this.databaseSpring.isOnTransaction()) {412                    if (connection != null) {413                        connection.close();414                    }415                }416            } catch (SQLException exception) {417                LOG.warn("Unable to close connection : " + exception.toString());418            }419        }420        response.setResultMessage(msg);421        response.setDataList(objectList);422        return response;423    }424    @Override425    public AnswerItem readWithSystem1ByKey(String system, String key, String system1) {426        AnswerItem a = new AnswerItem();427        StringBuilder query = new StringBuilder();428        Parameter p = new Parameter();429        MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);430        msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));431        query.append("SELECT par.param, par.`value`, par.description, ? system1, par1.`value` system1value FROM parameter par "432                + "LEFT OUTER JOIN (SELECT * FROM parameter WHERE system = ? and param = ?) as par1 ON par.param = par1.param WHERE par.system = ? AND par.param = ?");433        // Debug message on SQL.434        if (LOG.isDebugEnabled()) {435            LOG.debug("SQL : " + query);436            LOG.debug("SQL.param.system1 : " + system1);437            LOG.debug("SQL.param.system : " + system);438            LOG.debug("SQL.param.key : " + key);439        }440        441        try(Connection connection = this.databaseSpring.connect();442        		PreparedStatement preStat = connection.prepareStatement(query.toString());) {443            444            preStat.setString(1, system1);445            preStat.setString(2, system1);446            preStat.setString(3, key);447            preStat.setString(4, system);448            preStat.setString(5, key);449            450            try(ResultSet resultSet = preStat.executeQuery();){451            	//gets the data452                while (resultSet.next()) {453                    p = this.loadFromResultSetWithSystem1(resultSet);454                }455                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);456                msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "SELECT"));457            }catch (SQLException exception) {458                LOG.error("Unable to execute query : " + exception.toString());459                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);460                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));461            } 462        } catch (SQLException e) {463            LOG.error("Unable to execute query : " + e.toString());464            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);465            msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", e.toString()));466        }467        a.setResultMessage(msg);468        a.setItem(p);469        return a;470    }471    @Override472    public Parameter loadFromResultSetWithSystem1(ResultSet rs) throws SQLException {473        String param = ParameterParserUtil.parseStringParam(rs.getString("par.param"), "");474        String value = ParameterParserUtil.parseStringParam(rs.getString("par.value"), "");475        String description = ParameterParserUtil.parseStringParam(rs.getString("par.description"), "");476        String system1 = ParameterParserUtil.parseStringParam(rs.getString("system1"), "");477        String system1Value = ParameterParserUtil.parseStringParam(rs.getString("system1Value"), "");478        //TODO remove when working in test with mockito and autowired479        factoryParameter = new FactoryParameter();480        return factoryParameter.create("", param, value, description, system1, system1Value);481    }482    @Override483    public Parameter loadFromResultSet(ResultSet rs) throws SQLException {484        String system = ParameterParserUtil.parseStringParam(rs.getString("system"), "");485        String param = ParameterParserUtil.parseStringParam(rs.getString("param"), "");486        String value = ParameterParserUtil.parseStringParam(rs.getString("value"), "");487        String description = ParameterParserUtil.parseStringParam(rs.getString("description"), "");488        //TODO remove when working in test with mockito and autowired489        factoryParameter = new FactoryParameter();490        return factoryParameter.create(system, param, value, description);491    }492    @Override493    public AnswerList<String> readDistinctValuesWithSystem1ByCriteria(String system, String system1, String searchTerm, Map<String, List<String>> individualSearch, String columnName) {494        AnswerList answer = new AnswerList();495        MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);496        msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));497        List<String> distinctValues = new ArrayList<>();498        StringBuilder searchSQL = new StringBuilder();499        List<String> individalColumnSearchValues = new ArrayList<String>();500        StringBuilder query = new StringBuilder();501        query.append("SELECT distinct ");502        query.append(columnName);503        query.append(" as distinctValues FROM parameter par");504        query.append(" LEFT OUTER JOIN ( SELECT * from parameter par1 WHERE par1.system = ? ) as par1 ON par1.`param` = par.`param` ");505        query.append(" WHERE par.system = ?");506        if (!StringUtil.isNullOrEmpty(searchTerm)) {507            searchSQL.append(" and (par.param like ?");508            searchSQL.append(" or par.`value` like ?");509            searchSQL.append(" or par1.`value` like ?");510            searchSQL.append(" or par.description like ?)");511        }512        if (individualSearch != null && !individualSearch.isEmpty()) {513            searchSQL.append(" and ( 1=1 ");514            for (Map.Entry<String, List<String>> entry : individualSearch.entrySet()) {515                searchSQL.append(" and ");516                searchSQL.append(SqlUtil.getInSQLClauseForPreparedStatement(entry.getKey(), entry.getValue()));517                individalColumnSearchValues.addAll(entry.getValue());518            }519            searchSQL.append(" )");520        }521        query.append(searchSQL);522        query.append(" group by ifnull(").append(columnName).append(",'')");523        query.append(" order by ").append(columnName).append(" asc");524        // Debug message on SQL.525        if (LOG.isDebugEnabled()) {526            LOG.debug("SQL : " + query.toString());527        }528        529        try (Connection connection = databaseSpring.connect();530                PreparedStatement preStat = connection.prepareStatement(query.toString());531        		Statement stm = connection.createStatement();) {532            int i = 1;533            if (!StringUtil.isNullOrEmpty(system1)) {534                preStat.setString(i++, system1);535            }536            preStat.setString(i++, system);537            if (!StringUtil.isNullOrEmpty(searchTerm)) {538                preStat.setString(i++, "%" + searchTerm + "%");539                preStat.setString(i++, "%" + searchTerm + "%");540                preStat.setString(i++, "%" + searchTerm + "%");541                preStat.setString(i++, "%" + searchTerm + "%");542            }543            for (String individualColumnSearchValue : individalColumnSearchValues) {544                preStat.setString(i++, individualColumnSearchValue);545            }546            547            try(ResultSet resultSet = preStat.executeQuery();548            		ResultSet rowSet = stm.executeQuery("SELECT FOUND_ROWS()");){549            	//gets the data550                while (resultSet.next()) {551                    distinctValues.add(resultSet.getString("distinctValues") == null ? "" : resultSet.getString("distinctValues"));552                }553                int nrTotalRows = 0;554                if (rowSet != null && rowSet.next()) {555                    nrTotalRows = rowSet.getInt(1);556                }557                if (distinctValues.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.558                    LOG.error("Partial Result in the query.");559                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_WARNING_PARTIAL_RESULT);560                    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", "Maximum row reached : " + MAX_ROW_SELECTED));561                    answer = new AnswerList(distinctValues, nrTotalRows);562                } else if (distinctValues.size() <= 0) {563                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);564                    answer = new AnswerList(distinctValues, nrTotalRows);565                } else {566                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);567                    msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "SELECT"));568                    answer = new AnswerList(distinctValues, nrTotalRows);569                }570            }catch (SQLException exception) {571                LOG.error("Unable to execute query : " + exception.toString());572                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);573                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));574            } 575        } catch (Exception e) {576            LOG.warn("Unable to execute query : " + e.toString());577            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED).resolveDescription("DESCRIPTION",578                    e.toString());579        } finally {580            // We always set the result message581            answer.setResultMessage(msg);582        }583        answer.setResultMessage(msg);584        answer.setDataList(distinctValues);585        return answer;586    }587    @Override588    public AnswerItem readByKey(String system, String param) {589        AnswerItem<Parameter> ans = new AnswerItem<>();590        MessageEvent msg = null;591        try (Connection connection = databaseSpring.connect();592                PreparedStatement preStat = connection.prepareStatement(Query.READ_BY_KEY)) {593            // Prepare and execute query594            preStat.setString(1, system);595            preStat.setString(2, param);596            597            try(ResultSet resultSet = preStat.executeQuery();){598            	while (resultSet.next()) {599                    ans.setItem(loadFromResultSet(resultSet));600                }601                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK).resolveDescription("ITEM", OBJECT_NAME)602                        .resolveDescription("OPERATION", "SELECT");603            }catch (SQLException exception) {604                LOG.error("Unable to execute query : " + exception.toString());605                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);606                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));607            } 608        } catch (Exception e) {609            LOG.warn("Unable to execute query : " + e.toString());610            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED).resolveDescription("DESCRIPTION",611                    e.toString());612        } finally {613            // We always set the result message...

Automation Testing Tutorials

Learn to execute automation testing from scratch with LambdaTest Learning Hub. Right from setting up the prerequisites to run your first automation test, to following best practices and diving deeper into advanced test scenarios. LambdaTest Learning Hubs compile a list of step-by-step guides to help you be proficient with different test automation frameworks i.e. Selenium, Cypress, TestNG etc.