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.schedoscope.metascope.tasks.repository.mysql.impl.TableEntityMySQLRepository.java

public void updateStatus(Connection connection, Set<TableEntity> tables) {
    String updateStatus = "update table_entity set status=?, last_transformation_timestamp=? where table_fqdn = ?";
    PreparedStatement updateStatusStmt = null;
    try {/*from  w ww  . ja  v a 2  s . c  om*/
        int updateStatusBatch = 0;
        connection.setAutoCommit(false);
        updateStatusStmt = connection.prepareStatement(updateStatus);
        for (TableEntity tableEntity : tables) {
            updateStatusStmt.setString(1, tableEntity.getStatus());
            updateStatusStmt.setLong(2, tableEntity.getLastTransformation());
            updateStatusStmt.setString(3, tableEntity.getFqdn());
            updateStatusStmt.addBatch();
            updateStatusBatch++;
            if (updateStatusBatch % 1000 == 0) {
                updateStatusStmt.executeBatch();
            }
        }
        updateStatusStmt.executeBatch();
        connection.commit();
        connection.setAutoCommit(true);
    } catch (SQLException e) {
        LOG.error("Could not update table", e);
    } finally {
        DbUtils.closeQuietly(updateStatusStmt);
    }
}

From source file:org.wso2.carbon.device.mgt.core.dao.impl.AbstractApplicationDAOImpl.java

@Override
public List<Integer> removeApplications(List<Application> apps, int tenantId)
        throws DeviceManagementDAOException {
    Connection conn = null;//from   w w  w  . j av  a2  s  .c o m
    PreparedStatement stmt = null;
    ResultSet rs = null;
    List<Integer> applicationIds = new ArrayList<>();
    try {
        conn = this.getConnection();
        conn.setAutoCommit(false);
        stmt = conn.prepareStatement("DELETE DM_APPLICATION WHERE APP_IDENTIFIER = ? AND TENANT_ID = ?",
                new String[] { "id" });

        for (Application app : apps) {
            stmt.setString(1, app.getApplicationIdentifier());
            stmt.setInt(2, tenantId);
            stmt.addBatch();
        }
        stmt.executeBatch();
        rs = stmt.getGeneratedKeys();
        if (rs.next()) {
            applicationIds.add(rs.getInt(1));
        }
        return applicationIds;
    } catch (SQLException e) {
        try {
            if (conn != null) {
                conn.rollback();
            }
        } catch (SQLException e1) {
            log.error("Error occurred while roll-backing the transaction", e);
        }
        throw new DeviceManagementDAOException("Error occurred while removing bulk application list", e);
    } finally {
        DeviceManagementDAOUtil.cleanupResources(stmt, rs);
    }
}

From source file:org.apache.sqoop.repository.derby.DerbyUpgradeGenericJdbcConnectorConfigAndInputNames.java

private void renameConfigInputs(long configId, Map<String, String> inputNameMap) {
    PreparedStatement statement = null;

    try {//from  w  w  w.j  a v  a2s.  c o m
        statement = connection.prepareStatement(DerbySchemaUpgradeQuery.QUERY_UPDATE_TABLE_SQ_INPUT_SQI_NAME);

        for (String inputName : inputNameMap.keySet()) {
            statement.setString(1, inputNameMap.get(inputName));
            statement.setString(2, inputName);
            statement.setLong(3, configId);
            statement.addBatch();

            LOG.debug("QUERY(" + DerbySchemaUpgradeQuery.QUERY_UPDATE_TABLE_SQ_INPUT_SQI_NAME + ") args ["
                    + inputNameMap.get(inputName) + "," + inputName + "," + configId + "]");
        }

        int[] updateCounts = statement.executeBatch();
        LOG.debug("QUERY(" + DerbySchemaUpgradeQuery.QUERY_UPDATE_TABLE_SQ_INPUT_SQI_NAME + ") update count: "
                + StringUtils.join(ArrayUtils.toObject(updateCounts), ","));
    } catch (SQLException e) {
        throw new SqoopException(DerbyRepoError.DERBYREPO_0002, e);
    } finally {
        handler.closeStatements(statement);
    }
}

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;//www. 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.wso2.carbon.device.mgt.core.dao.impl.ApplicationDAOImpl.java

