List of usage examples for java.sql ResultSet getObject
Object getObject(String columnLabel) throws SQLException;
Gets the value of the designated column in the current row of this ResultSet object as an Object in the Java programming language.
From source file:at.alladin.rmbt.mapServer.MarkerResource.java
@Post("json") public String request(final String entity) { addAllowOrigin();/* w w w . j a v a 2s. co 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:com.belle.yitiansystem.merchant.service.impl.MerchantsService.java
/** * ???3?// w w w. j a v a2s . c om * * @throws SQLException * */ private List<Map<String, Object>> getCommodityCatb2cByStructName(String structName) throws SQLException { List<Map<String, Object>> maps = null; String sql = "select struct_name,id,no,cat_name from tbl_commodity_catb2c WHERE level = 3 and delete_flag=1 "; if (StringUtils.isNotBlank(structName)) { // struct_name LIKE ? AND sql += " and struct_name like '" + structName + "%'"; Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try { conn = getConnection(); pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); Map<String, Object> map = null; maps = new ArrayList<Map<String, Object>>(); while (rs.next()) { map = new HashMap<String, Object>(); ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 0; i < rsmd.getColumnCount();) { ++i; String key = rsmd.getColumnLabel(i).toLowerCase(); if (map.containsKey(key)) { throw new IllegalArgumentException("?key " + key); } map.put(key, rs.getObject(i)); } maps.add(map); } } catch (Exception e) { // TODO: handle exception logger.error("???3?!", e); } finally { close(conn, pstmt, rs); } } return maps; }
From source file:com.belle.yitiansystem.merchant.service.impl.MerchantsService.java
/** * sql?//from w w w . j a v a 2 s. c o m * * @author wang.m * @throws SQLException */ private List<Map<String, Object>> getMapBysql(String sql, Object[] param) throws SQLException { List<Map<String, Object>> maps = null; Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try { conn = getConnection(); pstmt = conn.prepareStatement(sql); if (null != param && param.length > 0) { for (int i = 0; i < param.length; i++) { pstmt.setObject(i + 1, param[i]); } } rs = pstmt.executeQuery(); Map<String, Object> map = null; maps = new ArrayList<Map<String, Object>>(); while (rs.next()) { map = new HashMap<String, Object>(); ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 0; i < rsmd.getColumnCount();) { ++i; String key = rsmd.getColumnLabel(i).toLowerCase(); if (map.containsKey(key)) { throw new IllegalArgumentException("?key " + key); } map.put(key, rs.getObject(i)); } maps.add(map); } } catch (Exception e) { // TODO: handle exception logger.error("sql?!", e); } finally { close(conn, pstmt, rs); } return maps; }
From source file:com.nway.spring.jdbc.bean.AsmBeanProcessor.java
private Object processColumn(ResultSet rs, int index, Class<?> propType, String writer, String processorName, String beanName, MethodVisitor mv) throws SQLException { if (propType.equals(String.class)) { visitMethod(mv, index, beanName, "Ljava/lang/String;", "getString", writer); return rs.getString(index); } else if (propType.equals(Integer.TYPE)) { visitMethod(mv, index, beanName, "I", "getInt", writer); return rs.getInt(index); } else if (propType.equals(Integer.class)) { visitMethodWrap(mv, index, beanName, PROPERTY_TYPE_INTEGER, writer, processorName); return JdbcUtils.getResultSetValue(rs, index, Integer.class); } else if (propType.equals(Long.TYPE)) { visitMethod(mv, index, beanName, "J", "getLong", writer); return rs.getLong(index); } else if (propType.equals(Long.class)) { visitMethodWrap(mv, index, beanName, PROPERTY_TYPE_LONG, writer, processorName); return JdbcUtils.getResultSetValue(rs, index, Long.class); } else if (propType.equals(java.sql.Date.class)) { visitMethod(mv, index, beanName, "Ljava/sql/Date;", "getDate", writer); return rs.getDate(index); } else if (propType.equals(java.util.Date.class)) { visitMethodCast(mv, index, beanName, PROPERTY_TYPE_DATE, "java/util/Date", writer); return rs.getTimestamp(index); } else if (propType.equals(Timestamp.class)) { visitMethod(mv, index, beanName, "Ljava/sql/Timestamp;", "getTimestamp", writer); return rs.getTimestamp(index); } else if (propType.equals(Time.class)) { visitMethod(mv, index, beanName, "Ljava/sql/Time;", "getTime", writer); return rs.getTime(index); } else if (propType.equals(Double.TYPE)) { visitMethod(mv, index, beanName, "D", "getDouble", writer); return rs.getDouble(index); } else if (propType.equals(Double.class)) { visitMethodWrap(mv, index, beanName, PROPERTY_TYPE_DOUBLE, writer, processorName); return JdbcUtils.getResultSetValue(rs, index, Double.class); } else if (propType.equals(Float.TYPE)) { visitMethod(mv, index, beanName, "F", "getFloat", writer); return rs.getFloat(index); } else if (propType.equals(Float.class)) { visitMethodWrap(mv, index, beanName, PROPERTY_TYPE_FLOAT, writer, processorName); return JdbcUtils.getResultSetValue(rs, index, Float.class); } else if (propType.equals(Boolean.TYPE)) { visitMethod(mv, index, beanName, "Z", "getBoolean", writer); return rs.getBoolean(index); } else if (propType.equals(Boolean.class)) { visitMethodWrap(mv, index, beanName, PROPERTY_TYPE_BOOLEAN, writer, processorName); return JdbcUtils.getResultSetValue(rs, index, Boolean.class); } else if (propType.equals(Clob.class)) { visitMethod(mv, index, beanName, "Ljava/sql/Clob;", "getClob", writer); return rs.getClob(index); } else if (propType.equals(Blob.class)) { visitMethod(mv, index, beanName, "Ljava/sql/Blob;", "getBlob", writer); return rs.getBlob(index); } else if (propType.equals(byte[].class)) { visitMethod(mv, index, beanName, "[B", "getBytes", writer); return rs.getBytes(index); } else if (propType.equals(Short.TYPE)) { visitMethod(mv, index, beanName, "S", "getShort", writer); return rs.getShort(index); } else if (propType.equals(Short.class)) { visitMethodWrap(mv, index, beanName, PROPERTY_TYPE_SHORT, writer, processorName); return JdbcUtils.getResultSetValue(rs, index, Short.class); } else if (propType.equals(Byte.TYPE)) { visitMethod(mv, index, beanName, "B", "getByte", writer); return rs.getByte(index); } else if (propType.equals(Byte.class)) { visitMethodWrap(mv, index, beanName, PROPERTY_TYPE_BYTE, writer, processorName); return rs.getByte(index); } else {//from ww w. ja v a 2s .c om visitMethodCast(mv, index, beanName, PROPERTY_TYPE_OTHER, propType.getName().replace('.', '/'), writer); return rs.getObject(index); } }
From source file:edu.ku.brc.specify.dbsupport.SpecifySchemaUpdateService.java
/** * @param conn//from w w w .jav a 2s . c o m * @param sql * @param tableName * @param fldName * @param divToAgentToFixHash */ private void fixAgents(final Connection conn, final String sqlArg, final String tableName, final String fieldName, final HashMap<Integer, Integer> divToAgentToFixHash, final String inClause) { PreparedStatement pStmt = null; Statement stmt = null; try { String fullInClause = String.format(inClause, fieldName); String sql = addInClause(sqlArg, fullInClause); int inx = sql.indexOf("FROM"); String tmpSQL = "SELECT COUNT(*) " + sql.substring(inx); //log.debug(tmpSQL); int total = BasicSQLUtils.getCountAsInt(tmpSQL); int percentStep = (int) ((double) total * 0.02); frame.setProcess(0, total); frame.setDesc("Fixing " + tableName); String fldName = fieldName; inx = fieldName.indexOf('.'); if (inx > -1) { fldName = fieldName.substring(inx + 1); } stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql); ResultSetMetaData rsmd = rs.getMetaData(); String recIdColName = rs.getMetaData().getColumnName(1); String sqlStr = String.format("UPDATE %s SET %s=? WHERE %s = ?", tableName, fldName, recIdColName); //System.out.println(sql); //System.out.println(sqlStr); pStmt = conn.prepareStatement(sqlStr); int cnt = 0; int itemsFixed = 0; while (rs.next()) { Integer recID = rs.getInt(1); //Integer agentId = rs.getInt(2); Integer divId = rs.getObject(3) != null ? rs.getInt(3) : null; Integer divId2 = rsmd.getColumnCount() == 4 ? rs.getInt(4) : null; if (divId2 != null && divId == null) { divId = divId2; } Integer mappedAgentId = divToAgentToFixHash.get(divId); if (mappedAgentId != null) { pStmt.setInt(1, mappedAgentId); pStmt.setInt(2, recID); if (pStmt.executeUpdate() != 1) { errMsgList .add(String.format("Error deleting Agent %d Table %s Field %s RecIdCol %s RecID %d", mappedAgentId, tableName, fldName, recIdColName, recID)); } //log.debug(String.format("CNG %s.%s (%d) Old: %d -> New: %d", tableName, fldName, recID, agentId, mappedAgentId)); itemsFixed++; } cnt++; if (percentStep > 0 && cnt % percentStep == 0) { frame.setProcess(cnt); } } frame.setProcess(total); log.debug(String.format("%d Fixed ->%s.%s (%s)", itemsFixed, tableName, fldName, sql)); rs.close(); } catch (Exception ex) { ex.printStackTrace(); errMsgList.add(ex.getMessage()); } finally { try { if (pStmt != null) { pStmt.close(); } if (stmt != null) { stmt.close(); } } catch (Exception ex) { } } }