List of usage examples for java.sql ResultSet getBoolean
boolean getBoolean(String columnLabel) throws SQLException;
ResultSet object as a boolean in the Java programming language. From source file:fll.db.Queries.java
/** * Get the value of NoShow for the given team number, tournament and run * number/*from w w w.jav a 2s .c o m*/ * * @return true if the score is a No Show, false if it's not a bye or the * score does not exist * @throws SQLException on a database error */ public static boolean isNoShow(final Connection connection, final int tournament, final int teamNumber, final int runNumber) throws SQLException, IllegalArgumentException { PreparedStatement prep = null; ResultSet rs = null; try { prep = getScoreStatsPrep(connection); prep.setInt(1, tournament); prep.setInt(2, teamNumber); prep.setInt(3, runNumber); rs = prep.executeQuery(); if (rs.next()) { return rs.getBoolean("NoShow"); } else { return false; } } finally { SQLFunctions.close(rs); SQLFunctions.close(prep); } }
From source file:fll.db.Queries.java
/** * Returns true if the score has been verified, i.e. double-checked. *//*from w w w . j av a 2 s . c o m*/ public static boolean isVerified(final Connection connection, final int tournament, final int teamNumber, final int runNumber) throws SQLException { PreparedStatement prep = null; ResultSet rs = null; try { prep = getScoreStatsPrep(connection); prep.setInt(1, tournament); prep.setInt(2, teamNumber); prep.setInt(3, runNumber); rs = prep.executeQuery(); if (rs.next()) { return rs.getBoolean("Verified"); } else { return false; } } finally { SQLFunctions.close(rs); SQLFunctions.close(prep); } }
From source file:com.flexive.ejb.beans.AccountEngineBean.java
/** * {@inheritDoc}/*from w ww . j a v a 2s .c o m*/ */ @Override @TransactionAttribute(TransactionAttributeType.REQUIRED) public List<ACLAssignment> loadAccountAssignments(long accountId) throws FxApplicationException { Connection con = null; PreparedStatement stmt = null; String curSql; UserTicket ticket = getRequestTicket(); // Security checks if (!ticket.isGlobalSupervisor() && (!(accountId == ticket.getUserId() || accountId == Account.USER_GUEST))) { try { Account usr = load(accountId); if (ticket.isMandatorSupervisor() && ticket.getMandatorId() == usr.getMandatorId()) { // MandatorSupervisor may access all users within his domain } else { FxNoAccessException nae = new FxNoAccessException( "You may not access the ACLAssignment of user [" + accountId + "]"); if (LOG.isInfoEnabled()) LOG.info(nae); throw nae; } } catch (FxNotFoundException exc) { return new ArrayList<ACLAssignment>(0); } } try { // Obtain a database connection con = Database.getDbConnection(); // Fetch assignments // 1 2 3 4 5 6 7 curSql = "SELECT DISTINCT ass.USERGROUP,ass.ACL,ass.PREAD,ass.PEDIT,ass.PREMOVE,ass.PEXPORT,ass.PREL," + // 8 "ass.PCREATE, " + // 9 "(SELECT acl.CAT_TYPE FROM " + TBL_ACLS + " acl WHERE acl.ID=ass.ACL)" + // 10 11 12 13 ",ass.CREATED_BY,ass.CREATED_AT,ass.MODIFIED_BY,ass.MODIFIED_AT " + "FROM " + TBL_ACLS_ASSIGNMENT + " ass " + "WHERE ass.USERGROUP IN (SELECT grp.USERGROUP FROM " + TBL_ASSIGN_GROUPS + " grp WHERE grp.ACCOUNT=?)" + " OR ass.USERGROUP=" + UserGroup.GROUP_OWNER; stmt = con.prepareStatement(curSql); stmt.setLong(1, accountId); ResultSet rs = stmt.executeQuery(); // Read the data List<ACLAssignment> result = new ArrayList<ACLAssignment>(50); while (rs != null && rs.next()) { long groupId = rs.getLong(1); result.add(new ACLAssignment(rs.getLong(2), groupId, rs.getBoolean(3), rs.getBoolean(4), rs.getBoolean(7), rs.getBoolean(5), rs.getBoolean(6), rs.getBoolean(8), ACLCategory.getById(rs.getByte(9)), LifeCycleInfoImpl.load(rs, 10, 11, 12, 13))); } return result; } catch (SQLException exc) { FxLoadException dbe = new FxLoadException( "Failed to load the ACL assignments for user [" + accountId + "]: " + exc.getMessage(), exc); LOG.error(dbe); throw dbe; } finally { Database.closeObjects(ACLEngineBean.class, con, stmt); } }
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 .ja va2 s .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:mysql5.MySQL5PlayerDAO.java
@Override public PlayerCommonData loadPlayerCommonData(final int playerObjId) { PlayerCommonData cached = playerCommonData.get(playerObjId); if (cached != null) { log.debug("[DAO: MySQL5PlayerDAO] PlayerCommonData for id: " + playerObjId + " obtained from cache"); return cached; }//from w w w. j av a2 s .c o m final PlayerCommonData cd = new PlayerCommonData(playerObjId); boolean success = false; Connection con = null; try { con = DatabaseFactory.getConnection(); PreparedStatement stmt = con.prepareStatement("SELECT * FROM players WHERE id = ?"); stmt.setInt(1, playerObjId); ResultSet resultSet = stmt.executeQuery(); log.debug("[DAO: MySQL5PlayerDAO] loading from db " + playerObjId); if (resultSet.next()) { success = true; cd.setName(resultSet.getString("name")); // set player class before exp cd.setPlayerClass(PlayerClass.valueOf(resultSet.getString("player_class"))); cd.setExp(resultSet.getLong("exp")); cd.setRecoverableExp(resultSet.getLong("recoverexp")); cd.setRace(Race.valueOf(resultSet.getString("race"))); cd.setGender(Gender.valueOf(resultSet.getString("gender"))); cd.setLastOnline(resultSet.getTimestamp("last_online")); cd.setNote(resultSet.getString("note")); cd.setQuestExpands(resultSet.getInt("quest_expands")); cd.setNpcExpands(resultSet.getInt("npc_expands")); cd.setAdvancedStigmaSlotSize(resultSet.getInt("advanced_stigma_slot_size")); cd.setTitleId(resultSet.getInt("title_id")); cd.setBonusTitleId(resultSet.getInt("bonus_title_id")); cd.setWarehouseSize(resultSet.getInt("warehouse_size")); cd.setOnline(resultSet.getBoolean("online")); cd.setMailboxLetters(resultSet.getInt("mailbox_letters")); cd.setDp(resultSet.getInt("dp")); cd.setDeathCount(resultSet.getInt("soul_sickness")); cd.setCurrentReposteEnergy(resultSet.getLong("reposte_energy")); cd.setCurrentEventExp(resultSet.getLong("event_exp")); cd.setBattleGroundPoints(resultSet.getInt("bg_points")); float x = resultSet.getFloat("x"); float y = resultSet.getFloat("y"); float z = resultSet.getFloat("z"); byte heading = resultSet.getByte("heading"); int worldId = resultSet.getInt("world_id"); PlayerInitialData playerInitialData = DataManager.PLAYER_INITIAL_DATA; boolean checkThis = World.getInstance().getWorldMap(worldId).isInstanceType(); // this helps to pretend an player loading error // if you have a better idea do it :) if (checkThis) { mr = null; } else { mr = World.getInstance().getWorldMap(worldId).getMainWorldMapInstance().getRegion(x, y, z); } if (mr == null && playerInitialData != null) { // unstuck unlucky characters :) LocationData ld = playerInitialData.getSpawnLocation(cd.getRace()); x = ld.getX(); y = ld.getY(); z = ld.getZ(); heading = ld.getHeading(); worldId = ld.getMapId(); } WorldPosition position = World.getInstance().createPosition(worldId, x, y, z, heading, 0); cd.setPosition(position); cd.setWorldOwnerId(resultSet.getInt("world_owner")); cd.setMentorFlagTime(resultSet.getInt("mentor_flag_time")); cd.setInitialGameStats(resultSet.getInt("initial_gamestats")); cd.setLastTransferTime(resultSet.getLong("last_transfer_time")); cd.setFatigue(resultSet.getInt("fatigue")); cd.setFatigueRecover(resultSet.getInt("fatigueRecover")); cd.setFatigueReset(resultSet.getInt("fatigueReset")); cd.setPassportStamps(resultSet.getInt("stamps")); cd.setPassportReward(resultSet.getInt("rewarded_pass")); cd.setLastStamp(resultSet.getTimestamp("last_stamp")); cd.setJoinRequestLegionId(resultSet.getInt("joinRequestLegionId")); cd.setJoinRequestState(LegionJoinRequestState.valueOf(resultSet.getString("joinRequestState"))); } else { log.info("Missing PlayerCommonData from db " + playerObjId); } resultSet.close(); stmt.close(); } catch (Exception e) { log.error("Could not restore PlayerCommonData data for player: " + playerObjId + " from DB: " + e.getMessage(), e); } finally { DatabaseFactory.close(con); } if (success) { if (CacheConfig.CACHE_COMMONDATA) { playerCommonData.put(playerObjId, cd); playerCommonDataByName.put(cd.getName().toLowerCase(), cd); } return cd; } return null; }
From source file:net.sourceforge.msscodefactory.cfasterisk.v2_4.CFAsteriskPgSql.CFAsteriskPgSqlAuditActionTable.java
public void deleteAuditAction(CFSecurityAuthorization Authorization, CFSecurityAuditActionBuff Buff) { final String S_ProcName = "deleteAuditAction"; ResultSet resultSet = null; try {/*from ww w .java 2 s . co m*/ Connection cnx = schema.getCnx(); short AuditActionId = Buff.getRequiredAuditActionId(); String sql = "SELECT " + schema.getLowerDbSchemaName() + ".sp_delete_auditaction( ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + " ) as DeletedFlag"; if (stmtDeleteByPKey == null) { stmtDeleteByPKey = cnx.prepareStatement(sql); } int argIdx = 1; stmtDeleteByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtDeleteByPKey.setString(argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString()); stmtDeleteByPKey.setString(argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString()); stmtDeleteByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtDeleteByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); stmtDeleteByPKey.setShort(argIdx++, AuditActionId); stmtDeleteByPKey.setInt(argIdx++, Buff.getRequiredRevision()); ; resultSet = stmtDeleteByPKey.executeQuery(); if (resultSet.next()) { boolean deleteFlag = resultSet.getBoolean(1); if (resultSet.next()) { throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "Did not expect multi-record response"); } } else { throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "Expected 1 record result set to be returned by delete, not 0 rows"); } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } resultSet = null; } } }
From source file:net.sourceforge.msscodefactory.cfasterisk.v2_4.CFAsteriskPgSql.CFAsteriskPgSqlISOCountryTable.java
public void deleteISOCountryByIdIdx(CFSecurityAuthorization Authorization, short argId) { final String S_ProcName = "deleteISOCountryByIdIdx"; ResultSet resultSet = null; try {/*from ww w . ja va2s . c o m*/ Connection cnx = schema.getCnx(); String sql = "SELECT " + schema.getLowerDbSchemaName() + ".sp_delete_iso_cntry_by_ididx( ?, ?, ?, ?, ?" + ", " + "?" + " ) as DeletedFlag"; if (stmtDeleteByIdIdx == null) { stmtDeleteByIdIdx = cnx.prepareStatement(sql); } int argIdx = 1; stmtDeleteByIdIdx.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtDeleteByIdIdx.setString(argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString()); stmtDeleteByIdIdx.setString(argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString()); stmtDeleteByIdIdx.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtDeleteByIdIdx.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); stmtDeleteByIdIdx.setShort(argIdx++, argId); resultSet = stmtDeleteByIdIdx.executeQuery(); if (resultSet.next()) { boolean deleteFlag = resultSet.getBoolean(1); if (resultSet.next()) { throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "Did not expect multi-record response"); } } else { throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "Expected 1 record result set to be returned by delete, not 0 rows"); } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } resultSet = null; } } }
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 ww.j a v 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 " (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:com.sfs.whichdoctor.dao.MembershipDAOImpl.java
/** * Load membership.//from w ww.j a v a 2 s. c o m * * @param rs the rs * * @return the membership bean * * @throws SQLException the SQL exception */ private MembershipBean loadMembership(final ResultSet rs) throws SQLException { MembershipBean membership = this.getInstance(rs.getString("MembershipClass"), rs.getString("MembershipType")); membership.setId(rs.getInt("Id")); membership.setGUID(rs.getInt("GUID")); membership.setReferenceGUID(rs.getInt("ReferenceGUID")); membership.setMemo(rs.getString("Memo")); try { membership.setJoinedDate(rs.getDate("JoinedDate")); } catch (SQLException sqe) { dataLogger.debug("Error reading JoinedDate: " + sqe.getMessage()); } try { membership.setLeftDate(rs.getDate("LeftDate")); } catch (SQLException sqe) { dataLogger.debug("Error reading LeftDate: " + sqe.getMessage()); } membership.setPrimary(rs.getBoolean("PrimaryMembership")); for (int i = 1; i <= MAX_INTEGERS; i++) { String field = "intField" + i; membership.setIntField(i, rs.getInt(field)); } for (int i = 1; i <= MAX_OBJECTTYPES; i++) { String field = "objecttypeField" + i; if (rs.getInt(field) > 0) { // Value exists, load objecttype bean from supplied dataset ObjectTypeBean objectType = loadObjectType(String.valueOf(i), rs); membership.setObjectTypeField(i, objectType); } } for (int i = 1; i <= MAX_CHARS; i++) { String field = "charField" + i; membership.setCharField(i, rs.getString(field)); } for (int i = 1; i <= MAX_DATES; i++) { String field = "dateField" + i; try { membership.setDateField(i, rs.getDate(field)); } catch (SQLException sqe) { dataLogger.debug("Error reading dateField (" + i + "): " + sqe.getMessage()); } } membership.setActive(rs.getBoolean("Active")); try { membership.setCreatedDate(rs.getTimestamp("CreatedDate")); } catch (SQLException sqe) { dataLogger.debug("Error reading CreatedDate: " + sqe.getMessage()); } membership.setCreatedBy(rs.getString("CreatedBy")); try { membership.setModifiedDate(rs.getTimestamp("ModifiedDate")); } catch (SQLException sqe) { dataLogger.debug("Error reading ModifiedDate: " + sqe.getMessage()); } membership.setModifiedBy(rs.getString("ModifiedBy")); try { membership.setExportedDate(rs.getTimestamp("ExportedDate")); } catch (SQLException sqe) { dataLogger.debug("Error reading ExportedDate: " + sqe.getMessage()); } membership.setExportedBy(rs.getString("ExportedBy")); return membership; }
From source file:fll.web.api.SubjectiveScoresServlet.java
@SuppressFBWarnings(value = {
"SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING" }, justification = "columns and category are dynamic")
@Override/*from w w w .j a va 2 s. co m*/
protected final void doGet(final HttpServletRequest request, final HttpServletResponse response)
throws IOException, ServletException {
final ServletContext application = getServletContext();
final DataSource datasource = ApplicationAttributes.getDataSource(application);
Connection connection = null;
PreparedStatement prep = null;
ResultSet rs = null;
try {
connection = datasource.getConnection();
final int currentTournament = Queries.getCurrentTournament(connection);
// category->judge->teamNumber->score
final Map<String, Map<String, Map<Integer, SubjectiveScore>>> allScores = new HashMap<String, Map<String, Map<Integer, SubjectiveScore>>>();
final ChallengeDescription challengeDescription = ApplicationAttributes
.getChallengeDescription(application);
for (final ScoreCategory sc : challengeDescription.getSubjectiveCategories()) {
// judge->teamNumber->score
final Map<String, Map<Integer, SubjectiveScore>> categoryScores = new HashMap<String, Map<Integer, SubjectiveScore>>();
prep = connection.prepareStatement("SELECT * FROM " + sc.getName() + " WHERE Tournament = ?");
prep.setInt(1, currentTournament);
rs = prep.executeQuery();
while (rs.next()) {
final SubjectiveScore score = new SubjectiveScore();
score.setScoreOnServer(true);
final String judge = rs.getString("Judge");
final Map<Integer, SubjectiveScore> judgeScores;
if (categoryScores.containsKey(judge)) {
judgeScores = categoryScores.get(judge);
} else {
judgeScores = new HashMap<Integer, SubjectiveScore>();
categoryScores.put(judge, judgeScores);
}
score.setTeamNumber(rs.getInt("TeamNumber"));
score.setJudge(judge);
score.setNoShow(rs.getBoolean("NoShow"));
score.setNote(rs.getString("note"));
final Map<String, Double> standardSubScores = new HashMap<String, Double>();
final Map<String, String> enumSubScores = new HashMap<String, String>();
for (final AbstractGoal goal : sc.getGoals()) {
if (goal.isEnumerated()) {
final String value = rs.getString(goal.getName());
enumSubScores.put(goal.getName(), value);
} else {
final double value = rs.getDouble(goal.getName());
standardSubScores.put(goal.getName(), value);
}
}
score.setStandardSubScores(standardSubScores);
score.setEnumSubScores(enumSubScores);
judgeScores.put(score.getTeamNumber(), score);
}
allScores.put(sc.getName(), categoryScores);
SQLFunctions.close(rs);
rs = null;
SQLFunctions.close(prep);
prep = null;
}
final ObjectMapper jsonMapper = new ObjectMapper();
response.reset();
response.setContentType("application/json");
final PrintWriter writer = response.getWriter();
jsonMapper.writeValue(writer, allScores);
} catch (final SQLException e) {
throw new RuntimeException(e);
} finally {
SQLFunctions.close(rs);
SQLFunctions.close(prep);
SQLFunctions.close(connection);
}
}