Example usage for java.sql PreparedStatement executeBatch

List of usage examples for java.sql PreparedStatement executeBatch

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. executeBatch

Introduction

In this page you can find the example usage for java.sql PreparedStatement executeBatch.

Prototype

int[] executeBatch() throws SQLException;

Source Link

Document

Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.

Usage

From source file:org.openmrs.util.databasechange.UpdateCohortMemberIds.java

/**
 * @see CustomTaskChange#execute(Database)
 *//*from  w  ww.j  av a2s.c  om*/
@Override
public void execute(Database database) throws CustomChangeException {
    JdbcConnection connection = (JdbcConnection) database.getConnection();
    Statement stmt = null;
    PreparedStatement pStmt = null;

    try {
        stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM cohort_member");

        pStmt = connection.prepareStatement(
                "UPDATE cohort_member SET cohort_member_id = ?" + " WHERE cohort_id = ? AND patient_id = ?");
        int i = 0;
        while (rs.next()) {
            int cohortId = rs.getInt("cohort_id");
            int patientId = rs.getInt("patient_id");
            pStmt.setInt(1, ++i);
            pStmt.setInt(2, cohortId);
            pStmt.setInt(3, patientId);
            pStmt.addBatch();
        }
        pStmt.executeBatch();
    } catch (DatabaseException | SQLException e) {
        log.warn("Error generated", e);
    } finally {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                log.warn("Failed to close the statement object");
            }
        }

        if (pStmt != null) {
            try {
                pStmt.close();
            } catch (SQLException e) {
                log.warn("Failed to close the prepared statement object");
            }
        }
    }
}

From source file:iddb.runtime.db.model.dao.impl.mysql.PenaltyDAOImpl.java

@Override
public void delete(List<Penalty> list) {
    String sql = "delete from penalty where id = ?";
    Connection conn = null;/*from ww  w.j a  v a 2  s. c o  m*/
    try {
        conn = ConnectionFactory.getMasterConnection();
        PreparedStatement st = conn.prepareStatement(sql);
        for (Penalty p : list) {
            st.setLong(1, p.getKey());
            st.addBatch();
        }
        st.executeBatch();
    } catch (SQLException e) {
        logger.error("delete: {}", e);
    } catch (IOException e) {
        logger.error("delete: {}", e);
    } finally {
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
        }
    }
}

From source file:org.openmrs.util.databasechange.UpdateLayoutAddressFormatChangeSet.java

/**
 * @see CustomTaskChange#execute(Database)
 *//*  w  w w . j  av a2s .com*/
@Override
public void execute(Database database) throws CustomChangeException {
    JdbcConnection connection = (JdbcConnection) database.getConnection();
    Statement stmt = null;
    PreparedStatement pStmt = null;

    try {
        stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery(
                "SELECT property_value FROM global_property WHERE property = 'layout.address.format'");
        if (rs.next()) {
            String value = rs.getString("property_value");
            value = value.replace("org.openmrs.layout.web.", "org.openmrs.layout.");

            pStmt = connection.prepareStatement(
                    "UPDATE global_property SET property_value = ? WHERE property = 'layout.address.format'");
            pStmt.setString(1, value);
            pStmt.addBatch();
            pStmt.executeBatch();
        }
    } catch (DatabaseException e) {
        log.warn("Error generated", e);
    } catch (SQLException e) {
        log.warn("Error generated", e);
    } finally {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                log.warn("Failed to close the statement object");
            }
        }

        if (pStmt != null) {
            try {
                pStmt.close();
            } catch (SQLException e) {
                log.warn("Failed to close the prepared statement object");
            }
        }
    }
}

From source file:net.ymate.platform.persistence.jdbc.operator.AbstractOperator.java

