List of usage examples for java.sql PreparedStatement getResultSet
ResultSet getResultSet() throws SQLException;
ResultSet object. From source file:csiro.pidsvc.mappingstore.Manager.java
protected String exportMappingsImpl(Object mappingIdentifier, String scope, boolean fullBackup, String source, boolean preserveDatesForDeprecatedMappings, boolean includeConditionSets, boolean includeLookupMaps) throws SQLException { PreparedStatement pst = null; ResultSet rs = null;/*from www . j a va 2s .c o m*/ String ret = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<backup type=\"" + (fullBackup ? "full" : "partial") + "\" scope=\"" + scope + "\" xmlns=\"urn:csiro:xmlns:pidsvc:backup:1.0\">"; int defaultActionId; Timestamp timeStamp; String buf, path; try { if (mappingIdentifier instanceof Integer) { if ((Integer) mappingIdentifier == 0) { // Catch all mapping. pst = _connection.prepareStatement( "SELECT * FROM " + source + " WHERE mapping_path IS NULL ORDER BY mapping_id"); } else { pst = _connection.prepareStatement( "SELECT * FROM " + source + " WHERE mapping_id = ? ORDER BY mapping_id"); pst.setInt(1, (Integer) mappingIdentifier); } } else { // Export mapping by path or all mappings. pst = _connection.prepareStatement("SELECT * FROM " + source + (mappingIdentifier == null ? "" : " WHERE mapping_path = ?") + " ORDER BY mapping_id"); if (mappingIdentifier != null) pst.setString(1, (String) mappingIdentifier); } if (pst.execute()) { for (rs = pst.getResultSet(); rs.next();) { path = rs.getString( mappingIdentifier instanceof Integer || !fullBackup ? "original_path" : "mapping_path"); ret += "<mapping"; // Time stamps are only applicable for full backups and deprecated records. if (fullBackup || !fullBackup && preserveDatesForDeprecatedMappings && rs.getTimestamp("date_end") != null) { timeStamp = rs.getTimestamp("date_start"); if (timeStamp != null) ret += " date_start=\"" + timeStamp.toString().replace(" ", "T") + "Z\""; timeStamp = rs.getTimestamp("date_end"); if (timeStamp != null) ret += " date_end=\"" + timeStamp.toString().replace(" ", "T") + "Z\""; } // Preserve original mapping path for full backups. if (fullBackup && path != null) ret += " original_path=\"" + rs.getString("original_path") + "\""; ret += ">"; // mapping ret += (path == null ? "<path/>" : "<path>" + StringEscapeUtils.escapeXml(path) + "</path>"); buf = rs.getString("parent"); if (buf != null) ret += "<parent>" + StringEscapeUtils.escapeXml(buf) + "</parent>"; ret += "<type>" + rs.getString("type") + "</type>"; buf = rs.getString("title"); if (buf != null) ret += "<title>" + StringEscapeUtils.escapeXml(buf) + "</title>"; buf = rs.getString("description"); if (buf != null) ret += "<description>" + StringEscapeUtils.escapeXml(buf) + "</description>"; buf = rs.getString("creator"); if (buf != null) ret += "<creator>" + StringEscapeUtils.escapeXml(buf) + "</creator>"; buf = rs.getString("commit_note"); if (buf != null) ret += "<commitNote>" + StringEscapeUtils.escapeXml(buf) + "</commitNote>"; // Default action. defaultActionId = rs.getInt("default_action_id"); if (!rs.wasNull()) { csiro.pidsvc.mappingstore.action.Descriptor action = getAction(defaultActionId); ret += "<action>"; ret += "<type>" + action.Type + "</type>"; if (action.Name != null) ret += "<name>" + StringEscapeUtils.escapeXml(action.Name) + "</name>"; if (action.Value != null) ret += "<value>" + StringEscapeUtils.escapeXml(action.Value) + "</value>"; buf = rs.getString("default_action_description"); if (buf != null) ret += "<description>" + StringEscapeUtils.escapeXml(buf) + "</description>"; ret += "</action>"; } // Conditions. ret += exportConditionsByMappingId(rs.getInt("mapping_id")); ret += "</mapping>"; } } // Condition sets. if (includeConditionSets) ret += exportConditionSetImpl(null); // Lookup maps. if (includeLookupMaps) ret += exportLookupImpl(null); } catch (Exception e) { _logger.error(e); } finally { if (rs != null) rs.close(); if (pst != null) pst.close(); } ret += "</backup>"; return ret; }
From source file:org.cloudgraph.rdb.service.GraphQuery.java
private List<List<PropertyPair>> findResults(Query query, SelectionCollector collector, PlasmaType type, Connection con) {//from ww w . j av a2 s . c om Object[] params = new Object[0]; RDBDataConverter converter = RDBDataConverter.INSTANCE; AliasMap aliasMap = new AliasMap(type); // construct a filter adding to alias map RDBFilterAssembler filterAssembler = null; Where where = query.findWhereClause(); if (where != null) { filterAssembler = new RDBFilterAssembler(where, type, aliasMap); params = filterAssembler.getParams(); } RDBOrderingAssembler orderingDeclAssembler = null; OrderBy orderby = query.findOrderByClause(); if (orderby != null) orderingDeclAssembler = new RDBOrderingAssembler(orderby, type, aliasMap); RDBGroupingAssembler groupingDeclAssembler = null; GroupBy groupby = query.findGroupByClause(); if (groupby != null) groupingDeclAssembler = new RDBGroupingAssembler(groupby, type, aliasMap); String rootAlias = aliasMap.getAlias(type); StringBuilder sqlQuery = new StringBuilder(); sqlQuery.append("SELECT DISTINCT "); // FIXME: only necessary if // FIXME: determine if any selected column(s) are LOB and don't use // DISTINCT in this case boolean hasLob = false; int i = 0; Set<Property> props = collector.getProperties(type); for (Property prop : props) { if (prop.isMany() && !prop.getType().isDataType()) continue; if (i > 0) sqlQuery.append(", "); sqlQuery.append(rootAlias); sqlQuery.append("."); sqlQuery.append(((PlasmaProperty) prop).getPhysicalName()); i++; } // construct a FROM clause from alias map sqlQuery.append(" FROM "); Iterator<PlasmaType> it = aliasMap.getTypes(); int count = 0; while (it.hasNext()) { PlasmaType aliasType = it.next(); String alias = aliasMap.getAlias(aliasType); if (count > 0) sqlQuery.append(", "); sqlQuery.append(this.statementUtil.getQualifiedPhysicalName(aliasType)); sqlQuery.append(" "); sqlQuery.append(alias); count++; } // append WHERE filter if (filterAssembler != null) { sqlQuery.append(" "); sqlQuery.append(filterAssembler.getFilter()); } if (orderingDeclAssembler != null) { sqlQuery.append(" "); sqlQuery.append(orderingDeclAssembler.getOrderingDeclaration()); } if (groupingDeclAssembler != null) { sqlQuery.append(" "); sqlQuery.append(groupingDeclAssembler.getGroupingDeclaration()); } // set the result range RDBMSVendorName vendor = PlasmaRuntime.getInstance().getRDBMSProviderVendor(DataAccessProviderName.JDBC); switch (vendor) { case ORACLE: if (query.getStartRange() != null && query.getEndRange() != null) { long offset = query.getStartRange() - 1; // inclusive if (offset < 0) offset = 0; long rowcount = query.getEndRange() - offset; StringBuilder buf = new StringBuilder(); // Pagination wrapper making sure ordering occurs before any // ROWNUM selected by using // a nested SELECT. if (offset == 0) { buf.append("SELECT * FROM ("); buf.append(sqlQuery); buf.append(") WHERE ROWNUM <= "); buf.append(rowcount); } else { // For offsets uses limiting condition on ROWNUM itself // as well as a // ROWNUM alias to enable Oracle STOPKEY processing // which helps performance. buf.append("SELECT * FROM (SELECT "); buf.append(PAGE_ALIAS); buf.append(".*, ROWNUM AS "); buf.append(ROWNUM_ALIAS); buf.append(" FROM ("); buf.append(sqlQuery); buf.append(") "); buf.append(PAGE_ALIAS); buf.append(") "); buf.append("WHERE "); buf.append(ROWNUM_ALIAS); buf.append(" >= "); buf.append(query.getStartRange()); buf.append(" AND ROWNUM <= "); buf.append(rowcount); } sqlQuery = buf; } break; case MYSQL: if (query.getStartRange() != null && query.getEndRange() != null) { long offset = query.getStartRange() - 1; // inclusive if (offset < 0) offset = 0; long rowcount = query.getEndRange() - offset; sqlQuery.append(" LIMIT "); // e.g. LIMIT offset,numrows sqlQuery.append(String.valueOf(offset)); sqlQuery.append(","); sqlQuery.append(String.valueOf(rowcount)); } break; default: } List<List<PropertyPair>> rows = new ArrayList<List<PropertyPair>>(); PreparedStatement statement = null; ResultSet rs = null; try { statement = con.prepareStatement(sqlQuery.toString(), ResultSet.TYPE_FORWARD_ONLY, /* * ResultSet * . * TYPE_SCROLL_INSENSITIVE * , */ ResultSet.CONCUR_READ_ONLY); // statement.setFetchSize(32); // log.debug("setting fetch size 32"); // set params // FIXME: params are pre-converted // to string in filter assembly int paramCount = 0; if (filterAssembler != null) { params = filterAssembler.getParams(); if (params != null) { paramCount = params.length; for (i = 0; i < params.length; i++) statement.setObject(i + 1, params[i]); } } // execute long before = System.currentTimeMillis(); statement.execute(); long after = System.currentTimeMillis(); if (log.isDebugEnabled()) { if (params == null || params.length == 0) { log.debug("executed: " + sqlQuery.toString() + " (" + String.valueOf(after - before) + ")"); } else { StringBuilder paramBuf = new StringBuilder(); paramBuf.append(" ["); for (int p = 0; p < params.length; p++) { if (p > 0) paramBuf.append(", "); paramBuf.append(String.valueOf(params[p])); } paramBuf.append("]"); log.debug("executed: " + sqlQuery.toString() + " " + paramBuf.toString() + " (" + String.valueOf(after - before) + ")"); } } // read results before = System.currentTimeMillis(); int numresults = 0; rs = statement.getResultSet(); int numcols = rs.getMetaData().getColumnCount(); ResultSetMetaData rsMeta = rs.getMetaData(); List<PropertyPair> row = null; PropertyPair pair = null; while (rs.next()) { row = new ArrayList<PropertyPair>(); rows.add(row); for (i = 1; i <= numcols; i++) { String columnName = rsMeta.getColumnLabel(i); // mysql 5.5 // returns // original // table col // name for // views if (columnName == null) columnName = rsMeta.getColumnName(i); if (ROWNUM_ALIAS.equals(columnName)) continue; int columnType = rsMeta.getColumnType(i); PlasmaProperty prop = (PlasmaProperty) type.getProperty(columnName); PlasmaProperty valueProp = prop; while (!valueProp.getType().isDataType()) { valueProp = this.statementUtil.getOppositePriKeyProperty(valueProp); } Object value = converter.fromJDBCDataType(rs, i, columnType, valueProp); if (value != null) { pair = new PropertyPair(prop, value); pair.setColumn(i); if (!valueProp.equals(prop)) pair.setValueProp(valueProp); row.add(pair); } } numresults++; } after = System.currentTimeMillis(); if (log.isDebugEnabled()) log.debug("read " + numresults + " results (" + String.valueOf(after - before) + ")"); } catch (Throwable t) { StringBuffer buf = this.generateErrorDetail(t, sqlQuery.toString(), filterAssembler); log.error(buf.toString()); throw new DataAccessException(t); } finally { try { if (rs != null) rs.close(); if (statement != null) statement.close(); } catch (SQLException e) { log.error(e.getMessage(), e); } } return rows; }
From source file:org.apache.sqoop.repository.derby.DerbyRepositoryHandler.java
/** * Execute given query on database./*from w ww. ja v a 2 s.c o m*/ * * @param query Query that should be executed */ private void runQuery(String query, Connection conn, Object... args) { PreparedStatement stmt = null; try { stmt = conn.prepareStatement(query); for (int i = 0; i < args.length; ++i) { if (args[i] instanceof String) { stmt.setString(i + 1, (String) args[i]); } else if (args[i] instanceof Long) { stmt.setLong(i + 1, (Long) args[i]); } else { stmt.setObject(i + 1, args[i]); } } if (stmt.execute()) { ResultSet rset = stmt.getResultSet(); int count = 0; while (rset.next()) { count++; } LOG.info("QUERY(" + query + ") produced unused resultset with " + count + " rows"); } else { int updateCount = stmt.getUpdateCount(); LOG.info("QUERY(" + query + ") Update count: " + updateCount); } } catch (SQLException ex) { throw new SqoopException(DerbyRepoError.DERBYREPO_0003, query, ex); } finally { closeStatements(stmt); } }
From source file:org.kawanfw.sql.servlet.sql.ServerStatementRawExecute.java
/** * Execute the passed SQL PreparedStatement as execute() sand return: <br> * - The result set as a List of Maps for SELECT statements. <br> * - The return code for other statements * //from w w w . j av a 2 s. co m * @param sqlOrder * the qsql order * @param sqlParms * the sql parameters * @param out * the output stream where to write to result set output * * * @throws SQLException */ private void executePrepStatement(OutputStream out) throws SQLException, IOException { String sqlOrder = statementHolder.getSqlOrder(); PreparedStatement preparedStatement = null; boolean usesAutoGeneratedKeys = false; if (statementHolder.getAutoGeneratedKeys() != -1) { preparedStatement = connection.prepareStatement(sqlOrder, statementHolder.getAutoGeneratedKeys()); usesAutoGeneratedKeys = true; } else if (statementHolder.getColumnIndexesAutogenerateKeys().length != 0) { preparedStatement = connection.prepareStatement(sqlOrder, statementHolder.getColumnIndexesAutogenerateKeys()); usesAutoGeneratedKeys = true; } else if (statementHolder.getColumnNamesAutogenerateKeys().length != 0) { preparedStatement = connection.prepareStatement(sqlOrder, statementHolder.getColumnNamesAutogenerateKeys()); usesAutoGeneratedKeys = true; } else { preparedStatement = connection.prepareStatement(sqlOrder); } Map<Integer, Integer> parameterTypes = null; Map<Integer, String> parameterStringValues = null; // Class to set all the statement parameters ServerPreparedStatementParameters serverPreparedStatementParameters = null; try { ServerSqlUtil.setStatementProperties(preparedStatement, statementHolder); parameterTypes = statementHolder.getParameterTypes(); parameterStringValues = statementHolder.getParameterStringValues(); if (!SqlConfiguratorCall.allowExecute(sqlConfigurator, username, connection)) { String ipAddress = request.getRemoteAddr(); SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, username, connection, ipAddress, sqlOrder, new Vector<Object>()); String message = Tag.PRODUCT_SECURITY + " [" + "{Prepared Statement not authorized for execute}" + "{sql order : " + sqlOrder + "}" + "{sql parms : " + parameterTypes + "}" + "{sql values: " + parameterStringValues + "}]"; throw new SecurityException(message); } debug("before ServerPreparedStatementParameters"); serverPreparedStatementParameters = new ServerPreparedStatementParameters(request, username, fileConfigurator, preparedStatement, statementHolder); serverPreparedStatementParameters.setParameters(); // Throws a SQL exception if the order is not authorized: debug("before new SqlSecurityChecker()"); boolean isAllowed = sqlConfigurator.allowStatementAfterAnalysis(username, connection, sqlOrder, serverPreparedStatementParameters.getParameterValues()); if (!isAllowed) { String ipAddress = request.getRemoteAddr(); SqlConfiguratorCall.runIfStatementRefused(sqlConfigurator, username, connection, ipAddress, sqlOrder, serverPreparedStatementParameters.getParameterValues()); String message = Tag.PRODUCT_SECURITY + " [" + "{Prepared Statement not authorized}" + "{sql order : " + sqlOrder + "}" + "{sql parms : " + parameterTypes + "}" + "{sql values: " + parameterStringValues + "}]"; throw new SecurityException(message); } debug("before preparedStatement.execute()"); ServerSqlUtil.setMaxRowsToReturn(preparedStatement, sqlConfigurator); boolean isResultSet = preparedStatement.execute(); if (isResultSet) { ResultSet rs = preparedStatement.getResultSet(); try { //out.write(TransferStatus.SEND_OK + CR_LF); ServerSqlManager.writeLine(out, TransferStatus.SEND_OK); ResultSetWriter resultSetWriter = new ResultSetWriter(request, out, commonsConfigurator, fileConfigurator, sqlConfigurator, username, sqlOrder, statementHolder); resultSetWriter.write(rs); } finally { if (rs != null) rs.close(); } } else { int rc = preparedStatement.getUpdateCount(); //out.write(TransferStatus.SEND_OK + CR_LF); //out.write("getUpdateCount=" + rc + CR_LF); ServerSqlManager.writeLine(out, TransferStatus.SEND_OK); ServerSqlManager.writeLine(out, "getUpdateCount=" + rc); // Write the preparedStatement.getGeneratedKeys() on the stream // if necessary if (usesAutoGeneratedKeys) { ResultSet rs = preparedStatement.getGeneratedKeys(); try { ResultSetWriter resultSetWriter = new ResultSetWriter(request, out, commonsConfigurator, fileConfigurator, sqlConfigurator, username, sqlOrder, statementHolder); resultSetWriter.write(rs); } finally { if (rs != null) rs.close(); } } } } catch (SQLException e) { ServerLogger.getLogger().log(Level.WARNING, Tag.PRODUCT_PRODUCT_FAIL + CR_LF + "Prepared statement: " + sqlOrder + CR_LF + "- sql order : " + sqlOrder + CR_LF + "- sql parms : " + parameterTypes + CR_LF + "- sql values: " + parameterStringValues + CR_LF + "- exception : " + e.toString()); throw e; } finally { // Close the ServerPreparedStatementParameters if (serverPreparedStatementParameters != null) { serverPreparedStatementParameters.close(); } if (preparedStatement != null) { preparedStatement.close(); } } }
From source file:at.alladin.rmbt.mapServer.MarkerResource.java
@Post("json") public String request(final String entity) { addAllowOrigin();//from w w w . j a v a2 s. c o m final MapServerOptions mso = MapServerOptions.getInstance(); final Classification classification = Classification.getInstance(); JSONObject request = null; final JSONObject answer = new JSONObject(); if (entity != null && !entity.isEmpty()) // try parse the string to a JSON object try { request = new JSONObject(entity); String lang = request.optString("language"); // Load Language Files for Client final List<String> langs = Arrays .asList(settings.getString("RMBT_SUPPORTED_LANGUAGES").split(",\\s*")); if (langs.contains(lang)) labels = ResourceManager.getSysMsgBundle(new Locale(lang)); else lang = settings.getString("RMBT_DEFAULT_LANGUAGE"); // System.out.println(request.toString(4)); final JSONObject coords = request.getJSONObject("coords"); final int zoom; double geo_x = 0; double geo_y = 0; int size = 0; boolean useXY = false; boolean useLatLon = false; if (coords.has("x") && coords.has("y")) useXY = true; else if (coords.has("lat") && coords.has("lon")) useLatLon = true; if (coords.has("z") && (useXY || useLatLon)) { zoom = coords.optInt("z"); if (useXY) { geo_x = coords.optDouble("x"); geo_y = coords.optDouble("y"); } else if (useLatLon) { final double tmpLat = coords.optDouble("lat"); final double tmpLon = coords.optDouble("lon"); geo_x = GeoCalc.lonToMeters(tmpLon); geo_y = GeoCalc.latToMeters(tmpLat); // System.out.println(String.format("using %f/%f", geo_x, geo_y)); } if (coords.has("size")) size = coords.getInt("size"); if (zoom != 0 && geo_x != 0 && geo_y != 0) { double radius = 0; if (size > 0) radius = size * GeoCalc.getResFromZoom(256, zoom); // TODO use real tile size else radius = CLICK_RADIUS * GeoCalc.getResFromZoom(256, zoom); // TODO use real tile size final double geo_x_min = geo_x - radius; final double geo_x_max = geo_x + radius; final double geo_y_min = geo_y - radius; final double geo_y_max = geo_y + radius; String hightlightUUIDString = null; UUID highlightUUID = null; final JSONObject mapOptionsObj = request.getJSONObject("options"); String optionStr = mapOptionsObj.optString("map_options"); if (optionStr == null || optionStr.length() == 0) // set // default optionStr = "mobile/download"; final MapOption mo = mso.getMapOptionMap().get(optionStr); final List<SQLFilter> filters = new ArrayList<>(mso.getDefaultMapFilters()); filters.add(mso.getAccuracyMapFilter()); final JSONObject mapFilterObj = request.getJSONObject("filter"); final Iterator<?> keys = mapFilterObj.keys(); while (keys.hasNext()) { final String key = (String) keys.next(); if (mapFilterObj.get(key) instanceof Object) if (key.equals("highlight")) hightlightUUIDString = mapFilterObj.getString(key); else { final MapFilter mapFilter = mso.getMapFilterMap().get(key); if (mapFilter != null) filters.add(mapFilter.getFilter(mapFilterObj.getString(key))); } } if (hightlightUUIDString != null) try { highlightUUID = UUID.fromString(hightlightUUIDString); } catch (final Exception e) { highlightUUID = null; } if (conn != null) { PreparedStatement ps = null; ResultSet rs = null; final StringBuilder whereSQL = new StringBuilder(mo.sqlFilter); for (final SQLFilter sf : filters) whereSQL.append(" AND ").append(sf.where); final String sql = String.format("SELECT" + (useLatLon ? " geo_lat lat, geo_long lon" : " ST_X(t.location) x, ST_Y(t.location) y") + ", t.time, t.timezone, t.speed_download, t.speed_upload, t.ping_median, t.network_type," + " t.signal_strength, t.lte_rsrp, t.wifi_ssid, t.network_operator_name, t.network_operator," + " t.network_sim_operator, t.roaming_type, t.public_ip_as_name, " //TODO: sim_operator obsoled by sim_name + " pMob.shortname mobile_provider_name," // TODO: obsoleted by mobile_network_name + " prov.shortname provider_text, t.open_test_uuid," + " COALESCE(mnwk.shortname,mnwk.name) mobile_network_name," + " COALESCE(msim.shortname,msim.name) mobile_sim_name" + (highlightUUID == null ? "" : " , c.uid, c.uuid") + " FROM v_test t" + " LEFT JOIN mccmnc2name mnwk ON t.mobile_network_id=mnwk.uid" + " LEFT JOIN mccmnc2name msim ON t.mobile_sim_id=msim.uid" + " LEFT JOIN provider prov" + " ON t.provider_id=prov.uid" + " LEFT JOIN provider pMob" + " ON t.mobile_provider_id=pMob.uid" + (highlightUUID == null ? "" : " LEFT JOIN client c ON (t.client_id=c.uid AND c.uuid=?)") + " WHERE" + " %s" + " AND location && ST_SetSRID(ST_MakeBox2D(ST_Point(?,?), ST_Point(?,?)), 900913)" + " ORDER BY" + (highlightUUID == null ? "" : " c.uid ASC,") + " t.uid DESC" + " LIMIT 5", whereSQL); // System.out.println("SQL: " + sql); ps = conn.prepareStatement(sql); int i = 1; if (highlightUUID != null) ps.setObject(i++, highlightUUID); for (final SQLFilter sf : filters) i = sf.fillParams(i, ps); ps.setDouble(i++, geo_x_min); ps.setDouble(i++, geo_y_min); ps.setDouble(i++, geo_x_max); ps.setDouble(i++, geo_y_max); // System.out.println("SQL: " + ps.toString()); if (ps.execute()) { final Locale locale = new Locale(lang); final Format format = new SignificantFormat(2, locale); final JSONArray resultList = new JSONArray(); rs = ps.getResultSet(); while (rs.next()) { final JSONObject jsonItem = new JSONObject(); JSONArray jsonItemList = new JSONArray(); // RMBTClient Info if (highlightUUID != null && rs.getString("uuid") != null) jsonItem.put("highlight", true); final double res_x = rs.getDouble(1); final double res_y = rs.getDouble(2); final String openTestUUID = rs.getObject("open_test_uuid").toString(); jsonItem.put("lat", res_x); jsonItem.put("lon", res_y); jsonItem.put("open_test_uuid", "O" + openTestUUID); // marker.put("uid", uid); final Date date = rs.getTimestamp("time"); final String tzString = rs.getString("timezone"); final TimeZone tz = TimeZone.getTimeZone(tzString); final DateFormat dateFormat = DateFormat.getDateTimeInstance(DateFormat.MEDIUM, DateFormat.MEDIUM, locale); dateFormat.setTimeZone(tz); jsonItem.put("time_string", dateFormat.format(date)); final int fieldDown = rs.getInt("speed_download"); JSONObject singleItem = new JSONObject(); singleItem.put("title", labels.getString("RESULT_DOWNLOAD")); final String downloadString = String.format("%s %s", format.format(fieldDown / 1000d), labels.getString("RESULT_DOWNLOAD_UNIT")); singleItem.put("value", downloadString); singleItem.put("classification", Classification.classify(classification.THRESHOLD_DOWNLOAD, fieldDown)); // singleItem.put("help", "www.rtr.at"); jsonItemList.put(singleItem); final int fieldUp = rs.getInt("speed_upload"); singleItem = new JSONObject(); singleItem.put("title", labels.getString("RESULT_UPLOAD")); final String uploadString = String.format("%s %s", format.format(fieldUp / 1000d), labels.getString("RESULT_UPLOAD_UNIT")); singleItem.put("value", uploadString); singleItem.put("classification", Classification.classify(classification.THRESHOLD_UPLOAD, fieldUp)); // singleItem.put("help", "www.rtr.at"); jsonItemList.put(singleItem); final long fieldPing = rs.getLong("ping_median"); final int pingValue = (int) Math.round(rs.getDouble("ping_median") / 1000000d); singleItem = new JSONObject(); singleItem.put("title", labels.getString("RESULT_PING")); final String pingString = String.format("%s %s", format.format(pingValue), labels.getString("RESULT_PING_UNIT")); singleItem.put("value", pingString); singleItem.put("classification", Classification.classify(classification.THRESHOLD_PING, fieldPing)); // singleItem.put("help", "www.rtr.at"); jsonItemList.put(singleItem); final int networkType = rs.getInt("network_type"); final String signalField = rs.getString("signal_strength"); if (signalField != null && signalField.length() != 0) { final int signalValue = rs.getInt("signal_strength"); final int[] threshold = networkType == 99 || networkType == 0 ? classification.THRESHOLD_SIGNAL_WIFI : classification.THRESHOLD_SIGNAL_MOBILE; singleItem = new JSONObject(); singleItem.put("title", labels.getString("RESULT_SIGNAL")); singleItem.put("value", signalValue + " " + labels.getString("RESULT_SIGNAL_UNIT")); singleItem.put("classification", Classification.classify(threshold, signalValue)); jsonItemList.put(singleItem); } final String lteRsrpField = rs.getString("lte_rsrp"); if (lteRsrpField != null && lteRsrpField.length() != 0) { final int lteRsrpValue = rs.getInt("lte_rsrp"); final int[] threshold = classification.THRESHOLD_SIGNAL_RSRP; singleItem = new JSONObject(); singleItem.put("title", labels.getString("RESULT_LTE_RSRP")); singleItem.put("value", lteRsrpValue + " " + labels.getString("RESULT_LTE_RSRP_UNIT")); singleItem.put("classification", Classification.classify(threshold, lteRsrpValue)); jsonItemList.put(singleItem); } jsonItem.put("measurement", jsonItemList); jsonItemList = new JSONArray(); singleItem = new JSONObject(); singleItem.put("title", labels.getString("RESULT_NETWORK_TYPE")); singleItem.put("value", Helperfunctions.getNetworkTypeName(networkType)); jsonItemList.put(singleItem); if (networkType == 98 || networkType == 99) // mobile wifi or browser { String providerText = MoreObjects.firstNonNull( rs.getString("provider_text"), rs.getString("public_ip_as_name")); if (!Strings.isNullOrEmpty(providerText)) { if (providerText.length() > (MAX_PROVIDER_LENGTH + 3)) { providerText = providerText.substring(0, MAX_PROVIDER_LENGTH) + "..."; } singleItem = new JSONObject(); singleItem.put("title", labels.getString("RESULT_PROVIDER")); singleItem.put("value", providerText); jsonItemList.put(singleItem); } if (networkType == 99) // mobile wifi { if (highlightUUID != null && rs.getString("uuid") != null) // own test { final String ssid = rs.getString("wifi_ssid"); if (ssid != null && ssid.length() != 0) { singleItem = new JSONObject(); singleItem.put("title", labels.getString("RESULT_WIFI_SSID")); singleItem.put("value", ssid.toString()); jsonItemList.put(singleItem); } } } } else // mobile { String networkOperator = rs.getString("network_operator"); String mobileNetworkName = rs.getString("mobile_network_name"); String simOperator = rs.getString("network_sim_operator"); String mobileSimName = rs.getString("mobile_sim_name"); final int roamingType = rs.getInt("roaming_type"); //network if (!Strings.isNullOrEmpty(networkOperator)) { final String mobileNetworkString; if (roamingType != 2) { //not international roaming - display name of home network if (Strings.isNullOrEmpty(mobileSimName)) mobileNetworkString = networkOperator; else mobileNetworkString = String.format("%s (%s)", mobileSimName, networkOperator); } else { //international roaming - display name of network if (Strings.isNullOrEmpty(mobileSimName)) mobileNetworkString = networkOperator; else mobileNetworkString = String.format("%s (%s)", mobileNetworkName, networkOperator); } singleItem = new JSONObject(); singleItem.put("title", labels.getString("RESULT_MOBILE_NETWORK")); singleItem.put("value", mobileNetworkString); jsonItemList.put(singleItem); } //home network (sim) else if (!Strings.isNullOrEmpty(simOperator)) { final String mobileNetworkString; if (Strings.isNullOrEmpty(mobileSimName)) mobileNetworkString = simOperator; else mobileNetworkString = String.format("%s (%s)", mobileSimName, simOperator); /* if (!Strings.isNullOrEmpty(mobileProviderName)) { mobileNetworkString = mobileProviderName; } else { mobileNetworkString = simOperator; } */ singleItem = new JSONObject(); singleItem.put("title", labels.getString("RESULT_HOME_NETWORK")); singleItem.put("value", mobileNetworkString); jsonItemList.put(singleItem); } if (roamingType > 0) { singleItem = new JSONObject(); singleItem.put("title", labels.getString("RESULT_ROAMING")); singleItem.put("value", Helperfunctions.getRoamingType(labels, roamingType)); jsonItemList.put(singleItem); } } jsonItem.put("net", jsonItemList); resultList.put(jsonItem); if (resultList.length() == 0) System.out.println("Error getting Results."); // errorList.addError(MessageFormat.format(labels.getString("ERROR_DB_GET_CLIENT"), // new Object[] {uuid})); } answer.put("measurements", resultList); } else System.out.println("Error executing SQL."); } else System.out.println("No Database Connection."); } } else System.out.println("Expected request is missing."); } catch (final JSONException e) { System.out.println("Error parsing JSDON Data " + e.toString()); } catch (final SQLException e) { e.printStackTrace(); } else System.out.println("No Request."); return answer.toString(); }
From source file:org.openanzo.jdbc.container.sql.NodeSQL.java
/** * Runs the insertCommonValueWithIdentity prepared statement. * <code>/* ww w. j a v a 2 s.c o m*/ * INSERT INTO {0} ( VALUE) VALUES( ?) * </code> * *@param stmtProvider * factory and cache of PreparedStatments *@param connection * connection to underlying database * *@param value template parameter * *@param commonValuesTable template parameter *@return Long *@throws org.openanzo.jdbc.utils.RdbException */ public static Long insertCommonValueWithIdentity( final org.openanzo.jdbc.utils.PreparedStatementProvider stmtProvider, final java.sql.Connection connection, String value, String commonValuesTable) throws org.openanzo.jdbc.utils.RdbException { java.sql.PreparedStatement ps = null; //long startTimer=System.currentTimeMillis(); try { ps = stmtProvider.getPreparedSQLStatementWithGeneratedIDS(insertCommonValueWithIdentity, new String[] { commonValuesTable }, connection); int argc = 1; if (value == null) { throw new org.openanzo.jdbc.utils.RdbException( org.openanzo.exceptions.ExceptionConstants.RDB.NULL_PARAMETER, "value", "insertCommonValueWithIdentity"); } else { ps.setString(argc++, value); } java.sql.ResultSet rs = null; try { if (ps.execute()) { rs = ps.getResultSet(); } else { rs = ps.getGeneratedKeys(); } if (rs != null && rs.next()) { return rs.getLong(1); } else { return null; } } finally { if (rs != null) { try { rs.close(); } catch (java.sql.SQLException sqle) { if (log.isDebugEnabled()) log.debug(org.openanzo.exceptions.LogUtils.RDB_MARKER, "Error closing result set", sqle); } } } } catch (java.sql.SQLException e) { throw new org.openanzo.jdbc.utils.RdbException( org.openanzo.exceptions.ExceptionConstants.RDB.FAILED_EXECUTING_SQL, e, "insertCommonValueWithIdentity", stmtProvider.getSqlString(insertCommonValueWithIdentity), "" + "value=" + ((value != null) ? value.toString() : "null"), "" + "commonValuesTable=" + ((commonValuesTable != null) ? commonValuesTable.toString() : "null")); } finally { if (ps != null) { try { ps.close(); } catch (java.sql.SQLException sqle) { if (log.isDebugEnabled()) log.debug(org.openanzo.exceptions.LogUtils.RDB_MARKER, "Error closing prepared statement", sqle); } } //long endtimer=(System.currentTimeMillis()-startTimer); //if(endtimer>CUTOFF)System.out.println("[insertCommonValueWithIdentity]"+endtimer); } }