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

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

Source:CountryEnvParamDAO.java

...98                preStat.setString(15, ParameterParserUtil.wildcardIfEmpty(countryEnvParam.getMaintenanceEnd()));99                ResultSet resultSet = preStat.executeQuery();100                try {101                    while (resultSet.next()) {102                        result.add(this.loadFromResultSet(resultSet));103                    }104                } catch (SQLException exception) {105                    LOG.warn("Unable to execute query : " + exception.toString());106                } finally {107                    resultSet.close();108                }109            } catch (SQLException exception) {110                LOG.warn("Unable to execute query : " + exception.toString());111            } finally {112                preStat.close();113            }114        } catch (SQLException exception) {115            LOG.warn("Unable to execute query : " + exception.toString());116        } finally {117            try {118                if (connection != null) {119                    connection.close();120                }121            } catch (SQLException e) {122                LOG.warn(e.toString());123            }124        }125        if (throwex) {126            throw new CerberusException(new MessageGeneral(MessageGeneralEnum.NO_DATA_FOUND));127        }128        return result;129    }130    @Override131    public AnswerItem readByKey(String system, String country, String environment) {132        AnswerItem ans = new AnswerItem();133        CountryEnvParam result = null;134        final String query = "SELECT * FROM `countryenvparam` WHERE `system` = ? and `country` = ? and `environment` = ?";135        MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);136        msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));137        Connection connection = this.databaseSpring.connect();138        try {139            PreparedStatement preStat = connection.prepareStatement(query);140            try {141                preStat.setString(1, system);142                preStat.setString(2, country);143                preStat.setString(3, environment);144                ResultSet resultSet = preStat.executeQuery();145                try {146                    if (resultSet.first()) {147                        result = loadFromResultSet(resultSet);148                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);149                        msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "SELECT"));150                        ans.setItem(result);151                    } else {152                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);153                    }154                } catch (SQLException exception) {155                    LOG.error("Unable to execute query : " + exception.toString());156                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);157                    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));158                } finally {159                    resultSet.close();160                }161            } catch (SQLException exception) {162                LOG.error("Unable to execute query : " + exception.toString());163                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);164                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));165            } finally {166                preStat.close();167            }168        } catch (SQLException exception) {169            LOG.error("Unable to execute query : " + exception.toString());170            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);171            msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));172        } finally {173            try {174                if (connection != null) {175                    connection.close();176                }177            } catch (SQLException exception) {178                LOG.warn("Unable to close connection : " + exception.toString());179            }180        }181        //sets the message182        ans.setResultMessage(msg);183        return ans;184    }185    @Override186    public AnswerList readActiveBySystem(String system) {187        AnswerList response = new AnswerList();188        MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);189        msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));190        List<CountryEnvParam> countryEnvParamList = new ArrayList<CountryEnvParam>();191        StringBuilder query = new StringBuilder();192        //SQL_CALC_FOUND_ROWS allows to retrieve the total number of columns by disrearding the limit clauses that 193        //were applied -- used for pagination p194        query.append("SELECT * FROM countryenvparam WHERE 1=1 ");195        if (system != null) {196            query.append("AND system = ? ");197        }198        query.append("AND active = 'Y'");199        // Debug message on SQL.200        if (LOG.isDebugEnabled()) {201            LOG.debug("SQL : " + query.toString());202        }203        Connection connection = this.databaseSpring.connect();204        try {205            PreparedStatement preStat = connection.prepareStatement(query.toString());206            try {207                if (system != null) {208                    preStat.setString(1, system);209                }210                ResultSet resultSet = preStat.executeQuery();211                try {212                    //gets the data213                    while (resultSet.next()) {214                        countryEnvParamList.add(this.loadFromResultSet(resultSet));215                    }216                    //get the total number of rows217                    resultSet = preStat.executeQuery("SELECT FOUND_ROWS()");218                    int nrTotalRows = 0;219                    if (resultSet != null && resultSet.next()) {220                        nrTotalRows = resultSet.getInt(1);221                    }222                    if (countryEnvParamList.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.223                        LOG.error("Partial Result in the query.");224                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_WARNING_PARTIAL_RESULT);225                        msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", "Maximum row reached : " + MAX_ROW_SELECTED));226                        response = new AnswerList(countryEnvParamList, nrTotalRows);227                    } else {228                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);229                        msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "SELECT"));230                        response = new AnswerList(countryEnvParamList, nrTotalRows);231                    }232                } catch (SQLException exception) {233                    LOG.error("Unable to execute query : " + exception.toString());234                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);235                    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", "Unable to retrieve the list of entries!"));236                } finally {237                    if (resultSet != null) {238                        resultSet.close();239                    }240                }241            } catch (SQLException exception) {242                LOG.error("Unable to execute query : " + exception.toString());243                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);244                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", "Unable to retrieve the list of entries!"));245            } finally {246                if (preStat != null) {247                    preStat.close();248                }249            }250        } catch (SQLException exception) {251            LOG.error("Unable to execute query : " + exception.toString());252            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);253            msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", "Unable to retrieve the list of entries!"));254        } finally {255            try {256                if (!this.databaseSpring.isOnTransaction()) {257                    if (connection != null) {258                        connection.close();259                    }260                }261            } catch (SQLException exception) {262                LOG.error("Unable to close connection : " + exception.toString());263            }264        }265        response.setResultMessage(msg);266        response.setDataList(countryEnvParamList);267        return response;268    }269    @Override270    public AnswerList readByCriteria(int start, int amount, String colName, String dir, String searchTerm, String individualSearch) {271        AnswerList response = new AnswerList();272        MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);273        msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));274        List<CountryEnvParam> cepList = new ArrayList<CountryEnvParam>();275        StringBuilder searchSQL = new StringBuilder();276        StringBuilder query = new StringBuilder();277        //SQL_CALC_FOUND_ROWS allows to retrieve the total number of columns by disrearding the limit clauses that 278        //were applied -- used for pagination p279        query.append("SELECT SQL_CALC_FOUND_ROWS * FROM countryenvparam ");280        searchSQL.append(" where 1=1 ");281        if (!StringUtil.isNullOrEmpty(searchTerm)) {282            searchSQL.append(" and (`system` like '%").append(searchTerm).append("%'");283            searchSQL.append(" or `country` like '%").append(searchTerm).append("%'");284            searchSQL.append(" or `environment` like '%").append(searchTerm).append("%'");285            searchSQL.append(" or `description` like '%").append(searchTerm).append("%'");286            searchSQL.append(" or `build` like '%").append(searchTerm).append("%'");287            searchSQL.append(" or `revision` like '%").append(searchTerm).append("%'");288            searchSQL.append(" or `chain` like '%").append(searchTerm).append("%'");289            searchSQL.append(" or `distriblist` like '%").append(searchTerm).append("%'");290            searchSQL.append(" or `emailbodyrevision` like '%").append(searchTerm).append("%'");291            searchSQL.append(" or `type` like '%").append(searchTerm).append("%'");292            searchSQL.append(" or `emailbodychain` like '%").append(searchTerm).append("%'");293            searchSQL.append(" or `emailbodydisableenvironment` like '%").append(searchTerm).append("%'");294            searchSQL.append(" or `active` like '%").append(searchTerm).append("%'");295            searchSQL.append(" or `maintenanceact` like '%").append(searchTerm).append("%'");296            searchSQL.append(" or `maintenancestr` like '%").append(searchTerm).append("%'");297            searchSQL.append(" or `maintenanceend` like '%").append(searchTerm).append("%')");298        }299        if (!StringUtil.isNullOrEmpty(individualSearch)) {300            searchSQL.append(" and (`").append(individualSearch).append("`)");301        }302        query.append(searchSQL);303        if (!StringUtil.isNullOrEmpty(colName)) {304            query.append("order by `").append(colName).append("` ").append(dir);305        }306        if ((amount <= 0) || (amount >= MAX_ROW_SELECTED)) {307            query.append(" limit ").append(start).append(" , ").append(MAX_ROW_SELECTED);308        } else {309            query.append(" limit ").append(start).append(" , ").append(amount);310        }311        Connection connection = this.databaseSpring.connect();312        try {313            PreparedStatement preStat = connection.prepareStatement(query.toString());314            try {315                ResultSet resultSet = preStat.executeQuery();316                try {317                    //gets the data318                    while (resultSet.next()) {319                        cepList.add(this.loadFromResultSet(resultSet));320                    }321                    //get the total number of rows322                    resultSet = preStat.executeQuery("SELECT FOUND_ROWS()");323                    int nrTotalRows = 0;324                    if (resultSet != null && resultSet.next()) {325                        nrTotalRows = resultSet.getInt(1);326                    }327                    if (cepList.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.328                        LOG.error("Partial Result in the query.");329                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_WARNING_PARTIAL_RESULT);330                        msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", "Maximum row reached : " + MAX_ROW_SELECTED));331                        response = new AnswerList(cepList, nrTotalRows);332                    } else {333                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);334                        msg.setDescription(msg.getDescription().replace("%ITEM%", "CountryEnvParam").replace("%OPERATION%", "SELECT"));335                        response = new AnswerList(cepList, nrTotalRows);336                    }337                } catch (SQLException exception) {338                    LOG.error("Unable to execute query : " + exception.toString());339                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);340                    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", "Unable to retrieve the list of entries!"));341                } finally {342                    if (resultSet != null) {343                        resultSet.close();344                    }345                }346            } catch (SQLException exception) {347                LOG.error("Unable to execute query : " + exception.toString());348                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);349                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", "Unable to retrieve the list of entries!"));350            } finally {351                if (preStat != null) {352                    preStat.close();353                }354            }355        } catch (SQLException exception) {356            LOG.error("Unable to execute query : " + exception.toString());357            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);358            msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", "Unable to retrieve the list of entries!"));359        } finally {360            try {361                if (!this.databaseSpring.isOnTransaction()) {362                    if (connection != null) {363                        connection.close();364                    }365                }366            } catch (SQLException exception) {367                LOG.warn("Unable to close connection : " + exception.toString());368            }369        }370        response.setResultMessage(msg);371        response.setDataList(cepList);372        return response;373    }374    @Override375    public AnswerList readByVariousByCriteria(String system, String country, String environment, String build, String revision, String active, String envGp, int start, int amount, String colName, String dir, String searchTerm, Map<String, List<String>> individualSearch) {376        AnswerList response = new AnswerList();377        MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);378        msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));379        List<CountryEnvParam> cepList = new ArrayList<CountryEnvParam>();380        StringBuilder searchSQL = new StringBuilder();381        List<String> individalColumnSearchValues = new ArrayList<String>();382        StringBuilder query = new StringBuilder();383        //SQL_CALC_FOUND_ROWS allows to retrieve the total number of columns by disrearding the limit clauses that 384        //were applied -- used for pagination p385        query.append("SELECT SQL_CALC_FOUND_ROWS * FROM countryenvparam cep");386        if (!StringUtil.isNullOrEmpty(envGp)) {387            searchSQL.append(" LEFT OUTER JOIN invariant i on i.idname='ENVIRONMENT' and i.value=cep.environment ");388        }389        searchSQL.append(" where 1=1 ");390        if (!StringUtil.isNullOrEmpty(searchTerm)) {391            searchSQL.append(" and (`system` like ?");392            searchSQL.append(" or `country` like ?");393            searchSQL.append(" or `environment` like ?");394            searchSQL.append(" or `description` like ?");395            searchSQL.append(" or `build` like ?");396            searchSQL.append(" or `revision` like ?");397            searchSQL.append(" or `chain` like ?");398            searchSQL.append(" or `distriblist` like ?");399            searchSQL.append(" or `emailbodyrevision` like ?");400            searchSQL.append(" or `type` like ?");401            searchSQL.append(" or `emailbodychain` like ?");402            searchSQL.append(" or `emailbodydisableenvironment` like ?");403            searchSQL.append(" or `active` like ?");404            searchSQL.append(" or `maintenanceact` like ?");405            searchSQL.append(" or `maintenancestr` like ?");406            searchSQL.append(" or `maintenanceend` like ?)");407        }408        if (individualSearch != null && !individualSearch.isEmpty()) {409            searchSQL.append(" and ( 1=1 ");410            for (Map.Entry<String, List<String>> entry : individualSearch.entrySet()) {411                searchSQL.append(" and ");412                searchSQL.append(SqlUtil.getInSQLClauseForPreparedStatement(entry.getKey(), entry.getValue()));413                individalColumnSearchValues.addAll(entry.getValue());414            }415            searchSQL.append(" )");416        }417        if (!StringUtil.isNullOrEmpty(system)) {418            searchSQL.append(" and (`System` = ? )");419        }420        if (!StringUtil.isNullOrEmpty(active)) {421            searchSQL.append(" and (`active` = ? )");422        }423        if (!StringUtil.isNullOrEmpty(country)) {424            searchSQL.append(" and (`country` = ? )");425        }426        if (!StringUtil.isNullOrEmpty(environment)) {427            searchSQL.append(" and (`environment` = ? )");428        }429        if (!StringUtil.isNullOrEmpty(build)) {430            searchSQL.append(" and (`build` = ? )");431        }432        if (!StringUtil.isNullOrEmpty(revision)) {433            searchSQL.append(" and (`revision` = ? )");434        }435        if (!StringUtil.isNullOrEmpty(envGp)) {436            searchSQL.append(" and (i.`gp1` = ? )");437        }438        query.append(searchSQL);439        if (!StringUtil.isNullOrEmpty(colName)) {440            query.append(" order by `").append(colName).append("` ").append(dir);441        }442        if ((amount <= 0) || (amount >= MAX_ROW_SELECTED)) {443            query.append(" limit ").append(start).append(" , ").append(MAX_ROW_SELECTED);444        } else {445            query.append(" limit ").append(start).append(" , ").append(amount);446        }447        // Debug message on SQL.448        if (LOG.isDebugEnabled()) {449            LOG.debug("SQL : " + query.toString());450        }451        Connection connection = this.databaseSpring.connect();452        try {453            PreparedStatement preStat = connection.prepareStatement(query.toString());454            try {455                int i = 1;456                if (!StringUtil.isNullOrEmpty(searchTerm)) {457                    preStat.setString(i++, "%" + searchTerm + "%");458                    preStat.setString(i++, "%" + searchTerm + "%");459                    preStat.setString(i++, "%" + searchTerm + "%");460                    preStat.setString(i++, "%" + searchTerm + "%");461                    preStat.setString(i++, "%" + searchTerm + "%");462                    preStat.setString(i++, "%" + searchTerm + "%");463                    preStat.setString(i++, "%" + searchTerm + "%");464                    preStat.setString(i++, "%" + searchTerm + "%");465                    preStat.setString(i++, "%" + searchTerm + "%");466                    preStat.setString(i++, "%" + searchTerm + "%");467                    preStat.setString(i++, "%" + searchTerm + "%");468                    preStat.setString(i++, "%" + searchTerm + "%");469                    preStat.setString(i++, "%" + searchTerm + "%");470                    preStat.setString(i++, "%" + searchTerm + "%");471                    preStat.setString(i++, "%" + searchTerm + "%");472                    preStat.setString(i++, "%" + searchTerm + "%");473                }474                for (String individualColumnSearchValue : individalColumnSearchValues) {475                    preStat.setString(i++, individualColumnSearchValue);476                }477                if (!StringUtil.isNullOrEmpty(system)) {478                    preStat.setString(i++, system);479                }480                if (!StringUtil.isNullOrEmpty(active)) {481                    preStat.setString(i++, active);482                }483                if (!StringUtil.isNullOrEmpty(country)) {484                    preStat.setString(i++, country);485                }486                if (!StringUtil.isNullOrEmpty(environment)) {487                    preStat.setString(i++, environment);488                }489                if (!StringUtil.isNullOrEmpty(build)) {490                    preStat.setString(i++, build);491                }492                if (!StringUtil.isNullOrEmpty(revision)) {493                    preStat.setString(i++, revision);494                }495                if (!StringUtil.isNullOrEmpty(envGp)) {496                    preStat.setString(i++, envGp);497                }498                ResultSet resultSet = preStat.executeQuery();499                try {500                    //gets the data501                    while (resultSet.next()) {502                        cepList.add(this.loadFromResultSet(resultSet));503                    }504                    //get the total number of rows505                    resultSet = preStat.executeQuery("SELECT FOUND_ROWS()");506                    int nrTotalRows = 0;507                    if (resultSet != null && resultSet.next()) {508                        nrTotalRows = resultSet.getInt(1);509                    }510                    if (cepList.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.511                        LOG.error("Partial Result in the query.");512                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_WARNING_PARTIAL_RESULT);513                        msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", "Maximum row reached : " + MAX_ROW_SELECTED));514                        response = new AnswerList(cepList, nrTotalRows);515                    } else if (cepList.size() <= 0) {516                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);517                        response = new AnswerList(cepList, nrTotalRows);518                    } else {519                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);520                        msg.setDescription(msg.getDescription().replace("%ITEM%", "CountryEnvParam").replace("%OPERATION%", "SELECT"));521                        response = new AnswerList(cepList, nrTotalRows);522                    }523                } catch (SQLException exception) {524                    LOG.error("Unable to execute query : " + exception.toString());525                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);526                    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));527                } finally {528                    if (resultSet != null) {529                        resultSet.close();530                    }531                }532            } catch (SQLException exception) {533                LOG.error("Unable to execute query : " + exception.toString());534                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);535                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));536            } finally {537                if (preStat != null) {538                    preStat.close();539                }540            }541        } catch (SQLException exception) {542            LOG.error("Unable to execute query : " + exception.toString());543            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);544            msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));545        } finally {546            try {547                if (!this.databaseSpring.isOnTransaction()) {548                    if (connection != null) {549                        connection.close();550                    }551                }552            } catch (SQLException exception) {553                LOG.warn("Unable to close connection : " + exception.toString());554            }555        }556        response.setResultMessage(msg);557        response.setDataList(cepList);558        return response;559    }560    @Override561    public AnswerList readDistinctEnvironmentByVariousByCriteria(String system, String country, String environment, String build, String revision, String active, String envGp, int start, int amount, String colName, String dir, String searchTerm, String individualSearch) {562        AnswerList response = new AnswerList();563        MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);564        msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));565        List<CountryEnvParam> cepList = new ArrayList<CountryEnvParam>();566        StringBuilder searchSQL = new StringBuilder();567        StringBuilder query = new StringBuilder();568        //SQL_CALC_FOUND_ROWS allows to retrieve the total number of columns by disrearding the limit clauses that 569        //were applied -- used for pagination p570        query.append("SELECT SQL_CALC_FOUND_ROWS ");571        query.append(" max(system) system, max(country) country, Environment, '' Description,  ");572        query.append(" max(build) build, max(revision) revision, max(chain) chain, '' DistribList, ");573        query.append(" '' EMailBodyRevision, max(Type) type, '' EMailBodyChain, '' EMailBodyDisableEnvironment,  ");574        query.append(" max(Active) active, max(maintenanceact) maintenanceact, max(maintenancestr) maintenancestr, max(maintenanceend) maintenanceend ");575        query.append(" FROM countryenvparam cep ");576        if (!StringUtil.isNullOrEmpty(envGp)) {577            searchSQL.append(" LEFT OUTER JOIN invariant i on i.idname='ENVIRONMENT' and i.value=cep.environment ");578        }579        searchSQL.append(" where 1=1 ");580        if (!StringUtil.isNullOrEmpty(system)) {581            searchSQL.append(" and (`System` = ? )");582        }583        if (!StringUtil.isNullOrEmpty(active)) {584            searchSQL.append(" and (`active` = ? )");585        }586        if (!StringUtil.isNullOrEmpty(country)) {587            searchSQL.append(" and (`country` = ? )");588        }589        if (!StringUtil.isNullOrEmpty(environment)) {590            searchSQL.append(" and (`environment` = ? )");591        }592        if (!StringUtil.isNullOrEmpty(build)) {593            searchSQL.append(" and (`build` = ? )");594        }595        if (!StringUtil.isNullOrEmpty(revision)) {596            searchSQL.append(" and (`revision` = ? )");597        }598        if (!StringUtil.isNullOrEmpty(envGp)) {599            searchSQL.append(" and (i.`gp1` = ? )");600        }601        query.append(searchSQL);602        query.append(" group by `environment` ");603        query.append(" order by `environment` ");604        if ((amount <= 0) || (amount >= MAX_ROW_SELECTED)) {605            query.append(" limit ").append(start).append(" , ").append(MAX_ROW_SELECTED);606        } else {607            query.append(" limit ").append(start).append(" , ").append(amount);608        }609        // Debug message on SQL.610        if (LOG.isDebugEnabled()) {611            LOG.debug("SQL : " + query.toString());612            LOG.debug("SQL.system : " + system);613            LOG.debug("SQL.active : " + active);614        }615        Connection connection = this.databaseSpring.connect();616        try {617            PreparedStatement preStat = connection.prepareStatement(query.toString());618            try {619                int i = 1;620                if (!StringUtil.isNullOrEmpty(system)) {621                    preStat.setString(i++, system);622                }623                if (!StringUtil.isNullOrEmpty(active)) {624                    preStat.setString(i++, active);625                }626                if (!StringUtil.isNullOrEmpty(country)) {627                    preStat.setString(i++, country);628                }629                if (!StringUtil.isNullOrEmpty(environment)) {630                    preStat.setString(i++, environment);631                }632                if (!StringUtil.isNullOrEmpty(build)) {633                    preStat.setString(i++, build);634                }635                if (!StringUtil.isNullOrEmpty(revision)) {636                    preStat.setString(i++, revision);637                }638                if (!StringUtil.isNullOrEmpty(envGp)) {639                    preStat.setString(i++, envGp);640                }641                ResultSet resultSet = preStat.executeQuery();642                try {643                    //gets the data644                    while (resultSet.next()) {645                        cepList.add(this.loadFromResultSet(resultSet));646                    }647                    //get the total number of rows648                    resultSet = preStat.executeQuery("SELECT FOUND_ROWS()");649                    int nrTotalRows = 0;650                    if (resultSet != null && resultSet.next()) {651                        nrTotalRows = resultSet.getInt(1);652                    }653                    if (cepList.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.654                        LOG.error("Partial Result in the query.");655                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_WARNING_PARTIAL_RESULT);656                        msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", "Maximum row reached : " + MAX_ROW_SELECTED));657                        response = new AnswerList(cepList, nrTotalRows);658                    } else if (cepList.size() <= 0) {659                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);660                        response = new AnswerList(cepList, nrTotalRows);661                    } else {662                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);663                        msg.setDescription(msg.getDescription().replace("%ITEM%", "CountryEnvParam").replace("%OPERATION%", "SELECT"));664                        response = new AnswerList(cepList, nrTotalRows);665                    }666                } catch (SQLException exception) {667                    LOG.error("Unable to execute query : " + exception.toString());668                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);669                    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));670                } finally {671                    if (resultSet != null) {672                        resultSet.close();673                    }674                }675            } catch (SQLException exception) {676                LOG.error("Unable to execute query : " + exception.toString());677                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);678                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));679            } finally {680                if (preStat != null) {681                    preStat.close();682                }683            }684        } catch (SQLException exception) {685            LOG.error("Unable to execute query : " + exception.toString());686            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);687            msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));688        } finally {689            try {690                if (!this.databaseSpring.isOnTransaction()) {691                    if (connection != null) {692                        connection.close();693                    }694                }695            } catch (SQLException exception) {696                LOG.warn("Unable to close connection : " + exception.toString());697            }698        }699        response.setResultMessage(msg);700        response.setDataList(cepList);701        return response;702    }703    @Override704    public Answer create(CountryEnvParam cep) {705        MessageEvent msg = null;706        StringBuilder query = new StringBuilder();707        query.append("INSERT INTO `countryenvparam`  (`system`, `country`, `environment`, `build`, `revision`,`chain`, `distriblist`, `eMailBodyRevision`, `type`,`eMailBodyChain`, ");708        query.append("`eMailBodyDisableEnvironment`,  `active`, `maintenanceact`, `maintenancestr`, `maintenanceend`, `description`) ");709        query.append("VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");710        // Debug message on SQL.711        if (LOG.isDebugEnabled()) {712            LOG.debug("SQL : " + query.toString());713        }714        Connection connection = this.databaseSpring.connect();715        try {716            PreparedStatement preStat = connection.prepareStatement(query.toString());717            try {718                preStat.setString(1, cep.getSystem());719                preStat.setString(2, cep.getCountry());720                preStat.setString(3, cep.getEnvironment());721                preStat.setString(4, cep.getBuild());722                preStat.setString(5, cep.getRevision());723                preStat.setString(6, cep.getChain());724                preStat.setString(7, cep.getDistribList());725                preStat.setString(8, cep.geteMailBodyRevision());726                preStat.setString(9, cep.getType());727                preStat.setString(10, cep.geteMailBodyChain());728                preStat.setString(11, cep.geteMailBodyDisableEnvironment());729                if (cep.isActive()) {730                    preStat.setString(12, "Y");731                } else {732                    preStat.setString(12, "N");733                }734                if (cep.isMaintenanceAct()) {735                    preStat.setString(13, "Y");736                } else {737                    preStat.setString(13, "N");738                }739                preStat.setString(14, cep.getMaintenanceStr());740                preStat.setString(15, cep.getMaintenanceEnd());741                preStat.setString(16, cep.getDescription());742                preStat.executeUpdate();743                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);744                msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "INSERT"));745            } catch (SQLException exception) {746                LOG.error("Unable to execute query : " + exception.toString());747                if (exception.getSQLState().equals(SQL_DUPLICATED_CODE)) { //23000 is the sql state for duplicate entries748                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_DUPLICATE);749                    msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "INSERT").replace("%REASON%", exception.toString()));750                } else {751                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);752                    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));753                }754            } finally {755                preStat.close();756            }757        } catch (SQLException exception) {758            LOG.error("Unable to execute query : " + exception.toString());759            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);760            msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));761        } finally {762            try {763                if (connection != null) {764                    connection.close();765                }766            } catch (SQLException exception) {767                LOG.warn("Unable to close connection : " + exception.toString());768            }769        }770        return new Answer(msg);771    }772    @Override773    public Answer delete(CountryEnvParam cep) {774        MessageEvent msg = null;775        final String query = "DELETE FROM `countryenvparam` WHERE `system`=? and `country`=? and `environment`=?";776        // Debug message on SQL.777        if (LOG.isDebugEnabled()) {778            LOG.debug("SQL : " + query);779        }780        Connection connection = this.databaseSpring.connect();781        try {782            PreparedStatement preStat = connection.prepareStatement(query);783            try {784                preStat.setString(1, cep.getSystem());785                preStat.setString(2, cep.getCountry());786                preStat.setString(3, cep.getEnvironment());787                preStat.executeUpdate();788                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);789                msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "DELETE"));790            } catch (SQLException exception) {791                LOG.error("Unable to execute query : " + exception.toString());792                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);793                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));794            } finally {795                preStat.close();796            }797        } catch (SQLException exception) {798            LOG.error("Unable to execute query : " + exception.toString());799            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);800            msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));801        } finally {802            try {803                if (connection != null) {804                    connection.close();805                }806            } catch (SQLException exception) {807                LOG.warn("Unable to close connection : " + exception.toString());808            }809        }810        return new Answer(msg);811    }812    @Override813    public Answer update(CountryEnvParam cep) {814        MessageEvent msg = null;815        final StringBuilder query = new StringBuilder("UPDATE `countryenvparam` SET `build`=?, ");816        query.append("`revision`=?,`chain`=?, `distriblist`=?, `eMailBodyRevision`=?, `type`=?,`eMailBodyChain`=?,");817        query.append("`eMailBodyDisableEnvironment`=?,  `active`=?, `maintenanceact`=?, `maintenancestr`=?, `maintenanceend`=?, `description`=? ");818        query.append(" where `system`=? and `country`=? and `environment`=? ");819        // Debug message on SQL.820        if (LOG.isDebugEnabled()) {821            LOG.debug("SQL : " + query.toString());822            LOG.debug("SQL.param.maintenancestr : " + cep.getMaintenanceStr());823        }824        Connection connection = this.databaseSpring.connect();825        try {826            PreparedStatement preStat = connection.prepareStatement(query.toString());827            try {828                preStat.setString(1, ParameterParserUtil.parseStringParam(cep.getBuild(), ""));829                preStat.setString(2, cep.getRevision());830                preStat.setString(3, cep.getChain());831                preStat.setString(4, cep.getDistribList());832                preStat.setString(5, cep.geteMailBodyRevision());833                preStat.setString(6, cep.getType());834                preStat.setString(7, cep.geteMailBodyChain());835                preStat.setString(8, cep.geteMailBodyDisableEnvironment());836                if (cep.isActive()) {837                    preStat.setString(9, "Y");838                } else {839                    preStat.setString(9, "N");840                }841                if (cep.isMaintenanceAct()) {842                    preStat.setString(10, "Y");843                } else {844                    preStat.setString(10, "N");845                }846                preStat.setString(11, cep.getMaintenanceStr());847                preStat.setString(12, cep.getMaintenanceEnd());848                preStat.setString(13, cep.getDescription());849                preStat.setString(14, cep.getSystem());850                preStat.setString(15, cep.getCountry());851                preStat.setString(16, cep.getEnvironment());852                preStat.executeUpdate();853                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);854                msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "UPDATE"));855            } catch (SQLException exception) {856                LOG.error("Unable to execute query : " + exception.toString());857                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);858                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));859            } finally {860                preStat.close();861            }862        } catch (SQLException exception) {863            LOG.error("Unable to execute query : " + exception.toString());864            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);865            msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));866        } finally {867            try {868                if (connection != null) {869                    connection.close();870                }871            } catch (SQLException exception) {872                LOG.warn("Unable to close connection : " + exception.toString());873            }874        }875        return new Answer(msg);876    }877    private CountryEnvParam loadFromResultSet(ResultSet resultSet) throws SQLException {878        String system = resultSet.getString("System");879        String count = resultSet.getString("Country");880        String env = resultSet.getString("Environment");881        String description = resultSet.getString("Description");882        String build = resultSet.getString("Build");883        String revision = resultSet.getString("Revision");884        String chain = resultSet.getString("chain");885        String distribList = resultSet.getString("distribList");886        String eMailBodyRevision = resultSet.getString("eMailBodyRevision");887        String type = resultSet.getString("type");888        String eMailBodyChain = resultSet.getString("eMailBodyChain");889        String eMailBodyDisableEnvironment = resultSet.getString("eMailBodyDisableEnvironment");890        boolean active = StringUtil.parseBoolean(resultSet.getString("active"));891        boolean maintenanceAct = StringUtil.parseBoolean(resultSet.getString("maintenanceact"));...

