X-Git-Url: https://gerrit.onap.org/r/gitweb?a=blobdiff_plain;f=datarouter-prov%2Fsrc%2Fmain%2Fjava%2Forg%2Fonap%2Fdmaap%2Fdatarouter%2Fprovisioning%2FStatisticsServlet.java;h=76991ca0bde993243ec10fdfda90c93225fdd46a;hb=68a9ca240970fceaf12bbe91b7bad8e1d98ecd93;hp=fcdca0a3bd013e769533ce3c643a574dbbe68635;hpb=f94a3f35ccf9f95455b2840e4465a71b0bb5c71d;p=dmaap%2Fdatarouter.git diff --git a/datarouter-prov/src/main/java/org/onap/dmaap/datarouter/provisioning/StatisticsServlet.java b/datarouter-prov/src/main/java/org/onap/dmaap/datarouter/provisioning/StatisticsServlet.java index fcdca0a3..76991ca0 100755 --- a/datarouter-prov/src/main/java/org/onap/dmaap/datarouter/provisioning/StatisticsServlet.java +++ b/datarouter-prov/src/main/java/org/onap/dmaap/datarouter/provisioning/StatisticsServlet.java @@ -42,8 +42,8 @@ import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.json.JSONException; import org.onap.dmaap.datarouter.provisioning.beans.EventLogRecord; -import org.onap.dmaap.datarouter.provisioning.utils.DB; import org.onap.dmaap.datarouter.provisioning.utils.LOGJSONObject; +import org.onap.dmaap.datarouter.provisioning.utils.ProvDbUtils; /** @@ -63,6 +63,21 @@ public class StatisticsServlet extends BaseServlet { private static final String FMT2 = "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"; public static final String FEEDID = "FEEDID"; + //sql Strings + private static final String SQL_SELECT_NAME = "SELECT (SELECT NAME FROM FEEDS AS f WHERE f.FEEDID in("; + private static final String SQL_FEED_ID = ") and f.FEEDID=e.FEEDID) AS FEEDNAME, e.FEEDID as FEEDID, "; + private static final String SQL_SELECT_COUNT = "(SELECT COUNT(*) FROM LOG_RECORDS AS c WHERE c.FEEDID in("; + private static final String SQL_TYPE_PUB = ") and c.FEEDID=e.FEEDID AND c.TYPE='PUB') AS FILES_PUBLISHED,"; + private static final String SQL_SELECT_SUM = "(SELECT SUM(content_length) FROM LOG_RECORDS AS c WHERE c.FEEDID in("; + private static final String SQL_PUBLISH_LENGTH = ") and c.FEEDID=e.FEEDID AND c.TYPE='PUB') AS PUBLISH_LENGTH, COUNT(e.EVENT_TIME) as FILES_DELIVERED,"; + private static final String SQL_SUBSCRIBER_URL = " sum(m.content_length) as DELIVERED_LENGTH, SUBSTRING_INDEX(e.REQURI,'/',+3) as SUBSCRIBER_URL,"; + private static final String SQL_SUB_ID = " e.DELIVERY_SUBID as SUBID, "; + private static final String SQL_DELIVERY_TIME = " e.EVENT_TIME AS PUBLISH_TIME, m.EVENT_TIME AS DELIVERY_TIME, "; + private static final String SQL_AVERAGE_DELAY = " AVG(e.EVENT_TIME - m.EVENT_TIME)/1000 as AverageDelay FROM LOG_RECORDS"; + private static final String SQL_JOIN_RECORDS = " e JOIN LOG_RECORDS m ON m.PUBLISH_ID = e.PUBLISH_ID AND e.FEEDID IN ("; + private static final String SQL_STATUS_204 = " AND m.STATUS=204 AND e.RESULT=204 "; + private static final String SQL_GROUP_SUB_ID = " group by SUBID"; + /** * DELETE a logging URL -- not supported. @@ -86,7 +101,7 @@ public class StatisticsServlet extends BaseServlet { Map map = buildMapFromRequest(req); if (map.get("err") != null) { sendResponseError(resp, HttpServletResponse.SC_BAD_REQUEST, - "Invalid arguments: " + map.get("err"), eventlogger); + "Invalid arguments: " + map.get("err"), eventlogger); return; } // check Accept: header?? @@ -108,7 +123,7 @@ public class StatisticsServlet extends BaseServlet { try { groupid1 = this.getFeedIdsByGroupId(Integer.parseInt(req.getParameter(GROUPID))); map.put(FEEDIDS, groupid1.toString()); - } catch (NumberFormatException | SQLException e) { + } catch (NumberFormatException e) { eventlogger.error("PROV0172 StatisticsServlet.doGet: " + e.getMessage(), e); } } @@ -119,7 +134,7 @@ public class StatisticsServlet extends BaseServlet { groupid1.append(","); groupid1.append(req.getParameter(FEEDID).replace("|", ",")); map.put(FEEDIDS, groupid1.toString()); - } catch (NumberFormatException | SQLException e) { + } catch (NumberFormatException e) { eventlogger.error("PROV0173 StatisticsServlet.doGet: " + e.getMessage(), e); } } @@ -169,7 +184,7 @@ public class StatisticsServlet extends BaseServlet { * @throws IOException input/output exception * @throws SQLException SQL exception */ - public void rsToCSV(ResultSet rs, ServletOutputStream out) throws IOException, SQLException { + private void rsToCSV(ResultSet rs, ServletOutputStream out) throws IOException, SQLException { String header = "FEEDNAME,FEEDID,FILES_PUBLISHED,PUBLISH_LENGTH, FILES_DELIVERED, " + "DELIVERED_LENGTH, SUBSCRIBER_URL, SUBID, PUBLISH_TIME,DELIVERY_TIME, AverageDelay\n"; out.write(header.getBytes()); @@ -237,41 +252,24 @@ public class StatisticsServlet extends BaseServlet { /** * getFeedIdsByGroupId - Getting FEEDID's by GROUP ID. * - * @throws SQLException Query SQLException. + * @param groupIds Integer ref of Group */ - private StringBuilder getFeedIdsByGroupId(int groupIds) throws SQLException { - DB db = null; - Connection conn = null; - ResultSet resultSet = null; - String sqlGoupid = null; + private StringBuilder getFeedIdsByGroupId(int groupIds) { StringBuilder feedIds = new StringBuilder(); - try { - db = new DB(); - conn = db.getConnection(); - sqlGoupid = " SELECT FEEDID from FEEDS WHERE GROUPID = ?"; - try (PreparedStatement prepareStatement = conn.prepareStatement(sqlGoupid)) { - prepareStatement.setInt(1, groupIds); - resultSet = prepareStatement.executeQuery(); + try (Connection conn = ProvDbUtils.getInstance().getConnection(); + PreparedStatement prepareStatement = conn.prepareStatement( + " SELECT FEEDID from FEEDS WHERE GROUPID = ?")) { + prepareStatement.setInt(1, groupIds); + try (ResultSet resultSet = prepareStatement.executeQuery()) { while (resultSet.next()) { feedIds.append(resultSet.getInt(FEEDID)); feedIds.append(","); } - feedIds.deleteCharAt(feedIds.length() - 1); - eventlogger.info("PROV0177 StatisticsServlet.getFeedIdsByGroupId: feedIds = " + feedIds.toString()); } + feedIds.deleteCharAt(feedIds.length() - 1); + eventlogger.info("PROV0177 StatisticsServlet.getFeedIdsByGroupId: feedIds = " + feedIds.toString()); } catch (SQLException e) { eventlogger.error("PROV0175 StatisticsServlet.getFeedIdsByGroupId: " + e.getMessage(), e); - } finally { - try { - if (resultSet != null) { - resultSet.close(); - } - if (conn != null) { - db.release(conn); - } - } catch (Exception e) { - eventlogger.error("PROV0176 StatisticsServlet.getFeedIdsByGroupId: " + e.getMessage(), e); - } } return feedIds; } @@ -285,14 +283,11 @@ public class StatisticsServlet extends BaseServlet { private String queryGeneretor(Map map) throws ParseException { String sql; - String eventType = null; String feedids = null; String startTime = null; String endTime = null; String subid = " "; - if (map.get(EVENT_TYPE) != null) { - eventType = map.get(EVENT_TYPE); - } + if (map.get(FEEDIDS) != null) { feedids = map.get(FEEDIDS); } @@ -302,9 +297,6 @@ public class StatisticsServlet extends BaseServlet { if (map.get(END_TIME) != null) { endTime = map.get(END_TIME); } - if ("all".equalsIgnoreCase(eventType)) { - eventType = "PUB','DEL, EXP, PBF"; - } if (map.get(SUBID) != null) { subid = map.get(SUBID); } @@ -313,20 +305,12 @@ public class StatisticsServlet extends BaseServlet { if (endTime == null && startTime == null) { - sql = "SELECT (SELECT NAME FROM FEEDS AS f WHERE f.FEEDID in(" + feedids - + ") and f.FEEDID=e.FEEDID) AS FEEDNAME, e.FEEDID as FEEDID, " - + "(SELECT COUNT(*) FROM LOG_RECORDS AS c WHERE c.FEEDID in(" - + feedids - + ") and c.FEEDID=e.FEEDID AND c.TYPE='PUB') AS FILES_PUBLISHED,(SELECT SUM(content_length)" - + " FROM LOG_RECORDS AS c WHERE c.FEEDID in(" - + feedids - + ") and c.FEEDID=e.FEEDID AND c.TYPE='PUB') AS PUBLISH_LENGTH, COUNT(e.EVENT_TIME) as FILES_DELIVERED," - + "sum(m.content_length) as DELIVERED_LENGTH,SUBSTRING_INDEX(e.REQURI,'/',+3) as SUBSCRIBER_URL," - + " e.DELIVERY_SUBID as SUBID, " - + "e.EVENT_TIME AS PUBLISH_TIME, m.EVENT_TIME AS DELIVERY_TIME, " - + " AVG(e.EVENT_TIME - m.EVENT_TIME)/1000 as AverageDelay FROM LOG_RECORDS" - + " e JOIN LOG_RECORDS m ON m.PUBLISH_ID = e.PUBLISH_ID AND e.FEEDID IN (" - + feedids + ") " + subid + " AND m.STATUS=204 AND e.RESULT=204 group by SUBID"; + sql = SQL_SELECT_NAME + feedids + SQL_FEED_ID + SQL_SELECT_COUNT + feedids + SQL_TYPE_PUB + + SQL_SELECT_SUM + + feedids + SQL_PUBLISH_LENGTH + + SQL_SUBSCRIBER_URL + SQL_SUB_ID + SQL_DELIVERY_TIME + SQL_AVERAGE_DELAY + SQL_JOIN_RECORDS + + feedids + ") " + subid + + SQL_STATUS_204 + SQL_GROUP_SUB_ID; return sql; } else if (startTime != null && endTime == null) { @@ -336,22 +320,12 @@ public class StatisticsServlet extends BaseServlet { long currentTimeInMilli = cal.getTimeInMillis(); long compareTime = currentTimeInMilli - inputTimeInMilli; - sql = "SELECT (SELECT NAME FROM FEEDS AS f WHERE f.FEEDID in(" + feedids - + ") and f.FEEDID=e.FEEDID) AS FEEDNAME, e.FEEDID as FEEDID, " - + "(SELECT COUNT(*) FROM LOG_RECORDS AS c WHERE c.FEEDID in(" - + feedids - + ") and c.FEEDID=e.FEEDID AND c.TYPE='PUB') AS FILES_PUBLISHED," - + "(SELECT SUM(content_length) FROM LOG_RECORDS AS c WHERE c.FEEDID in(" - + feedids - + ") and c.FEEDID=e.FEEDID AND c.TYPE='PUB') AS PUBLISH_LENGTH, COUNT(e.EVENT_TIME) as FILES_DELIVERED," - + "sum(m.content_length) as DELIVERED_LENGTH,SUBSTRING_INDEX(e.REQURI,'/',+3) as SUBSCRIBER_URL," - + " e.DELIVERY_SUBID as SUBID, " - + "e.EVENT_TIME AS PUBLISH_TIME, m.EVENT_TIME AS DELIVERY_TIME, " - + "AVG(e.EVENT_TIME - m.EVENT_TIME)/1000 as AverageDelay " - + "FROM LOG_RECORDS e JOIN LOG_RECORDS m ON m.PUBLISH_ID = e.PUBLISH_ID AND e.FEEDID IN (" - + feedids + ") " + subid + " AND m.STATUS=204 AND e.RESULT=204 and e.event_time>=" - + compareTime + " group by SUBID"; - + sql = SQL_SELECT_NAME + feedids + SQL_FEED_ID + SQL_SELECT_COUNT + feedids + SQL_TYPE_PUB + + SQL_SELECT_SUM + + feedids + SQL_PUBLISH_LENGTH + + SQL_SUBSCRIBER_URL + SQL_SUB_ID + SQL_DELIVERY_TIME + SQL_AVERAGE_DELAY + SQL_JOIN_RECORDS + + feedids + ") " + subid + + SQL_STATUS_204 + " and e.event_time>=" + compareTime + SQL_GROUP_SUB_ID; return sql; } else { @@ -362,23 +336,11 @@ public class StatisticsServlet extends BaseServlet { long startInMillis = startDate.getTime(); long endInMillis = endDate.getTime(); - sql = "SELECT (SELECT NAME FROM FEEDS AS f WHERE f.FEEDID in(" + feedids - + ") and f.FEEDID=e.FEEDID) AS FEEDNAME, e.FEEDID as FEEDID, " - + "(SELECT COUNT(*) FROM LOG_RECORDS AS c WHERE c.FEEDID in(" - + feedids - + ") and c.FEEDID=e.FEEDID AND c.TYPE='PUB') AS FILES_PUBLISHED," - + "(SELECT SUM(content_length) FROM LOG_RECORDS AS c WHERE c.FEEDID in(" - + feedids - + ") and c.FEEDID=e.FEEDID AND c.TYPE='PUB') AS PUBLISH_LENGTH, COUNT(e.EVENT_TIME) as FILES_DELIVERED," - + "sum(m.content_length) as DELIVERED_LENGTH," - + "SUBSTRING_INDEX(e.REQURI,'/',+3) as SUBSCRIBER_URL," - + "e.DELIVERY_SUBID as SUBID, " - + "e.EVENT_TIME AS PUBLISH_TIME, m.EVENT_TIME AS DELIVERY_TIME, " - + "AVG(e.EVENT_TIME - m.EVENT_TIME)/1000 as AverageDelay FROM LOG_RECORDS" - + " e JOIN LOG_RECORDS m ON m.PUBLISH_ID = e.PUBLISH_ID AND e.FEEDID IN (" - + feedids + ")" + subid + " AND m.STATUS=204 AND e.RESULT=204 and e.event_time between " + startInMillis - + " and " + endInMillis + " group by SUBID"; - + sql = SQL_SELECT_NAME + feedids + SQL_FEED_ID + SQL_SELECT_COUNT + feedids + SQL_TYPE_PUB + + SQL_SELECT_SUM + + feedids + SQL_PUBLISH_LENGTH + SQL_SUBSCRIBER_URL + + SQL_SUB_ID + SQL_DELIVERY_TIME + SQL_AVERAGE_DELAY + SQL_JOIN_RECORDS + feedids + ")" + subid + SQL_STATUS_204 + +" and e.event_time between " + startInMillis + " and " + endInMillis + SQL_GROUP_SUB_ID; return sql; } @@ -538,29 +500,27 @@ public class StatisticsServlet extends BaseServlet { } private void getRecordsForSQL(Map map, String outputType, ServletOutputStream out, - HttpServletResponse resp) { + HttpServletResponse resp) { try { String filterQuery = this.queryGeneretor(map); eventlogger.debug("SQL Query for Statistics resultset. " + filterQuery); intlogger.debug(filterQuery); long start = System.currentTimeMillis(); - DB db = new DB(); - try (Connection conn = db.getConnection()) { - try (ResultSet rs = conn.prepareStatement(filterQuery).executeQuery()) { - if ("csv".equals(outputType)) { - resp.setContentType("application/octet-stream"); - Date date = new Date(); - SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-YYYY HH:mm:ss"); - resp.setHeader("Content-Disposition", - "attachment; filename=\"result:" + dateFormat.format(date) + ".csv\""); - eventlogger.info("Generating CSV file from Statistics resultset"); - rsToCSV(rs, out); - } else { - eventlogger.info("Generating JSON for Statistics resultset"); - this.rsToJson(rs, out); - } + try (Connection conn = ProvDbUtils.getInstance().getConnection(); + PreparedStatement ps = conn.prepareStatement(filterQuery); + ResultSet rs = ps.executeQuery()) { + if ("csv".equals(outputType)) { + resp.setContentType("application/octet-stream"); + Date date = new Date(); + SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-YYYY HH:mm:ss"); + resp.setHeader("Content-Disposition", + "attachment; filename=\"result:" + dateFormat.format(date) + ".csv\""); + eventlogger.info("Generating CSV file from Statistics resultset"); + rsToCSV(rs, out); + } else { + eventlogger.info("Generating JSON for Statistics resultset"); + this.rsToJson(rs, out); } - db.release(conn); } catch (SQLException e) { eventlogger.error("SQLException:" + e); }