List of usage examples for java.sql PreparedStatement setBoolean
void setBoolean(int parameterIndex, boolean x) throws SQLException;
boolean value. 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 {// ww w .ja v a 2 s. com ps.setObject(j, value); } } else { ps.setNull(j, Types.NULL); } }
From source file:org.springframework.jdbc.core.StatementCreatorUtils.java
private static void setValue(PreparedStatement ps, int paramIndex, int sqlType, @Nullable String typeName, @Nullable Integer scale, Object inValue) throws SQLException { if (inValue instanceof SqlTypeValue) { ((SqlTypeValue) inValue).setTypeValue(ps, paramIndex, sqlType, typeName); } else if (inValue instanceof SqlValue) { ((SqlValue) inValue).setValue(ps, paramIndex); } else if (sqlType == Types.VARCHAR || sqlType == Types.LONGVARCHAR) { ps.setString(paramIndex, inValue.toString()); } else if (sqlType == Types.NVARCHAR || sqlType == Types.LONGNVARCHAR) { ps.setNString(paramIndex, inValue.toString()); } else if ((sqlType == Types.CLOB || sqlType == Types.NCLOB) && isStringValue(inValue.getClass())) { String strVal = inValue.toString(); if (strVal.length() > 4000) { // Necessary for older Oracle drivers, in particular when running against an Oracle 10 database. // Should also work fine against other drivers/databases since it uses standard JDBC 4.0 API. if (sqlType == Types.NCLOB) { ps.setNClob(paramIndex, new StringReader(strVal), strVal.length()); } else { ps.setClob(paramIndex, new StringReader(strVal), strVal.length()); }/*from ww w. j a v a 2 s. c o m*/ return; } else { // Fallback: setString or setNString binding if (sqlType == Types.NCLOB) { ps.setNString(paramIndex, strVal); } else { ps.setString(paramIndex, strVal); } } } else if (sqlType == Types.DECIMAL || sqlType == Types.NUMERIC) { if (inValue instanceof BigDecimal) { ps.setBigDecimal(paramIndex, (BigDecimal) inValue); } else if (scale != null) { ps.setObject(paramIndex, inValue, sqlType, scale); } else { ps.setObject(paramIndex, inValue, sqlType); } } else if (sqlType == Types.BOOLEAN) { if (inValue instanceof Boolean) { ps.setBoolean(paramIndex, (Boolean) inValue); } else { ps.setObject(paramIndex, inValue, Types.BOOLEAN); } } else if (sqlType == Types.DATE) { if (inValue instanceof java.util.Date) { if (inValue instanceof java.sql.Date) { ps.setDate(paramIndex, (java.sql.Date) inValue); } else { ps.setDate(paramIndex, new java.sql.Date(((java.util.Date) inValue).getTime())); } } else if (inValue instanceof Calendar) { Calendar cal = (Calendar) inValue; ps.setDate(paramIndex, new java.sql.Date(cal.getTime().getTime()), cal); } else { ps.setObject(paramIndex, inValue, Types.DATE); } } else if (sqlType == Types.TIME) { if (inValue instanceof java.util.Date) { if (inValue instanceof java.sql.Time) { ps.setTime(paramIndex, (java.sql.Time) inValue); } else { ps.setTime(paramIndex, new java.sql.Time(((java.util.Date) inValue).getTime())); } } else if (inValue instanceof Calendar) { Calendar cal = (Calendar) inValue; ps.setTime(paramIndex, new java.sql.Time(cal.getTime().getTime()), cal); } else { ps.setObject(paramIndex, inValue, Types.TIME); } } else if (sqlType == Types.TIMESTAMP) { if (inValue instanceof java.util.Date) { if (inValue instanceof java.sql.Timestamp) { ps.setTimestamp(paramIndex, (java.sql.Timestamp) inValue); } else { ps.setTimestamp(paramIndex, new java.sql.Timestamp(((java.util.Date) inValue).getTime())); } } else if (inValue instanceof Calendar) { Calendar cal = (Calendar) inValue; ps.setTimestamp(paramIndex, new java.sql.Timestamp(cal.getTime().getTime()), cal); } else { ps.setObject(paramIndex, inValue, Types.TIMESTAMP); } } else if (sqlType == SqlTypeValue.TYPE_UNKNOWN || (sqlType == Types.OTHER && "Oracle".equals(ps.getConnection().getMetaData().getDatabaseProductName()))) { if (isStringValue(inValue.getClass())) { ps.setString(paramIndex, inValue.toString()); } else if (isDateValue(inValue.getClass())) { ps.setTimestamp(paramIndex, new java.sql.Timestamp(((java.util.Date) inValue).getTime())); } else if (inValue instanceof Calendar) { Calendar cal = (Calendar) inValue; ps.setTimestamp(paramIndex, new java.sql.Timestamp(cal.getTime().getTime()), cal); } else { // Fall back to generic setObject call without SQL type specified. ps.setObject(paramIndex, inValue); } } else { // Fall back to generic setObject call with SQL type specified. ps.setObject(paramIndex, inValue, sqlType); } }
From source file:org.bytesoft.openjtcc.supports.logger.DbTransactionLoggerImpl.java
private Map<XidImpl, TerminatorArchive> loadTerminator(Connection connection) { Map<XidImpl, TerminatorArchive> transactionMap = new HashMap<XidImpl, TerminatorArchive>(); PreparedStatement stmt = null; ResultSet rs = null;/*from w ww.j a v a2 s. c o m*/ try { StringBuilder ber = new StringBuilder(); ber.append("select s.global_tx_id, s.to_application, s.to_endpoint"); ber.append(", s.prepared, s.committed, s.rolledback, s.cleanup "); ber.append("from tcc_terminator s "); ber.append("left join tcc_transaction t on ("); ber.append(" t.application = s.application "); ber.append(" and t.endpoint = s.endpoint "); ber.append(" and t.global_tx_id = s.global_tx_id "); ber.append(") where s.application = ? and s.endpoint = ? and t.deleted = ?"); stmt = connection.prepareStatement(ber.toString()); stmt.setString(1, this.instanceKey.getApplication()); stmt.setString(2, this.instanceKey.getEndpoint()); stmt.setBoolean(3, false); rs = stmt.executeQuery(); while (rs.next()) { TerminatorArchive holder = new TerminatorArchive(); String towardsApplication = rs.getString("to_application"); String towardsEndpoint = rs.getString("to_endpoint"); String globalTransactionId = rs.getString("global_tx_id"); boolean prepared = rs.getBoolean("prepared"); boolean committed = rs.getBoolean("committed"); boolean rolledback = rs.getBoolean("rolledback"); boolean cleanup = rs.getBoolean("cleanup"); byte[] globalBytes = ByteUtils.stringToByteArray(globalTransactionId); XidImpl globalXid = this.xidFactory.createGlobalXid(globalBytes); holder.prepared = prepared; holder.committed = committed; holder.rolledback = rolledback; holder.cleanup = cleanup; TerminatorInfo info = new TerminatorInfo(); info.setApplication(towardsApplication); info.setEndpoint(towardsEndpoint); info.setBranchXid(globalXid); RemoteTerminator terminator = this.terminatorMarshaller.unmarshallTerminator(info); holder.terminator = terminator; transactionMap.put(globalXid, holder); } } catch (Exception ex) { ex.printStackTrace(); } finally { closeResultSet(rs); closeStatement(stmt); } return transactionMap; }
From source file:fll.web.admin.UploadSubjectiveData.java
@SuppressFBWarnings(value = {
"SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING" }, justification = "columns are dynamic")
private static void saveCategoryData(final int currentTournament, final Connection connection,
final Element scoreCategoryElement, final String categoryName, final ScoreCategory categoryElement)
throws SQLException, ParseException {
final List<AbstractGoal> goalDescriptions = categoryElement.getGoals();
PreparedStatement insertPrep = null;
PreparedStatement updatePrep = null;
try {/*from w w w .ja va 2s. c om*/
// prepare statements for update and insert
final StringBuffer updateStmt = new StringBuffer();
final StringBuffer insertSQLColumns = new StringBuffer();
insertSQLColumns.append("INSERT INTO " + categoryName + " (TeamNumber, Tournament, Judge, NoShow");
final StringBuffer insertSQLValues = new StringBuffer();
insertSQLValues.append(") VALUES ( ?, ?, ?, ?");
updateStmt.append("UPDATE " + categoryName + " SET NoShow = ? ");
final int numGoals = goalDescriptions.size();
for (final AbstractGoal goalDescription : goalDescriptions) {
insertSQLColumns.append(", " + goalDescription.getName());
insertSQLValues.append(", ?");
updateStmt.append(", " + goalDescription.getName() + " = ?");
}
updateStmt.append(" WHERE TeamNumber = ? AND Tournament = ? AND Judge = ?");
updatePrep = connection.prepareStatement(updateStmt.toString());
insertPrep = connection
.prepareStatement(insertSQLColumns.toString() + insertSQLValues.toString() + ")");
// initialze the tournament
insertPrep.setInt(2, currentTournament);
updatePrep.setInt(numGoals + 3, currentTournament);
for (final Element scoreElement : new NodelistElementCollectionAdapter(
scoreCategoryElement.getElementsByTagName("score"))) {
if (scoreElement.hasAttribute("modified")
&& "true".equalsIgnoreCase(scoreElement.getAttribute("modified"))) {
final int teamNumber = Utilities.NUMBER_FORMAT_INSTANCE
.parse(scoreElement.getAttribute("teamNumber")).intValue();
if (LOGGER.isTraceEnabled()) {
LOGGER.trace("Saving score data for team: " + teamNumber);
}
final String judgeId = scoreElement.getAttribute("judge");
final boolean noShow = Boolean.parseBoolean(scoreElement.getAttribute("NoShow"));
updatePrep.setBoolean(1, noShow);
insertPrep.setBoolean(4, noShow);
insertPrep.setInt(1, teamNumber);
updatePrep.setInt(numGoals + 2, teamNumber);
insertPrep.setString(3, judgeId);
updatePrep.setString(numGoals + 4, judgeId);
int goalIndex = 0;
for (final AbstractGoal goalDescription : goalDescriptions) {
final String goalName = goalDescription.getName();
final Element subscoreElement = SubjectiveUtils.getSubscoreElement(scoreElement, goalName);
if (null == subscoreElement) {
// no subscore element, no show or deleted
insertPrep.setNull(goalIndex + 5, Types.DOUBLE);
updatePrep.setNull(goalIndex + 2, Types.DOUBLE);
} else {
final String value = subscoreElement.getAttribute("value");
if (!value.trim().isEmpty()) {
insertPrep.setString(goalIndex + 5, value.trim());
updatePrep.setString(goalIndex + 2, value.trim());
} else {
insertPrep.setNull(goalIndex + 5, Types.DOUBLE);
updatePrep.setNull(goalIndex + 2, Types.DOUBLE);
}
}
++goalIndex;
} // end for
// attempt the update first
final int modifiedRows = updatePrep.executeUpdate();
if (modifiedRows < 1) {
// do insert if nothing was updated
insertPrep.executeUpdate();
}
}
}
} finally {
SQLFunctions.close(insertPrep);
SQLFunctions.close(updatePrep);
}
}
From source file:org.bytesoft.openjtcc.supports.logger.DbTransactionLoggerImpl.java
private Map<XidImpl, CompensableArchive> loadNativeService(Connection connection) { Map<XidImpl, CompensableArchive> serviceMap = new HashMap<XidImpl, CompensableArchive>(); PreparedStatement stmt = null; ResultSet rs = null;/*from ww w . j av a 2s. c om*/ try { StringBuilder ber = new StringBuilder(); ber.append("select s.global_tx_id, s.branch_qualifier, s.coordinator, s.bean_name, s.variable"); ber.append(", s.try_committed, s.confirmed, s.cancelled, s.committed, s.rolledback "); ber.append("from tcc_compensable s "); ber.append("left join tcc_transaction t on ("); ber.append(" t.application = s.application "); ber.append(" and t.endpoint = s.endpoint "); ber.append(" and t.global_tx_id = s.global_tx_id "); ber.append(") where s.application = ? and s.endpoint = ? and t.deleted = ?"); stmt = connection.prepareStatement(ber.toString()); stmt.setString(1, this.instanceKey.getApplication()); stmt.setString(2, this.instanceKey.getEndpoint()); stmt.setBoolean(3, false); rs = stmt.executeQuery(); while (rs.next()) { String globalTransactionId = rs.getString("global_tx_id"); String branchQualifier = rs.getString("branch_qualifier"); boolean coordinator = rs.getBoolean("coordinator"); String beanName = rs.getString("bean_name"); CompensableArchive holder = new CompensableArchive(); holder.launchSvc = coordinator; byte[] globalBytes = ByteUtils.stringToByteArray(globalTransactionId); XidImpl globalXid = this.xidFactory.createGlobalXid(globalBytes); if (coordinator) { holder.branchXid = globalXid; } else { byte[] branchBytes = ByteUtils.stringToByteArray(branchQualifier); XidImpl branchXid = this.xidFactory.createBranchXid(globalXid, branchBytes); holder.branchXid = branchXid; } boolean tryCommitted = rs.getBoolean("try_committed"); boolean confirmed = rs.getBoolean("confirmed"); boolean cancelled = rs.getBoolean("cancelled"); boolean committed = rs.getBoolean("committed"); boolean rolledback = rs.getBoolean("rolledback"); holder.tryCommitted = tryCommitted; holder.confirmed = confirmed; holder.cancelled = cancelled; holder.committed = committed; holder.rolledback = rolledback; CompensableInfo info = new CompensableInfo(); info.setIdentifier(beanName); Compensable<Serializable> service = this.compensableMarshaller.unmarshallCompensable(info); holder.service = service; Blob blob = rs.getBlob("variable"); Serializable variable = null; if (blob != null) { InputStream input = blob.getBinaryStream(); if (input != null) { byte[] bytes = this.streamToByteArray(input); try { variable = (Serializable) this.serializer.deserialize(bytes); } catch (IOException e) { // ignore } } } holder.variable = variable; serviceMap.put(globalXid, holder); } } catch (Exception ex) { ex.printStackTrace(); } finally { closeResultSet(rs); closeStatement(stmt); } return serviceMap; }
From source file:com.flexive.core.storage.genericSQL.GenericBinarySQLStorage.java
/** * {@inheritDoc}//from w w w. j a v a2s. co m */ @Override public OutputStream receiveTransitBinary(int divisionId, String handle, String mimeType, long expectedSize, long ttl) throws SQLException, IOException { try { if (EJBLookup.getConfigurationEngine().get(SystemParameters.BINARY_TRANSIT_DB)) return new GenericBinarySQLOutputStream(divisionId, handle, mimeType, expectedSize, ttl); else { Connection con; PreparedStatement ps = null; con = Database.getNonTXDataSource(divisionId).getConnection(); try { //create a dummy entry ps = con.prepareStatement("INSERT INTO " + DatabaseConst.TBL_BINARY_TRANSIT + " (BKEY,MIMETYPE,FBLOB,TFER_DONE,EXPIRE) VALUES(?,?,NULL,?,?)"); ps.setString(1, handle); ps.setString(2, mimeType); ps.setBoolean(3, true); ps.setLong(4, System.currentTimeMillis() + ttl); ps.executeUpdate(); } finally { Database.closeObjects(GenericBinarySQLStorage.class, con, ps); } return new FileOutputStream(FxBinaryUtils.createTransitFile(divisionId, handle, ttl)); } } catch (FxApplicationException e) { throw e.asRuntimeException(); } }
From source file:edu.uga.cs.fluxbuster.db.PostgresDBInterface.java
/** * @see edu.uga.cs.fluxbuster.db.DBInterface#storeClusterClasses(java.util.Date, java.util.Map, boolean) *//* w w w .j a va 2s. c om*/ @Override public void storeClusterClasses(Date logdate, Map<ClusterClass, List<StoredDomainCluster>> clusterClasses, boolean validated) { String logDateTable = dateFormatTable.format(logdate); Connection con = null; PreparedStatement clusterClassesInsertStmt = null; try { con = this.getConnection(); clusterClassesInsertStmt = con.prepareStatement( "INSERT INTO cluster_classes_" + logDateTable + " VALUES (?, 'SIE', ?, ?, ?)"); for (ClusterClass clusclass : clusterClasses.keySet()) { for (StoredDomainCluster cluster : clusterClasses.get(clusclass)) { clusterClassesInsertStmt.setInt(1, cluster.getClusterId()); clusterClassesInsertStmt.setDate(2, new java.sql.Date(logdate.getTime())); clusterClassesInsertStmt.setString(3, clusclass.toString()); clusterClassesInsertStmt.setBoolean(4, validated); this.executePreparedStatementNoResult(con, clusterClassesInsertStmt); } } } catch (SQLException e) { if (log.isErrorEnabled()) { log.error("Error storing cluster classes.", e); } } finally { try { if (clusterClassesInsertStmt != null && !clusterClassesInsertStmt.isClosed()) { clusterClassesInsertStmt.close(); } } catch (SQLException e) { if (log.isErrorEnabled()) { log.error("e"); } } try { if (con != null && !con.isClosed()) { con.close(); } } catch (SQLException e) { if (log.isErrorEnabled()) { log.error("e"); } } } }
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();// w w w . j a v a 2 s.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.GenericTreeStorageSpreaded.java
/** * Helper function to create a new node. * * @param con an open and valid connection * @param seq reference to a sequencer * @param ce reference to the content engine * @param mode Live or Edit mode * @param parentNodeId the parent node (1=root) * @param name the name of the new node (only informative value) * @param label label for Caption property (only used if new reference is created) * @param position the position within the childs (0 based, Integer.MAX_VALUE may be used to * append to the end) * @param reference a reference to an existing content (must exist!) * @param data the optional data * @param nodeId the id to use or create a new one if < 0 * @param activateContent change the step of contents that have no live step to live in the max version? * @return the used or created node id//from w w w . j ava 2s. c o m * @throws FxTreeException if the function fails */ private long _createNode(Connection con, SequencerEngine seq, ContentEngine ce, FxTreeMode mode, long parentNodeId, String name, FxString label, int position, FxPK reference, String data, long nodeId, boolean activateContent) throws FxApplicationException { // acquire exclusive lock for parent node acquireLocksForUpdate(con, mode, Arrays.asList(parentNodeId)); // makeSpace(con, seq/*irrelevant*/, mode, parentNodeId, position/*irrelevant*/, 1); FxTreeNodeInfoSpreaded parentNode = (FxTreeNodeInfoSpreaded) getTreeNodeInfo(con, mode, parentNodeId); BigInteger boundaries[] = getBoundaries(con, parentNode, position); BigInteger leftBoundary = boundaries[0]; //== left border BigInteger rightBoundary = boundaries[1]; //== right border // Node has to be inserted between the left and right boundary and needs 2 slots for its left and right border BigInteger spacing = rightBoundary.subtract(leftBoundary).subtract(TWO); // Compute spacing for left,inner and right part spacing = spacing.divide(THREE); // We need at least 2 open slots (for the left and right boundary of the new node) //if the spacing is <= 0 we need more space if (spacing.compareTo(BigInteger.ZERO) <= 0/*less than*/) { throw new FxTreeException("ex.tree.create.noSpace", parentNodeId); } // try to use space more efficiently for flat structures, otherwise the first node of a folder // will get a third of the subtree space, the second one ninth, and so on. // Maxspacing indicates the number of nodes (*2) we expect to put in this node before space reorg spacing = spacing.compareTo(DEFAULT_NODE_SPACING) > 0 ? DEFAULT_NODE_SPACING : spacing; // final BigInteger left = leftBoundary.add(spacing).add(BigInteger.ONE); // don't add gap to left boundary (doesn't seem to have any benefits since that space is lost // unless the tree is reorganized anyway final BigInteger left = leftBoundary.add(BigInteger.ONE); final BigInteger right = left.add(spacing).add(BigInteger.ONE); NodeCreateInfo nci = getNodeCreateInfo(mode, seq, ce, nodeId, name, label, reference, activateContent); // Create the node PreparedStatement ps = null; try { ps = con.prepareStatement("INSERT INTO " + getTable(mode) + " (ID,PARENT,DEPTH,DIRTY,REF,LFT,RGT," + "CHILDCOUNT,NAME,MODIFIED_AT,TEMPLATE) VALUES " + "(" + nci.id + "," + parentNodeId + "," + (parentNode.getDepth() + 1) + ",?," + nci.reference.getId() + ",?,?,0,?," + StorageManager.getTimestampFunction() + ",?)"); ps.setBoolean(1, mode != FxTreeMode.Live); setNodeBounds(ps, 2, left); setNodeBounds(ps, 3, right); ps.setString(4, FxFormatUtils.escapeTreePath(nci.name)); if (StringUtils.isEmpty(data)) { ps.setNull(5, java.sql.Types.VARCHAR); } else { ps.setString(6, data); } ps.executeUpdate(); ps.close(); //update the parents childcount ps = con.prepareStatement( "UPDATE " + getTable(mode) + " SET CHILDCOUNT=CHILDCOUNT+1 WHERE ID=" + parentNodeId); ps.executeUpdate(); } catch (SQLException e) { throw new FxTreeException(LOG, e, "ex.db.sqlError", e.getMessage()); } finally { try { if (ps != null) ps.close(); } catch (Throwable t) { /*ignore*/ } } return nci.id; }
From source file:org.nuxeo.ecm.core.storage.sql.db.dialect.DialectPostgreSQL.java
@Override public void setToPreparedStatement(PreparedStatement ps, int index, Serializable value, Column column) throws SQLException { switch (column.getJdbcType()) { case Types.VARCHAR: case Types.CLOB: String v;/*from w w w .ja va 2s . com*/ if (column.getType() == ColumnType.BLOBID) { v = ((Binary) value).getDigest(); } else { v = (String) value; } ps.setString(index, v); break; case Types.BIT: ps.setBoolean(index, ((Boolean) value).booleanValue()); return; case Types.SMALLINT: ps.setInt(index, ((Long) value).intValue()); return; case Types.INTEGER: case Types.BIGINT: ps.setLong(index, ((Long) value).longValue()); return; case Types.DOUBLE: ps.setDouble(index, ((Double) value).doubleValue()); return; case Types.TIMESTAMP: Calendar cal = (Calendar) value; Timestamp ts = new Timestamp(cal.getTimeInMillis()); ps.setTimestamp(index, ts, cal); // cal passed for timezone return; case Types.ARRAY: Array array = createArrayOf(Types.VARCHAR, (Object[]) value, ps.getConnection()); ps.setArray(index, array); return; case Types.OTHER: if (column.getType() == ColumnType.FTSTORED) { ps.setString(index, (String) value); return; } throw new SQLException("Unhandled type: " + column.getType()); default: throw new SQLException("Unhandled JDBC type: " + column.getJdbcType()); } }