List of usage examples for java.sql ResultSet getObject
Object getObject(String columnLabel) throws SQLException;
Gets the value of the designated column in the current row of this ResultSet object as an Object in the Java programming language.
From source file:com.streamsets.pipeline.lib.jdbc.JdbcUtil.java
public LinkedHashMap<String, Field> resultSetToFields(ResultSet rs, int maxClobSize, int maxBlobSize, Map<String, DataType> columnsToTypes, ErrorRecordHandler errorRecordHandler, UnknownTypeAction unknownTypeAction, Set<String> recordHeader, boolean timestampToString) throws SQLException, StageException { ResultSetMetaData md = rs.getMetaData(); LinkedHashMap<String, Field> fields = new LinkedHashMap<>(md.getColumnCount()); for (int i = 1; i <= md.getColumnCount(); i++) { try {//from w w w .j av a 2s.com if (recordHeader == null || !recordHeader.contains(md.getColumnName(i))) { DataType dataType = columnsToTypes.get(md.getColumnName(i)); Field field = resultToField(md, rs, i, maxClobSize, maxBlobSize, dataType == null ? DataType.USE_COLUMN_TYPE : dataType, unknownTypeAction, timestampToString); fields.put(md.getColumnLabel(i), field); } } catch (SQLException e) { errorRecordHandler.onError(JdbcErrors.JDBC_13, e.getMessage(), e); } catch (IOException e) { errorRecordHandler.onError(JdbcErrors.JDBC_03, md.getColumnName(i), rs.getObject(i), e); } } return fields; }
From source file:com.glaf.dts.transform.MxTransformManager.java
@SuppressWarnings("unchecked") protected List<Map<String, Object>> prepare(QueryDefinition query, Map<String, Object> paramMap) { logger.debug("-------------------------2 start------------------------"); List<Map<String, Object>> resultList = new java.util.ArrayList<Map<String, Object>>(); Connection conn = null;// ww w . j a v a2 s . c o m PreparedStatement psmt = null; ResultSet rs = null; ResultSetMetaData rsmd = null; try { Database database = databaseService.getDatabaseById(query.getDatabaseId()); if (database != null) { conn = DBConnectionFactory.getConnection(database.getName()); } else { conn = DBConnectionFactory.getConnection(); } logger.debug("-------------------------2 connection------------------------"); SqlExecutor sqlExecutor = JdbcUtils.rebuildSQL(query.getSql(), paramMap); logger.debug("--2 sql:" + sqlExecutor.getSql()); psmt = conn.prepareStatement(sqlExecutor.getSql()); if (sqlExecutor.getParameter() != null) { List<Object> values = (List<Object>) sqlExecutor.getParameter(); JdbcUtils.fillStatement(psmt, values); } rs = psmt.executeQuery(); logger.debug("-------------------------2 executeQuery------------------------"); rsmd = rs.getMetaData(); int count = rsmd.getColumnCount(); while (rs.next()) { Map<String, Object> rowMap = new java.util.HashMap<String, Object>(); for (int i = 1; i <= count; i++) { String columnName = rsmd.getColumnLabel(i); if (null == columnName || 0 == columnName.length()) { columnName = rsmd.getColumnName(i); } try { rowMap.put(columnName, rs.getObject(i)); } catch (SQLException ex) { rowMap.put(columnName, rs.getString(i)); } } resultList.add(rowMap); } query.setResultList(resultList); // logger.debug("resultList:" + resultList); return resultList; } catch (Exception ex) { logger.error(ex); ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(rs); JdbcUtils.close(psmt); JdbcUtils.close(conn); logger.debug("-------------------------2 end------------------------"); } }
From source file:com.itemanalysis.jmetrik.stats.itemanalysis.ItemAnalysis.java
public void summarize() throws IllegalArgumentException, SQLException { Statement stmt = null;//from w w w . jav a2s.co m ResultSet rs = null; int missingCount = 0; int numberOfSubscales = this.numberOfSubscales(); try { //connect to db Table sqlTable = new Table(tableName.getNameForDatabase()); SelectQuery select = new SelectQuery(); for (VariableAttributes v : variables) { select.addColumn(sqlTable, v.getName().nameForDatabase()); } stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = stmt.executeQuery(select.toString()); //create test summary object testSummary = new TestSummary(numberOfItems, numberOfSubscales, cutScores, variables, unbiased, deletedReliability, showCsem); Object response = null; Double responseScore = null; RawScore rawScore = null; ClassicalItem tempItem = null; int[] resposneVectorIndex = null; Object[] responseVector = null; Double[] scoreVector = null; //loop over examinees while (rs.next()) { //loop over items to compute RawScore rawScore = new RawScore(numberOfItems); missingCount = 0; for (VariableAttributes v : variables) { tempItem = item.get(v.positionInDb()); if (tempItem == null) { tempItem = new ClassicalItem(v, biasCorrection, allCategories, pearsonCorrelation, dIndex); item.put(v.positionInDb(), tempItem); } response = rs.getObject(v.getName().nameForDatabase()); //count missing responses per examinee if (response == null || response.equals("")) {//FIXME need to allow a space " " or other special codes to be viewed as missing data missingCount++; } responseScore = v.getItemScoring().computeItemScore(response); rawScore.increment(responseScore); rawScore.incrementResponseVector(v.positionInDb(), response, responseScore); rawScore.incrementSubScaleScore(v.getItemGroup(), responseScore); } //only use complete cases if listwise deletion is specified //otherwise a missing item response is scored as 0 if ((listwiseDeletion && missingCount == 0) || !listwiseDeletion) { // System.out.println("TEST: " + listwiseDeletion + " " + (missingCount==0) + " " + (listwiseDeletion && missingCount==0)); testSummary.increment(rawScore); if (numberOfSubscales > 1) testSummary.incrementPartTestReliability(rawScore); //loop over items to compute item analysis responseVector = rawScore.getResponseVector(); resposneVectorIndex = rawScore.getResponseVectorIndex(); scoreVector = rawScore.getScoreVector(); for (int i = 0; i < responseVector.length; i++) { if (showItemStats) { item.get(resposneVectorIndex[i]).increment(rawScore, responseVector[i]); } for (int j = i; j < responseVector.length; j++) { testSummary.incrementReliability(i, j, scoreVector[i], scoreVector[j]); } } } updateProgress(); } //end loop over examinees if (dIndex) { double[] bounds = testSummary.getDIndexBounds(); computeDindex(bounds[0], bounds[1]); } } catch (SQLException ex) { throw ex; } finally { if (rs != null) rs.close(); if (stmt != null) stmt.close(); conn.setAutoCommit(true); } }
From source file:edu.ku.brc.specify.conversion.BasicSQLUtils.java
/** * @param conn //from www . j a va 2s .co m * @param sql * @return */ public static Vector<Object> querySingleCol(final Connection conn, final String sql) { Vector<Object> list = new Vector<Object>(); Statement stmt = null; Connection connection = null; boolean doCloseConn = false; boolean doSkipConnSet = false; boolean isStale = true; int tries = 0; while (isStale && tries < 3) { try { if (!doSkipConnSet) { if (conn != null) { connection = conn; } else if (dbConn != null) { connection = dbConn; } else { connection = DBConnection.getInstance().createConnection(); doCloseConn = true; } } tries++; if (connection != null) { stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery(sql); ResultSetMetaData metaData = rs.getMetaData(); int numCols = metaData.getColumnCount(); if (numCols > 1) { log.warn("Query has " + numCols + " columns and should only have one."); } while (rs.next()) { list.add(rs.getObject(1)); } rs.close(); isStale = false; } else { isStale = true; } } catch (CommunicationsException ex) { connection = DBConnection.getInstance().createConnection(); doCloseConn = true; doSkipConnSet = true; } catch (SQLException ex) { ex.printStackTrace(); if (!skipTrackExceptions) { edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex); } } finally { if (stmt != null) { try { stmt.close(); } catch (Exception ex) { } } } if (!isStale && connection != null && doCloseConn) { try { connection.close(); } catch (Exception ex) { } } } return list; }
From source file:TerminalMonitor.java
static public void processResults(ResultSet results) throws SQLException { try {/* w w w . j a v a2 s .c o m*/ ResultSetMetaData meta = results.getMetaData(); StringBuffer bar = new StringBuffer(); StringBuffer buffer = new StringBuffer(); int cols = meta.getColumnCount(); int row_count = 0; int i, width = 0; // Prepare headers for each of the columns // The display should look like: // -------------------------------------- // | Column One | Column Two | // -------------------------------------- // | Row 1 Value | Row 1 Value | // -------------------------------------- // create the bar that is as long as the total of all columns for (i = 1; i <= cols; i++) { width += meta.getColumnDisplaySize(i); } width += 1 + cols; for (i = 0; i < width; i++) { bar.append('-'); } bar.append('\n'); buffer.append(bar.toString() + "|"); // After the first bar goes the column labels for (i = 1; i <= cols; i++) { StringBuffer filler = new StringBuffer(); String label = meta.getColumnLabel(i); int size = meta.getColumnDisplaySize(i); int x; // If the label is longer than the column is wide, // then we truncate the column label if (label.length() > size) { label = label.substring(0, size); } // If the label is shorter than the column, pad it with spaces if (label.length() < size) { int j; x = (size - label.length()) / 2; for (j = 0; j < x; j++) { filler.append(' '); } label = filler + label + filler; if (label.length() > size) { label = label.substring(0, size); } else { while (label.length() < size) { label += " "; } } } // Add the column header to the buffer buffer.append(label + "|"); } // Add the lower bar buffer.append("\n" + bar.toString()); // Format each row in the result set and add it on while (results.next()) { row_count++; buffer.append('|'); // Format each column of the row for (i = 1; i <= cols; i++) { StringBuffer filler = new StringBuffer(); Object value = results.getObject(i); int size = meta.getColumnDisplaySize(i); String str; if (results.wasNull()) { str = "NULL"; } else { str = value.toString(); } if (str.length() > size) { str = str.substring(0, size); } if (str.length() < size) { int j, x; x = (size - str.length()) / 2; for (j = 0; j < x; j++) { filler.append(' '); } str = filler + str + filler; if (str.length() > size) { str = str.substring(0, size); } else { while (str.length() < size) { str += " "; } } } buffer.append(str + "|"); } buffer.append("\n"); } // Stick a row count up at the top if (row_count == 0) { buffer = new StringBuffer("No rows selected.\n"); } else if (row_count == 1) { buffer = new StringBuffer("1 row selected.\n" + buffer.toString() + bar.toString()); } else { buffer = new StringBuffer(row_count + " rows selected.\n" + buffer.toString() + bar.toString()); } System.out.print(buffer.toString()); System.out.flush(); } catch (SQLException e) { throw e; } finally { try { results.close(); } catch (SQLException e) { } } }
From source file:edu.umd.cs.marmoset.modelClasses.TestOutcome.java
/** * Populate a TestOutcome from a ResultSet that is positioned * at a row of the test_outcomes table./*from w w w . j a v a2s. c om*/ * * @param rs the ResultSet returned by the database. * @param startingFrom index specifying where to start fetching attributes from; * useful if the row contains attributes from multiple tables * @throws SQLException */ public int fetchValues(ResultSet rs, int startingFrom) throws SQLException { setTestRunPK(rs.getInt(startingFrom++)); setTestType(TestType.valueOfAnyCase(rs.getString(startingFrom++))); setTestNumber(rs.getString(startingFrom++)); setOutcome(asOutcomeType(rs.getString(startingFrom++))); setPointValue(rs.getInt(startingFrom++)); setTestName(rs.getString(startingFrom++)); setShortTestResult(rs.getString(startingFrom++)); setLongTestResult(rs.getString(startingFrom++)); setExceptionClassName(rs.getString(startingFrom++)); setCoarsestCoverageLevel(CoverageLevel.fromString(rs.getString(startingFrom++))); setExceptionSourceCoveredElsewhere(rs.getBoolean(startingFrom++)); setDetails(rs.getObject(startingFrom++)); setExecutionTimeMillis(rs.getInt(startingFrom++)); limitSizes(); return startingFrom++; }
From source file:edu.ku.brc.specify.toycode.RegPivot.java
/** * @param newTblName//w ww .j ava2 s. co m * @param stmt * @param pStmt * @param fillSQL * @param secInx * @param dbFieldTypes * @param dbFieldNames * @param inxToName * @return * @throws SQLException */ private int fillRegisterTable(final String newTblName, final Statement stmt, final PreparedStatement pStmt, final String fillSQL, final int secInx, final Vector<Integer> dbFieldTypes, final Vector<String> dbFieldNames, final HashMap<Integer, String> inxToName) throws SQLException { System.out.println("Filling Register Table."); int instCnt = 0; System.out.println(fillSQL); ResultSet rs = stmt.executeQuery(fillSQL); ResultSetMetaData rsmd = rs.getMetaData(); HashMap<String, Integer> nameToIndex = new HashMap<String, Integer>(); for (int c = 1; c <= rsmd.getColumnCount(); c++) { nameToIndex.put(rsmd.getColumnName(c), c); System.out.println(c + " - " + rsmd.getColumnName(c)); } //int nameInx = nameToIndex.get("i.Name"); boolean debug = false; String prevRegId = null; HashMap<String, HashMap<String, Object>> instHash = new HashMap<String, HashMap<String, Object>>(); HashMap<String, HashMap<String, Object>> divHash = new HashMap<String, HashMap<String, Object>>(); HashMap<String, HashMap<String, Object>> dspHash = new HashMap<String, HashMap<String, Object>>(); HashMap<String, HashMap<String, Object>> colHash = new HashMap<String, HashMap<String, Object>>(); HashMap<String, Object> nameToVals = new HashMap<String, Object>(); while (rs.next()) { String regId = rs.getString(1); if (prevRegId == null) prevRegId = regId; for (int i = 1; i < secInx; i++) { if (debug) System.out.println("Put: " + dbFieldNames.get(i - 1) + " " + dbFieldTypes.get(i - 1) + " = " + rs.getObject(i)); nameToVals.put(dbFieldNames.get(i - 1), rs.getObject(i)); } String name = rs.getString(secInx); name = StringUtils.replace(name, "(", "_"); name = StringUtils.replace(name, ")", "_"); if (name.equals("reg_type")) { String strVal = (String) rs.getObject(secInx + 2); name = strVal + "_number"; nameToVals.put(name, regId); if (debug) System.out.println("Put: " + name + " = " + regId); } else { Integer intVal = (Integer) rs.getObject(secInx + 1); String strVal = (String) rs.getObject(secInx + 2); nameToVals.put(name, strVal != null ? strVal : intVal); if (debug) System.out.println("Put: " + name + " = " + intVal + " / " + strVal); } if (debug) System.out.println("-------------------------------------------"); if (!prevRegId.equals(regId)) { String instNum = (String) nameToVals.get("Institution_number"); String divNum = (String) nameToVals.get("Division_number"); String dspNum = (String) nameToVals.get("Discipline_number"); String colNum = (String) nameToVals.get("Collection_number"); if (StringUtils.isNotEmpty(instNum)) { copyHash(instNum, instHash, nameToVals); } if (StringUtils.isNotEmpty(divNum)) { copyHash(divNum, divHash, nameToVals); } if (StringUtils.isNotEmpty(dspNum)) { copyHash(dspNum, dspHash, nameToVals); } if (StringUtils.isNotEmpty(colNum)) { // 1288612353.83 String cn = (String) nameToVals.get("Collection_number"); copyHash(colNum, colHash, nameToVals); } /*{ System.err.println("ID is empty:"); for (String key : nameToVals.keySet()) { System.out.println("--: "+key+" = "+nameToVals.get(key)); } System.err.println("==============="); }*/ prevRegId = regId; nameToVals.clear(); } } writeHash(instHash, 0, pStmt, dbFieldTypes, dbFieldNames, inxToName); writeHash(divHash, 1, pStmt, dbFieldTypes, dbFieldNames, inxToName); writeHash(dspHash, 2, pStmt, dbFieldTypes, dbFieldNames, inxToName); writeHash(colHash, 3, pStmt, dbFieldTypes, dbFieldNames, inxToName); String alterSQL = "ALTER TABLE " + newTblName + " ADD Lookup VARCHAR(64) AFTER IP"; BasicSQLUtils.update(connection, alterSQL); alterSQL = "ALTER TABLE " + newTblName + " ADD Country VARCHAR(64) AFTER Lookup"; BasicSQLUtils.update(connection, alterSQL); alterSQL = "ALTER TABLE " + newTblName + " ADD City VARCHAR(64) AFTER Country"; BasicSQLUtils.update(connection, alterSQL); return instCnt; }
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.jav a2 s. c o 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 " 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.alibaba.wasp.jdbc.TestPreparedStatement.java
public void testObject() throws SQLException { Statement stat = conn.createStatement(); ResultSet rs; stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))"); stat.execute("INSERT INTO TEST VALUES(1, 'Hello')"); PreparedStatement prep = conn .prepareStatement("SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? FROM TEST"); prep.setObject(1, Boolean.TRUE); prep.setObject(2, "Abc"); prep.setObject(3, new BigDecimal("10.2")); prep.setObject(4, new Byte((byte) 0xff)); prep.setObject(5, new Short(Short.MAX_VALUE)); prep.setObject(6, new Integer(Integer.MIN_VALUE)); prep.setObject(7, new Long(Long.MAX_VALUE)); prep.setObject(8, new Float(Float.MAX_VALUE)); prep.setObject(9, new Double(Double.MAX_VALUE)); prep.setObject(10, Date.valueOf("2001-02-03")); prep.setObject(11, Time.valueOf("04:05:06")); prep.setObject(12, Timestamp.valueOf("2001-02-03 04:05:06.123456789")); prep.setObject(13, new java.util.Date(Date.valueOf("2001-02-03").getTime())); prep.setObject(14, new byte[] { 10, 20, 30 }); prep.setObject(15, new Character('a'), Types.OTHER); prep.setObject(16, "2001-01-02", Types.DATE); // converting to null seems strange... prep.setObject(17, "2001-01-02", Types.NULL); prep.setObject(18, "3.725", Types.DOUBLE); prep.setObject(19, "23:22:21", Types.TIME); prep.setObject(20, new java.math.BigInteger("12345"), Types.OTHER); rs = prep.executeQuery();//from w w w . j a v a 2 s. c o m rs.next(); assertTrue(rs.getObject(1).equals(Boolean.TRUE)); assertTrue(rs.getObject(2).equals("Abc")); assertTrue(rs.getObject(3).equals(new BigDecimal("10.2"))); assertTrue(rs.getObject(4).equals((byte) 0xff)); assertTrue(rs.getObject(5).equals(new Short(Short.MAX_VALUE))); assertTrue(rs.getObject(6).equals(new Integer(Integer.MIN_VALUE))); assertTrue(rs.getObject(7).equals(new Long(Long.MAX_VALUE))); assertTrue(rs.getObject(8).equals(new Float(Float.MAX_VALUE))); assertTrue(rs.getObject(9).equals(new Double(Double.MAX_VALUE))); assertTrue(rs.getObject(10).equals(Date.valueOf("2001-02-03"))); assertEquals("04:05:06", rs.getObject(11).toString()); assertTrue(rs.getObject(11).equals(Time.valueOf("04:05:06"))); assertTrue(rs.getObject(12).equals(Timestamp.valueOf("2001-02-03 04:05:06.123456789"))); assertTrue(rs.getObject(13).equals(Timestamp.valueOf("2001-02-03 00:00:00"))); assertEquals(new byte[] { 10, 20, 30 }, (byte[]) rs.getObject(14)); assertTrue(rs.getObject(15).equals('a')); assertTrue(rs.getObject(16).equals(Date.valueOf("2001-01-02"))); assertTrue(rs.getObject(17) == null && rs.wasNull()); assertTrue(rs.getObject(18).equals(new Double(3.725))); assertTrue(rs.getObject(19).equals(Time.valueOf("23:22:21"))); assertTrue(rs.getObject(20).equals(new java.math.BigInteger("12345"))); // } else if(x instanceof java.io.Reader) { // return session.createLob(Value.CLOB, // TypeConverter.getInputStream((java.io.Reader)x), 0); // } else if(x instanceof java.io.InputStream) { // return session.createLob(Value.BLOB, (java.io.InputStream)x, 0); // } else { // return ValueBytes.get(TypeConverter.serialize(x)); stat.execute("DROP TABLE TEST"); }
From source file:edu.ku.brc.specify.toycode.RegPivot.java
/** * @param newTblName/*from w ww . j ava2 s.c om*/ * @param stmt * @param pStmt * @param fillSQL * @param secInx * @param dbFieldTypes * @param dbFieldNames * @param inxToName * @return * @throws SQLException */ private int fillTrackTableX(final String newTblName, final Statement stmt, final PreparedStatement pStmt, final String fillSQL, final int secInx, final Vector<Integer> dbFieldTypes, final Vector<String> dbFieldNames, final HashMap<Integer, String> inxToName) throws SQLException { System.out.println("Filling Track Table."); int instCnt = 0; HashMap<String, Object> nameToVals = new HashMap<String, Object>(); System.out.println(fillSQL); String prevId = null; ResultSet rs = stmt.executeQuery(fillSQL); ResultSetMetaData rsmd = rs.getMetaData(); HashMap<String, Integer> nameToIndex = new HashMap<String, Integer>(); for (int c = 1; c <= rsmd.getColumnCount(); c++) { nameToIndex.put(rsmd.getColumnName(c), c); System.out.println(c + " - " + rsmd.getColumnName(c)); } while (rs.next()) { String id = rs.getString(1); if (prevId == null) prevId = id; if (!prevId.equals(id)) { for (int i = 1; i < secInx; i++) { //System.out.println("Put: "+dbFieldNames.get(i-1)+" "+dbFieldTypes.get(i-1));//+" = "+rs.getObject(i)); if (dbFieldTypes.get(i - 1) == java.sql.Types.TIMESTAMP) { try { String ts = rs.getString(i); if (StringUtils.isNotEmpty(ts) && ts.equals("0000-00-00 00:00:00")) { //nameToVals.put(dbFieldNames.get(i-1), null); continue; } } catch (Exception ex) { nameToVals.put(dbFieldNames.get(i - 1), null);//"2000-01-01 00:00:00"); continue; } } nameToVals.put(dbFieldNames.get(i - 1), rs.getObject(i)); } for (int i = 0; i < dbFieldNames.size(); i++) { int fInx = i + 1; String name = inxToName.get(i); Object value = nameToVals.get(name); pStmt.setObject(fInx, null); int typ = dbFieldTypes.get(i); if (value != null) { switch (typ) { case java.sql.Types.INTEGER: if (value instanceof Integer) { pStmt.setInt(fInx, (Integer) value); } break; case java.sql.Types.VARCHAR: if (value instanceof String) { pStmt.setString(fInx, (String) value); } break; case java.sql.Types.TIMESTAMP: { if (value instanceof Timestamp) { pStmt.setTimestamp(fInx, (Timestamp) value); } break; } } } else { pStmt.setObject(fInx, null); } } pStmt.executeUpdate(); prevId = id; nameToVals.clear(); } String name = rs.getString(secInx); name = StringUtils.replace(name, "(", "_"); name = StringUtils.replace(name, ")", "_"); Integer intVal = (Integer) rs.getObject(secInx + 1); String strVal = (String) rs.getObject(secInx + 2); nameToVals.put(name, strVal != null ? strVal : intVal); } String alterSQL = "ALTER TABLE " + newTblName + " ADD Lookup VARCHAR(64) AFTER IP"; BasicSQLUtils.update(connection, alterSQL); alterSQL = "ALTER TABLE " + newTblName + " ADD Country VARCHAR(64) AFTER Lookup"; BasicSQLUtils.update(connection, alterSQL); alterSQL = "ALTER TABLE " + newTblName + " ADD City VARCHAR(64) AFTER Country"; BasicSQLUtils.update(connection, alterSQL); return instCnt; }