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:com.glaf.base.test.service.MxMixFeatureTestService.java

@Transactional
public void run() {
    log.debug("-------------------start run-------------------");
    for (int i = 0; i < 2; i++) {
        SysLog bean = new SysLog();
        bean.setAccount("test");
        bean.setIp("127.0.0.1");
        bean.setOperate("add");
        sysLogService.create(bean);/*  w  w  w  .jav  a2 s  .com*/
    }

    String sql = "insert into SYS_LOG(ID, ACCOUNT, IP, CREATETIME, MODULEID, OPERATE, FLAG, TIMEMS) values (?, ?, ?, ?, ?, ?, ?, ?) ";
    Connection connection = null;
    PreparedStatement psmt = null;
    try {
        connection = DataSourceUtils.getConnection(jdbcTemplate.getDataSource());
        System.out.println("connection:" + connection.toString());
        psmt = connection.prepareStatement(sql);
        for (int i = 0; i < 2; i++) {
            psmt.setLong(1, idGenerator.nextId());
            psmt.setString(2, "test2");
            psmt.setString(3, "192.168.0.100");
            psmt.setTimestamp(4, DateUtils.toTimestamp(new Date()));
            psmt.setString(5, "xx");
            psmt.setString(6, "Y");
            psmt.setInt(7, 1);
            psmt.setLong(8, i * i);
            psmt.addBatch();
        }
        psmt.executeBatch();
        psmt.close();
    } catch (Exception ex) {
        ex.printStackTrace();
        log.error(ex);
        throw new RuntimeException(ex);
    } finally {
        JdbcUtils.close(psmt);
    }
    log.debug("-------------------end run-------------------");
}

From source file:org.batoo.jpa.core.impl.jdbc.dbutils.QueryRunner.java

/**
 * Calls update after checking the parameters to ensure nothing is null.
 * /* ww  w .j  a v a2 s .co  m*/
 * @param conn
 *            The connection to use for the batch call.
 * @param closeConn
 *            True if the connection should be closed, false otherwise.
 * @param sql
 *            The SQL statement to execute.
 * @param params
 *            An array of query replacement parameters. Each row in this array is one set of batch replacement values.
 * @return The number of rows updated in the batch.
 * @throws SQLException
 *             If there are database or parameter errors.
 */
private int[] batch(Connection conn, boolean closeConn, String sql, Object[][] params) throws SQLException {
    if (conn == null) {
        throw new SQLException("Null connection");
    }

    if (sql == null) {
        if (closeConn) {
            this.close(conn);
        }
        throw new SQLException("Null SQL statement");
    }

    if (params == null) {
        if (closeConn) {
            this.close(conn);
        }
        throw new SQLException("Null parameters. If parameters aren't need, pass an empty array.");
    }

    PreparedStatement stmt = null;
    int[] rows = null;
    try {
        stmt = this.prepareStatement(conn, sql);

        for (final Object[] param : params) {
            this.fillStatement(stmt, param);
            stmt.addBatch();
        }
        rows = stmt.executeBatch();

    } catch (final SQLException e) {
        this.rethrow(e, sql, (Object[]) params);
    } finally {
        this.close(stmt);
        if (closeConn) {
            this.close(conn);
        }
    }

    return rows;
}

From source file:org.obm.push.backend.obm22.mail.EmailCacheStorage.java

@Override
public void deleteMessage(Integer devId, Integer collectionId, Long mailUid) {
    PreparedStatement del = null;
    if (logger.isDebugEnabled()) {
        logger.debug(debugName + " should run a batch with 1 deletions.");
    }//from ww  w.j a va  2  s  .c  o  m
    Connection con = null;
    try {
        con = OBMPoolActivator.getDefault().getConnection();
        del = con.prepareStatement(
                "DELETE FROM opush_sync_mail WHERE collection_id=? AND device_id=? AND mail_uid=?");
        del.setInt(1, collectionId);
        del.setInt(2, devId);
        del.setInt(3, mailUid.intValue());
        del.addBatch();

        del.executeBatch();
    } catch (SQLException e) {
        logger.error(e.getMessage(), e);
    } finally {
        JDBCUtils.cleanup(con, del, null);
    }
}

