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.wso2.carbon.policy.mgt.core.dao.impl.feature.GenericFeatureDAOImpl.java

@Override
public List<ProfileFeature> addProfileFeatures(List<ProfileFeature> features, int profileId)
        throws FeatureManagerDAOException {

    Connection conn;//w ww . j  a  v a  2 s . c  o  m
    PreparedStatement stmt = null;
    ResultSet generatedKeys = null;
    int tenantId = PrivilegedCarbonContext.getThreadLocalCarbonContext().getTenantId();

    try {
        conn = this.getConnection();
        String query = "INSERT INTO DM_PROFILE_FEATURES (PROFILE_ID, FEATURE_CODE, DEVICE_TYPE, CONTENT, "
                + "TENANT_ID) VALUES (?, ?, ?, ?, ?)";
        stmt = conn.prepareStatement(query, new String[] { "id" });

        for (ProfileFeature feature : features) {
            stmt.setInt(1, profileId);
            stmt.setString(2, feature.getFeatureCode());
            stmt.setString(3, feature.getDeviceType());
            // if (conn.getMetaData().getDriverName().contains("H2")) {
            //    stmt.setBytes(4, PolicyManagerUtil.getBytes(feature.getContent()));
            // } else {
            stmt.setBytes(4, PolicyManagerUtil.getBytes(feature.getContent()));
            //}
            stmt.setInt(5, tenantId);
            stmt.addBatch();
            //Not adding the logic to check the size of the stmt and execute if the size records added is over 1000
        }
        stmt.executeBatch();

        generatedKeys = stmt.getGeneratedKeys();
        int i = 0;

        while (generatedKeys.next()) {
            features.get(i).setId(generatedKeys.getInt(1));
            i++;
        }

    } catch (SQLException | IOException e) {
        throw new FeatureManagerDAOException("Error occurred while adding the feature list to the database.",
                e);
    } finally {
        PolicyManagementDAOUtil.cleanupResources(stmt, generatedKeys);
    }
    return features;
}

From source file:org.wso2.carbon.policy.mgt.core.dao.impl.feature.SQLServerFeatureDAOImpl.java

@Override
public List<ProfileFeature> addProfileFeatures(List<ProfileFeature> features, int profileId)
        throws FeatureManagerDAOException {

    Connection conn;//from  w w  w.j  a  v  a 2 s. c o m
    PreparedStatement stmt = null;
    ResultSet generatedKeys = null;
    int tenantId = PrivilegedCarbonContext.getThreadLocalCarbonContext().getTenantId();

    try {
        conn = this.getConnection();
        String query = "INSERT INTO DM_PROFILE_FEATURES (PROFILE_ID, FEATURE_CODE, DEVICE_TYPE, CONTENT, "
                + "TENANT_ID) VALUES (?, ?, ?, ?, ?)";
        stmt = conn.prepareStatement(query, new String[] { "id" });

        for (ProfileFeature feature : features) {
            stmt.setInt(1, profileId);
            stmt.setString(2, feature.getFeatureCode());
            stmt.setString(3, feature.getDeviceType());
            // if (conn.getMetaData().getDriverName().contains("H2")) {
            //    stmt.setBytes(4, PolicyManagerUtil.getBytes(feature.getContent()));
            // } else {
            stmt.setBytes(4, PolicyManagerUtil.getBytes(feature.getContent()));
            //}
            stmt.setInt(5, tenantId);
            stmt.addBatch();
            //Not adding the logic to check the size of the stmt and execute if the size records added is over 1000
        }
        stmt.executeBatch();
        // This logic has been commented out due to getGeneratedKeys method is not supported in MSSQL.
        //            generatedKeys = stmt.getGeneratedKeys();
        //            int i = 0;
        //
        //            while (generatedKeys.next()) {
        //                features.get(i).setId(generatedKeys.getInt(1));
        //                i++;
        //            }

    } catch (SQLException | IOException e) {
        throw new FeatureManagerDAOException("Error occurred while adding the feature list to the database.",
                e);
    } finally {
        PolicyManagementDAOUtil.cleanupResources(stmt, generatedKeys);
    }
    return features;
}

From source file:org.wso2.carbon.policy.mgt.core.dao.impl.MonitoringDAOImpl.java

