Example usage for java.sql PreparedStatement executeBatch

List of usage examples for java.sql PreparedStatement 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.tera.common.database.query.CQueryService.java

@Override
public <T> boolean batchUpdate(String batchUpdate, BatchUpdateQuery<T> query, String errorMessage,
        boolean autoCommit) {
    Connection connection = null;
    PreparedStatement statement = null;

    try {/*from   w w  w.java2s  .  com*/
        connection = databaseFactory.getConnection();
        statement = connection.prepareStatement(batchUpdate);
        connection.setAutoCommit(autoCommit);

        Collection<T> items = query.getItems();
        for (T item : items) {
            query.handleBatch(statement, item);
            statement.addBatch();
        }
        statement.executeBatch();

        if (!autoCommit) {
            connection.commit();
        }
    } catch (Exception e) {
        if (errorMessage == null)
            log.error("Failed to execute BatchUpdate query {}", e, e);
        else
            log.error(errorMessage + " " + e, e);
        return false;
    } finally {

        close(null, statement, connection);
    }
    return true;
}

From source file:org.bytesoft.bytejta.supports.jdbc.RecoveredResource.java

public synchronized void forget(Xid[] xids) throws XAException {
    if (xids == null || xids.length == 0) {
        return;//from  w w  w.j  ava2s .  c  o m
    }

    String[] xidArray = new String[xids.length];

    for (int i = 0; i < xids.length; i++) {
        Xid xid = xids[i];

        byte[] globalTransactionId = xid.getGlobalTransactionId();
        byte[] branchQualifier = xid.getBranchQualifier();
        xidArray[i] = this.getIdentifier(globalTransactionId, branchQualifier);
    }

    Connection conn = null;
    PreparedStatement stmt = null;
    Boolean autoCommit = null;
    try {
        conn = this.dataSource.getConnection();
        autoCommit = conn.getAutoCommit();
        conn.setAutoCommit(false);
        stmt = conn.prepareStatement("delete from bytejta where xid = ?");
        for (int i = 0; i < xids.length; i++) {
            stmt.setString(1, xidArray[i]);
            stmt.addBatch();
        }
        stmt.executeBatch();
        conn.commit();
    } catch (Exception ex) {
        logger.error("Error occurred while forgetting resources.");

        try {
            conn.rollback();
        } catch (Exception sqlEx) {
            logger.error("Error occurred while rolling back local resources.", sqlEx);
        }

        boolean tableExists = false;
        try {
            tableExists = this.isTableExists(conn);
        } catch (Exception sqlEx) {
            logger.warn("Error occurred while forgeting local resources.", ex);
            throw new XAException(XAException.XAER_RMFAIL);
        }

        if (tableExists) {
            throw new XAException(XAException.XAER_RMERR);
        }
    } finally {
        if (autoCommit != null) {
            try {
                conn.setAutoCommit(autoCommit);
            } catch (SQLException sqlEx) {
                logger.error("Error occurred while configuring attribute 'autoCommit'.", sqlEx);
            }
        }

        this.closeQuietly(stmt);
        this.closeQuietly(conn);
    }
}

From source file:de.is24.infrastructure.gridfs.http.metadata.generation.PrimaryDbGenerator.java

private void writePrimaryFiles(PreparedStatement ps, YumPackage yumPackage, int pkgKey,
        Matcher<YumPackageFile> matcher) throws SQLException {
    int counter = 0;
    for (YumPackageDir dir : yumPackage.getPackageDirs()) {
        for (YumPackageFile file : select(dir.getFiles(), matcher)) {
            ps.setString(1, file.getDir() + file.getName());
            ps.setString(2, file.getType().toString().toLowerCase());
            ps.setInt(3, pkgKey);/*from   w ww.  ja  v a 2  s  .  co m*/
            ps.addBatch();
            counter++;
        }
    }
    if (counter > 0) {
        ps.executeBatch();
    }
}

From source file:com.splicemachine.derby.impl.sql.execute.operations.InsertOperationIT.java