From source file:com.github.tosdan.utils.sql.MyQueryRunner.java

/**
* Calls update after checking the parameters to ensure nothing is null.
* @param conn The connection to use for the batch call.
* @param closeConn True if the connection should be closed, false otherwise.
* @param sql The SQL statement to execute.
* @param params An array of query replacement parameters.  Each row in
* this array is one set of batch replacement values.
* @return The number of rows updated in the batch.
* @throws SQLException If there are database or parameter errors.
*///w w w.j  a v  a2 s  .c om
private int[] batch(Connection conn, boolean closeConn, String sql, Object[][] params) throws SQLException {
    if (conn == null) {
        throw new SQLException("Null connection");
    }

    if (sql == null) {
        if (closeConn) {
            close(conn);
        }
        throw new SQLException("Null SQL statement");
    }

    if (params == null) {
        if (closeConn) {
            close(conn);
        }
        throw new SQLException("Null parameters. If parameters aren't need, pass an empty array.");
    }

    PreparedStatement stmt = null;
    int[] rows = null;
    try {
        stmt = this.prepareStatement(conn, sql);

        for (int i = 0; i < params.length; i++) {
            this.fillStatement(stmt, params[i]);
            stmt.addBatch();
        }
        rows = stmt.executeBatch();

    } catch (SQLException e) {
        this.rethrow(e, sql, (Object[]) params);
    } finally {
        close(stmt);
        if (closeConn) {
            close(conn);
        }
    }

    return rows;
}

From source file:org.schedoscope.metascope.tasks.repository.mysql.impl.FieldEntityMySQLRepository.java

@Override
public void insertOrUpdate(Connection connection, List<FieldEntity> fields) {
    String insertFieldSql = "insert into field_entity ("
            + JDBCUtil.getDatabaseColumnsForClass(FieldEntity.class) + ") values ("
            + JDBCUtil.getValuesCountForClass(FieldEntity.class) + ") " + "on duplicate key update "
            + MySQLUtil.getOnDuplicateKeyString(FieldEntity.class);
    PreparedStatement stmt = null;
    try {//  w  ww  . j  ava 2s.  c  om
        int batch = 0;
        connection.setAutoCommit(false);
        stmt = connection.prepareStatement(insertFieldSql);
        for (FieldEntity fieldEntity : fields) {
            stmt.setString(1, fieldEntity.getFqdn());
            stmt.setString(2, fieldEntity.getName());
            stmt.setString(3, fieldEntity.getType());
            stmt.setString(4, fieldEntity.getDescription());
            stmt.setInt(5, fieldEntity.getFieldOrder());
            stmt.setBoolean(6, fieldEntity.isParameterField());
            stmt.setString(7, fieldEntity.getFqdn());
            stmt.addBatch();
            batch++;
            if (batch % 1024 == 0) {
                stmt.executeBatch();
            }
        }
        stmt.executeBatch();
        connection.commit();
        connection.setAutoCommit(true);
    } catch (SQLException e) {
        LOG.error("Could not save field", e);
    } finally {
        DbUtils.closeQuietly(stmt);
    }
}

From source file:eionet.cr.dao.virtuoso.VirtuosoHarvestScriptDAO.java

/**
 * @see eionet.cr.dao.HarvestScriptDAO#delete(eionet.cr.dto.HarvestScriptDTO)
 *//*from w w  w . j  a v  a  2s.c o  m*/
@Override
public void delete(List<Integer> ids) throws DAOException {

    if (ids == null || ids.isEmpty()) {
        return;
    }

    Connection conn = null;
    PreparedStatement stmt = null;
    try {
        conn = getSQLConnection();
        conn.setAutoCommit(false);
        stmt = conn.prepareStatement(DELETE_SQL);
        for (Integer id : ids) {
            stmt.setInt(1, id);
            stmt.addBatch();
        }
        stmt.executeBatch();
        conn.commit();
    } catch (SQLException e) {
        SQLUtil.rollback(conn);
        throw new DAOException(e.getMessage(), e);
    } finally {
        SQLUtil.close(stmt);
        SQLUtil.close(conn);
    }
}

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 v  a 2  s  .com

    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:com.nabla.dc.server.xml.settings.XmlRole.java