@Override
public void addNonComplianceFeatures(int policyComplianceStatusId, int deviceId,
        List<ComplianceFeature> complianceFeatures) throws MonitoringDAOException {
    Connection conn;/*ww w .  jav a 2 s  .  co  m*/
    PreparedStatement stmt = null;
    int tenantId = PrivilegedCarbonContext.getThreadLocalCarbonContext().getTenantId();
    try {
        conn = this.getConnection();
        String query = "INSERT INTO DM_POLICY_COMPLIANCE_FEATURES (COMPLIANCE_STATUS_ID, FEATURE_CODE, STATUS, "
                + "TENANT_ID) VALUES (?, ?, ?, ?) ";
        stmt = conn.prepareStatement(query);
        for (ComplianceFeature feature : complianceFeatures) {
            stmt.setInt(1, policyComplianceStatusId);
            stmt.setString(2, feature.getFeatureCode());
            if (feature.isCompliant()) {
                stmt.setInt(3, 1);
            } else {
                stmt.setInt(3, 0);
            }
            stmt.setInt(4, tenantId);
            stmt.addBatch();
        }
        stmt.executeBatch();
    } catch (SQLException e) {
        throw new MonitoringDAOException(
                "Error occurred while adding the none compliance features to the " + "database.", e);
    } finally {
        PolicyManagementDAOUtil.cleanupResources(stmt, null);
    }
}

From source file:com.wso2telco.dep.mediator.dao.USSDDAO.java

public void moUssdSubscriptionEntry(List<OperatorSubscriptionDTO> domainsubs, Integer moSubscriptionId)
        throws SQLException, Exception {

    Connection con = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);
    PreparedStatement insertStatement = null;

    try {/*  ww  w.j  av  a 2s.  c o m*/

        if (con == null) {
            throw new Exception("Connection not found");
        }
        con.setAutoCommit(false);

        StringBuilder queryString = new StringBuilder("INSERT INTO ");
        queryString.append(DatabaseTables.MO_USSD_SUBSCRIPTIONS.getTableName());
        queryString.append(" (ussd_request_did, domainurl, operator) ");
        queryString.append("VALUES (?, ?, ?)");

        insertStatement = con.prepareStatement(queryString.toString());

        for (OperatorSubscriptionDTO d : domainsubs) {

            insertStatement.setInt(1, moSubscriptionId);
            insertStatement.setString(2, d.getDomain());
            insertStatement.setString(3, d.getOperator());

            insertStatement.addBatch();
        }

        insertStatement.executeBatch();
        con.commit();

    } catch (SQLException e) {

        log.error("database operation error in operatorSubsEntry : ", e);
        throw e;
    } catch (Exception e) {

        log.error("error in operatorSubsEntry : ", e);
        throw e;
    } finally {

        DbUtils.closeAllConnections(insertStatement, con, null);
    }
}

From source file:edu.ncsa.sstde.indexing.postgis.PostgisIndexer.java

void flushInternal() throws SQLException {
    if (addedStatements == null)
        return;/*from   ww  w  .j  a v a2 s  .  c  o m*/
    try {
        for (Map.Entry<String, List<IndexedStatement>> perTable : addedStatements.entrySet()) {
            PreparedStatement ps = IndexedStatement.addPrepare(getConnection(), perTable.getKey());
            try {
                for (IndexedStatement stat : perTable.getValue())
                    IndexedStatement.addNewBatch(ps, stat);
                ps.executeBatch();
                ps.close();
                ps = null;
            } finally {
                IndexedStatement.closeQuietly(ps);
            }
        }
    } finally {
        addedStatements = null;
    }
}

From source file:com.flexive.ejb.beans.structure.SelectListEngineBean.java

/**
 * Update the positions of all items/*  w  ww  . j  a va 2  s  . c o m*/
 *
 * @param items select list items
 * @param idMap map of ids for new created items
 * @throws FxApplicationException on errors
 */