@Override
public List<Integer> removeApplications(List<Application> apps, int tenantId)
        throws DeviceManagementDAOException {
    Connection conn = null;//w w w  . ja  v  a 2  s  .c o m
    PreparedStatement stmt = null;
    ResultSet rs = null;
    List<Integer> applicationIds = new ArrayList<>();
    try {
        conn = this.getConnection();
        conn.setAutoCommit(false);
        stmt = conn.prepareStatement("DELETE DM_APPLICATION WHERE APP_IDENTIFIER = ? AND TENANT_ID = ?",
                new String[] { "id" });

        for (Application app : apps) {
            stmt.setString(1, app.getApplicationIdentifier());
            stmt.setInt(2, tenantId);
            stmt.addBatch();
        }
        stmt.executeBatch();
        rs = stmt.getGeneratedKeys();
        if (rs.next()) {
            applicationIds.add(rs.getInt(1));
        }
        return applicationIds;
    } catch (SQLException e) {
        try {
            if (conn != null) {
                conn.rollback();
            }
        } catch (SQLException e1) {
            log.warn("Error occurred while roll-backing the transaction", e);
        }
        throw new DeviceManagementDAOException("Error occurred while removing bulk application list", e);
    } finally {
        DeviceManagementDAOUtil.cleanupResources(stmt, rs);
    }
}

From source file:org.apache.marmotta.kiwi.reasoner.test.persistence.JustificationPersistenceTest.java

/**
 * Test 1: create some triples through a repository connection (some inferred, some base), load a program, and
 * store justifications for the inferred triples based on rules and base triples. Test the different listing
 * functions./*  w  ww.j  ava2  s .com*/
 *
 */
