List of usage examples for java.sql PreparedStatement setDouble
void setDouble(int parameterIndex, double x) throws SQLException;
double value. From source file:tinygsn.storage.StorageManager.java
public void executeInsert(CharSequence tableName, DataField[] fields, StreamElement streamElement, Connection connection) throws SQLException { PreparedStatement ps = null; String query = getStatementInsert(tableName, fields).toString(); try {/*w w w . j ava2 s. c o m*/ ps = connection.prepareStatement(query); int counter = 1; for (DataField dataField : fields) { if (dataField.getName().equalsIgnoreCase("timed")) continue; Serializable value = streamElement.getData(dataField.getName()); switch (dataField.getDataTypeID()) { case DataTypes.VARCHAR: if (value == null) ps.setNull(counter, Types.VARCHAR); else ps.setString(counter, value.toString()); break; case DataTypes.CHAR: if (value == null) ps.setNull(counter, Types.CHAR); else ps.setString(counter, value.toString()); break; case DataTypes.INTEGER: if (value == null) ps.setNull(counter, Types.INTEGER); else ps.setInt(counter, ((Number) value).intValue()); break; case DataTypes.SMALLINT: if (value == null) ps.setNull(counter, Types.SMALLINT); else ps.setShort(counter, ((Number) value).shortValue()); break; case DataTypes.TINYINT: if (value == null) ps.setNull(counter, Types.TINYINT); else ps.setByte(counter, ((Number) value).byteValue()); break; case DataTypes.DOUBLE: if (value == null) ps.setNull(counter, Types.DOUBLE); else ps.setDouble(counter, ((Number) value).doubleValue()); break; case DataTypes.BIGINT: if (value == null) ps.setNull(counter, Types.BIGINT); else ps.setLong(counter, ((Number) value).longValue()); break; case DataTypes.BINARY: if (value == null) ps.setNull(counter, Types.BINARY); else ps.setBytes(counter, (byte[]) value); break; default: // logger.error("The type conversion is not supported for : " // + dataField.getName() + "(" + dataField.getDataTypeID() + ") : "); } counter++; } ps.setLong(counter, streamElement.getTimeStamp()); ps.execute(); } catch (GSNRuntimeException e) { // if (e.getType() == // GSNRuntimeException.UNEXPECTED_VIRTUAL_SENSOR_REMOVAL) { // if (logger.isDebugEnabled()) // // logger // .debug( // "An stream element dropped due to unexpected virtual sensor removal. (Stream element: " // + streamElement.toString() + ")+ Query: " + query, e); // } else // logger.warn( // "Inserting a stream element failed : " + streamElement.toString(), e); } catch (SQLException e) { if (e.getMessage().toLowerCase().contains("duplicate entry")) // logger // .info("Error occurred on inserting data to the database, an stream element dropped due to: " // + e.getMessage() // + ". (Stream element: " // + streamElement.toString() + ")+ Query: " + query); // else // logger // .warn("Error occurred on inserting data to the database, an stream element dropped due to: " // + e.getMessage() // + ". (Stream element: " // + streamElement.toString() + ")+ Query: " + query); throw e; } finally { close(ps); } }
From source file:edu.pitt.apollo.db.ApolloDbUtils.java
public void insertDiseaseStateTimeSeries(PreparedStatement pstmt, int runId, int popId, String disease_state, List<Double> ts) throws SQLException, ClassNotFoundException { logger.info("In insertDiseaseStateTimeSeries, runId={}, popId={}, disease_state={}", runId, popId, disease_state);//w w w . ja va 2 s. c om // logger.info("Time Series is:"); try { for (int i = 0; i < ts.size(); i++) { // logger.debug("Time Series[{}] is: {}", i, ts.get(i)); pstmt.setInt(1, runId); pstmt.setInt(2, popId); pstmt.setInt(3, i); pstmt.setDouble(4, ts.get(i)); pstmt.addBatch(); } } catch (SQLException e) { logger.error("Error inserting disease state time series for runId={}, popId={}, disease_state={}", runId, popId, disease_state); throw new SQLException("Error inserting disease state time series for internal run id: " + runId + ", disease state: " + disease_state + ". Specific error was:\n" + e.getMessage()); } }
From source file:de.ingrid.importer.udk.strategy.v32.IDCStrategy3_2_0.java
private void updateDQDatendefizit() throws Exception { log.info("\nUpdating object_data_quality 'Datendefizit'..."); log.info(/* ww w . j av a 2s . c o m*/ "Transfer 'Datendefizit' value from DQ table (object_data_quality) to DQ field (t011_obj_geo.rec_grade) if field is empty ..."); // NOTICE: We do NOT update search index due to same values. // select all relevant entries in DQ Table String sqlSelectDQTable = "select obj_id, result_value from object_data_quality where dq_element_id = 110"; // select according value in DQ Field PreparedStatement psSelectDQField = jdbc .prepareStatement("SELECT rec_grade FROM t011_obj_geo WHERE obj_id = ?"); // update according value in DQ Field PreparedStatement psUpdateDQField = jdbc .prepareStatement("UPDATE t011_obj_geo SET " + "rec_grade = ? " + "WHERE obj_id = ?"); Statement st = jdbc.createStatement(); ResultSet rs = jdbc.executeQuery(sqlSelectDQTable, st); int numProcessed = 0; while (rs.next()) { long objId = rs.getLong("obj_id"); String dqTableValue = rs.getString("result_value"); if (dqTableValue != null) { // read according value from field psSelectDQField.setLong(1, objId); ResultSet rs2 = psSelectDQField.executeQuery(); if (rs2.next()) { // just read it to check if null ! double fieldValue = rs2.getDouble("rec_grade"); boolean fieldValueWasNull = rs2.wasNull(); log.debug("Object id=" + objId + " -> read DQ table value=" + dqTableValue + " / value in field Datendefizit=" + (fieldValueWasNull ? null : fieldValue)); if (fieldValueWasNull) { try { psUpdateDQField.setDouble(1, new Double(dqTableValue)); psUpdateDQField.setLong(2, objId); psUpdateDQField.executeUpdate(); numProcessed++; log.debug("Transferred 'Datendefizit' value '" + dqTableValue + "' from DQ table to field (was empty), obj_id:" + objId); } catch (Exception ex) { String msg = "Problems transferring 'Datendefizit' value '" + dqTableValue + "' from DQ table as DOUBLE to field, value is lost ! obj_id:" + objId; log.error(msg, ex); System.out.println(msg); } } } rs2.close(); } } rs.close(); st.close(); psSelectDQField.close(); psUpdateDQField.close(); log.info("Transferred " + numProcessed + " entries... done"); log.info("Delete 'Datendefizit' values from DQ table (object_data_quality) ..."); sqlStr = "DELETE FROM object_data_quality where dq_element_id = 110"; int numDeleted = jdbc.executeUpdate(sqlStr); log.debug("Deleted " + numDeleted + " entries."); log.info("Updating object_data_quality 'Datendefizit' ... done\n"); }
From source file:org.ramadda.repository.database.DatabaseManager.java
/** * _more_/*from w w w. ja va2 s.c o m*/ * * @param statement _more_ * @param col _more_ * @param value _more_ * @param missing _more_ * * @throws Exception _more_ */ public void setDouble(PreparedStatement statement, int col, double value, double missing) throws Exception { if (Double.isNaN(value) || (value == Double.NEGATIVE_INFINITY) || (value == Double.POSITIVE_INFINITY)) { value = missing; } statement.setDouble(col, value); }
From source file:assignment3.Populate.java
CreateUser() {
try {//from w w w. j a va 2s . c om
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
System.out.println("JDBC Driver Missing");
e.printStackTrace();
return;
}
System.out.println("Oracle JDBC Driver Connected");
Connection conn = null;
try {
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "MAYUR", "123456");
conn.setAutoCommit(false);
PreparedStatement ps = conn
.prepareStatement("insert into yelp_user values (?, ?, ?,?, ?, ?,?, ?, ?,?,?)");
JSONParser parser = new JSONParser();
Object obj = parser.parse(new BufferedReader(new FileReader(
"C:\\Users\\mayur\\Downloads\\YelpDataset\\YelpDataset-CptS451\\yelp_user.json")));
//Object obj = parser.parse(new BufferedReader(new FileReader("C:\\Users\\mayur\\Downloads\\YelpDataset\\YelpDataset-CptS451\\yelp_user1.json")));
JSONArray jsonArray = (JSONArray) (obj);
// JSONArray jsonArray = (JSONArray)(obj1);
for (int i = 0; i < jsonArray.size(); i++) {
JSONObject t = (JSONObject) jsonArray.get(i);
String c = t.get("yelping_since").toString();
Date yelping_since = (Date) java.sql.Date.valueOf(c + "-01");
JSONObject votes = (JSONObject) t.get("votes"); // get all votes details
Long votes_funny = (Long) votes.get("funny");
Long votes_useful = (Long) votes.get("useful");
Long votes_cool = (Long) votes.get("cool");
Long review_count = (Long) t.get("review_count");
String name = t.get("name").toString();
String user_id = t.get("user_id").toString();
JSONArray friends = (JSONArray) (t).get("friends");
int numfriends = 0;
if (friends != null) {
Iterator<String> iterator = friends.iterator();
ArrayList<String> friendid_list = new ArrayList<String>();
while (iterator.hasNext()) {
friendid_list.add(iterator.next());
}
if (friendid_list != null)
numfriends = friendid_list.size();
friendid_list = null;
iterator = null;
}
Long fans = (Long) t.get("fans");
double average_stars = (double) t.get("average_stars");
String type = t.get("type").toString();
ps.setDate(1, yelping_since);
ps.setLong(2, votes_funny);
ps.setLong(3, votes_useful);
ps.setLong(4, votes_cool);
ps.setLong(5, review_count);
ps.setString(6, name);
ps.setString(7, user_id);
ps.setLong(8, fans);
ps.setDouble(9, average_stars);
ps.setString(10, type);
ps.setInt(11, numfriends);
ps.executeUpdate();
System.out.println("Record inserted " + i);
}
conn.commit();
ps.close();
} catch (Exception e) {
System.out.println("Connection Failed! Check output console");
e.printStackTrace();
return;
}
}
From source file:org.apache.phoenix.end2end.DateTimeIT.java
private String initAtable() throws SQLException { String tableName = generateUniqueName(); ensureTableCreated(getUrl(), tableName, ATABLE_NAME, (byte[][]) null, null); PreparedStatement stmt = conn.prepareStatement("upsert into " + tableName + "(" + " ORGANIZATION_ID, " + " ENTITY_ID, " + " A_STRING, " + " B_STRING, " + " A_INTEGER, " + " A_DATE, " + " X_DECIMAL, " + " X_LONG, " + " X_INTEGER," + " Y_INTEGER," + " A_BYTE," + " A_SHORT," + " A_FLOAT," + " A_DOUBLE," + " A_UNSIGNED_FLOAT," + " A_UNSIGNED_DOUBLE)" + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); stmt.setString(1, tenantId);/*ww w . j a v a 2 s.co m*/ stmt.setString(2, ROW1); stmt.setString(3, A_VALUE); stmt.setString(4, B_VALUE); stmt.setInt(5, 1); stmt.setDate(6, date); stmt.setBigDecimal(7, null); stmt.setNull(8, Types.BIGINT); stmt.setNull(9, Types.INTEGER); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 1); stmt.setShort(12, (short) 128); stmt.setFloat(13, 0.01f); stmt.setDouble(14, 0.0001); stmt.setFloat(15, 0.01f); stmt.setDouble(16, 0.0001); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW2); stmt.setString(3, A_VALUE); stmt.setString(4, C_VALUE); stmt.setInt(5, 2); stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 1)); stmt.setBigDecimal(7, null); stmt.setNull(8, Types.BIGINT); stmt.setNull(9, Types.INTEGER); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 2); stmt.setShort(12, (short) 129); stmt.setFloat(13, 0.02f); stmt.setDouble(14, 0.0002); stmt.setFloat(15, 0.02f); stmt.setDouble(16, 0.0002); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW3); stmt.setString(3, A_VALUE); stmt.setString(4, E_VALUE); stmt.setInt(5, 3); stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 2)); stmt.setBigDecimal(7, null); stmt.setNull(8, Types.BIGINT); stmt.setNull(9, Types.INTEGER); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 3); stmt.setShort(12, (short) 130); stmt.setFloat(13, 0.03f); stmt.setDouble(14, 0.0003); stmt.setFloat(15, 0.03f); stmt.setDouble(16, 0.0003); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW4); stmt.setString(3, A_VALUE); stmt.setString(4, B_VALUE); stmt.setInt(5, 4); stmt.setDate(6, date == null ? null : date); stmt.setBigDecimal(7, null); stmt.setNull(8, Types.BIGINT); stmt.setNull(9, Types.INTEGER); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 4); stmt.setShort(12, (short) 131); stmt.setFloat(13, 0.04f); stmt.setDouble(14, 0.0004); stmt.setFloat(15, 0.04f); stmt.setDouble(16, 0.0004); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW5); stmt.setString(3, B_VALUE); stmt.setString(4, C_VALUE); stmt.setInt(5, 5); stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 1)); stmt.setBigDecimal(7, null); stmt.setNull(8, Types.BIGINT); stmt.setNull(9, Types.INTEGER); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 5); stmt.setShort(12, (short) 132); stmt.setFloat(13, 0.05f); stmt.setDouble(14, 0.0005); stmt.setFloat(15, 0.05f); stmt.setDouble(16, 0.0005); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW6); stmt.setString(3, B_VALUE); stmt.setString(4, E_VALUE); stmt.setInt(5, 6); stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 2)); stmt.setBigDecimal(7, null); stmt.setNull(8, Types.BIGINT); stmt.setNull(9, Types.INTEGER); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 6); stmt.setShort(12, (short) 133); stmt.setFloat(13, 0.06f); stmt.setDouble(14, 0.0006); stmt.setFloat(15, 0.06f); stmt.setDouble(16, 0.0006); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW7); stmt.setString(3, B_VALUE); stmt.setString(4, B_VALUE); stmt.setInt(5, 7); stmt.setDate(6, date == null ? null : date); stmt.setBigDecimal(7, BigDecimal.valueOf(0.1)); stmt.setLong(8, 5L); stmt.setInt(9, 5); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 7); stmt.setShort(12, (short) 134); stmt.setFloat(13, 0.07f); stmt.setDouble(14, 0.0007); stmt.setFloat(15, 0.07f); stmt.setDouble(16, 0.0007); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW8); stmt.setString(3, B_VALUE); stmt.setString(4, C_VALUE); stmt.setInt(5, 8); stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 1)); stmt.setBigDecimal(7, BigDecimal.valueOf(3.9)); long l = Integer.MIN_VALUE - 1L; assert (l < Integer.MIN_VALUE); stmt.setLong(8, l); stmt.setInt(9, 4); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 8); stmt.setShort(12, (short) 135); stmt.setFloat(13, 0.08f); stmt.setDouble(14, 0.0008); stmt.setFloat(15, 0.08f); stmt.setDouble(16, 0.0008); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW9); stmt.setString(3, C_VALUE); stmt.setString(4, E_VALUE); stmt.setInt(5, 9); stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 2)); stmt.setBigDecimal(7, BigDecimal.valueOf(3.3)); l = Integer.MAX_VALUE + 1L; assert (l > Integer.MAX_VALUE); stmt.setLong(8, l); stmt.setInt(9, 3); stmt.setInt(10, 300); stmt.setByte(11, (byte) 9); stmt.setShort(12, (short) 0); stmt.setFloat(13, 0.09f); stmt.setDouble(14, 0.0009); stmt.setFloat(15, 0.09f); stmt.setDouble(16, 0.0009); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW10); stmt.setString(3, B_VALUE); stmt.setString(4, B_VALUE); stmt.setInt(5, 7); // Intentionally null stmt.setDate(6, null); stmt.setBigDecimal(7, BigDecimal.valueOf(0.1)); stmt.setLong(8, 5L); stmt.setInt(9, 5); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 7); stmt.setShort(12, (short) 134); stmt.setFloat(13, 0.07f); stmt.setDouble(14, 0.0007); stmt.setFloat(15, 0.07f); stmt.setDouble(16, 0.0007); stmt.execute(); conn.commit(); return tableName; }
From source file:edu.pitt.apollo.db.ApolloDbUtils.java
public void insertDiseaseStateTimeSeriesNegative(PreparedStatement pstmt, int runId, int popId, String disease_state, List<Double> ts) throws SQLException, ClassNotFoundException { logger.info("In insertDiseaseStateTimeSeries, runId={}, popId={}, disease_state={}", runId, popId, disease_state);/*from ww w . j av a 2 s .co m*/ // logger.info("Time Series is:"); try { int counter = 0; for (int i = -ts.size(); i < 0; i++) { // logger.debug("Time Series[{}] is: {}", i, ts.get(i)); pstmt.setInt(1, runId); pstmt.setInt(2, popId); pstmt.setInt(3, i); pstmt.setDouble(4, ts.get(counter)); pstmt.addBatch(); counter++; } } catch (SQLException e) { logger.error("Error inserting disease state time series for runId={}, popId={}, disease_state={}", runId, popId, disease_state); throw new SQLException("Error inserting disease state time series for internal run id: " + runId + ", disease state: " + disease_state + ". Specific error was:\n" + e.getMessage()); } }
From source file:org.wso2.carbon.social.sql.SQLActivityPublisher.java
/** * Use this method to update rating-cache if we remove the attached comment * activity// w w w .j a va2s .c o m * * @param activityId * @return boolean * @throws SQLException * @throws SocialActivityException */ private boolean removeRating(int activityId, Connection connection, String userId) throws SQLException, JsonSyntaxException, SocialActivityException { ResultSet selectResultSet; ResultSet cacheResultSet; PreparedStatement selectStatement; PreparedStatement CacheStatement; PreparedStatement updateCacheStatement; try { if (log.isDebugEnabled()) { log.debug("Executing: " + COMMENT_ACTIVITY_SELECT_SQL); } selectStatement = connection.prepareStatement(COMMENT_ACTIVITY_SELECT_SQL); selectStatement.setInt(1, activityId); selectResultSet = selectStatement.executeQuery(); if (!selectResultSet.next()) { log.error("Unable to remove rating for the given activity : " + activityId); return false; } else { JsonObject body; body = (JsonObject) parser.parse(selectResultSet.getString(Constants.BODY_COLUMN)); Activity activity = new SQLActivity(body); String actorId = activity.getActorId(); if (!actorId.equals(userId)) { if (log.isDebugEnabled()) { log.debug("User: " + userId + " not authorized to perform activity remove action."); } throw new SocialActivityException( "User: " + userId + " not authorized to perform activity remove action."); } int rating = activity.getRating(); if (rating > 0) { // reduce this rating value from target String targetId = activity.getTargetId(); if (log.isDebugEnabled()) { log.debug("Executing: " + SELECT_CACHE_SQL); } CacheStatement = connection.prepareStatement(SELECT_CACHE_SQL); CacheStatement.setString(1, targetId); cacheResultSet = CacheStatement.executeQuery(); if (cacheResultSet.next()) { int total, count; total = cacheResultSet.getInt(Constants.RATING_TOTAL); count = cacheResultSet.getInt(Constants.RATING_COUNT); if (log.isDebugEnabled()) { log.debug("Executing: " + UPDATE_CACHE_SQL); } updateCacheStatement = connection.prepareStatement(UPDATE_CACHE_SQL); updateCacheStatement.setInt(1, total - rating); updateCacheStatement.setInt(2, count - 1); updateCacheStatement.setDouble(3, (double) total - rating / count - 1); updateCacheStatement.setString(4, targetId); updateCacheStatement.executeUpdate(); } cacheResultSet.close(); } selectResultSet.close(); return true; } } catch (SQLException e) { log.error("Unable to update the rating cache. " + e.getMessage(), e); throw e; } catch (JsonSyntaxException e) { log.error("Malformed JSON element found: " + e.getMessage(), e); throw e; } }
From source file:ro.nextreports.engine.queryexec.QueryExecutor.java
private void setParameterValue(PreparedStatement pstmt, Class paramValueClass, Object paramValue, int index) throws SQLException, QueryException { // for "NOT IN (?)" setting null -> result is undeterminated // ParameterUtil.NULL was good only for list of strings (for NOT IN)! if (ParameterUtil.NULL.equals(paramValue)) { paramValue = null;/* www . j av a 2s . c o m*/ } if (paramValueClass.equals(Object.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.JAVA_OBJECT); } else { if (paramValue instanceof IdName) { pstmt.setObject(index + 1, ((IdName) paramValue).getId()); } else { pstmt.setObject(index + 1, paramValue); } } } else if (paramValueClass.equals(Boolean.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.BIT); } else { if (paramValue instanceof IdName) { pstmt.setBoolean(index + 1, (Boolean) ((IdName) paramValue).getId()); } else { pstmt.setBoolean(index + 1, (Boolean) paramValue); } } } else if (paramValueClass.equals(Byte.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.TINYINT); } else { if (paramValue instanceof IdName) { pstmt.setByte(index + 1, (Byte) ((IdName) paramValue).getId()); } else { pstmt.setByte(index + 1, (Byte) paramValue); } } } else if (paramValueClass.equals(Double.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.DOUBLE); } else { if (paramValue instanceof IdName) { pstmt.setDouble(index + 1, (Double) ((IdName) paramValue).getId()); } else { pstmt.setDouble(index + 1, (Double) paramValue); } } } else if (paramValueClass.equals(Float.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.FLOAT); } else { if (paramValue instanceof IdName) { pstmt.setFloat(index + 1, (Float) ((IdName) paramValue).getId()); } else { pstmt.setFloat(index + 1, (Float) paramValue); } } } else if (paramValueClass.equals(Integer.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.INTEGER); } else { if (paramValue instanceof IdName) { pstmt.setObject(index + 1, ((IdName) paramValue).getId()); } else { pstmt.setInt(index + 1, (Integer) paramValue); } } } else if (paramValueClass.equals(Long.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.BIGINT); } else { if (paramValue instanceof IdName) { pstmt.setLong(index + 1, (Long) ((IdName) paramValue).getId()); } else { pstmt.setLong(index + 1, (Long) paramValue); } } } else if (paramValueClass.equals(Short.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.SMALLINT); } else { if (paramValue instanceof IdName) { pstmt.setShort(index + 1, (Short) ((IdName) paramValue).getId()); } else { pstmt.setShort(index + 1, (Short) paramValue); } } //@todo // ParameterUtil -> values are taken from dialect (where there is no BigDecimal yet!) // or from meta data } else if (paramValueClass.equals(BigDecimal.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.DECIMAL); } else { if (paramValue instanceof IdName) { Serializable ser = ((IdName) paramValue).getId(); if (ser instanceof BigDecimal) { pstmt.setBigDecimal(index + 1, (BigDecimal) (ser)); } else { pstmt.setInt(index + 1, (Integer) (ser)); } } else { // a simple value cannot be cast to BigDecimal! pstmt.setObject(index + 1, paramValue); } } } else if (paramValueClass.equals(BigInteger.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.BIGINT); } else { if (paramValue instanceof IdName) { Serializable ser = ((IdName) paramValue).getId(); if (ser instanceof BigInteger) { pstmt.setBigDecimal(index + 1, new BigDecimal((BigInteger) (ser))); } else if (ser instanceof BigDecimal) { pstmt.setBigDecimal(index + 1, (BigDecimal) (ser)); } else { pstmt.setInt(index + 1, (Integer) (ser)); } } else { // a simple value cannot be cast to BigDecimal! pstmt.setObject(index + 1, paramValue); } } } else if (paramValueClass.equals(String.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.VARCHAR); } else { if (paramValue instanceof IdName) { if (((IdName) paramValue).getId() == null) { pstmt.setNull(index + 1, Types.VARCHAR); } else { pstmt.setString(index + 1, ((IdName) paramValue).getId().toString()); } } else { pstmt.setString(index + 1, paramValue.toString()); } } } else if (paramValueClass.equals(Date.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.DATE); } else { if (paramValue instanceof IdName) { Serializable obj = ((IdName) paramValue).getId(); Date date; if (obj instanceof String) { try { date = IdNameRenderer.sdf.parse((String) obj); } catch (ParseException e) { e.printStackTrace(); LOG.error(e.getMessage(), e); date = new Date(); } } else { date = (Date) obj; } pstmt.setDate(index + 1, new java.sql.Date(date.getTime())); } else { pstmt.setDate(index + 1, new java.sql.Date(((Date) paramValue).getTime())); } } } else if (paramValueClass.equals(Timestamp.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.TIMESTAMP); } else { if (paramValue instanceof IdName) { Serializable obj = ((IdName) paramValue).getId(); Date date; if (obj instanceof String) { try { date = IdNameRenderer.sdf.parse((String) obj); } catch (ParseException e) { e.printStackTrace(); LOG.error(e.getMessage(), e); date = new Date(); } } else { date = (Date) obj; } pstmt.setTimestamp(index + 1, new Timestamp(date.getTime())); } else { pstmt.setTimestamp(index + 1, new Timestamp(((Date) paramValue).getTime())); } } } else if (paramValueClass.equals(Time.class)) { if (paramValue == null) { pstmt.setNull(index + 1, Types.TIME); } else { if (paramValue instanceof IdName) { Serializable obj = ((IdName) paramValue).getId(); Date date; if (obj instanceof String) { try { date = IdNameRenderer.sdf.parse((String) obj); } catch (ParseException e) { e.printStackTrace(); LOG.error(e.getMessage(), e); date = new Date(); } } else { date = (Date) obj; } pstmt.setTime(index + 1, new Time(date.getTime())); } else { pstmt.setTime(index + 1, new Time(((Date) paramValue).getTime())); } } } else { throw new QueryException("Parameter type " + paramValueClass.getName() + " not supported in query"); } // for logSql() statementParameters.put(index, paramValue); }
From source file:dk.netarkivet.harvester.datamodel.RunningJobsInfoDBDAO.java
/** * Stores a {@link StartedJobInfo} record to the persistent storage. * The record is stored in the monitor table, and if the elapsed time since * the last history sample is equal or superior to the history sample rate, * also to the history table./*from w w w . j a v a2s .com*/ * @param startedJobInfo the record to store. */ @Override public synchronized void store(StartedJobInfo startedJobInfo) { ArgumentNotValid.checkNotNull(startedJobInfo, "StartedJobInfo startedJobInfo"); Connection c = HarvestDBConnection.get(); try { PreparedStatement stm = null; // First is there a record in the monitor table? boolean update = false; try { stm = c.prepareStatement( "SELECT jobId FROM runningJobsMonitor" + " WHERE jobId=? AND harvestName=?"); stm.setLong(1, startedJobInfo.getJobId()); stm.setString(2, startedJobInfo.getHarvestName()); // One row expected, as per PK definition update = stm.executeQuery().next(); } catch (SQLException e) { String message = "SQL error checking running jobs monitor table" + "\n" + ExceptionUtils.getSQLExceptionCause(e); log.warn(message, e); throw new IOFailure(message, e); } try { // Update or insert latest progress information for this job c.setAutoCommit(false); StringBuffer sql = new StringBuffer(); if (update) { sql.append("UPDATE runningJobsMonitor SET "); StringBuffer columns = new StringBuffer(); for (HM_COLUMN setCol : HM_COLUMN.values()) { columns.append(setCol.name() + "=?, "); } sql.append(columns.substring(0, columns.lastIndexOf(","))); sql.append(" WHERE jobId=? AND harvestName=?"); } else { sql.append("INSERT INTO runningJobsMonitor ("); sql.append(HM_COLUMN.getColumnsInOrder()); sql.append(") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); } stm = c.prepareStatement(sql.toString()); stm.setLong(HM_COLUMN.jobId.rank(), startedJobInfo.getJobId()); stm.setString(HM_COLUMN.harvestName.rank(), startedJobInfo.getHarvestName()); stm.setLong(HM_COLUMN.elapsedSeconds.rank(), startedJobInfo.getElapsedSeconds()); stm.setString(HM_COLUMN.hostUrl.rank(), startedJobInfo.getHostUrl()); stm.setDouble(HM_COLUMN.progress.rank(), startedJobInfo.getProgress()); stm.setLong(HM_COLUMN.queuedFilesCount.rank(), startedJobInfo.getQueuedFilesCount()); stm.setLong(HM_COLUMN.totalQueuesCount.rank(), startedJobInfo.getTotalQueuesCount()); stm.setLong(HM_COLUMN.activeQueuesCount.rank(), startedJobInfo.getActiveQueuesCount()); stm.setLong(HM_COLUMN.retiredQueuesCount.rank(), startedJobInfo.getRetiredQueuesCount()); stm.setLong(HM_COLUMN.exhaustedQueuesCount.rank(), startedJobInfo.getExhaustedQueuesCount()); stm.setLong(HM_COLUMN.alertsCount.rank(), startedJobInfo.getAlertsCount()); stm.setLong(HM_COLUMN.downloadedFilesCount.rank(), startedJobInfo.getDownloadedFilesCount()); stm.setLong(HM_COLUMN.currentProcessedKBPerSec.rank(), startedJobInfo.getCurrentProcessedKBPerSec()); stm.setLong(HM_COLUMN.processedKBPerSec.rank(), startedJobInfo.getProcessedKBPerSec()); stm.setDouble(HM_COLUMN.currentProcessedDocsPerSec.rank(), startedJobInfo.getCurrentProcessedDocsPerSec()); stm.setDouble(HM_COLUMN.processedDocsPerSec.rank(), startedJobInfo.getProcessedDocsPerSec()); stm.setInt(HM_COLUMN.activeToeCount.rank(), startedJobInfo.getActiveToeCount()); stm.setInt(HM_COLUMN.status.rank(), startedJobInfo.getStatus().ordinal()); stm.setTimestamp(HM_COLUMN.tstamp.rank(), new Timestamp(startedJobInfo.getTimestamp().getTime())); if (update) { stm.setLong(HM_COLUMN.values().length + 1, startedJobInfo.getJobId()); stm.setString(HM_COLUMN.values().length + 2, startedJobInfo.getHarvestName()); } stm.executeUpdate(); c.commit(); } catch (SQLException e) { String message = "SQL error storing started job info " + startedJobInfo + " in monitor table" + "\n" + ExceptionUtils.getSQLExceptionCause(e); log.warn(message, e); throw new IOFailure(message, e); } finally { DBUtils.closeStatementIfOpen(stm); DBUtils.rollbackIfNeeded(c, "store started job info", startedJobInfo); } // Should we store an history record? Long lastHistoryStore = lastSampleDateByJobId.get(startedJobInfo.getJobId()); long time = System.currentTimeMillis(); boolean shouldSample = lastHistoryStore == null || time >= lastHistoryStore + HISTORY_SAMPLE_RATE; if (!shouldSample) { return; // we're done } try { c.setAutoCommit(false); stm = c.prepareStatement("INSERT INTO runningJobsHistory (" + HM_COLUMN.getColumnsInOrder() + ") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); stm.setLong(HM_COLUMN.jobId.rank(), startedJobInfo.getJobId()); stm.setString(HM_COLUMN.harvestName.rank(), startedJobInfo.getHarvestName()); stm.setLong(HM_COLUMN.elapsedSeconds.rank(), startedJobInfo.getElapsedSeconds()); stm.setString(HM_COLUMN.hostUrl.rank(), startedJobInfo.getHostUrl()); stm.setDouble(HM_COLUMN.progress.rank(), startedJobInfo.getProgress()); stm.setLong(HM_COLUMN.queuedFilesCount.rank(), startedJobInfo.getQueuedFilesCount()); stm.setLong(HM_COLUMN.totalQueuesCount.rank(), startedJobInfo.getTotalQueuesCount()); stm.setLong(HM_COLUMN.activeQueuesCount.rank(), startedJobInfo.getActiveQueuesCount()); stm.setLong(HM_COLUMN.retiredQueuesCount.rank(), startedJobInfo.getRetiredQueuesCount()); stm.setLong(HM_COLUMN.exhaustedQueuesCount.rank(), startedJobInfo.getExhaustedQueuesCount()); stm.setLong(HM_COLUMN.alertsCount.rank(), startedJobInfo.getAlertsCount()); stm.setLong(HM_COLUMN.downloadedFilesCount.rank(), startedJobInfo.getDownloadedFilesCount()); stm.setLong(HM_COLUMN.currentProcessedKBPerSec.rank(), startedJobInfo.getCurrentProcessedKBPerSec()); stm.setLong(HM_COLUMN.processedKBPerSec.rank(), startedJobInfo.getProcessedKBPerSec()); stm.setDouble(HM_COLUMN.currentProcessedDocsPerSec.rank(), startedJobInfo.getCurrentProcessedDocsPerSec()); stm.setDouble(HM_COLUMN.processedDocsPerSec.rank(), startedJobInfo.getProcessedDocsPerSec()); stm.setInt(HM_COLUMN.activeToeCount.rank(), startedJobInfo.getActiveToeCount()); stm.setInt(HM_COLUMN.status.rank(), startedJobInfo.getStatus().ordinal()); stm.setTimestamp(HM_COLUMN.tstamp.rank(), new Timestamp(startedJobInfo.getTimestamp().getTime())); stm.executeUpdate(); c.commit(); } catch (SQLException e) { String message = "SQL error storing started job info " + startedJobInfo + " in history table" + "\n" + ExceptionUtils.getSQLExceptionCause(e); log.warn(message, e); throw new IOFailure(message, e); } finally { DBUtils.closeStatementIfOpen(stm); DBUtils.rollbackIfNeeded(c, "store started job info", startedJobInfo); } // Remember last sampling date lastSampleDateByJobId.put(startedJobInfo.getJobId(), time); } finally { HarvestDBConnection.release(c); } }