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.wso2telco.dep.mediator.dao.SMSMessagingDAO.java

/**
 * Outbound operatorsubs entry.//w  w  w  .  j  a  va  2  s  . c  o m
 *
 * @param domainsubs
 *            the domainsubs
 * @param dnSubscriptionId
 *            the dnSubscriptionId
 * @return true, if successful
 * @throws Exception
 *             the exception
 */
public void outboundOperatorsubsEntry(List<OperatorSubscriptionDTO> domainsubs, Integer dnSubscriptionId)
        throws SQLException, Exception {

    Connection con = null;
    PreparedStatement insertStatement = null;
    PreparedStatement updateStatement = null;

    try {

        con = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);

        if (con == null) {

            throw new Exception("Connection not found");
        }

        /**
         * Set autocommit off to handle the transaction
         */
        con.setAutoCommit(false);

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

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

        for (OperatorSubscriptionDTO d : domainsubs) {

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

            insertStatement.addBatch();
        }

        log.debug("sql query in outboundOperatorsubsEntry : " + insertStatement);

        insertStatement.executeBatch();

        StringBuilder updateQueryString = new StringBuilder("UPDATE ");
        updateQueryString.append(DatabaseTables.OUTBOUND_SUBSCRIPTIONS.getTableName());
        updateQueryString.append(" SET is_active = ?");
        updateQueryString.append(" WHERE dn_subscription_did = ?");

        updateStatement = con.prepareStatement(updateQueryString.toString());

        updateStatement.setInt(1, 1);
        updateStatement.setInt(2, dnSubscriptionId);

        log.debug("sql query in outboundOperatorsubsEntry : " + updateStatement);

        updateStatement.executeUpdate();

        /**
         * commit the transaction if all success
         */
        con.commit();
    } catch (SQLException e) {

        /**
         * rollback if Exception occurs
         */
        con.rollback();

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

        /**
         * rollback if Exception occurs
         */
        con.rollback();

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

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

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

public boolean operatorsubsEntry(List<OperatorSubscriptionDTO> domainsubs, Integer dnSubscriptionId)
        throws SQLException, Exception {

    Connection con = null;//from ww  w .  j a va  2  s  . co m
    PreparedStatement insertStatement = null;
    PreparedStatement updateStatement = null;

    try {

        con = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);

        if (con == null) {

            throw new Exception("Connection not found");
        }

        /**
         * Set autocommit off to handle the transaction
         */
        con.setAutoCommit(false);

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

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

        for (OperatorSubscriptionDTO d : domainsubs) {

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

            insertStatement.addBatch();
        }

        log.debug("sql query in outboundOperatorsubsEntry : " + insertStatement);

        insertStatement.executeBatch();

        StringBuilder updateQueryString = new StringBuilder("UPDATE ");
        updateQueryString.append(DatabaseTables.OUTBOUND_SUBSCRIPTIONS.getTableName());
        updateQueryString.append(" SET is_active = ?");
        updateQueryString.append(" WHERE dn_subscription_did = ?");

        updateStatement = con.prepareStatement(updateQueryString.toString());

        updateStatement.setInt(1, 1);
        updateStatement.setInt(2, dnSubscriptionId);

        log.debug("sql query in outboundOperatorsubsEntry : " + updateStatement);

        updateStatement.executeUpdate();

        /**
         * commit the transaction if all success
         */
        con.commit();
    } catch (SQLException e) {

        /**
         * rollback if Exception occurs
         */
        con.rollback();

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

        /**
         * rollback if Exception occurs
         */
        con.rollback();

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

        DbUtils.closeAllConnections(insertStatement, con, null);
        DbUtils.closeAllConnections(updateStatement, null, null);
    }
    return true;
}

From source file:org.openbel.framework.core.kam.JdbcKAMLoaderImpl.java

/**
 * {@inheritDoc}//  w  ww.j a v  a 2  s  .  c o  m
 */
