KaneAI - World’s First E2E Software Testing Agent.Signup for Private Beta Join TestMu Conference
How to use loadFromResultSet method of org.cerberus.crud.dao.impl.TestDataLibDAO class

Best Cerberus-source code snippet using org.cerberus.crud.dao.impl.TestDataLibDAO.loadFromResultSet
Source:TestDataLibDAO.java
...84            try {85                ResultSet resultSet = preStat.executeQuery();86                try {87                    if (resultSet.first()) {88                        result = this.loadFromResultSet(resultSet);89                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);90                        msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "SELECT"));91                        answer.setItem(result);92                    } else {93                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);94                    }95                } catch (SQLException exception) {96                    LOG.error("Unable to execute query : " + exception.toString());97                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);98                    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));99                } finally {100                    if (resultSet != null) {101                        resultSet.close();102                    }103                }104            } catch (SQLException exception) {105                LOG.error("Unable to execute query : " + exception.toString());106                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);107                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));108            } finally {109                if (preStat != null) {110                    preStat.close();111                }112            }113        } catch (SQLException exception) {114            LOG.error("Unable to execute query : " + exception.toString());115            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);116            msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));117        } finally {118            try {119                if (!this.databaseSpring.isOnTransaction()) {120                    if (connection != null) {121                        connection.close();122                    }123                }124            } catch (SQLException ex) {125                LOG.warn("Unable to close connection : " + ex.toString());126            }127        }128        //sets the message129        answer.setResultMessage(msg);130        return answer;131    }132    @Override133    public AnswerItem readByNameBySystemByEnvironmentByCountry(String name, String system, String environment, String country) {134        AnswerItem answer = new AnswerItem();135        TestDataLib result = null;136        MessageEvent msg;137        final String query = new StringBuilder("SELECT * FROM testdatalib tdl")138                .append(" LEFT OUTER JOIN testdatalibdata tdd ON tdl.TestDataLibID = tdd.TestDataLibID and tdd.Subdata='' ")139                .append(" WHERE `name` LIKE ?")140                .append(" and (`system` = ? or `system` = '')")141                .append(" and (`environment` = ? or `environment` = '')")142                .append(" and (`country` = ? or `country` = '')")143                .append(" ORDER BY `name` DESC, system DESC, environment DESC, country DESC, tdl.TestDataLibID ASC")144                .append(" LIMIT 1").toString();145        // Debug message on SQL.146        if (LOG.isDebugEnabled()) {147            LOG.debug("SQL : " + query);148            LOG.debug("SQL name : " + name);149            LOG.debug("SQL system : " + system);150            LOG.debug("SQL environment : " + environment);151            LOG.debug("SQL country : " + country);152        }153        Connection connection = this.databaseSpring.connect();154        try {155            PreparedStatement preStat = connection.prepareStatement(query);156            preStat.setString(1, name);157            preStat.setString(2, ParameterParserUtil.returnEmptyStringIfNull(system));158            preStat.setString(3, ParameterParserUtil.returnEmptyStringIfNull(environment));159            preStat.setString(4, ParameterParserUtil.returnEmptyStringIfNull(country));160            try {161                ResultSet resultSet = preStat.executeQuery();162                try {163                    if (resultSet.first()) {164                        result = this.loadFromResultSet(resultSet);165                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);166                        msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "SELECT"));167                    } else {168                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);169                    }170                } catch (SQLException exception) {171                    LOG.error("Unable to execute query : " + exception.toString());172                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);173                    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));174                } finally {175                    if (resultSet != null) {176                        resultSet.close();177                    }178                }179            } catch (SQLException exception) {180                LOG.error("Unable to execute query : " + exception.toString());181                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);182                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));183            } finally {184                if (preStat != null) {185                    preStat.close();186                }187            }188        } catch (SQLException exception) {189            LOG.error("Unable to execute query : " + exception.toString());190            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);191            msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));192        } finally {193            try {194                if (!this.databaseSpring.isOnTransaction()) {195                    if (connection != null) {196                        connection.close();197                    }198                }199            } catch (SQLException ex) {200                LOG.warn("Unable to close connection : " + ex.toString());201            }202        }203        answer.setItem(result);204        answer.setResultMessage(msg);205        return answer;206    }207    private static void deleteFolder(File folder, boolean deleteit) {208        File[] files = folder.listFiles();209        if (files != null) { //some JVMs return null for empty dirs210            for (File f : files) {211                if (f.isDirectory()) {212                    deleteFolder(f, true);213                } else {214                    f.delete();215                }216            }217        }218        if (deleteit) {219            folder.delete();220        }221    }222    @Override223    public Answer uploadFile(int id, FileItem file) {224        MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED).resolveDescription("DESCRIPTION",225                "cerberus_testdatalibcsv_path Parameter not found");226        AnswerItem a = parameterService.readByKey("", "cerberus_testdatalibcsv_path");227        if (a.isCodeEquals(MessageEventEnum.DATA_OPERATION_OK.getCode())) {228            Parameter p = (Parameter) a.getItem();229            String uploadPath = p.getValue();230            File appDir = new File(uploadPath + File.separator + id);231            if (!appDir.exists()) {232                try {233                    appDir.mkdirs();234                } catch (SecurityException se) {235                    LOG.warn("Unable to create testdatalib csv dir: " + se.getMessage());236                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED).resolveDescription("DESCRIPTION",237                            se.toString());238                    a.setResultMessage(msg);239                }240            }241            if (a.isCodeEquals(MessageEventEnum.DATA_OPERATION_OK.getCode())) {242                deleteFolder(appDir, false);243                File picture = new File(uploadPath + File.separator + id + File.separator + file.getName());244                try {245                    file.write(picture);246                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK).resolveDescription("DESCRIPTION",247                            "TestDataLib CSV file uploaded");248                    msg.setDescription(msg.getDescription().replace("%ITEM%", "testDatalib CSV").replace("%OPERATION%", "Upload"));249                } catch (Exception e) {250                    LOG.warn("Unable to upload testdatalib csv file: " + e.getMessage());251                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED).resolveDescription("DESCRIPTION",252                            e.toString());253                }254            }255        } else {256            LOG.warn("cerberus_testdatalibCSV_path Parameter not found");257        }258        a.setResultMessage(msg);259        return a;260    }261    @Override262    public AnswerList readNameListByName(String testDataLibName, int limit, boolean like) {263        AnswerList answer = new AnswerList();264        MessageEvent msg;265        List<TestDataLib> list = new ArrayList<TestDataLib>();266        StringBuilder query = new StringBuilder();267        query.append("SELECT * ")268                .append("FROM testdatalib tdl ");269        if (like) {270            query.append(" WHERE `name` like  ? ");271        } else {272            query.append(" WHERE `name` =  ? ");273        }274        query.append(" limit ? ");275        if ((limit <= 0) || (limit >= MAX_ROW_SELECTED)) {276            limit = MAX_ROW_SELECTED;277        }278        // Debug message on SQL.279        if (LOG.isDebugEnabled()) {280            LOG.debug("SQL : " + query);281        }282        Connection connection = this.databaseSpring.connect();283        try {284            PreparedStatement preStat = connection.prepareStatement(query.toString());285            if (like) {286                preStat.setString(1, "%" + testDataLibName + "%");287            } else {288                preStat.setString(1, testDataLibName);289            }290            preStat.setInt(2, limit);291            try {292                ResultSet resultSet = preStat.executeQuery();293                try {294                    while (resultSet.next()) {295                        list.add(this.loadFromResultSet(resultSet));296                    }297                    if (list.isEmpty()) {298                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);299                    } else {300                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);301                        msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "SELECT"));302                    }303                } catch (SQLException exception) {304                    LOG.error("Unable to execute query : " + exception.toString());305                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);306                    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));307                    list.clear();308                } finally {309                    if (resultSet != null) {310                        resultSet.close();311                    }312                }313            } catch (SQLException exception) {314                LOG.error("Unable to execute query : " + exception.toString());315                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);316                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));317            } finally {318                if (preStat != null) {319                    preStat.close();320                }321            }322        } catch (SQLException exception) {323            LOG.error("Unable to execute query : " + exception.toString());324            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);325            msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));326        } finally {327            try {328                if (!this.databaseSpring.isOnTransaction()) {329                    if (connection != null) {330                        connection.close();331                    }332                }333            } catch (SQLException ex) {334                LOG.warn("Unable to close connection : " + ex.toString());335            }336        }337        answer.setDataList(list);338        answer.setTotalRows(list.size());339        answer.setResultMessage(msg);340        return answer;341    }342    @Override343    public AnswerList readAll() {344        AnswerList answer = new AnswerList();345        MessageEvent msg;346        List<TestDataLib> list = new ArrayList<TestDataLib>();347        final String query = "SELECT * FROM testdatalib tdl"348                + " LEFT OUTER JOIN testdatalibdata tdd ON tdl.TestDataLibID = tdd.TestDataLibID and tdd.Subdata=''; ";349        // Debug message on SQL.350        if (LOG.isDebugEnabled()) {351            LOG.debug("SQL : " + query);352        }353        Connection connection = this.databaseSpring.connect();354        try {355            PreparedStatement preStat = connection.prepareStatement(query);356            try {357                ResultSet resultSet = preStat.executeQuery();358                list = new ArrayList<TestDataLib>();359                try {360                    while (resultSet.next()) {361                        list.add(this.loadFromResultSet(resultSet));362                    }363                    if (list.isEmpty()) {364                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);365                    } else {366                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);367                        msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "SELECT"));368                    }369                } catch (SQLException exception) {370                    LOG.error("Unable to execute query : " + exception.toString());371                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);372                    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));373                    list.clear();374                } finally {375                    if (resultSet != null) {376                        resultSet.close();377                    }378                }379            } catch (SQLException exception) {380                LOG.error("Unable to execute query : " + exception.toString());381                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);382                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));383            } finally {384                if (preStat != null) {385                    preStat.close();386                }387            }388        } catch (SQLException exception) {389            LOG.error("Unable to execute query : " + exception.toString());390            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);391            msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));392        } finally {393            try {394                if (!this.databaseSpring.isOnTransaction()) {395                    if (connection != null) {396                        connection.close();397                    }398                }399            } catch (SQLException ex) {400                LOG.warn("Unable to close connection : " + ex.toString());401            }402        }403        answer.setResultMessage(msg);404        answer.setDataList(list);405        answer.setTotalRows(list.size());406        return answer;407    }408    @Override409    public AnswerList readByVariousByCriteria(String name, String system, String environment, String country, String type, int start, int amount, String column, String dir, String searchTerm, Map<String, List<String>> individualSearch) {410        AnswerList answer = new AnswerList();411        MessageEvent msg;412        int nrTotalRows = 0;413        List<TestDataLib> objectList = new ArrayList<>();414        List<String> individalColumnSearchValues = new ArrayList<>();415        StringBuilder searchSQL = new StringBuilder();416        StringBuilder query = new StringBuilder();417        //SQL_CALC_FOUND_ROWS allows to retrieve the total number of columns by disrearding the limit clauses that 418        //were applied -- used for pagination p419        query.append("SELECT SQL_CALC_FOUND_ROWS * FROM testdatalib tdl ");420        query.append("LEFT OUTER JOIN testdatalibdata tdd ON tdl.TestDataLibID=tdd.TestDataLibID and tdd.SubData='' ");421        searchSQL.append(" WHERE 1=1 ");422        if (!StringUtil.isNullOrEmpty(searchTerm)) {423            searchSQL.append(" and (tdl.`name` like ?");424            searchSQL.append(" or tdl.`group` like ?");425            searchSQL.append(" or tdl.`type` like ?");426            searchSQL.append(" or tdl.`database` like ?");427            searchSQL.append(" or tdl.`databaseUrl` like ?");428            searchSQL.append(" or tdl.`script` like ?");429            searchSQL.append(" or tdl.`service` like ?");430            searchSQL.append(" or tdl.`servicepath` like ?");431            searchSQL.append(" or tdl.`method` like ?");432            searchSQL.append(" or tdl.`envelope` like ?");433            searchSQL.append(" or tdl.`databaseCsv` like ?");434            searchSQL.append(" or tdl.`csvUrl` like ?");435            searchSQL.append(" or tdl.`separator` like ?");436            searchSQL.append(" or tdl.`description` like ?");437            searchSQL.append(" or tdl.`system` like ?");438            searchSQL.append(" or tdl.`environment` like ?");439            searchSQL.append(" or tdl.`country` like ?) ");440        }441        if (individualSearch != null && !individualSearch.isEmpty()) {442            searchSQL.append(" and ( 1=1 ");443            for (Map.Entry<String, List<String>> entry : individualSearch.entrySet()) {444                searchSQL.append(" and ");445                searchSQL.append(SqlUtil.getInSQLClauseForPreparedStatement(entry.getKey(), entry.getValue()));446                individalColumnSearchValues.addAll(entry.getValue());447            }448            searchSQL.append(" )");449        }450        if (name != null) {451            searchSQL.append(" and tdl.`name` = ? ");452        }453        if (system != null) {454            searchSQL.append(" and tdl.`system` = ? ");455        }456        if (environment != null) {457            searchSQL.append(" and tdl.`environment` = ? ");458        }459        if (country != null) {460            searchSQL.append(" and tdl.`country` = ? ");461        }462        if (!StringUtil.isNullOrEmpty(type)) {463            searchSQL.append(" and tdl.`type` = ? ");464        }465        query.append(searchSQL);466        if (!StringUtil.isNullOrEmpty(column)) {467            query.append(" order by ").append(column).append(" ").append(dir);468        }469        if ((amount <= 0) || (amount >= MAX_ROW_SELECTED)) {470            query.append(" limit ").append(start).append(" , ").append(MAX_ROW_SELECTED);471        } else {472            query.append(" limit ").append(start).append(" , ").append(amount).append(" ");473        }474        // Debug message on SQL.475        if (LOG.isDebugEnabled()) {476            LOG.debug("SQL : " + query);477            LOG.debug("SQL.name : " + name);478            LOG.debug("SQL.system : " + system);479            LOG.debug("SQL.environment : " + environment);480            LOG.debug("SQL.country : " + country);481            LOG.debug("SQL.type : " + type);482        }483        Connection connection = this.databaseSpring.connect();484        try {485            PreparedStatement preStat = connection.prepareStatement(query.toString());486            try {487                int i = 1;488                if (!StringUtil.isNullOrEmpty(searchTerm)) {489                    preStat.setString(i++, "%" + searchTerm + "%");490                    preStat.setString(i++, "%" + searchTerm + "%");491                    preStat.setString(i++, "%" + searchTerm + "%");492                    preStat.setString(i++, "%" + searchTerm + "%");493                    preStat.setString(i++, "%" + searchTerm + "%");494                    preStat.setString(i++, "%" + searchTerm + "%");495                    preStat.setString(i++, "%" + searchTerm + "%");496                    preStat.setString(i++, "%" + searchTerm + "%");497                    preStat.setString(i++, "%" + searchTerm + "%");498                    preStat.setString(i++, "%" + searchTerm + "%");499                    preStat.setString(i++, "%" + searchTerm + "%");500                    preStat.setString(i++, "%" + searchTerm + "%");501                    preStat.setString(i++, "%" + searchTerm + "%");502                    preStat.setString(i++, "%" + searchTerm + "%");503                    preStat.setString(i++, "%" + searchTerm + "%");504                    preStat.setString(i++, "%" + searchTerm + "%");505                    preStat.setString(i++, "%" + searchTerm + "%");506                }507                for (String individualColumnSearchValue : individalColumnSearchValues) {508                    preStat.setString(i++, individualColumnSearchValue);509                }510                if (name != null) {511                    preStat.setString(i++, name);512                }513                if (system != null) {514                    preStat.setString(i++, system);515                }516                if (environment != null) {517                    preStat.setString(i++, environment);518                }519                if (country != null) {520                    preStat.setString(i++, country);521                }522                if (!StringUtil.isNullOrEmpty(type)) {523                    preStat.setString(i++, type);524                }525                ResultSet resultSet = preStat.executeQuery();526                try {527                    //gets the data528                    while (resultSet.next()) {529                        objectList.add(this.loadFromResultSet(resultSet));530                    }531                    //get the total number of rows532                    resultSet = preStat.executeQuery("SELECT FOUND_ROWS()");533                    if (resultSet != null && resultSet.next()) {534                        nrTotalRows = resultSet.getInt(1);535                    }536                    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.537                        LOG.error("Partial Result in the query.");538                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_WARNING_PARTIAL_RESULT);539                        msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", "Maximum row reached : " + MAX_ROW_SELECTED));540                    } else if (objectList.isEmpty()) {541                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);542                    } else {543                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);544                        msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "SELECT"));545                    }546                } catch (SQLException exception) {547                    LOG.error("Unable to execute query : " + exception.toString());548                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);549                    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));550                    objectList.clear();551                } finally {552                    if (resultSet != null) {553                        resultSet.close();554                    }555                }556            } catch (SQLException exception) {557                LOG.error("Unable to execute query : " + exception.toString());558                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);559                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));560            } finally {561                if (preStat != null) {562                    preStat.close();563                }564            }565        } catch (SQLException exception) {566            LOG.error("Unable to execute query : " + exception.toString());567            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);568            msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));569        } finally {570            try {571                if (!this.databaseSpring.isOnTransaction()) {572                    if (connection != null) {573                        connection.close();574                    }575                }576            } catch (SQLException ex) {577                LOG.warn("Unable to close connection : " + ex.toString());578            }579        }580        answer.setTotalRows(nrTotalRows);581        answer.setResultMessage(msg);582        answer.setDataList(objectList);583        return answer;584    }585    @Override586    public AnswerList readDistinctGroups() {587        AnswerList answerList = new AnswerList();588        ArrayList<String> listOfGroups = new ArrayList<String>();589        MessageEvent msg;590        String query = "SELECT distinct(`Group`) FROM testdatalib  WHERE `Group` <> '' ORDER BY `Group`";591        // Debug message on SQL.592        if (LOG.isDebugEnabled()) {593            LOG.debug("SQL : " + query);594        }595        Connection connection = this.databaseSpring.connect();596        try {597            PreparedStatement preStat = connection.prepareStatement(query);598            try {599                ResultSet resultSet = preStat.executeQuery();600                try {601                    while (resultSet.next()) {602                        listOfGroups.add(resultSet.getString(1));603                    }604                    if (listOfGroups.isEmpty()) {605                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);606                    } else {607                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);608                        msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "SELECT"));609                    }610                } catch (SQLException exception) {611                    LOG.error("Unable to execute query : " + exception.toString());612                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);613                    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));614                    listOfGroups.clear();615                } finally {616                    if (resultSet != null) {617                        resultSet.close();618                    }619                }620            } catch (SQLException exception) {621                LOG.error("Unable to execute query : " + exception.toString());622                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);623                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));624            } finally {625                if (preStat != null) {626                    preStat.close();627                }628            }629        } catch (SQLException exception) {630            LOG.error("Unable to execute query : " + exception.toString());631            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);632            msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));633        } finally {634            try {635                if (!this.databaseSpring.isOnTransaction()) {636                    if (connection != null) {637                        connection.close();638                    }639                }640            } catch (SQLException ex) {641                LOG.warn("Unable to close connection : " + ex.toString());642            }643        }644        answerList.setTotalRows(listOfGroups.size());645        answerList.setDataList(listOfGroups);646        answerList.setResultMessage(msg);647        return answerList;648    }649    @Override650    public AnswerItem create(TestDataLib testDataLib) {651        MessageEvent msg;652        AnswerItem answer = new AnswerItem();653        StringBuilder query = new StringBuilder();654        TestDataLib createdTestDataLib;655        query.append("INSERT INTO testdatalib (`name`, `system`, `environment`, `country`, `group`, `type`, `database`, `script`, `databaseUrl`, ");656        query.append("`service`, `servicePath`, `method`, `envelope`, `databaseCsv`, `csvUrl`,`separator`, `description`, `creator`) ");657        if ((testDataLib.getService() != null) && (!testDataLib.getService().equals(""))) {658            query.append("VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");659        } else {660            query.append("VALUES (?,?,?,?,?,?,?,?,?,null,?,?,?,?,?,?,?,?)");661        }662        // Debug message on SQL.663        if (LOG.isDebugEnabled()) {664            LOG.debug("SQL : " + query.toString());665            LOG.debug("SQL.param.name : " + testDataLib.getName());666        }667        Connection connection = this.databaseSpring.connect();668        try {669            PreparedStatement preStat = connection.prepareStatement(query.toString(), PreparedStatement.RETURN_GENERATED_KEYS);670            try {671                int i = 1;672                preStat.setString(i++, testDataLib.getName());673                preStat.setString(i++, ParameterParserUtil.returnEmptyStringIfNull(testDataLib.getSystem()));674                preStat.setString(i++, ParameterParserUtil.returnEmptyStringIfNull(testDataLib.getEnvironment()));675                preStat.setString(i++, ParameterParserUtil.returnEmptyStringIfNull(testDataLib.getCountry()));676                preStat.setString(i++, ParameterParserUtil.returnEmptyStringIfNull(testDataLib.getGroup()));677                preStat.setString(i++, testDataLib.getType());678                preStat.setString(i++, ParameterParserUtil.returnEmptyStringIfNull(testDataLib.getDatabase()));679                preStat.setString(i++, ParameterParserUtil.returnEmptyStringIfNull(testDataLib.getScript()));680                preStat.setString(i++, ParameterParserUtil.returnEmptyStringIfNull(testDataLib.getDatabaseUrl()));681                if ((testDataLib.getService() != null) && (!testDataLib.getService().equals(""))) {682                    preStat.setString(i++, ParameterParserUtil.returnEmptyStringIfNull(testDataLib.getService()));683                }684                preStat.setString(i++, ParameterParserUtil.returnEmptyStringIfNull(testDataLib.getServicePath()));685                preStat.setString(i++, ParameterParserUtil.returnEmptyStringIfNull(testDataLib.getMethod()));686                preStat.setString(i++, testDataLib.getEnvelope()); //is the one that allows null values687                preStat.setString(i++, ParameterParserUtil.returnEmptyStringIfNull(testDataLib.getDatabaseCsv()));688                preStat.setString(i++, ParameterParserUtil.returnEmptyStringIfNull(testDataLib.getCsvUrl()));689                preStat.setString(i++, ParameterParserUtil.returnEmptyStringIfNull(testDataLib.getSeparator()));690                preStat.setString(i++, ParameterParserUtil.returnEmptyStringIfNull(testDataLib.getDescription()));691                preStat.setString(i++, ParameterParserUtil.returnEmptyStringIfNull(testDataLib.getCreator()));692                preStat.executeUpdate();693                ResultSet keys = preStat.getGeneratedKeys();694                try {695                    if (keys != null && keys.next()) {696                        testDataLib.setTestDataLibID(keys.getInt(1));697                        // Debug message on SQL.698                        if (LOG.isDebugEnabled()) {699                            LOG.debug("SQL.result.TestDataLibID : " + testDataLib.getTestDataLibID());700                        }701                        answer.setItem(testDataLib);702                    }703                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);704                    msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "INSERT"));705                } catch (SQLException exception) {706                    LOG.error("Unable to execute query : " + exception.toString());707                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);708                    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));709                } finally {710                    if (keys != null) {711                        keys.close();712                    }713                }714            } catch (SQLException exception) {715                LOG.error("Unable to execute query : " + exception.toString());716                if (exception.getSQLState().equals(SQL_DUPLICATED_CODE)) { //23000 is the sql state for duplicate entries717                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_DUPLICATE);718                    msg.setDescription(msg.getDescription().replace("%ITEM%", "Test data lib ").replace("%OPERATION%", "INSERT").replace("%REASON%", exception.toString()));719                } else {720                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);721                    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));722                }723            } finally {724                if (preStat != null) {725                    preStat.close();726                }727            }728        } catch (SQLException exception) {729            LOG.error("Unable to execute query : " + exception.toString());730            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);731            msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));732        } finally {733            try {734                if (!this.databaseSpring.isOnTransaction()) {735                    if (connection != null) {736                        connection.close();737                    }738                }739            } catch (SQLException ex) {740                LOG.error("Unable to close connection : " + ex.toString());741            }742        }743        answer.setResultMessage(msg);744        return answer;745    }746    @Override747    public Answer delete(TestDataLib testDataLib) {748        Answer ans = new Answer();749        MessageEvent msg;750        StringBuilder query = new StringBuilder();751        query.append("DELETE FROM testdatalib WHERE testdatalibid = ?");752        // Debug message on SQL.753        if (LOG.isDebugEnabled()) {754            LOG.debug("SQL : " + query.toString());755        }756        Connection connection = this.databaseSpring.connect();757        try {758            PreparedStatement preStat = connection.prepareStatement(query.toString());759            try {760                preStat.setInt(1, testDataLib.getTestDataLibID());761                int rowsDeleted = preStat.executeUpdate();762                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);763                msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "DELETE"));764            } catch (SQLException exception) {765                LOG.error("Unable to execute query : " + exception.toString());766                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);767                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));768            } finally {769                if (preStat != null) {770                    preStat.close();771                }772            }773        } catch (SQLException exception) {774            LOG.error("Unable to execute query : " + exception.toString());775            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);776            msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));777        } finally {778            try {779                if (!this.databaseSpring.isOnTransaction()) {780                    if (connection != null) {781                        connection.close();782                    }783                }784            } catch (SQLException ex) {785                LOG.warn("Unable to close connection : " + ex.toString());786            }787        }788        ans.setResultMessage(msg);789        return ans;790    }791    @Override792    public Answer update(TestDataLib testDataLib) {793        Answer answer = new Answer();794        MessageEvent msg;795        String query = "UPDATE testdatalib SET `name`=?, `type`=?, `group`= ?, `system`=?, `environment`=?, `country`=?, `database`= ? , `script`= ? , "796                + "`databaseUrl`= ? , `servicepath`= ? , `method`= ? , `envelope`= ? , `DatabaseCsv` = ? , `csvUrl` = ? ,`separator`= ?,  `description`= ? , `LastModifier`= ?, `LastModified` = NOW() ";797        if ((testDataLib.getService() != null) && (!testDataLib.getService().equals(""))) {798            query += " ,`service` = ? ";799        } else {800            query += " ,`service` = null ";801        }802        query += "WHERE `TestDataLibID`= ?";803        // Debug message on SQL.804        if (LOG.isDebugEnabled()) {805            LOG.debug("SQL : " + query);806            LOG.debug("SQL.param.service : " + testDataLib.getService());807            LOG.debug("SQL.param.servicePath : " + testDataLib.getServicePath());808        }809        Connection connection = this.databaseSpring.connect();810        try {811            PreparedStatement preStat = connection.prepareStatement(query);812            try {813                int i = 1;814                preStat.setString(i++, testDataLib.getName());815                preStat.setString(i++, testDataLib.getType());816                preStat.setString(i++, testDataLib.getGroup());817                preStat.setString(i++, testDataLib.getSystem());818                preStat.setString(i++, testDataLib.getEnvironment());819                preStat.setString(i++, testDataLib.getCountry());820                preStat.setString(i++, testDataLib.getDatabase());821                preStat.setString(i++, testDataLib.getScript());822                preStat.setString(i++, testDataLib.getDatabaseUrl());823                preStat.setString(i++, testDataLib.getServicePath());824                preStat.setString(i++, testDataLib.getMethod());825                preStat.setString(i++, testDataLib.getEnvelope());826                preStat.setString(i++, testDataLib.getDatabaseCsv());827                preStat.setString(i++, testDataLib.getCsvUrl());828                preStat.setString(i++, testDataLib.getSeparator());829                preStat.setString(i++, testDataLib.getDescription());830                preStat.setString(i++, testDataLib.getLastModifier());831                if ((testDataLib.getService() != null) && (!testDataLib.getService().equals(""))) {832                    preStat.setString(i++, testDataLib.getService());833                }834                preStat.setInt(i++, testDataLib.getTestDataLibID());835                int rowsUpdated = preStat.executeUpdate();836                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);837                msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "UPDATE"));838            } catch (SQLException exception) {839                LOG.error("Unable to execute query : " + exception.toString());840                if (exception.getSQLState().equals(SQL_DUPLICATED_CODE)) { //23000 is the sql state for duplicate entries841                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_DUPLICATE);842                    msg.setDescription(msg.getDescription().replace("%ITEM%", "Test data lib ").replace("%OPERATION%", "UPDATE").replace("%REASON%", exception.toString()));843                } else {844                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);845                    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));846                }847            } finally {848                if (preStat != null) {849                    preStat.close();850                }851            }852        } catch (SQLException exception) {853            LOG.error("Unable to execute query : " + exception.toString());854            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);855            msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));856        } finally {857            try {858                if (!this.databaseSpring.isOnTransaction()) {859                    if (connection != null) {860                        connection.close();861                    }862                }863            } catch (SQLException ex) {864                LOG.warn("Unable to close connection : " + ex.toString());865            }866        }867        answer.setResultMessage(msg);868        return answer;869    }870    @Override871    public TestDataLib loadFromResultSet(ResultSet resultSet) throws SQLException {872        Integer testDataLibID = resultSet.getInt("tdl.testDataLibID");873        String name = resultSet.getString("tdl.name");874        String system = ParameterParserUtil.returnEmptyStringIfNull(resultSet.getString("tdl.system"));875        String environment = ParameterParserUtil.returnEmptyStringIfNull(resultSet.getString("tdl.environment"));876        String country = ParameterParserUtil.returnEmptyStringIfNull(resultSet.getString("tdl.country"));877        String group = ParameterParserUtil.returnEmptyStringIfNull(resultSet.getString("tdl.group"));878        String type = ParameterParserUtil.returnEmptyStringIfNull(resultSet.getString("tdl.type"));879        String database = ParameterParserUtil.returnEmptyStringIfNull(resultSet.getString("tdl.database"));880        String script = ParameterParserUtil.returnEmptyStringIfNull(resultSet.getString("tdl.script"));881        String databaseUrl = ParameterParserUtil.returnEmptyStringIfNull(resultSet.getString("tdl.databaseUrl"));882        String service = ParameterParserUtil.returnEmptyStringIfNull(resultSet.getString("tdl.service"));883        String servicePath = ParameterParserUtil.returnEmptyStringIfNull(resultSet.getString("tdl.servicePath"));884        String method = ParameterParserUtil.returnEmptyStringIfNull(resultSet.getString("tdl.method"));885        String envelope = ParameterParserUtil.returnEmptyStringIfNull(resultSet.getString("tdl.envelope"));...
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.