@Test
public void testBatchInsert() throws Exception {
    Connection conn = methodWatcher.getOrCreateConnection();
    //insert a single record
    conn.setAutoCommit(false);//from  w  ww.j a  v  a  2  s.  c  o m
    PreparedStatement ps = conn.prepareStatement("insert into batch_test (col1,col2,col3) values (?,?,?)");
    int iterCount = 10;
    for (int i = 0; i < iterCount; i++) {
        ps.setInt(1, i);
        ps.setInt(2, i);
        ps.setInt(3, i);
        ps.addBatch();
    }
    int[] results = ps.executeBatch();
    Assert.assertEquals("results returned correct", 10, results.length);
    ps.close();
    ps = conn.prepareStatement("select count(*) from batch_test");
    ResultSet rs = ps.executeQuery();
    rs.next();
    Assert.assertEquals("results returned correct", 10, rs.getInt(1));
}

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

@Override
public void insertOrUpdate(Connection connection, List<ParameterValueEntity> parameterValues) {
    String insertParameterSql = "insert into parameter_value_entity ("
            + JDBCUtil.getDatabaseColumnsForClass(ParameterValueEntity.class) + ") values ("
            + JDBCUtil.getValuesCountForClass(ParameterValueEntity.class) + ") " + "on duplicate key update "
            + MySQLUtil.getOnDuplicateKeyString(ParameterValueEntity.class);
    PreparedStatement stmt = null;
    try {//  w w  w  .j av a  2s.  co m
        int batch = 0;
        connection.setAutoCommit(false);
        stmt = connection.prepareStatement(insertParameterSql);
        for (ParameterValueEntity parameterValueEntity : parameterValues) {
            stmt.setString(1, parameterValueEntity.getUrlPath());
            stmt.setString(2, parameterValueEntity.getKey());
            stmt.setString(3, parameterValueEntity.getValue());
            stmt.setString(4, parameterValueEntity.getTableFqdn());
            stmt.addBatch();
            batch++;
            if (batch % 1024 == 0) {
                stmt.executeBatch();
            }
        }
        stmt.executeBatch();
        connection.commit();
        connection.setAutoCommit(true);
    } catch (SQLException e) {
        LOG.error("Could not save parameter value", e);
    } finally {
        DbUtils.closeQuietly(stmt);
    }
}

From source file:com.liferay.portal.upgrade.util.Table.java

public void populateTableRows(PreparedStatement ps, boolean batch) throws Exception {

    if (_log.isDebugEnabled()) {
        _log.debug("Updating rows for " + getTableName());
    }//  w  ww. j av  a 2s.  c o  m

    if (batch) {
        ps.executeBatch();
    } else {
        ps.executeUpdate();
    }

    ps.close();
}

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

@Override
public void insertOrUpdate(Connection connection, List<SuccessorEntity> successors) {
    String insertSuccessorSql = "insert into successor_entity ("
            + JDBCUtil.getDatabaseColumnsForClass(SuccessorEntity.class) + ") values ("
            + JDBCUtil.getValuesCountForClass(SuccessorEntity.class) + ") " + "on duplicate key update "
            + MySQLUtil.getOnDuplicateKeyString(SuccessorEntity.class);
    PreparedStatement stmt = null;
    try {//from  w  w  w.  j  av  a 2s  . c  om
        int batch = 0;
        connection.setAutoCommit(false);
        stmt = connection.prepareStatement(insertSuccessorSql);
        for (SuccessorEntity successorEntity : successors) {
            stmt.setString(1, successorEntity.getUrlPath());
            stmt.setString(2, successorEntity.getSuccessorUrlPath());
            stmt.setString(3, successorEntity.getSuccessorFqdn());
            stmt.setString(4, successorEntity.getInternalViewId());
            stmt.addBatch();
            batch++;
            if (batch % 1024 == 0) {
                stmt.executeBatch();
            }
        }
        stmt.executeBatch();
        connection.commit();
        connection.setAutoCommit(true);
    } catch (SQLException e) {
        LOG.error("Could not save successor", e);
    } finally {
        DbUtils.closeQuietly(stmt);
    }
}

From source file:com.mtgi.analytics.sql.BehaviorTrackingDataSourceTest.java