@Override
public void loadNodes(NamespaceTable nt, ParameterTable pt, TermTable tt, TermParameterMapTable tpmt,
        ProtoNodeTable pnt) throws SQLException {
    List<String> terms = tt.getTermValues();
    Map<Integer, List<Integer>> tpmtidx = tpmt.getTermParameterIndex();

    PreparedStatement knps = getPreparedStatement(KAM_NODE_SQL);

    // load kam nodes
    final List<String> nodes = pnt.getProtoNodes();
    final Map<Integer, Integer> eqn = pnt.getEquivalences();
    final Set<Integer> added = new HashSet<Integer>();
    for (int i = 0, n = nodes.size(); i < n; i++) {
        final Integer eqId = eqn.get(i);

        // continue if we have already seen this equivalent proto node
        if (added.contains(eqId)) {
            continue;
        }

        added.add(eqId);

        final String nl = nodes.get(i);

        Integer knl = valueIndexMap.get(nl);
        if (knl == null) {
            knl = saveObject(1, nl);
            valueIndexMap.put(nl, knl);
        }

        String tf = nl.substring(0, nl.indexOf('('));
        int fv = FunctionEnum.getFunctionEnum(tf).getValue();

        // XXX offset
        knps.setInt(1, eqId + 1);
        knps.setInt(2, fv);
        knps.setInt(3, knl);
        knps.addBatch();
    }

    knps.executeBatch();

    PreparedStatement knpps = getPreparedStatement(KAM_NODE_PARAMETER_SQL);
    PreparedStatement knups = getPreparedStatement(KAM_PARAMETER_UUID_SQL);
    final Map<Integer, Integer> gpi = pt.getGlobalIndex();
    final Map<Integer, SkinnyUUID> guu = pt.getGlobalUUIDs();

    final Set<Integer> seenKamNodes = sizedHashSet(nodes.size());
    final Set<Integer> seenGlobalIds = sizedHashSet(gpi.size());

    // load kam node parameters
    final Map<Integer, Integer> nti = pnt.getNodeTermIndex();
    for (int i = 0, n = nodes.size(); i < n; i++) {
        final Integer ti = nti.get(i);

        // XXX offset
        final Integer eqId = eqn.get(i) + 1;

        // don't add kam node parameters if we have already seen the
        // equivalent node.
        if (seenKamNodes.contains(eqId)) {
            continue;
        }

        List<Integer> gtpl = tpmtidx.get(ti);
        if (hasItems(gtpl)) {
            for (int j = 0; j < gtpl.size(); j++) {
                // get parameter index, retrieve global parameter, and set
                Integer parameterIndex = gtpl.get(j);

                final Integer gid = gpi.get(parameterIndex);

                // XXX offset
                knpps.setInt(1, gid + 1);
                knpps.setInt(2, eqId);
                knpps.setInt(3, j);
                knpps.addBatch();

                if (seenGlobalIds.contains(gid)) {
                    continue;
                }
                SkinnyUUID uuid = guu.get(gid);
                if (uuid != null) {
                    // XXX offset
                    knups.setInt(1, gid + 1);
                    knups.setLong(2, uuid.getMostSignificantBits());
                    knups.setLong(3, uuid.getLeastSignificantBits());
                    knups.addBatch();
                }
                seenGlobalIds.add(gid);
            }
        }

        // track equivalent kam node
        seenKamNodes.add(eqId);
    }

    knpps.executeBatch();

    PreparedStatement pps = getPreparedStatement(TERM_PARAMETER_SQL);
    PreparedStatement tps = getPreparedStatement(TERM_SQL);
    final Map<Integer, Integer> termNodes = pnt.getTermNodeIndex();
    // load term parameters and terms
    int tpindex = 0;
    for (int ti = 0; ti < terms.size(); ti++) {
        String t = terms.get(ti);

        // find node equivalence
        Integer nodeId = termNodes.get(ti);
        // XXX offset
        final Integer eqId = eqn.get(nodeId) + 1;

        Integer ctl = valueIndexMap.get(t);
        if (ctl == null) {
            ctl = saveObject(1, t);
            valueIndexMap.put(t, ctl);
        }

        // XXX offset
        tps.setInt(1, ti + 1);
        // XXX offset
        tps.setInt(2, eqId);
        tps.setInt(3, ctl);
        tps.addBatch();

        int ord = 0;
        List<Integer> pl = tpmtidx.get(ti);
        if (hasItems(pl)) {
            for (Integer pi : pl) {
                TableParameter p = pt.getIndexTableParameter().get(pi);
                Integer cpv = valueIndexMap.get(p.getValue());
                if (cpv == null) {
                    cpv = saveObject(1, p.getValue());
                    valueIndexMap.put(p.getValue(), cpv);
                }

                final Integer gid = gpi.get(pi);

                // XXX offset
                pps.setInt(1, tpindex + 1);
                // XXX offset
                pps.setInt(2, gid + 1);
                // XXX offset
                pps.setInt(3, ti + 1);

                // find index for the parameter's namespace
                TableNamespace tn = p.getNamespace();
                Integer ni = null;
                if (tn != null) {
                    ni = nt.getNamespaceIndex().get(tn);
                }

                if (ni == null) {
                    pps.setNull(4, Types.INTEGER);
                } else {
                    // XXX offset
                    pps.setInt(4, ni + 1);
                }

                pps.setInt(5, cpv);
                pps.setInt(6, ord);
                pps.addBatch();

                ord++;
                tpindex++;

                if (seenGlobalIds.contains(gid)) {
                    continue;
                }
                SkinnyUUID uuid = guu.get(gid);
                if (uuid != null) {
                    // XXX offset
                    knups.setInt(1, gid + 1);
                    knups.setLong(2, uuid.getMostSignificantBits());
                    knups.setLong(3, uuid.getLeastSignificantBits());
                    knups.addBatch();
                }
                seenGlobalIds.add(gid);
            }
        }
    }

    tps.executeBatch();
    pps.executeBatch();
    knups.executeBatch();
}