public boolean save(final Connection conn, final SaveContext ctx) throws SQLException, DispatchException {
    final IErrorList<Integer> errors = ctx.getErrorList();
    if (ctx.getPrivilegeIds().containsKey(getName())) {
        if (log.isDebugEnabled())
            log.debug("role '" + getName() + "' is already defined as a privilege");
        errors.add(name.getRow(), "name", CommonServerErrors.DUPLICATE_ENTRY);
        return false;
    }//  ww  w. j  av a  2  s  .c o  m
    Integer roleId = ctx.getRoleIds().get(getName());
    if (roleId != null) {
        if (ctx.getOption() == SqlInsertOptions.APPEND) {
            if (log.isDebugEnabled())
                log.debug("skipping role '" + getName() + "' (already defined)");
            return true;
        }
        // nothing to update for the actual role record
        // but clear definition before replacing it with new definition
        Database.executeUpdate(conn, "DELETE FROM role_definition WHERE role_id=?;", roleId);
    } else {
        if (log.isDebugEnabled())
            log.debug("adding role '" + getName() + "'");
        roleId = Database.addRecord(conn, "INSERT INTO role (name,uname) VALUES(?,?);", getName(),
                getName().toUpperCase());
        if (roleId == null)
            throw new InternalErrorException(
                    Util.formatInternalErrorDescription("failed to insert role '" + getName() + "'"));
        ctx.getRoleIds().put(getName(), roleId);
    }
    if (definition == null || definition.isEmpty())
        return true;
    if (log.isDebugEnabled())
        log.debug("saving role definition for '" + getName() + "'");
    final PreparedStatement stmt = conn
            .prepareStatement("INSERT INTO role_definition (role_id, child_role_id) VALUES(?,?);");
    try {
        stmt.setInt(1, roleId);
        boolean success = true;
        for (XmlRoleName role : definition) {
            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 role definition"));
        return success;
    } finally {
        Database.close(stmt);
    }
}

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

/**
 * @see CustomTaskChange#execute(Database)
 */// w w  w  .j  a  v  a  2s .c  o m
@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 user_id, salt, secret_answer FROM users WHERE secret_answer IS NOT NULL");
        pStmt = connection.prepareStatement("UPDATE users SET secret_answer = ? WHERE user_id = ?");
        while (rs.next()) {
            String answer = rs.getString("secret_answer");
            String salt = rs.getString("salt");
            String encryptedAnswer = Security.encodeString(answer.toLowerCase() + salt);

            pStmt.setString(1, encryptedAnswer);
            pStmt.setInt(2, rs.getInt("user_id"));
            pStmt.addBatch();
        }
        pStmt.executeBatch();
    } catch (DatabaseException e) {
        throw new CustomChangeException("Failed to update secret answers: " + e);
    } catch (SQLException e) {
        throw new CustomChangeException("Failed to update secret answers: " + 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:eionet.cr.dao.virtuoso.VirtuosoHarvestScriptDAO.java

/**
 * @see eionet.cr.dao.HarvestScriptDAO#activateDeactivate(java.util.List)
 *//* www  .j a va2 s . c  om*/
@Override
public void activateDeactivate(List<Integer> ids) throws DAOException {

    if (ids == null || ids.isEmpty()) {
        return;
    }

    Connection conn = null;
    PreparedStatement stmt = null;
    try {
        conn = getSQLConnection();
        conn.setAutoCommit(false);
        stmt = conn.prepareStatement(ACTIVATE_DEACTIVATE_SQL);
        for (Integer id : ids) {
            stmt.setInt(1, id);
            stmt.addBatch();
        }
        stmt.executeBatch();
        conn.commit();
    } catch (SQLException e) {
        SQLUtil.rollback(conn);
        throw new DAOException(e.getMessage(), e);
    } finally {
        SQLUtil.close(stmt);
        SQLUtil.close(conn);
    }
}