Example usage for java.sql PreparedStatement setObject

List of usage examples for java.sql PreparedStatement setObject

Introduction

In this page you can find the example usage for java.sql PreparedStatement setObject.

Prototype

default void setObject(int parameterIndex, Object x, SQLType targetSqlType) throws SQLException 

Source Link

Document

Sets the value of the designated parameter with the given object.

Usage

From source file:org.cloudgraph.rdb.service.JDBCSupport.java

protected void executeInsert(PlasmaType type, StringBuilder sql, Map<String, PropertyPair> values,
        Connection con) {//from w w w .ja  v a 2s.  com
    PreparedStatement statement = null;
    List<InputStream> streams = null;
    try {

        if (log.isDebugEnabled()) {
            log.debug("execute: " + sql.toString());
            StringBuilder paramBuf = createParamDebug(values);
            log.debug("params: " + paramBuf.toString());
        }

        statement = con.prepareStatement(sql.toString());

        for (PropertyPair pair : values.values()) {
            PlasmaProperty valueProp = pair.getProp();
            if (pair.getValueProp() != null)
                valueProp = pair.getValueProp();
            int jdbcType = converter.toJDBCDataType(valueProp, pair.getValue());
            Object jdbcValue = converter.toJDBCDataValue(valueProp, pair.getValue());
            if (jdbcType != Types.BLOB && jdbcType != Types.VARBINARY) {
                statement.setObject(pair.getColumn(), jdbcValue, jdbcType);
            } else {
                byte[] bytes = (byte[]) jdbcValue;
                long len = bytes.length;
                ByteArrayInputStream is = new ByteArrayInputStream(bytes);
                statement.setBinaryStream(pair.getColumn(), is, len);
                if (streams == null)
                    streams = new ArrayList<InputStream>();
                streams.add(is);
            }
        }

        statement.execute();
    } catch (Throwable t) {
        throw new DataAccessException(t);
    } finally {
        try {
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
        if (streams != null)
            try {
                for (InputStream stream : streams)
                    stream.close();
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }

    }
}

From source file:org.cloudgraph.rdb.service.JDBCSupport.java

protected void execute(PlasmaType type, StringBuilder sql, Map<String, PropertyPair> values, Connection con) {
    PreparedStatement statement = null;
    List<InputStream> streams = null;
    try {/*  www. j a  v  a 2  s .c o m*/
        if (log.isDebugEnabled()) {
            log.debug("execute: " + sql.toString());
            StringBuilder paramBuf = createParamDebug(values);
            log.debug("params: " + paramBuf.toString());
        }
        statement = con.prepareStatement(sql.toString());
        for (PropertyPair pair : values.values()) {
            PlasmaProperty valueProp = pair.getProp();
            if (pair.getValueProp() != null)
                valueProp = pair.getValueProp();

            int jdbcType = converter.toJDBCDataType(valueProp, pair.getValue());
            Object jdbcValue = converter.toJDBCDataValue(valueProp, pair.getValue());
            if (jdbcType != Types.BLOB && jdbcType != Types.VARBINARY) {
                statement.setObject(pair.getColumn(), jdbcValue, jdbcType);
            } else {
                byte[] bytes = (byte[]) jdbcValue;
                long len = bytes.length;
                ByteArrayInputStream is = new ByteArrayInputStream(bytes);
                statement.setBinaryStream(pair.getColumn(), is, len);
                if (streams == null)
                    streams = new ArrayList<InputStream>();
                streams.add(is);
            }

            if (pair.getOldValue() != null) {
                Object jdbcOldValue = converter.toJDBCDataValue(valueProp, pair.getOldValue());
                if (jdbcType != Types.BLOB && jdbcType != Types.VARBINARY) {
                    statement.setObject(pair.getOldValueColumn(), jdbcOldValue, jdbcType);
                } else {
                    byte[] bytes = (byte[]) jdbcOldValue;
                    long len = bytes.length;
                    ByteArrayInputStream is = new ByteArrayInputStream(bytes);
                    statement.setBinaryStream(pair.getOldValueColumn(), is, len);
                    if (streams == null)
                        streams = new ArrayList<InputStream>();
                    streams.add(is);
                }
            }
        }
        statement.executeUpdate();
    } catch (Throwable t) {
        throw new DataAccessException(t);
    } finally {
        try {
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
        if (streams != null)
            try {
                for (InputStream stream : streams)
                    stream.close();
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }

    }
}

From source file:org.apache.jmeter.protocol.jdbc.AbstractJDBCwoTimeOutTestElement.java

private int[] setArguments(final PreparedStatement pstmt) throws SQLException, IOException {
    if (getQueryArguments().trim().length() == 0) {
        return new int[] {};
    }//w w  w  .j a  v a  2 s  . c o  m
    final String[] arguments = CSVSaveService.csvSplitString(getQueryArguments(), COMMA_CHAR);
    final String[] argumentsTypes = getQueryArgumentsTypes().split(COMMA);
    if (arguments.length != argumentsTypes.length) {
        throw new SQLException("number of arguments (" + arguments.length + ") and number of types ("
                + argumentsTypes.length + ") are not equal");
    }
    final int[] outputs = new int[arguments.length];
    for (int i = 0; i < arguments.length; i++) {
        final String argument = arguments[i];
        String argumentType = argumentsTypes[i];
        final String[] arg = argumentType.split(" ");
        String inputOutput = "";
        if (arg.length > 1) {
            argumentType = arg[1];
            inputOutput = arg[0];
        }
        final int targetSqlType = getJdbcType(argumentType);
        try {
            if (!OUT.equalsIgnoreCase(inputOutput)) {
                if (argument.equals(NULL_MARKER)) {
                    pstmt.setNull(i + 1, targetSqlType);
                } else {
                    pstmt.setObject(i + 1, argument, targetSqlType);
                }
            }
            if (OUT.equalsIgnoreCase(inputOutput) || INOUT.equalsIgnoreCase(inputOutput)) {
                final CallableStatement cs = (CallableStatement) pstmt;
                cs.registerOutParameter(i + 1, targetSqlType);
                outputs[i] = targetSqlType;
            } else {
                outputs[i] = java.sql.Types.NULL; // can't have an output parameter type null
            }
        } catch (final NullPointerException e) { // thrown by Derby JDBC (at least) if there are no "?" markers in statement
            throw new SQLException("Could not set argument no: " + (i + 1) + " - missing parameter marker?");
        }
    }
    return outputs;
}

From source file:org.apache.jmeter.protocol.jdbc.sampler.JDBCSampler.java

private int[] setArguments(PreparedStatement pstmt) throws SQLException, IOException {
    if (getQueryArguments().trim().length() == 0) {
        return new int[] {};
    }/*from   www  .  j  a  va  2 s .c  o  m*/
    String[] arguments = CSVSaveService.csvSplitString(getQueryArguments(), COMMA_CHAR);
    String[] argumentsTypes = getQueryArgumentsTypes().split(COMMA);
    if (arguments.length != argumentsTypes.length) {
        throw new SQLException("number of arguments (" + arguments.length + ") and number of types ("
                + argumentsTypes.length + ") are not equal");
    }
    int[] outputs = new int[arguments.length];
    for (int i = 0; i < arguments.length; i++) {
        String argument = arguments[i];
        String argumentType = argumentsTypes[i];
        String[] arg = argumentType.split(" ");
        String inputOutput = "";
        if (arg.length > 1) {
            argumentType = arg[1];
            inputOutput = arg[0];
        }
        int targetSqlType = getJdbcType(argumentType);
        try {
            if (!OUT.equalsIgnoreCase(inputOutput)) {
                if (argument.equals(NULL_MARKER)) {
                    pstmt.setNull(i + 1, targetSqlType);
                } else {
                    pstmt.setObject(i + 1, argument, targetSqlType);
                }
            }
            if (OUT.equalsIgnoreCase(inputOutput) || INOUT.equalsIgnoreCase(inputOutput)) {
                CallableStatement cs = (CallableStatement) pstmt;
                cs.registerOutParameter(i + 1, targetSqlType);
                outputs[i] = targetSqlType;
            } else {
                outputs[i] = java.sql.Types.NULL; // can't have an output parameter type null
            }
        } catch (NullPointerException e) { // thrown by Derby JDBC (at least) if there are no "?" markers in statement
            throw new SQLException("Could not set argument no: " + (i + 1) + " - missing parameter marker?");
        }
    }
    return outputs;
}

From source file:org.cloudgraph.rdb.service.JDBCSupport.java

protected List<PropertyPair> executeInsertWithGeneratedKeys(PlasmaType type, StringBuilder sql,
        Map<String, PropertyPair> values, Connection con) {
    List<PropertyPair> resultKeys = new ArrayList<PropertyPair>();
    PreparedStatement statement = null;
    List<InputStream> streams = null;
    ResultSet generatedKeys = null;
    try {//w  w w  .ja v a 2s . c om

        if (log.isDebugEnabled()) {
            log.debug("execute: " + sql.toString());
            StringBuilder paramBuf = createParamDebug(values);
            log.debug("params: " + paramBuf.toString());
        }

        statement = con.prepareStatement(sql.toString(), PreparedStatement.RETURN_GENERATED_KEYS);

        for (PropertyPair pair : values.values()) {
            PlasmaProperty valueProp = pair.getProp();
            if (pair.getValueProp() != null)
                valueProp = pair.getValueProp();
            int jdbcType = converter.toJDBCDataType(valueProp, pair.getValue());
            Object jdbcValue = converter.toJDBCDataValue(valueProp, pair.getValue());
            if (jdbcType != Types.BLOB && jdbcType != Types.VARBINARY) {
                statement.setObject(pair.getColumn(), jdbcValue, jdbcType);
            } else {
                byte[] bytes = (byte[]) jdbcValue;
                long len = bytes.length;
                ByteArrayInputStream is = new ByteArrayInputStream(bytes);
                statement.setBinaryStream(pair.getColumn(), is, len);
                if (streams == null)
                    streams = new ArrayList<InputStream>();
                streams.add(is);
            }
        }

        statement.execute();
        generatedKeys = statement.getGeneratedKeys();
        ResultSetMetaData rsMeta = generatedKeys.getMetaData();
        int numcols = rsMeta.getColumnCount();
        if (log.isDebugEnabled())
            log.debug("returned " + numcols + " keys");

        if (generatedKeys.next()) {
            // FIXME; without metadata describing which properties
            // are actually a sequence, there is guess work
            // involved in matching the values returned
            // automatically from PreparedStatment as they
            // are anonymous in terms of the column names
            // making it impossible to match them to a metadata
            // property.
            List<Property> pkPropList = type.findProperties(KeyType.primary);
            if (pkPropList == null || pkPropList.size() == 0)
                throw new DataAccessException("no pri-key properties found for type '" + type.getName() + "'");
            if (pkPropList.size() > 1)
                throw new DataAccessException("multiple pri-key properties found for type '" + type.getName()
                        + "' - cannot map to generated keys");
            PlasmaProperty prop = (PlasmaProperty) pkPropList.get(0);
            // FIXME: need to find properties per column by physical name
            // alias
            // in case where multiple generated pri-keys
            for (int i = 1; i <= numcols; i++) {
                String columnName = rsMeta.getColumnName(i);
                if (log.isDebugEnabled())
                    log.debug("returned key column '" + columnName + "'");
                int columnType = rsMeta.getColumnType(i);
                Object value = converter.fromJDBCDataType(generatedKeys, i, columnType, prop);
                PropertyPair pair = new PropertyPair((PlasmaProperty) prop, value);
                resultKeys.add(pair);
            }
        }
    } catch (Throwable t) {
        throw new DataAccessException(t);
    } finally {
        try {
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
        if (streams != null)
            try {
                for (InputStream stream : streams)
                    stream.close();
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }
    }

    return resultKeys;
}

From source file:at.alladin.rmbt.statisticServer.OpenTestResource.java

/**
 * Gets the JSON-Representation of all open-data-fields for one specific
 * open-test-uuid/*from  w  w w . j a v a  2 s .  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
            " 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:hoot.services.db.DbUtils.java

public static void batchRecordsDirectWays(final long mapId, final List<?> records,
        final RecordBatchType recordBatchType, Connection conn, int maxRecordBatchSize) throws Exception {
    PreparedStatement ps = null;
    try {/*from   w w w  . j av  a  2  s . co m*/
        String sql = null;
        long execResult = -1;
        //conn.setAutoCommit(false);
        int count = 0;

        switch (recordBatchType) {
        case INSERT:

            sql = "insert into current_ways_" + mapId
                    + " (id, changeset_id, \"timestamp\", visible, version, tags) "
                    + "values (?, ?, ?, ?, ?, ?)";

            ps = conn.prepareStatement(sql);
            for (Object o : records) {
                CurrentWays way = (CurrentWays) o;

                ps.setLong(1, way.getId());
                ps.setLong(2, way.getChangesetId());
                ps.setTimestamp(3, way.getTimestamp());
                ps.setBoolean(4, way.getVisible());
                ps.setLong(5, way.getVersion());

                Map<String, String> tags = (Map<String, String>) way.getTags();

                String hstoreStr = "";
                Iterator it = tags.entrySet().iterator();
                while (it.hasNext()) {
                    Map.Entry pairs = (Map.Entry) it.next();
                    if (hstoreStr.length() > 0) {
                        hstoreStr += ",";
                    }
                    hstoreStr += "\"" + pairs.getKey() + "\"=>\"" + pairs.getValue() + "\"";
                }
                ps.setObject(6, hstoreStr, Types.OTHER);
                ps.addBatch();

                if (maxRecordBatchSize > -1) {
                    if (++count % maxRecordBatchSize == 0) {
                        ps.executeBatch();
                        //conn.commit();
                    }
                }

            }

            break;

        case UPDATE:

            sql = "update current_ways_" + mapId
                    + " set changeset_id=?, visible=?, \"timestamp\"=?, version=?, tags=? " + "where id=?";
            ps = conn.prepareStatement(sql);
            for (Object o : records) {
                CurrentWays way = (CurrentWays) o;

                ps.setLong(1, way.getChangesetId());
                ps.setBoolean(2, way.getVisible());
                ps.setTimestamp(3, way.getTimestamp());
                ps.setLong(4, way.getVersion());

                Map<String, String> tags = (Map<String, String>) way.getTags();

                String hstoreStr = "";
                Iterator it = tags.entrySet().iterator();
                while (it.hasNext()) {
                    Map.Entry pairs = (Map.Entry) it.next();
                    if (hstoreStr.length() > 0) {
                        hstoreStr += ",";
                    }
                    hstoreStr += "\"" + pairs.getKey() + "\"=>\"" + pairs.getValue() + "\"";
                }
                ps.setObject(5, hstoreStr, Types.OTHER);

                ps.setLong(6, way.getId());

                ps.addBatch();

                if (maxRecordBatchSize > -1) {
                    if (++count % maxRecordBatchSize == 0) {
                        ps.executeBatch();
                        //conn.commit();
                    }
                }
            }

            break;

        case DELETE:

            sql = "delete from current_ways_" + mapId + " where id=?";
            ps = conn.prepareStatement(sql);
            for (Object o : records) {
                CurrentWays way = (CurrentWays) o;

                ps.setLong(1, way.getId());

                ps.addBatch();

                if (maxRecordBatchSize > -1) {
                    if (++count % maxRecordBatchSize == 0) {
                        ps.executeBatch();
                        //conn.commit();
                    }
                }

            }

            break;

        default:
            throw new Exception("");
        }

        ps.executeBatch();
        //conn.commit();
    } catch (Exception e) {
        conn.rollback();
        String msg = "Error executing batch query.";
        msg += "  " + e.getMessage();
        msg += " Cause:" + e.getCause().toString();
        throw new Exception(msg);
    } finally {
        if (ps != null) {
            ps.close();
        }
        //conn.setAutoCommit(true);
    }
}

From source file:hoot.services.db.DbUtils.java

public static void batchRecordsDirectRelations(final long mapId, final List<?> records,
        final RecordBatchType recordBatchType, Connection conn, int maxRecordBatchSize) throws Exception {
    PreparedStatement ps = null;
    try {//  w w  w .j a v  a 2s . c  o  m
        String sql = null;
        long execResult = -1;
        //conn.setAutoCommit(false);
        int count = 0;

        switch (recordBatchType) {
        case INSERT:

            sql = "insert into current_relations_" + mapId
                    + " (id, changeset_id, \"timestamp\", visible, version, tags) "
                    + "values (?, ?, ?, ?, ?, ?)";

            ps = conn.prepareStatement(sql);
            for (Object o : records) {
                CurrentRelations rel = (CurrentRelations) o;

                ps.setLong(1, rel.getId());
                ps.setLong(2, rel.getChangesetId());
                ps.setTimestamp(3, rel.getTimestamp());
                ps.setBoolean(4, rel.getVisible());
                ps.setLong(5, rel.getVersion());

                Map<String, String> tags = (Map<String, String>) rel.getTags();

                String hstoreStr = "";
                Iterator it = tags.entrySet().iterator();
                while (it.hasNext()) {
                    Map.Entry pairs = (Map.Entry) it.next();
                    if (hstoreStr.length() > 0) {
                        hstoreStr += ",";
                    }
                    hstoreStr += "\"" + pairs.getKey() + "\"=>\"" + pairs.getValue() + "\"";
                }
                ps.setObject(6, hstoreStr, Types.OTHER);
                ps.addBatch();

                if (maxRecordBatchSize > -1) {
                    if (++count % maxRecordBatchSize == 0) {
                        ps.executeBatch();
                        //conn.commit();
                    }
                }

            }

            break;

        case UPDATE:

            sql = "update current_relations_" + mapId
                    + " set changeset_id=?, visible=?, \"timestamp\"=?, version=?, tags=? " + "where id=?";
            ps = conn.prepareStatement(sql);
            for (Object o : records) {
                CurrentRelations rel = (CurrentRelations) o;

                ps.setLong(1, rel.getChangesetId());
                ps.setBoolean(2, rel.getVisible());
                ps.setTimestamp(3, rel.getTimestamp());
                ps.setLong(4, rel.getVersion());

                Map<String, String> tags = (Map<String, String>) rel.getTags();

                String hstoreStr = "";
                Iterator it = tags.entrySet().iterator();
                while (it.hasNext()) {
                    Map.Entry pairs = (Map.Entry) it.next();
                    if (hstoreStr.length() > 0) {
                        hstoreStr += ",";
                    }
                    hstoreStr += "\"" + pairs.getKey() + "\"=>\"" + pairs.getValue() + "\"";
                }
                ps.setObject(5, hstoreStr, Types.OTHER);

                ps.setLong(6, rel.getId());

                ps.addBatch();

                if (maxRecordBatchSize > -1) {
                    if (++count % maxRecordBatchSize == 0) {
                        ps.executeBatch();
                        //conn.commit();
                    }
                }
            }

            break;

        case DELETE:

            sql = "delete from current_relations_" + mapId + " where id=?";
            ps = conn.prepareStatement(sql);
            for (Object o : records) {
                CurrentRelations rel = (CurrentRelations) o;

                ps.setLong(1, rel.getId());

                ps.addBatch();

                if (maxRecordBatchSize > -1) {
                    if (++count % maxRecordBatchSize == 0) {
                        ps.executeBatch();
                        //conn.commit();
                    }
                }

            }

            break;

        default:
            throw new Exception("");
        }

        ps.executeBatch();
        //conn.commit();
    } catch (Exception e) {
        conn.rollback();
        String msg = "Error executing batch query.";
        msg += "  " + e.getMessage();
        msg += " Cause:" + e.getCause().toString();
        throw new Exception(msg);
    } finally {
        if (ps != null) {
            ps.close();
        }
        //conn.setAutoCommit(true);
    }
}

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 .  j a  va2s  . 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
            " (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//www  . ja v  a2s . com
 * @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;
}