private void updatePositions(List<FxSelectListItem> items, Map<Long, Long> idMap)
        throws FxApplicationException {
    Connection con = null;
    PreparedStatement ps = null;
    int pos = 0;
    try {
        con = Database.getDbConnection();
        //                                                                    1          2
        ps = con.prepareStatement("UPDATE " + TBL_STRUCT_SELECTLIST_ITEM + " SET POS=? WHERE ID=?");
        for (FxSelectListItem item : items) {
            ps.setInt(1, pos++);
            ps.setLong(2,
                    item.getId() < 0 && idMap != null
                            ? (idMap.containsKey(item.getId()) ? idMap.get(item.getId()) : -1)
                            : item.getId());
            ps.addBatch();
        }
        ps.executeBatch();
    } catch (SQLException e) {
        EJBUtils.rollback(ctx);
        throw new FxCreateException(LOG, e, "ex.db.sqlError", e.getMessage());
    } finally {
        Database.closeObjects(TypeEngineBean.class, con, ps);
    }
}

From source file:com.nabla.wapp.server.auth.UserManager.java

public boolean initializeDatabase(final IRoleListProvider roleListProvider, final String rootPassword)
        throws SQLException {
    Assert.argumentNotNull(roleListProvider);

    final LockTableGuard lock = new LockTableGuard(conn, LOCK_USER_TABLES);
    try {/*from ww w  . j  a v  a 2  s  .  c  o  m*/
        if (!Database.isTableEmpty(conn, IRoleTable.TABLE))
            return true;
        if (log.isDebugEnabled())
            log.debug("initializing role tables");
        final Map<String, String[]> roles = roleListProvider.get();
        Assert.state(!roles.containsKey(IRootUser.NAME));
        final ConnectionTransactionGuard guard = new ConnectionTransactionGuard(conn);
        try {
            final PreparedStatement stmtRole = conn.prepareStatement(
                    "INSERT INTO role (name,uname,privilege,internal) VALUES(?,?,?,?);",
                    Statement.RETURN_GENERATED_KEYS);
            final Map<String, Integer> roleIds = new HashMap<String, Integer>();
            try {
                stmtRole.clearBatch();
                stmtRole.setBoolean(4, true);
                // add privileges and default roles
                for (final Map.Entry<String, String[]> role : roles.entrySet()) {
                    stmtRole.setString(1, role.getKey());
                    stmtRole.setString(2, role.getKey().toUpperCase());
                    stmtRole.setBoolean(3, role.getValue() == null);
                    stmtRole.addBatch();
                }
                if (!Database.isBatchCompleted(stmtRole.executeBatch()))
                    return false;
                final ResultSet rsKey = stmtRole.getGeneratedKeys();
                try {
                    for (final Map.Entry<String, String[]> role : roles.entrySet()) {
                        rsKey.next();
                        roleIds.put(role.getKey(), rsKey.getInt(1));
                    }
                } finally {
                    rsKey.close();
                }
            } finally {
                stmtRole.close();
            }
            final PreparedStatement stmtDefinition = conn
                    .prepareStatement("INSERT INTO role_definition (role_id,child_role_id) VALUES(?,?);");
            try {
                stmtDefinition.clearBatch();
                for (final Map.Entry<String, String[]> role : roles.entrySet()) {
                    final String[] definition = role.getValue();
                    if (definition == null)
                        continue;
                    stmtDefinition.setInt(1, roleIds.get(role.getKey()));
                    for (final String child : definition) {
                        final Integer childId = roleIds.get(child);
                        if (childId == null) {
                            if (log.isErrorEnabled())
                                log.error("child role '" + child + "' not defined!");
                            return false;
                        }
                        stmtDefinition.setInt(2, childId);
                        stmtDefinition.addBatch();
                    }
                }
                if (!Database.isBatchCompleted(stmtDefinition.executeBatch()))
                    return false;
            } finally {
                stmtDefinition.close();
            }
            // add 'root' user
            Database.executeUpdate(conn, "INSERT INTO user (name,uname,active,password) VALUES(?,?,TRUE,?);",
                    IRootUser.NAME, IRootUser.NAME.toUpperCase(),
                    getPasswordEncryptor().encryptPassword(rootPassword));
            return guard.setSuccess(true);
        } finally {
            guard.close();
        }
    } finally {
        lock.close();
    }
}

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