From source file:hoot.services.db.DbUtils.java

public static void batchRecordsDirectWays(final long mapId, final List<?> records,
        final RecordBatchType recordBatchType, Connection conn, int maxRecordBatchSize) throws Exception {
    PreparedStatement ps = null;
    try {//from   w  w w .j a  v a  2s.c  om
        String sql = null;
        long execResult = -1;
        //conn.setAutoCommit(false);
        int count = 0;

        switch (recordBatchType) {
        case INSERT:

            sql = "insert into current_ways_" + mapId
                    + " (id, changeset_id, \"timestamp\", visible, version, tags) "
                    + "values (?, ?, ?, ?, ?, ?)";

            ps = conn.prepareStatement(sql);
            for (Object o : records) {
                CurrentWays way = (CurrentWays) o;

                ps.setLong(1, way.getId());
                ps.setLong(2, way.getChangesetId());
                ps.setTimestamp(3, way.getTimestamp());
                ps.setBoolean(4, way.getVisible());
                ps.setLong(5, way.getVersion());

                Map<String, String> tags = (Map<String, String>) way.getTags();

                String hstoreStr = "";
                Iterator it = tags.entrySet().iterator();
                while (it.hasNext()) {
                    Map.Entry pairs = (Map.Entry) it.next();
                    if (hstoreStr.length() > 0) {
                        hstoreStr += ",";
                    }
                    hstoreStr += "\"" + pairs.getKey() + "\"=>\"" + pairs.getValue() + "\"";
                }
                ps.setObject(6, hstoreStr, Types.OTHER);
                ps.addBatch();

                if (maxRecordBatchSize > -1) {
                    if (++count % maxRecordBatchSize == 0) {
                        ps.executeBatch();
                        //conn.commit();
                    }
                }

            }

            break;

        case UPDATE:

            sql = "update current_ways_" + mapId
                    + " set changeset_id=?, visible=?, \"timestamp\"=?, version=?, tags=? " + "where id=?";
            ps = conn.prepareStatement(sql);
            for (Object o : records) {
                CurrentWays way = (CurrentWays) o;

                ps.setLong(1, way.getChangesetId());
                ps.setBoolean(2, way.getVisible());
                ps.setTimestamp(3, way.getTimestamp());
                ps.setLong(4, way.getVersion());

                Map<String, String> tags = (Map<String, String>) way.getTags();

                String hstoreStr = "";
                Iterator it = tags.entrySet().iterator();
                while (it.hasNext()) {
                    Map.Entry pairs = (Map.Entry) it.next();
                    if (hstoreStr.length() > 0) {
                        hstoreStr += ",";
                    }
                    hstoreStr += "\"" + pairs.getKey() + "\"=>\"" + pairs.getValue() + "\"";
                }
                ps.setObject(5, hstoreStr, Types.OTHER);

                ps.setLong(6, way.getId());

                ps.addBatch();

                if (maxRecordBatchSize > -1) {
                    if (++count % maxRecordBatchSize == 0) {
                        ps.executeBatch();
                        //conn.commit();
                    }
                }
            }

            break;

        case DELETE:

            sql = "delete from current_ways_" + mapId + " where id=?";
            ps = conn.prepareStatement(sql);
            for (Object o : records) {
                CurrentWays way = (CurrentWays) o;

                ps.setLong(1, way.getId());

                ps.addBatch();

                if (maxRecordBatchSize > -1) {
                    if (++count % maxRecordBatchSize == 0) {
                        ps.executeBatch();
                        //conn.commit();
                    }
                }

            }

            break;

        default:
            throw new Exception("");
        }

        ps.executeBatch();
        //conn.commit();
    } catch (Exception e) {
        conn.rollback();
        String msg = "Error executing batch query.";
        msg += "  " + e.getMessage();
        msg += " Cause:" + e.getCause().toString();
        throw new Exception(msg);
    } finally {
        if (ps != null) {
            ps.close();
        }
        //conn.setAutoCommit(true);
    }
}