/**
 * ?//from ww w  .  j a  v  a2s .  c om
 * 
 * @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:org.springframework.jdbc.object.BatchSqlUpdateTests.java

private void doTestBatchUpdate(boolean flushThroughBatchSize) throws Exception {
    final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?";
    final int[] ids = new int[] { 100, 200 };
    final int[] rowsAffected = new int[] { 1, 2 };

    MockControl ctrlPreparedStatement = MockControl.createControl(PreparedStatement.class);
    PreparedStatement mockPreparedStatement = (PreparedStatement) ctrlPreparedStatement.getMock();
    mockPreparedStatement.getConnection();
    ctrlPreparedStatement.setReturnValue(mockConnection);
    mockPreparedStatement.setObject(1, new Integer(ids[0]), Types.INTEGER);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.addBatch();/*from   ww w . j  a  v a 2 s.  co  m*/
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.setObject(1, new Integer(ids[1]), Types.INTEGER);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.addBatch();
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.executeBatch();
    ctrlPreparedStatement.setReturnValue(rowsAffected);
    if (debugEnabled) {
        mockPreparedStatement.getWarnings();
        ctrlPreparedStatement.setReturnValue(null);
    }
    mockPreparedStatement.close();
    ctrlPreparedStatement.setVoidCallable();

    MockControl ctrlDatabaseMetaData = MockControl.createControl(DatabaseMetaData.class);
    DatabaseMetaData mockDatabaseMetaData = (DatabaseMetaData) ctrlDatabaseMetaData.getMock();
    mockDatabaseMetaData.supportsBatchUpdates();
    ctrlDatabaseMetaData.setReturnValue(true);

    mockConnection.prepareStatement(sql);
    ctrlConnection.setReturnValue(mockPreparedStatement);
    mockConnection.getMetaData();
    ctrlConnection.setReturnValue(mockDatabaseMetaData, 1);

    ctrlPreparedStatement.replay();
    ctrlDatabaseMetaData.replay();
    replay();

    BatchSqlUpdate update = new BatchSqlUpdate(mockDataSource, sql);
    update.declareParameter(new SqlParameter(Types.INTEGER));
    if (flushThroughBatchSize) {
        update.setBatchSize(2);
    }

    update.update(ids[0]);
    update.update(ids[1]);

    if (flushThroughBatchSize) {
        assertEquals(0, update.getQueueCount());
        assertEquals(2, update.getRowsAffected().length);
    } else {
        assertEquals(2, update.getQueueCount());
        assertEquals(0, update.getRowsAffected().length);
    }

    int[] actualRowsAffected = update.flush();
    assertEquals(0, update.getQueueCount());

    if (flushThroughBatchSize) {
        assertTrue("flush did not execute updates", actualRowsAffected.length == 0);
    } else {
        assertTrue("executed 2 updates", actualRowsAffected.length == 2);
        assertEquals(rowsAffected[0], actualRowsAffected[0]);
        assertEquals(rowsAffected[1], actualRowsAffected[1]);
    }

    actualRowsAffected = update.getRowsAffected();
    assertTrue("executed 2 updates", actualRowsAffected.length == 2);
    assertEquals(rowsAffected[0], actualRowsAffected[0]);
    assertEquals(rowsAffected[1], actualRowsAffected[1]);

    update.reset();
    assertEquals(0, update.getRowsAffected().length);

    ctrlPreparedStatement.verify();
    ctrlDatabaseMetaData.verify();
}

From source file:org.wso2.carbon.device.mgt.mobile.impl.windows.dao.impl.WindowsFeatureDAOImpl.java

@Override
public boolean addFeatures(List<MobileFeature> mobileFeatures) throws MobileDeviceManagementDAOException {
    PreparedStatement stmt = null;
    boolean status = false;
    Connection conn;//from www  . ja va2  s  .  co  m
    try {
        conn = WindowsDAOFactory.getConnection();
        stmt = conn.prepareStatement("INSERT INTO WIN_FEATURE(CODE, NAME, DESCRIPTION) VALUES (?, ?, ?)");
        for (MobileFeature mobileFeature : mobileFeatures) {
            stmt.setString(1, mobileFeature.getCode());
            stmt.setString(2, mobileFeature.getName());
            stmt.setString(3, mobileFeature.getDescription());
            stmt.addBatch();
        }
        stmt.executeBatch();
        status = true;
    } catch (SQLException e) {
        throw new WindowsFeatureManagementDAOException(
                "Error occurred while adding windows features into the metadata repository", e);
    } finally {
        MobileDeviceManagementDAOUtil.cleanupResources(stmt, null);
    }
    return status;
}

From source file:org.eclipse.scada.ds.storage.jdbc.internal.JdbcStorageDaoBase64Impl.java

