List of usage examples for java.sql PreparedStatement clearBatch
void clearBatch() throws SQLException;
Statement object's current list of SQL commands. From source file:com.pactera.edg.am.metamanager.extractor.dao.helper.ExtractorLogHelper.java
public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException { String taskInstanceId = AdapterExtractorContext.getInstance().getTaskInstanceId(); for (ExtractorLog extractorLog : extractorLogs) { ExtractorLogLevel level = extractorLog.getLevel(); if (!TaskInstance.STATE_IMPORTED_ERROR .equals(AdapterExtractorContext.getInstance().getReturnStatus())) { // ?error?? if (level == ExtractorLogLevel.ERROR) { AdapterExtractorContext.getInstance().setReturnStatus(TaskInstance.STATE_IMPORTED_ERROR); } else if (level == ExtractorLogLevel.WARN) { AdapterExtractorContext.getInstance().setReturnStatus(TaskInstance.STATE_IMPORTED_WARN); }//from w ww .j a v a 2 s . c o m } if (extractorLog.getMessage() == null) // continue; ps.setString(1, taskInstanceId); ps.setString(2, String.valueOf(level.ordinal())); ps.setLong(3, extractorLog.getLogTime()); // ? boolean msgTooLength = extractorLog.getMessage().length() > AdapterExtractorContext.getInstance() .getMaxLogSize(); String message = msgTooLength ? extractorLog.getMessage().substring(0, AdapterExtractorContext.getInstance().getMaxLogSize()) : extractorLog.getMessage(); ps.setString(4, message); ps.setString(5, extractorLog.getLogType()); ps.addBatch(); ps.clearParameters(); } ps.executeBatch(); ps.clearBatch(); return null; }
From source file:org.wso2.carbon.registry.core.jdbc.dao.JDBCRatingsDAO.java
public void removeVersionRatings(long regVersion) throws RegistryException { JDBCDatabaseTransaction.ManagedRegistryConnection conn = JDBCDatabaseTransaction.getConnection(); PreparedStatement ps1 = null, ps2 = null; List<Long> taggingIds = getRatingIds(regVersion); if (taggingIds == null) { return;//from w w w . ja v a 2 s . co m } try { String sql = "DELETE FROM REG_RESOURCE_RATING WHERE REG_RATING_ID= ? AND REG_TENANT_ID=?"; ps1 = conn.prepareStatement(sql); sql = "DELETE FROM REG_RATING WHERE REG_ID= ? AND REG_TENANT_ID=?"; ps2 = conn.prepareStatement(sql); for (long l : taggingIds) { ps1.setLong(1, l); ps1.setInt(2, CurrentSession.getTenantId()); ps2.setLong(1, l); ps2.setInt(2, CurrentSession.getTenantId()); ps1.addBatch(); ps2.addBatch(); } if (taggingIds.size() > 0) { try { ps1.executeBatch(); ps2.executeBatch(); } catch (SQLException e) { ps1.clearBatch(); ps2.clearBatch(); // the exception will be handled in the next catch block throw e; } } } catch (SQLException e) { String msg = "Failed to remove tagging with the version: " + regVersion + ". " + e.getMessage(); log.error(msg, e); throw new RegistryException(msg, e); } finally { try { try { if (ps1 != null) { ps1.close(); } } finally { if (ps2 != null) { ps2.close(); } } } catch (SQLException ex) { String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR; log.error(msg, ex); } } }
From source file:org.wso2.carbon.registry.core.jdbc.dao.JDBCRatingsDAO.java
public void removeRatings(ResourceImpl resourceImpl) throws RegistryException { JDBCDatabaseTransaction.ManagedRegistryConnection conn = JDBCDatabaseTransaction.getConnection(); PreparedStatement ps1 = null, ps2 = null; RatingDO[] ratingDOs = getResourceRatingDO(resourceImpl); try {/*from w ww .j a va2 s . c om*/ String sql1 = "DELETE FROM REG_RESOURCE_RATING WHERE REG_RATING_ID = ? AND REG_TENANT_ID=?"; ps1 = conn.prepareStatement(sql1); String sql2 = "DELETE FROM REG_RATING WHERE REG_ID = ? AND REG_TENANT_ID=?"; ps2 = conn.prepareStatement(sql2); for (RatingDO ratingDO : ratingDOs) { ps1.setLong(1, ratingDO.getRatingID()); ps1.setInt(2, CurrentSession.getTenantId()); ps2.setLong(1, ratingDO.getRatingID()); ps2.setInt(2, CurrentSession.getTenantId()); ps1.addBatch(); ps2.addBatch(); } if (ratingDOs.length > 0) { try { ps1.executeBatch(); ps2.executeBatch(); } catch (SQLException e) { ps1.clearBatch(); ps2.clearBatch(); // the exception will be handled in the next catch block throw e; } } } catch (SQLException e) { String msg = "Failed to remove ratings on resource " + resourceImpl.getPath() + ". " + e.getMessage(); log.error(msg, e); throw new RegistryException(msg, e); } finally { try { try { if (ps1 != null) { ps1.close(); } } finally { if (ps2 != null) { ps2.close(); } } } catch (SQLException ex) { String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR; log.error(msg, ex); } } }
From source file:org.wso2.extension.siddhi.store.rdbms.RDBMSEventTable.java
private void batchProcessDelete(List<Map<String, Object>> deleteConditionParameterMaps, CompiledCondition compiledCondition) { String condition = ((RDBMSCompiledCondition) compiledCondition).getCompiledQuery(); Connection conn = this.getConnection(); PreparedStatement stmt = null; try {//from www .ja va 2 s . c o m stmt = RDBMSTableUtils.isEmpty(condition) ? conn.prepareStatement(deleteQuery.replace(PLACEHOLDER_CONDITION, "")) : conn.prepareStatement(RDBMSTableUtils.formatQueryWithCondition(deleteQuery, condition)); int counter = 0; for (Map<String, Object> deleteConditionParameterMap : deleteConditionParameterMaps) { RDBMSTableUtils.resolveCondition(stmt, (RDBMSCompiledCondition) compiledCondition, deleteConditionParameterMap, 0); stmt.addBatch(); counter++; if (counter == batchSize) { stmt.executeBatch(); stmt.clearBatch(); counter = 0; } } if (counter > 0) { stmt.executeBatch(); } } catch (SQLException e) { throw new RDBMSTableException( "Error performing record deletion on table '" + this.tableName + "': " + e.getMessage(), e); } finally { RDBMSTableUtils.cleanupConnection(null, stmt, conn); } }
From source file:org.wso2.carbon.registry.core.jdbc.dao.JDBCTagsDAO.java
public void removeVersionTags(long regVersion) throws RegistryException { JDBCDatabaseTransaction.ManagedRegistryConnection conn = JDBCDatabaseTransaction.getConnection(); PreparedStatement ps1 = null, ps2 = null; List<Long> taggingIds = getTagIds(regVersion); if (taggingIds == null) { return;/* w w w .j a v a 2 s . c o m*/ } try { String sql = "DELETE FROM REG_RESOURCE_TAG WHERE REG_TAG_ID= ? AND REG_TENANT_ID=?"; ps1 = conn.prepareStatement(sql); sql = "DELETE FROM REG_TAG WHERE REG_ID= ? AND REG_TENANT_ID=?"; ps2 = conn.prepareStatement(sql); for (long l : taggingIds) { ps1.setLong(1, l); ps1.setInt(2, CurrentSession.getTenantId()); ps2.setLong(1, l); ps2.setInt(2, CurrentSession.getTenantId()); ps1.addBatch(); ps2.addBatch(); } if (taggingIds.size() > 0) { try { ps1.executeBatch(); ps2.executeBatch(); } catch (SQLException e) { ps1.clearBatch(); ps2.clearBatch(); // the exception will be handled in the next catch block throw e; } } } catch (SQLException e) { String msg = "Failed to remove tagging for the version: " + regVersion + ". " + e.getMessage(); log.error(msg, e); throw new RegistryException(msg, e); } finally { try { try { if (ps1 != null) { ps1.close(); } } finally { if (ps2 != null) { ps2.close(); } } } catch (SQLException ex) { String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR; log.error(msg, ex); } } }
From source file:org.wso2.carbon.registry.core.jdbc.dao.JDBCTagsDAO.java
public void removeTags(ResourceImpl resource) throws RegistryException { JDBCDatabaseTransaction.ManagedRegistryConnection conn = JDBCDatabaseTransaction.getConnection(); PreparedStatement ps1 = null, ps2 = null; TaggingDO[] taggingDOs = getTagging(resource); if (taggingDOs == null) { return;//w ww . j ava 2 s .com } try { String sql = "DELETE FROM REG_RESOURCE_TAG WHERE REG_TAG_ID= ? AND REG_TENANT_ID=?"; ps1 = conn.prepareStatement(sql); sql = "DELETE FROM REG_TAG WHERE REG_ID= ? AND REG_TENANT_ID=?"; ps2 = conn.prepareStatement(sql); for (TaggingDO taggingDO : taggingDOs) { ps1.setLong(1, taggingDO.getTagID()); ps1.setInt(2, CurrentSession.getTenantId()); ps2.setLong(1, taggingDO.getTagID()); ps2.setInt(2, CurrentSession.getTenantId()); ps1.addBatch(); ps2.addBatch(); } if (taggingDOs.length > 0) { try { ps1.executeBatch(); ps2.executeBatch(); } catch (SQLException e) { ps1.clearBatch(); ps2.clearBatch(); // the exception will be handled in the next catch block throw e; } } } catch (SQLException e) { String msg = "Failed to remove tagging from " + resource.getPath() + ". " + e.getMessage(); log.error(msg, e); throw new RegistryException(msg, e); } finally { try { try { if (ps1 != null) { ps1.close(); } } finally { if (ps2 != null) { ps2.close(); } } } catch (SQLException ex) { String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR; log.error(msg, ex); } } }
From source file:org.wso2.extension.siddhi.store.rdbms.RDBMSEventTable.java
private List<Integer> batchProcessInsert(List<Object[]> addingRecords, List<Integer> recordInsertIndexList) { int counter = 0; String query = this.composeInsertQuery(); Connection conn = this.getConnection(false); PreparedStatement insertStmt = null; List<Integer> retryUpdateOrdinalList = new ArrayList<>(); try {/* w ww . j a v a2s. co m*/ insertStmt = conn.prepareStatement(query); if (!primaryKeysEnabled) { //Default Scenario: If the primary keys are disabled, then insert all the records into the table. while (counter < recordInsertIndexList.size()) { if (recordInsertIndexList.get(counter) == counter) { Object[] record = addingRecords.get(counter); this.populateStatement(record, insertStmt); try { insertStmt.addBatch(); if (counter % batchSize == (batchSize - 1)) { insertStmt.executeBatch(); conn.commit(); insertStmt.clearBatch(); } } catch (SQLException e2) { RDBMSTableUtils.rollbackConnection(conn); throw new RDBMSTableException( "Error performing update/insert operation (insert) on table '" + this.tableName + "': " + e2.getMessage(), e2); } } counter++; } if (counter % batchSize > 0) { insertStmt.executeBatch(); conn.commit(); } } else { // If the primary keys are enabled, add records into a map with keys as a combination of primary keys, // values as ordinal of the recordInsertIndexList. // If the key already available in the map, then its copy the relevant updateSetParameter entry into // retryUpdateSetParameterMaps, where it will be process again with update operation. // Finally insert records from recordInsertIndexList into database table. Map<String, Integer> nonDuplicateRecordIdMap = new LinkedHashMap<>(); while (counter < recordInsertIndexList.size()) { if (recordInsertIndexList.get(counter) == counter) { StringBuilder primaryKeyHashBuilder = new StringBuilder(); for (Integer primaryKeyAttributePosition : primaryKeyAttributePositionsList) { Attribute attribute = attributes.get(primaryKeyAttributePosition); Object recordAttribute = addingRecords.get(counter)[primaryKeyAttributePosition]; switch (attribute.getType()) { case BOOL: primaryKeyHashBuilder.append(Boolean.toString((Boolean) recordAttribute)); break; case DOUBLE: primaryKeyHashBuilder.append(Double.toString((Double) recordAttribute)); break; case FLOAT: primaryKeyHashBuilder.append(Float.toString((Float) recordAttribute)); break; case INT: primaryKeyHashBuilder.append(Integer.toString((Integer) recordAttribute)); break; case LONG: primaryKeyHashBuilder.append(Long.toString((Long) recordAttribute)); break; case OBJECT: primaryKeyHashBuilder.append(recordAttribute.toString()); break; case STRING: primaryKeyHashBuilder.append((String) recordAttribute); break; } } if (!nonDuplicateRecordIdMap.containsKey(primaryKeyHashBuilder.toString())) { nonDuplicateRecordIdMap.put(primaryKeyHashBuilder.toString(), counter); } else { //Add the record insert index ordinal to retry update retryUpdateOrdinalList.add(counter); } } counter++; } counter = 0; for (Map.Entry<String, Integer> record : nonDuplicateRecordIdMap.entrySet()) { this.populateStatement(addingRecords.get(record.getValue()), insertStmt); try { insertStmt.addBatch(); if (counter % batchSize == (batchSize - 1)) { insertStmt.executeBatch(); conn.commit(); insertStmt.clearBatch(); } } catch (SQLException e2) { RDBMSTableUtils.rollbackConnection(conn); throw new RDBMSTableException("Error performing update/insert operation (insert) on table '" + this.tableName + "': " + e2.getMessage(), e2); } counter++; } if (counter % batchSize > 0) { insertStmt.executeBatch(); conn.commit(); } } } catch (SQLException e) { throw new RDBMSTableException("Cannot execute update/insert operation (update) on table '" + this.tableName + "' with SQL query " + query + " .", e); } finally { RDBMSTableUtils.cleanupConnection(null, insertStmt, conn); } return retryUpdateOrdinalList; }
From source file:org.wso2.extension.siddhi.store.rdbms.RDBMSEventTable.java
/** * Method for processing update operations in a batched manner. This assumes that all update operations will be * accepted by the database./* www.j a va 2 s . c om*/ * * @param sql the SQL update operation as string. * @param updateConditionParameterMaps the runtime parameters that should be populated to the condition. * @param compiledCondition the condition that was built during compile time. * @param updateSetExpressions the expressions that are used in the SET operation * @param updateSetParameterMaps the runtime parameters that should be populated to the update statement. */ private void batchProcessSQLUpdates(String sql, List<Map<String, Object>> updateConditionParameterMaps, CompiledCondition compiledCondition, Map<String, CompiledExpression> updateSetExpressions, List<Map<String, Object>> updateSetParameterMaps) { int counter = 0; Connection conn = this.getConnection(); PreparedStatement stmt = null; try { stmt = conn.prepareStatement(sql); Iterator<Map<String, Object>> conditionParamIterator = updateConditionParameterMaps.iterator(); Iterator<Map<String, Object>> updateSetParameterMapsIterator = updateSetParameterMaps.iterator(); while (conditionParamIterator.hasNext() && updateSetParameterMapsIterator.hasNext()) { Map<String, Object> conditionParameters = conditionParamIterator.next(); Map<String, Object> updateSetMap = updateSetParameterMapsIterator.next(); int ordinal = RDBMSTableUtils.enumerateUpdateSetEntries(updateSetExpressions, stmt, updateSetMap); //Incrementing the ordinals of the conditions in the statement with the # of variables to be updated RDBMSTableUtils.resolveCondition(stmt, (RDBMSCompiledCondition) compiledCondition, conditionParameters, ordinal - 1); stmt.addBatch(); counter++; if (counter == batchSize) { stmt.executeBatch(); stmt.clearBatch(); counter = 0; } } if (counter > 0) { stmt.executeBatch(); } } catch (SQLException e) { throw new RDBMSTableException("Error performing record update operations on table '" + this.tableName + "': " + e.getMessage(), e); } finally { RDBMSTableUtils.cleanupConnection(null, stmt, conn); } }
From source file:com.fortmoon.utils.CSVDBLoader.java
public void executeBatch() { PreparedStatement stmt = null; //Statement stmt = null; boolean first = true; StringBuffer insert = new StringBuffer("insert into " + this.tableName + " ("); for (String col : this.columnNames) { if (first) insert.append(col);/* w w w . java 2s. co m*/ else insert.append(", " + col); first = false; } insert.append(") values("); first = true; for (String col : this.columnNames) { if (first) insert.append("?"); else insert.append(", ?"); first = false; } insert.append(")"); log.info("Insert statement: " + insert); try { con = DriverManager.getConnection(url, user, password); //stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); stmt = con.prepareStatement(insert.toString()); con.setAutoCommit(false); TimeUnit SECONDS = TimeUnit.SECONDS; ArrayList<String> statements = null; boolean complete = false; while (!complete) { try { statements = (ArrayList<String>) queue.poll(10, SECONDS); if (statements == null) { return; } } catch (InterruptedException e) { log.error("Poll timed out"); e.printStackTrace(); complete = true; return; } for (String statement : statements) { stmt.addBatch(statement); } //log.info("Starting execute."); int[] updateCounts = stmt.executeBatch(); //log.info("Starting commit."); con.commit(); stmt.clearBatch(); log.info("Committed number of rows: " + updateCounts.length); } } catch (SQLException ex) { log.error(ex.getMessage(), ex); } finally { try { if (stmt != null) stmt.close(); //if (pst != null) // pst.close(); if (con != null) con.close(); } catch (SQLException ex) { log.error(ex.getMessage(), ex); } // statements.clear(); } log.info("\nLoad complete. Goodbye.\n"); }
From source file:org.wso2.extension.siddhi.store.rdbms.RDBMSEventTable.java
private List<Integer> batchProcessUpdate(List<Map<String, Object>> updateConditionParameterMaps, CompiledCondition compiledCondition, Map<String, CompiledExpression> updateSetExpressions, List<Map<String, Object>> updateSetParameterMaps) { int counter = 0; Connection conn = this.getConnection(); PreparedStatement updateStmt = null; List<Integer> recordInsertIndexList = new ArrayList<>(); String query = this.composeUpdateQuery(compiledCondition, updateSetExpressions); try {//from www . j a v a2s . com updateStmt = conn.prepareStatement(query); Iterator<Map<String, Object>> conditionParamIterator = updateConditionParameterMaps.iterator(); Iterator<Map<String, Object>> updateSetMapIterator = updateSetParameterMaps.iterator(); while (conditionParamIterator.hasNext() && updateSetMapIterator.hasNext()) { Map<String, Object> conditionParameters = conditionParamIterator.next(); Map<String, Object> updateSetMap = updateSetMapIterator.next(); int ordinal = RDBMSTableUtils.enumerateUpdateSetEntries(updateSetExpressions, updateStmt, updateSetMap); //Incrementing the ordinals of the conditions in the statement with the # of variables to be updated RDBMSTableUtils.resolveCondition(updateStmt, (RDBMSCompiledCondition) compiledCondition, conditionParameters, ordinal - 1); updateStmt.addBatch(); if (counter % batchSize == batchSize - 1) { recordInsertIndexList.addAll(this.filterRequiredInsertIndex(updateStmt.executeBatch(), ((counter / batchSize) * batchSize))); updateStmt.clearBatch(); } counter++; } if (counter % batchSize > 0) { recordInsertIndexList.addAll(this.filterRequiredInsertIndex(updateStmt.executeBatch(), (counter - (counter % batchSize)))); } } catch (SQLException e) { throw new RDBMSTableException("Cannot execute update/insert operation (update) on table '" + this.tableName + "' with SQL query " + query + " .", e); } finally { RDBMSTableUtils.cleanupConnection(null, updateStmt, conn); } return recordInsertIndexList; }