@Test
public void testStoreJustifications() throws Exception {
    KiWiValueFactory v = (KiWiValueFactory) repository.getValueFactory();

    URI ctxb = v.createURI("http://localhost/context/default");
    URI ctxi = v.createURI("http://localhost/context/inferred");

    URI s1 = v.createURI("http://localhost/resource/" + RandomStringUtils.randomAlphanumeric(8));
    URI s2 = v.createURI("http://localhost/resource/" + RandomStringUtils.randomAlphanumeric(8));
    URI s3 = v.createURI("http://localhost/resource/" + RandomStringUtils.randomAlphanumeric(8));
    URI p1 = v.createURI("http://localhost/resource/" + RandomStringUtils.randomAlphanumeric(8));
    URI p2 = v.createURI("http://localhost/resource/" + RandomStringUtils.randomAlphanumeric(8));
    URI o1 = v.createURI("http://localhost/resource/" + RandomStringUtils.randomAlphanumeric(8));
    URI o2 = v.createURI("http://localhost/resource/" + RandomStringUtils.randomAlphanumeric(8));
    URI o3 = v.createURI("http://localhost/resource/" + RandomStringUtils.randomAlphanumeric(8));

    // first, load a sample program (it does not really matter what it actually contains, since we are not really
    // running the reasoner)
    KWRLProgramParserBase parser = new KWRLProgramParser(v,
            this.getClass().getResourceAsStream("test-001.kwrl"));
    Program p = parser.parseProgram();
    p.setName("test-001");

    KiWiReasoningConnection connection = rpersistence.getConnection();
    try {
        // should not throw an exception and the program should have a database ID afterwards
        connection.storeProgram(p);
        connection.commit();
    } finally {
        connection.close();
    }

    // then get a connection to the repository and create a number of triples, some inferred and some base
    RepositoryConnection con = repository.getConnection();
    try {
        con.add(s1, p1, o1);
        con.add(s2, p1, o2);
        con.add(s3, p1, o3);

        con.add(s1, p2, o1, ctxi);
        con.add(s2, p2, o2, ctxi);
        con.add(s3, p2, o3, ctxi);

        con.commit();
    } finally {
        con.close();
    }

    connection = rpersistence.getConnection();
    try {
        // retrieve the persisted triples and put them into two sets to build justifications
        List<Statement> baseTriples = asList(
                connection.listTriples(null, null, null, v.convert(ctxb), false, true));
        List<Statement> infTriples = asList(
                connection.listTriples(null, null, null, v.convert(ctxi), true, true));

        Assert.assertEquals("number of base triples was not 3", 3, baseTriples.size());
        Assert.assertEquals("number of inferred triples was not 3", 3, infTriples.size());

        // we manually update the "inferred" flag for all inferred triples, since this is not possible through the
        // repository API
        PreparedStatement updateInferred = connection.getJDBCConnection()
                .prepareStatement("UPDATE triples SET inferred = true WHERE id = ?");
        for (Statement stmt : infTriples) {
            KiWiTriple triple = (KiWiTriple) stmt;
            updateInferred.setLong(1, triple.getId());
            updateInferred.addBatch();
        }
        updateInferred.executeBatch();
        updateInferred.close();

        // now we create some justifications for the inferred triples and store them
        Set<Justification> justifications = new HashSet<Justification>();
        Justification j1 = new Justification();
        j1.getSupportingRules().add(p.getRules().get(0));
        j1.getSupportingRules().add(p.getRules().get(1));
        j1.getSupportingTriples().add((KiWiTriple) baseTriples.get(0));
        j1.getSupportingTriples().add((KiWiTriple) baseTriples.get(1));
        j1.setTriple((KiWiTriple) infTriples.get(0));
        justifications.add(j1);

        Justification j2 = new Justification();
        j2.getSupportingRules().add(p.getRules().get(1));
        j2.getSupportingTriples().add((KiWiTriple) baseTriples.get(1));
        j2.getSupportingTriples().add((KiWiTriple) baseTriples.get(2));
        j2.setTriple((KiWiTriple) infTriples.get(1));
        justifications.add(j2);

        connection.storeJustifications(justifications);
        connection.commit();

        // we should now have two justifications in the database
        PreparedStatement listJustifications = connection.getJDBCConnection()
                .prepareStatement("SELECT count(*) AS count FROM reasoner_justifications");
        ResultSet resultListJustifications = listJustifications.executeQuery();

        Assert.assertTrue(resultListJustifications.next());
        Assert.assertEquals(2, resultListJustifications.getInt("count"));
        resultListJustifications.close();
        connection.commit();

        PreparedStatement listSupportingTriples = connection.getJDBCConnection()
                .prepareStatement("SELECT count(*) AS count FROM reasoner_just_supp_triples");
        ResultSet resultListSupportingTriples = listSupportingTriples.executeQuery();

        Assert.assertTrue(resultListSupportingTriples.next());
        Assert.assertEquals(4, resultListSupportingTriples.getInt("count"));
        resultListSupportingTriples.close();
        connection.commit();

        PreparedStatement listSupportingRules = connection.getJDBCConnection()
                .prepareStatement("SELECT count(*) AS count FROM reasoner_just_supp_rules");
        ResultSet resultListSupportingRules = listSupportingRules.executeQuery();

        Assert.assertTrue(resultListSupportingRules.next());
        Assert.assertEquals(3, resultListSupportingRules.getInt("count"));
        resultListSupportingRules.close();
        connection.commit();

        // *** check listing justifications by base triple (supporting triple)

        // there should now be two justifications based on triple baseTriples.get(1))
        List<Justification> supported1 = asList(
                connection.listJustificationsBySupporting((KiWiTriple) baseTriples.get(1)));
        Assert.assertEquals("number of justifications is wrong", 2, supported1.size());
        Assert.assertThat("justifications differ", supported1, hasItems(j1, j2));

        // only j1 should be supported by triple baseTriples.get(0))
        List<Justification> supported2 = asList(
                connection.listJustificationsBySupporting((KiWiTriple) baseTriples.get(0)));
        Assert.assertEquals("number of justifications is wrong", 1, supported2.size());
        Assert.assertThat("justifications differ", supported2, allOf(hasItem(j1), not(hasItem(j2))));

        // only j2 should be supported by triple baseTriples.get(2))
        List<Justification> supported3 = asList(
                connection.listJustificationsBySupporting((KiWiTriple) baseTriples.get(2)));
        Assert.assertEquals("number of justifications is wrong", 1, supported3.size());
        Assert.assertThat("justifications differ", supported3, allOf(hasItem(j2), not(hasItem(j1))));

        // *** check listing justificatoins by supporting rule

        // there should now be two justifications based on triple p.getRules().get(1)
        List<Justification> supported4 = asList(connection.listJustificationsBySupporting(p.getRules().get(1)));
        Assert.assertEquals("number of justifications is wrong", 2, supported4.size());
        Assert.assertThat("justifications differ", supported4, hasItems(j1, j2));

        // only j1 should be supported by triple p.getRules().get(0)
        List<Justification> supported5 = asList(connection.listJustificationsBySupporting(p.getRules().get(0)));
        Assert.assertEquals("number of justifications is wrong", 1, supported5.size());
        Assert.assertThat("justifications differ", supported5, allOf(hasItem(j1), not(hasItem(j2))));

        // *** check listing justifications by supported (inferred) triple

        // there should now be one justification supporting infTriples.get(0)
        List<Justification> supported6 = asList(
                connection.listJustificationsForTriple((KiWiTriple) infTriples.get(0)));
        Assert.assertEquals("number of justifications is wrong", 1, supported6.size());
        Assert.assertThat("justifications differ", supported6, allOf(hasItem(j1), not(hasItem(j2))));

        // there should now be one justification supporting infTriples.get(1)
        List<Justification> supported7 = asList(
                connection.listJustificationsForTriple((KiWiTriple) infTriples.get(1)));
        Assert.assertEquals("number of justifications is wrong", 1, supported7.size());
        Assert.assertThat("justifications differ", supported7, allOf(hasItem(j2), not(hasItem(j1))));

        // there should now be no justification supporting infTriples.get(2)
        List<Justification> supported8 = asList(
                connection.listJustificationsForTriple((KiWiTriple) infTriples.get(2)));
        Assert.assertEquals("number of justifications is wrong", 0, supported8.size());

        // *** check listing unsupported triples
        List<KiWiTriple> unsupported = asList(connection.listUnsupportedTriples());
        Assert.assertEquals("number of unsupported triples is wrong", 1, unsupported.size());
        Assert.assertThat("unsupported triples differ", unsupported, hasItem((KiWiTriple) infTriples.get(2)));

        // now we delete justification 2; as a consequence,
        // - there should be only once justification left
        // - there should be two unsupported triples
        connection.deleteJustifications(Collections.singleton(j2));

        // we should now have one justifications in the database
        resultListJustifications = listJustifications.executeQuery();

        Assert.assertTrue(resultListJustifications.next());
        Assert.assertEquals(1, resultListJustifications.getInt("count"));
        resultListJustifications.close();
        connection.commit();

        resultListSupportingTriples = listSupportingTriples.executeQuery();

        Assert.assertTrue(resultListSupportingTriples.next());
        Assert.assertEquals(2, resultListSupportingTriples.getInt("count"));
        resultListSupportingTriples.close();
        connection.commit();

        resultListSupportingRules = listSupportingRules.executeQuery();

        Assert.assertTrue(resultListSupportingRules.next());
        Assert.assertEquals(2, resultListSupportingRules.getInt("count"));
        resultListSupportingRules.close();
        connection.commit();

        List<KiWiTriple> unsupported2 = asList(connection.listUnsupportedTriples());
        Assert.assertEquals("number of unsupported triples is wrong", 2, unsupported2.size());
        Assert.assertThat("unsupported triples differ", unsupported2, hasItem((KiWiTriple) infTriples.get(1)));

    } catch (BatchUpdateException ex) {
        if (ex.getNextException() != null) {
            ex.printStackTrace();
            throw ex.getNextException();
        } else {
            throw ex;
        }
    } finally {
        connection.close();
    }

}

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 {/* w ww  . ja  v a2 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:org.latticesoft.util.resource.dao.QueryService.java

/**
 * Exceutes the batch statements to the database
 * @param data the data for the batch. Must not be null and must be instances fo
 * @return the result of the batch execution
 *//*w  ww  . ja  va2  s  .c  o  m*/
public int[] executeBatch(Collection c) {
    if (c == null)
        return null;
    PreparedStatement pstmt = null;
    int[] res = null;
    try {
        if (log.isDebugEnabled()) {
            log.debug(sql);
        }
        pstmt = conn.prepareStatement(this.sql);
        Iterator iter = c.iterator();
        while (iter.hasNext()) {
            Object iterObj = iter.next();
            if (iterObj != null) {
                this.prepareBatch(pstmt, iterObj);
            }
        }
        res = pstmt.executeBatch();
    } catch (Exception e) {
        if (log.isErrorEnabled()) {
            log.error(e);
        }
    } finally {
        DatabaseUtil.closeStatement(pstmt);
        pstmt = null;
        if (this.closeConn) {
            DatabaseUtil.closeConnection(conn);
            conn = null;
        }

    }
    return res;
}

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

public void updateStatus(Connection connection, Set<ViewEntity> views) {
    String updateStatus = "update view_entity set status=?, transformation_start=?, transformation_end=? where url_path = ?";
    PreparedStatement updateStatusStmt = null;
    try {//from   w w w .j a v  a  2s. co  m
        int updateStatusBatch = 0;
        connection.setAutoCommit(false);
        updateStatusStmt = connection.prepareStatement(updateStatus);
        for (ViewEntity viewEntity : views) {
            updateStatusStmt.setString(1, viewEntity.getStatus());
            updateStatusStmt.setLong(2, viewEntity.getTransformationStart());
            updateStatusStmt.setLong(3, viewEntity.getTransformationEnd());
            updateStatusStmt.setString(4, viewEntity.getUrlPath());
            updateStatusStmt.addBatch();
            updateStatusBatch++;
            if (updateStatusBatch % 1000 == 0) {
                updateStatusStmt.executeBatch();
            }
        }
        updateStatusStmt.executeBatch();
        connection.commit();
        connection.setAutoCommit(true);
    } catch (SQLException e) {
        LOG.error("Could not update view", e);
    } finally {
        DbUtils.closeQuietly(updateStatusStmt);
    }
}

From source file:org.wso2.carbon.device.mgt.mobile.android.impl.dao.impl.AndroidFeatureDAOImpl.java

@Override
public boolean addFeatures(List<MobileFeature> mobileFeatures) throws MobileDeviceManagementDAOException {
    PreparedStatement stmt = null;
    MobileFeature mobileFeature;/*from w  w  w  .  j a v  a  2  s  .c  o m*/
    boolean status = false;
    Connection conn;
    try {
        conn = AndroidDAOFactory.getConnection();
        stmt = conn.prepareStatement("INSERT INTO AD_FEATURE(CODE, NAME, DESCRIPTION) VALUES (?, ?, ?)");
        for (int i = 0; i < mobileFeatures.size(); i++) {
            mobileFeature = mobileFeatures.get(i);
            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 AndroidFeatureManagementDAOException(
                "Error occurred while adding android features into the metadata repository", e);
    } finally {
        MobileDeviceManagementDAOUtil.cleanupResources(stmt, null);
    }
    return status;
}