List of usage examples for java.sql PreparedStatement getResultSet
ResultSet getResultSet() throws SQLException;
ResultSet object. From source file:org.zaproxy.zap.extension.sse.db.TableEventStream.java
/** * Retrieves list of {@link ServerSentEvent}, but loads only parts of the payload. * * @param criteria//from w w w. j a v a 2 s. c om * @param inScopeStreamIds * @param offset * @param limit * @param payloadPreviewLength * @return Events that fulfill given template. * @throws DatabaseException */ public synchronized List<ServerSentEvent> getEvents(ServerSentEvent criteria, List<Integer> inScopeStreamIds, int offset, int limit, int payloadPreviewLength) throws DatabaseException { try { String query = "SELECT e.event_id, e.stream_id, e.timestamp, e.last_event_id, e.event_type, e.data, e.reconnection_time, e.raw_event " + "FROM event_stream_event AS e " + "<where> " + "ORDER BY e.timestamp, e.stream_id, e.event_id " + "LIMIT ? " + "OFFSET ?"; PreparedStatement stmt; try { stmt = buildEventCriteriaStatement(query, criteria, inScopeStreamIds); } catch (DatabaseException e) { if (getConnection().isClosed()) { return new ArrayList<>(0); } throw e; } try { int paramsCount = stmt.getParameterMetaData().getParameterCount(); stmt.setInt(paramsCount - 1, limit); stmt.setInt(paramsCount, offset); stmt.execute(); return buildEvents(stmt.getResultSet(), true, payloadPreviewLength); } finally { stmt.close(); } } catch (SQLException e) { throw new DatabaseException(e); } }
From source file:org.apache.calcite.avatica.jdbc.JdbcMeta.java
public Frame fetch(StatementHandle h, List<TypedValue> parameterValues, long offset, int fetchMaxRowCount) { if (LOG.isTraceEnabled()) { LOG.trace("fetching " + h + " offset:" + offset + " fetchMaxRowCount:" + fetchMaxRowCount); }/* w ww. j a v a2 s . c o m*/ try { final StatementInfo statementInfo = Objects.requireNonNull(statementCache.getIfPresent(h.id), "Statement not found, potentially expired. " + h); if (statementInfo.resultSet == null || parameterValues != null) { if (statementInfo.statement instanceof PreparedStatement) { final PreparedStatement preparedStatement = (PreparedStatement) statementInfo.statement; if (parameterValues != null) { for (int i = 0; i < parameterValues.size(); i++) { TypedValue o = parameterValues.get(i); preparedStatement.setObject(i + 1, o.toJdbc(calendar)); } } if (preparedStatement.execute()) { statementInfo.resultSet = preparedStatement.getResultSet(); } } } if (statementInfo.resultSet == null) { return Frame.EMPTY; } else { return JdbcResultSet.frame(statementInfo.resultSet, offset, fetchMaxRowCount, calendar); } } catch (SQLException e) { throw propagate(e); } }
From source file:com.cloudera.sqoop.tool.EvalSqlTool.java
@Override /** {@inheritDoc} */ public int run(SqoopOptions options) { if (!init(options)) { return 1; }/*from w w w . ja v a2 s.c om*/ PreparedStatement stmt = null; ResultSet rs = null; PrintWriter pw = null; try { Connection c = manager.getConnection(); String query = options.getSqlQuery(); LOG.debug("SQL query: " + query); stmt = c.prepareStatement(query); boolean resultType = stmt.execute(); // Iterate over all the results from this statement. while (true) { LOG.debug("resultType=" + resultType); if (!resultType) { // This result was an update count. int updateCount = stmt.getUpdateCount(); LOG.debug("updateCount=" + updateCount); if (updateCount == -1) { // We are done iterating over results from this statement. c.commit(); break; } else { LOG.info(updateCount + " row(s) updated."); } } else { // This yields a ResultSet. rs = stmt.getResultSet(); pw = new PrintWriter(System.out, true); new ResultSetPrinter().printResultSet(pw, rs); pw.close(); pw = null; } resultType = stmt.getMoreResults(); } } catch (IOException ioe) { LOG.warn("IOException formatting results: " + StringUtils.stringifyException(ioe)); return 1; } catch (SQLException sqlE) { LOG.warn("SQL exception executing statement: " + StringUtils.stringifyException(sqlE)); return 1; } finally { if (null != pw) { pw.close(); } if (null != rs) { try { rs.close(); } catch (SQLException sqlE) { LOG.warn("SQL exception closing ResultSet: " + StringUtils.stringifyException(sqlE)); } } if (null != stmt) { try { stmt.close(); } catch (SQLException sqlE) { LOG.warn("SQL exception closing statement: " + StringUtils.stringifyException(sqlE)); } } destroy(options); } return 0; }
From source file:com.tesora.dve.common.DBHelper.java
private boolean executePrepared(PreparedStatement ps, String query, List<Object> params) throws SQLException, PEException { if (ps == null) { throw new PEException("A prepared statement is not available to be executed - call prepare first"); }/*from w w w. j ava2 s. c om*/ if (logger.isDebugEnabled() && query != null) logger.debug("Command: " + query); for (int i = 0; i < params.size(); i++) { ps.setObject(i + 1, params.get(i)); } boolean ret = ps.execute(); if (!ret) { // when stmt.execute returns false it means no result set is // expected get the number of rows affected rowCount = ps.getUpdateCount(); printLine(rowCount + " rows affected"); } else { // a prepStmt returning a result set was run resultSet = ps.getResultSet(); if (useBufferedQuery) resultSet.setFetchSize(Integer.MAX_VALUE); } return ret; }
From source file:at.alladin.rmbt.controlServer.ResultResource.java
@Post("json") public String request(final String entity) { final String secret = getContext().getParameters().getFirstValue("RMBT_SECRETKEY"); addAllowOrigin();/*from w w w.j av a 2 s. com*/ JSONObject request = null; final ErrorList errorList = new ErrorList(); final JSONObject answer = new JSONObject(); System.out.println(MessageFormat.format(labels.getString("NEW_RESULT"), getIP())); if (entity != null && !entity.isEmpty()) // try parse the string to a JSON object try { request = new JSONObject(entity); System.out.println(request); final String lang = request.optString("client_language"); // Load Language Files for Client final List<String> langs = Arrays .asList(settings.getString("RMBT_SUPPORTED_LANGUAGES").split(",\\s*")); if (langs.contains(lang)) { errorList.setLanguage(lang); labels = ResourceManager.getSysMsgBundle(new Locale(lang)); } // System.out.println(request.toString(4)); if (conn != null) { conn.setAutoCommit(false); final Test test = new Test(conn); if (request.optString("test_token").length() > 0) { final String[] token = request.getString("test_token").split("_"); try { // Check if UUID final UUID testUuid = UUID.fromString(token[0]); final String data = token[0] + "_" + token[1]; final String hmac = Helperfunctions.calculateHMAC(secret, data); if (hmac.length() == 0) errorList.addError("ERROR_TEST_TOKEN"); if (token[2].length() > 0 && hmac.equals(token[2])) { final List<String> clientNames = Arrays .asList(settings.getString("RMBT_CLIENT_NAME").split(",\\s*")); final List<String> clientVersions = Arrays .asList(settings.getString("RMBT_VERSION_NUMBER").split(",\\s*")); if (test.getTestByUuid(testUuid) > 0) if (clientNames.contains(request.optString("client_name")) && clientVersions.contains(request.optString("client_version"))) { test.setFields(request); final String networkOperator = request .optString("telephony_network_operator"); if (MCC_MNC_PATTERN.matcher(networkOperator).matches()) test.getField("network_operator").setString(networkOperator); else test.getField("network_operator").setString(null); final String networkSimOperator = request .optString("telephony_network_sim_operator"); if (MCC_MNC_PATTERN.matcher(networkSimOperator).matches()) test.getField("network_sim_operator").setString(networkSimOperator); else test.getField("network_sim_operator").setString(null); // RMBTClient Info final String ipLocalRaw = request.optString("test_ip_local", null); if (ipLocalRaw != null) { final InetAddress ipLocalAddress = InetAddresses.forString(ipLocalRaw); // original address (not filtered) test.getField("client_ip_local") .setString(InetAddresses.toAddrString(ipLocalAddress)); // anonymized local address final String ipLocalAnonymized = Helperfunctions .anonymizeIp(ipLocalAddress); test.getField("client_ip_local_anonymized") .setString(ipLocalAnonymized); // type of local ip test.getField("client_ip_local_type") .setString(Helperfunctions.IpType(ipLocalAddress)); // public ip final InetAddress ipPublicAddress = InetAddresses .forString(test.getField("client_public_ip").toString()); test.getField("nat_type").setString( Helperfunctions.getNatType(ipLocalAddress, ipPublicAddress)); } final String ipServer = request.optString("test_ip_server", null); if (ipServer != null) { final InetAddress testServerInetAddress = InetAddresses .forString(ipServer); test.getField("server_ip") .setString(InetAddresses.toAddrString(testServerInetAddress)); } //log IP address final String ipSource = getIP(); test.getField("source_ip").setString(ipSource); //log anonymized address try { final InetAddress ipSourceIP = InetAddress.getByName(ipSource); final String ipSourceAnonymized = Helperfunctions .anonymizeIp(ipSourceIP); test.getField("source_ip_anonymized").setString(ipSourceAnonymized); } catch (UnknownHostException e) { System.out.println("Exception thrown:" + e); } // Additional Info ////////////////////////////////////////////////// // extended test stats: ////////////////////////////////////////////////// final TestStat extendedTestStat = TestStat .checkForSubmittedTestStats(request, test.getUid()); if (extendedTestStat != null) { final TestStatDao testStatDao = new TestStatDao(conn); testStatDao.save(extendedTestStat); } ////////////////////////////////////////////////// JSONArray speedData = request.optJSONArray("speed_detail"); if (speedData != null && !test.hasError()) { final PreparedStatement psSpeed = conn.prepareStatement( "INSERT INTO test_speed (test_id, upload, thread, time, bytes) VALUES (?,?,?,?,?)"); psSpeed.setLong(1, test.getUid()); for (int i = 0; i < speedData.length(); i++) { final JSONObject item = speedData.getJSONObject(i); final String direction = item.optString("direction"); if (direction != null && (direction.equals("download") || direction.equals("upload"))) { psSpeed.setBoolean(2, direction.equals("upload")); psSpeed.setInt(3, item.optInt("thread")); psSpeed.setLong(4, item.optLong("time")); psSpeed.setLong(5, item.optLong("bytes")); psSpeed.executeUpdate(); } } } final JSONArray pingData = request.optJSONArray("pings"); if (pingData != null && !test.hasError()) { final PreparedStatement psPing = conn.prepareStatement( "INSERT INTO ping (test_id, value, value_server, time_ns) " + "VALUES(?,?,?,?)"); psPing.setLong(1, test.getUid()); for (int i = 0; i < pingData.length(); i++) { final JSONObject pingDataItem = pingData.getJSONObject(i); long valueClient = pingDataItem.optLong("value", -1); if (valueClient >= 0) psPing.setLong(2, valueClient); else psPing.setNull(2, Types.BIGINT); long valueServer = pingDataItem.optLong("value_server", -1); if (valueServer >= 0) psPing.setLong(3, valueServer); else psPing.setNull(3, Types.BIGINT); long timeNs = pingDataItem.optLong("time_ns", -1); if (timeNs >= 0) psPing.setLong(4, timeNs); else psPing.setNull(4, Types.BIGINT); psPing.executeUpdate(); } } final JSONArray geoData = request.optJSONArray("geoLocations"); if (geoData != null && !test.hasError()) for (int i = 0; i < geoData.length(); i++) { final JSONObject geoDataItem = geoData.getJSONObject(i); if (geoDataItem.optLong("tstamp", 0) != 0 && geoDataItem.optDouble("geo_lat", 0) != 0 && geoDataItem.optDouble("geo_long", 0) != 0) { final GeoLocation geoloc = new GeoLocation(conn); geoloc.setTest_id(test.getUid()); final long clientTime = geoDataItem.optLong("tstamp"); final Timestamp tstamp = java.sql.Timestamp .valueOf(new Timestamp(clientTime).toString()); geoloc.setTime(tstamp, test.getField("timezone").toString()); geoloc.setAccuracy( (float) geoDataItem.optDouble("accuracy", 0)); geoloc.setAltitude(geoDataItem.optDouble("altitude", 0)); geoloc.setBearing((float) geoDataItem.optDouble("bearing", 0)); geoloc.setSpeed((float) geoDataItem.optDouble("speed", 0)); geoloc.setProvider(geoDataItem.optString("provider", "")); geoloc.setGeo_lat(geoDataItem.optDouble("geo_lat", 0)); geoloc.setGeo_long(geoDataItem.optDouble("geo_long", 0)); geoloc.setTime_ns(geoDataItem.optLong("time_ns", 0)); geoloc.storeLocation(); // Store Last Geolocation as // Testlocation if (i == geoData.length() - 1) { if (geoDataItem.has("geo_lat")) test.getField("geo_lat").setField(geoDataItem); if (geoDataItem.has("geo_long")) test.getField("geo_long").setField(geoDataItem); if (geoDataItem.has("accuracy")) test.getField("geo_accuracy").setField(geoDataItem); if (geoDataItem.has("provider")) test.getField("geo_provider").setField(geoDataItem); } if (geoloc.hasError()) { errorList.addError(geoloc.getError()); break; } } } final JSONArray cellData = request.optJSONArray("cellLocations"); if (cellData != null && !test.hasError()) for (int i = 0; i < cellData.length(); i++) { final JSONObject cellDataItem = cellData.getJSONObject(i); final Cell_location cellloc = new Cell_location(conn); cellloc.setTest_id(test.getUid()); final long clientTime = cellDataItem.optLong("time"); final Timestamp tstamp = java.sql.Timestamp .valueOf(new Timestamp(clientTime).toString()); cellloc.setTime(tstamp, test.getField("timezone").toString()); cellloc.setTime_ns(cellDataItem.optLong("time_ns", 0)); cellloc.setLocation_id(cellDataItem.optInt("location_id", 0)); cellloc.setArea_code(cellDataItem.optInt("area_code", 0)); cellloc.setPrimary_scrambling_code( cellDataItem.optInt("primary_scrambling_code", 0)); cellloc.storeLocation(); if (cellloc.hasError()) { errorList.addError(cellloc.getError()); break; } } int signalStrength = Integer.MAX_VALUE; //measured as RSSI (GSM,UMTS,Wifi) int lteRsrp = Integer.MAX_VALUE; // signal strength measured as RSRP int lteRsrq = Integer.MAX_VALUE; // signal quality of LTE measured as RSRQ int linkSpeed = UNKNOWN; final int networkType = test.getField("network_type").intValue(); final JSONArray signalData = request.optJSONArray("signals"); if (signalData != null && !test.hasError()) { for (int i = 0; i < signalData.length(); i++) { final JSONObject signalDataItem = signalData.getJSONObject(i); final Signal signal = new Signal(conn); signal.setTest_id(test.getUid()); final long clientTime = signalDataItem.optLong("time"); final Timestamp tstamp = java.sql.Timestamp .valueOf(new Timestamp(clientTime).toString()); signal.setTime(tstamp, test.getField("timezone").toString()); final int thisNetworkType = signalDataItem.optInt("network_type_id", 0); signal.setNetwork_type_id(thisNetworkType); final int thisSignalStrength = signalDataItem .optInt("signal_strength", UNKNOWN); if (thisSignalStrength != UNKNOWN) signal.setSignal_strength(thisSignalStrength); signal.setGsm_bit_error_rate( signalDataItem.optInt("gsm_bit_error_rate", 0)); final int thisLinkSpeed = signalDataItem.optInt("wifi_link_speed", 0); signal.setWifi_link_speed(thisLinkSpeed); final int rssi = signalDataItem.optInt("wifi_rssi", UNKNOWN); if (rssi != UNKNOWN) signal.setWifi_rssi(rssi); lteRsrp = signalDataItem.optInt("lte_rsrp", UNKNOWN); lteRsrq = signalDataItem.optInt("lte_rsrq", UNKNOWN); final int lteRssnr = signalDataItem.optInt("lte_rssnr", UNKNOWN); final int lteCqi = signalDataItem.optInt("lte_cqi", UNKNOWN); final long timeNs = signalDataItem.optLong("time_ns", UNKNOWN); signal.setLte_rsrp(lteRsrp); signal.setLte_rsrq(lteRsrq); signal.setLte_rssnr(lteRssnr); signal.setLte_cqi(lteCqi); signal.setTime_ns(timeNs); signal.storeSignal(); if (networkType == 99) // wlan { if (rssi < signalStrength && rssi != UNKNOWN) signalStrength = rssi; } else if (thisSignalStrength < signalStrength && thisSignalStrength != UNKNOWN) signalStrength = thisSignalStrength; if (thisLinkSpeed != 0 && (linkSpeed == UNKNOWN || thisLinkSpeed < linkSpeed)) linkSpeed = thisLinkSpeed; if (signal.hasError()) { errorList.addError(signal.getError()); break; } } // set rssi value (typically GSM,UMTS, but also old LTE-phones) if (signalStrength != Integer.MAX_VALUE && signalStrength != UNKNOWN && signalStrength != 0) // 0 dBm is out of range ((IntField) test.getField("signal_strength")) .setValue(signalStrength); // set rsrp value (typically LTE) if (lteRsrp != Integer.MAX_VALUE && lteRsrp != UNKNOWN && lteRsrp != 0) // 0 dBm is out of range ((IntField) test.getField("lte_rsrp")).setValue(lteRsrp); // set rsrq value (LTE) if (lteRsrq != Integer.MAX_VALUE && lteRsrq != UNKNOWN) ((IntField) test.getField("lte_rsrq")).setValue(lteRsrq); if (linkSpeed != Integer.MAX_VALUE && linkSpeed != UNKNOWN) ((IntField) test.getField("wifi_link_speed")).setValue(linkSpeed); } // use max network type final String sqlMaxNetworkType = "SELECT nt.uid" + " FROM signal s" + " JOIN network_type nt" + " ON s.network_type_id=nt.uid" + " WHERE test_id=?" + " ORDER BY nt.technology_order DESC" + " LIMIT 1"; final PreparedStatement psMaxNetworkType = conn .prepareStatement(sqlMaxNetworkType); psMaxNetworkType.setLong(1, test.getUid()); if (psMaxNetworkType.execute()) { final ResultSet rs = psMaxNetworkType.getResultSet(); if (rs.next()) { final int maxNetworkType = rs.getInt("uid"); if (maxNetworkType != 0) ((IntField) test.getField("network_type")) .setValue(maxNetworkType); } } /* * check for different types (e.g. * 2G/3G) */ final String sqlAggSignal = "WITH agg AS" + " (SELECT array_agg(DISTINCT nt.group_name ORDER BY nt.group_name) agg" + " FROM signal s" + " JOIN network_type nt ON s.network_type_id=nt.uid WHERE test_id=?)" + " SELECT uid FROM agg JOIN network_type nt ON nt.aggregate=agg"; final PreparedStatement psAgg = conn.prepareStatement(sqlAggSignal); psAgg.setLong(1, test.getUid()); if (psAgg.execute()) { final ResultSet rs = psAgg.getResultSet(); if (rs.next()) { final int newNetworkType = rs.getInt("uid"); if (newNetworkType != 0) ((IntField) test.getField("network_type")) .setValue(newNetworkType); } } if (test.getField("network_type").intValue() <= 0) errorList.addError("ERROR_NETWORK_TYPE"); final IntField downloadField = (IntField) test.getField("speed_download"); if (downloadField.isNull() || downloadField.intValue() <= 0 || downloadField.intValue() > 10000000) // 10 gbit/s limit errorList.addError("ERROR_DOWNLOAD_INSANE"); final IntField upField = (IntField) test.getField("speed_upload"); if (upField.isNull() || upField.intValue() <= 0 || upField.intValue() > 10000000) // 10 gbit/s limit errorList.addError("ERROR_UPLOAD_INSANE"); //clients still report eg: "test_ping_shortest":9195040 (note the 'test_' prefix there!) final LongField pingField = (LongField) test.getField("ping_shortest"); if (pingField.isNull() || pingField.longValue() <= 0 || pingField.longValue() > 60000000000L) // 1 min limit errorList.addError("ERROR_PING_INSANE"); if (errorList.isEmpty()) test.getField("status").setString("FINISHED"); else test.getField("status").setString("ERROR"); test.storeTestResults(false); if (test.hasError()) errorList.addError(test.getError()); } else errorList.addError("ERROR_CLIENT_VERSION"); } else errorList.addError("ERROR_TEST_TOKEN_MALFORMED"); } catch (final IllegalArgumentException e) { e.printStackTrace(); errorList.addError("ERROR_TEST_TOKEN_MALFORMED"); } } else errorList.addError("ERROR_TEST_TOKEN_MISSING"); conn.commit(); } else errorList.addError("ERROR_DB_CONNECTION"); } catch (final JSONException e) { errorList.addError("ERROR_REQUEST_JSON"); System.out.println("Error parsing JSDON Data " + e.toString()); e.printStackTrace(); } catch (final SQLException e) { System.out.println("Error while storing data " + e.toString()); e.printStackTrace(); } else errorList.addErrorString("Expected request is missing."); try { answer.putOpt("error", errorList.getList()); } catch (final JSONException e) { System.out.println("Error saving ErrorList: " + e.toString()); } return answer.toString(); }
From source file:at.alladin.rmbt.controlServer.RegistrationResource.java
@Post("json") public String request(final String entity) { long startTime = System.currentTimeMillis(); final String secret = getContext().getParameters().getFirstValue("RMBT_SECRETKEY"); addAllowOrigin();/*from ww w.j a v a2s. c o m*/ JSONObject request = null; final ErrorList errorList = new ErrorList(); final JSONObject answer = new JSONObject(); String answerString; final String clientIpRaw = getIP(); final InetAddress clientAddress = InetAddresses.forString(clientIpRaw); final String clientIpString = InetAddresses.toAddrString(clientAddress); System.out.println(MessageFormat.format(labels.getString("NEW_REQUEST"), clientIpRaw)); final String geoIpCountry = GeoIPHelper.lookupCountry(clientAddress); // public_ip_asn final Long asn = Helperfunctions.getASN(clientAddress); // public_ip_as_name // country_asn (2 digit country code of AS, eg. AT or EU) final String asName; final String asCountry; if (asn == null) { asName = null; asCountry = null; } else { asName = Helperfunctions.getASName(asn); asCountry = Helperfunctions.getAScountry(asn); } if (entity != null && !entity.isEmpty()) // try parse the string to a JSON object try { request = new JSONObject(entity); int typeId = 0; final String lang = request.optString("language"); // Load Language Files for Client final List<String> langs = Arrays .asList(settings.getString("RMBT_SUPPORTED_LANGUAGES").split(",\\s*")); if (langs.contains(lang)) { errorList.setLanguage(lang); labels = ResourceManager.getSysMsgBundle(new Locale(lang)); } // System.out.println(request.toString(4)); if (conn != null) { final Client clientDb = new Client(conn); if (!request.optString("type").isEmpty()) { typeId = clientDb.getTypeId(request.getString("type")); if (clientDb.hasError()) errorList.addError(clientDb.getError()); } final List<String> clientNames = Arrays .asList(settings.getString("RMBT_CLIENT_NAME").split(",\\s*")); final List<String> clientVersions = Arrays .asList(settings.getString("RMBT_VERSION_NUMBER").split(",\\s*")); if (clientNames.contains(request.optString("client")) && clientVersions.contains(request.optString("version")) && typeId > 0) { UUID uuid = null; final String uuidString = request.optString("uuid", ""); if (uuidString.length() != 0) uuid = UUID.fromString(uuidString); final String clientName = request.getString("client"); final String clientVersion = request.getString("version"); String timeZoneId = request.getString("timezone"); // String tmpTimeZoneId = timeZoneId; final long clientTime = request.getLong("time"); final Timestamp clientTstamp = java.sql.Timestamp .valueOf(new Timestamp(clientTime).toString()); final JSONObject location = request.optJSONObject("location"); long geotime = 0; double geolat = 0; double geolong = 0; float geoaccuracy = 0; double geoaltitude = 0; float geobearing = 0; float geospeed = 0; String geoprovider = ""; if (!request.isNull("location")) { geotime = location.optLong("time", 0); geolat = location.optDouble("lat", 0); geolong = location.optDouble("long", 0); geoaccuracy = (float) location.optDouble("accuracy", 0); geoaltitude = location.optDouble("altitude", 0); geobearing = (float) location.optDouble("bearing", 0); geospeed = (float) location.optDouble("speed", 0); geoprovider = location.optString("provider", ""); } Calendar timeWithZone = null; if (timeZoneId.isEmpty()) { timeZoneId = Helperfunctions.getTimezoneId(); timeWithZone = Helperfunctions.getTimeWithTimeZone(timeZoneId); } else timeWithZone = Helperfunctions.getTimeWithTimeZone(timeZoneId); long clientUid = 0; /* * if (uuid == null) { * clientDb.setTimeZone(timeWithZone); * clientDb.setTime(tstamp); * clientDb.setClient_type_id(typeId); uuid = * clientDb.storeClient(); if (clientDb.hasError()) { * errorList.addError(clientDb.getError()); } else { * answer.put("uuid", uuid.toString()); } } */ if (errorList.getLength() == 0 && uuid != null) { clientUid = clientDb.getClientByUuid(uuid); if (clientDb.hasError()) errorList.addError(clientDb.getError()); } if (clientUid > 0) { final String testUuid = UUID.randomUUID().toString(); final String testOpenUuid = UUID.randomUUID().toString(); boolean testServerEncryption = true; // default is // true // hack for android api <= 10 (2.3.x) // using encryption with test doesn't work if (request.has("plattform") && request.optString("plattform").equals("Android")) if (request.has("api_level")) { final String apiLevelString = request.optString("api_level"); try { final int apiLevel = Integer.parseInt(apiLevelString); if (apiLevel <= 10) testServerEncryption = false; } catch (final NumberFormatException e) { } } final String serverType; if (request.optString("client").equals("RMBTws")) serverType = "RMBTws"; else serverType = "RMBT"; final Boolean ipv6; if (clientAddress instanceof Inet6Address) ipv6 = true; else if (clientAddress instanceof Inet4Address) ipv6 = false; else // should never happen, unless ipv > 6 is available ipv6 = null; final TestServer server = getNearestServer(errorList, geolat, geolong, geotime, clientIpString, asCountry, geoIpCountry, serverType, testServerEncryption, ipv6); try { if (server == null) throw new JSONException("could not find server"); if (timeZoneId.isEmpty()) { timeZoneId = Helperfunctions.getTimezoneId(); timeWithZone = Helperfunctions.getTimeWithTimeZone(timeZoneId); } else timeWithZone = Helperfunctions.getTimeWithTimeZone(timeZoneId); answer.put("test_server_address", server.address); answer.put("test_server_port", server.port); answer.put("test_server_name", server.name); answer.put("test_server_encryption", testServerEncryption); answer.put("test_duration", getSetting("rmbt_duration")); answer.put("test_numthreads", getSetting("rmbt_num_threads")); answer.put("test_numpings", getSetting("rmbt_num_pings")); answer.put("client_remote_ip", clientIpString); final String resultUrl = new Reference(getURL(), settings.getString("RMBT_RESULT_PATH")).getTargetRef().toString(); // System.out.println(resultUrl); answer.put("result_url", resultUrl); final String resultQoSUrl = new Reference(getURL(), settings.getString("RMBT_QOS_RESULT_PATH")).getTargetRef().toString(); // System.out.println(resultUrl); answer.put("result_qos_url", resultQoSUrl); } catch (final JSONException e) { System.out.println("Error generating Answer " + e.toString()); errorList.addError("ERROR_RESPONSE_JSON"); } if (errorList.getLength() == 0) try { PreparedStatement st; st = conn.prepareStatement( "INSERT INTO test(time, uuid, open_test_uuid, client_id, client_name, client_version, client_software_version, client_language, client_public_ip, client_public_ip_anonymized, country_geoip, server_id, port, use_ssl, timezone, client_time, duration, num_threads_requested, status, software_revision, client_test_counter, client_previous_test_status, public_ip_asn, public_ip_as_name, country_asn, public_ip_rdns, run_ndt)" + "VALUES(NOW(), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS); int i = 1; // uuid st.setObject(i++, UUID.fromString(testUuid)); // open_test_uuid st.setObject(i++, UUID.fromString(testOpenUuid)); // client_id st.setLong(i++, clientUid); // client_name st.setString(i++, clientName); // client_version st.setString(i++, clientVersion); // client_software_version st.setString(i++, request.optString("softwareVersion", null)); // client_language st.setString(i++, lang); // client_public_ip st.setString(i++, clientIpString); // client_public_ip_anonymized st.setString(i++, Helperfunctions.anonymizeIp(clientAddress)); // country_geoip (2digit country code derived from public IP of client) st.setString(i++, geoIpCountry); // server_id st.setInt(i++, server.id); // port st.setInt(i++, server.port); // use_ssl st.setBoolean(i++, testServerEncryption); // timezone (of client) st.setString(i++, timeZoneId); // client_time (local time of client) st.setTimestamp(i++, clientTstamp, timeWithZone); // duration (requested) st.setInt(i++, Integer.parseInt(getSetting("rmbt_duration"))); // num_threads_requested st.setInt(i++, Integer.parseInt(getSetting("rmbt_num_threads"))); // status (of test) st.setString(i++, "STARTED"); //was "RUNNING" before // software_revision (of client) st.setString(i++, request.optString("softwareRevision", null)); // client_test_counter (number of tests the client has performed) final int testCounter = request.optInt("testCounter", -1); if (testCounter == -1) // older clients did not support testCounter st.setNull(i++, Types.INTEGER); else st.setLong(i++, testCounter); // client_previous_test_status (outcome of previous test) st.setString(i++, request.optString("previousTestStatus", null)); // AS name if (asn == null) st.setNull(i++, Types.BIGINT); else st.setLong(i++, asn); if (asName == null) st.setNull(i++, Types.VARCHAR); else st.setString(i++, asName); // AS country if (asCountry == null) st.setNull(i++, Types.VARCHAR); else st.setString(i++, asCountry); //public_ip_rdns String reverseDNS = Helperfunctions.reverseDNSLookup(clientAddress); if (reverseDNS == null || reverseDNS.isEmpty()) st.setNull(i++, Types.VARCHAR); else { reverseDNS = reverseDNS.replaceFirst("\\.$", ""); st.setString(i++, reverseDNS); // cut off last dot (#332) } // run_ndt if (request.has("ndt")) st.setBoolean(i++, request.getBoolean("ndt")); else st.setNull(i++, Types.BOOLEAN); final int affectedRows = st.executeUpdate(); if (affectedRows == 0) errorList.addError("ERROR_DB_STORE_TEST"); else { long key = 0; final ResultSet rs = st.getGeneratedKeys(); if (rs.next()) // Retrieve the auto generated // key(s). key = rs.getLong(1); rs.close(); final PreparedStatement getProviderSt = conn .prepareStatement("SELECT rmbt_set_provider_from_as(?)"); getProviderSt.setLong(1, key); String provider = null; if (getProviderSt.execute()) { final ResultSet rs2 = getProviderSt.getResultSet(); if (rs2.next()) provider = rs2.getString(1); } if (provider != null) answer.put("provider", provider); final PreparedStatement testSlotStatement = conn .prepareStatement("SELECT rmbt_get_next_test_slot(?)"); testSlotStatement.setLong(1, key); int testSlot = -1; if (testSlotStatement.execute()) { final ResultSet rs2 = testSlotStatement.getResultSet(); if (rs2.next()) testSlot = rs2.getInt(1); } if (testSlot < 0) errorList.addError("ERROR_DB_STORE_GENERAL"); else { final String data = testUuid + "_" + testSlot; final String hmac = Helperfunctions.calculateHMAC(secret, data); if (hmac.length() == 0) errorList.addError("ERROR_TEST_TOKEN"); final String token = data + "_" + hmac; final PreparedStatement updateSt = conn .prepareStatement("UPDATE test SET token = ? WHERE uid = ?"); updateSt.setString(1, token); updateSt.setLong(2, key); updateSt.executeUpdate(); answer.put("test_token", token); answer.put("test_uuid", testUuid); answer.put("test_id", key); final long now = System.currentTimeMillis(); int wait = testSlot - (int) (now / 1000); if (wait < 0) wait = 0; answer.put("test_wait", wait); if (geotime != 0 && geolat != 0 && geolong != 0) { final GeoLocation clientLocation = new GeoLocation(conn); clientLocation.setTest_id(key); final Timestamp geotstamp = java.sql.Timestamp .valueOf(new Timestamp(geotime).toString()); clientLocation.setTime(geotstamp, timeZoneId); clientLocation.setAccuracy(geoaccuracy); clientLocation.setAltitude(geoaltitude); clientLocation.setBearing(geobearing); clientLocation.setSpeed(geospeed); clientLocation.setProvider(geoprovider); clientLocation.setGeo_lat(geolat); clientLocation.setGeo_long(geolong); clientLocation.storeLocation(); if (clientLocation.hasError()) errorList.addError(clientLocation.getError()); } } } st.close(); } catch (final SQLException e) { errorList.addError("ERROR_DB_STORE_GENERAL"); e.printStackTrace(); } } else errorList.addError("ERROR_CLIENT_UUID"); } else errorList.addError("ERROR_CLIENT_VERSION"); } else errorList.addError("ERROR_DB_CONNECTION"); // System.out.println(answer.toString(4)); } catch (final JSONException e) { errorList.addError("ERROR_REQUEST_JSON"); System.out.println("Error parsing JSDON Data " + e.toString()); } else errorList.addErrorString("Expected request is missing."); try { answer.putOpt("error", errorList.getList()); } catch (final JSONException e) { System.out.println("Error saving ErrorList: " + e.toString()); } answerString = answer.toString(); long elapsedTime = System.currentTimeMillis() - startTime; System.out.println(MessageFormat.format(labels.getString("NEW_REQUEST_SUCCESS"), clientIpRaw, Long.toString(elapsedTime))); return answerString; }
From source file:csiro.pidsvc.mappingstore.ManagerJson.java
@SuppressWarnings("unchecked") public JSONObject getLookupConfig(String ns) throws SQLException { PreparedStatement pst = null; ResultSet rs = null;/* ww w .j av a 2 s .co m*/ JSONObject ret = new JSONObject(); JSONArray jsonArr; String lookupType; try { pst = _connection.prepareStatement( "SELECT ns, type, behaviour_type, behaviour_value FROM lookup_ns WHERE ns = ?;SELECT key, value FROM lookup WHERE ns = ?;"); pst.setString(1, ns); pst.setString(2, ns); if (pst.execute()) { rs = pst.getResultSet(); if (rs.next()) { lookupType = rs.getString("type"); ret.put("ns", rs.getString("ns")); ret.put("type", lookupType); ret.put("default", JSONObjectHelper.create("type", rs.getString("behaviour_type"), "value", rs.getString("behaviour_value"))); pst.getMoreResults(); rs = pst.getResultSet(); if (lookupType.equalsIgnoreCase("Static")) { jsonArr = new JSONArray(); while (rs.next()) { jsonArr.add(JSONObjectHelper.create("key", rs.getString(1), "value", rs.getString(2))); } ret.put("lookup", jsonArr); } else if (lookupType.equalsIgnoreCase("HttpResolver")) { if (rs.next()) { final Pattern reType = Pattern.compile("^T:(.+)$", Pattern.CASE_INSENSITIVE | Pattern.MULTILINE); final Pattern reExtract = Pattern.compile("^E:(.+)$", Pattern.CASE_INSENSITIVE | Pattern.MULTILINE); final Pattern reNamespace = Pattern.compile("^NS:(.+?):(.+)$", Pattern.CASE_INSENSITIVE | Pattern.MULTILINE); Matcher m; String buf = rs.getString(2); try { JSONObject jsonPart = new JSONObject(); jsonPart.put("endpoint", rs.getString(1)); // Type. m = reType.matcher(buf); m.find(); jsonPart.put("type", m.group(1)); // Extractor. m = reExtract.matcher(buf); m.find(); jsonPart.put("extractor", m.group(1)); // Namespaces. m = reNamespace.matcher(buf); jsonArr = new JSONArray(); while (m.find()) { jsonArr.add(JSONObjectHelper.create("prefix", m.group(1), "uri", m.group(2))); } jsonPart.put("namespaces", jsonArr); ret.put("lookup", jsonPart); } catch (Exception e) { _logger.debug(e); return null; } } } } } } finally { if (rs != null) rs.close(); if (pst != null) pst.close(); } return ret; }
From source file:org.cloudgraph.rdb.filter.RDBStatementExecutor.java
@Override public List<PropertyPair> fetchRow(PlasmaType type, StringBuilder sql) { List<PropertyPair> result = new ArrayList<PropertyPair>(); PreparedStatement statement = null; ResultSet rs = null;// w w w . jav a2 s .co m try { if (log.isDebugEnabled()) { log.debug("fetch: " + sql.toString()); } statement = con.prepareStatement(sql.toString(), ResultSet.TYPE_FORWARD_ONLY, /* * ResultSet * . * TYPE_SCROLL_INSENSITIVE * , */ ResultSet.CONCUR_READ_ONLY); statement.execute(); rs = statement.getResultSet(); ResultSetMetaData rsMeta = rs.getMetaData(); int numcols = rsMeta.getColumnCount(); int count = 0; while (rs.next()) { for (int i = 1; i <= numcols; i++) { String columnName = rsMeta.getColumnName(i); int columnType = rsMeta.getColumnType(i); PlasmaProperty prop = (PlasmaProperty) type.getProperty(columnName); PlasmaProperty valueProp = prop; while (!valueProp.getType().isDataType()) { valueProp = this.statementUtil.getOppositePriKeyProperty(valueProp); } Object value = converter.fromJDBCDataType(rs, i, columnType, valueProp); if (value != null) { PropertyPair pair = new PropertyPair((PlasmaProperty) prop, value); if (!valueProp.equals(prop)) pair.setValueProp(valueProp); result.add(pair); } } count++; } if (log.isDebugEnabled()) log.debug("returned " + count + " results"); } catch (Throwable t) { throw new DataAccessException(t); } finally { try { if (rs != null) rs.close(); if (statement != null) statement.close(); } catch (SQLException e) { log.error(e.getMessage(), e); } } return result; }
From source file:org.cloudgraph.rdb.filter.RDBStatementExecutor.java
@Override public Map<String, PropertyPair> fetchRowMap(PlasmaType type, StringBuilder sql) { Map<String, PropertyPair> result = new HashMap<String, PropertyPair>(); PreparedStatement statement = null; ResultSet rs = null;/*www . j a v a 2 s. c om*/ try { if (log.isDebugEnabled()) { log.debug("fetch: " + sql.toString()); } statement = con.prepareStatement(sql.toString(), ResultSet.TYPE_FORWARD_ONLY, /* * ResultSet * . * TYPE_SCROLL_INSENSITIVE * , */ ResultSet.CONCUR_READ_ONLY); statement.execute(); rs = statement.getResultSet(); ResultSetMetaData rsMeta = rs.getMetaData(); int numcols = rsMeta.getColumnCount(); int count = 0; while (rs.next()) { for (int i = 1; i <= numcols; i++) { String columnName = rsMeta.getColumnName(i); int columnType = rsMeta.getColumnType(i); PlasmaProperty prop = (PlasmaProperty) type.getProperty(columnName); PlasmaProperty valueProp = prop; while (!valueProp.getType().isDataType()) { valueProp = this.statementUtil.getOppositePriKeyProperty(valueProp); } Object value = converter.fromJDBCDataType(rs, i, columnType, valueProp); if (value != null) { PropertyPair pair = new PropertyPair((PlasmaProperty) prop, value); if (!valueProp.equals(prop)) pair.setValueProp(valueProp); result.put(prop.getName(), pair); } } count++; } if (log.isDebugEnabled()) log.debug("returned " + count + " results"); } catch (Throwable t) { throw new DataAccessException(t); } finally { try { if (rs != null) rs.close(); if (statement != null) statement.close(); } catch (SQLException e) { log.error(e.getMessage(), e); } } return result; }
From source file:csiro.pidsvc.mappingstore.ManagerJson.java
@SuppressWarnings("unchecked") private JSONArray encodeChartChildren(String parent) throws SQLException { PreparedStatement pst = null; ResultSet rs = null;/* w w w.j a v a 2 s. c om*/ JSONArray ret = new JSONArray(); JSONObject jsonData; String mappingPath, title; boolean isOneToOne; try { pst = _connection.prepareStatement( "SELECT mapping_path, parent, title, description, creator, type FROM vw_active_mapping WHERE mapping_path IS NOT NULL AND parent " + (parent == null ? "IS NULL" : "= ?")); if (parent != null) pst.setString(1, parent); if (pst.execute()) { rs = pst.getResultSet(); while (rs.next()) { mappingPath = rs.getString("mapping_path"); title = rs.getString("title"); isOneToOne = rs.getString("type").equalsIgnoreCase("1:1"); // Construct data object. jsonData = JSONObjectHelper.create("mapping_path", mappingPath, "title", title, "author", rs.getString("creator"), "description", rs.getString("description")); if (isOneToOne) { jsonData.put("$type", "square"); jsonData.put("$color", "#bed600"); } JSONObject json = new JSONObject(); json.put("id", mappingPath); json.put("name", title == null ? mappingPath : title); json.put("data", jsonData); json.put("children", encodeChartChildren(mappingPath)); ret.add(json); } } } finally { if (rs != null) rs.close(); if (pst != null) pst.close(); } return ret; }