@Override
public void addApplicationMappingsWithApps(int deviceId, int enrolmentId, List<Application> applications,
        int tenantId) throws DeviceManagementDAOException {

    Connection conn;/*from w  ww  .  j a v a2 s . com*/
    PreparedStatement stmt = null;
    ResultSet rs = null;
    ByteArrayOutputStream bao = null;
    ObjectOutputStream oos = null;

    try {
        conn = this.getConnection();
        String sql = "INSERT INTO DM_DEVICE_APPLICATION_MAPPING (DEVICE_ID, ENROLMENT_ID, APPLICATION_ID, "
                + "APP_PROPERTIES, MEMORY_USAGE, IS_ACTIVE, TENANT_ID) VALUES (?, ?, ?, ?, ?, ?, ?)";

        conn.setAutoCommit(false);
        stmt = conn.prepareStatement(sql);

        for (Application application : applications) {
            stmt.setInt(1, deviceId);
            stmt.setInt(2, enrolmentId);
            stmt.setInt(3, application.getId());

            bao = new ByteArrayOutputStream();
            oos = new ObjectOutputStream(bao);
            oos.writeObject(application.getAppProperties());
            stmt.setBytes(4, bao.toByteArray());

            stmt.setInt(5, application.getMemoryUsage());
            stmt.setBoolean(6, application.isActive());

            stmt.setInt(7, tenantId);
            stmt.addBatch();
        }
        stmt.executeBatch();
    } catch (SQLException e) {
        throw new DeviceManagementDAOException("Error occurred while adding device application mappings", e);
    } catch (IOException e) {
        throw new DeviceManagementDAOException("Error occurred while serializing application properties object",
                e);
    } finally {
        if (bao != null) {
            try {
                bao.close();
            } catch (IOException e) {
                log.error("Error occurred while closing ByteArrayOutputStream", e);
            }
        }
        if (oos != null) {
            try {
                oos.close();
            } catch (IOException e) {
                log.error("Error occurred while closing ObjectOutputStream", e);
            }
        }
        DeviceManagementDAOUtil.cleanupResources(stmt, rs);
    }

}

From source file:com.thinkmore.framework.orm.hibernate.SimpleHibernateDao.java

/**
 * ?//from  w ww  . j av  a2  s  .  c  om
 * @param list sql?
 */
public void executeBatchByPrepare(String sql, final List<String> list) {
    Connection conn = null;
    PreparedStatement st = null;
    try {
        conn = SessionFactoryUtils.getDataSource(getSessionFactory()).getConnection();
        conn.setAutoCommit(false); // ??
        st = conn.prepareStatement(sql);
        for (int i = 1, j = list.size(); i < j; i++) {
            Object objs = list.get(i - 1);
            if (objs instanceof List) {
                List<Object> values = (List<Object>) objs;
                for (int h = 1, k = values.size(); h <= k; k++) {
                    Object value = values.get(k - 1);
                    setParameters(st, k, value);
                }
            } else {
                setParameters(st, i, objs);
            }
            st.addBatch(sql);
            if (i % 240 == 0) {//?240?sql???
                st.executeBatch();
                conn.commit();
                st.clearBatch();
            } else if (i % j == 0) {//??
                st.executeBatch();
                conn.commit();
                st.clearBatch();
            }
        }
    } catch (Exception e) {
        try {
            conn.rollback();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        e.printStackTrace();
    } finally {
        closeAll(st, conn);
    }
}

From source file:com.ibm.bluemix.samples.PostgreSQLClient.java

/**
 * Insert text into PostgreSQL/*from  w w  w  .  j av a2 s .com*/
 * 
 * param posts List of Strings of text to insert
 * 
 * @return number of rows affected
 * @throws Exception
 * @throws Exception
 */
public int addPosts(List<String> posts) throws Exception {
    String sql = "INSERT INTO posts (text) VALUES (?)";
    Connection connection = null;
    PreparedStatement statement = null;
    try {
        connection = getConnection();
        connection.setAutoCommit(false);
        statement = connection.prepareStatement(sql);

        for (String s : posts) {
            statement.setString(1, s);
            statement.addBatch();
        }
        int[] rows = statement.executeBatch();
        connection.commit();

        return rows.length;
    } catch (SQLException e) {
        SQLException next = e.getNextException();

        if (next != null) {
            throw next;
        }

        throw e;
    } finally {
        if (statement != null) {
            statement.close();
        }

        if (connection != null) {
            connection.close();
        }
    }
}