protected void insertNode(final ConnectionContext connectionContext, final DataNode node, final String data)
        throws SQLException {
    if (data == null) {
        return;/*from   ww w .  j  a v  a2  s. c  om*/
    }

    final PreparedStatement stmt = connectionContext.getConnection().prepareStatement(SQL_INSERT);

    final int len = data.length();

    for (int i = 0; i <= len / this.chunkSize; i++) {
        int end = (i + 1) * this.chunkSize;
        if (end > len) {
            end = len;
        }

        final String chunk = data.substring(i * this.chunkSize, end);

        stmt.setObject(1, node.getId());
        stmt.setObject(2, this.instanceId);
        stmt.setObject(3, i);
        stmt.setObject(4, chunk);
        stmt.addBatch();
    }

    stmt.executeBatch();
}

From source file:org.opencron.server.dao.HibernateDao.java

@Transactional(readOnly = false)
public void executeBatch(final String sql, final Object[]... parameters) {
    getSession().doWork(new Work() {

        public void execute(Connection connection) throws SQLException {
            connection.setAutoCommit(false);
            PreparedStatement stmt = connection.prepareStatement(sql);
            for (Object[] arr : parameters) {
                int i = 1;
                for (Object p : arr) {
                    stmt.setObject(i++, p);
                }/*w w  w .jav a 2 s.com*/
                stmt.addBatch();
            }
            stmt.executeBatch();
            connection.commit();
        }
    });
}

From source file:iddb.runtime.db.model.dao.impl.mysql.PenaltyDAOImpl.java

@Override
public void disable(List<Penalty> list) {
    String sql = "update penalty set active = ? where id = ?";
    Connection conn = null;//from w  ww . j  a va  2  s .c  o  m
    try {
        conn = ConnectionFactory.getMasterConnection();
        PreparedStatement st = conn.prepareStatement(sql);
        for (Penalty p : list) {
            st.setBoolean(1, false);
            st.setLong(2, p.getKey());
            st.addBatch();
        }
        st.executeBatch();
    } catch (SQLException e) {
        logger.error("disable: {}", e);
    } catch (IOException e) {
        logger.error("disable: {}", e);
    } finally {
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
        }
    }
}

From source file:com.nabla.dc.server.xml.settings.XmlUser.java

public boolean save(final Connection conn, final SaveContext ctx) throws SQLException, DispatchException {
    Integer userId = ctx.getUserIds().get(getName());
    if (userId != null) {
        if (ctx.getOption() == SqlInsertOptions.APPEND) {
            if (log.isDebugEnabled())
                log.debug("skipping user '" + getName() + "' (already defined)");
            return true;
        }/*from   w w w  . j av a 2  s . co m*/
        Database.executeUpdate(conn, "UPDATE user SET password=?,active=? WHERE id=?;",
                UserManager.getPasswordEncryptor().encryptPassword(password.getValue()), active, userId);
        Database.executeUpdate(conn, "DELETE FROM user_definition WHERE object_id IS NULL AND user_id=?;",
                userId);
    } else {
        if (log.isDebugEnabled())
            log.debug("adding user '" + getName() + "'");
        userId = Database.addRecord(conn, "INSERT INTO user (name,uname,active,password) VALUES(?,?,?,?);",
                getName(), getName().toUpperCase(), active,
                UserManager.getPasswordEncryptor().encryptPassword(password.getValue()));
        if (userId == null)
            throw new InternalErrorException(Util.formatInternalErrorDescription("failed to insert user"));
        ctx.getUserIds().put(getName(), userId);
    }
    if (roles == null || roles.isEmpty())
        return true;
    if (log.isDebugEnabled())
        log.debug("saving user definition for '" + getName() + "'");
    final IErrorList<Integer> errors = ctx.getErrorList();
    final PreparedStatement stmt = conn
            .prepareStatement("INSERT INTO user_definition (user_id, role_id) VALUES(?,?);");
    try {
        stmt.setInt(1, userId);
        boolean success = true;
        for (XmlRoleName role : roles) {
            final Integer id = ctx.getRoleIds().get(role.getValue());
            if (id == null) {
                errors.add(role.getRow(), XmlRoleName.FIELD, CommonServerErrors.INVALID_VALUE);
                success = false;
            } else {
                stmt.setInt(2, id);
                stmt.addBatch();
            }
        }
        if (success && !Database.isBatchCompleted(stmt.executeBatch()))
            throw new InternalErrorException(
                    Util.formatInternalErrorDescription("failed to insert user definition"));
        return success;
    } finally {
        Database.close(stmt);
    }
}