List of usage examples for java.sql Types OTHER
int OTHER
To view the source code for java.sql Types OTHER.
Click Source Link
getObject and setObject. From source file:com.alibaba.wasp.jdbc.TestPreparedStatement.java
public void testSetObject() throws SQLException { Statement stat = conn.createStatement(); stat.execute("CREATE TABLE TEST(C CHAR(1))"); PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?)"); prep.setObject(1, 'x'); prep.execute();//from ww w. j av a2s . co m stat.execute("DROP TABLE TEST"); stat.execute("CREATE TABLE TEST(ID INT, DATA BINARY, JAVA OTHER)"); prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?, ?)"); prep.setInt(1, 1); prep.setObject(2, 11); prep.setObject(3, null); prep.execute(); prep.setInt(1, 2); prep.setObject(2, 101, Types.OTHER); prep.setObject(3, 103, Types.OTHER); prep.execute(); PreparedStatement p2 = conn.prepareStatement("SELECT * FROM TEST ORDER BY ID"); ResultSet rs = p2.executeQuery(); rs.next(); Object o = rs.getObject(2); assertTrue(o instanceof byte[]); assertTrue(rs.getObject(3) == null); rs.next(); o = rs.getObject(2); assertTrue(o instanceof byte[]); o = rs.getObject(3); assertTrue(o instanceof Integer); assertEquals(103, ((Integer) o).intValue()); assertFalse(rs.next()); stat.execute("DROP TABLE TEST"); }
From source file:org.apache.ode.bpel.extvar.jdbc.JdbcExternalVariableModule.java
private Object downcastValue(Object value, int dataType) { if (value == null) { return null; }//from w w w . j av a 2 s. c o m // Try down casting the value as per its column type. try { // Some JDBC 4.0 types have been ignored to avoid compilation errors switch (dataType) { case Types.ARRAY: break; case Types.BIGINT: if (!(value instanceof BigInteger)) { value = new BigDecimal(value.toString()).longValue(); } break; case Types.BINARY: break; case Types.BIT: if (!(value instanceof Boolean)) { value = new Boolean(value.toString()); } break; case Types.BLOB: break; case Types.BOOLEAN: if (!(value instanceof Boolean)) { value = new Boolean(value.toString()); } break; case Types.CHAR: break; case Types.CLOB: break; case Types.DATALINK: break; case Types.DATE: break; case Types.DECIMAL: //ODE-872: Oracle 9g and 10g has problems with BigDecimal on Java1.5 value = new BigDecimal(new BigDecimal(value.toString()).toPlainString()); break; case Types.DISTINCT: break; case Types.DOUBLE: if (!(value instanceof Double)) { value = Double.valueOf(value.toString()).doubleValue(); } break; case Types.FLOAT: if (!(value instanceof Float)) { value = Float.valueOf(value.toString()).floatValue(); } break; case Types.INTEGER: if (!(value instanceof Integer)) { value = Double.valueOf(value.toString()).intValue(); } break; case Types.JAVA_OBJECT: break; // case Types.LONGNVARCHAR: // break; case Types.LONGVARBINARY: break; case Types.LONGVARCHAR: break; // case Types.NCHAR: // break; // case Types.NCLOB: // break; case Types.NUMERIC: //ODE-872: Oracle 9g and 10g has problems with BigDecimal on Java1.5 value = new BigDecimal(new BigDecimal(value.toString()).toPlainString()); break; // case Types.NVARCHAR: // break; case Types.OTHER: break; case Types.REAL: if (!(value instanceof Double)) { value = Float.valueOf(value.toString()).floatValue(); } break; case Types.REF: break; // case Types.ROWID: // break; case Types.SMALLINT: if (!(value instanceof Short)) { value = new Short(value.toString()).shortValue(); } break; // case Types.SQLXML: // break; case Types.STRUCT: break; case Types.TIME: break; case Types.TIMESTAMP: break; case Types.TINYINT: if (!(value instanceof Short)) { value = new Short(value.toString()).shortValue(); } break; case Types.VARBINARY: break; case Types.VARCHAR: break; default: break; } } catch (Exception e) { // couldn't cast... let's just use original value object } return value; }
From source file:org.jumpmind.symmetric.io.data.DbFill.java
private Object generateRandomValueForColumn(Column column) { Object objectValue = null;/*from ww w . j a va 2 s. c om*/ int type = column.getMappedTypeCode(); if (column.isEnum()) { objectValue = column.getEnumValues()[new Random().nextInt(column.getEnumValues().length)]; } else if (column.isTimestampWithTimezone()) { objectValue = String.format("%s %s", FormatUtils.TIMESTAMP_FORMATTER.format(randomDate()), AppUtils.getTimezoneOffset()); } else if (type == Types.DATE) { objectValue = DateUtils.truncate(randomDate(), Calendar.DATE); } else if (type == Types.TIMESTAMP || type == Types.TIME) { objectValue = randomTimestamp(); } else if (type == Types.INTEGER || type == Types.BIGINT) { objectValue = randomInt(); } else if (type == Types.SMALLINT) { objectValue = randomSmallInt(column.getJdbcTypeName().toLowerCase().contains("unsigned")); } else if (type == Types.FLOAT) { objectValue = randomFloat(); } else if (type == Types.DOUBLE) { objectValue = randomDouble(); } else if (type == Types.TINYINT) { objectValue = randomTinyInt(); } else if (type == Types.NUMERIC || type == Types.DECIMAL || type == Types.REAL) { objectValue = randomBigDecimal(column.getSizeAsInt(), column.getScale()); } else if (type == Types.BOOLEAN || type == Types.BIT) { objectValue = randomBoolean(); } else if (type == Types.BLOB || type == Types.LONGVARBINARY || type == Types.BINARY || type == Types.VARBINARY || // SQLServer text type type == -10) { objectValue = randomBytes(); } else if (type == Types.ARRAY) { objectValue = null; } else if (type == Types.VARCHAR || type == Types.LONGVARCHAR || type == Types.CHAR || type == Types.CLOB) { int size = 0; // Assume if the size is 0 there is no max size configured. if (column.getSizeAsInt() != 0) { size = column.getSizeAsInt() > 50 ? 50 : column.getSizeAsInt(); } else { // No max length so default to 50 size = 50; } objectValue = randomString(size); } else if (type == Types.OTHER) { if ("UUID".equalsIgnoreCase(column.getJdbcTypeName())) { objectValue = randomUUID(); } } return objectValue; }
From source file:edu.ncsa.sstde.indexing.postgis.PostgisIndexer.java
private void asSql(MatchedIndexedGraph graph, SqlQueryBuilder builder, BindingSet sparqlBindings) { StringBuffer from = new StringBuffer(SELECT); Map<String, String> verseNameMappings = graph.getVerseNameMappings(); for (String varName : graph.getUsedVarNames()) { String name = verseNameMappings.get(varName); if (name != null) { from.append(' ').append(name).append(','); }// w w w .ja va 2 s . co m } if (from.length() == SELECT.length()) { for (String varName : verseNameMappings.values()) { from.append(' ').append(varName).append(','); } } // for (String column : graph.getNameMappings().keySet()) { // from.append(' ').append(column).append(','); // } Map<String, String> verseMapping = verseNameMappings; from.deleteCharAt(from.length() - 1); from.append(FROM); from.append(this.getName()); // compose the where clause StringBuffer where = new StringBuffer(); for (FunctionCall call : graph.getFunctionCalls()) { URIImpl url = new URIImpl(call.getURI()); where.append(ST_PREFIX).append(url.getLocalName()).append('('); for (int i = 0; i < call.getArgs().size(); i++) { ValueExpr param = call.getArgs().get(i); if (param instanceof Var) { where.append(verseMapping.get(((Var) param).getName())).append(','); } else if (param instanceof ValueConstant) { where.append('?').append(','); builder.inputBindings.add( new Binding(Types.OTHER, parseLiteral((Literal) ((ValueConstant) param).getValue()))); } } where.deleteCharAt(where.length() - 1).append(")=true").append(AND); } for (Compare compare : graph.getCompares()) { addCompareWhere(where, compare, builder, verseMapping); where.append(AND); } for (VarFilter filter : graph.getVarFilters()) { where.append(filter.getVarName()).append("=?").append(AND); builder.inputBindings.add(new Binding(Types.VARCHAR, filter.getValue())); // System.out.println(filter); } for (@SuppressWarnings("unused") Regex regex : graph.getRegexs()) { } if (where.length() > 0) { where.delete(where.length() - 5, where.length() - 1); } // compose the order by clause StringBuffer orderby = new StringBuffer(); if (graph.getOrders() != null && graph.getOrders() instanceof List) { List<OrderElem> orders = (List<OrderElem>) graph.getOrders(); Map<String, LiteralDef> literalDefMap = this.getSettings().getIndexGraph().getLiteralDefMap(); for (int i = graph.getOrders().size() - 1; i > -1; i--) { OrderElem order = orders.get(i); if (order.getExpr() instanceof Var) { String colName = verseMapping.get(((Var) order.getExpr()).getName()); if (literalDefMap.get(colName) != null) { orderby.append(colName); orderby.append(order.isAscending() ? ASC : DESC); orderby.append(','); } } } } // for (OrderElem order : graph.getOrders()) { // if (order.getExpr() instanceof Var) { // orderby.append(verseMapping.get(((Var) order.getExpr()) // .getName())); // orderby.append(order.isAscending() ? ASC : DESC); // orderby.append(','); // } // // } if (orderby.length() > 0) { orderby.deleteCharAt(orderby.length() - 1); } // combine all the query segments if (where.length() > 0) { from.append(WHERE).append(where); } if (orderby.length() > 0) { from.append(ORDER_BY).append(orderby); } builder.setSQL(from.toString()); builder.setLimit(graph.getLimit()); }
From source file:at.alladin.rmbt.statisticServer.OpenTestResource.java
/** * Gets the JSON-Representation of all open-data-fields for one specific * open-test-uuid/* w w w . j a v a2s . c om*/ * @param openTestUUID * @return the json-string * * * columns in csv ("open data") * 1:open_uuid, * 2:open_test_uuid, * 3:time, * 4:cat_technology, * 5:network_type, * 6:lat, * 7:long, * 8:loc_src, * 9:zip_code, * 10:download_kbit, * 11:upload_kbit, * 12:ping_ms, * 13:signal_strength, * 14:server_name, * 15:test_duration, * 16:num_threads, * 17:platform, * 18:model, * 19:client_version, * 20:network_mcc_mnc, * 21:network_name, * 22:sim_mcc_mnc, * 23:connection, * 24:asn, * 25:ip_anonym, * 26:ndt_download_kbit, * 27:ndt_upload_kbit, * 28:implausible, * 29:lte_rsrp * * * Columns in test table * uid (internal) * uuid (private) * client_id * client_version * client_name * client_language (private) * token (private, obsolete) * server_id * port * use_ssl * * time * speed_upload * speed_download * ping_shortest * encryption * * client_public_ip (private) * plattform * os_version (internal) * api_level (internal) * device * model * product * phone_type (internal) * data_state (internal) * network_country (internal) * network_operator * network_operator_name * network_sim_country (internal) * network_sim_operator * network_sim_operator_name * wifi_ssid (private) * wifi_bssid (private) * wifi_network_id (private) * duration * num_threads * status * timezone (private) * bytes_download * bytes_upload * nsec_download * nsec_upload * server_ip * client_software_version * geo_lat * geo_long * network_type * location * signal_strength * software_revision * client_test_counter * nat_type * client_previous_test_status * public_ip_asn * speed_upload_log * speed_download_log * total_bytes_download * total_bytes_upload * wifi_link_speed * public_ip_rdns * public_ip_as_name * test_slot * provider_id * network_is_roaming (internal) * ping_shortest_log * run_ndt (internal) * num_threads_requested * client_public_ip_anonymized * zip_code * geo_provider * geo_accuracy * deleted (internal) * comment (internal) * open_uuid * client_time (internal) * zip_code_geo (internal) * mobile_provider_id * roaming_type * open_test_uuid * country_asn * country_location * test_if_bytes_download * test_if_bytes_upload * implausible * testdl_if_bytes_download * testdl_if_bytes_upload * testul_if_bytes_download * testul_if_bytes_upload * country_geoip * location_max_distance * location_max_distance_gps * network_group_name * network_group_type * time_dl_ns * time_ul_ns * num_threads_ul * lte_rsrp * lte_rsrq * mobile_network_id * mobile_sim_id * dist_prev * speed_prev * tag * ping_median * ping_median_log * client_ip_local_type (private) * * private: visible to user only (not public) * internal: not visible (neither user nor public) * */ private String getSingleOpenTest(String openTestUUID) { final String sql = "SELECT t.uid as test_uid, " + " ('O' || t.open_test_uuid) open_test_uuid," + //csv open_test_uuid, UUID prefixed with 'O' " to_char(t.time AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS') \"time\"," + " t.time full_time," + //csv: 3:time server time-stamp of start of measurement " t.client_time client_time," + //(internal) client time-stamp of start of measure " t.network_group_name cat_technology," + //csv 4:cat_technology " t.network_group_type network_type," + //csv 5:network_type " t.geo_lat lat," + //csv 6:lat " t.geo_long long," + // csv 7:long " t.geo_provider loc_src," + //csv 8:loc_src android: 'gps'/'network'; browser/iOS: '' (empty string) " t.geo_accuracy loc_accuracy, " + //accuracy of geo location in m /* //csv 6:lat " (CASE WHEN (t.geo_accuracy < ?) AND (t.geo_provider != 'manual') AND (t.geo_provider != 'geocoder') THEN" + " t.geo_lat" + " WHEN (t.geo_accuracy < ?) THEN" + " ROUND(t.geo_lat*1111)/1111" + // approx 100m " ELSE null" + " END) lat," + // csv 7:long " (CASE WHEN (t.geo_accuracy < ?) AND (t.geo_provider != 'manual') AND (t.geo_provider != 'geocoder') THEN" + " t.geo_long" + " WHEN (t.geo_accuracy < ?) THEN" + " ROUND(t.geo_long*741)/741 " + //approx 100m " ELSE null" + " END) long," + // csv 8:loc_src android: 'gps'/'network'; browser/iOS: '' (empty string) " (CASE WHEN ((t.geo_provider = 'manual') OR (t.geo_provider = 'geocoder')) THEN" + " 'rastered'" + //make raster transparent " ELSE t.geo_provider" + " END) loc_src," + // accuracy of geo location in m " (CASE WHEN (t.geo_accuracy < ?) AND (t.geo_provider != 'manual') AND (t.geo_provider != 'geocoder') " + " THEN t.geo_accuracy " + " WHEN (t.geo_accuracy < 100) AND ((t.geo_provider = 'manual') OR (t.geo_provider = 'geocoder')) THEN 100" + // limit accuracy to 100m " WHEN (t.geo_accuracy < ?) THEN t.geo_accuracy" + " ELSE null END) loc_accuracy, " + */ //csv 9:zip-code - defined as integer in data base, only meaningful for measurements in Austria " (CASE WHEN (t.zip_code < 1000 OR t.zip_code > 9999) THEN null ELSE t.zip_code END) zip_code," + // " zip_code_geo," + //(internal) zip-code, integer derived from geo_location, Austria only " t.speed_download download_kbit," + //csv 10:download_kbit " t.speed_upload upload_kbit," + //csv 11: upload_kbit " t.wifi_link_speed," + // nominal speed of wifi-link in mbit/s , Android-only " (t.ping_median::float / 1000000) ping_ms," + //median ping-time in ms (stored in ns in data base) " signal_strength," + //csv 13:signal_strength RSSI, mainly GSM/UMTS and Wifi, Android only, in dBm " lte_rsrp," + // csv 29: signal_strength RSRP, Android only, in dBm " lte_rsrq," + // signal quality RSRQ, Android only, in dB " ts.name server_name," + //csv 14:server_name, name of the test server used for download/upload (not applicable for JStest) " implausible, " + //csv 28:implausible, measurement not shown in map nor used in statistics, normally not visible " public_ip_as_name, " + //name of AS (not number) " duration test_duration," + //csv 15:test_duration, nominal duration of downlink and uplink throughput tests in seconds " num_threads_requested," + // number of threads requested by control-server " num_threads," + //csv 16:num_threads, number of threads used in downlink throughput test (uplink may differ) " num_threads_ul," + // number of threads used in uplink test " COALESCE(t.plattform, t.client_name) as platform," + //csv 17:platform; currently used: 'CLI'/'Android'/Applet/iOS/[from client_name: RMBTws, RMBTjs](null); (null) is used for RMBTjs " COALESCE(adm.fullname, t.model) model," + //csv 18:model, translated t.model (model_native) to human readable form " t.model model_native," + //device used for test; Android API 'model'; iOS:'product'; Browser: Browser-name (zB Firefox) " t.product product," + // product used for test; Android APO 'product'; iOS: '' (not used); Browser: same as for model (browser name) " t.client_software_version client_version," + //csv 19:client_version, SW-version of client software (not RMBT-client-version), eg. '1.3' " t.network_operator network_mcc_mnc," + //csv 20:network_mcc_mnc, mobile country and network code of current network (Android only), string, eg "232-12' " network_country," + //(internal) Android-only, country code derived by client from mobile network code // " network_is_roaming," + //(internal) roaming-status of mobile network, boolean or (null); Android-only (obsolete) " roaming_type," + //roaming-status of mobile network, integer: 0:not roaming,1:national,2:international,(null):unknown (eg. iOS) " t.network_operator_name network_name," + //csv 21:network_name, name of current mobile network as displayed on device (eg: '3likeHome') " t.network_sim_operator sim_mcc_mnc," + //csv 22:sim_mcc_mnc, home network of SIM (initial 5 digits from IMSI), eg '232-01' " t.network_sim_country sim_country," + //(internal) Android-only, country derived by client from SIM (country of home network) " COALESCE(mprov.name,msim.shortname,msim.name,prov.name) provider_name," + //pre-defined list of providers (based on provider_id) //TODO replace provider " t.nat_type \"connection\"," + //csv 23:connection, translation-mechanism in NAT, eg. nat_local_to_public_ipv4 " t.public_ip_asn asn," + //csv 24:asn, AS (autonomous system) number, number of public IP network " t.client_public_ip_anonymized ip_anonym," + //csv 25:ip_anonym, anonymized IP of client (IPv4: 8 bits removed, IPv6: 72 bits removed) " (ndt.s2cspd*1000)::int ndt_download_kbit," + //csv 26:ndt_download_kbit, result of NDT downlink throughput test kbit/s " (ndt.c2sspd*1000)::int ndt_upload_kbit," + //csv 27 ndt_uoload_kbit, result of NDT uplink throughput test in kbit/s " country_geoip," + // country-code derived from public IP-address, eg. 'AT' " country_location," + // country-code derived from geo_location, eg. 'DE' " country_asn," + // country_code derived from AS, eg. 'EU' " data->>'region' region," + // si - region from geo location " data->>'municipality' municipality," + // si - municipality from geo location " data->>'settlement' settlement," + // si - settlement from geo location " data->>'whitespace' whitespot," + // si - whitespace from geo location " bytes_download," + // number of bytes downloaded during test (download and upload) (obsolete) " bytes_upload," + // number of bytes uploaded during test (download and upload) (obsolete) " test_if_bytes_download," + //downloaded bytes on interface during total test (inc. training, ping, without NDT) (obsolete) " test_if_bytes_upload," + //uploaded bytes on interface during total test (inc. training, ping, without NDT) (obsolete) " testdl_if_bytes_download," + //downloaded bytes on interface during download-test (without training-seq) " testdl_if_bytes_upload," + //uploaded bytes on interface during download-test (without training-seq) " testul_if_bytes_download," + //downloaded bytes on interface during upload-test (without training-seq) " testul_if_bytes_upload," + //downloaded bytes on interface during upload-test (without training-seq) " (t.nsec_download::float / 1000000) duration_download_ms," + //duration of download-test in ms " (t.nsec_upload::float / 1000000) duration_upload_ms," + //duration of upload-test in ms " (t.time_dl_ns::float / 1000000) time_dl_ms," + //relative start time of download-test in ms (ignoring training-phase) " (t.time_ul_ns::float / 1000000) time_ul_ms" + //relative start time of download-test in ms (ignoring training-phase) // " phone_type" + //(internal) radio type of phone: 0 no mobile radio, 1 GSM (incl. UMTS,LTE) 2 CDMA (obsolete) " FROM test t" + " LEFT JOIN device_map adm ON adm.codename=t.model" + " LEFT JOIN test_server ts ON ts.uid=t.server_id" + " LEFT JOIN test_ndt ndt ON t.uid=ndt.test_id" + " LEFT JOIN provider prov ON t.provider_id=prov.uid" + " LEFT JOIN provider mprov ON t.mobile_provider_id=mprov.uid" + " LEFT JOIN mccmnc2name msim ON t.mobile_sim_id=msim.uid" + " WHERE " + " t.deleted = false " + " AND t.status = 'FINISHED' " + " AND t.open_test_uuid = ? "; //System.out.println(sql); final String[] columns; PreparedStatement ps = null; ResultSet rs = null; final JSONObject response = new JSONObject(); try { ps = conn.prepareStatement(sql); //insert filter for accuracy /* double accuracy = Double.parseDouble(getSetting("rmbt_geo_accuracy_detail_limit")); ps.setDouble(1, accuracy); ps.setDouble(2, accuracy); ps.setDouble(3, accuracy); ps.setDouble(4, accuracy); ps.setDouble(5, accuracy); ps.setDouble(6, accuracy); */ //openTestIDs are starting with "O" if (openTestUUID != null && openTestUUID.startsWith("O")) { openTestUUID = openTestUUID.substring(1); } ps.setObject(1, openTestUUID, Types.OTHER); if (!ps.execute()) return null; rs = ps.getResultSet(); if (rs.next()) { //fetch data for every field for (int i = 0; i < openDataFieldsFull.length; i++) { //convert data to correct json response final Object obj = rs.getObject(openDataFieldsFull[i]); if (openDataBooleanFields.contains(openDataFieldsFull[i])) { if (obj == null) { response.put(openDataFieldsFull[i], false); } else { response.put(openDataFieldsFull[i], obj); } } else if (obj == null) { response.put(openDataFieldsFull[i], JSONObject.NULL); } else if (openDataNumberFields.contains(openDataFieldsFull[i])) { final String tmp = obj.toString().trim(); if (tmp.isEmpty()) response.put(openDataFieldsFull[i], JSONObject.NULL); else response.put(openDataFieldsFull[i], JSONObject.stringToValue(tmp)); } else { final String tmp = obj.toString().trim(); if (tmp.isEmpty()) response.put(openDataFieldsFull[i], JSONObject.NULL); else response.put(openDataFieldsFull[i], tmp); } } /* obsolete (now in database) //special threatment for lat/lng: if too low accuracy -> do not send back to client double accuracy = rs.getDouble("loc_accuracy"); if (accuracy > Double.parseDouble(settings.getString("RMBT_GEO_ACCURACY_DETAIL_LIMIT"))) { response.put("loc_accuracy", JSONObject.NULL); response.put("long", JSONObject.NULL); response.put("lat", JSONObject.NULL); } try { // do not output invalid zip-codes, must be 4 digits int zip_code = rs.getInt("zip_code"); if (zip_code <= 999 || zip_code > 9999) response.put("zip_code", JSONObject.NULL); } catch (final SQLException e) { System.out.println("Error on zip_code: " + e.toString()); }; */ //classify download, upload, ping, signal response.put("download_classification", Classification.classify(classification.THRESHOLD_DOWNLOAD, rs.getLong("download_kbit"))); response.put("upload_classification", Classification.classify(classification.THRESHOLD_UPLOAD, rs.getLong("upload_kbit"))); response.put("ping_classification", Classification.classify(classification.THRESHOLD_PING, rs.getLong("ping_ms") * 1000000)); //classify signal accordingly if ((rs.getString("signal_strength") != null || rs.getString("lte_rsrp") != null) && rs.getString("network_type") != null) { // signal available if (rs.getString("lte_rsrp") == null) { // use RSSI if (rs.getString("network_type").equals("WLAN")) { // RSSI for Wifi response.put("signal_classification", Classification .classify(classification.THRESHOLD_SIGNAL_WIFI, rs.getLong("signal_strength"))); } else { // RSSI for Mobile response.put("signal_classification", Classification.classify( classification.THRESHOLD_SIGNAL_MOBILE, rs.getLong("signal_strength"))); } } else // RSRP for LTE response.put("signal_classification", Classification .classify(classification.THRESHOLD_SIGNAL_RSRP, rs.getLong("lte_rsrp"))); } else { // no signal available response.put("signal_classification", JSONObject.NULL); } //also load download/upload-speed-data, signal data and location data if possible JSONObject speedCurve = new JSONObject(); JSONArray downloadSpeeds = new JSONArray(); JSONArray uploadSpeeds = new JSONArray(); JSONArray locArray = new JSONArray(); JSONArray signalArray = new JSONArray(); //Load speed data from database SpeedGraph speedGraph = new SpeedGraph(rs.getLong("test_uid"), Math.max(rs.getInt("num_threads"), rs.getInt("num_threads_ul")), conn); for (SpeedGraph.SpeedGraphItem item : speedGraph.getUpload()) { JSONObject obj = new JSONObject(); obj.put("time_elapsed", item.getTimeElapsed()); obj.put("bytes_total", item.getBytesTotal()); uploadSpeeds.put(obj); } for (SpeedGraph.SpeedGraphItem item : speedGraph.getDownload()) { JSONObject obj = new JSONObject(); obj.put("time_elapsed", item.getTimeElapsed()); obj.put("bytes_total", item.getBytesTotal()); downloadSpeeds.put(obj); } //Load signal strength from database SignalGraph sigGraph = new SignalGraph(rs.getLong("test_uid"), rs.getTimestamp("client_time").getTime(), conn); for (SignalGraph.SignalGraphItem item : sigGraph.getSignalList()) { JSONObject json = new JSONObject(); json.put("time_elapsed", item.getTimeElapsed()); json.put("network_type", item.getNetworkType()); json.put("signal_strength", item.getSignalStrength()); json.put("lte_rsrp", item.getLteRsrp()); json.put("lte_rsrq", item.getLteRsrq()); json.put("cat_technology", item.getCatTechnology()); signalArray.put(json); } //Load gps coordinates from database LocationGraph locGraph = new LocationGraph(rs.getLong("test_uid"), rs.getTimestamp("client_time").getTime(), conn); double totalDistance = locGraph.getTotalDistance(); for (LocationGraph.LocationGraphItem item : locGraph.getLocations()) { JSONObject json = new JSONObject(); json.put("time_elapsed", item.getTimeElapsed()); json.put("lat", item.getLatitude()); json.put("long", item.getLongitude()); json.put("loc_accuracy", (item.getAccuracy() > 0) ? item.getAccuracy() : JSONObject.NULL); locArray.put(json); } speedCurve.put("upload", uploadSpeeds); speedCurve.put("download", downloadSpeeds); speedCurve.put("signal", signalArray); speedCurve.put("location", locArray); response.put("speed_curve", speedCurve); //add total distance during test - but only if within bounds if ((totalDistance > 0) && totalDistance <= Double.parseDouble(getSetting("rmbt_geo_distance_detail_limit"))) response.put("distance", totalDistance); else response.put("distance", JSONObject.NULL); } else { //invalid open_uuid setStatus(Status.CLIENT_ERROR_NOT_FOUND); response.put("error", "invalid open-uuid"); } } catch (final JSONException e) { Logger.getLogger(OpenTestResource.class.getName()).log(Level.SEVERE, null, e); } catch (SQLException ex) { try { setStatus(Status.CLIENT_ERROR_NOT_FOUND); response.put("error", "invalid open-uuid"); } catch (JSONException ex1) { Logger.getLogger(OpenTestResource.class.getName()).log(Level.SEVERE, null, ex1); } Logger.getLogger(OpenTestResource.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (rs != null) rs.close(); if (ps != null) ps.close(); } catch (final SQLException e) { Logger.getLogger(OpenTestResource.class.getName()).log(Level.SEVERE, null, e); } } return response.toString(); }
From source file:com.netspective.axiom.sql.StoredProcedureParameter.java
/** * Extract the OUT parameter values from the callable statment and * assign them to the value of the parameter. *//*from w ww . j a v a 2s. c om*/ public void extract(ConnectionContext cc, CallableStatement stmt) throws SQLException { if (getType().getValueIndex() == StoredProcedureParameter.Type.IN) return; int index = this.getIndex(); QueryParameterType paramType = getSqlType(); int jdbcType = paramType.getJdbcType(); String identifier = paramType.getIdentifier(); // result sets are special if (identifier.equals(QueryParameterType.RESULTSET_IDENTIFIER)) { ResultSet rs = (ResultSet) stmt.getObject(index); QueryResultSet qrs = new QueryResultSet(getParent().getProcedure(), cc, rs); value.getValue(cc).setValue(qrs); return; } switch (jdbcType) { case Types.VARCHAR: value.getValue(cc).setTextValue(stmt.getString(index)); break; case Types.INTEGER: value.getValue(cc).setValue(new Integer(stmt.getInt(index))); break; case Types.DOUBLE: value.getValue(cc).setValue(new Double(stmt.getDouble(index))); break; case Types.CLOB: Clob clob = stmt.getClob(index); value.getValue(cc).setTextValue(clob.getSubString(1, (int) clob.length())); break; case java.sql.Types.ARRAY: Array array = stmt.getArray(index); value.getValue(cc).setValue(array); break; case java.sql.Types.BIGINT: long bigint = stmt.getLong(index); value.getValue(cc).setValue(new Long(bigint)); break; case java.sql.Types.BINARY: value.getValue(cc).setTextValue(new String(stmt.getBytes(index))); break; case java.sql.Types.BIT: boolean bit = stmt.getBoolean(index); value.getValue(cc).setValue(new Boolean(bit)); case java.sql.Types.BLOB: value.getValue(cc).setValue(stmt.getBlob(index)); break; case java.sql.Types.CHAR: value.getValue(cc).setTextValue(stmt.getString(index)); break; case java.sql.Types.DATE: value.getValue(cc).setValue(stmt.getDate(index)); break; case java.sql.Types.DECIMAL: value.getValue(cc).setValue(stmt.getBigDecimal(index)); break; case java.sql.Types.DISTINCT: value.getValue(cc).setValue(stmt.getObject(index)); break; case java.sql.Types.FLOAT: value.getValue(cc).setValue(new Float(stmt.getFloat(index))); break; case java.sql.Types.JAVA_OBJECT: value.getValue(cc).setValue(stmt.getObject(index)); break; case java.sql.Types.LONGVARBINARY: value.getValue(cc).setTextValue(new String(stmt.getBytes(index))); break; case java.sql.Types.LONGVARCHAR: value.getValue(cc).setTextValue(stmt.getString(index)); break; //case java.sql.Types.NULL: // value.getValue(cc).setValue(null); // break; case java.sql.Types.NUMERIC: value.getValue(cc).setValue(stmt.getBigDecimal(index)); break; case java.sql.Types.OTHER: value.getValue(cc).setValue(stmt.getObject(index)); break; case java.sql.Types.REAL: value.getValue(cc).setValue(new Float(stmt.getFloat(index))); break; //case java.sql.Types.REF: // Ref ref = stmt.getRef(index); // break; case java.sql.Types.SMALLINT: short sh = stmt.getShort(index); value.getValue(cc).setValue(new Short(sh)); break; case java.sql.Types.STRUCT: value.getValue(cc).setValue(stmt.getObject(index)); break; case java.sql.Types.TIME: value.getValue(cc).setValue(stmt.getTime(index)); break; case java.sql.Types.TIMESTAMP: value.getValue(cc).setValue(stmt.getTimestamp(index)); break; case java.sql.Types.TINYINT: byte b = stmt.getByte(index); value.getValue(cc).setValue(new Byte(b)); break; case java.sql.Types.VARBINARY: value.getValue(cc).setValue(stmt.getBytes(index)); break; default: throw new RuntimeException( "Unknown JDBC Type set for stored procedure parameter '" + this.getName() + "'."); } }
From source file:org.nuxeo.ecm.core.storage.sql.db.H2Fulltext.java
protected static String asString(Object data, int type) throws SQLException { if (data == null) { return ""; }/*from w w w . j a v a 2s. com*/ switch (type) { case Types.BIT: case Types.BOOLEAN: case Types.INTEGER: case Types.BIGINT: case Types.DECIMAL: case Types.DOUBLE: case Types.FLOAT: case Types.NUMERIC: case Types.REAL: case Types.SMALLINT: case Types.TINYINT: case Types.DATE: case Types.TIME: case Types.TIMESTAMP: case Types.LONGVARCHAR: case Types.CHAR: case Types.VARCHAR: return data.toString(); case Types.CLOB: try { if (data instanceof Clob) { data = ((Clob) data).getCharacterStream(); } return IOUtils.readStringAndClose((Reader) data, -1); } catch (IOException e) { throw DbException.convert(e); } case Types.VARBINARY: case Types.LONGVARBINARY: case Types.BINARY: case Types.JAVA_OBJECT: case Types.OTHER: case Types.BLOB: case Types.STRUCT: case Types.REF: case Types.NULL: case Types.ARRAY: case Types.DATALINK: case Types.DISTINCT: throw new SQLException("Unsupported column data type: " + type); default: return ""; } }
From source file:at.alladin.rmbt.controlServer.OpenTestResource.java
/** * Gets the JSON-Representation of all open-data-fields for one specific * open-test-uuid//from w w w . ja va2s . co m * @param openTestUUID * @return the json-string * * * columns in csv ("open data") * 1:open_uuid, * 2:open_test_uuid, * 3:time, * 4:cat_technology, * 5:network_type, * 6:lat, * 7:long, * 8:loc_src, * 9:zip_code, * 10:download_kbit, * 11:upload_kbit, * 12:ping_ms, * 13:signal_strength, * 14:server_name, * 15:test_duration, * 16:num_threads, * 17:platform, * 18:model, * 19:client_version, * 20:network_mcc_mnc, * 21:network_name, * 22:sim_mcc_mnc, * 23:connection, * 24:asn, * 25:ip_anonym, * 26:ndt_download_kbit, * 27:ndt_upload_kbit, * 28:implausible, * 29:lte_rsrp * * * Columns in test table * uid (internal) * uuid (private) * client_id * client_version * client_name * client_language (private) * token (private, obsolete) * server_id * port * use_ssl * * time * speed_upload * speed_download * ping_shortest * encryption * * client_public_ip (private) * plattform * os_version (internal) * api_level (internal) * device * model * product * phone_type (internal) * data_state (internal) * network_country (internal) * network_operator * network_operator_name * network_sim_country (internal) * network_sim_operator * network_sim_operator_name * wifi_ssid (private) * wifi_bssid (private) * wifi_network_id (private) * duration * num_threads * status * timezone (private) * bytes_download * bytes_upload * nsec_download * nsec_upload * server_ip * client_software_version * geo_lat * geo_long * network_type * location * signal_strength * software_revision * client_test_counter * nat_type * client_previous_test_status * public_ip_asn * speed_upload_log * speed_download_log * total_bytes_download * total_bytes_upload * wifi_link_speed * public_ip_rdns * public_ip_as_name * test_slot * provider_id * network_is_roaming (internal) * ping_shortest_log * run_ndt (internal) * num_threads_requested * client_public_ip_anonymized * zip_code * geo_provider * geo_accuracy * deleted (internal) * comment (internal) * open_uuid * client_time (internal) * zip_code_geo (internal) * mobile_provider_id * roaming_type * open_test_uuid * country_asn * country_location * test_if_bytes_download * test_if_bytes_upload * implausible * testdl_if_bytes_download * testdl_if_bytes_upload * testul_if_bytes_download * testul_if_bytes_upload * country_geoip * location_max_distance * location_max_distance_gps * network_group_name * network_group_type * time_dl_ns * time_ul_ns * num_threads_ul * lte_rsrp * lte_rsrq * mobile_network_id * mobile_sim_id * dist_prev * speed_prev * tag * ping_median * ping_median_log * client_ip_local_type (private) * * private: visible to user only (not public) * internal: not visible (neither user nor public) * */ private String getSingleOpenTest(String openTestUUID) { final String sql = "SELECT t.uid as test_uid, " + " ('O' || t.open_test_uuid) open_test_uuid," + //csv open_test_uuid, UUID prefixed with 'O' " to_char(t.time AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS') \"time\"," + " t.time full_time," + //csv: 3:time server time-stamp of start of measurement " t.client_time client_time," + //(internal) client time-stamp of start of measure " t.network_group_name cat_technology," + //csv 4:cat_technology " t.network_group_type network_type," + //csv 5:network_type " t.geo_lat lat," + //csv 6:lat " t.geo_long long," + // csv 7:long " t.geo_provider loc_src," + //csv 8:loc_src android: 'gps'/'network'; browser/iOS: '' (empty string) " t.geo_accuracy loc_accuracy, " + //accuracy of geo location in m /* //csv 6:lat " (CASE WHEN (t.geo_accuracy < ?) AND (t.geo_provider != 'manual') AND (t.geo_provider != 'geocoder') THEN" + " t.geo_lat" + " WHEN (t.geo_accuracy < ?) THEN" + " ROUND(t.geo_lat*1111)/1111" + // approx 100m " ELSE null" + " END) lat," + // csv 7:long " (CASE WHEN (t.geo_accuracy < ?) AND (t.geo_provider != 'manual') AND (t.geo_provider != 'geocoder') THEN" + " t.geo_long" + " WHEN (t.geo_accuracy < ?) THEN" + " ROUND(t.geo_long*741)/741 " + //approx 100m " ELSE null" + " END) long," + // csv 8:loc_src android: 'gps'/'network'; browser/iOS: '' (empty string) " (CASE WHEN ((t.geo_provider = 'manual') OR (t.geo_provider = 'geocoder')) THEN" + " 'rastered'" + //make raster transparent " ELSE t.geo_provider" + " END) loc_src," + // accuracy of geo location in m " (CASE WHEN (t.geo_accuracy < ?) AND (t.geo_provider != 'manual') AND (t.geo_provider != 'geocoder') " + " THEN t.geo_accuracy " + " WHEN (t.geo_accuracy < 100) AND ((t.geo_provider = 'manual') OR (t.geo_provider = 'geocoder')) THEN 100" + // limit accuracy to 100m " WHEN (t.geo_accuracy < ?) THEN t.geo_accuracy" + " ELSE null END) loc_accuracy, " + */ //csv 9:zip-code - defined as integer in data base, only meaningful for measurements in Austria " (CASE WHEN (t.zip_code < 1000 OR t.zip_code > 9999) THEN null ELSE t.zip_code END) zip_code," + // " zip_code_geo," + //(internal) zip-code, integer derived from geo_location, Austria only " t.speed_download download_kbit," + //csv 10:download_kbit " t.speed_upload upload_kbit," + //csv 11: upload_kbit " t.wifi_link_speed," + // nominal speed of wifi-link in mbit/s , Android-only " (t.ping_median::float / 1000000) ping_ms," + //median ping-time in ms (stored in ns in data base) " signal_strength," + //csv 13:signal_strength RSSI, mainly GSM/UMTS and Wifi, Android only, in dBm " lte_rsrp," + // csv 29: signal_strength RSRP, Android only, in dBm " lte_rsrq," + // signal quality RSRQ, Android only, in dB " ts.name server_name," + //csv 14:server_name, name of the test server used for download/upload (not applicable for JStest) " implausible, " + //csv 28:implausible, measurement not shown in map nor used in statistics, normally not visible " public_ip_as_name, " + //name of AS (not number) " duration test_duration," + //csv 15:test_duration, nominal duration of downlink and uplink throughput tests in seconds " num_threads_requested," + // number of threads requested by control-server " num_threads," + //csv 16:num_threads, number of threads used in downlink throughput test (uplink may differ) " num_threads_ul," + // number of threads used in uplink test " (CASE WHEN publish_public_data THEN COALESCE(t.plattform, t.client_name) ELSE '' END) platform," + //csv 17:platform; currently used: 'CLI'/'Android'/Applet/iOS/[from client_name: RMBTws, RMBTjs](null); (null) is used for RMBTjs //AKOS: IC " (CASE WHEN publish_public_data THEN COALESCE(adm.fullname, t.model) ELSE '' END) model," + //csv 18:model, translated t.model (model_native) to human readable form //AKOS: IC " (CASE WHEN publish_public_data THEN t.model ELSE '' END) model_native," + //device used for test; Android API 'model'; iOS:'product'; Browser: Browser-name (zB Firefox) //AKOS: IC " t.product product," + // product used for test; Android APO 'product'; iOS: '' (not used); Browser: same as for model (browser name) " t.client_software_version client_version," + //csv 19:client_version, SW-version of client software (not RMBT-client-version), eg. '1.3' " t.network_operator network_mcc_mnc," + //csv 20:network_mcc_mnc, mobile country and network code of current network (Android only), string, eg "232-12' " network_country," + //(internal) Android-only, country code derived by client from mobile network code // " network_is_roaming," + //(internal) roaming-status of mobile network, boolean or (null); Android-only (obsolete) " roaming_type," + //roaming-status of mobile network, integer: 0:not roaming,1:national,2:international,(null):unknown (eg. iOS) " t.network_operator_name network_name," + //csv 21:network_name, name of current mobile network as displayed on device (eg: '3likeHome') " t.network_sim_operator sim_mcc_mnc," + //csv 22:sim_mcc_mnc, home network of SIM (initial 5 digits from IMSI), eg '232-01' " t.network_sim_country sim_country," + //(internal) Android-only, country derived by client from SIM (country of home network) " COALESCE(mprov.name,msim.shortname,msim.name,prov.name) provider_name," + //pre-defined list of providers (based on provider_id) //TODO replace provider " t.nat_type \"connection\"," + //csv 23:connection, translation-mechanism in NAT, eg. nat_local_to_public_ipv4 " t.public_ip_asn asn," + //csv 24:asn, AS (autonomous system) number, number of public IP network " (CASE WHEN publish_public_data THEN t.client_public_ip_anonymized ELSE '' END) ip_anonym," + //csv 25:ip_anonym, anonymized IP of client (IPv4: 8 bits removed, IPv6: 72 bits removed) " (ndt.s2cspd*1000)::int ndt_download_kbit," + //csv 26:ndt_download_kbit, result of NDT downlink throughput test kbit/s " (ndt.c2sspd*1000)::int ndt_upload_kbit," + //csv 27 ndt_uoload_kbit, result of NDT uplink throughput test in kbit/s " country_geoip," + // country-code derived from public IP-address, eg. 'AT' " country_location," + // country-code derived from geo_location, eg. 'DE' " country_asn," + // country_code derived from AS, eg. 'EU' " data->>'region' region," + // si - region from geo location " data->>'municipality' municipality," + // si - municipality from geo location " data->>'settlement' settlement," + // si - settlement from geo location " data->>'whitespace' whitespot," + // si - whitespace from geo location " data->>'cell_id' cell_id," + // si - cell_id " data->>'cell_name' cell_name," + // si - cell_name " data->>'cell_id_multiple' cell_id_multiple," + // si - cell_id_multiple " bytes_download," + // number of bytes downloaded during test (download and upload) (obsolete) " bytes_upload," + // number of bytes uploaded during test (download and upload) (obsolete) " test_if_bytes_download," + //downloaded bytes on interface during total test (inc. training, ping, without NDT) (obsolete) " test_if_bytes_upload," + //uploaded bytes on interface during total test (inc. training, ping, without NDT) (obsolete) " testdl_if_bytes_download," + //downloaded bytes on interface during download-test (without training-seq) " testdl_if_bytes_upload," + //uploaded bytes on interface during download-test (without training-seq) " testul_if_bytes_download," + //downloaded bytes on interface during upload-test (without training-seq) " testul_if_bytes_upload," + //downloaded bytes on interface during upload-test (without training-seq) " (t.nsec_download::float / 1000000) duration_download_ms," + //duration of download-test in ms " (t.nsec_upload::float / 1000000) duration_upload_ms," + //duration of upload-test in ms " (t.time_dl_ns::float / 1000000) time_dl_ms," + //relative start time of download-test in ms (ignoring training-phase) " (t.time_ul_ns::float / 1000000) time_ul_ms," + //relative start time of download-test in ms (ignoring training-phase) // " phone_type" + //(internal) radio type of phone: 0 no mobile radio, 1 GSM (incl. UMTS,LTE) 2 CDMA (obsolete) " t.publish_public_data, " + " ((EXTRACT (EPOCH FROM (t.timestamp - t.time))) * 1000) speed_test_duration " + " FROM test t" + " LEFT JOIN device_map adm ON adm.codename=t.model" + " LEFT JOIN test_server ts ON ts.uid=t.server_id" + " LEFT JOIN test_ndt ndt ON t.uid=ndt.test_id" + " LEFT JOIN provider prov ON t.provider_id=prov.uid" + " LEFT JOIN provider mprov ON t.mobile_provider_id=mprov.uid" + " LEFT JOIN mccmnc2name msim ON t.mobile_sim_id=msim.uid" + " WHERE " + " t.deleted = false " + " AND t.status = 'FINISHED' " + " AND t.open_test_uuid = ? "; //System.out.println(sql); final String[] columns; PreparedStatement ps = null; ResultSet rs = null; final JSONObject response = new JSONObject(); try { ps = conn.prepareStatement(sql); //insert filter for accuracy /* double accuracy = Double.parseDouble(getSetting("rmbt_geo_accuracy_detail_limit")); ps.setDouble(1, accuracy); ps.setDouble(2, accuracy); ps.setDouble(3, accuracy); ps.setDouble(4, accuracy); ps.setDouble(5, accuracy); ps.setDouble(6, accuracy); */ //openTestIDs are starting with "O" if (openTestUUID != null && openTestUUID.startsWith("O")) { openTestUUID = openTestUUID.substring(1); } ps.setObject(1, openTestUUID, Types.OTHER); if (!ps.execute()) return null; rs = ps.getResultSet(); if (rs.next()) { final boolean isPublishPublicData = rs.getBoolean("publish_public_data"); //fetch data for every field for (int i = 0; i < openDataFieldsFull.length; i++) { //convert data to correct json response final Object obj = rs.getObject(openDataFieldsFull[i]); if (openDataBooleanFields.contains(openDataFieldsFull[i])) { if (obj == null) { response.put(openDataFieldsFull[i], false); } else { response.put(openDataFieldsFull[i], obj); } } else if (obj == null) { response.put(openDataFieldsFull[i], JSONObject.NULL); } else if (openDataNumberFields.contains(openDataFieldsFull[i])) { final String tmp = obj.toString().trim(); if (tmp.isEmpty()) response.put(openDataFieldsFull[i], JSONObject.NULL); else response.put(openDataFieldsFull[i], JSONObject.stringToValue(tmp)); } else { final String tmp = obj.toString().trim(); if (tmp.isEmpty()) response.put(openDataFieldsFull[i], JSONObject.NULL); else response.put(openDataFieldsFull[i], tmp); } } /* obsolete (now in database) //special threatment for lat/lng: if too low accuracy -> do not send back to client double accuracy = rs.getDouble("loc_accuracy"); if (accuracy > Double.parseDouble(settings.getString("RMBT_GEO_ACCURACY_DETAIL_LIMIT"))) { response.put("loc_accuracy", JSONObject.NULL); response.put("long", JSONObject.NULL); response.put("lat", JSONObject.NULL); } try { // do not output invalid zip-codes, must be 4 digits int zip_code = rs.getInt("zip_code"); if (zip_code <= 999 || zip_code > 9999) response.put("zip_code", JSONObject.NULL); } catch (final SQLException e) { System.out.println("Error on zip_code: " + e.toString()); }; */ //classify download, upload, ping, signal response.put("download_classification", Classification.classify(classification.THRESHOLD_DOWNLOAD, rs.getLong("download_kbit"))); response.put("upload_classification", Classification.classify(classification.THRESHOLD_UPLOAD, rs.getLong("upload_kbit"))); response.put("ping_classification", Classification.classify(classification.THRESHOLD_PING, rs.getLong("ping_ms") * 1000000)); //classify signal accordingly if ((rs.getString("signal_strength") != null || rs.getString("lte_rsrp") != null) && rs.getString("network_type") != null) { // signal available if (rs.getString("lte_rsrp") == null) { // use RSSI if (rs.getString("network_type").equals("WLAN")) { // RSSI for Wifi response.put("signal_classification", Classification .classify(classification.THRESHOLD_SIGNAL_WIFI, rs.getLong("signal_strength"))); } else { // RSSI for Mobile response.put("signal_classification", Classification.classify( classification.THRESHOLD_SIGNAL_MOBILE, rs.getLong("signal_strength"))); } } else // RSRP for LTE response.put("signal_classification", Classification .classify(classification.THRESHOLD_SIGNAL_RSRP, rs.getLong("lte_rsrp"))); } else { // no signal available response.put("signal_classification", JSONObject.NULL); } //also load download/upload-speed-data, signal data and location data if possible JSONObject speedCurve = new JSONObject(); JSONArray downloadSpeeds = new JSONArray(); JSONArray uploadSpeeds = new JSONArray(); JSONArray locArray = new JSONArray(); JSONArray signalArray = new JSONArray(); //Load speed data from database SpeedGraph speedGraph = new SpeedGraph(rs.getLong("test_uid"), Math.max(rs.getInt("num_threads"), rs.getInt("num_threads_ul")), conn); for (SpeedGraph.SpeedGraphItem item : speedGraph.getUpload()) { JSONObject obj = new JSONObject(); obj.put("time_elapsed", item.getTimeElapsed()); obj.put("bytes_total", item.getBytesTotal()); uploadSpeeds.put(obj); } for (SpeedGraph.SpeedGraphItem item : speedGraph.getDownload()) { JSONObject obj = new JSONObject(); obj.put("time_elapsed", item.getTimeElapsed()); obj.put("bytes_total", item.getBytesTotal()); downloadSpeeds.put(obj); } //Load signal strength from database SignalGraph sigGraph = new SignalGraph(rs.getLong("test_uid"), rs.getTimestamp("client_time").getTime(), conn); for (SignalGraph.SignalGraphItem item : sigGraph.getSignalList()) { JSONObject json = new JSONObject(); json.put("time_elapsed", item.getTimeElapsed()); json.put("network_type", item.getNetworkType()); json.put("signal_strength", item.getSignalStrength()); json.put("lte_rsrp", item.getLteRsrp()); json.put("lte_rsrq", item.getLteRsrq()); json.put("cat_technology", item.getCatTechnology()); signalArray.put(json); } if (isPublishPublicData) { //Load gps coordinates from database LocationGraph locGraph = new LocationGraph(rs.getLong("test_uid"), rs.getTimestamp("client_time").getTime(), conn); double totalDistance = locGraph.getTotalDistance(); for (LocationGraph.LocationGraphItem item : locGraph.getLocations()) { JSONObject json = new JSONObject(); json.put("time_elapsed", item.getTimeElapsed()); json.put("lat", item.getLatitude()); json.put("long", item.getLongitude()); json.put("loc_accuracy", (item.getAccuracy() > 0) ? item.getAccuracy() : JSONObject.NULL); locArray.put(json); } //add total distance during test - but only if within bounds if ((totalDistance > 0) && totalDistance <= Double.parseDouble(getSetting("rmbt_geo_distance_detail_limit"))) response.put("distance", totalDistance); else response.put("distance", JSONObject.NULL); } else { response.put("distance", JSONObject.NULL); } final TestStatDao testStatDao = new TestStatDao(conn); final TestStat ts = testStatDao.getById(rs.getLong("test_uid")); if (ts != null) { response.put("extended_test_stat", ts.toJsonObject()); } speedCurve.put("upload", uploadSpeeds); speedCurve.put("download", downloadSpeeds); speedCurve.put("signal", signalArray); speedCurve.put("location", locArray); response.put("speed_curve", speedCurve); } else { //invalid open_uuid setStatus(Status.CLIENT_ERROR_NOT_FOUND); response.put("error", "invalid open-uuid"); } } catch (final JSONException e) { Logger.getLogger(OpenTestResource.class.getName()).log(Level.SEVERE, null, e); } catch (SQLException ex) { try { setStatus(Status.CLIENT_ERROR_NOT_FOUND); response.put("error", "invalid open-uuid"); } catch (JSONException ex1) { Logger.getLogger(OpenTestResource.class.getName()).log(Level.SEVERE, null, ex1); } Logger.getLogger(OpenTestResource.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (rs != null) rs.close(); if (ps != null) ps.close(); } catch (final SQLException e) { Logger.getLogger(OpenTestResource.class.getName()).log(Level.SEVERE, null, e); } } return response.toString(); }
From source file:es.juntadeandalucia.panelGestion.negocio.utiles.JDBCConnector.java
/** * This method executes a low level insert with the data values * and data types (java.sql.Types) specified in a Map. * This is a way to improve the performance of data insertion. * * @param sql escaped SQL to avoid SQL-I * @param line the line to inser/* ww w . j a v a 2s. co m*/ * @param columns of the table * @return number of affected rows * @throws Exception exception thrown */ public int executeLineInsertLowLevel(String sql, String[] line, List<ColumnVO> columns) throws Exception { Exception error = null; int numRowsAffected = 0; if (columns.size() > 0) { Connection connection = null; PreparedStatement preparedStmnt = null; try { DataSource dataSource = poolDataSources.get(schemaId); connection = dataSource.getConnection(); connection.setAutoCommit(false); preparedStmnt = connection.prepareStatement(sql); String coordinateX = null; String coordinateY = null; int paramPosition = 1; for (ColumnVO column : columns) { Integer filePosition = column.getFilePosition(); String dataValue; Integer dataType = column.getSqlType(); if (column.isCoordinateX()) { dataValue = line[filePosition]; coordinateX = dataValue; preparedStmnt.setObject(paramPosition, dataValue, dataType); } else if (column.isCoordinateY()) { dataValue = line[filePosition]; coordinateY = dataValue; preparedStmnt.setObject(paramPosition, dataValue, dataType); } else if (column.isFromCoordinates()) { int coordXIndex = column.getFileCoordinateXPosition(); int coordYIndex = column.getFileCoordinateYPosition(); coordinateX = line[coordXIndex]; coordinateY = line[coordYIndex]; continue; } else if (dataType == Types.OTHER) { // it is a geometry // ((org.postgresql.PGConnection)connection).addDataType(column.getName(), // column.getTypeClass()); dataValue = line[filePosition]; preparedStmnt.setObject(paramPosition, dataValue); } else { dataValue = line[filePosition]; if (StringUtils.isEmpty(dataValue)) { preparedStmnt.setNull(paramPosition, dataType); } else { preparedStmnt.setObject(paramPosition, dataValue, dataType); } } paramPosition++; } if ((coordinateX != null) && (coordinateY != null)) { String pointWKT = Utils.getPointWKTFromCoordinates(coordinateX, coordinateY); preparedStmnt.setObject(paramPosition, pointWKT); } numRowsAffected = preparedStmnt.executeUpdate(); connection.commit(); } catch (SQLException e) { error = e; } finally { if (preparedStmnt != null) { try { preparedStmnt.close(); } catch (SQLException se2) { log.warn("No se pudo cerrar el statment: ".concat(se2.getLocalizedMessage())); } } if (connection != null) { try { if (error != null) { connection.rollback(); } } catch (SQLException se) { log.warn("Se produjo un error al manejar la conexin: ".concat(se.getLocalizedMessage())); } try { connection.close(); } catch (SQLException se) { log.warn("Se produjo un error al intentar cerrar la conexin: " .concat(se.getLocalizedMessage())); } } } if (error != null) { throw error; } } return numRowsAffected; }
From source file:org.apache.openjpa.jdbc.schema.Column.java
/** * Return true if this column is compatible with the given JDBC type * from {@link Types} and size.//from w ww. j a v a 2s . c om */ public boolean isCompatible(int type, String typeName, int size, int decimals) { if (type == Types.OTHER || getType() == Types.OTHER) return true; // note that the given size is currently ignored, but may be useful // to dynamically-populating subclasses switch (getType()) { case Types.BIT: case Types.TINYINT: case Types.BIGINT: case Types.INTEGER: case Types.NUMERIC: case Types.SMALLINT: case Types.DECIMAL: case Types.DOUBLE: case Types.FLOAT: case Types.REAL: switch (type) { case Types.BIT: case Types.TINYINT: case Types.BIGINT: case Types.INTEGER: case Types.NUMERIC: case Types.SMALLINT: case Types.DECIMAL: case Types.DOUBLE: case Types.FLOAT: case Types.REAL: return true; default: return false; } case Types.BINARY: case Types.BLOB: case Types.LONGVARBINARY: case Types.VARBINARY: case Types.OTHER: switch (type) { case Types.BINARY: case Types.BLOB: case Types.LONGVARBINARY: case Types.VARBINARY: case Types.OTHER: return true; default: return false; } case Types.CLOB: case Types.CHAR: case Types.LONGVARCHAR: case Types.VARCHAR: switch (type) { case Types.CLOB: case Types.CHAR: case Types.LONGVARCHAR: case Types.VARCHAR: case Types.DATE: case Types.TIME: case Types.TIMESTAMP: return true; default: return false; } case Types.DATE: case Types.TIME: case Types.TIMESTAMP: switch (type) { case Types.LONGVARCHAR: case Types.CLOB: case Types.VARCHAR: case Types.DATE: case Types.TIME: case Types.TIMESTAMP: return true; default: return false; } case 2007: // Oracle-defined opaque type code for XMLType switch (type) { case Types.CHAR: case Types.LONGVARCHAR: case Types.VARCHAR: case Types.CLOB: case Types.BLOB: return true; default: return false; } default: return type == getType(); } }