From source file:hoot.services.db.DbUtils.java

public static void batchRecordsDirectRelations(final long mapId, final List<?> records,
        final RecordBatchType recordBatchType, Connection conn, int maxRecordBatchSize) throws Exception {
    PreparedStatement ps = null;
    try {/*  w w  w  .j  av  a2 s  . c  o  m*/
        String sql = null;
        long execResult = -1;
        //conn.setAutoCommit(false);
        int count = 0;

        switch (recordBatchType) {
        case INSERT:

            sql = "insert into current_relations_" + mapId
                    + " (id, changeset_id, \"timestamp\", visible, version, tags) "
                    + "values (?, ?, ?, ?, ?, ?)";

            ps = conn.prepareStatement(sql);
            for (Object o : records) {
                CurrentRelations rel = (CurrentRelations) o;

                ps.setLong(1, rel.getId());
                ps.setLong(2, rel.getChangesetId());
                ps.setTimestamp(3, rel.getTimestamp());
                ps.setBoolean(4, rel.getVisible());
                ps.setLong(5, rel.getVersion());

                Map<String, String> tags = (Map<String, String>) rel.getTags();

                String hstoreStr = "";
                Iterator it = tags.entrySet().iterator();
                while (it.hasNext()) {
                    Map.Entry pairs = (Map.Entry) it.next();
                    if (hstoreStr.length() > 0) {
                        hstoreStr += ",";
                    }
                    hstoreStr += "\"" + pairs.getKey() + "\"=>\"" + pairs.getValue() + "\"";
                }
                ps.setObject(6, hstoreStr, Types.OTHER);
                ps.addBatch();

                if (maxRecordBatchSize > -1) {
                    if (++count % maxRecordBatchSize == 0) {
                        ps.executeBatch();
                        //conn.commit();
                    }
                }

            }

            break;

        case UPDATE:

            sql = "update current_relations_" + mapId
                    + " set changeset_id=?, visible=?, \"timestamp\"=?, version=?, tags=? " + "where id=?";
            ps = conn.prepareStatement(sql);
            for (Object o : records) {
                CurrentRelations rel = (CurrentRelations) o;

                ps.setLong(1, rel.getChangesetId());
                ps.setBoolean(2, rel.getVisible());
                ps.setTimestamp(3, rel.getTimestamp());
                ps.setLong(4, rel.getVersion());

                Map<String, String> tags = (Map<String, String>) rel.getTags();

                String hstoreStr = "";
                Iterator it = tags.entrySet().iterator();
                while (it.hasNext()) {
                    Map.Entry pairs = (Map.Entry) it.next();
                    if (hstoreStr.length() > 0) {
                        hstoreStr += ",";
                    }
                    hstoreStr += "\"" + pairs.getKey() + "\"=>\"" + pairs.getValue() + "\"";
                }
                ps.setObject(5, hstoreStr, Types.OTHER);

                ps.setLong(6, rel.getId());

                ps.addBatch();

                if (maxRecordBatchSize > -1) {
                    if (++count % maxRecordBatchSize == 0) {
                        ps.executeBatch();
                        //conn.commit();
                    }
                }
            }

            break;

        case DELETE:

            sql = "delete from current_relations_" + mapId + " where id=?";
            ps = conn.prepareStatement(sql);
            for (Object o : records) {
                CurrentRelations rel = (CurrentRelations) o;

                ps.setLong(1, rel.getId());

                ps.addBatch();

                if (maxRecordBatchSize > -1) {
                    if (++count % maxRecordBatchSize == 0) {
                        ps.executeBatch();
                        //conn.commit();
                    }
                }

            }

            break;

        default:
            throw new Exception("");
        }

        ps.executeBatch();
        //conn.commit();
    } catch (Exception e) {
        conn.rollback();
        String msg = "Error executing batch query.";
        msg += "  " + e.getMessage();
        msg += " Cause:" + e.getCause().toString();
        throw new Exception(msg);
    } finally {
        if (ps != null) {
            ps.close();
        }
        //conn.setAutoCommit(true);
    }
}

