Java tutorial
/******************************************************************************* * Copyright 2016 Thomas Schreiber * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. *******************************************************************************/ package at.alladin.rmbt.statisticServer.opendata; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Arrays; import java.util.HashMap; import java.util.LinkedList; import java.util.List; import java.util.Objects; import org.json.JSONArray; import org.json.JSONObject; import org.restlet.data.Form; import org.restlet.data.Status; import org.restlet.resource.Get; import com.google.gson.GsonBuilder; import com.google.gson.JsonObject; import at.alladin.rmbt.shared.cache.CacheHelper; import at.alladin.rmbt.statisticServer.ServerResource; public class OpenTestStatisticsResource extends ServerResource { private static final int CACHE_EXP = 300; private final CacheHelper cache = CacheHelper.getInstance(); //Intervals returned by the resource. Have to be ordered ascending private final int[] intervalsMins = { 5, 30, 60, 12 * 60, //12h 60 * 24, //24h 60 * 24 * 7 //7d }; @Get("json") public String request(final String entity) { addAllowOrigin(); final Form getParameters = getRequest().getResourceRef().getQueryAsForm(); final QueryParser qp = new QueryParser(); // set transformator for time to allow for broader caching (a few // minutes) qp.registerSingleParameterTransformator("time", new QueryParser.SingleParameterTransformator() { private final static int FEW_MINUTES = 60 * 1 * 1000; @Override public void transform(QueryParser.SingleParameter param) { // round to 1h long timestamp = Long.parseLong(param.getValue()); timestamp = timestamp - (timestamp % FEW_MINUTES); param.setValue(Long.toString(timestamp)); } }); qp.parseQuery(getParameters); // try cache first String cacheString = (String) cache.get("opentest-statistics-" + qp.hashCode()); if (cacheString != null) { // System.out.println("cache hit"); return cacheString; } try { String json = this.getStatistics(qp); // put in cache cache.set("opentest-statistics-" + qp.hashCode(), CACHE_EXP, json); return json; } catch (SQLException e) { e.printStackTrace(); setStatus(Status.SERVER_ERROR_INTERNAL); return "\"error\":\"internal error\""; } } /** * Get JSON array containing conducted tests at different time intervals * * @param qp * @return * @throws SQLException */ private String getStatistics(QueryParser qp) throws SQLException { // build the sql query StringBuilder selectStatement = new StringBuilder(); selectStatement.append("SELECT CASE "); for (int i = 0; i < intervalsMins.length; i++) { selectStatement.append(String.format( "WHEN (time > (current_timestamp - interval '%d minutes')) THEN '%d' ", intervalsMins[i], i)); } selectStatement.append(" END as ident "); // concatenate sql with the CASE statement String query = "SELECT ident, count(ident) as cnt FROM (" + selectStatement.toString() + "FROM test t " + "LEFT JOIN network_type nt ON nt.uid=t.network_type" + " LEFT JOIN device_map adm ON adm.codename=t.model" + " LEFT JOIN test_server ts ON ts.uid=t.server_id" + " LEFT JOIN provider prov ON provider_id = prov.uid " + " LEFT JOIN provider mprov ON mobile_provider_id = mprov.uid" + " WHERE" + " t.deleted = false" + " AND status = 'FINISHED' " + qp.getWhereClause("AND") + "AND time > (current_timestamp - interval '" + intervalsMins[intervalsMins.length - 1] + " minutes'" + ")" + ") a GROUP BY ident ORDER BY ident ASC;"; PreparedStatement stmt = conn.prepareStatement(query); qp.fillInWhereClause(stmt, 1); ResultSet rs = stmt.executeQuery(); HashMap<Integer, Long> map = new HashMap<>(); for (int i = 0; i < intervalsMins.length; i++) { map.put(intervalsMins[i], 0l); } long tests = 0; while (rs.next()) { int interval = intervalsMins[rs.getInt("ident")]; tests += rs.getLong("cnt"); map.put(interval, tests); } //now, change keys to different time measurements depending on #mins for better readability HashMap<String, Long> retMap = new HashMap<>(); for (Integer interval : map.keySet()) { long t = map.get(interval); //use different time measurements depending on #mins if (interval % (60 * 24) == 0 && (interval / (60 * 24)) > 1) { retMap.put((interval / (60 * 24)) + "d", t); } else if ((interval % 60) == 0 && (interval / 60) > 1) { retMap.put((interval / 60) + "h", t); } else { retMap.put(interval + "min", t); } } return new GsonBuilder().create().toJson(retMap); } }