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.nabla.wapp.server.auth.UserManager.java
public boolean updateRoleDefinition(final Integer roleId, final SelectionDelta delta) throws SQLException { Assert.argumentNotNull(roleId);/* ww w . j av a 2 s. c om*/ Assert.argumentNotNull(delta); final LockTableGuard lock = new LockTableGuard(conn, LOCK_USER_TABLES); try { final ConnectionTransactionGuard guard = new ConnectionTransactionGuard(conn); try { if (delta.isRemovals()) Database.executeUpdate(conn, "DELETE FROM role_definition WHERE role_id=? AND child_role_id IN (?);", roleId, delta.getRemovals()); if (delta.isAdditions()) { final PreparedStatement stmt = conn .prepareStatement("INSERT INTO role_definition (role_id, child_role_id) VALUES(?,?);"); try { stmt.clearBatch(); stmt.setInt(1, roleId); for (final Integer childId : delta.getAdditions()) { stmt.setInt(2, childId); stmt.addBatch(); } if (!Database.isBatchCompleted(stmt.executeBatch())) return false; } finally { stmt.close(); } } return guard.setSuccess(updateUserRoleTable()); } finally { guard.close(); } } finally { lock.close(); } }
From source file:com.nabla.wapp.server.auth.UserManager.java
public boolean updateUserDefinition(final Integer objectId, final Integer userId, final SelectionDelta delta) throws SQLException { Assert.argumentNotNull(userId);/* ww w .java2s .c om*/ Assert.argumentNotNull(delta); final LockTableGuard lock = new LockTableGuard(conn, LOCK_USER_TABLES); try { final ConnectionTransactionGuard guard = new ConnectionTransactionGuard(conn); try { if (delta.isRemovals()) { if (objectId == null) Database.executeUpdate(conn, "DELETE FROM user_definition WHERE object_id IS NULL AND user_id=? AND role_id IN (?);", userId, delta.getRemovals()); else Database.executeUpdate(conn, "DELETE FROM user_definition WHERE object_id=? AND user_id=? AND role_id IN (?);", objectId, userId, delta.getRemovals()); } if (delta.isAdditions()) { final PreparedStatement stmt = conn.prepareStatement( "INSERT INTO user_definition (object_id, user_id, role_id) VALUES(?,?,?);"); try { stmt.clearBatch(); if (objectId == null) stmt.setNull(1, Types.BIGINT); else stmt.setInt(1, objectId); stmt.setInt(2, userId); for (final Integer childId : delta.getAdditions()) { stmt.setInt(3, childId); stmt.addBatch(); } if (!Database.isBatchCompleted(stmt.executeBatch())) return false; } finally { stmt.close(); } } return guard.setSuccess(updateUserRoleTable()); } finally { guard.close(); } } finally { lock.close(); } }
From source file:com.pactera.edg.am.metamanager.extractor.dao.helper.CreateDependencyHelper.java
public Object doInPreparedStatement(PreparedStatement ps) throws SQLException { String logMsg = ""; // ???//from w w w . j a va 2 s .com long sysTime = AdapterExtractorContext.getInstance().getGlobalTime(); for (Iterator<MMDDependency> dependencies = dependenciesList.iterator(); dependencies.hasNext();) { MMDDependency dependency = dependencies.next(); myDoInPreparedStatement(sysTime, dependency, ps); } if (super.count % super.batchSize != 0) { ps.executeBatch(); ps.clearBatch(); } logMsg = "?:" + dependenciesList.size(); log.info(logMsg); AdapterExtractorContext.addExtractorLog(ExtractorLogLevel.INFO, logMsg); return null; }
From source file:com.pactera.edg.am.metamanager.extractor.dao.helper.DeleteDependencyHelper.java
public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException { String logMsg = ""; // ???// w w w. j av a 2 s . com Iterator<MMDDependency> dependencies = dependenciesList.iterator(); long sysTime = AdapterExtractorContext.getInstance().getGlobalTime(); while (dependencies.hasNext()) { MMDDependency dependency = dependencies.next(); doInPreparedStatement(sysTime, dependency, ps); } if (super.count % super.batchSize != 0) { ps.executeBatch(); ps.clearBatch(); } logMsg = "?:" + dependenciesList.size(); log.info(logMsg); AdapterExtractorContext.addExtractorLog(ExtractorLogLevel.INFO, logMsg); return null; }
From source file:org.apache.hadoop.raid.DBUtils.java
public static List<List<Object>> runInsertSelect(DBConnectionFactory connectionFactory, String sql, List<Object> sqlParams, boolean isWrite, int numRetries, int retryMaxInternalSec, boolean insert, boolean getGeneratedKeys) throws IOException { int waitMS = 3000; // wait for at least 3s before next retry. for (int i = 0; i < numRetries; ++i) { Connection conn = null;/* w w w . j a v a2 s . c o m*/ ResultSet generatedKeys = null; PreparedStatement pstmt = null; String url = null; try { try { url = connectionFactory.getUrl(isWrite); } catch (IOException ioe) { LOG.warn("Cannot get DB URL, fall back to the default one", ioe); url = defaultUrls.get(isWrite); if (url == null) { throw ioe; } } LOG.info("Attepting connection with URL " + url); conn = connectionFactory.getConnection(url); defaultUrls.put(isWrite, url); pstmt = getPreparedStatement(conn, sql, sqlParams, getGeneratedKeys); if (insert) { int recordsUpdated = pstmt.executeUpdate(); LOG.info("rows inserted: " + recordsUpdated + " sql: " + sql); List<List<Object>> results = null; if (getGeneratedKeys) { generatedKeys = pstmt.getGeneratedKeys(); results = getResults(generatedKeys); } Thread.sleep(connectionFactory.getDBOpsSleepTime() + rand.nextInt(1000)); return results; } else { generatedKeys = pstmt.executeQuery(); List<List<Object>> results = getResults(generatedKeys); pstmt.clearBatch(); LOG.info("rows selected: " + results.size() + " sql: " + sql); Thread.sleep(connectionFactory.getDBOpsSleepTime() + rand.nextInt(1000)); return results; } } catch (Exception e) { // We should catch a better exception than Exception, but since // DBConnectionUrlFactory.getUrl() defines throws Exception, it's hard // for us to figure out the complete set it can throw. We follow // DBConnectionUrlFactory.getUrl()'s definition to catch Exception. // It shouldn't be a big problem as after numRetries, we anyway exit. LOG.info("Exception " + e + ". Will retry " + (numRetries - i) + " times."); // Introducing a random factor to the wait time before another retry. // The wait time is dependent on # of failures and a random factor. // At the first time of getting a SQLException, the wait time // is a random number between [0,300] msec. If the first retry // still fails, we will wait 300 msec grace period before the 2nd retry. // Also at the second retry, the waiting window is expanded to 600 msec // alleviating the request rate from the server. Similarly the 3rd retry // will wait 600 msec grace period before retry and the waiting window // is // expanded to 1200 msec. waitMS += waitMS; if (waitMS > retryMaxInternalSec * 1000) { waitMS = retryMaxInternalSec * 1000; } double waitTime = waitMS + waitMS * rand.nextDouble(); if (i + 1 == numRetries) { LOG.error("Still got Exception after " + numRetries + " retries.", e); throw new IOException(e); } try { Thread.sleep((long) waitTime); } catch (InterruptedException ie) { throw new IOException(ie); } } finally { DBUtils.close(generatedKeys, new PreparedStatement[] { pstmt }, conn); } } return null; }
From source file:com.nabla.wapp.server.auth.UserManager.java
public boolean initializeDatabase(final IRoleListProvider roleListProvider, final String rootPassword) throws SQLException { Assert.argumentNotNull(roleListProvider); final LockTableGuard lock = new LockTableGuard(conn, LOCK_USER_TABLES); try {//from w ww . j a va 2s . co m if (!Database.isTableEmpty(conn, IRoleTable.TABLE)) return true; if (log.isDebugEnabled()) log.debug("initializing role tables"); final Map<String, String[]> roles = roleListProvider.get(); Assert.state(!roles.containsKey(IRootUser.NAME)); final ConnectionTransactionGuard guard = new ConnectionTransactionGuard(conn); try { final PreparedStatement stmtRole = conn.prepareStatement( "INSERT INTO role (name,uname,privilege,internal) VALUES(?,?,?,?);", Statement.RETURN_GENERATED_KEYS); final Map<String, Integer> roleIds = new HashMap<String, Integer>(); try { stmtRole.clearBatch(); stmtRole.setBoolean(4, true); // add privileges and default roles for (final Map.Entry<String, String[]> role : roles.entrySet()) { stmtRole.setString(1, role.getKey()); stmtRole.setString(2, role.getKey().toUpperCase()); stmtRole.setBoolean(3, role.getValue() == null); stmtRole.addBatch(); } if (!Database.isBatchCompleted(stmtRole.executeBatch())) return false; final ResultSet rsKey = stmtRole.getGeneratedKeys(); try { for (final Map.Entry<String, String[]> role : roles.entrySet()) { rsKey.next(); roleIds.put(role.getKey(), rsKey.getInt(1)); } } finally { rsKey.close(); } } finally { stmtRole.close(); } final PreparedStatement stmtDefinition = conn .prepareStatement("INSERT INTO role_definition (role_id,child_role_id) VALUES(?,?);"); try { stmtDefinition.clearBatch(); for (final Map.Entry<String, String[]> role : roles.entrySet()) { final String[] definition = role.getValue(); if (definition == null) continue; stmtDefinition.setInt(1, roleIds.get(role.getKey())); for (final String child : definition) { final Integer childId = roleIds.get(child); if (childId == null) { if (log.isErrorEnabled()) log.error("child role '" + child + "' not defined!"); return false; } stmtDefinition.setInt(2, childId); stmtDefinition.addBatch(); } } if (!Database.isBatchCompleted(stmtDefinition.executeBatch())) return false; } finally { stmtDefinition.close(); } // add 'root' user Database.executeUpdate(conn, "INSERT INTO user (name,uname,active,password) VALUES(?,?,TRUE,?);", IRootUser.NAME, IRootUser.NAME.toUpperCase(), getPasswordEncryptor().encryptPassword(rootPassword)); return guard.setSuccess(true); } finally { guard.close(); } } finally { lock.close(); } }
From source file:org.linqs.psl.database.rdbms.RDBMSInserter.java
private void insertInternal(List<Double> values, List<List<Object>> data) { assert (values.size() == data.size()); int partitionID = partition.getID(); if (partitionID < 0) { throw new IllegalArgumentException("Partition IDs must be non-negative."); }/*from w w w . j a va2 s. c o m*/ for (int rowIndex = 0; rowIndex < data.size(); rowIndex++) { List<Object> row = data.get(rowIndex); assert (row != null); if (row.size() != predicateInfo.argumentColumns().size()) { throw new IllegalArgumentException( String.format("Data on row %d length does not match for %s: Expecting: %d, Got: %d", rowIndex, partition.getName(), predicateInfo.argumentColumns().size(), row.size())); } } try (Connection connection = dataStore.getConnection(); PreparedStatement multiInsertStatement = connection.prepareStatement(multiInsertSQL); PreparedStatement singleInsertStatement = connection.prepareStatement(singleInsertSQL);) { int batchSize = 0; // We will go from the multi-insert to the single-insert when we don't have enough data to fill the multi-insert. PreparedStatement activeStatement = multiInsertStatement; int insertSize = DEFAULT_MULTIROW_COUNT; int rowIndex = 0; while (rowIndex < data.size()) { // Index for the current index. int paramIndex = 1; if (activeStatement == multiInsertStatement && data.size() - rowIndex < DEFAULT_MULTIROW_COUNT) { // Commit any records left in the multi-insert batch. if (batchSize > 0) { activeStatement.executeBatch(); activeStatement.clearBatch(); batchSize = 0; } activeStatement = singleInsertStatement; insertSize = 1; } for (int i = 0; i < insertSize; i++) { List<Object> row = data.get(rowIndex); Double value = values.get(rowIndex); // Partition activeStatement.setInt(paramIndex++, partitionID); // Value if (value == null || value.isNaN()) { activeStatement.setNull(paramIndex++, java.sql.Types.DOUBLE); } else { activeStatement.setDouble(paramIndex++, value); } for (int argIndex = 0; argIndex < predicateInfo.argumentColumns().size(); argIndex++) { Object argValue = row.get(argIndex); assert (argValue != null); if (argValue instanceof Integer) { activeStatement.setInt(paramIndex++, (Integer) argValue); } else if (argValue instanceof Double) { // The standard JDBC way to insert NaN is using setNull if (Double.isNaN((Double) argValue)) { activeStatement.setNull(paramIndex++, java.sql.Types.DOUBLE); } else { activeStatement.setDouble(paramIndex++, (Double) argValue); } } else if (argValue instanceof String) { // This is the most common value we get when someone is using InsertUtils. // The value may need to be convered from a string. activeStatement.setObject(paramIndex++, convertString((String) argValue, argIndex)); } else if (argValue instanceof UniqueIntID) { activeStatement.setInt(paramIndex++, ((UniqueIntID) argValue).getID()); } else if (argValue instanceof UniqueStringID) { activeStatement.setString(paramIndex++, ((UniqueStringID) argValue).getID()); } else { throw new IllegalArgumentException("Unknown data type for :" + argValue); } } rowIndex++; } activeStatement.addBatch(); batchSize++; if (batchSize >= DEFAULT_PAGE_SIZE) { activeStatement.executeBatch(); activeStatement.clearBatch(); batchSize = 0; } } if (batchSize > 0) { activeStatement.executeBatch(); activeStatement.clearBatch(); batchSize = 0; } activeStatement.clearParameters(); activeStatement = null; } catch (SQLException ex) { log.error(ex.getMessage()); throw new RuntimeException("Error inserting into RDBMS.", ex); } }
From source file:net.ymate.platform.persistence.jdbc.operator.AbstractOperator.java
/** * ?/* www .j a va2 s .c o m*/ * * @param accessor ? * @param conn ? * @return ?? * @throws SQLException */ protected int[] doBatchUpdate(IAccessor accessor, IConnectionHolder conn) throws SQLException { PreparedStatement _statement = null; AccessorEventContext _context = null; try { _statement = accessor.getPreparedStatement(conn.getConnection()); if (accessor.getAccessorCfgEvent() != null) { _context = new AccessorEventContext(_statement, false, false); accessor.getAccessorCfgEvent().beforeStatementExecution(_context); } int _returnValue[] = _statement.executeBatch(); if (accessor.getAccessorCfgEvent() != null && _context != null) { accessor.getAccessorCfgEvent().afterStatementExecution(_context); } _statement.clearBatch(); return _returnValue; } finally { if (_statement != null) { _statement.close(); _statement = null; } _context = null; } }
From source file:com.pactera.edg.am.metamanager.extractor.dao.helper.CreateDependencyHelper.java
private void myDoInPreparedStatement(long sysTime, MMDDependency dependency, PreparedStatement ps) throws SQLException { String ownerModelId = dependency.getOwnerMetadata().getClassifierId(); String valueModelId = dependency.getValueMetadata().getClassifierId(); try {// w ww. ja v a2 s . co m // ?ID ps.setString(1, dependency.getOwnerMetadata().getId()); // ?ID ps.setString(2, ownerModelId); // ?ID ps.setString(3, dependency.getValueMetadata().getId()); // ?ID ps.setString(4, valueModelId); // ??? ps.setString(5, dependency.getCode()); // ps.setLong(6, sysTime); ps.setString(7, "1"); ps.setString(8, "1"); setPs(ps, 8); ps.addBatch(); // ?? ps.clearParameters(); if (++super.count % super.batchSize == 0) { ps.executeBatch(); ps.clearBatch(); } } catch (SQLException e) { String logMsg = new StringBuilder().append("?,?:??ID:") .append(dependency.getOwnerMetadata().getId()).append(",?:") .append(ownerModelId).append(",??ID:") .append(dependency.getValueMetadata().getId()).append(",?:") .append(valueModelId).append(", ???:").append(dependency.getCode()).toString(); log.error(logMsg); AdapterExtractorContext.addExtractorLog(ExtractorLogLevel.ERROR, logMsg); throw e; } }
From source file:com.sec.ose.osi.localdb.identification.IdentificationDBManager.java
synchronized public static void executeUpdateStatement(PreparedStatement tmpStatement) { if (tmpStatement == null) { return;/*from w w w .ja va2s .co m*/ } try { tmpStatement.executeBatch(); tmpStatement.clearBatch(); } catch (SQLException e) { log.warn(e); } }