From source file:org.openbel.framework.core.kam.JdbcKAMLoaderImpl.java

/**
 * {@inheritDoc}/*from ww  w  . java 2 s. c o m*/
 */
@Override
public void loadAnnotationDefinitions(AnnotationDefinitionTable adt) throws SQLException {
    PreparedStatement adps = getPreparedStatement(ANNOTATION_DEFINITION_SQL);

    for (Map.Entry<Integer, TableAnnotationDefinition> ade : adt.getIndexDefinition().entrySet()) {
        TableAnnotationDefinition ad = ade.getValue();
        adps.setInt(1, (ade.getKey() + 1));
        adps.setString(2, ad.getName());

        if (AnnotationDefinitionTable.URL_ANNOTATION_TYPE_ID == ad.getAnnotationType()) {
            adps.setNull(3, Types.VARCHAR);
            adps.setNull(4, Types.VARCHAR);
        } else {
            adps.setString(3, StringUtils.abbreviate(ad.getDescription(), MAX_MEDIUM_VARCHAR_LENGTH));
            adps.setString(4, StringUtils.abbreviate(ad.getUsage(), MAX_MEDIUM_VARCHAR_LENGTH));
        }

        final int oid;
        final String domain = ad.getAnnotationDomain();
        final Integer objectId = valueIndexMap.get(domain);
        if (objectId != null) {
            oid = objectId;
        } else {
            oid = saveObject(1, domain);
            valueIndexMap.put(domain, oid);
        }

        adps.setInt(5, oid);
        adps.setInt(6, ad.getAnnotationType());
        adps.addBatch();
    }

    adps.executeBatch();

    // associate annotate definitions to documents
    PreparedStatement dadmps = getPreparedStatement(DOCUMENT_ANNOTATION_DEFINITION_MAP_SQL);
    Map<Integer, Set<Integer>> dadm = adt.getDocumentAnnotationDefinitions();

    Set<Entry<Integer, Set<Integer>>> entries = dadm.entrySet();
    for (final Entry<Integer, Set<Integer>> entry : entries) {
        final Integer key = entry.getKey();
        for (final Integer adid : entry.getValue()) {
            dadmps.setInt(1, (key + 1));
            dadmps.setInt(2, (adid + 1));
            dadmps.addBatch();
        }
        dadmps.executeBatch();
    }
}

From source file:gemlite.core.internal.db.DBSynchronizer.java

/**
 * Get or create a {@link PreparedStatement} for a primary key based delete
 * operation./* www .  j av  a 2  s . c o  m*/
 */

