/** * @param column * @param sortingStatus * @return */ private String toJSon(int column, int sortingStatus) { try { JSONObject response = new JSONObject(); JSONObject content = new JSONObject(); JSONArray array = new JSONArray(); if (column == -1 || sortingStatus == -2) { array.put(tableModel.getHeaderVector()); } else if (column > -1 && sortingStatus > -2 && sortingStatus < 2 /*-1,0 or 1 */) { Vector<String> vectorCopy = (Vector<String>) tableModel.getHeaderVector().clone(); for (int header = 0; header < tableModel.getHeaderVector().size(); header++) { if (header == column) { switch (sortingStatus) { case -1: vectorCopy.set(header, vectorCopy.elementAt(header) + " ↓"); break; case 0: vectorCopy.set(header, vectorCopy.elementAt(header) + " ↑↓"); break; case 1: vectorCopy.set(header, vectorCopy.elementAt(header) + " ↑"); break; } } } array.put(vectorCopy); } for (int row = 0; row < tableModel.getDataVector().size(); row++) { array.put((Vector<Object>) tableModel.getDataVector().get(row)); } content.put("table", array); content.put("isAdmin", new Boolean(isAdmin).toString()); response.put("result", content); return response.toString(); } catch (JSONException e) { e.printStackTrace(); } return null; }
/** * Use the current sql command with a Count(*) to return the number of results of the current <br> * sql command * * @throws SQLException */ private void countResults(HttpSession session, Connection con) throws SQLException { String currentSqlCommand = (String) session.getAttribute("countSqlCommand"); Vector sqlParams = (Vector) session.getAttribute("countSqlParams"); int fromPosition = currentSqlCommand.toLowerCase().indexOf("from"); String secondPart = currentSqlCommand.substring(fromPosition); // AMS - MySQL does not like spaces between COUNT and (*) String SQLCommandCountStar = "SELECT COUNT(*) " + secondPart; if (logger.isDebugEnabled()) { logger.debug("GetTableServlet: SQLCommandCountStar: " + SQLCommandCountStar); } logger.info("GetTableServlet: SQLCommandCountStar: " + SQLCommandCountStar); PreparedStatement pstmt = null; ResultSet resultSet = null; try { pstmt = con.prepareStatement(SQLCommandCountStar); pstmt = TableModel.setPStmtParameters(pstmt, sqlParams); resultSet = pstmt.executeQuery(); resultSet.next(); int numberOfResults = resultSet.getInt(1); session.setAttribute("numberOfResults", new Integer(numberOfResults)); } catch (SQLException ex) { throw ex; } finally { try { if (resultSet != null) { resultSet.close(); } if (pstmt != null) { pstmt.close(); } } catch (SQLException ex) { logger.error(ex); } } }
/** * <b>sqlCommandProcessing</b><br> * Create the sql command beginning with the sql command specified in the web.xml file and * applying all the options <br> * specified by the user. This command also apply a limit in the results to avoid to overload the * server */ private void sqlCommandProcessing( HttpSession session, int optionNumberInt, String page, String numberResultsByPage, String databaseType) { int parameterNumber = 1; reInitSqlCommand(session); String currentSqlCommand = (String) session.getAttribute("currentSqlCommand"); Vector currentSqlParams = new Vector(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); if (logger.isDebugEnabled()) { logger.debug("SQL-BASE: " + currentSqlCommand); } // If search criteria was entered, build the WHERE clause to // include the filter data entered if (map != null && optionNumberInt > 0) { String commandTemp = currentSqlCommand; if (currentSqlCommand.toLowerCase().indexOf("where") > -1) { commandTemp += " AND "; } else { commandTemp += " WHERE "; } while (parameterNumber <= optionNumberInt) { if (map.containsKey("option" + parameterNumber)) { if (map.containsKey("and-or" + parameterNumber)) { commandTemp += " " + map.get("and-or" + parameterNumber) + " "; } String parameterName = map.get("option" + parameterNumber).toString(); if (parameterName.equals("ip")) { commandTemp += " main.ip LIKE ?"; currentSqlParams.add( TableModel.setSqlParam( TableModel.STRING, "%" + map.get("value" + parameterNumber).toString() + "%")); } else if (parameterName.equals("pass")) { commandTemp += " main.pass = ?"; currentSqlParams.add( TableModel.setSqlParam( TableModel.BOOLEAN, map.get("value" + parameterNumber).toString())); } else if (parameterName.equals("test")) { commandTemp += " main.test = ?"; currentSqlParams.add( TableModel.setSqlParam( TableModel.STRING, map.get("value" + parameterNumber).toString())); } else if (parameterName.equals("company")) { commandTemp += " ip.company_name = ?"; currentSqlParams.add( TableModel.setSqlParam( TableModel.STRING, map.get("value" + parameterNumber).toString())); } else if (parameterName.equals("date1")) { String date1 = map.get("value" + parameterNumber).toString().trim(); java.util.Date d1; GregorianCalendar endday = new GregorianCalendar(); try { d1 = sdf.parse(date1); if (map.containsKey("option" + (parameterNumber + 1)) && map.get("option" + (parameterNumber + 1)).toString().equals("date2")) { if (map.containsKey("value" + (parameterNumber + 1))) { String date2 = map.get("value" + (parameterNumber + 1)).toString().trim(); java.util.Date d2 = sdf.parse(date2); if (d2.getTime() > d1.getTime()) { endday.setTimeInMillis(d2.getTime()); endday.roll(Calendar.DAY_OF_MONTH, +1); d2 = endday.getTime(); commandTemp += " timereceived >= ? and timereceived < ?"; } else if (d2.getTime() < d1.getTime()) { endday.setTimeInMillis(d1.getTime()); endday.roll(Calendar.DAY_OF_MONTH, +1); d1 = endday.getTime(); commandTemp += " timereceived < ? and timereceived >= ?"; } else { // From Date same as To Date endday.setTimeInMillis(d2.getTime()); endday.roll(Calendar.DAY_OF_MONTH, +1); d2 = endday.getTime(); commandTemp += " timereceived >= ? and timereceived < ?"; } currentSqlParams.add(TableModel.setSqlParam(TableModel.DATE, d1)); currentSqlParams.add(TableModel.setSqlParam(TableModel.DATE, d2)); map.remove("value" + (parameterNumber + 1)); map.remove("option" + (parameterNumber + 1)); if (map.containsKey("and-or" + (parameterNumber + 1))) { map.remove("and-or" + (parameterNumber + 1)); } } } else { // To Date not provided commandTemp += " timereceived >= ? "; currentSqlParams.add(TableModel.setSqlParam(TableModel.DATE, d1)); } } catch (ParseException e) { logger.error("Date Parsing Error: " + e); } } else if (parameterName.equals("date2")) { // From Date not provided String date1 = map.get("value" + parameterNumber).toString(); java.util.Date d1 = new java.util.Date(); try { d1 = sdf.parse(date1); } catch (ParseException ex) { logger.error("Date Parsing Error: " + ex); } GregorianCalendar endday = new GregorianCalendar(); endday.setTime(d1); endday.roll(Calendar.DAY_OF_MONTH, +1); commandTemp += " timereceived < ?"; currentSqlParams.add(TableModel.setSqlParam(TableModel.DATE, endday.getTime())); } else if (parameterName.equals("date")) { String value = map.get("value" + parameterNumber).toString(); GregorianCalendar today = new GregorianCalendar(); GregorianCalendar endday = new GregorianCalendar(); if (value.equals("today")) { endday.roll(Calendar.DAY_OF_MONTH, +1); } else if (value.equals("yesterday")) { today.roll(Calendar.DAY_OF_MONTH, -1); } else if (value.equals("2days")) { today.roll(Calendar.DAY_OF_MONTH, -2); endday.roll(Calendar.DAY_OF_MONTH, -1); } else if (value.equals("3days")) { today.roll(Calendar.DAY_OF_MONTH, -3); endday.roll(Calendar.DAY_OF_MONTH, -2); } commandTemp += " timereceived >= ? and timereceived < ?"; currentSqlParams.add(TableModel.setSqlParam(TableModel.DATE, today.getTime())); currentSqlParams.add(TableModel.setSqlParam(TableModel.DATE, endday.getTime())); } } parameterNumber++; } if (logger.isDebugEnabled()) { logger.debug("SQL-WITH-FILTERS: " + commandTemp); } currentSqlCommand = commandTemp; } // Save SQL for count(*) command session.setAttribute("countSqlCommand", currentSqlCommand); session.setAttribute("countSqlParams", currentSqlParams); // Add Paging logic to the SQL Statement if (logger.isDebugEnabled()) { logger.debug("GetTableServlet: SQLRequest before paging: >" + currentSqlCommand); } int nbResByPage = MAX_RESULTS_BY_PAGE; if (numberResultsByPage != null) { nbResByPage = new Integer(numberResultsByPage).intValue(); } int pageNumber = 0; if (page != null) { pageNumber = new Integer(page).intValue(); } // Add database specific paging logic to the SQL command HashMap sqlMap = TableModel.getSQLWithPaging( databaseType, currentSqlCommand, currentSqlParams, pageNumber, nbResByPage); StringBuffer completeSQL = (StringBuffer) sqlMap.get("completeSQL"); Vector<HashMap> completeSqlParams = (Vector<HashMap>) sqlMap.get("completeSqlParams"); // Save the SQL Command session.setAttribute("currentSqlCommand", completeSQL.toString()); session.setAttribute("currentSqlParams", completeSqlParams); if (logger.isInfoEnabled()) { logger.debug("LogBrowser Page: " + page); logger.info("LogBrowser SQL (with paging): " + completeSQL); } }