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

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

Source:ApplicationObjectDAO.java

...110                PreparedStatement preStat = connection.prepareStatement(Query.READ_BY_KEY)) {111            // Prepare and execute query112            preStat.setInt(1, id);113            ResultSet rs = preStat.executeQuery();114            ApplicationObject ao = loadFromResultSet(rs);115            ans.setItem(ao);116            // Set the final message117            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK).resolveDescription("ITEM", OBJECT_NAME)118                    .resolveDescription("OPERATION", "READ_BY_KEY");119        } catch (Exception e) {120            LOG.warn("Unable to read by key: " + e.getMessage());121            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED).resolveDescription("DESCRIPTION",122                    e.toString());123        } finally {124            ans.setResultMessage(msg);125        }126        return ans;127    }128    @Override129    public AnswerItem<ApplicationObject> readByKey(String application, String object) {130        AnswerItem<ApplicationObject> ans = new AnswerItem<>();131        MessageEvent msg = null;132        try (Connection connection = databaseSpring.connect();133                PreparedStatement preStat = connection.prepareStatement(Query.READ_BY_KEY1)) {134            ApplicationObject ao = null;135            // Prepare and execute query136            preStat.setString(1, application);137            preStat.setString(2, object);138            ResultSet rs = preStat.executeQuery();139            try {140                while (rs.next()) {141                    ao = loadFromResultSet(rs);142                }143                ans.setItem(ao);144                // Set the final message145                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK).resolveDescription("ITEM", OBJECT_NAME)146                        .resolveDescription("OPERATION", "READ_BY_KEY");147            }catch (Exception e) {148                LOG.warn("Unable to execute query : " + e.toString());149                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED).resolveDescription("DESCRIPTION",150                        e.toString());151            } finally {152            	if(rs != null) {153            		rs.close();154            	}155            }156        } catch (Exception e) {157            LOG.warn("Unable to read by key: " + e.getMessage());158            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED).resolveDescription("DESCRIPTION",159                    e.toString());160        } finally {161            ans.setResultMessage(msg);162        }163        return ans;164    }165    @Override166    public AnswerList<ApplicationObject> readByApplication(String Application) {167        AnswerList<ApplicationObject> ans = new AnswerList<>();168        MessageEvent msg = null;169        try (Connection connection = databaseSpring.connect();170                PreparedStatement preStat = connection.prepareStatement(Query.READ_BY_APP)) {171            // Prepare and execute query172            preStat.setString(1, Application);173            ResultSet rs = preStat.executeQuery();174            try {175            	List<ApplicationObject> al = new ArrayList<>();176                while (rs.next()) {177                    al.add(loadFromResultSet(rs));178                }179                ans.setDataList(al);180                // Set the final message181                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK).resolveDescription("ITEM", OBJECT_NAME)182                        .resolveDescription("OPERATION", "READ_BY_APP");183            }catch (Exception e) {184                LOG.warn("Unable to execute query : " + e.toString());185                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED).resolveDescription("DESCRIPTION",186                        e.toString());187            } finally {188            	if(rs != null) {189            		rs.close();190            	}191            }192        } catch (Exception e) {193            LOG.warn("Unable to read by app: " + e.getMessage());194            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED).resolveDescription("DESCRIPTION",195                    e.toString());196        } finally {197            ans.setResultMessage(msg);198        }199        return ans;200    }201    @Override202    public BufferedImage readImageByKey(String application, String object) {203        BufferedImage image = null;204        MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED).resolveDescription("DESCRIPTION",205                "cerberus_applicationobject_path Parameter not found");206        AnswerItem a = parameterService.readByKey("", "cerberus_applicationobject_path");207        if (a.isCodeEquals(MessageEventEnum.DATA_OPERATION_OK.getCode())) {208            Parameter p = (Parameter) a.getItem();209            String uploadPath = p.getValue();210            a = readByKey(application, object);211            if (a.isCodeEquals(MessageEventEnum.DATA_OPERATION_OK.getCode())) {212                ApplicationObject ao = (ApplicationObject) a.getItem();213                if (ao != null) {214                    File picture = new File(uploadPath + File.separator + ao.getID() + File.separator + ao.getScreenShotFileName());215                    try {216                        image = ImageIO.read(picture);217                    } catch (IOException e) {218                        LOG.warn("Impossible to read the image");219                    }220                }221            } else {222                LOG.warn("Application Object not found");223            }224        } else {225            LOG.warn("cerberus_applicationobject_path Parameter not found");226        }227        a.setResultMessage(msg);228        return image;229    }230    private static void deleteFolder(File folder, boolean deleteit) {231        File[] files = folder.listFiles();232        if (files != null) { //some JVMs return null for empty dirs233            for (File f : files) {234                if (f.isDirectory()) {235                    deleteFolder(f, true);236                } else {237                    f.delete();238                }239            }240        }241        if (deleteit) {242            folder.delete();243        }244    }245    @Override246    public Answer uploadFile(int id, FileItem file) {247        MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED).resolveDescription("DESCRIPTION",248                "cerberus_applicationobject_path Parameter not found");249        AnswerItem a = parameterService.readByKey("", "cerberus_applicationobject_path");250        if (a.isCodeEquals(MessageEventEnum.DATA_OPERATION_OK.getCode())) {251            Parameter p = (Parameter) a.getItem();252            String uploadPath = p.getValue();253            File appDir = new File(uploadPath + File.separator + id);254            if (!appDir.exists()) {255                try {256                    appDir.mkdirs();257                } catch (SecurityException se) {258                    LOG.warn("Unable to create application dir: " + se.getMessage());259                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED).resolveDescription("DESCRIPTION",260                            se.toString());261                    a.setResultMessage(msg);262                }263            }264            if (a.isCodeEquals(MessageEventEnum.DATA_OPERATION_OK.getCode())) {265                deleteFolder(appDir, false);266                File picture = new File(uploadPath + File.separator + id + File.separator + file.getName());267                try {268                    file.write(picture);269                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK).resolveDescription("DESCRIPTION",270                            "Application Object file uploaded");271                    msg.setDescription(msg.getDescription().replace("%ITEM%", "Application Object").replace("%OPERATION%", "Upload"));272                } catch (Exception e) {273                    LOG.warn("Unable to upload application object file: " + e.getMessage());274                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED).resolveDescription("DESCRIPTION",275                            e.toString());276                }277            }278        } else {279            LOG.warn("cerberus_applicationobject_path Parameter not found");280        }281        a.setResultMessage(msg);282        return a;283    }284    @Override285    public AnswerList<ApplicationObject> readByCriteria(int start, int amount, String column, String dir, String searchTerm, Map<String, List<String>> individualSearch) {286        AnswerList<ApplicationObject> response = new AnswerList<>();287        MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);288        msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));289        List<ApplicationObject> objectList = new ArrayList<>();290        StringBuilder searchSQL = new StringBuilder();291        List<String> individalColumnSearchValues = new ArrayList<>();292        StringBuilder query = new StringBuilder();293        //SQL_CALC_FOUND_ROWS allows to retrieve the total number of columns by disrearding the limit clauses that294        //were applied -- used for pagination p295        query.append("SELECT SQL_CALC_FOUND_ROWS * FROM applicationobject ");296        searchSQL.append(" where 1=1 ");297        if (!StringUtil.isNullOrEmpty(searchTerm)) {298            searchSQL.append(" and (`Application` like ?");299            searchSQL.append(" or `Object` like ?");300            searchSQL.append(" or `Value` like ?");301            searchSQL.append(" or `ScreenshotFileName` like ?");302            searchSQL.append(" or `UsrCreated` like ?");303            searchSQL.append(" or `DateCreated` like ?");304            searchSQL.append(" or `UsrModif` like ?");305            searchSQL.append(" or `DateModif` like ?)");306        }307        if (individualSearch != null && !individualSearch.isEmpty()) {308            searchSQL.append(" and ( 1=1 ");309            for (Map.Entry<String, List<String>> entry : individualSearch.entrySet()) {310                searchSQL.append(" and ");311                searchSQL.append(SqlUtil.getInSQLClauseForPreparedStatement(entry.getKey(), entry.getValue()));312                individalColumnSearchValues.addAll(entry.getValue());313            }314            searchSQL.append(" )");315        }316        query.append(searchSQL);317        if (!StringUtil.isNullOrEmpty(column)) {318            query.append(" order by `").append(column).append("` ").append(dir);319        }320        if ((amount <= 0) || (amount >= MAX_ROW_SELECTED)) {321            query.append(" limit ").append(start).append(" , ").append(MAX_ROW_SELECTED);322        } else {323            query.append(" limit ").append(start).append(" , ").append(amount);324        }325        // Debug message on SQL.326        if (LOG.isDebugEnabled()) {327            LOG.debug("SQL : " + query.toString());328        }329        330        try(Connection connection = this.databaseSpring.connect();331        		PreparedStatement preStat = connection.prepareStatement(query.toString());332        		Statement stm = connection.createStatement();) {333            334            int i = 1;335            if (!StringUtil.isNullOrEmpty(searchTerm)) {336                preStat.setString(i++, "%" + searchTerm + "%");337                preStat.setString(i++, "%" + searchTerm + "%");338                preStat.setString(i++, "%" + searchTerm + "%");339                preStat.setString(i++, "%" + searchTerm + "%");340                preStat.setString(i++, "%" + searchTerm + "%");341                preStat.setString(i++, "%" + searchTerm + "%");342                preStat.setString(i++, "%" + searchTerm + "%");343                preStat.setString(i++, "%" + searchTerm + "%");344            }345            for (String individualColumnSearchValue : individalColumnSearchValues) {346                preStat.setString(i++, individualColumnSearchValue);347            }348            349            try(ResultSet resultSet = preStat.executeQuery();350            		ResultSet rowSet = stm.executeQuery("SELECT FOUND_ROWS()");) {351                //gets the data352                while (resultSet.next()) {353                    objectList.add(this.loadFromResultSet(resultSet));354                }355                //get the total number of rows356                int nrTotalRows = 0;357                if (rowSet != null && rowSet.next()) {358                    nrTotalRows = rowSet.getInt(1);359                }360                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.361                    LOG.error("Partial Result in the query.");362                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_WARNING_PARTIAL_RESULT);363                    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", "Maximum row reached : " + MAX_ROW_SELECTED));364                    response = new AnswerList<>(objectList, nrTotalRows);365                } else if (objectList.size() <= 0) {366                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);367                    response = new AnswerList<>(objectList, nrTotalRows);368                } else {369                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);370                    msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "SELECT"));371                    response = new AnswerList<>(objectList, nrTotalRows);372                }373            } catch (SQLException exception) {374                LOG.error("Unable to execute query : " + exception.toString());375                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);376                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));377            } 378        } catch (SQLException exception) {379            LOG.error("Unable to execute query : " + exception.toString());380            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);381            msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));382        }383        response.setResultMessage(msg);384        response.setDataList(objectList);385        return response;386    }387    @Override388    public AnswerList<ApplicationObject> readByApplicationByCriteria(String application, int start, int amount, String column, String dir, String searchTerm, Map<String, List<String>> individualSearch, List<String> systems) {389        AnswerList<ApplicationObject> response = new AnswerList<>();390        MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);391        msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));392        List<ApplicationObject> objectList = new ArrayList<ApplicationObject>();393        StringBuilder searchSQL = new StringBuilder();394        List<String> individalColumnSearchValues = new ArrayList<String>();395        StringBuilder query = new StringBuilder();396        //SQL_CALC_FOUND_ROWS allows to retrieve the total number of columns by disrearding the limit clauses that397        //were applied -- used for pagination p398        query.append("SELECT SQL_CALC_FOUND_ROWS obj.* FROM applicationobject obj ");399        query.append(" left outer JOIN application app ON obj.Application = app.Application ");400        searchSQL.append(" where 1=1 ");401        if (!StringUtil.isNullOrEmpty(searchTerm)) {402            searchSQL.append(" and (obj.`Application` like ?");403            searchSQL.append(" or obj.`Object` like ?");404            searchSQL.append(" or obj.`Value` like ?");405            searchSQL.append(" or obj.`ScreenshotFileName` like ?");406            searchSQL.append(" or obj.`UsrCreated` like ?");407            searchSQL.append(" or obj.`DateCreated` like ?");408            searchSQL.append(" or obj.`UsrModif` like ?");409            searchSQL.append(" or obj.`DateModif` like ?)");410        }411        if (individualSearch != null && !individualSearch.isEmpty()) {412            searchSQL.append(" and ( 1=1 ");413            for (Map.Entry<String, List<String>> entry : individualSearch.entrySet()) {414                searchSQL.append(" and ");415                searchSQL.append(SqlUtil.getInSQLClauseForPreparedStatement(entry.getKey(), entry.getValue()));416                individalColumnSearchValues.addAll(entry.getValue());417            }418            searchSQL.append(" )");419        }420        if (!StringUtil.isNullOrEmpty(application)) {421            searchSQL.append(" and (obj.`Application` = ? )");422        }423        if ((systems != null) && (!systems.isEmpty())) {424            systems.add("");425            searchSQL.append(" and (").append(SqlUtil.generateInClause("app.`System`", systems)).append(") ");426        }427        searchSQL.append( " AND ").append(UserSecurity.getSystemAllowForSQL("app.`System`")).append(" ");428        query.append(searchSQL);429        if (!StringUtil.isNullOrEmpty(column)) {430            query.append(" order by `").append(column).append("` ").append(dir);431        }432        if ((amount <= 0) || (amount >= MAX_ROW_SELECTED)) {433            query.append(" limit ").append(start).append(" , ").append(MAX_ROW_SELECTED);434        } else {435            query.append(" limit ").append(start).append(" , ").append(amount);436        }437        // Debug message on SQL.438        if (LOG.isDebugEnabled()) {439            LOG.debug("SQL : " + query.toString());440        }441        Connection connection = this.databaseSpring.connect();442        try {443            PreparedStatement preStat = connection.prepareStatement(query.toString());444            try {445                int i = 1;446                if (!StringUtil.isNullOrEmpty(searchTerm)) {447                    preStat.setString(i++, "%" + searchTerm + "%");448                    preStat.setString(i++, "%" + searchTerm + "%");449                    preStat.setString(i++, "%" + searchTerm + "%");450                    preStat.setString(i++, "%" + searchTerm + "%");451                    preStat.setString(i++, "%" + searchTerm + "%");452                    preStat.setString(i++, "%" + searchTerm + "%");453                    preStat.setString(i++, "%" + searchTerm + "%");454                    preStat.setString(i++, "%" + searchTerm + "%");455                }456                for (String individualColumnSearchValue : individalColumnSearchValues) {457                    preStat.setString(i++, individualColumnSearchValue);458                }459                if (!StringUtil.isNullOrEmpty(application)) {460                    preStat.setString(i++, application);461                }462                if ((systems != null) && (!systems.isEmpty())) {463                    for (String mysystem : systems) {464                        preStat.setString(i++, mysystem);465                    }466                }467                ResultSet resultSet = preStat.executeQuery();468                try {469                    //gets the data470                    while (resultSet.next()) {471                        objectList.add(this.loadFromResultSet(resultSet));472                    }473                    //get the total number of rows474                    resultSet = preStat.executeQuery("SELECT FOUND_ROWS()");475                    int nrTotalRows = 0;476                    if (resultSet != null && resultSet.next()) {477                        nrTotalRows = resultSet.getInt(1);478                    }479                    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.480                        LOG.error("Partial Result in the query.");481                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_WARNING_PARTIAL_RESULT);482                        msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", "Maximum row reached : " + MAX_ROW_SELECTED));483                        response = new AnswerList<>(objectList, nrTotalRows);484                    } else if (objectList.size() <= 0) {485                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);486                        response = new AnswerList<>(objectList, nrTotalRows);487                    } else {488                        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);489                        msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "SELECT"));490                        response = new AnswerList<>(objectList, nrTotalRows);491                    }492                } catch (SQLException exception) {493                    LOG.error("Unable to execute query : " + exception.toString());494                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);495                    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));496                } finally {497                    if (resultSet != null) {498                        resultSet.close();499                    }500                }501            } catch (SQLException exception) {502                LOG.error("Unable to execute query : " + exception.toString());503                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);504                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));505            } finally {506                if (preStat != null) {507                    preStat.close();508                }509            }510        } catch (SQLException exception) {511            LOG.error("Unable to execute query : " + exception.toString());512            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);513            msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));514        } finally {515            try {516                if (!this.databaseSpring.isOnTransaction()) {517                    if (connection != null) {518                        connection.close();519                    }520                }521            } catch (SQLException exception) {522                LOG.warn("Unable to close connection : " + exception.toString());523            }524        }525        response.setResultMessage(msg);526        response.setDataList(objectList);527        return response;528    }529    @Override530    public Answer create(ApplicationObject object) {531        Answer ans = new Answer();532        MessageEvent msg = null;533        try (Connection connection = databaseSpring.connect();534                PreparedStatement preStat = connection.prepareStatement(Query.CREATE)) {535            // Prepare and execute query536            preStat.setString(1, object.getApplication());537            preStat.setString(2, object.getObject());538            preStat.setString(3, object.getValue());539            preStat.setString(4, object.getScreenShotFileName());540            preStat.setString(5, object.getUsrCreated());541            preStat.setString(6, object.getDateCreated());542            preStat.setString(7, object.getUsrModif());543            preStat.setString(8, object.getDateModif());544            preStat.executeUpdate();545            // Set the final message546            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK).resolveDescription("ITEM", OBJECT_NAME)547                    .resolveDescription("OPERATION", "CREATE");548        } catch (Exception e) {549            LOG.warn("Unable to create campaign object: " + e.getMessage());550            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED).resolveDescription("DESCRIPTION",551                    e.toString());552        } finally {553            ans.setResultMessage(msg);554        }555        return ans;556    }557    @Override558    public Answer delete(ApplicationObject object) {559        Answer ans = new Answer();560        MessageEvent msg = null;561        try (Connection connection = databaseSpring.connect();562                PreparedStatement preStat = connection.prepareStatement(Query.DELETE)) {563            // Prepare and execute query564            preStat.setInt(1, object.getID());565            preStat.executeUpdate();566            // Set the final message567            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK).resolveDescription("ITEM", OBJECT_NAME)568                    .resolveDescription("OPERATION", "CREATE");569        } catch (Exception e) {570            LOG.warn("Unable to create campaign object: " + e.getMessage());571            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED).resolveDescription("DESCRIPTION",572                    e.toString());573        } finally {574            ans.setResultMessage(msg);575        }576        return ans;577    }578    @Override579    public Answer update(String application, String appObject, ApplicationObject object) {580        Answer ans = new Answer();581        MessageEvent msg = null;582        String query = "UPDATE `applicationobject` SET `application` = ?, `object` = ?, `value` = ?, `screenshotfilename` = ?, `usrcreated` = ?, `datecreated` = ?, `usrmodif` = ?, `datemodif` = ? "583                + " WHERE `application` = ? AND `object` = ?";584        try (Connection connection = databaseSpring.connect();585                PreparedStatement preStat = connection.prepareStatement(query)) {586            // Prepare and execute query587            int i = 1;588            preStat.setString(i++, object.getApplication());589            preStat.setString(i++, object.getObject());590            preStat.setString(i++, object.getValue());591            preStat.setString(i++, object.getScreenShotFileName());592            preStat.setString(i++, object.getUsrCreated());593            preStat.setString(i++, object.getDateCreated());594            preStat.setString(i++, object.getUsrModif());595            preStat.setString(i++, object.getDateModif());596            preStat.setString(i++, application);597            preStat.setString(i++, appObject);598            preStat.executeUpdate();599            // Set the final message600            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK).resolveDescription("ITEM", OBJECT_NAME)601                    .resolveDescription("OPERATION", "UPDATE");602        } catch (Exception e) {603            LOG.warn("Unable to update campaign object: " + e.getMessage());604            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED).resolveDescription("DESCRIPTION",605                    e.toString());606        } finally {607            ans.setResultMessage(msg);608        }609        return ans;610    }611    @Override612    public AnswerList<String> readDistinctValuesByCriteria(String searchTerm, Map<String, List<String>> individualSearch, String columnName) {613        AnswerList<String> answer = new AnswerList<>();614        MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);615        msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));616        List<String> distinctValues = new ArrayList<>();617        StringBuilder searchSQL = new StringBuilder();618        List<String> individalColumnSearchValues = new ArrayList<String>();619        StringBuilder query = new StringBuilder();620        query.append("SELECT distinct `");621        query.append(columnName);622        query.append("` as distinctValues FROM applicationobject ");623        searchSQL.append("WHERE 1=1 ");624    	if (!StringUtil.isNullOrEmpty(searchTerm)) {625            searchSQL.append(" and (`Application` like ?");626            searchSQL.append(" or `Object` like ?");627            searchSQL.append(" or `Value` like ?");628            searchSQL.append(" or `ScreenshotFileName` like ?");629            searchSQL.append(" or `UsrCreated` like ?");630            searchSQL.append(" or `DateCreated` like ?");631            searchSQL.append(" or `UsrModif` like ?");632            searchSQL.append(" or `DateModif` like ?)");633        }634        if (individualSearch != null && !individualSearch.isEmpty()) {635            searchSQL.append(" and ( 1=1 ");636            for (Map.Entry<String, List<String>> entry : individualSearch.entrySet()) {637                searchSQL.append(" and ");638                searchSQL.append(SqlUtil.getInSQLClauseForPreparedStatement(entry.getKey(), entry.getValue()));639                individalColumnSearchValues.addAll(entry.getValue());640            }641            searchSQL.append(" )");642        }643        644        query.append(searchSQL);645        query.append(" order by `").append(columnName).append("` asc");646        647        // Debug message on SQL.648        if (LOG.isDebugEnabled()) {649            LOG.debug("SQL : " + query.toString());650        }651        652        try (Connection connection = databaseSpring.connect();653                PreparedStatement preStat = connection.prepareStatement(query.toString());654        		Statement stm = connection.createStatement();) {655            int i = 1;656            657        	if (!StringUtil.isNullOrEmpty(searchTerm)) {658                preStat.setString(i++, "%" + searchTerm + "%");659                preStat.setString(i++, "%" + searchTerm + "%");660                preStat.setString(i++, "%" + searchTerm + "%");661                preStat.setString(i++, "%" + searchTerm + "%");662                preStat.setString(i++, "%" + searchTerm + "%");663                preStat.setString(i++, "%" + searchTerm + "%");664                preStat.setString(i++, "%" + searchTerm + "%");665                preStat.setString(i++, "%" + searchTerm + "%");666            }667            for (String individualColumnSearchValue : individalColumnSearchValues) {668                preStat.setString(i++, individualColumnSearchValue);669            }670            671            try(ResultSet resultSet = preStat.executeQuery();672            		ResultSet rowSet = stm.executeQuery("SELECT FOUND_ROWS()");) {673            	//gets the data674                while (resultSet.next()) {675                    distinctValues.add(resultSet.getString("distinctValues") == null ? "" : resultSet.getString("distinctValues"));676                }677                //get the total number of rows678                679                int nrTotalRows = 0;680                if (rowSet != null && rowSet.next()) {681                    nrTotalRows = rowSet.getInt(1);682                }683                if (distinctValues.size() >= MAX_ROW_SELECTED) { // Result of SQl was limited by MAX_ROW_SELECTED constrain. That means that we may miss some lines in the resultList.684                    LOG.error("Partial Result in the query.");685                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_WARNING_PARTIAL_RESULT);686                    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", "Maximum row reached : " + MAX_ROW_SELECTED));687                    answer = new AnswerList<>(distinctValues, nrTotalRows);688                } else if (distinctValues.size() <= 0) {689                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);690                    answer = new AnswerList<>(distinctValues, nrTotalRows);691                } else {692                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);693                    msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "SELECT"));694                    answer = new AnswerList<>(distinctValues, nrTotalRows);695                }696            }catch (SQLException exception) {697                LOG.error("Unable to execute query : " + exception.toString());698                msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);699                msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));700            } 701        } catch (Exception e) {702            LOG.warn("Unable to execute query : " + e.toString());703            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED).resolveDescription("DESCRIPTION",704                    e.toString());705        } finally {706            // We always set the result message707            answer.setResultMessage(msg);708        }709        answer.setResultMessage(msg);710        answer.setDataList(distinctValues);711        return answer;712    }713    @Override714    public AnswerList<String> readDistinctValuesByApplicationByCriteria(String application, String searchTerm, Map<String, List<String>> individualSearch, String columnName) {715        AnswerList<String> answer = new AnswerList<>();716        MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);717        msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));718        List<String> distinctValues = new ArrayList<>();719        StringBuilder searchSQL = new StringBuilder();720        List<String> individalColumnSearchValues = new ArrayList<>();721        StringBuilder query = new StringBuilder();722        query.append("SELECT distinct ");723        query.append(columnName);724        query.append(" as distinctValues FROM applicationobject ");725        searchSQL.append("WHERE 1=1");726        if (!StringUtil.isNullOrEmpty(application)) {727            searchSQL.append(" and (`Application` = ? )");728        }729        if (!StringUtil.isNullOrEmpty(searchTerm)) {730            searchSQL.append(" and (`Application` like ?");731            searchSQL.append(" or `Object` like ?");732            searchSQL.append(" or `Value` like ?");733            searchSQL.append(" or `ScreenshotFileName` like ?");734            searchSQL.append(" or `UsrCreated` like ?");735            searchSQL.append(" or `DateCreated` like ?");736            searchSQL.append(" or `UsrModif` like ?");737            searchSQL.append(" or `DateModif` like ?)");738        }739        if (individualSearch != null && !individualSearch.isEmpty()) {740            searchSQL.append(" and ( 1=1 ");741            for (Map.Entry<String, List<String>> entry : individualSearch.entrySet()) {742                searchSQL.append(" and ");743                searchSQL.append(SqlUtil.getInSQLClauseForPreparedStatement(entry.getKey(), entry.getValue()));744                individalColumnSearchValues.addAll(entry.getValue());745            }746            searchSQL.append(" )");747        }748        query.append(searchSQL);749        query.append(" order by ").append(columnName).append(" asc");750        // Debug message on SQL.751        if (LOG.isDebugEnabled()) {752            LOG.debug("SQL : " + query.toString());753        }754        try (Connection connection = databaseSpring.connect();755                PreparedStatement preStat = connection.prepareStatement(query.toString());756        		Statement stm = connection.createStatement();) {757            int i = 1;758            if (!StringUtil.isNullOrEmpty(application)) {759                preStat.setString(i++, application);760            }761            if (!StringUtil.isNullOrEmpty(searchTerm)) {762                preStat.setString(i++, "%" + searchTerm + "%");763                preStat.setString(i++, "%" + searchTerm + "%");764                preStat.setString(i++, "%" + searchTerm + "%");765                preStat.setString(i++, "%" + searchTerm + "%");766                preStat.setString(i++, "%" + searchTerm + "%");767                preStat.setString(i++, "%" + searchTerm + "%");768                preStat.setString(i++, "%" + searchTerm + "%");769                preStat.setString(i++, "%" + searchTerm + "%");770            }771            for (String individualColumnSearchValue : individalColumnSearchValues) {772                preStat.setString(i++, individualColumnSearchValue);773            }774            try(ResultSet resultSet = preStat.executeQuery();775            		ResultSet rowSet = stm.executeQuery("SELECT FOUND_ROWS()");) {776            	//gets the data777                while (resultSet.next()) {778                    distinctValues.add(resultSet.getString("distinctValues") == null ? "" : resultSet.getString("distinctValues"));779                }780                int nrTotalRows = 0;781                if (rowSet != null && rowSet.next()) {782                    nrTotalRows = rowSet.getInt(1);783                }784                if (distinctValues.size() >= MAX_ROW_SELECTED) { // Result of SQl was limited by MAX_ROW_SELECTED constrain. That means that we may miss some lines in the resultList.785                    LOG.error("Partial Result in the query.");786                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_WARNING_PARTIAL_RESULT);787                    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", "Maximum row reached : " + MAX_ROW_SELECTED));788                    answer = new AnswerList<>(distinctValues, nrTotalRows);789                } else if (distinctValues.size() <= 0) {790                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);791                    answer = new AnswerList<>(distinctValues, nrTotalRows);792                } else {793                    msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);794                    msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "SELECT"));795                    answer = new AnswerList<>(distinctValues, nrTotalRows);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            }802        } catch (Exception e) {803            LOG.warn("Unable to execute query : " + e.toString());804            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED).resolveDescription("DESCRIPTION",805                    e.toString());806        } finally {807            // We always set the result message808            answer.setResultMessage(msg);809        }810        answer.setResultMessage(msg);811        answer.setDataList(distinctValues);812        return answer;813    }814    private ApplicationObject loadFromResultSet(ResultSet rs) throws SQLException {815        Integer ID = ParameterParserUtil.parseIntegerParam(rs.getString("ID"), -1);816        String application = ParameterParserUtil.parseStringParam(rs.getString("Application"), "");817        String object = ParameterParserUtil.parseStringParam(rs.getString("Object"), "");818        String value = ParameterParserUtil.parseStringParam(rs.getString("Value"), "");819        String screenshotfilename = ParameterParserUtil.parseStringParam(rs.getString("ScreenshotFileName"), "");820        String usrcreated = ParameterParserUtil.parseStringParam(rs.getString("UsrCreated"), "");821        String datecreated = ParameterParserUtil.parseStringParam(rs.getString("DateCreated"), "");822        String usrmodif = ParameterParserUtil.parseStringParam(rs.getString("UsrModif"), "");823        String datemodif = ParameterParserUtil.parseStringParam(rs.getString("DateModif"), "");824        return factoryApplicationObject.create(ID, application, object, value, screenshotfilename, usrcreated, datecreated, usrmodif, datemodif);825    }826}...

loadFromResultSet

Using AI Code Generation

1public ApplicationObject loadFromResultSet(ResultSet rs) throws SQLException {2    String application = ParameterParserUtil.parseStringParam(rs.getString("application"), "");3    String system = ParameterParserUtil.parseStringParam(rs.getString("system"), "");4    String object = ParameterParserUtil.parseStringParam(rs.getString("object"), "");5    String objectID = ParameterParserUtil.parseStringParam(rs.getString("objectID"), "");6    String objectType = ParameterParserUtil.parseStringParam(rs.getString("objectType"), "");7    String objectName = ParameterParserUtil.parseStringParam(rs.getString("objectName"), "");8    String objectDescription = ParameterParserUtil.parseStringParam(rs.getString("objectDescription"), "");9    String objectSystem = ParameterParserUtil.parseStringParam(rs.getString("objectSystem"), "");10    String objectParent = ParameterParserUtil.parseStringParam(rs.getString("objectParent"), "");11    String objectMethod = ParameterParserUtil.parseStringParam(rs.getString("objectMethod"), "");12    String objectServicePath = ParameterParserUtil.parseStringParam(rs.getString("objectServicePath"), "");13    String objectServiceRequest = ParameterParserUtil.parseStringParam(rs.getString("objectServiceRequest"), "");14    String objectServiceResponse = ParameterParserUtil.parseStringParam(rs.getString("objectServiceResponse"), "");15    String objectDatabase = ParameterParserUtil.parseStringParam(rs.getString("objectDatabase"), "");16    String objectDatabaseTable = ParameterParserUtil.parseStringParam(rs.getString("objectDatabaseTable"), "");17    String objectDatabaseColumn = ParameterParserUtil.parseStringParam(rs.getString("objectDatabaseColumn"), "");18    String objectDatabaseKey1 = ParameterParserUtil.parseStringParam(rs.getString("objectDatabaseKey1"), "");19    String objectDatabaseKey2 = ParameterParserUtil.parseStringParam(rs.getString("objectDatabaseKey2"), "");20    String objectDatabaseKey3 = ParameterParserUtil.parseStringParam(rs.getString("objectDatabaseKey3"), "");21    String objectDatabaseKey4 = ParameterParserUtil.parseStringParam(rs.getString("objectDatabaseKey4"), "");22    String objectDatabaseKey5 = ParameterParserUtil.parseStringParam(rs.getString("objectDatabaseKey5"), "");23    String objectDatabaseKey6 = ParameterParserUtil.parseStringParam(rs.getString("objectDatabaseKey6"), "");24    String objectDatabaseKey7 = ParameterParserUtil.parseStringParam(rs.getString("objectDatabaseKey7"), "");25    String objectDatabaseKey8 = ParameterParserUtil.parseStringParam(rs.getString("object

loadFromResultSet

Using AI Code Generation

1public class ApplicationObjectDAO extends DAO implements IApplicationObjectDAO {2    private static final Logger LOG = LogManager.getLogger(ApplicationObjectDAO.class);3    private final String OBJECT_TABLE = "applicationobject";4    private final String OBJECT_COLUMN = "applicationobjectcolumn";5    private final String OBJECT_COLUMN_VALUE = "applicationobjectcolumnvalue";6    private final String OBJECT_PROPERTY = "applicationobjectproperty";7    private final String OBJECT_PROPERTY_VALUE = "applicationobjectpropertyvalue";8    private final String OBJECT_PROPERTY_USAGE = "applicationobjectpropertyusage";9    private final int MAX_ROW_SELECTED = 100000;10    private final String[] OBJECT_COLUMNS = new String[]{"id", "system", "object", "description", "type", "database", "script"};11    private final String[] OBJECT_COLUMN_COLUMNS = new String[]{"id", "object", "column", "sort", "description", "type", "database", "script", "length", "format"};12    private final String[] OBJECT_COLUMN_VALUE_COLUMNS = new String[]{"id", "object", "column", "value", "description", "sort"};13    private final String[] OBJECT_PROPERTY_COLUMNS = new String[]{"id", "object", "property", "sort", "description", "type", "database", "script", "length", "format"};14    private final String[] OBJECT_PROPERTY_VALUE_COLUMNS = new String[]{"id", "object", "property", "value", "description", "sort"};15    private final String[] OBJECT_PROPERTY_USAGE_COLUMNS = new String[]{"id", "object", "property", "objectusage", "propertyusage", "sort"};16    public AnswerList readBySystemByType(String system, String type) {17        AnswerList ans = new AnswerList();18        List<ApplicationObject> applicationObjectList = new ArrayList<ApplicationObject>();19        final String query = "SELECT * FROM `" + OBJECT_TABLE + "` WHERE `system` = ? AND `type` = ?";20        try (Connection connection = this.databaseSpring.connect();21                PreparedStatement preStat = connection.prepareStatement(query);) {22            preStat.setString(1, system);23            preStat.setString(2, type);24            try (ResultSet resultSet = preStat.executeQuery()) {25                while (resultSet.next()) {26                    applicationObjectList.add(this.loadFromResultSet(resultSet));27                }28            }29            ans.setDataList(applicationObjectList);30        } catch (SQLException exception) {31            LOG.warn("Unable to execute query : " + exception.toString());

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.