protected PreparedStatement getExecutableDeletePrepStmntPKBased(AsyncEvent pkEvent, PreparedStatement prevPS)
        throws SQLException {

    final String regionName = pkEvent.getRegion().getName();
    PreparedStatement ps = this.deleteStmntMap.get(regionName);
    IMapperTool tool = DomainRegistry.getMapperTool(regionName);
    String tableName = DomainRegistry.regionToTable(regionName);
    List<String> keyFields = tool.getKeyFieldNames();
    if (ps == null) {
        final String dmlString = AsyncEventHelper.getDeleteString(tableName, keyFields);
        if (logger.isDebugEnabled()) {
            logger.info("DBSynchronizer::getExecutableInsertPrepStmntPKBased: preparing '" + dmlString
                    + "' for event: " + pkEvent);
        }
        ps = conn.prepareStatement(dmlString);
        this.deleteStmntMap.put(regionName, ps);
    } else if (prevPS == ps) {
        // add a new batch of values
        ps.addBatch();
    }

    //key?
    if (pkEvent.getKey() instanceof DataSerializable)
        setKeysInPrepStatement(pkEvent.getKey(), keyFields, tool.getValueClass(), ps, 1);
    else
        setSingleKeysInPrepStatement(pkEvent.getKey(), tool.getKeyClass(), ps, 1);
    return ps;
}

From source file:org.cerberus.crud.dao.impl.TestCaseCountryPropertiesDAO.java

@Override
public Answer createTestCaseCountryPropertiesBatch(List<TestCaseCountryProperties> listOfPropertiesToInsert) {
    Answer answer = new Answer();
    MessageEvent rs = null;/* w w w  .j  av  a 2 s  .co  m*/
    StringBuilder query = new StringBuilder();
    query.append(
            "INSERT INTO testcasecountryproperties (`Test`,`TestCase`,`Country`,`Property` , `Description`, `Type`");
    query.append(",`Database`,`Value1`,`Value2`,`Length`,`RowLimit`,`Nature`,`RetryNb`,`RetryPeriod`) ");
    query.append("VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

    Connection connection = this.databaseSpring.connect();
    try {
        PreparedStatement preStat = connection.prepareStatement(query.toString());
        try {
            for (TestCaseCountryProperties prop : listOfPropertiesToInsert) {
                preStat.setString(1, prop.getTest());
                preStat.setString(2, prop.getTestCase());
                preStat.setString(3, prop.getCountry());
                preStat.setString(4, prop.getProperty());
                preStat.setString(5, prop.getDescription());
                preStat.setString(6, prop.getType());
                preStat.setString(7, prop.getDatabase());
                preStat.setString(8, prop.getValue1());
                preStat.setString(9, prop.getValue2());
                preStat.setInt(10, prop.getLength());
                preStat.setInt(11, prop.getRowLimit());
                preStat.setString(12, prop.getNature());
                preStat.setInt(13, prop.getRetryNb());
                preStat.setInt(14, prop.getRetryPeriod());

                preStat.addBatch();
            }

            //executes the batch         
            preStat.executeBatch();

            int affectedRows[] = preStat.executeBatch();

            //verify if some of the statements failed
            boolean someFailed = ArrayUtils.contains(affectedRows, 0)
                    || ArrayUtils.contains(affectedRows, Statement.EXECUTE_FAILED);

            if (someFailed == false) {
                rs = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
                rs.setDescription(
                        rs.getDescription().replace("%ITEM%", "Property").replace("%OPERATION%", "CREATE"));
            } else {
                rs = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_EXPECTED);
                rs.setDescription(rs.getDescription().replace("%ITEM%", "Property")
                        .replace("%OPERATION%", "CREATE")
                        .replace("%REASON%", "Some problem occurred while creating the new property! "));
            }

        } catch (SQLException exception) {
            rs = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
            rs.setDescription(
                    rs.getDescription().replace("%DESCRIPTION%", "It was not possible to update table."));
            LOG.error("Unable to execute query : " + exception.toString());
        } finally {
            if (preStat != null) {
                preStat.close();
            }
        }
    } catch (SQLException exception) {
        rs = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
        rs.setDescription(rs.getDescription().replace("%DESCRIPTION%", "It was not possible to update table."));
        LOG.error("Unable to execute query : " + exception.toString());
    } finally {
        try {
            if (!this.databaseSpring.isOnTransaction()) {
                if (connection != null) {
                    connection.close();
                }
            }
        } catch (SQLException e) {
            LOG.warn(e.toString());
        }
    }

    answer.setResultMessage(rs);
    return answer;
}

