Example usage for java.sql PreparedStatement addBatch

List of usage examples for java.sql PreparedStatement addBatch

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

Introduction

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

Prototype

void addBatch() throws SQLException;

Source Link

Document

Adds a set of parameters to this PreparedStatement object's batch of commands.

Usage

From source file:com.agiletec.plugins.jpcrowdsourcing.aps.system.services.ideainstance.IdeaInstanceDAO.java

private void addIdeaInstanceGroups(String code, List<String> groups, Connection conn) {
    if (null == groups || groups.isEmpty())
        return;/*  w  w w.  ja  v  a  2  s.c  o  m*/
    PreparedStatement stat = null;
    try {
        stat = conn.prepareStatement(ADD_IDEAINSTANCE_GROUP);
        Iterator<String> groupIter = groups.iterator();

        while (groupIter.hasNext()) {
            String groupName = groupIter.next();
            int i = 1;
            stat.setString(i++, code);
            stat.setString(i++, groupName);
            stat.addBatch();
            stat.clearParameters();
        }
        stat.executeBatch();
    } catch (Throwable t) {
        _logger.error("Error creating ideainstance-group relation for {}", code, t);
        throw new RuntimeException("Error creating ideainstance-group relation for " + code, t);
    } finally {
        this.closeDaoResources(null, stat, null);
    }
}

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