@Test
public void testPreparedStatement() throws Exception {
    //test tracking through the prepared statement API, which should also
    //log parameters in the events.
    PreparedStatement stmt = conn.prepareStatement("insert into TEST_TRACKING values (?, ?, ?)");
    stmt.setLong(1, 1);//from   w ww .  j  ava  2s.  com
    stmt.setString(2, "hello");
    stmt.setObject(3, null, Types.VARCHAR);
    assertEquals(1, stmt.executeUpdate());

    //test support for batching.  each batch should log 1 event.
    stmt.setLong(1, 3);
    stmt.setString(2, "batch");
    stmt.setObject(3, "1", Types.VARCHAR);
    stmt.addBatch();

    stmt.setLong(1, 4);
    stmt.setString(2, "batch");
    stmt.setObject(3, "2", Types.VARCHAR);
    stmt.addBatch();
    stmt.executeBatch();

    //back to a regular old update.
    stmt.setLong(1, 2);
    stmt.setObject(2, "goodbye", Types.VARCHAR);
    stmt.setNull(3, Types.VARCHAR);
    assertEquals(1, stmt.executeUpdate());

    stmt = conn.prepareStatement("update TEST_TRACKING set DESCRIPTION = 'world'");
    assertEquals(4, stmt.executeUpdate());

    stmt = conn.prepareStatement("select ID from TEST_TRACKING order by ID");
    ResultSet rs = stmt.executeQuery();
    int index = 0;
    long[] keys = { 1L, 2L, 3L, 4L };
    while (rs.next())
        assertEquals(keys[index++], rs.getLong(1));
    rs.close();
    assertEquals(4, index);

    manager.flush();
    assertEventDataMatches("BehaviorTrackingDataSourceTest.testPreparedStatement-result.xml");
}

From source file:com.rajaram.bookmark.dao.BookmarkDaoImpl.java

@Override
public void insertBookmarks(String userName, List<Bookmark> bookmarkList) {

    String sql = "INSERT INTO " + tableName + " (user_name, url, "
            + "url_name, folder_name) VALUES (?, ?, ?, ?)";

    Connection conn = null;//www  .j a  v  a2  s  .  c o  m
    final int batchSize = 1000;
    int count = 0;

    try {
        conn = dataSource.getConnection();

        PreparedStatement prepareStatement = conn.prepareStatement(sql);

        for (Bookmark bookmark : bookmarkList) {

            prepareStatement.setString(BookmarkSqlTableConstants.userNameIndex, userName);
            prepareStatement.setString(BookmarkSqlTableConstants.urlIndex, bookmark.getUrl());
            prepareStatement.setString(BookmarkSqlTableConstants.urlNameIndex, bookmark.getName());
            prepareStatement.setString(BookmarkSqlTableConstants.folderNameIndex, bookmark.getFolder());

            prepareStatement.addBatch();

            if (++count % batchSize == 0) {
                prepareStatement.executeBatch();
            }
        }
        prepareStatement.executeBatch(); // insert remaining records                   
        prepareStatement.close();

    } catch (SQLException e) {
        throw new RuntimeException(e);
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
            }
        }
    }
}

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

@Override
public void insertOrUpdate(Connection connection, List<ViewDependencyEntity> dependencies) {
    String insertDependencySql = "insert into view_dependency_entity ("
            + JDBCUtil.getDatabaseColumnsForClass(ViewDependencyEntity.class) + ") values ("
            + JDBCUtil.getValuesCountForClass(ViewDependencyEntity.class) + ") " + "on duplicate key update "
            + MySQLUtil.getOnDuplicateKeyString(ViewDependencyEntity.class);
    PreparedStatement stmt = null;
    try {//from   w  w w.  ja  v  a2  s . c  o m
        int batch = 0;
        connection.setAutoCommit(false);
        stmt = connection.prepareStatement(insertDependencySql);
        for (ViewDependencyEntity viewDependencyEntity : dependencies) {
            stmt.setString(1, viewDependencyEntity.getUrlPath());
            stmt.setString(2, viewDependencyEntity.getDependencyUrlPath());
            stmt.setString(3, viewDependencyEntity.getDependencyFqdn());
            stmt.setString(4, viewDependencyEntity.getInternalViewId());
            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 dependency", e);
    } finally {
        DbUtils.closeQuietly(stmt);
    }
}