From source file:gemlite.core.internal.db.DBSynchronizer.java

/**
 * Get or create a {@link PreparedStatement} for an insert operation.
 *//*  w  w  w .  j a  v a 2  s .c om*/
protected PreparedStatement getExecutableInsertPrepStmntPKBased(AsyncEvent pkEvent, PreparedStatement prevPS)
        throws SQLException {
    final String regionName = pkEvent.getRegion().getName();
    PreparedStatement ps = this.insertStmntMap.get(regionName);

    IMapperTool tool = DomainRegistry.getMapperTool(regionName);
    String tableName = DomainRegistry.regionToTable(regionName);
    List<String> valueFields = tool.getValueFieldNames();

    if (ps == null) {
        final String dmlString = AsyncEventHelper.getInsertString(tableName, valueFields);
        if (logger.isDebugEnabled()) {
            logger.info("DBSynchronizer::getExecutableInsertPrepStmntPKBased: " + "preparing '" + dmlString
                    + "' for event: " + pkEvent);
        }
        ps = conn.prepareStatement(dmlString);
        this.insertStmntMap.put(tableName, ps);
    } else if (prevPS == ps) {
        // add a new batch of values
        ps.addBatch();
    }
    int paramIndex = 1;
    Class valueClass = tool.getValueClass();
    for (int colIdx = 0; colIdx < valueFields.size(); colIdx++) {
        String field = valueFields.get(colIdx);
        try {
            Map map = PropertyUtils.describe(pkEvent.getDeserializedValue());
            Object val = map.get(field);
            String type = valueClass.getDeclaredField(field).getType().getName();
            helper.setColumnInPrepStatement(type, val, ps, this, paramIndex);
        } catch (Exception e) {
            throw new SQLException(e);
        }
        paramIndex++;
    }
    return ps;
}

From source file:com.octo.captcha.engine.bufferedengine.buffer.DatabaseCaptchaBuffer.java

/**
 * Put a collection of captchas with his locale
 *
 * @param captchas The captchas to add/*from  w w  w  .  j  a  v a2  s .  c  o  m*/
 * @param locale   The locale of the captchas
 */
public void putAllCaptcha(Collection captchas, Locale locale) {
    Connection con = null;
    PreparedStatement ps = null;

    if (captchas != null && captchas.size() > 0) {
        Iterator captIt = captchas.iterator();
        if (log.isDebugEnabled()) {
            log.debug("try to insert " + captchas.size() + " captchas");
        }

        try {
            con = datasource.getConnection();
            con.setAutoCommit(false);
            ps = con.prepareStatement("insert into " + table + "(" + timeMillisColumn + "," + hashCodeColumn
                    + "," + localeColumn + "," + captchaColumn + ") values (?,?,?,?)");

            while (captIt.hasNext()) {

                Captcha captcha = (Captcha) captIt.next();
                try {
                    long currenttime = System.currentTimeMillis();
                    long hash = captcha.hashCode();

                    ps.setLong(1, currenttime);
                    ps.setLong(2, hash);
                    ps.setString(3, locale.toString());
                    // Serialise the entry
                    final ByteArrayOutputStream outstr = new ByteArrayOutputStream();
                    final ObjectOutputStream objstr = new ObjectOutputStream(outstr);
                    objstr.writeObject(captcha);
                    objstr.close();
                    final ByteArrayInputStream inpstream = new ByteArrayInputStream(outstr.toByteArray());

                    ps.setBinaryStream(4, inpstream, outstr.size());

                    ps.addBatch();

                    if (log.isDebugEnabled()) {
                        log.debug("insert captcha added to batch : " + currenttime + ";" + hash);
                    }

                } catch (IOException e) {
                    log.warn("error during captcha serialization, "
                            + "check your class versions. removing row from database", e);
                }
            }
            //exexute batch and commit()

            ps.executeBatch();
            log.debug("batch executed");

            con.commit();
            log.debug("batch commited");

        } catch (SQLException e) {
            log.error(DB_ERROR, e);

        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                }
            }
        }
    }

}