loadFromResultSet

Using AI Code Generation

1    public CountryEnvParam loadFromResultSet(ResultSet rs) throws SQLException {2        String system = ParameterParserUtil.parseStringParam(rs.getString("System"), "");3        String country = ParameterParserUtil.parseStringParam(rs.getString("Country"), "");4        String environment = ParameterParserUtil.parseStringParam(rs.getString("Environment"), "");5        String build = ParameterParserUtil.parseStringParam(rs.getString("Build"), "");6        String revision = ParameterParserUtil.parseStringParam(rs.getString("Revision"), "");7        String ip = ParameterParserUtil.parseStringParam(rs.getString("IP"), "");8        String url = ParameterParserUtil.parseStringParam(rs.getString("URL"), "");9        String domain = ParameterParserUtil.parseStringParam(rs.getString("Domain"), "");10        String urlLogin = ParameterParserUtil.parseStringParam(rs.getString("URLLogin"), "");11        String urlLogout = ParameterParserUtil.parseStringParam(rs.getString("URLLogout"), "");12        String sso = ParameterParserUtil.parseStringParam(rs.getString("SSO"), "");13        String database = ParameterParserUtil.parseStringParam(rs.getString("Database"), "");14        String databaseUrl = ParameterParserUtil.parseStringParam(rs.getString("DatabaseUrl"), "");15        String databaseLogin = ParameterParserUtil.parseStringParam(rs.getString("DatabaseLogin"), "");16        String databasePassword = ParameterParserUtil.parseStringParam(rs.getString("DatabasePassword"), "");17        String databaseDriver = ParameterParserUtil.parseStringParam(rs.getString("DatabaseDriver"), "");18        String databaseDns = ParameterParserUtil.parseStringParam(rs.getString("DatabaseDns"), "");19        String databasePoolSize = ParameterParserUtil.parseStringParam(rs.getString("DatabasePoolSize"), "");20        String databasePoolMaxSize = ParameterParserUtil.parseStringParam(rs.getString("DatabasePoolMaxSize"), "");21        String databasePoolWaitTimeout = ParameterParserUtil.parseStringParam(rs.getString("DatabasePoolWaitTimeout"), "");22        String databasePoolMaxLifetime = ParameterParserUtil.parseStringParam(rs.getString("DatabasePoolMaxLifetime"), "");23        String databasePoolIdleTimeout = ParameterParserUtil.parseStringParam(rs.getString("DatabasePoolIdleTimeout"), "");24        String databasePoolConnectionTimeout = ParameterParserUtil.parseStringParam(rs.getString("DatabasePoolConnectionTimeout"), "");25        String databasePoolEvictionInterval = ParameterParserUtil.parseStringParam(rs.getString("DatabasePoolEvictionInterval"), "");26        String databasePoolMinEvictableIdleTime = ParameterParserUtil.parseStringParam(rs.getString("DatabasePoolMinEvictableIdleTime"), "");

loadFromResultSet

Using AI Code Generation

1CountryEnvParamDAO countryEnvParamDAO = new CountryEnvParamDAO();2CountryEnvParam countryEnvParam = countryEnvParamDAO.loadFromResultSet(resultSet);3CountryEnvParamDAO countryEnvParamDAO = new CountryEnvParamDAO();4CountryEnvParam countryEnvParam = countryEnvParamDAO.loadFromResultSet(resultSet);5CountryEnvParamDAO countryEnvParamDAO = new CountryEnvParamDAO();6CountryEnvParam countryEnvParam = countryEnvParamDAO.loadFromResultSet(resultSet);

loadFromResultSet

Using AI Code Generation

1CountryEnvParamDAO cepDAO = new CountryEnvParamDAO();2List<CountryEnvParam> cepList = new ArrayList<CountryEnvParam>();3try {4    ResultSet rs = null;5    cepList = cepDAO.loadFromResultSet(rs);6} catch (SQLException ex) {7    LOG.error(ex.toString(), ex);8}9TestCaseStepDAO tcsDAO = new TestCaseStepDAO();10List<TestCaseStep> tcsList = new ArrayList<TestCaseStep>();11try {12    ResultSet rs = null;13    tcsList = tcsDAO.loadFromResultSet(rs);14} catch (SQLException ex) {15    LOG.error(ex.toString(), ex);16}17TestCaseStepActionDAO tcsaDAO = new TestCaseStepActionDAO();18List<TestCaseStepAction> tcsaList = new ArrayList<TestCaseStepAction>();19try {20    ResultSet rs = null;21    tcsaList = tcsaDAO.loadFromResultSet(rs);22} catch (SQLException ex) {23    LOG.error(ex.toString(), ex);24}25TestCaseStepActionControlDAO tcsacDAO = new TestCaseStepActionControlDAO();26List<TestCaseStepActionControl> tcsacList = new ArrayList<TestCaseStepActionControl>();27try {28    ResultSet rs = null;29    tcsacList = tcsacDAO.loadFromResultSet(rs);30} catch (SQLException ex) {31    LOG.error(ex.toString(), ex);32}33TestCaseStepActionControlExecutionDAO tcsaceDAO = new TestCaseStepActionControlExecutionDAO();34List<TestCaseStepActionControlExecution> tcsaceList = new ArrayList<TestCaseStepActionControlExecution>();35try {36    ResultSet rs = null;37    tcsaceList = tcsaceDAO.loadFromResultSet(rs);38} catch (SQLException ex) {39    LOG.error(ex.toString(), ex);40}

loadFromResultSet

Using AI Code Generation

1import org.cerberus.crud.entity.CountryEnvParam;2import org.cerberus.crud.dao.impl.CountryEnvParamDAO;3import java.util.List;4import java.sql.ResultSet;5import java.sql.SQLException;6import java.util.ArrayList;7import java.util.logging.Level;8import java.util.logging.Logger;9import org.springframework.jdbc.core.RowMapper;10public class CountryEnvParamDAOImpl implements CountryEnvParamDAO {11    public List<CountryEnvParam> loadFromResultSet(ResultSet rs) throws SQLException {12        List<CountryEnvParam> list = new ArrayList<CountryEnvParam>();13        try {14            while (rs.next()) {15                String system = rs.getString("System");16                String country = rs.getString("Country");17                String environment = rs.getString("Environment");18                String ip = rs.getString("IP");19                String url = rs.getString("Url");20                String type = rs.getString("Type");21                String database = rs.getString("Database");22                String poolSize = rs.getString("PoolSize");23                String description = rs.getString("Description");24                String active = rs.getString("Active");25                String verbose = rs.getString("Verbose");26                String seleniumIP = rs.getString("SeleniumIP");27                String seleniumPort = rs.getString("SeleniumPort");28                String robotDecli = rs.getString("RobotDecli");29                String robotHost = rs.getString("RobotHost");30                String robotPort = rs.getString("RobotPort");31                String robotPlatform = rs.getString("RobotPlatform");32                String robotBrowser = rs.getString("RobotBrowser");33                String robotVersion = rs.getString("RobotVersion");34                String robotUrl = rs.getString("RobotUrl");35                String robotActive = rs.getString("RobotActive");36                String robotScreenSize = rs.getString("RobotScreenSize");37                String robotCapability = rs.getString("RobotCapability");38                String robotExecutor = rs.getString("RobotExecutor");39                String robotTimeout = rs.getString("RobotTimeout");40                String robotProxyHost = rs.getString("RobotProxyHost");41                String robotProxyPort = rs.getString("RobotProxyPort");42                String robotProxyUser = rs.getString("RobotProxyUser");43                String robotProxyPass = rs.getString("RobotProxyPass");44                String robotProxyExclude = rs.getString("RobotProxyExclude");

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.