List of usage examples for java.sql PreparedStatement setDouble
void setDouble(int parameterIndex, double x) throws SQLException;
double value. From source file:org.globus.workspace.persistence.PersistenceAdapterImpl.java
public void add(InstanceResource resource) throws WorkspaceDatabaseException { if (resource == null) { throw new WorkspaceDatabaseException("resource is null"); }// w ww .j a va2 s .c o m final int id = resource.getID(); if (id < 0) { throw new WorkspaceDatabaseException("id is less than zero"); } if (this.dbTrace) { logger.trace("add(): " + Lager.id(id) + ", WorkspaceResource = " + resource); } final byte[] faultBytes; try { faultBytes = ErrorUtil.toByteArray(resource.getStateThrowable()); } catch (IOException e) { throw new WorkspaceDatabaseException(e); } Connection c = null; PreparedStatement pstmt = null; PreparedStatement[] pstmts = null; try { c = getConnection(); c.setAutoCommit(false); pstmt = c.prepareStatement(SQL_INSERT_RESOURCE); pstmt.setInt(1, id); pstmt.setString(2, resource.getName()); pstmt.setInt(3, resource.getState()); pstmt.setInt(4, resource.getTargetState()); if (resource.getTerminationTime() != null) { pstmt.setObject(5, new Long(resource.getTerminationTime().getTimeInMillis())); } else { pstmt.setInt(5, 0); } if (resource.isOpsEnabled()) { pstmt.setInt(6, 1); } else { pstmt.setInt(6, 0); } if (resource.getCreatorID() != null) { pstmt.setString(7, resource.getCreatorID()); } else { pstmt.setNull(7, Types.VARCHAR); } if (resource.getStartTime() != null) { pstmt.setObject(8, new Long(resource.getStartTime().getTimeInMillis())); } else { pstmt.setInt(8, 0); } if (resource.isVMMaccessOK()) { pstmt.setInt(9, 1); } else { pstmt.setInt(9, 0); } if (resource.getEnsembleId() != null) { pstmt.setString(10, resource.getEnsembleId()); } else { pstmt.setNull(10, Types.VARCHAR); } if (resource.getGroupId() != null) { pstmt.setString(11, resource.getGroupId()); } else { pstmt.setNull(11, Types.VARCHAR); } pstmt.setInt(12, resource.getGroupSize()); if (resource.isLastInGroup()) { pstmt.setInt(13, 1); } else { pstmt.setInt(13, 0); } pstmt.setInt(14, resource.getLaunchIndex()); if (faultBytes != null) { pstmt.setObject(15, faultBytes, Types.BLOB); } else { pstmt.setNull(15, Types.BLOB); } pstmt.setString(16, resource.getClientToken()); pstmt.setDouble(17, resource.getChargeRatio()); if (this.dbTrace) { logger.trace("creating WorkspaceResource db " + "entry for " + Lager.id(id)); } pstmt.executeUpdate(); if (resource instanceof VMPersistence) { pstmts = VirtualMachinePersistenceUtil.getInsertVM(resource, id, c); if (this.dbTrace) { logger.trace("creating VirtualMachine db " + "entry for " + Lager.id(id) + ": " + pstmts.length + " inserts"); } for (int i = 0; i < pstmts.length; i++) { pstmts[i].executeUpdate(); } } c.commit(); } catch (SQLException e) { logger.error("", e); throw new WorkspaceDatabaseException(e); } catch (ManageException e) { logger.error("", e); throw new WorkspaceDatabaseException(e); } finally { try { if (pstmt != null) { pstmt.close(); } if (pstmts != null) { for (int i = 0; i < pstmts.length; i++) { pstmts[i].close(); } } if (c != null) { c.setAutoCommit(true); returnConnection(c); } } catch (SQLException sql) { logger.error("SQLException in finally cleanup", sql); } } }
From source file:org.apache.cocoon.util.JDBCTypeConversions.java
/** * Set the Statement column so that the results are mapped correctly. * * @param statement the prepared statement * @param position the position of the column * @param value the value of the column// www . j a v a2 s . c o m */ public static void setColumn(PreparedStatement statement, int position, Object value, Integer typeObject) throws Exception { if (value instanceof String) { value = ((String) value).trim(); } if (typeObject == null) { throw new SQLException("Can't set column because the type is unrecognized"); } if (value == null) { /** If the value is null, set the column value null and return **/ statement.setNull(position, typeObject.intValue()); return; } if ("".equals(value)) { switch (typeObject.intValue()) { case Types.CHAR: case Types.CLOB: case Types.VARCHAR: /** If the value is an empty string and the column is a string type, we can continue **/ break; default: /** If the value is an empty string and the column is something else, we treat it as a null value **/ statement.setNull(position, typeObject.intValue()); return; } } File file = null; int length = -1; InputStream asciiStream = null; //System.out.println("========================================================================"); //System.out.println("JDBCTypeConversions: setting type "+typeObject.intValue()); switch (typeObject.intValue()) { case Types.CLOB: //System.out.println("CLOB"); Clob clob = null; if (value instanceof Clob) { clob = (Clob) value; } else if (value instanceof File) { File asciiFile = (File) value; asciiStream = new BufferedInputStream(new FileInputStream(asciiFile)); length = (int) asciiFile.length(); clob = new ClobHelper(asciiStream, length); } else if (value instanceof Part) { Part anyFile = (Part) value; asciiStream = new BufferedInputStream(anyFile.getInputStream()); length = anyFile.getSize(); clob = new ClobHelper(asciiStream, length); } else if (value instanceof JDBCxlobHelper) { asciiStream = ((JDBCxlobHelper) value).inputStream; length = ((JDBCxlobHelper) value).length; clob = new ClobHelper(asciiStream, length); } else if (value instanceof Source) { asciiStream = ((Source) value).getInputStream(); length = (int) ((Source) value).getContentLength(); clob = new ClobHelper(asciiStream, length); } else { String asciiText = value.toString(); asciiStream = new ByteArrayInputStream(asciiText.getBytes()); length = asciiText.length(); clob = new ClobHelper(asciiStream, length); } statement.setClob(position, clob); break; case Types.CHAR: // simple large object, e.g. Informix's TEXT //System.out.println("CHAR"); if (value instanceof File) { File asciiFile = (File) value; asciiStream = new BufferedInputStream(new FileInputStream(asciiFile)); length = (int) asciiFile.length(); } else if (value instanceof JDBCxlobHelper) { asciiStream = ((JDBCxlobHelper) value).inputStream; length = ((JDBCxlobHelper) value).length; } else if (value instanceof Source) { asciiStream = ((Source) value).getInputStream(); length = (int) ((Source) value).getContentLength(); } else if (value instanceof Part) { Part anyFile = (Part) value; asciiStream = new BufferedInputStream(anyFile.getInputStream()); length = anyFile.getSize(); clob = new ClobHelper(asciiStream, length); } else { String asciiText = value.toString(); asciiStream = new BufferedInputStream(new ByteArrayInputStream(asciiText.getBytes())); length = asciiText.length(); } statement.setAsciiStream(position, asciiStream, length); break; case Types.BIGINT: //System.out.println("BIGINT"); BigDecimal bd = null; if (value instanceof BigDecimal) { bd = (BigDecimal) value; } else if (value instanceof Number) { bd = BigDecimal.valueOf(((Number) value).longValue()); } else { bd = new BigDecimal(value.toString()); } statement.setBigDecimal(position, bd); break; case Types.TINYINT: //System.out.println("TINYINT"); Byte b = null; if (value instanceof Byte) { b = (Byte) value; } else if (value instanceof Number) { b = new Byte(((Number) value).byteValue()); } else { b = new Byte(value.toString()); } statement.setByte(position, b.byteValue()); break; case Types.DATE: //System.out.println("DATE"); Date d = null; if (value instanceof Date) { d = (Date) value; } else if (value instanceof java.util.Date) { d = new Date(((java.util.Date) value).getTime()); } else if (value instanceof Calendar) { d = new Date(((Calendar) value).getTime().getTime()); } else { d = Date.valueOf(value.toString()); } statement.setDate(position, d); break; case Types.DOUBLE: //System.out.println("DOUBLE"); double db; if (value instanceof Number) { db = (((Number) value).doubleValue()); } else { db = Double.parseDouble(value.toString()); } statement.setDouble(position, db); break; case Types.FLOAT: //System.out.println("FLOAT"); float f; if (value instanceof Number) { f = (((Number) value).floatValue()); } else { f = Float.parseFloat(value.toString()); } statement.setFloat(position, f); break; case Types.NUMERIC: //System.out.println("NUMERIC"); long l; if (value instanceof Number) { l = (((Number) value).longValue()); } else { l = Long.parseLong(value.toString()); } statement.setLong(position, l); break; case Types.SMALLINT: //System.out.println("SMALLINT"); Short s = null; if (value instanceof Short) { s = (Short) value; } else if (value instanceof Number) { s = new Short(((Number) value).shortValue()); } else { s = new Short(value.toString()); } statement.setShort(position, s.shortValue()); break; case Types.TIME: //System.out.println("TIME"); Time t = null; if (value instanceof Time) { t = (Time) value; } else if (value instanceof java.util.Date) { t = new Time(((java.util.Date) value).getTime()); } else { t = Time.valueOf(value.toString()); } statement.setTime(position, t); break; case Types.TIMESTAMP: //System.out.println("TIMESTAMP"); Timestamp ts = null; if (value instanceof Time) { ts = (Timestamp) value; } else if (value instanceof java.util.Date) { ts = new Timestamp(((java.util.Date) value).getTime()); } else { ts = Timestamp.valueOf(value.toString()); } statement.setTimestamp(position, ts); break; case Types.ARRAY: //System.out.println("ARRAY"); statement.setArray(position, (Array) value); // no way to convert string to array break; case Types.STRUCT: //System.out.println("STRUCT"); case Types.OTHER: //System.out.println("OTHER"); statement.setObject(position, value); break; case Types.LONGVARBINARY: //System.out.println("LONGVARBINARY"); statement.setTimestamp(position, new Timestamp((new java.util.Date()).getTime())); break; case Types.VARCHAR: //System.out.println("VARCHAR"); statement.setString(position, value.toString()); break; case Types.BLOB: //System.out.println("BLOB"); if (value instanceof JDBCxlobHelper) { statement.setBinaryStream(position, ((JDBCxlobHelper) value).inputStream, ((JDBCxlobHelper) value).length); } else if (value instanceof Source) { statement.setBinaryStream(position, ((Source) value).getInputStream(), (int) ((Source) value).getContentLength()); } else { Blob blob = null; if (value instanceof Blob) { blob = (Blob) value; } else if (value instanceof File) { file = (File) value; blob = new BlobHelper(new FileInputStream(file), (int) file.length()); } else if (value instanceof String) { file = new File((String) value); blob = new BlobHelper(new FileInputStream(file), (int) file.length()); } else if (value instanceof Part) { Part anyFile = (Part) value; blob = new BlobHelper(new BufferedInputStream(anyFile.getInputStream()), anyFile.getSize()); } else { throw new SQLException("Invalid type for blob: " + value.getClass().getName()); } //InputStream input = new BufferedInputStream(new FileInputStream(file)); statement.setBlob(position, blob); } break; case Types.VARBINARY: //System.out.println("VARBINARY"); if (value instanceof JDBCxlobHelper) { statement.setBinaryStream(position, ((JDBCxlobHelper) value).inputStream, ((JDBCxlobHelper) value).length); } else if (value instanceof Source) { statement.setBinaryStream(position, ((Source) value).getInputStream(), (int) ((Source) value).getContentLength()); } else if (value instanceof Part) { statement.setBinaryStream(position, ((Part) value).getInputStream(), ((Part) value).getSize()); } else { if (value instanceof File) { file = (File) value; } else if (value instanceof String) { file = new File((String) value); } else { throw new SQLException("Invalid type for blob: " + value.getClass().getName()); } //InputStream input = new BufferedInputStream(new FileInputStream(file)); FileInputStream input = new FileInputStream(file); statement.setBinaryStream(position, input, (int) file.length()); } break; case Types.INTEGER: //System.out.println("INTEGER"); Integer i = null; if (value instanceof Integer) { i = (Integer) value; } else if (value instanceof Number) { i = new Integer(((Number) value).intValue()); } else { i = new Integer(value.toString()); } statement.setInt(position, i.intValue()); break; case Types.BIT: //System.out.println("BIT"); Boolean bo = null; if (value instanceof Boolean) { bo = (Boolean) value; } else if (value instanceof Number) { bo = BooleanUtils.toBooleanObject(((Number) value).intValue() == 1); } else { bo = BooleanUtils.toBooleanObject(value.toString()); } statement.setBoolean(position, bo.booleanValue()); break; default: //System.out.println("default"); throw new SQLException("Impossible exception - invalid type "); } //System.out.println("========================================================================"); }
From source file:fll.web.api.SubjectiveScoresServlet.java
@SuppressFBWarnings(value = {
"SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING" }, justification = "columns and category are dynamic")
@Override//w ww .ja v a2 s . co m
protected final void doPost(final HttpServletRequest request, final HttpServletResponse response)
throws IOException, ServletException {
int numModified = 0;
final ObjectMapper jsonMapper = new ObjectMapper();
final ServletContext application = getServletContext();
final ChallengeDescription challengeDescription = ApplicationAttributes
.getChallengeDescription(application);
Connection connection = null;
PreparedStatement deletePrep = null;
PreparedStatement noShowPrep = null;
PreparedStatement insertPrep = null;
try {
final DataSource datasource = ApplicationAttributes.getDataSource(application);
connection = datasource.getConnection();
final int currentTournament = Queries.getCurrentTournament(connection);
final StringWriter debugWriter = new StringWriter();
IOUtils.copy(request.getReader(), debugWriter);
if (LOGGER.isTraceEnabled()) {
LOGGER.trace("Read data: " + debugWriter.toString());
}
final Reader reader = new StringReader(debugWriter.toString());
final Map<String, Map<String, Map<Integer, SubjectiveScore>>> allScores = jsonMapper.readValue(reader,
ScoresTypeInfo.INSTANCE);
for (final Map.Entry<String, Map<String, Map<Integer, SubjectiveScore>>> catEntry : allScores
.entrySet()) {
final String category = catEntry.getKey();
final ScoreCategory categoryDescription = challengeDescription
.getSubjectiveCategoryByName(category);
deletePrep = connection.prepareStatement("DELETE FROM " + category //
+ " WHERE TeamNumber = ?" //
+ " AND Tournament = ?" //
+ " AND Judge = ?" //
);
deletePrep.setInt(2, currentTournament);
noShowPrep = connection.prepareStatement("INSERT INTO " + category //
+ "(TeamNumber, Tournament, Judge, NoShow) VALUES(?, ?, ?, ?)");
noShowPrep.setInt(2, currentTournament);
noShowPrep.setBoolean(4, true);
final int NUM_COLUMNS_BEFORE_GOALS = 6;
insertPrep = createInsertStatement(connection, categoryDescription);
insertPrep.setInt(2, currentTournament);
insertPrep.setBoolean(4, false);
for (final Map.Entry<String, Map<Integer, SubjectiveScore>> judgeEntry : catEntry.getValue()
.entrySet()) {
final String judgeId = judgeEntry.getKey();
deletePrep.setString(3, judgeId);
noShowPrep.setString(3, judgeId);
insertPrep.setString(3, judgeId);
for (final Map.Entry<Integer, SubjectiveScore> teamEntry : judgeEntry.getValue().entrySet()) {
final int teamNumber = teamEntry.getKey();
final SubjectiveScore score = teamEntry.getValue();
if (score.getModified()) {
deletePrep.setInt(1, teamNumber);
noShowPrep.setInt(1, teamNumber);
insertPrep.setInt(1, teamNumber);
insertPrep.setString(5, score.getNote());
++numModified;
if (score.getDeleted()) {
if (LOGGER.isTraceEnabled()) {
LOGGER.trace("Deleting team: " + teamNumber + " judge: " + judgeId
+ " category: " + category);
}
deletePrep.executeUpdate();
} else if (score.getNoShow()) {
if (LOGGER.isTraceEnabled()) {
LOGGER.trace("NoShow team: " + teamNumber + " judge: " + judgeId + " category: "
+ category);
}
deletePrep.executeUpdate();
noShowPrep.executeUpdate();
} else {
if (LOGGER.isTraceEnabled()) {
LOGGER.trace("scores for team: " + teamNumber + " judge: " + judgeId
+ " category: " + category);
}
int goalIndex = 0;
for (final AbstractGoal goalDescription : categoryDescription.getGoals()) {
if (!goalDescription.isComputed()) {
final String goalName = goalDescription.getName();
if (goalDescription.isEnumerated()) {
final String value = score.getEnumSubScores().get(goalName);
if (null == value) {
insertPrep.setNull(goalIndex + NUM_COLUMNS_BEFORE_GOALS,
Types.VARCHAR);
} else {
insertPrep.setString(goalIndex + NUM_COLUMNS_BEFORE_GOALS,
value.trim());
}
} else {
final Double value = score.getStandardSubScores().get(goalName);
if (null == value) {
insertPrep.setNull(goalIndex + NUM_COLUMNS_BEFORE_GOALS,
Types.DOUBLE);
} else {
insertPrep.setDouble(goalIndex + NUM_COLUMNS_BEFORE_GOALS, value);
}
}
++goalIndex;
} // not computed
} // end for
deletePrep.executeUpdate();
insertPrep.executeUpdate();
}
} // is modified
} // foreach team score
} // foreach judge
SQLFunctions.close(deletePrep);
deletePrep = null;
SQLFunctions.close(noShowPrep);
noShowPrep = null;
SQLFunctions.close(insertPrep);
insertPrep = null;
} // foreach category
UploadSubjectiveData.removeNullSubjectiveRows(connection, currentTournament, challengeDescription);
final Tournament tournament = Tournament.findTournamentByID(connection, currentTournament);
tournament.recordSubjectiveModified(connection);
final UploadResult result = new UploadResult(true, "Successfully uploaded scores", numModified);
response.reset();
response.setContentType("application/json");
final PrintWriter writer = response.getWriter();
jsonMapper.writeValue(writer, result);
} catch (final SQLException sqle) {
LOGGER.error("Error uploading scores", sqle);
final UploadResult result = new UploadResult(false, sqle.getMessage(), numModified);
response.reset();
response.setContentType("application/json");
final PrintWriter writer = response.getWriter();
jsonMapper.writeValue(writer, result);
} finally {
SQLFunctions.close(deletePrep);
SQLFunctions.close(noShowPrep);
SQLFunctions.close(insertPrep);
SQLFunctions.close(connection);
}
}
From source file:com.flexive.core.storage.genericSQL.GenericHierarchicalStorage.java
/** * Set a properties data for inserts or updates * * @param insert perform insert or update? * @param prop current property * @param allData all data of the instance (might be needed to buld references, etc.) * @param con an open and valid connection * @param data current property data * @param ps prepared statement for the data table * @param ft fulltext indexer * @param upperColumnPos position of the uppercase column (if present, else <code>-1</code>) * @param includeFullText add fulltext entries? Will be skipped for position only changes * @throws SQLException on errors * @throws FxUpdateException on errors * @throws FxDbException on errors * @throws FxNoAccessException for FxNoAccess values *//* w ww . j av a2s.c o m*/ private void setPropertyData(boolean insert, FxProperty prop, List<FxData> allData, Connection con, FxPropertyData data, PreparedStatement ps, FulltextIndexer ft, int upperColumnPos, boolean includeFullText) throws SQLException, FxUpdateException, FxDbException, FxNoAccessException { FxValue value = data.getValue(); if (value instanceof FxNoAccess) throw new FxNoAccessException("ex.content.value.noaccess"); if (value.isMultiLanguage() != ((FxPropertyAssignment) data.getAssignment()).isMultiLang()) { if (((FxPropertyAssignment) data.getAssignment()).isMultiLang()) throw new FxUpdateException("ex.content.value.invalid.multilanguage.ass.multi", data.getXPathFull()); else throw new FxUpdateException("ex.content.value.invalid.multilanguage.ass.single", data.getXPathFull()); } int pos_lang = insert ? INSERT_LANG_POS : UPDATE_ID_POS + 2; int pos_isdef_lang = insert ? INSERT_ISDEF_LANG_POS : UPDATE_MLDEF_POS; final FxEnvironment env = CacheAdmin.getEnvironment(); if (prop.getDataType().isSingleRowStorage()) { //Data types that just use one db row can be handled in a very similar way Object translatedValue; GregorianCalendar gc = null; final long[] translatedLanguages = value.getTranslatedLanguages(); for (long translatedLanguage : translatedLanguages) { translatedValue = value.getTranslation(translatedLanguage); if (translatedValue == null) { LOG.warn("Translation for " + data.getXPath() + " is null!"); } ps.setLong(pos_lang, translatedLanguage); if (!value.isMultiLanguage()) ps.setBoolean(pos_isdef_lang, true); else ps.setBoolean(pos_isdef_lang, value.isDefaultLanguage(translatedLanguage)); if (upperColumnPos != -1) { final Locale locale = value.isMultiLanguage() ? env.getLanguage(translatedLanguage).getLocale() : Locale.getDefault(); ps.setString(upperColumnPos, translatedValue.toString().toUpperCase(locale)); } int[] pos = insert ? getColumnPosInsert(prop) : getColumnPosUpdate(prop); switch (prop.getDataType()) { case Double: checkDataType(FxDouble.class, value, data.getXPathFull()); ps.setDouble(pos[0], (Double) translatedValue); break; case Float: checkDataType(FxFloat.class, value, data.getXPathFull()); ps.setFloat(pos[0], (Float) translatedValue); break; case LargeNumber: checkDataType(FxLargeNumber.class, value, data.getXPathFull()); ps.setLong(pos[0], (Long) translatedValue); break; case Number: checkDataType(FxNumber.class, value, data.getXPathFull()); ps.setInt(pos[0], (Integer) translatedValue); break; case HTML: checkDataType(FxHTML.class, value, data.getXPathFull()); boolean useTidy = ((FxHTML) value).isTidyHTML(); ps.setBoolean(pos[1], useTidy); final String extractorInput = doTidy(data.getXPathFull(), (String) translatedValue); if (useTidy) { translatedValue = extractorInput; } final HtmlExtractor result = new HtmlExtractor(extractorInput, true); setBigString(ps, pos[2], result.getText()); setBigString(ps, pos[0], (String) translatedValue); break; case String1024: case Text: checkDataType(FxString.class, value, data.getXPathFull()); setBigString(ps, pos[0], (String) translatedValue); break; case Boolean: checkDataType(FxBoolean.class, value, data.getXPathFull()); ps.setBoolean(pos[0], (Boolean) translatedValue); break; case Date: checkDataType(FxDate.class, value, data.getXPathFull()); if (gc == null) gc = new GregorianCalendar(); gc.setTime((Date) translatedValue); //strip all time information, this might not be necessary since ps.setDate() strips them //for most databases but won't hurt either ;) gc.set(GregorianCalendar.HOUR, 0); gc.set(GregorianCalendar.MINUTE, 0); gc.set(GregorianCalendar.SECOND, 0); gc.set(GregorianCalendar.MILLISECOND, 0); ps.setDate(pos[0], new java.sql.Date(gc.getTimeInMillis())); break; case DateTime: checkDataType(FxDateTime.class, value, data.getXPathFull()); if (gc == null) gc = new GregorianCalendar(); gc.setTime((Date) translatedValue); ps.setTimestamp(pos[0], new Timestamp(gc.getTimeInMillis())); break; case DateRange: checkDataType(FxDateRange.class, value, data.getXPathFull()); if (gc == null) gc = new GregorianCalendar(); gc.setTime(((DateRange) translatedValue).getLower()); gc.set(GregorianCalendar.HOUR, 0); gc.set(GregorianCalendar.MINUTE, 0); gc.set(GregorianCalendar.SECOND, 0); gc.set(GregorianCalendar.MILLISECOND, 0); ps.setDate(pos[0], new java.sql.Date(gc.getTimeInMillis())); gc.setTime(((DateRange) translatedValue).getUpper()); gc.set(GregorianCalendar.HOUR, 0); gc.set(GregorianCalendar.MINUTE, 0); gc.set(GregorianCalendar.SECOND, 0); gc.set(GregorianCalendar.MILLISECOND, 0); ps.setDate(pos[1], new java.sql.Date(gc.getTimeInMillis())); break; case DateTimeRange: checkDataType(FxDateTimeRange.class, value, data.getXPathFull()); if (gc == null) gc = new GregorianCalendar(); gc.setTime(((DateRange) translatedValue).getLower()); ps.setTimestamp(pos[0], new Timestamp(gc.getTimeInMillis())); gc.setTime(((DateRange) translatedValue).getUpper()); ps.setTimestamp(pos[1], new Timestamp(gc.getTimeInMillis())); break; case Binary: checkDataType(FxBinary.class, value, data.getXPathFull()); BinaryDescriptor binary = (BinaryDescriptor) translatedValue; if (!binary.isNewBinary()) { ps.setLong(pos[0], binary.getId()); } else { try { //transfer the binary from the transit table to the binary table BinaryDescriptor created = binaryStorage.binaryTransit(con, binary); ps.setLong(pos[0], created.getId()); //check all other properties if they contain the same handle //and replace with the data of the new binary for (FxData _curr : allData) { if (_curr instanceof FxPropertyData && !_curr.isEmpty() && ((FxPropertyData) _curr).getValue() instanceof FxBinary) { FxBinary _val = (FxBinary) ((FxPropertyData) _curr).getValue(); _val._replaceHandle(binary.getHandle(), created); } } } catch (FxApplicationException e) { throw new FxDbException(e); } } break; case SelectOne: checkDataType(FxSelectOne.class, value, data.getXPathFull()); ps.setLong(pos[0], ((FxSelectListItem) translatedValue).getId()); break; case SelectMany: checkDataType(FxSelectMany.class, value, data.getXPathFull()); SelectMany sm = (SelectMany) translatedValue; for (int i1 = 0; i1 < sm.getSelected().size(); i1++) { FxSelectListItem item = sm.getSelected().get(i1); if (i1 > 0) { if (batchContentDataChanges()) ps.addBatch(); else ps.executeUpdate(); } ps.setLong(pos[0], item.getId()); ps.setString(pos[1], sm.getSelectedIdsList()); ps.setLong(pos[2], sm.getSelectedIds().size()); } if (sm.getSelected().size() == 0) ps.setLong(pos[0], 0); //write the virtual item as a marker to have a valid row break; case Reference: //reference integrity check is done prior to saving ps.setLong(pos[0], ((FxPK) translatedValue).getId()); break; case InlineReference: default: throw new FxDbException(LOG, "ex.db.notImplemented.store", prop.getDataType().getName()); } int valueDataPos = insert ? getValueDataInsertPos(prop.getDataType()) : getValueDataUpdatePos(prop.getDataType()); if (value.hasValueData(translatedLanguage)) { ps.setInt(valueDataPos, value.getValueDataRaw(translatedLanguage)); } else ps.setNull(valueDataPos, Types.NUMERIC); if (batchContentDataChanges()) ps.addBatch(); else { try { ps.executeUpdate(); } catch (SQLException e) { LOG.error(prop.getName(), e); throw e; } } } } else { switch (prop.getDataType()) { //TODO: implement datatype specific insert default: throw new FxDbException(LOG, "ex.db.notImplemented.store", prop.getDataType().getName()); } } if (ft != null && prop.isFulltextIndexed() && includeFullText) ft.index(data); }
From source file:org.siphon.jssql.SqlExecutor.java
void setArg(PreparedStatement ps, int index, Object arg) throws SQLException, SqlExecutorException, UnsupportedDataTypeException, NoSuchMethodException, ScriptException { boolean output = false; int outputParameterType = 0; CallableStatement cs = null;//from w w w . j av a 2 s. c o m if (ps instanceof CallableStatement) { cs = (CallableStatement) ps; if (arg instanceof ScriptObjectMirror && ((ScriptObjectMirror) arg).containsKey("OUT")) { ScriptObjectMirror jsarg = ((ScriptObjectMirror) arg); outputParameterType = (int) jsarg.get("JDBC_TYPE"); arg = jsarg.get("VALUE"); output = true; } } if (output) { cs.registerOutParameter(index + 1, outputParameterType); if (JsTypeUtil.isNull(arg) || (arg instanceof Double && Double.isNaN((Double) arg))) { return; } } if (JsTypeUtil.isNull(arg)) { ps.setObject(index + 1, null); } else if (arg instanceof CharSequence) { ps.setString(index + 1, arg.toString()); } else if (arg instanceof NativeString) { ps.setString(index + 1, arg.toString()); } else if (arg instanceof Double) { // js number always be // Doublebut if its came from // JSON.parse since JSON is jdk // given global object, it will // make Integer and ... double d = ((Double) arg).doubleValue(); if (d == (int) d) { ps.setInt(index + 1, (int) d); } else if (d == (long) d) { ps.setLong(index + 1, (long) d); } else { ps.setBigDecimal(index + 1, new BigDecimal(d)); } } else if (arg instanceof Integer) { ps.setInt(index + 1, (Integer) arg); } else if (arg instanceof Long) { ps.setLong(index + 1, (Long) arg); } else if (arg instanceof Float) { ps.setFloat(index + 1, (Float) arg); } else if (jsTypeUtil.isNativeDate(arg)) { ps.setTimestamp(index + 1, parseDate(arg)); } else if (arg instanceof ZonedDateTime) { ZonedDateTime zdt = (ZonedDateTime) arg; ps.setTimestamp(index + 1, new Timestamp(zdt.toInstant().toEpochMilli())); } else if (arg instanceof Boolean) { ps.setBoolean(index + 1, JsTypeUtil.isTrue(arg)); } else if (arg instanceof ScriptObjectMirror || arg instanceof ScriptObject) { String attr = null; Object value = null; if (arg instanceof ScriptObjectMirror) { ScriptObjectMirror atm = (ScriptObjectMirror) arg; if (atm.keySet().contains("toJavaObject")) { Object obj = atm.callMember("toJavaObject"); setArg(ps, index, obj); return; } attr = atm.keySet().iterator().next(); value = atm.get(attr); } else { ScriptObject obj = (ScriptObject) arg; if (obj.containsKey("toJavaObject")) { ScriptObjectMirror atm = (ScriptObjectMirror) jsTypeUtil.toScriptObjectMirror(obj); Object result = atm.callMember("toJavaObject"); setArg(ps, index, result); return; } String[] arr = obj.getOwnKeys(false); if (arr.length == 0) { throw new SqlExecutorException("js argument " + arg + " (" + arg.getClass() + ") at " + index + " is an empty js object"); } attr = arr[0]; value = obj.get(attr); } if ("STRING".equals(attr)) { ps.setString(index + 1, String.valueOf(value)); } else if ("DECIMAL".equals(attr)) { if (value instanceof Double) { ps.setBigDecimal(index + 1, new BigDecimal((Double) value)); } else { ps.setBigDecimal(index + 1, new BigDecimal(value + "")); } } else if ("INT".equals(attr)) { if (value instanceof Double) { if (((Double) value).isNaN()) { ps.setObject(index + 1, null); } else { ps.setInt(index + 1, ((Double) value).intValue()); } } else { ps.setInt(index + 1, new Integer(value + "")); } } else if ("BOOLEAN".equals(attr)) { ps.setBoolean(index + 1, JsTypeUtil.isTrue(arg)); } else if ("DOUBLE".equals(attr)) { if (value instanceof Double) { if (((Double) value).isNaN()) { ps.setObject(index + 1, null); } else { ps.setDouble(index + 1, (double) value); } } else { ps.setDouble(index + 1, new Double(value + "")); } } else if ("FLOAT".equals(attr)) { if (value instanceof Double) { if (((Double) value).isNaN()) { ps.setObject(index + 1, null); } else { ps.setFloat(index + 1, (float) (double) value); } } else { ps.setFloat(index + 1, new Float(value + "")); } } else if ("DATE".equals(attr)) { ps.setTimestamp(index + 1, parseDate(value)); } else if ("TIME".equals(attr)) { ps.setTimestamp(index + 1, parseTime(value)); } else if ("BINARY".equals(attr)) { ps.setBytes(index + 1, parseBinary(value)); } else if ("CLOB".equals(attr)) { Clob clob = ps.getConnection().createClob(); clob.setString(1, String.valueOf(value)); ps.setClob(index + 1, clob); } else if ("LONG".equals(attr)) { if (value instanceof Double) { if (((Double) value).isNaN()) { ps.setObject(index + 1, null); } else { ps.setLong(index + 1, ((Double) value).longValue()); } } else { ps.setLong(index + 1, new Long(value + "")); } } else if ("OUTCURSOR".equals(attr)) { // cs.registerOutParameter(i+1, OracleTypes.CURSOR); cs.registerOutParameter(index + 1, -10); } else if ("ARRAY".equals(attr)) { if (value instanceof NativeArray) { ps.setArray(index + 1, createSqlArray(ps.getConnection(), (NativeArray) value)); } else { setArg(ps, index, value); // value is {ARRAY : ['int', e1, e2, ...]} } // ps.setObject(i+1, createSqlArray(ps.getConnection(), // (NativeArray) value)); } else if ("JSON".equals(attr) || "JSONB".equals(attr)) { PGobject obj = new PGobject(); obj.setType(attr.toLowerCase()); obj.setValue(this.JSON.tryStringify(value)); ps.setObject(index + 1, obj); } else if ("UUID".equals(attr)) { if (value != null) { ps.setObject(index + 1, UUID.fromString(value.toString())); } else { ps.setObject(index + 1, null); } } else { if (this.defaultJsonDbType != null) { PGobject obj = new PGobject(); obj.setType(this.defaultJsonDbType); obj.setValue(this.JSON.tryStringify(arg)); ps.setObject(index + 1, obj); } else { throw new SqlExecutorException("js argument " + arg + " (" + arg.getClass() + ") not support"); } } } else { throw new SqlExecutorException( "js argument " + arg + " (" + arg.getClass() + ") at " + index + " not support"); } }
From source file:edu.uga.cs.fluxbuster.db.PostgresDBInterface.java
/** * @see edu.uga.cs.fluxbuster.db.DBInterface#storeClusters(java.util.List, java.lang.String, java.util.Date) *//*from w ww.ja va2 s. c o m*/ @Override public void storeClusters(List<DomainCluster> clusters, String sensorname, Date logdate) { String logDateTable = dateFormatTable.format(logdate); Connection con = null; PreparedStatement domainsInsertStmt = null; PreparedStatement domainsSelectStmt = null; PreparedStatement clustersInsertStmt = null; PreparedStatement resolvedIPSInsertStmt = null; PreparedStatement clusterResolvedIPSInsertStmt = null; PreparedStatement clusterFeatureVectorsInsertStmt = null; try { con = this.getConnection(); domainsInsertStmt = con .prepareStatement("INSERT INTO domains_" + logDateTable + " VALUES(DEFAULT, ?, ?, ?)"); domainsSelectStmt = con .prepareStatement("SELECT domain_id FROM domains_" + logDateTable + " WHERE domain_name = ?"); clustersInsertStmt = con .prepareStatement("INSERT INTO clusters_" + logDateTable + " VALUES " + "(?, ?, ?, ?)"); resolvedIPSInsertStmt = con .prepareStatement("INSERT INTO resolved_ips_" + logDateTable + " VALUES " + "( ?, ?, inet(?))"); clusterResolvedIPSInsertStmt = con.prepareStatement( "INSERT INTO cluster_resolved_ips_" + logDateTable + " VALUES " + "( ?, ?, ?, inet(?))"); clusterFeatureVectorsInsertStmt = con.prepareStatement("INSERT INTO cluster_feature_vectors_" + logDateTable + "(cluster_id, sensor_name, log_date, network_cardinality, ip_diversity, " + "number_of_domains, ttl_per_domain, ip_growth_ratio, queries_per_domain, avg_last_growth_ratio_single_entry, " + "avg_last_growth_ratio_entries, avg_last_growth_prefix_ratio_entries, last_growth_ratio_cluster," + "last_growth_prefix_ratio_cluster) VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); int clusterId = 1; for (DomainCluster cluster : clusters) { for (CandidateFluxDomain candidateDomain : cluster.getCandidateDomains()) { String domainName = filterChars(candidateDomain.getDomainName()); String domainNameRev = DomainNameUtils.reverseDomainName(domainName); String secondLevelDomainName = DomainNameUtils.extractEffective2LD(domainName); String secondLevelDomainNameRev = null; if (secondLevelDomainName != null) { secondLevelDomainNameRev = DomainNameUtils.reverseDomainName(secondLevelDomainName); } else { secondLevelDomainNameRev = DomainNameUtils.reverseDomainName(domainName); } domainsInsertStmt.setString(1, domainNameRev); domainsInsertStmt.setDate(2, new java.sql.Date(logdate.getTime())); domainsInsertStmt.setString(3, secondLevelDomainNameRev); executePreparedStatementNoResult(con, domainsInsertStmt); domainsSelectStmt.setString(1, domainNameRev); ResultSet rs = this.executePreparedStatementWithResult(con, domainsSelectStmt); try { if (rs.next()) { int domainId = rs.getInt(1); clustersInsertStmt.setInt(1, clusterId); clustersInsertStmt.setInt(2, domainId); clustersInsertStmt.setString(3, sensorname); clustersInsertStmt.setDate(4, new java.sql.Date(logdate.getTime())); this.executePreparedStatementNoResult(con, clustersInsertStmt); for (InetAddress resolvedIP : candidateDomain.getIps()) { resolvedIPSInsertStmt.setInt(1, domainId); resolvedIPSInsertStmt.setDate(2, new java.sql.Date(logdate.getTime())); resolvedIPSInsertStmt.setString(3, resolvedIP.getHostAddress()); this.executePreparedStatementNoResult(con, resolvedIPSInsertStmt); } } } catch (SQLException ex) { if (log.isErrorEnabled()) { log.error("", ex); } } finally { rs.close(); } } /*String nickname = getNicknames((List<String>)cluster.getDomains()); insertQuery = "INSERT INTO cluster_nicknames_"+ logDateTable +" VALUES" + "("+clusterId+", '"+sensorname+"', '"+logDateStr+"', '"+nickname+"')"; performInsertQuery(insertQuery, clusterNicknamesCreateQuery);*/ for (InetAddress resolvedIP : cluster.getIps()) { clusterResolvedIPSInsertStmt.setInt(1, clusterId); clusterResolvedIPSInsertStmt.setString(2, sensorname); clusterResolvedIPSInsertStmt.setDate(3, new java.sql.Date(logdate.getTime())); clusterResolvedIPSInsertStmt.setString(4, resolvedIP.getHostAddress()); this.executePreparedStatementNoResult(con, clusterResolvedIPSInsertStmt); } clusterFeatureVectorsInsertStmt.setInt(1, clusterId); clusterFeatureVectorsInsertStmt.setString(2, sensorname); clusterFeatureVectorsInsertStmt.setDate(3, new java.sql.Date(logdate.getTime())); clusterFeatureVectorsInsertStmt.setInt(4, cluster.getIps().size()); clusterFeatureVectorsInsertStmt.setDouble(5, cluster.getIpDiversity()); clusterFeatureVectorsInsertStmt.setInt(6, cluster.getDomains().size()); clusterFeatureVectorsInsertStmt.setDouble(7, cluster.getAvgTTLPerDomain()); clusterFeatureVectorsInsertStmt.setDouble(8, cluster.getIpGrowthRatio()); clusterFeatureVectorsInsertStmt.setDouble(9, cluster.getQueriesPerDomain()); Double temp = cluster.getAvgLastGrowthRatioSingleEntry(); if (temp == null) { clusterFeatureVectorsInsertStmt.setNull(10, java.sql.Types.REAL); } else { clusterFeatureVectorsInsertStmt.setDouble(10, temp); } temp = cluster.getAvgLastGrowthRatioEntries(); if (temp == null) { clusterFeatureVectorsInsertStmt.setNull(11, java.sql.Types.REAL); } else { clusterFeatureVectorsInsertStmt.setDouble(11, temp); } temp = cluster.getAvgLastGrowthPrefixRatioEntries(); if (temp == null) { clusterFeatureVectorsInsertStmt.setNull(12, java.sql.Types.REAL); } else { clusterFeatureVectorsInsertStmt.setDouble(12, temp); } temp = cluster.getLastGrowthRatioCluster(); if (temp == null) { clusterFeatureVectorsInsertStmt.setNull(13, java.sql.Types.REAL); } else { clusterFeatureVectorsInsertStmt.setDouble(13, temp); } temp = cluster.getLastGrowthPrefixRatioCluster(); if (temp == null) { clusterFeatureVectorsInsertStmt.setNull(14, java.sql.Types.REAL); } else { clusterFeatureVectorsInsertStmt.setDouble(14, temp); } this.executePreparedStatementNoResult(con, clusterFeatureVectorsInsertStmt); clusterId++; } } catch (SQLException e) { if (log.isErrorEnabled()) { log.error("", e); } } finally { try { if (domainsInsertStmt != null && !domainsInsertStmt.isClosed()) { domainsInsertStmt.close(); } if (domainsSelectStmt != null && !domainsSelectStmt.isClosed()) { domainsSelectStmt.close(); } if (clustersInsertStmt != null && !clustersInsertStmt.isClosed()) { clustersInsertStmt.close(); } if (resolvedIPSInsertStmt != null && !resolvedIPSInsertStmt.isClosed()) { resolvedIPSInsertStmt.close(); } if (clusterResolvedIPSInsertStmt != null && !clusterResolvedIPSInsertStmt.isClosed()) { clusterResolvedIPSInsertStmt.close(); } if (clusterFeatureVectorsInsertStmt != null && !clusterFeatureVectorsInsertStmt.isClosed()) { clusterFeatureVectorsInsertStmt.close(); } if (con != null && !con.isClosed()) { con.close(); } } catch (SQLException e) { e.printStackTrace(); } } }
From source file:br.com.cobranca.util.Util.java
public static <T> boolean alterarRegistro(T objAlterado, Class<T> classe, Connection con, String strWhere) throws Exception, SQLException { if (strWhere == null || strWhere.trim().equals("")) { return false; }//from w w w .j a v a 2s. c o m PreparedStatement ps = null; ResultSet rs = null; T objOriginal = classe.newInstance(); try { // Recuperar objeto original no banco de dados String nomeTabela = objAlterado.getClass().getSimpleName(); String strSql = "SELECT * FROM " + nomeTabela + " " + strWhere; ps = con.prepareStatement(strSql); rs = ps.executeQuery(); if (rs.next()) { objOriginal = Util.atribuirValores(classe, rs); } else { return false; } rs.close(); ps.close(); // Comparar valores dos dois objetos strSql = "UPDATE " + nomeTabela + " SET "; boolean efetuarAlteracao; boolean usarVirgula = false; for (Field field : objAlterado.getClass().getDeclaredFields()) { efetuarAlteracao = false; String nomeColuna = field.getName(); String tipoColuna = field.getType().getSimpleName(); if (tipoColuna.toUpperCase().contains("INT")) { tipoColuna = "Int"; } else { tipoColuna = StringPrimeiraLetraMaiuscula(tipoColuna); } // obj . get + nome do campo Method met = classe.getMethod("get" + StringPrimeiraLetraMaiuscula(field.getName())); if (tipoColuna.equals("Int")) { Integer valorOriginal = (Integer) met.invoke(objOriginal); Integer valorAlterado = (Integer) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { efetuarAlteracao = true; } } else if (tipoColuna.equals("String")) { String valorOriginal = (String) met.invoke(objOriginal); String valorAlterado = (String) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { efetuarAlteracao = true; } } else if (tipoColuna.equals("Double")) { Double valorOriginal = (Double) met.invoke(objOriginal); Double valorAlterado = (Double) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { efetuarAlteracao = true; } } else if (tipoColuna.equals("Float")) { Float valorOriginal = (Float) met.invoke(objOriginal); Float valorAlterado = (Float) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { efetuarAlteracao = true; } } else if (tipoColuna.equals("Long")) { Long valorOriginal = (Long) met.invoke(objOriginal); Long valorAlterado = (Long) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { efetuarAlteracao = true; } } else if (tipoColuna.equals("Boolean")) { Boolean valorOriginal = (Boolean) met.invoke(objOriginal); Boolean valorAlterado = (Boolean) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { efetuarAlteracao = true; } } else if (tipoColuna.equals("Date")) { Date valorOriginal = (Date) met.invoke(objOriginal); Date valorAlterado = (Date) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { efetuarAlteracao = true; } } else { return false; } if (efetuarAlteracao) { if (usarVirgula) { strSql = strSql + ", "; //usarVirgula = false; } strSql = strSql + nomeColuna + " = ? "; usarVirgula = true; } } //Se no houve alterao, retorna falso if (!strSql.contains("?")) { return true; } strSql = strSql + strWhere; ps = con.prepareStatement(strSql); int i = 1; // ps.set?() for (Field field : objAlterado.getClass().getDeclaredFields()) { String nomeColuna = field.getName(); String tipoColuna = field.getType().getSimpleName(); if (tipoColuna.toUpperCase().contains("INT")) { tipoColuna = "Int"; } else { tipoColuna = StringPrimeiraLetraMaiuscula(tipoColuna); } // obj . get + nome do campo Method met = classe.getMethod("get" + StringPrimeiraLetraMaiuscula(field.getName())); if (tipoColuna.equals("Int")) { Integer valorOriginal = (Integer) met.invoke(objOriginal); Integer valorAlterado = (Integer) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { if (valorAlterado == null) { ps.setString(i, null); } else { ps.setInt(i, valorAlterado); } i++; } } else if (tipoColuna.equals("String")) { String valorOriginal = (String) met.invoke(objOriginal); String valorAlterado = (String) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { ps.setString(i, valorAlterado); i++; } } else if (tipoColuna.equals("Double")) { Double valorOriginal = (Double) met.invoke(objOriginal); Double valorAlterado = (Double) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { if (valorAlterado == null) { ps.setString(i, null); } else { ps.setDouble(i, valorAlterado); } i++; } } else if (tipoColuna.equals("Float")) { Float valorOriginal = (Float) met.invoke(objOriginal); Float valorAlterado = (Float) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { if (valorAlterado == null) { ps.setString(i, null); } else { ps.setFloat(i, valorAlterado); } i++; } } else if (tipoColuna.equals("Long")) { Long valorOriginal = (Long) met.invoke(objOriginal); Long valorAlterado = (Long) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { if (valorAlterado == null) { ps.setString(i, null); } else { ps.setLong(i, valorAlterado); } i++; } } else if (tipoColuna.equals("Boolean")) { Boolean valorOriginal = (Boolean) met.invoke(objOriginal); Boolean valorAlterado = (Boolean) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { if (valorAlterado == null) { ps.setString(i, null); } else { ps.setBoolean(i, valorAlterado); } i++; } } else if (tipoColuna.equals("Date")) { Date valorOriginal = (Date) met.invoke(objOriginal); Date valorAlterado = (Date) met.invoke(objAlterado); if (!valorOriginal.equals(valorAlterado)) { if (valorAlterado == null) { ps.setString(i, null); } else { ps.setDate(i, new java.sql.Date(valorAlterado.getTime())); } i++; } } else { return false; } } // fim int qtdLinhasAfetadas = ps.executeUpdate(); if (qtdLinhasAfetadas <= 0) { return false; } } catch (Exception ex) { throw new Exception(ex.getMessage()); } finally { if (rs != null) { rs.close(); } if (ps != null) { ps.close(); } } return true; }
From source file:at.alladin.rmbt.statisticServer.StatisticsResource.java
private PreparedStatement selectProviders(final Connection conn, final boolean group, final float quantile, final int durationDays, final double accuracy, final String country, final boolean useMobileProvider, final String where, final boolean signalMobile) throws SQLException { PreparedStatement ps; String sql = String.format("SELECT" + (group ? " p.name, p.shortname, " : "") + " count(t.uid) count," + " quantile(speed_download::bigint, ?::double precision) quantile_down," + " quantile(speed_upload::bigint, ?::double precision) quantile_up," + " quantile(signal_strength::bigint, ?::double precision) quantile_signal," + " quantile(ping_shortest::bigint, ?::double precision) quantile_ping," + " sum((speed_download >= ?)::int)::double precision / count(speed_download) down_green," + " sum((speed_download < ? and speed_download >= ?)::int)::double precision / count(speed_download) down_yellow," + " sum((speed_download < ?)::int)::double precision / count(speed_download) down_red," + " sum((speed_upload >= ?)::int)::double precision / count(speed_upload) up_green," + " sum((speed_upload < ? and speed_upload >= ?)::int)::double precision / count(speed_upload) up_yellow," + " sum((speed_upload < ?)::int)::double precision / count(speed_upload) up_red," + " sum((signal_strength >= ?)::int)::double precision / count(signal_strength) signal_green," + " sum((signal_strength < ? and signal_strength >= ?)::int)::double precision / count(signal_strength) signal_yellow," + " sum((signal_strength < ?)::int)::double precision / count(signal_strength) signal_red," + " sum((ping_shortest <= ?)::int)::double precision / count(ping_shortest) ping_green," + " sum((ping_shortest > ? and ping_shortest <= ?)::int)::double precision / count(ping_shortest) ping_yellow," + " sum((ping_shortest > ?)::int)::double precision / count(ping_shortest) ping_red" + " FROM test t" + " LEFT JOIN network_type nt ON nt.uid=t.network_type" + " JOIN provider p ON" + (useMobileProvider ? " t.mobile_provider_id = p.uid" : " t.provider_id = p.uid") + " WHERE %s" + ((country != null && useMobileProvider) ? " AND t.network_sim_country = ?" : "") + " AND t.deleted = false AND t.implausible = false AND t.status = 'FINISHED'" + " AND t.time > NOW() - CAST(? AS INTERVAL)" + ((accuracy > 0) ? " AND t.geo_accuracy < ?" : "") + (group ? " GROUP BY p.uid" : "") + " ORDER BY count DESC", where); if (country != null) { sql = String.format("SELECT" + ((group && useMobileProvider) ? " p.name AS name, p.shortname AS shortname, p.mccmnc AS sim_mcc_mnc, " : "") + ((group && !useMobileProvider) ? " public_ip_as_name AS name, public_ip_as_name AS shortname, t.public_ip_asn AS asn, " : "") + " count(t.uid) count," + " quantile(speed_download::bigint, ?::double precision) quantile_down," + " quantile(speed_upload::bigint, ?::double precision) quantile_up," + " quantile(signal_strength::bigint, ?::double precision) quantile_signal," + " quantile(ping_shortest::bigint, ?::double precision) quantile_ping," + " sum((speed_download >= ?)::int)::double precision / count(speed_download) down_green," + " sum((speed_download < ? and speed_download >= ?)::int)::double precision / count(speed_download) down_yellow," + " sum((speed_download < ?)::int)::double precision / count(speed_download) down_red," + " sum((speed_upload >= ?)::int)::double precision / count(speed_upload) up_green," + " sum((speed_upload < ? and speed_upload >= ?)::int)::double precision / count(speed_upload) up_yellow," + " sum((speed_upload < ?)::int)::double precision / count(speed_upload) up_red," + " sum((signal_strength >= ?)::int)::double precision / count(signal_strength) signal_green," + " sum((signal_strength < ? and signal_strength >= ?)::int)::double precision / count(signal_strength) signal_yellow," + " sum((signal_strength < ?)::int)::double precision / count(signal_strength) signal_red," + " sum((ping_shortest <= ?)::int)::double precision / count(ping_shortest) ping_green," + " sum((ping_shortest > ? and ping_shortest <= ?)::int)::double precision / count(ping_shortest) ping_yellow," + " sum((ping_shortest > ?)::int)::double precision / count(ping_shortest) ping_red" + " FROM test t" + " LEFT JOIN network_type nt ON nt.uid=t.network_type" + (useMobileProvider ? " LEFT JOIN mccmnc2name p ON p.uid = t.mobile_sim_id" : "") + " WHERE %s" + " AND " + (useMobileProvider// www .j a va 2s . c o m ? "p.country = ? AND ((t.country_location IS NULL OR t.country_location = ?) AND (NOT t.roaming_type = 2))" : "t.country_geoip = ? ") + " AND t.deleted = false AND t.implausible = false AND t.status = 'FINISHED'" + " AND t.time > NOW() - CAST(? AS INTERVAL)" + ((accuracy > 0) ? " AND t.geo_accuracy < ?" : "") + ((group && (useMobileProvider)) ? " GROUP BY p.uid, p.mccmnc" : "") + ((group && (!useMobileProvider)) ? " GROUP BY t.public_ip_as_name, t.public_ip_asn" : "") + " ORDER BY count DESC", where); } ps = conn.prepareStatement(sql); int i = 1; for (int j = 0; j < 3; j++) ps.setFloat(i++, quantile); ps.setFloat(i++, 1 - quantile); // inverse for ping final int[] td = classification.THRESHOLD_DOWNLOAD; ps.setInt(i++, td[0]); ps.setInt(i++, td[0]); ps.setInt(i++, td[1]); ps.setInt(i++, td[1]); final int[] tu = classification.THRESHOLD_UPLOAD; ps.setInt(i++, tu[0]); ps.setInt(i++, tu[0]); ps.setInt(i++, tu[1]); ps.setInt(i++, tu[1]); final int[] ts = signalMobile ? classification.THRESHOLD_SIGNAL_MOBILE : classification.THRESHOLD_SIGNAL_WIFI; ps.setInt(i++, ts[0]); ps.setInt(i++, ts[0]); ps.setInt(i++, ts[1]); ps.setInt(i++, ts[1]); final int[] tp = classification.THRESHOLD_PING; ps.setInt(i++, tp[0]); ps.setInt(i++, tp[0]); ps.setInt(i++, tp[1]); ps.setInt(i++, tp[1]); if (country != null) { if (useMobileProvider) { ps.setString(i++, country.toLowerCase()); //mccmnc2name.country ps.setString(i++, country.toUpperCase()); //country_location } else { ps.setString(i++, country.toUpperCase()); } } ps.setString(i++, String.format("%d days", durationDays)); if (accuracy > 0) { ps.setDouble(i++, accuracy); } System.out.println(ps); return ps; }
From source file:org.osmdroid.reader.Reader.java
/** * imports the osm bz2 file into the database * * @param path//w w w .ja v a2 s. c o m * @param connection * @throws Exception if the file wasn't found, can't write the output file, or there's some kind of IO exception while reading */ public void read(String path, Connection connection) throws Exception { if (path == null) throw new IllegalArgumentException("path"); if (!new File(path).exists()) throw new FileNotFoundException("File Not Found"); PreparedStatement p; try { p = connection.prepareStatement( "CREATE TABLE IF NOT EXISTS \"nodes\" (\"id\" INTEGER PRIMARY KEY NOT NULL , \"lat\" DOUBLE NOT NULL , \"lon\" DOUBLE NOT NULL , \"version\" INTEGER, \"timestamp\" DATETIME, \"uid\" INTEGER, \"user\" TEXT, \"changeset\" INTEGER)"); p.execute(); p.close(); } catch (Exception ex) { ex.printStackTrace(); } try { p = connection.prepareStatement( "CREATE TABLE IF NOT EXISTS \"relation_members\" (\"type\" TEXT NOT NULL , \"ref\" INTEGER NOT NULL , \"role\" TEXT, \"id\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL )"); p.execute(); p.close(); } catch (Exception ex) { ex.printStackTrace(); } try { p = connection.prepareStatement( "CREATE TABLE IF NOT EXISTS \"relations\" (\"id\" INTEGER PRIMARY KEY NOT NULL , \"user\" TEXT, \"uid\" INTEGER, \"version\" INTEGER, \"changeset\" INTEGER, \"timestamp\" DATETIME)"); p.execute(); p.close(); } catch (Exception ex) { ex.printStackTrace(); } try { p = connection.prepareStatement( "CREATE TABLE IF NOT EXISTS \"tag\" (\"id\" INTEGER NOT NULL , \"k\" TEXT NOT NULL , \"v\" TEXT NOT NULL , \"reftype\" INTEGER NOT NULL DEFAULT -1, PRIMARY KEY( \"reftype\",\"k\" ,\"id\" ) )"); p.execute(); p.close(); } catch (Exception ex) { ex.printStackTrace(); } try { p = connection.prepareStatement( "CREATE TABLE IF NOT EXISTS \"way_no\" (\"way_id\" INTEGER NOT NULL , \"node_id\" INTEGER NOT NULL, PRIMARY KEY (\"way_id\", \"node_id\") ) "); p.execute(); p.close(); } catch (Exception ex) { ex.printStackTrace(); } try { p = connection.prepareStatement( "CREATE TABLE IF NOT EXISTS \"ways\" (\"id\" INTEGER PRIMARY KEY NOT NULL , \"changeset\" INTEGER, \"version\" INTEGER, \"user\" TEXT, \"uid\" INTEGER, \"timestamp\" DATETIME)"); p.execute(); p.close(); } catch (Exception ex) { ex.printStackTrace(); } BufferedReader xmlInputStream = getBufferedReaderForBZ2File(path); XmlPullParserFactory factory = XmlPullParserFactory.newInstance(); factory.setNamespaceAware(false); XmlPullParser xmlStreamReader = factory.newPullParser(); xmlStreamReader.setInput(xmlInputStream); Stack<String> xpath = new Stack<String>(); long recordCount = 0; long batchCount = 0; long lastId = -1; long start = System.currentTimeMillis(); OsmType lastType = OsmType.NODE; long id = -1; long changset = -1; double lat = 0.0; double lon = 0.0; long version = -1; String user = ""; long uid = -1; long inserts = 0; String key = ""; String val = ""; //int eventType = -1; Date timestamp = new Date(System.currentTimeMillis()); //connection.setAutoCommit(false); int eventType = xmlStreamReader.getEventType(); while (eventType != XmlPullParser.END_DOCUMENT) { String tagname = xmlStreamReader.getName(); recordCount++; key = ""; val = ""; id = -1; changset = -1; version = -1; user = ""; uid = -1; //timestamp = new Date(System.currentTimeMillis()); //System.out.println(recordCount); //System.out.println ("XMLEvent " + eventType + " " + tagname); //long btime = System.currentTimeMillis(); switch (eventType) { case XmlPullParser.START_TAG: if (xmlStreamReader.getName().toString().equalsIgnoreCase("osm")) { } if (xmlStreamReader.getName().toString().equalsIgnoreCase("bounds")) { } if (xmlStreamReader.getName().toString().equalsIgnoreCase("node")) { xpath.push(xmlStreamReader.getName().toString()); p = connection .prepareStatement("INSERT INTO nodes (id,changeset,version,user,uid,timestamp,lat,lon) " + "VALUES (?,?,?,?,?,?,?,?); "); for (int i = 0; i < xmlStreamReader.getAttributeCount(); i++) { // System.out.println(xmlStreamReader.getAttributeName(i) + "=" // + xmlStreamReader.getAttributeValue(i)); if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("id")) { id = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("user")) { user = xmlStreamReader.getAttributeValue(i); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("uid")) { uid = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("changeset")) { changset = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("version")) { version = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("lat")) { lat = Double.parseDouble(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("lon")) { lon = Double.parseDouble(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("timestamp")) { timestamp.setTime(sdf.parse(xmlStreamReader.getAttributeValue(i)).getTime()); } } if (id != -1) { p.setLong(1, id); p.setLong(2, changset); p.setLong(3, version); p.setString(4, user); p.setLong(5, uid); p.setDate(6, timestamp); p.setDouble(7, lat); p.setDouble(8, lon); try { p.executeUpdate(); inserts++; //batchCount++; } catch (Exception ex) { System.out.println(p.toString()); ex.printStackTrace(); } p.close(); p = null; } lastId = id; lastType = OsmType.NODE; } else if (xmlStreamReader.getName().toString().equalsIgnoreCase("relation")) { xpath.push(xmlStreamReader.getName().toString()); p = connection .prepareStatement("INSERT INTO relations (id,changeset,version,user,uid,timestamp) " + "VALUES (?,?,?,?,?,?); "); for (int i = 0; i < xmlStreamReader.getAttributeCount(); i++) { // System.out.println(xmlStreamReader.getAttributeName(i) + "=" // + xmlStreamReader.getAttributeValue(i)); if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("id")) { id = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } else if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("user")) { user = xmlStreamReader.getAttributeValue(i); } else if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("uid")) { uid = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } else if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("changeset")) { changset = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } else if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("version")) { version = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } else if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("timestamp")) { timestamp.setTime(sdf.parse(xmlStreamReader.getAttributeValue(i)).getTime()); } else { System.err.println("relation attrib unhandled " + xmlStreamReader.getAttributeName(i)); } } if (id != -1) { p.setLong(1, id); p.setLong(2, changset); p.setLong(3, version); p.setString(4, user); p.setLong(5, uid); p.setDate(6, timestamp); try { p.executeUpdate(); // batchCount++; inserts++; } catch (Exception ex) { System.out.println(p.toString()); ex.printStackTrace(); } p.close(); p = null; } lastId = id; lastType = OsmType.RELATION; } else if (xmlStreamReader.getName().toString().equalsIgnoreCase("way")) { xpath.push(xmlStreamReader.getName().toString()); p = connection.prepareStatement("INSERT INTO ways (id,changeset,version,user,uid,timestamp) " + "VALUES (?,?,?,?,?,?); "); for (int i = 0; i < xmlStreamReader.getAttributeCount(); i++) { //System.out.println(xmlStreamReader.getAttributeName(i) + "=" // + xmlStreamReader.getAttributeValue(i)); if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("id")) { id = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("user")) { user = xmlStreamReader.getAttributeValue(i); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("uid")) { uid = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("changeset")) { changset = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("version")) { version = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("timestamp")) { timestamp.setTime(sdf.parse(xmlStreamReader.getAttributeValue(i)).getTime()); } } if (id != -1) { p.setLong(1, id); p.setLong(2, changset); p.setLong(3, version); p.setString(4, user); p.setLong(5, uid); p.setDate(6, timestamp); try { p.executeUpdate(); inserts++; // batchCount++; } catch (Exception ex) { System.out.println(p.toString()); ex.printStackTrace(); } p.close(); p = null; } lastId = id; lastType = OsmType.WAY; } else if (xmlStreamReader.getName().toString().equalsIgnoreCase("tag")) { if (!xpath.isEmpty() && ((xpath.peek().equalsIgnoreCase("way") || xpath.peek().equalsIgnoreCase("node") || xpath.peek().equalsIgnoreCase("relation")) && lastId != -1)) { for (int i = 0; i < xmlStreamReader.getAttributeCount(); i++) { //System.out.println(xmlStreamReader.getAttributeName(i) + "=" // + xmlStreamReader.getAttributeValue(i)); if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("k")) { key = xmlStreamReader.getAttributeValue(i); } else if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("v")) { val = xmlStreamReader.getAttributeValue(i); } else { //uncaptured attribute System.out.println(xmlStreamReader.getAttributeName(i) + "=" + xmlStreamReader.getAttributeValue(i)); } } if (lastId != -1) { p = connection .prepareStatement("INSERT INTO tag (id,k,v,reftype) " + "VALUES (?,?,?,?); "); p.setLong(1, lastId); p.setString(2, key); p.setString(3, val); p.setInt(4, lastType.ordinal()); try { p.executeUpdate(); inserts++; } catch (Exception ex) { System.out.println(p.toString()); ex.printStackTrace(); } // batchCount++; p.close(); p = null; } else { System.err.println("ERR0003"); } } else { System.err.println("ERR0002"); } } else if (xmlStreamReader.getName().toString().equalsIgnoreCase("nd")) { if (xpath.peek().equalsIgnoreCase("way") && lastId != -1) { id = -1; for (int i = 0; i < xmlStreamReader.getAttributeCount(); i++) { // System.out.println(xmlStreamReader.getAttributeName(i) + "=" // + xmlStreamReader.getAttributeValue(i)); if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("ref")) { id = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } } if (id != -1) { p = connection .prepareStatement("INSERT INTO way_no (way_id,node_id) " + "VALUES (?,?); "); p.setLong(1, lastId); p.setLong(2, id); try { p.executeUpdate(); inserts++; } catch (Exception ex) { System.out.println(p.toString()); ex.printStackTrace(); } p.close(); p = null; //batchCount++; } } else { System.err.println("ERR0001"); } } else if (xmlStreamReader.getName().toString().equalsIgnoreCase("member")) { if ((xpath.peek().equalsIgnoreCase("relation")) && lastId != -1) { //String type = ""; id = -1; //String role = ""; for (int i = 0; i < xmlStreamReader.getAttributeCount(); i++) { // System.out.println(xmlStreamReader.getAttributeName(i) + "=" // + xmlStreamReader.getAttributeValue(i)); if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("type")) { key = xmlStreamReader.getAttributeValue(i); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("ref")) { id = Long.parseLong(xmlStreamReader.getAttributeValue(i)); } if (xmlStreamReader.getAttributeName(i).equalsIgnoreCase("role")) { val = xmlStreamReader.getAttributeValue(i); } } if (lastId != -1) { p = connection.prepareStatement( "INSERT INTO relation_members (id,type,ref,role) " + "VALUES (?,?,?,?); "); p.setLong(1, lastId); p.setString(2, key); p.setLong(3, id); p.setString(4, val); try { p.executeUpdate(); inserts++; } catch (Exception ex) { System.out.println(p.toString()); ex.printStackTrace(); } p.close(); p = null; // batchCount++; } else { System.err.println("ERR0006"); } } else { System.err.println("ERR0005"); } } else { System.err.println("unhandled node! " + xmlStreamReader.getName().toString()); } break; case XmlPullParser.TEXT: //System.out.print("text!" + xmlStreamReader.getText()); break; case XmlPullParser.END_TAG: //System.out.println("</" + xmlStreamReader.getName().toString() + ">"); if (xmlStreamReader.getName().toString().equalsIgnoreCase("node")) { if (!xpath.isEmpty()) { xpath.pop(); } } if (xmlStreamReader.getName().toString().equalsIgnoreCase("way")) { if (!xpath.isEmpty()) { xpath.pop(); } } if (xmlStreamReader.getName().toString().equalsIgnoreCase("relation")) { if (!xpath.isEmpty()) { xpath.pop(); } } break; default: //do nothing break; } //if (batchCount == 100) { //connection.commit(); // long txPerSecond = (System.currentTimeMillis() - btime); System.out.println((start - System.currentTimeMillis()) + " total elements processed " + recordCount + " inserts " + inserts + " stack " + xpath.size()); //batchCount = 0; //System.gc(); //System.out.println(); // } eventType = xmlStreamReader.next(); } System.out.println(System.currentTimeMillis() - start + "ms"); }