List of usage examples for java.sql PreparedStatement setNull
void setNull(int parameterIndex, int sqlType) throws SQLException;
NULL. From source file:com.feedzai.commons.sql.abstraction.engine.impl.OracleEngine.java
@Override protected int entityToPreparedStatement(final DbEntity entity, final PreparedStatement ps, final EntityEntry entry, final boolean useAutoInc) throws DatabaseEngineException { int i = 1;// www . j a v a2 s. c om for (DbColumn column : entity.getColumns()) { if (column.isAutoInc() && useAutoInc) { continue; } try { final Object val; if (column.isDefaultValueSet() && !entry.containsKey(column.getName())) { val = column.getDefaultValue().getConstant(); } else { val = entry.get(column.getName()); } switch (column.getDbColumnType()) { case BLOB: ps.setBytes(i, objectToArray(val)); break; case CLOB: if (val == null) { ps.setNull(i, Types.CLOB); break; } if (val instanceof String) { StringReader sr = new StringReader((String) val); ps.setClob(i, sr); } else { throw new DatabaseEngineException("Cannot convert " + val.getClass().getSimpleName() + " to String. CLOB columns only accept Strings."); } break; case BOOLEAN: Boolean b = (Boolean) val; if (b == null) { ps.setObject(i, null); } else if (b) { ps.setObject(i, "1"); } else { ps.setObject(i, "0"); } break; default: ps.setObject(i, ensureNoUnderflow(val)); } } catch (Exception ex) { throw new DatabaseEngineException("Error while mapping variables to database", ex); } i++; } return i - 1; }
From source file:org.ojbc.adapters.analyticsstaging.custody.dao.AnalyticalDatastoreDAOImpl.java
@Override public void saveBookingCharges(List<BookingCharge> bookingCharges) { log.info("Inserting row into BookingCharge table: " + bookingCharges); final String sqlString = "INSERT INTO BookingCharge (BookingArrestID, ChargeCode, AgencyID, " + "BondAmount, BondTypeID, ChargeClassTypeID, BondStatusTypeId, ChargeJurisdictionTypeId, ChargeDisposition) " + "values (?,?,?,?,?,?,?,?,?)"; jdbcTemplate.batchUpdate(sqlString, new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { BookingCharge bookingCharge = bookingCharges.get(i); ps.setInt(1, bookingCharge.getBookingArrestId()); setPreparedStatementVariable(bookingCharge.getChargeCode(), ps, 2); setPreparedStatementVariable(bookingCharge.getAgencyId(), ps, 3); setPreparedStatementVariable(bookingCharge.getBondAmount(), ps, 4); if (bookingCharge.getBondType() != null) { setPreparedStatementVariable(bookingCharge.getBondType().getKey(), ps, 5); } else { ps.setNull(5, java.sql.Types.NULL); }/*from ww w . j a va 2 s . c om*/ setPreparedStatementVariable(bookingCharge.getChargeClassTypeId(), ps, 6); setPreparedStatementVariable(bookingCharge.getBondStatusTypeId(), ps, 7); setPreparedStatementVariable(bookingCharge.getChargeJurisdictionTypeId(), ps, 8); setPreparedStatementVariable(bookingCharge.getChargeDisposition(), ps, 9); } public int getBatchSize() { return bookingCharges.size(); } }); }
From source file:org.apache.phoenix.query.BaseTest.java
protected static void initATableValues(String tenantId, byte[][] splits, Date date, Long ts, String url) throws Exception { if (ts == null) { ensureTableCreated(url, ATABLE_NAME, splits); } else {/* ww w . j a va 2 s .c o m*/ ensureTableCreated(url, ATABLE_NAME, splits, ts - 5); } Properties props = new Properties(); if (ts != null) { props.setProperty(CURRENT_SCN_ATTRIB, Long.toString(ts - 3)); } Connection conn = DriverManager.getConnection(url, props); try { // Insert all rows at ts PreparedStatement stmt = conn.prepareStatement("upsert into " + "ATABLE(" + " ORGANIZATION_ID, " + " ENTITY_ID, " + " A_STRING, " + " B_STRING, " + " A_INTEGER, " + " A_DATE, " + " X_DECIMAL, " + " X_LONG, " + " X_INTEGER," + " Y_INTEGER," + " A_BYTE," + " A_SHORT," + " A_FLOAT," + " A_DOUBLE," + " A_UNSIGNED_FLOAT," + " A_UNSIGNED_DOUBLE)" + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); stmt.setString(1, tenantId); stmt.setString(2, ROW1); stmt.setString(3, A_VALUE); stmt.setString(4, B_VALUE); stmt.setInt(5, 1); stmt.setDate(6, date); stmt.setBigDecimal(7, null); stmt.setNull(8, Types.BIGINT); stmt.setNull(9, Types.INTEGER); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 1); stmt.setShort(12, (short) 128); stmt.setFloat(13, 0.01f); stmt.setDouble(14, 0.0001); stmt.setFloat(15, 0.01f); stmt.setDouble(16, 0.0001); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW2); stmt.setString(3, A_VALUE); stmt.setString(4, C_VALUE); stmt.setInt(5, 2); stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 1)); stmt.setBigDecimal(7, null); stmt.setNull(8, Types.BIGINT); stmt.setNull(9, Types.INTEGER); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 2); stmt.setShort(12, (short) 129); stmt.setFloat(13, 0.02f); stmt.setDouble(14, 0.0002); stmt.setFloat(15, 0.02f); stmt.setDouble(16, 0.0002); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW3); stmt.setString(3, A_VALUE); stmt.setString(4, E_VALUE); stmt.setInt(5, 3); stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 2)); stmt.setBigDecimal(7, null); stmt.setNull(8, Types.BIGINT); stmt.setNull(9, Types.INTEGER); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 3); stmt.setShort(12, (short) 130); stmt.setFloat(13, 0.03f); stmt.setDouble(14, 0.0003); stmt.setFloat(15, 0.03f); stmt.setDouble(16, 0.0003); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW4); stmt.setString(3, A_VALUE); stmt.setString(4, B_VALUE); stmt.setInt(5, 4); stmt.setDate(6, date == null ? null : date); stmt.setBigDecimal(7, null); stmt.setNull(8, Types.BIGINT); stmt.setNull(9, Types.INTEGER); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 4); stmt.setShort(12, (short) 131); stmt.setFloat(13, 0.04f); stmt.setDouble(14, 0.0004); stmt.setFloat(15, 0.04f); stmt.setDouble(16, 0.0004); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW5); stmt.setString(3, B_VALUE); stmt.setString(4, C_VALUE); stmt.setInt(5, 5); stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 1)); stmt.setBigDecimal(7, null); stmt.setNull(8, Types.BIGINT); stmt.setNull(9, Types.INTEGER); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 5); stmt.setShort(12, (short) 132); stmt.setFloat(13, 0.05f); stmt.setDouble(14, 0.0005); stmt.setFloat(15, 0.05f); stmt.setDouble(16, 0.0005); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW6); stmt.setString(3, B_VALUE); stmt.setString(4, E_VALUE); stmt.setInt(5, 6); stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 2)); stmt.setBigDecimal(7, null); stmt.setNull(8, Types.BIGINT); stmt.setNull(9, Types.INTEGER); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 6); stmt.setShort(12, (short) 133); stmt.setFloat(13, 0.06f); stmt.setDouble(14, 0.0006); stmt.setFloat(15, 0.06f); stmt.setDouble(16, 0.0006); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW7); stmt.setString(3, B_VALUE); stmt.setString(4, B_VALUE); stmt.setInt(5, 7); stmt.setDate(6, date == null ? null : date); stmt.setBigDecimal(7, BigDecimal.valueOf(0.1)); stmt.setLong(8, 5L); stmt.setInt(9, 5); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 7); stmt.setShort(12, (short) 134); stmt.setFloat(13, 0.07f); stmt.setDouble(14, 0.0007); stmt.setFloat(15, 0.07f); stmt.setDouble(16, 0.0007); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW8); stmt.setString(3, B_VALUE); stmt.setString(4, C_VALUE); stmt.setInt(5, 8); stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 1)); stmt.setBigDecimal(7, BigDecimal.valueOf(3.9)); long l = Integer.MIN_VALUE - 1L; assert (l < Integer.MIN_VALUE); stmt.setLong(8, l); stmt.setInt(9, 4); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 8); stmt.setShort(12, (short) 135); stmt.setFloat(13, 0.08f); stmt.setDouble(14, 0.0008); stmt.setFloat(15, 0.08f); stmt.setDouble(16, 0.0008); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW9); stmt.setString(3, C_VALUE); stmt.setString(4, E_VALUE); stmt.setInt(5, 9); stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 2)); stmt.setBigDecimal(7, BigDecimal.valueOf(3.3)); l = Integer.MAX_VALUE + 1L; assert (l > Integer.MAX_VALUE); stmt.setLong(8, l); stmt.setInt(9, 3); stmt.setInt(10, 300); stmt.setByte(11, (byte) 9); stmt.setShort(12, (short) 0); stmt.setFloat(13, 0.09f); stmt.setDouble(14, 0.0009); stmt.setFloat(15, 0.09f); stmt.setDouble(16, 0.0009); stmt.execute(); conn.commit(); } finally { conn.close(); } }
From source file:org.openanzo.datasource.nodecentric.sql.StatementRdbWrapper.java
/** * Runs the deleteStatement prepared statement. * <code>/*from w ww . j a va 2s . c o m*/ * UPDATE STATEMENTS SET REND=? WHERE ID=?; * </code> * *@param stmtProvider * factory and cache of PreparedStatments *@param connection * connection to underlying database * *@param rend template parameter *@param id template parameter * *@return int *@throws org.openanzo.jdbc.utils.RdbException */ public static int deleteStatement(final org.openanzo.jdbc.utils.PreparedStatementProvider stmtProvider, final java.sql.Connection connection, long rend, String id) throws org.openanzo.jdbc.utils.RdbException { java.sql.PreparedStatement ps = null; //long startTimer=System.currentTimeMillis(); try { ps = stmtProvider.getPreparedSQLStatement(deleteStatement, new String[] {}, connection); int argc = 1; ps.setLong(argc++, rend); if (id == null) { ps.setNull(argc++, java.sql.Types.VARCHAR); } else { ps.setString(argc++, id); } int counter = 0; try { counter = ps.executeUpdate(); } catch (java.sql.SQLException sqle) { if (sqle.getErrorCode() == 1205) { int retries = 0; while (retries < 5) { try { Thread.sleep(5000); } catch (InterruptedException ie) { throw sqle; } try { counter = ps.executeUpdate(); break; } catch (java.sql.SQLException sqleInner) { if (sqleInner.getErrorCode() == 1205) { retries++; } else { throw sqleInner; } } } if (retries >= 5) { throw sqle; } } else { throw sqle; } } return counter; } catch (java.sql.SQLException e) { throw new org.openanzo.jdbc.utils.RdbException( org.openanzo.exceptions.ExceptionConstants.RDB.FAILED_EXECUTING_SQL, e, "deleteStatement", stmtProvider.getSqlString(deleteStatement), "" + "rend=" + (rend) + "," + "id=" + ((id != null) ? id.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("[deleteStatement]"+endtimer); } }
From source file:at.alladin.rmbt.controlServer.ResultResource.java
@Post("json") public String request(final String entity) { final String secret = getContext().getParameters().getFirstValue("RMBT_SECRETKEY"); addAllowOrigin();/*from w w w . j av a2s .c o m*/ JSONObject request = null; final ErrorList errorList = new ErrorList(); final JSONObject answer = new JSONObject(); System.out.println(MessageFormat.format(labels.getString("NEW_RESULT"), getIP())); if (entity != null && !entity.isEmpty()) // try parse the string to a JSON object try { request = new JSONObject(entity); System.out.println(request); final String lang = request.optString("client_language"); // Load Language Files for Client final List<String> langs = Arrays .asList(settings.getString("RMBT_SUPPORTED_LANGUAGES").split(",\\s*")); if (langs.contains(lang)) { errorList.setLanguage(lang); labels = ResourceManager.getSysMsgBundle(new Locale(lang)); } // System.out.println(request.toString(4)); if (conn != null) { conn.setAutoCommit(false); final Test test = new Test(conn); if (request.optString("test_token").length() > 0) { final String[] token = request.getString("test_token").split("_"); try { // Check if UUID final UUID testUuid = UUID.fromString(token[0]); final String data = token[0] + "_" + token[1]; final String hmac = Helperfunctions.calculateHMAC(secret, data); if (hmac.length() == 0) errorList.addError("ERROR_TEST_TOKEN"); if (token[2].length() > 0 && hmac.equals(token[2])) { final List<String> clientNames = Arrays .asList(settings.getString("RMBT_CLIENT_NAME").split(",\\s*")); final List<String> clientVersions = Arrays .asList(settings.getString("RMBT_VERSION_NUMBER").split(",\\s*")); if (test.getTestByUuid(testUuid) > 0) if (clientNames.contains(request.optString("client_name")) && clientVersions.contains(request.optString("client_version"))) { test.setFields(request); final String networkOperator = request .optString("telephony_network_operator"); if (MCC_MNC_PATTERN.matcher(networkOperator).matches()) test.getField("network_operator").setString(networkOperator); else test.getField("network_operator").setString(null); final String networkSimOperator = request .optString("telephony_network_sim_operator"); if (MCC_MNC_PATTERN.matcher(networkSimOperator).matches()) test.getField("network_sim_operator").setString(networkSimOperator); else test.getField("network_sim_operator").setString(null); // RMBTClient Info final String ipLocalRaw = request.optString("test_ip_local", null); if (ipLocalRaw != null) { final InetAddress ipLocalAddress = InetAddresses.forString(ipLocalRaw); // original address (not filtered) test.getField("client_ip_local") .setString(InetAddresses.toAddrString(ipLocalAddress)); // anonymized local address final String ipLocalAnonymized = Helperfunctions .anonymizeIp(ipLocalAddress); test.getField("client_ip_local_anonymized") .setString(ipLocalAnonymized); // type of local ip test.getField("client_ip_local_type") .setString(Helperfunctions.IpType(ipLocalAddress)); // public ip final InetAddress ipPublicAddress = InetAddresses .forString(test.getField("client_public_ip").toString()); test.getField("nat_type").setString( Helperfunctions.getNatType(ipLocalAddress, ipPublicAddress)); } final String ipServer = request.optString("test_ip_server", null); if (ipServer != null) { final InetAddress testServerInetAddress = InetAddresses .forString(ipServer); test.getField("server_ip") .setString(InetAddresses.toAddrString(testServerInetAddress)); } //log IP address final String ipSource = getIP(); test.getField("source_ip").setString(ipSource); //log anonymized address try { final InetAddress ipSourceIP = InetAddress.getByName(ipSource); final String ipSourceAnonymized = Helperfunctions .anonymizeIp(ipSourceIP); test.getField("source_ip_anonymized").setString(ipSourceAnonymized); } catch (UnknownHostException e) { System.out.println("Exception thrown:" + e); } // Additional Info ////////////////////////////////////////////////// // extended test stats: ////////////////////////////////////////////////// final TestStat extendedTestStat = TestStat .checkForSubmittedTestStats(request, test.getUid()); if (extendedTestStat != null) { final TestStatDao testStatDao = new TestStatDao(conn); testStatDao.save(extendedTestStat); } ////////////////////////////////////////////////// JSONArray speedData = request.optJSONArray("speed_detail"); if (speedData != null && !test.hasError()) { final PreparedStatement psSpeed = conn.prepareStatement( "INSERT INTO test_speed (test_id, upload, thread, time, bytes) VALUES (?,?,?,?,?)"); psSpeed.setLong(1, test.getUid()); for (int i = 0; i < speedData.length(); i++) { final JSONObject item = speedData.getJSONObject(i); final String direction = item.optString("direction"); if (direction != null && (direction.equals("download") || direction.equals("upload"))) { psSpeed.setBoolean(2, direction.equals("upload")); psSpeed.setInt(3, item.optInt("thread")); psSpeed.setLong(4, item.optLong("time")); psSpeed.setLong(5, item.optLong("bytes")); psSpeed.executeUpdate(); } } } final JSONArray pingData = request.optJSONArray("pings"); if (pingData != null && !test.hasError()) { final PreparedStatement psPing = conn.prepareStatement( "INSERT INTO ping (test_id, value, value_server, time_ns) " + "VALUES(?,?,?,?)"); psPing.setLong(1, test.getUid()); for (int i = 0; i < pingData.length(); i++) { final JSONObject pingDataItem = pingData.getJSONObject(i); long valueClient = pingDataItem.optLong("value", -1); if (valueClient >= 0) psPing.setLong(2, valueClient); else psPing.setNull(2, Types.BIGINT); long valueServer = pingDataItem.optLong("value_server", -1); if (valueServer >= 0) psPing.setLong(3, valueServer); else psPing.setNull(3, Types.BIGINT); long timeNs = pingDataItem.optLong("time_ns", -1); if (timeNs >= 0) psPing.setLong(4, timeNs); else psPing.setNull(4, Types.BIGINT); psPing.executeUpdate(); } } final JSONArray geoData = request.optJSONArray("geoLocations"); if (geoData != null && !test.hasError()) for (int i = 0; i < geoData.length(); i++) { final JSONObject geoDataItem = geoData.getJSONObject(i); if (geoDataItem.optLong("tstamp", 0) != 0 && geoDataItem.optDouble("geo_lat", 0) != 0 && geoDataItem.optDouble("geo_long", 0) != 0) { final GeoLocation geoloc = new GeoLocation(conn); geoloc.setTest_id(test.getUid()); final long clientTime = geoDataItem.optLong("tstamp"); final Timestamp tstamp = java.sql.Timestamp .valueOf(new Timestamp(clientTime).toString()); geoloc.setTime(tstamp, test.getField("timezone").toString()); geoloc.setAccuracy( (float) geoDataItem.optDouble("accuracy", 0)); geoloc.setAltitude(geoDataItem.optDouble("altitude", 0)); geoloc.setBearing((float) geoDataItem.optDouble("bearing", 0)); geoloc.setSpeed((float) geoDataItem.optDouble("speed", 0)); geoloc.setProvider(geoDataItem.optString("provider", "")); geoloc.setGeo_lat(geoDataItem.optDouble("geo_lat", 0)); geoloc.setGeo_long(geoDataItem.optDouble("geo_long", 0)); geoloc.setTime_ns(geoDataItem.optLong("time_ns", 0)); geoloc.storeLocation(); // Store Last Geolocation as // Testlocation if (i == geoData.length() - 1) { if (geoDataItem.has("geo_lat")) test.getField("geo_lat").setField(geoDataItem); if (geoDataItem.has("geo_long")) test.getField("geo_long").setField(geoDataItem); if (geoDataItem.has("accuracy")) test.getField("geo_accuracy").setField(geoDataItem); if (geoDataItem.has("provider")) test.getField("geo_provider").setField(geoDataItem); } if (geoloc.hasError()) { errorList.addError(geoloc.getError()); break; } } } final JSONArray cellData = request.optJSONArray("cellLocations"); if (cellData != null && !test.hasError()) for (int i = 0; i < cellData.length(); i++) { final JSONObject cellDataItem = cellData.getJSONObject(i); final Cell_location cellloc = new Cell_location(conn); cellloc.setTest_id(test.getUid()); final long clientTime = cellDataItem.optLong("time"); final Timestamp tstamp = java.sql.Timestamp .valueOf(new Timestamp(clientTime).toString()); cellloc.setTime(tstamp, test.getField("timezone").toString()); cellloc.setTime_ns(cellDataItem.optLong("time_ns", 0)); cellloc.setLocation_id(cellDataItem.optInt("location_id", 0)); cellloc.setArea_code(cellDataItem.optInt("area_code", 0)); cellloc.setPrimary_scrambling_code( cellDataItem.optInt("primary_scrambling_code", 0)); cellloc.storeLocation(); if (cellloc.hasError()) { errorList.addError(cellloc.getError()); break; } } int signalStrength = Integer.MAX_VALUE; //measured as RSSI (GSM,UMTS,Wifi) int lteRsrp = Integer.MAX_VALUE; // signal strength measured as RSRP int lteRsrq = Integer.MAX_VALUE; // signal quality of LTE measured as RSRQ int linkSpeed = UNKNOWN; final int networkType = test.getField("network_type").intValue(); final JSONArray signalData = request.optJSONArray("signals"); if (signalData != null && !test.hasError()) { for (int i = 0; i < signalData.length(); i++) { final JSONObject signalDataItem = signalData.getJSONObject(i); final Signal signal = new Signal(conn); signal.setTest_id(test.getUid()); final long clientTime = signalDataItem.optLong("time"); final Timestamp tstamp = java.sql.Timestamp .valueOf(new Timestamp(clientTime).toString()); signal.setTime(tstamp, test.getField("timezone").toString()); final int thisNetworkType = signalDataItem.optInt("network_type_id", 0); signal.setNetwork_type_id(thisNetworkType); final int thisSignalStrength = signalDataItem .optInt("signal_strength", UNKNOWN); if (thisSignalStrength != UNKNOWN) signal.setSignal_strength(thisSignalStrength); signal.setGsm_bit_error_rate( signalDataItem.optInt("gsm_bit_error_rate", 0)); final int thisLinkSpeed = signalDataItem.optInt("wifi_link_speed", 0); signal.setWifi_link_speed(thisLinkSpeed); final int rssi = signalDataItem.optInt("wifi_rssi", UNKNOWN); if (rssi != UNKNOWN) signal.setWifi_rssi(rssi); lteRsrp = signalDataItem.optInt("lte_rsrp", UNKNOWN); lteRsrq = signalDataItem.optInt("lte_rsrq", UNKNOWN); final int lteRssnr = signalDataItem.optInt("lte_rssnr", UNKNOWN); final int lteCqi = signalDataItem.optInt("lte_cqi", UNKNOWN); final long timeNs = signalDataItem.optLong("time_ns", UNKNOWN); signal.setLte_rsrp(lteRsrp); signal.setLte_rsrq(lteRsrq); signal.setLte_rssnr(lteRssnr); signal.setLte_cqi(lteCqi); signal.setTime_ns(timeNs); signal.storeSignal(); if (networkType == 99) // wlan { if (rssi < signalStrength && rssi != UNKNOWN) signalStrength = rssi; } else if (thisSignalStrength < signalStrength && thisSignalStrength != UNKNOWN) signalStrength = thisSignalStrength; if (thisLinkSpeed != 0 && (linkSpeed == UNKNOWN || thisLinkSpeed < linkSpeed)) linkSpeed = thisLinkSpeed; if (signal.hasError()) { errorList.addError(signal.getError()); break; } } // set rssi value (typically GSM,UMTS, but also old LTE-phones) if (signalStrength != Integer.MAX_VALUE && signalStrength != UNKNOWN && signalStrength != 0) // 0 dBm is out of range ((IntField) test.getField("signal_strength")) .setValue(signalStrength); // set rsrp value (typically LTE) if (lteRsrp != Integer.MAX_VALUE && lteRsrp != UNKNOWN && lteRsrp != 0) // 0 dBm is out of range ((IntField) test.getField("lte_rsrp")).setValue(lteRsrp); // set rsrq value (LTE) if (lteRsrq != Integer.MAX_VALUE && lteRsrq != UNKNOWN) ((IntField) test.getField("lte_rsrq")).setValue(lteRsrq); if (linkSpeed != Integer.MAX_VALUE && linkSpeed != UNKNOWN) ((IntField) test.getField("wifi_link_speed")).setValue(linkSpeed); } // use max network type final String sqlMaxNetworkType = "SELECT nt.uid" + " FROM signal s" + " JOIN network_type nt" + " ON s.network_type_id=nt.uid" + " WHERE test_id=?" + " ORDER BY nt.technology_order DESC" + " LIMIT 1"; final PreparedStatement psMaxNetworkType = conn .prepareStatement(sqlMaxNetworkType); psMaxNetworkType.setLong(1, test.getUid()); if (psMaxNetworkType.execute()) { final ResultSet rs = psMaxNetworkType.getResultSet(); if (rs.next()) { final int maxNetworkType = rs.getInt("uid"); if (maxNetworkType != 0) ((IntField) test.getField("network_type")) .setValue(maxNetworkType); } } /* * check for different types (e.g. * 2G/3G) */ final String sqlAggSignal = "WITH agg AS" + " (SELECT array_agg(DISTINCT nt.group_name ORDER BY nt.group_name) agg" + " FROM signal s" + " JOIN network_type nt ON s.network_type_id=nt.uid WHERE test_id=?)" + " SELECT uid FROM agg JOIN network_type nt ON nt.aggregate=agg"; final PreparedStatement psAgg = conn.prepareStatement(sqlAggSignal); psAgg.setLong(1, test.getUid()); if (psAgg.execute()) { final ResultSet rs = psAgg.getResultSet(); if (rs.next()) { final int newNetworkType = rs.getInt("uid"); if (newNetworkType != 0) ((IntField) test.getField("network_type")) .setValue(newNetworkType); } } if (test.getField("network_type").intValue() <= 0) errorList.addError("ERROR_NETWORK_TYPE"); final IntField downloadField = (IntField) test.getField("speed_download"); if (downloadField.isNull() || downloadField.intValue() <= 0 || downloadField.intValue() > 10000000) // 10 gbit/s limit errorList.addError("ERROR_DOWNLOAD_INSANE"); final IntField upField = (IntField) test.getField("speed_upload"); if (upField.isNull() || upField.intValue() <= 0 || upField.intValue() > 10000000) // 10 gbit/s limit errorList.addError("ERROR_UPLOAD_INSANE"); //clients still report eg: "test_ping_shortest":9195040 (note the 'test_' prefix there!) final LongField pingField = (LongField) test.getField("ping_shortest"); if (pingField.isNull() || pingField.longValue() <= 0 || pingField.longValue() > 60000000000L) // 1 min limit errorList.addError("ERROR_PING_INSANE"); if (errorList.isEmpty()) test.getField("status").setString("FINISHED"); else test.getField("status").setString("ERROR"); test.storeTestResults(false); if (test.hasError()) errorList.addError(test.getError()); } else errorList.addError("ERROR_CLIENT_VERSION"); } else errorList.addError("ERROR_TEST_TOKEN_MALFORMED"); } catch (final IllegalArgumentException e) { e.printStackTrace(); errorList.addError("ERROR_TEST_TOKEN_MALFORMED"); } } else errorList.addError("ERROR_TEST_TOKEN_MISSING"); conn.commit(); } else errorList.addError("ERROR_DB_CONNECTION"); } catch (final JSONException e) { errorList.addError("ERROR_REQUEST_JSON"); System.out.println("Error parsing JSDON Data " + e.toString()); e.printStackTrace(); } catch (final SQLException e) { System.out.println("Error while storing data " + e.toString()); e.printStackTrace(); } else errorList.addErrorString("Expected request is missing."); try { answer.putOpt("error", errorList.getList()); } catch (final JSONException e) { System.out.println("Error saving ErrorList: " + e.toString()); } return answer.toString(); }
From source file:com.flexive.core.storage.genericSQL.GenericBinarySQLStorage.java
/** * Set insert parameters for a preview image * * @param ps the prepared statement to use * @param previewFile the preview file * @param dimensionsPreview dimensions (width, height) * @param positionBinary position in the prepared statement * @param positionSize position of the file size parameter in the prepared statement * @return FileInputStream/* w w w .j av a 2 s . c o m*/ * @throws FileNotFoundException if the file does not exist * @throws SQLException on errors */ private FileInputStream setPreviewTransferParameters(PreparedStatement ps, File previewFile, int[] dimensionsPreview, int positionBinary, int positionSize) throws FileNotFoundException, SQLException { FileInputStream pin = null; if (previewFile != null && previewFile.exists()) { pin = new FileInputStream(previewFile); ps.setBinaryStream(positionBinary, pin, (int) previewFile.length()); ps.setInt(positionBinary + 1, dimensionsPreview[0]); ps.setInt(positionBinary + 2, dimensionsPreview[1]); ps.setInt(positionSize, (int) previewFile.length()); } else { ps.setNull(positionBinary, Types.BINARY); ps.setInt(positionBinary + 1, 0); ps.setInt(positionBinary + 2, 0); ps.setInt(positionSize, 0); } return pin; }
From source file:com.thinkmore.framework.orm.hibernate.SimpleHibernateDao.java
public void setParameters(PreparedStatement ps, int j, Object value) throws SQLException { if (value != null) { if (value instanceof java.lang.Integer) { ps.setInt(j, (Integer) value); } else if (value instanceof java.lang.Long) { ps.setLong(j, (Long) value); } else if (value instanceof java.util.Date) { ps.setTimestamp(j, new java.sql.Timestamp(((Date) value).getTime())); } else if (value instanceof java.sql.Date) { ps.setDate(j, new java.sql.Date(((Date) value).getTime())); } else if (value instanceof java.lang.String) { ps.setString(j, value.toString()); } else if (value instanceof java.lang.Double) { ps.setDouble(j, (Double) value); } else if (value instanceof java.lang.Byte) { ps.setByte(j, (Byte) value); } else if (value instanceof java.lang.Character) { ps.setString(j, value.toString()); } else if (value instanceof java.lang.Float) { ps.setFloat(j, (Float) value); } else if (value instanceof java.lang.Boolean) { ps.setBoolean(j, (Boolean) value); } else if (value instanceof java.lang.Short) { ps.setShort(j, (Short) value); } else {//from w w w. java2 s . c om ps.setObject(j, value); } } else { ps.setNull(j, Types.NULL); } }
From source file:org.cloudfoundry.identity.uaa.scim.jdbc.JdbcScimUserProvisioning.java
@Override public ScimUser create(final ScimUser user) { if (!hasText(user.getOrigin())) { user.setOrigin(OriginKeys.UAA);//www.j av a 2s. c o m } validate(user); logger.debug("Creating new user: " + user.getUserName()); final String id = UUID.randomUUID().toString(); final String identityZoneId = IdentityZoneHolder.get().getId(); final String origin = user.getOrigin(); try { jdbcTemplate.update(CREATE_USER_SQL, new PreparedStatementSetter() { @Override public void setValues(PreparedStatement ps) throws SQLException { Timestamp t = new Timestamp(new Date().getTime()); ps.setString(1, id); ps.setInt(2, user.getVersion()); ps.setTimestamp(3, t); ps.setTimestamp(4, t); ps.setString(5, user.getUserName()); ps.setString(6, user.getPrimaryEmail()); if (user.getName() == null) { ps.setString(7, null); ps.setString(8, null); } else { ps.setString(7, user.getName().getGivenName()); ps.setString(8, user.getName().getFamilyName()); } ps.setBoolean(9, user.isActive()); String phoneNumber = extractPhoneNumber(user); ps.setString(10, phoneNumber); ps.setBoolean(11, user.isVerified()); ps.setString(12, origin); ps.setString(13, hasText(user.getExternalId()) ? user.getExternalId() : null); ps.setString(14, identityZoneId); ps.setString(15, user.getSalt()); ps.setTimestamp(16, getPasswordLastModifiedTimestamp(t)); ps.setNull(17, Types.BIGINT); ps.setNull(18, Types.BIGINT); ps.setString(19, user.getPassword()); } }); } catch (DuplicateKeyException e) { ScimUser existingUser = query("userName eq \"" + user.getUserName() + "\" and origin eq \"" + (hasText(user.getOrigin()) ? user.getOrigin() : OriginKeys.UAA) + "\"").get(0); Map<String, Object> userDetails = new HashMap<>(); userDetails.put("active", existingUser.isActive()); userDetails.put("verified", existingUser.isVerified()); userDetails.put("user_id", existingUser.getId()); throw new ScimResourceAlreadyExistsException("Username already in use: " + existingUser.getUserName(), userDetails); } return retrieve(id); }
From source file:com.flexive.core.Database.java
/** * Store a FxString in a translation table that only consists of n translation columns * * @param string string to be stored * @param con existing connection * @param table storage table//w ww . j ava 2 s . c o m * @param dataColumn names of the data columns * @param idColumn name of the id column * @param id id of the given string * @throws SQLException if a database error occured */ public static void storeFxString(FxString[] string, Connection con, String table, String[] dataColumn, String idColumn, long id) throws SQLException { PreparedStatement ps = null; if (string.length != dataColumn.length) throw new SQLException("string.length != dataColumn.length"); for (FxString param : string) { if (!param.isMultiLanguage()) { throw new FxInvalidParameterException("string", LOG, "ex.db.fxString.store.multilang", table) .asRuntimeException(); } } try { ps = con.prepareStatement("DELETE FROM " + table + ML + " WHERE " + idColumn + "=?"); ps.setLong(1, id); ps.execute(); //find languages to write List<Long> langs = new ArrayList<Long>(5); for (FxString curr : string) for (long currLang : curr.getTranslatedLanguages()) if (curr.translationExists(currLang)) { if (!langs.contains(currLang)) langs.add(currLang); } if (langs.size() > 0) { StringBuffer sql = new StringBuffer(300); sql.append("INSERT INTO ").append(table).append(ML + "(").append(idColumn).append(",LANG"); for (String dc : dataColumn) sql.append(',').append(dc).append(',').append(dc).append("_MLD"); sql.append(")VALUES(?,?"); //noinspection UnusedDeclaration for (FxString aString : string) sql.append(",?,?"); sql.append(')'); ps.close(); ps = con.prepareStatement(sql.toString()); boolean hasData; for (long lang : langs) { hasData = false; ps.setLong(1, id); ps.setInt(2, (int) lang); for (int i = 0; i < string.length; i++) { if (FxString.EMPTY.equals(string[i].getTranslation(lang))) { ps.setNull(3 + i * 2, java.sql.Types.VARCHAR); ps.setBoolean(3 + 1 + i * 2, false); } else { ps.setString(3 + i * 2, string[i].getTranslation(lang)); //get translation or empty string ps.setBoolean(3 + 1 + i * 2, string[i].isDefaultLanguage(lang)); hasData = true; } } if (hasData) ps.executeUpdate(); } } } finally { if (ps != null) ps.close(); } }
From source file:org.ojbc.adapters.analyticsstaging.custody.dao.AnalyticalDatastoreDAOImpl.java
@Override public void saveCustodyStatusChangeCharges(List<CustodyStatusChangeCharge> custodyStatusChangeCharges) { log.info("Inserting row into CustodyStatusChangeCharge table: " + custodyStatusChangeCharges); final String sqlString = "INSERT INTO CustodyStatusChangeCharge (CustodyStatusChangeArrestID, ChargeCode, AgencyID, " + "BondAmount, BondTypeID, ChargeClassTypeId, BondStatusTypeId, ChargeJurisdictionTypeId, " + "ChargeDisposition ) " + "values (?,?,?,?,?,?,?,?,?)"; jdbcTemplate.batchUpdate(sqlString, new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { CustodyStatusChangeCharge custodyStatusChangeCharge = custodyStatusChangeCharges.get(i); ps.setInt(1, custodyStatusChangeCharge.getCustodyStatusChangeArrestId()); setPreparedStatementVariable(custodyStatusChangeCharge.getChargeCode(), ps, 2); setPreparedStatementVariable(custodyStatusChangeCharge.getAgencyId(), ps, 3); setPreparedStatementVariable(custodyStatusChangeCharge.getBondAmount(), ps, 4); if (custodyStatusChangeCharge.getBondType() != null) { setPreparedStatementVariable(custodyStatusChangeCharge.getBondType().getKey(), ps, 5); } else { ps.setNull(5, java.sql.Types.NULL); }// www .j a v a 2 s . c o m setPreparedStatementVariable(custodyStatusChangeCharge.getChargeClassTypeId(), ps, 6); setPreparedStatementVariable(custodyStatusChangeCharge.getBondStatusTypeId(), ps, 7); setPreparedStatementVariable(custodyStatusChangeCharge.getChargeJurisdictionTypeId(), ps, 8); setPreparedStatementVariable(custodyStatusChangeCharge.getChargeDisposition(), ps, 9); } public int getBatchSize() { return custodyStatusChangeCharges.size(); } }); }