public void insertOrUpdatePartial(Connection connection, Iterable<ViewEntity> views) {
    String insertViewSql = "insert into view_entity (url_path, fqdn, status, parameter_string, internal_view_id, table_fqdn) values "
            + "(?, ?, ?, ?, ?, ?) on duplicate key update url_path=values(url_path), fqdn=values(fqdn), "
            + "status=values(status), parameter_string=values(parameter_string), internal_view_id=values(internal_view_id), "
            + "table_fqdn=values(table_fqdn)";
    PreparedStatement stmt = null;
    try {//from www .j  a v  a 2  s  .c om
        int batch = 0;
        connection.setAutoCommit(false);
        stmt = connection.prepareStatement(insertViewSql);
        for (ViewEntity viewEntity : views) {
            stmt.setString(1, viewEntity.getUrlPath());
            stmt.setString(2, viewEntity.getFqdn());
            stmt.setString(3, viewEntity.getStatus());
            stmt.setString(4, viewEntity.getParameterString());
            stmt.setString(5, viewEntity.getInternalViewId());
            stmt.setString(6, viewEntity.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 view", e);
    } finally {
        DbUtils.closeQuietly(stmt);
    }
}

From source file:herddb.cli.HerdDBCLI.java

@SuppressFBWarnings("SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING")
private static void executeStatementInSqlFile(String query, final Statement statement,
        SqlFileStatus sqlFileStatus) throws SQLException, ScriptException {
    query = query.trim();//  w w w. j  a  va2 s  .c  o m

    if (query.isEmpty() || query.startsWith("--")) {
        return;
    }
    String formattedQuery = query.toLowerCase();
    if (formattedQuery.endsWith(";")) {
        // mysqldump
        formattedQuery = formattedQuery.substring(0, formattedQuery.length() - 1);
    }
    if (formattedQuery.equals("exit") || formattedQuery.equals("quit")) {
        throw new SQLException("explicit END of script with '" + formattedQuery + "'");
    }
    if (sqlFileStatus.frommysqldump
            && (formattedQuery.startsWith("lock tables") || formattedQuery.startsWith("unlock tables"))) {
        // mysqldump
        return;
    }
    Boolean setAutoCommit = null;
    if (formattedQuery.startsWith("autocommit=")) {
        String value = "";
        if (formattedQuery.split("=").length > 1) {
            value = formattedQuery.split("=")[1];
        }
        switch (value) {
        case "true":
            setAutoCommit = true;
            break;
        case "false":
            setAutoCommit = false;
            break;
        default:
            System.out.println("No valid value for autocommit. Only true and false allowed.");
            return;
        }
    }
    if (sqlFileStatus.verbose) {
        System.out.println("Executing query:" + query);
    }
    try {
        if (setAutoCommit != null) {
            statement.getConnection().setAutoCommit(setAutoCommit);
            System.out.println("Set autocommit=" + setAutoCommit + " executed.");
            return;
        }
        if (formattedQuery.equals("commit")) {
            sqlFileStatus.flushAndCommit(statement.getConnection());

            System.out.println("Commit executed.");
            return;
        }
        if (formattedQuery.equals("rollback")) {
            sqlFileStatus.rollback(statement.getConnection());
            statement.getConnection().rollback();
            System.out.println("Rollback executed.");
            return;
        }

        QueryWithParameters rewritten = null;
        if (sqlFileStatus.rewritestatements) {
            rewritten = rewriteQuery(query, sqlFileStatus.tableSpaceMapper, sqlFileStatus.frommysqldump);
        }
        if (rewritten != null) {
            if (rewritten.schema != null) {
                HerdDBConnection connection = statement.getConnection().unwrap(HerdDBConnection.class);
                if (connection != null && !connection.getSchema().equalsIgnoreCase(rewritten.schema)) {
                    sqlFileStatus.flushAndCommit(connection);
                    commitAndChangeSchema(connection, rewritten.schema);
                }
            }
            PreparedStatement ps = sqlFileStatus.prepareStatement(statement.getConnection(), rewritten.query);
            int i = 1;
            for (Object o : rewritten.jdbcParameters) {
                ps.setObject(i++, o);
            }
            ps.addBatch();
        } else {
            PreparedStatement ps = sqlFileStatus.prepareStatement(statement.getConnection(), query);
            ps.addBatch();
        }
        sqlFileStatus.countPendingOp();
    } catch (SQLException err) {
        if (sqlFileStatus.ignoreerrors) {
            println("ERROR:" + err);
            return;
        } else {
            throw err;
        }
    }
}

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

/**
 * @see eionet.cr.dao.HarvestScriptDAO#delete(eionet.cr.dto.HarvestScriptDTO)
 *///w  w  w .  ja v  a2  s  .c  om
@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:edu.harvard.i2b2.crc.dao.pdo.PdoQueryConceptDao.java

private void uploadTempTable(Statement tempStmt, String tempTable, List<String> patientNumList)
        throws SQLException {
    String createTempInputListTable = "create table " + tempTable + " ( char_param1 varchar(100) )";
    tempStmt.executeUpdate(createTempInputListTable);
    log.debug("created temp table" + tempTable);
    PreparedStatement preparedStmt = tempStmt.getConnection()
            .prepareStatement("insert into " + tempTable + " values (?)");
    // load to temp table
    // TempInputListInsert inputListInserter = new
    // TempInputListInsert(dataSource,TEMP_PDO_INPUTLIST_TABLE);
    // inputListInserter.setBatchSize(100);
    int i = 0;//from www. j  a v  a  2 s.  co  m
    for (String singleValue : patientNumList) {
        preparedStmt.setString(1, singleValue);
        preparedStmt.addBatch();
        log.debug("adding batch [" + i + "] " + singleValue);
        i++;
        if (i % 100 == 0) {
            log.debug("batch insert [" + i + "]");
            preparedStmt.executeBatch();

        }
    }
    log.debug("batch insert [" + i + "]");
    preparedStmt.executeBatch();
}

From source file:com.ea.core.orm.handle.impl.MybatisSqlORMHandle.java

@Override
protected Object execute(ORMParamsDTO dto) throws Exception {
    // TODO Auto-generated method stub
    Connection connection = this.getMybatisSessionTemplate().getConnection();
    final ORMParamsDTO tmp = dto;
    PreparedStatement ps = connection.prepareStatement(tmp.getSqlid());
    if (tmp.getParam() != null) {
        Object data = tmp.getParam();
        if (data instanceof Object[]) {
            Object[] array = (Object[]) data;
            int index = 1;
            for (Object obj : array) {
                setParam(ps, index++, obj);
            }/*from   w  w w.j  a  v a  2  s .com*/
        } else if (data instanceof Collection) {
            for (Object array : (Collection) data) {
                if (array instanceof Object[]) {
                    int index = 1;
                    for (Object obj : (Object[]) array) {
                        setParam(ps, index++, obj);
                    }
                    ps.addBatch();
                } else {
                    throw new SQLException("SQLDB?Object[]???!");
                }

            }
        } else {
            throw new SQLException(
                    "SQLDB?Object[]Collection???!");
        }
    }
    return ps.executeQuery();
}

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.");
    }//  ww w  .j  a  v a2s. c om
    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:org.openmrs.util.databasechange.EncryptSecretAnswersChangeSet.java

/**
 * @see CustomTaskChange#execute(Database)
 *//* w w w .j  ava2 s .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:org.apache.hive.hplsql.Copy.java

/**
 * Copy the query results to another table
 * @throws Exception /*from   ww w.  j  av a2  s .c  o  m*/
 */
void copyToTable(HplsqlParser.Copy_stmtContext ctx, Query query) throws Exception {
    ResultSet rs = query.getResultSet();
    if (rs == null) {
        return;
    }
    ResultSetMetaData rm = rs.getMetaData();
    int cols = rm.getColumnCount();
    int rows = 0;
    if (trace) {
        trace(ctx, "SELECT executed: " + cols + " columns");
    }
    Connection conn = exec.getConnection(targetConn);
    StringBuilder sql = new StringBuilder();
    sql.append("INSERT INTO " + sqlInsertName + " VALUES (");
    for (int i = 0; i < cols; i++) {
        sql.append("?");
        if (i + 1 < cols) {
            sql.append(",");
        }
    }
    sql.append(")");
    PreparedStatement ps = conn.prepareStatement(sql.toString());
    long start = timer.start();
    long prev = start;
    boolean batchOpen = false;
    while (rs.next()) {
        for (int i = 1; i <= cols; i++) {
            ps.setObject(i, rs.getObject(i));
        }
        rows++;
        if (batchSize > 1) {
            ps.addBatch();
            batchOpen = true;
            if (rows % batchSize == 0) {
                ps.executeBatch();
                batchOpen = false;
            }
        } else {
            ps.executeUpdate();
        }
        if (trace && rows % 100 == 0) {
            long cur = timer.current();
            if (cur - prev > 10000) {
                trace(ctx, "Copying rows: " + rows + " (" + rows / ((cur - start) / 1000) + " rows/sec)");
                prev = cur;
            }
        }
    }
    if (batchOpen) {
        ps.executeBatch();
    }
    ps.close();
    exec.returnConnection(targetConn, conn);
    exec.setRowCount(rows);
    long elapsed = timer.stop();
    if (info) {
        info(ctx, "COPY completed: " + rows + " row(s), " + timer.format() + ", " + rows / (elapsed / 1000)
                + " rows/sec");
    }
}

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

/**
 * @see eionet.cr.dao.HarvestScriptDAO#activateDeactivate(java.util.List)
 *//*  w ww. ja va 2  s .c  o m*/
@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);
    }
}