List of usage examples for java.sql PreparedStatement clearParameters
void clearParameters() throws SQLException;
From source file:org.sakaiproject.search.indexer.impl.SearchBuilderQueueManager.java
/** * get the Instance Master/*from w w w. j a va 2 s. com*/ * * @return * @throws SQLException */ private SearchBuilderItem getMasterItem(Connection connection) throws SQLException { log.debug("get Master Items with " + connection); //$NON-NLS-1$ PreparedStatement pst = null; PreparedStatement lockMaster = null; ResultSet rst = null; try { lockMaster = connection.prepareStatement("update " + SEARCH_BUILDER_ITEM_T + " set searchstate = ? where itemscope = ? and searchstate = ? "); lockMaster.clearParameters(); lockMaster.setInt(1, nodeLock); lockMaster.setInt(2, SearchBuilderItem.ITEM_GLOBAL_MASTER.intValue()); lockMaster.setInt(3, SearchBuilderItem.STATE_PENDING.intValue()); lockMaster.executeUpdate(); pst = connection.prepareStatement("select " //$NON-NLS-1$ + SEARCH_BUILDER_ITEM_FIELDS + " from " //$NON-NLS-1$ + SEARCH_BUILDER_ITEM_T + " where itemscope = ? and searchstate = ? "); //$NON-NLS-1$ pst.clearParameters(); pst.setInt(1, SearchBuilderItem.ITEM_GLOBAL_MASTER.intValue()); pst.setInt(2, nodeLock); rst = pst.executeQuery(); SearchBuilderItemImpl sbi = new SearchBuilderItemImpl(); if (rst.next()) { populateSearchBuilderItem(rst, sbi); sbi.setLock(nodeLock); log.info("Locked Master item to this node " + sbi); rst.close(); connection.commit(); } else { rst.close(); connection.rollback(); sbi.setName(SearchBuilderItem.INDEX_MASTER); sbi.setContext(SearchBuilderItem.GLOBAL_CONTEXT); sbi.setSearchaction(SearchBuilderItem.ACTION_UNKNOWN); sbi.setSearchstate(SearchBuilderItem.STATE_UNKNOWN); sbi.setItemscope(SearchBuilderItem.ITEM_GLOBAL_MASTER); } return sbi; } finally { try { if (rst != null) rst.close(); } catch (Exception ex) { log.warn("Error closing rst", ex); } try { if (pst != null) pst.close(); } catch (Exception ex) { log.warn("Error closing pst", ex); } try { lockMaster.close(); } catch (Exception ex) { log.warn("Error closing lockMaster", ex); } } }
From source file:org.sakaiproject.dash.jobs.DashAggregateJob.java
private String startJob() throws SQLException { List<Event> eventsQueue = new ArrayList<Event>(); long counter = 0; long offset = 0; long lastProcessedEventId = 0; long lastProcessedEventIdWithSuccess = 0; long firstEventIdProcessed = -1; long firstEventIdProcessedInBlock = -1; Date lastEventDate = null;// w ww . j a v a 2 s . com Date lastEventDateWithSuccess = null; boolean abortIteration = false; long start = System.currentTimeMillis(); boolean sqlError = false; String returnMessage = null; Connection connection = getEventDbConnection(); long eventIdLowerLimit = getEventIdLowerLimit(); PreparedStatement st = null; ResultSet rs = null; try { st = connection.prepareStatement(sqlGetEvent); rs = null; while (!abortIteration) { abortIteration = true; st.clearParameters(); if (!isOracle) { if (firstEventIdProcessed == -1) offset = eventIdLowerLimit; st.setLong(1, offset); // MySQL >= startId st.setLong(2, sqlBlockSize + offset); // MySQL < endId } else { st.setLong(1, eventIdLowerLimit); // Oracle lower limit st.setLong(2, offset); // Oracle offset st.setLong(3, sqlBlockSize + offset); // Oracle limit } rs = st.executeQuery(); while (rs.next()) { abortIteration = false; Date date = null; String event = null; String ref = null; String context = null; String sessionUser = null; String sessionId = null; try { //If an exception is launched, iteration is not aborted but no event is added to event queue date = new Date(rs.getTimestamp("EVENT_DATE").getTime()); event = rs.getString("EVENT"); ref = rs.getString("REF"); sessionUser = rs.getString("SESSION_USER"); sessionId = rs.getString("SESSION_ID"); if (isEventContextSupported) context = rs.getString("CONTEXT"); EventCopy eventcopy = new EventCopy(date, event, ref, context, sessionUser, sessionId, ' ', 0); eventsQueue.add(eventcopy); counter++; lastProcessedEventId = rs.getInt("EVENT_ID"); lastEventDate = date; if (firstEventIdProcessed == -1) firstEventIdProcessed = jobRun.getStartEventId(); //was: lastProcessedEventId; if (firstEventIdProcessedInBlock == -1) firstEventIdProcessedInBlock = lastProcessedEventId; } catch (Exception e) { if (LOG.isDebugEnabled()) LOG.debug("Ignoring " + event + ", " + ref + ", " + date + ", " + sessionUser + ", " + sessionId + " due to: " + e.toString()); } } rs.close(); if (!abortIteration) { // process events boolean processedOk = true; LOG.info("Found " + counter + " events, starting processing."); for (Event event : eventsQueue) { if (LOG.isDebugEnabled()) { LOG.debug("Dashboard Event Processing Thread is processing event: " + event.getEvent()); } //TODO: This seems efficient to retrieve, may want to cache event processors for each event? EventProcessor eventProcessor = dashboardLogic.getEventProcessor(event.getEvent()); SecurityAdvisor advisor = new DashboardAggregateSecurityAdvisor(); sakaiProxy.pushSecurityAdvisor(advisor); try { if (eventProcessor != null) { eventProcessor.processEvent(event); LOG.debug("Event " + event.getEvent() + " successfully processed."); } else { LOG.debug("No processor to process event " + event.getEvent()); } } catch (Exception e) { LOG.warn("Error processing event: " + event, e); processedOk = false; } finally { sakaiProxy.popSecurityAdvisor(advisor); sakaiProxy.clearThreadLocalCache(); } } eventsQueue.clear(); if (processedOk) { lastProcessedEventIdWithSuccess = lastProcessedEventId; lastEventDateWithSuccess = lastEventDate; jobRun.setStartEventId(firstEventIdProcessed); jobRun.setEndEventId(lastProcessedEventIdWithSuccess); jobRun.setLastEventDate(lastEventDateWithSuccess); jobRun.setJobEndDate(new Date(System.currentTimeMillis())); saveJobRun(jobRun); firstEventIdProcessedInBlock = -1; if (counter >= getMaxEventsPerRun()) { abortIteration = true; } else if (counter + sqlBlockSize < getMaxEventsPerRun()) { offset += sqlBlockSize; } else { offset += getMaxEventsPerRun() - counter; } } else { returnMessage = "An error occurred while processing/persisting events to db. Please check your logs, fix possible problems and re-run this job (will start after last successful processed event)."; LOG.error(returnMessage); throw new Exception(returnMessage); } } } } catch (SQLException e) { sqlError = true; if (returnMessage == null) { returnMessage = "Unable to retrieve events due to: " + e.getMessage(); LOG.error("Unable to retrieve events", e); } } catch (Exception e) { sqlError = true; if (returnMessage == null) { returnMessage = "Unable to retrieve events due to: " + e.getMessage(); LOG.error("Unable to retrieve events due to an unknown cause", e); } } finally { try { if (rs != null) rs.close(); } finally { try { if (st != null) st.close(); } finally { closeEventDbConnection(connection); } } } // error occurred if (sqlError) { return returnMessage; } long processingTime = (System.currentTimeMillis() - start) / 1000; if (firstEventIdProcessed == -1 && jobRun != null) { // no data was processed: do not persist to DB // long eventId = jobRun.getEndEventId(); // firstEventIdProcessed = eventId; // lastProcessedEventIdWithSuccess = eventId; // jobRun.setEndEventId(lastProcessedEventId > 0 ? lastProcessedEventId : jobRun.getStartEventId()); // jobRun.setLastEventDate(lastEventDate != null ? lastEventDate : null); // jobRun.setJobEndDate(new Date(System.currentTimeMillis())); return "0 events processed in " + processingTime + "s (no entry will be added to DASH_JOB_RUN; only events associated with a session are processed)"; } else { saveJobRun(jobRun); } return counter + " events processed (ids: " + firstEventIdProcessed + " - " + lastProcessedEventIdWithSuccess + ") in " + processingTime + "s (only events associated with a session are processed)"; }
From source file:org.apache.hadoop.yarn.server.applicationhistoryservice.metrics.timeline.PhoenixHBaseAccessor.java
public void insertMetricRecords(TimelineMetrics metrics) throws SQLException, IOException { List<TimelineMetric> timelineMetrics = metrics.getMetrics(); if (timelineMetrics == null || timelineMetrics.isEmpty()) { LOG.debug("Empty metrics insert request."); return;/* w w w .j a v a 2 s. c o m*/ } Connection conn = getConnection(); PreparedStatement metricRecordStmt = null; long currentTime = System.currentTimeMillis(); try { metricRecordStmt = conn.prepareStatement(String.format(UPSERT_METRICS_SQL, METRICS_RECORD_TABLE_NAME)); for (TimelineMetric metric : timelineMetrics) { metricRecordStmt.clearParameters(); if (LOG.isTraceEnabled()) { LOG.trace("host: " + metric.getHostName() + ", " + "metricName = " + metric.getMetricName() + ", " + "values: " + metric.getMetricValues()); } double[] aggregates = AggregatorUtils.calculateAggregates(metric.getMetricValues()); metricRecordStmt.setString(1, metric.getMetricName()); metricRecordStmt.setString(2, metric.getHostName()); metricRecordStmt.setString(3, metric.getAppId()); metricRecordStmt.setString(4, metric.getInstanceId()); metricRecordStmt.setLong(5, currentTime); metricRecordStmt.setLong(6, metric.getStartTime()); metricRecordStmt.setString(7, metric.getType()); metricRecordStmt.setDouble(8, aggregates[0]); metricRecordStmt.setDouble(9, aggregates[1]); metricRecordStmt.setDouble(10, aggregates[2]); metricRecordStmt.setLong(11, (long) aggregates[3]); String json = TimelineUtils.dumpTimelineRecordtoJSON(metric.getMetricValues()); metricRecordStmt.setString(12, json); try { metricRecordStmt.executeUpdate(); } catch (SQLException sql) { LOG.error(sql); } } conn.commit(); } finally { if (metricRecordStmt != null) { try { metricRecordStmt.close(); } catch (SQLException e) { // Ignore } } if (conn != null) { try { conn.close(); } catch (SQLException sql) { // Ignore } } } }
From source file:com.agiletec.plugins.jpcrowdsourcing.aps.system.services.idea.IdeaDAO.java
private void addTagsRelationsRecord(IIdea idea, PreparedStatement stat) throws ApsSystemException { if (idea.getTags().size() > 0) { try {/*from ww w. jav a 2 s . c om*/ Iterator<String> codeIter = idea.getTags().iterator(); while (codeIter.hasNext()) { String code = codeIter.next(); int i = 1; stat.setString(i++, idea.getId()); stat.setString(i++, code); stat.addBatch(); stat.clearParameters(); } } catch (SQLException e) { _logger.error("Errore in aggiunta record tabella collaboration_idea_tags {}", idea.getId(), e); throw new RuntimeException("Errore in aggiunta record tabella collaboration_idea_tags", e); } } }
From source file:org.sakaiproject.search.optimize.shared.impl.DbJournalOptimizationManager.java
/** * @see org.sakaiproject.search.journal.api.JournalManager#commitSave(org.sakaiproject.search.journal.api.JournalManagerState) *//*from www. ja va 2 s . c o m*/ public void commitSave(JournalManagerState jms) throws IndexJournalException { OptimizeJournalManagerStateImpl ojms = (OptimizeJournalManagerStateImpl) jms; PreparedStatement success = null; PreparedStatement updateTarget = null; Connection connection = null; try { System.err.println("+++++++++++++++++++++COMMIT+++++++++++++++"); connection = datasource.getConnection(); // set the target to committed and then delete the rest // so the final segment becomes commtted with a writer id of // this+txiD, // and all merging-prepare states in this transaction are removed. updateTarget = connection .prepareStatement("update search_journal set status = 'committed', txts = ? where txid = ? "); updateTarget.clearParameters(); updateTarget.setLong(1, System.currentTimeMillis()); updateTarget.setLong(2, ojms.oldestSavePoint); int i = updateTarget.executeUpdate(); // clear out all others success = connection.prepareStatement( "delete from search_journal where indexwriter = ? and status = 'merging-prepare' "); success.clearParameters(); success.setString(1, ojms.indexWriter); success.executeUpdate(); connection.commit(); log.info("Shared Journal Mege Committed into SavePoint " + ojms.oldestSavePoint); } catch (Exception ex) { try { connection.rollback(); } catch (Exception ex2) { log.debug(ex2); } throw new IndexJournalException("Failed to commit index ", ex); } finally { try { updateTarget.close(); } catch (Exception ex) { log.debug(ex); } try { success.close(); } catch (Exception ex) { log.debug(ex); } try { connection.close(); } catch (Exception ex) { log.debug(ex); } } }
From source file:net.dv8tion.discord.commands.TodoCommand.java
public TodoCommand(JDA api) { this.api = api; try {/*from ww w .j a v a 2 s. c o m*/ ResultSet sqlTodoLists = Database.getInstance().getStatement(GET_TODO_LISTS).executeQuery(); while (sqlTodoLists.next()) { String label = sqlTodoLists.getString(2); TodoList todoList = new TodoList(sqlTodoLists.getInt(1), //Id label, sqlTodoLists.getString(3), //OwnerId sqlTodoLists.getBoolean(4) //Locked ); todoLists.put(label, todoList); PreparedStatement getEntries = Database.getInstance().getStatement(GET_TODO_ENTRIES); getEntries.setInt(1, todoList.id); ResultSet sqlTodoEntries = getEntries.executeQuery(); while (sqlTodoEntries.next()) { TodoEntry todoEntry = new TodoEntry(sqlTodoEntries.getInt(1), //Id sqlTodoEntries.getString(2), //Content sqlTodoEntries.getBoolean(3) //Checked ); todoList.entries.add(todoEntry); } getEntries.clearParameters(); PreparedStatement getUsers = Database.getInstance().getStatement(GET_TODO_USERS); getUsers.setInt(1, todoList.id); ResultSet sqlTodoUsers = getUsers.executeQuery(); while (sqlTodoUsers.next()) { todoList.allowedUsers.add(sqlTodoUsers.getString(1)); //UserId } getUsers.clearParameters(); } } catch (SQLException e) { e.printStackTrace(); } }
From source file:org.sakaiproject.search.indexer.impl.SearchBuilderQueueManager.java
private void save(Connection connection, SearchBuilderItem sbi) throws SQLException { PreparedStatement pst = null; try {//from w ww.j a v a 2s. com pst = connection.prepareStatement(" insert into " //$NON-NLS-1$ + SEARCH_BUILDER_ITEM_T + " ( " //$NON-NLS-1$ + SEARCH_BUILDER_ITEM_FIELDS + " ) values ( " //$NON-NLS-1$ + SEARCH_BUILDER_ITEM_FIELDS_PARAMS + " ) "); //$NON-NLS-1$ pst.clearParameters(); populateStatement(pst, sbi); pst.executeUpdate(); } finally { try { pst.close(); } catch (Exception ex) { log.warn("Error closing pst", ex); } } }
From source file:org.apache.jmeter.protocol.jdbc.sampler.JDBCSampler.java
private PreparedStatement getPreparedStatement(Connection conn, boolean callable) throws SQLException { Map preparedStatementMap = (Map) perConnCache.get(conn); if (null == preparedStatementMap) { // MRU PreparedStatements cache. preparedStatementMap = new LinkedHashMap(MAX_ENTRIES) { protected boolean removeEldestEntry(java.util.Map.Entry arg0) { final int theSize = size(); if (theSize > MAX_ENTRIES) { Object value = arg0.getValue(); if (value instanceof PreparedStatement) { PreparedStatement pstmt = (PreparedStatement) value; close(pstmt);/*from w ww . j av a 2 s . c o m*/ } return true; } return false; } }; perConnCache.put(conn, preparedStatementMap); } PreparedStatement pstmt = (PreparedStatement) preparedStatementMap.get(getQuery()); if (null == pstmt) { if (callable) { pstmt = conn.prepareCall(getQuery()); } else { pstmt = conn.prepareStatement(getQuery()); } preparedStatementMap.put(getQuery(), pstmt); } pstmt.clearParameters(); return pstmt; }
From source file:net.pms.dlna.DLNAMediaDatabase.java
public synchronized void insertData(String name, long modified, int type, DLNAMediaInfo media) { Connection conn = null;/*from ww w .j a v a 2s. c o m*/ PreparedStatement ps = null; try { conn = getConnection(); ps = conn.prepareStatement( "INSERT INTO FILES(FILENAME, MODIFIED, TYPE, DURATION, BITRATE, WIDTH, HEIGHT, SIZE, CODECV, FRAMERATE, ASPECT, ASPECTRATIOCONTAINER, ASPECTRATIOVIDEOTRACK, REFRAMES, AVCLEVEL, BITSPERPIXEL, THUMB, CONTAINER, MODEL, EXPOSURE, ORIENTATION, ISO, MUXINGMODE, FRAMERATEMODE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); ps.setString(1, name); ps.setTimestamp(2, new Timestamp(modified)); ps.setInt(3, type); if (media != null) { if (media.getDuration() != null) { ps.setDouble(4, media.getDurationInSeconds()); } else { ps.setNull(4, Types.DOUBLE); } int databaseBitrate = 0; if (type != Format.IMAGE) { databaseBitrate = media.getBitrate(); if (databaseBitrate == 0) { logger.debug("Could not parse the bitrate from: " + name); } } ps.setInt(5, databaseBitrate); ps.setInt(6, media.getWidth()); ps.setInt(7, media.getHeight()); ps.setLong(8, media.getSize()); ps.setString(9, left(media.getCodecV(), SIZE_CODECV)); ps.setString(10, left(media.getFrameRate(), SIZE_FRAMERATE)); ps.setString(11, left(media.getAspect(), SIZE_ASPECT)); ps.setString(12, left(media.getAspect(), SIZE_ASPECTRATIO_CONTAINER)); ps.setString(13, left(media.getAspect(), SIZE_ASPECTRATIO_VIDEOTRACK)); ps.setByte(14, media.getReferenceFrameCount()); ps.setString(15, left(media.getAvcLevel(), SIZE_AVC_LEVEL)); ps.setInt(16, media.getBitsPerPixel()); ps.setBytes(17, media.getThumb()); ps.setString(18, left(media.getContainer(), SIZE_CONTAINER)); if (media.getExtras() != null) { ps.setString(19, left(media.getExtrasAsString(), SIZE_MODEL)); } else { ps.setString(19, left(media.getModel(), SIZE_MODEL)); } ps.setInt(20, media.getExposure()); ps.setInt(21, media.getOrientation()); ps.setInt(22, media.getIso()); ps.setString(23, left(media.getMuxingModeAudio(), SIZE_MUXINGMODE)); ps.setString(24, left(media.getFrameRateMode(), SIZE_FRAMERATE_MODE)); } else { ps.setString(4, null); ps.setInt(5, 0); ps.setInt(6, 0); ps.setInt(7, 0); ps.setLong(8, 0); ps.setString(9, null); ps.setString(10, null); ps.setString(11, null); ps.setString(12, null); ps.setString(13, null); ps.setByte(14, (byte) -1); ps.setString(15, null); ps.setInt(16, 0); ps.setBytes(17, null); ps.setString(18, null); ps.setString(19, null); ps.setInt(20, 0); ps.setInt(21, 0); ps.setInt(22, 0); ps.setString(23, null); ps.setString(24, null); } ps.executeUpdate(); ResultSet rs = ps.getGeneratedKeys(); int id = -1; while (rs.next()) { id = rs.getInt(1); } rs.close(); if (media != null && id > -1) { PreparedStatement insert = null; if (media.getAudioTracksList().size() > 0) { insert = conn.prepareStatement( "INSERT INTO AUDIOTRACKS VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); } for (DLNAMediaAudio audio : media.getAudioTracksList()) { insert.clearParameters(); insert.setInt(1, id); insert.setInt(2, audio.getId()); insert.setString(3, left(audio.getLang(), SIZE_LANG)); insert.setString(4, left(audio.getFlavor(), SIZE_FLAVOR)); insert.setInt(5, audio.getAudioProperties().getNumberOfChannels()); insert.setString(6, left(audio.getSampleFrequency(), SIZE_SAMPLEFREQ)); insert.setString(7, left(audio.getCodecA(), SIZE_CODECA)); insert.setInt(8, audio.getBitsperSample()); insert.setString(9, left(trimToEmpty(audio.getAlbum()), SIZE_ALBUM)); insert.setString(10, left(trimToEmpty(audio.getArtist()), SIZE_ARTIST)); insert.setString(11, left(trimToEmpty(audio.getSongname()), SIZE_SONGNAME)); insert.setString(12, left(trimToEmpty(audio.getGenre()), SIZE_GENRE)); insert.setInt(13, audio.getYear()); insert.setInt(14, audio.getTrack()); insert.setInt(15, audio.getAudioProperties().getAudioDelay()); insert.setString(16, left(trimToEmpty(audio.getMuxingModeAudio()), SIZE_MUXINGMODE)); insert.setInt(17, audio.getBitRate()); try { insert.executeUpdate(); } catch (JdbcSQLException e) { if (e.getErrorCode() == 23505) { logger.debug( "A duplicate key error occurred while trying to store the following file's audio information in the database: " + name); } else { logger.debug( "An error occurred while trying to store the following file's audio information in the database: " + name); } logger.debug("The error given by jdbc was: " + e); } } if (media.getSubtitleTracksList().size() > 0) { insert = conn.prepareStatement("INSERT INTO SUBTRACKS VALUES (?, ?, ?, ?, ?)"); } for (DLNAMediaSubtitle sub : media.getSubtitleTracksList()) { if (sub.getExternalFile() == null) { // no save of external subtitles insert.clearParameters(); insert.setInt(1, id); insert.setInt(2, sub.getId()); insert.setString(3, left(sub.getLang(), SIZE_LANG)); insert.setString(4, left(sub.getFlavor(), SIZE_FLAVOR)); insert.setInt(5, sub.getType().getStableIndex()); try { insert.executeUpdate(); } catch (JdbcSQLException e) { if (e.getErrorCode() == 23505) { logger.debug( "A duplicate key error occurred while trying to store the following file's subtitle information in the database: " + name); } else { logger.debug( "An error occurred while trying to store the following file's subtitle information in the database: " + name); } logger.debug("The error given by jdbc was: " + e); } } } close(insert); } } catch (SQLException se) { if (se.getErrorCode() == 23001) { logger.debug("Duplicate key while inserting this entry: " + name + " into the database: " + se.getMessage()); } else { logger.error(null, se); } } finally { close(ps); close(conn); } }
From source file:org.apache.jackrabbit.core.persistence.db.DatabasePersistenceManager.java
/** * Resets the given <code>PreparedStatement</code> by clearing the parameters * and warnings contained.// ww w . j a va 2 s. com * <p/> * NOTE: This method MUST be called in a synchronized context as neither * this method nor the <code>PreparedStatement</code> instance on which it * operates are thread safe. * * @param stmt The <code>PreparedStatement</code> to reset. If * <code>null</code> this method does nothing. */ protected void resetStatement(PreparedStatement stmt) { if (stmt != null) { try { stmt.clearParameters(); stmt.clearWarnings(); } catch (SQLException se) { logException("failed resetting PreparedStatement", se); } } }