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.appmgt.impl.dao.AppMDAO.java

/**
 * save java policy and application mapping
 *
 * @param connection    : SQL Connection
 * @param applicationId : Application Id
 * @param javaPolicyIds : selected Java Policy
 * @throws AppManagementException//from  w  w w  .j  a  v a 2  s . c om
 */
public void saveJavaPolicyMappings(Connection connection, int applicationId, Object[] javaPolicyIds)
        throws SQLException {

    PreparedStatement preparedStatement = null;
    String query = " INSERT INTO APM_APP_JAVA_POLICY_MAPPING(APP_ID, JAVA_POLICY_ID) VALUES(?,?) ";

    try {
        preparedStatement = connection.prepareStatement(query);

        for (Object policyId : javaPolicyIds) {
            preparedStatement.setInt(1, applicationId);
            preparedStatement.setInt(2, Integer.parseInt(policyId.toString()));
            preparedStatement.addBatch();
        }
        preparedStatement.executeBatch();

    } catch (SQLException e) {
        StringBuilder builder = new StringBuilder(); //build log description String
        builder.append("SQL Error while executing the query to save Java Policy mappings : ").append(query)
                .append(" : (applicationId:").append(applicationId).append(", Java Policy Ids:")
                .append(javaPolicyIds).append(") : ").append(e.getMessage());
        log.error(builder.toString(), e);
        /*
        In the code im using a single SQL connection passed from the parent function so I'm logging the error here
        and throwing the SQLException so the connection will be disposed by the parent function.
        */
        throw e;
    } finally {
        APIMgtDBUtil.closeAllConnections(preparedStatement, null, null);
    }
}

From source file:org.wso2.carbon.appmgt.impl.dao.AppMDAO.java

/**
 * Persists the application's entitlement policy partials in database
 *
 * @param applicationId application id/*w  w  w .j av  a 2 s.  c  o m*/
 * @param partialIds    policy partial ids
 * @throws org.wso2.carbon.appmgt.api.AppManagementException
 */
public void saveApplicationPolicyPartialsMappings(Connection connection, int applicationId, Object[] partialIds)
        throws AppManagementException {

    PreparedStatement preparedStatement = null;
    String queryToInsertRecord = "INSERT INTO " + "APM_APP_XACML_PARTIAL_MAPPINGS(APP_ID,PARTIAL_ID)"
            + " VALUES (?,?)";

    try {
        preparedStatement = connection.prepareStatement(queryToInsertRecord);

        for (Object partialId : partialIds) {
            preparedStatement.setInt(1, applicationId);
            preparedStatement.setInt(2, Integer.parseInt(partialId.toString()));
            preparedStatement.addBatch();
        }
        preparedStatement.executeBatch();

    } catch (SQLException e) {
        handleException("Error while persisting application-policy partial mappings of webapp with id :  "
                + applicationId, e);
    } finally {
        APIMgtDBUtil.closeAllConnections(preparedStatement, null, null);
    }
}

From source file:org.wso2.carbon.appmgt.impl.dao.AppMDAO.java

/**
 * Update URLMapping - Entittlement policy patial mappings
 *
 * @param xacmlPolicyTemplateContexts xacml poilicy partial template contexts
 * @throws org.wso2.carbon.appmgt.api.AppManagementException
 */// ww w. ja v  a  2  s.  c  om
public void updateURLEntitlementPolicyPartialMappings(
        List<XACMLPolicyTemplateContext> xacmlPolicyTemplateContexts) throws AppManagementException {

    String query = "UPDATE APM_POLICY_GRP_PARTIAL_MAPPING SET POLICY_ID = ? " + "WHERE POLICY_GRP_ID = ? "
            + "AND POLICY_PARTIAL_ID = ?";

    Connection connection = null;
    PreparedStatement preparedStatement = null;

    try {
        connection = APIMgtDBUtil.getConnection();
        preparedStatement = connection.prepareStatement(query);

        for (XACMLPolicyTemplateContext context : xacmlPolicyTemplateContexts) {
            preparedStatement.setString(1, context.getPolicyId());
            preparedStatement.setInt(2, context.getPolicyGroupId());
            preparedStatement.setInt(3, context.getRuleId());
            preparedStatement.addBatch();
        }

        preparedStatement.executeBatch();

        // Finally commit transaction.
        connection.commit();

    } catch (SQLException e) {
        handleException("Failed to update URL - Entitlement Policy Partial mappings", e);
    } finally {
        APIMgtDBUtil.closeAllConnections(preparedStatement, connection, null);
    }
}

From source file:com.pari.nm.utils.db.InventoryDBHelper.java

public static void insertSnmpColsInBatch(int deviceId, Map<String, ISnmpColumn> snmpCols, String type) {
    String insQuery = DBHelperConstants.INSERT_SNMP_MIB_QUERY;
    Connection con = null;/*from w w  w . java2s. c  o m*/
    PreparedStatement ps = null;
    try {
        con = DBHelper.getConnection();
        ps = con.prepareStatement(insQuery);
        con.setAutoCommit(false);
        for (String colOid : snmpCols.keySet()) {
            // e.g. if colOid is .1.3.6.1.2.1.47.1.1.1.1.5.2, then entryOid
            // is .1.3.6.1.2.1.47.1.1.1.1.5
            String entryOid = colOid.substring(0, colOid.lastIndexOf("."));
            // and tableOid is .1.3.6.1.2.1.47.1.1.1.1
            String tableOid = entryOid.substring(0, entryOid.lastIndexOf("."));
            ISnmpColumn snmpCol = snmpCols.get(colOid);
            String colXml = snmpCol.toXml();
            ps.setInt(1, deviceId);
            ps.setString(2, colOid);
            ps.setString(3, tableOid);
            ps.setString(6, snmpCol.getTitle());
            // TODO: Do we need to compress data before storing?
            if (ps instanceof OraclePreparedStatement) {
                ((OraclePreparedStatement) ps).setStringForClob(4, colXml);
            } else {
                logger.debug("PS is not OraclePreparedStatement, inserting as regular string");
                ps.setString(4, colXml);
            }
            ps.setString(5, type);
            ps.addBatch();
        }
        ps.executeBatch();
        con.commit();
    } catch (SQLException sqlex) {
        logger.error("Error while inserting rows to database", sqlex);
        try {
            if (con != null) {
                con.rollback();
            }
        } catch (SQLException ex) {
            logger.error("Error while calling rollback on db conn", ex);
        }
    } catch (Exception ex) {
        logger.error("Error while inserting snmp data in batch", ex);
    } finally {
        try {
            if (con != null) {
                con.setAutoCommit(true);
            }
        } catch (SQLException sqlex) {
            logger.error("Error while calling setAutoCommit", sqlex);
        }
        try {
            ps.close();
        } catch (SQLException sqlex) {
            logger.error("Error while closing ps", sqlex);
        }
        DBHelper.releaseConnection(con);
    }
}

From source file:com.oltpbenchmark.benchmarks.tpcc.TPCCLoader.java

protected int loadOrder(int whseKount, int distWhseKount, int custDistKount) {

    int k = 0;/*from  w  w w  . j a  v  a  2  s . c o  m*/
    int t = 0;
    PrintWriter outLine = null;
    PrintWriter outNewOrder = null;

    try {
        PreparedStatement ordrPrepStmt = getInsertStatement(TPCCConstants.TABLENAME_OPENORDER);
        PreparedStatement nworPrepStmt = getInsertStatement(TPCCConstants.TABLENAME_NEWORDER);
        PreparedStatement orlnPrepStmt = getInsertStatement(TPCCConstants.TABLENAME_ORDERLINE);

        if (outputFiles == true) {
            out = new PrintWriter(new FileOutputStream(fileLocation + "order.csv"));
            LOG.debug("\nWriting Order file to: " + fileLocation + "order.csv");
            outLine = new PrintWriter(new FileOutputStream(fileLocation + "order-line.csv"));
            LOG.debug("\nWriting Order Line file to: " + fileLocation + "order-line.csv");
            outNewOrder = new PrintWriter(new FileOutputStream(fileLocation + "new-order.csv"));
            LOG.debug("\nWriting New Order file to: " + fileLocation + "new-order.csv");
        }

        now = new java.util.Date();
        Oorder oorder = new Oorder();
        NewOrder new_order = new NewOrder();
        OrderLine order_line = new OrderLine();
        jdbcIO myJdbcIO = new jdbcIO();

        t = (whseKount * distWhseKount * custDistKount);
        t = (t * 11) + (t / 3);
        LOG.debug("whse=" + whseKount + ", dist=" + distWhseKount + ", cust=" + custDistKount);
        LOG.debug("\nStart Order-Line-New Load for approx " + t + " rows @ " + now + " ...");

        for (int w = 1; w <= whseKount; w++) {

            for (int d = 1; d <= distWhseKount; d++) {
                // TPC-C 4.3.3.1: o_c_id must be a permutation of [1, 3000]
                int[] c_ids = new int[custDistKount];
                for (int i = 0; i < custDistKount; ++i) {
                    c_ids[i] = i + 1;
                }
                // Collections.shuffle exists, but there is no
                // Arrays.shuffle
                for (int i = 0; i < c_ids.length - 1; ++i) {
                    int remaining = c_ids.length - i - 1;
                    int swapIndex = gen.nextInt(remaining) + i + 1;
                    assert i < swapIndex;
                    int temp = c_ids[swapIndex];
                    c_ids[swapIndex] = c_ids[i];
                    c_ids[i] = temp;
                }

                for (int c = 1; c <= custDistKount; c++) {

                    oorder.o_id = c;
                    oorder.o_w_id = w;
                    oorder.o_d_id = d;
                    oorder.o_c_id = c_ids[c - 1];
                    // o_carrier_id is set *only* for orders with ids < 2101
                    // [4.3.3.1]
                    if (oorder.o_id < FIRST_UNPROCESSED_O_ID) {
                        oorder.o_carrier_id = TPCCUtil.randomNumber(1, 10, gen);
                    } else {
                        oorder.o_carrier_id = null;
                    }
                    oorder.o_ol_cnt = TPCCUtil.randomNumber(5, 15, gen);
                    oorder.o_all_local = 1;
                    oorder.o_entry_d = System.currentTimeMillis();

                    k++;
                    if (outputFiles == false) {
                        myJdbcIO.insertOrder(ordrPrepStmt, oorder);
                    } else {
                        String str = "";
                        str = str + oorder.o_id + ",";
                        str = str + oorder.o_w_id + ",";
                        str = str + oorder.o_d_id + ",";
                        str = str + oorder.o_c_id + ",";
                        str = str + oorder.o_carrier_id + ",";
                        str = str + oorder.o_ol_cnt + ",";
                        str = str + oorder.o_all_local + ",";
                        Timestamp entry_d = new java.sql.Timestamp(oorder.o_entry_d);
                        str = str + entry_d;
                        out.println(str);
                    }

                    // 900 rows in the NEW-ORDER table corresponding to the
                    // last
                    // 900 rows in the ORDER table for that district (i.e.,
                    // with
                    // NO_O_ID between 2,101 and 3,000)

                    if (c >= FIRST_UNPROCESSED_O_ID) {

                        new_order.no_w_id = w;
                        new_order.no_d_id = d;
                        new_order.no_o_id = c;

                        k++;
                        if (outputFiles == false) {
                            myJdbcIO.insertNewOrder(nworPrepStmt, new_order);
                        } else {
                            String str = "";
                            str = str + new_order.no_w_id + ",";
                            str = str + new_order.no_d_id + ",";
                            str = str + new_order.no_o_id;
                            outNewOrder.println(str);
                        }

                    } // end new order

                    for (int l = 1; l <= oorder.o_ol_cnt; l++) {
                        order_line.ol_w_id = w;
                        order_line.ol_d_id = d;
                        order_line.ol_o_id = c;
                        order_line.ol_number = l; // ol_number
                        order_line.ol_i_id = TPCCUtil.randomNumber(1, 100000, gen);
                        if (order_line.ol_o_id < FIRST_UNPROCESSED_O_ID) {
                            order_line.ol_delivery_d = oorder.o_entry_d;
                            order_line.ol_amount = 0;
                        } else {
                            order_line.ol_delivery_d = null;
                            // random within [0.01 .. 9,999.99]
                            order_line.ol_amount = (float) (TPCCUtil.randomNumber(1, 999999, gen) / 100.0);
                        }

                        order_line.ol_supply_w_id = order_line.ol_w_id;
                        order_line.ol_quantity = 5;
                        order_line.ol_dist_info = TPCCUtil.randomStr(24);

                        k++;
                        if (outputFiles == false) {

                            myJdbcIO.insertOrderLine(orlnPrepStmt, order_line);
                        } else {
                            String str = "";
                            str = str + order_line.ol_w_id + ",";
                            str = str + order_line.ol_d_id + ",";
                            str = str + order_line.ol_o_id + ",";
                            str = str + order_line.ol_number + ",";
                            str = str + order_line.ol_i_id + ",";
                            Timestamp delivery_d = new Timestamp(order_line.ol_delivery_d);
                            str = str + delivery_d + ",";
                            str = str + order_line.ol_amount + ",";
                            str = str + order_line.ol_supply_w_id + ",";
                            str = str + order_line.ol_quantity + ",";
                            str = str + order_line.ol_dist_info;
                            outLine.println(str);
                        }

                        if ((k % configCommitCount) == 0) {
                            long tmpTime = new java.util.Date().getTime();
                            String etStr = "  Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000)
                                    + "                    ";
                            LOG.debug(etStr.substring(0, 30) + "  Writing record " + k + " of " + t);
                            lastTimeMS = tmpTime;
                            if (outputFiles == false) {

                                ordrPrepStmt.executeBatch();
                                nworPrepStmt.executeBatch();
                                orlnPrepStmt.executeBatch();
                                ordrPrepStmt.clearBatch();
                                nworPrepStmt.clearBatch();
                                orlnPrepStmt.clearBatch();
                                transCommit();
                            }
                        }

                    } // end for [l]

                } // end for [c]

            } // end for [d]

        } // end for [w]

        LOG.debug("  Writing final records " + k + " of " + t);
        if (outputFiles == false) {
            ordrPrepStmt.executeBatch();
            nworPrepStmt.executeBatch();
            orlnPrepStmt.executeBatch();
        } else {
            outLine.close();
            outNewOrder.close();
        }
        transCommit();
        now = new java.util.Date();
        LOG.debug("End Orders Load @  " + now);

    } catch (Exception e) {
        e.printStackTrace();
        transRollback();
        if (outputFiles == true) {
            outLine.close();
            outNewOrder.close();
        }
    }

    return (k);

}

From source file:com.flexive.core.storage.genericSQL.GenericTreeStorageSpreaded.java

protected long _reorganizeSpace(Connection con, SequencerEngine seq, FxTreeMode sourceMode, FxTreeMode destMode,
        long nodeId, boolean includeNodeId, BigInteger overrideSpacing, BigInteger overrideLeft,
        FxTreeNodeInfo insertParent, int insertPosition, BigInteger insertSpace, BigInteger insertBoundaries[],
        int depthDelta, Long destinationNode, boolean createMode, boolean createKeepIds,
        boolean disableSpaceOptimization) throws FxTreeException {
    long firstCreatedNodeId = -1;
    FxTreeNodeInfoSpreaded nodeInfo;//ww w  .  j av a2s .c  o m
    try {
        nodeInfo = (FxTreeNodeInfoSpreaded) getTreeNodeInfo(con, sourceMode, nodeId);
    } catch (Exception e) {
        return -1;
    }

    if (!nodeInfo.isSpaceOptimizable() && !disableSpaceOptimization) {
        // The Root node and cant be optimize any more ... so all we can do is fail :-/
        // This should never really happen
        if (nodeId == ROOT_NODE) {
            return -1;
        }
        //System.out.println("### UP we go, depthDelta=" + depthDelta);
        return _reorganizeSpace(con, seq, sourceMode, destMode, nodeInfo.getParentId(), includeNodeId,
                overrideSpacing, overrideLeft, insertParent, insertPosition, insertSpace, insertBoundaries,
                depthDelta, destinationNode, createMode, createKeepIds, false);
    }

    BigInteger spacing = nodeInfo.getDefaultSpacing();
    if (overrideSpacing != null && (overrideSpacing.compareTo(spacing) < 0 || overrideLeft != null)) {
        // override spacing unless it is greater OR overrideLeft is specified (in that case we
        // have to use the spacing for valid tree ranges)  
        spacing = overrideSpacing;
    } else {
        if (spacing.compareTo(GO_UP) < 0 && !createMode && !disableSpaceOptimization) {
            return _reorganizeSpace(con, seq, sourceMode, destMode, nodeInfo.getParentId(), includeNodeId,
                    overrideSpacing, overrideLeft, insertParent, insertPosition, insertSpace, insertBoundaries,
                    depthDelta, destinationNode, createMode, createKeepIds, false);
        }
    }

    if (insertBoundaries != null && insertPosition == -1) {
        insertPosition = 0; // insertPosition cannot be negative
    }

    Statement stmt = null;
    PreparedStatement ps = null;
    ResultSet rs;
    BigInteger left = overrideLeft == null ? nodeInfo.getLeft() : overrideLeft;
    BigInteger right = null;
    String includeNode = includeNodeId ? "=" : "";
    long counter = 0;
    long newId = -1;
    try {
        final long start = System.currentTimeMillis();
        String createProps = createMode ? ",PARENT,REF,NAME,TEMPLATE" : "";
        String sql = " SELECT ID," + StorageManager.getIfFunction( // compute total child count only when the node has children
                "CHILDCOUNT = 0", "0",
                "(SELECT COUNT(*) FROM " + getTable(sourceMode) + " WHERE LFT > NODE.LFT AND RGT < NODE.RGT)") +
        // 3           4             5   6
                ", CHILDCOUNT, LFT AS LFTORD,RGT,DEPTH" + createProps
                + " FROM (SELECT ID,CHILDCOUNT,LFT,RGT,DEPTH" + createProps + " FROM " + getTable(sourceMode)
                + " WHERE " + "LFT>" + includeNode + nodeInfo.getLeft() + " AND LFT<" + includeNode
                + nodeInfo.getRight() + ") NODE " + "ORDER BY LFTORD ASC";
        stmt = con.createStatement();
        rs = stmt.executeQuery(sql);
        if (createMode) {
            //                                                                 1  2      3     4     5   6        7   8
            ps = con.prepareStatement(
                    "INSERT INTO " + getTable(destMode) + " (ID,PARENT,DEPTH,DIRTY,REF,TEMPLATE,LFT,RGT," +
                    //9           10    11
                            "CHILDCOUNT,NAME,MODIFIED_AT) " + "VALUES (?,?,?,?,?,?,?,?,?,?,?)");
        } else {
            ps = con.prepareStatement("UPDATE " + getTable(sourceMode) + " SET LFT=?,RGT=?,DEPTH=? WHERE ID=?");
        }
        long id;
        int total_childs;
        int direct_childs;
        BigInteger nextLeft;
        int lastDepth = nodeInfo.getDepth() + (includeNodeId ? 0 : 1);
        int depth;
        BigInteger _rgt;
        BigInteger _lft;
        Long ref = null;
        String data = null;
        String name = "";

        Stack<Long> currentParent = null;
        if (createMode) {
            currentParent = new Stack<Long>();
            currentParent.push(destinationNode);
        }

        //System.out.println("Spacing:"+SPACING);
        while (rs.next()) {
            //System.out.println("------------------");
            id = rs.getLong(1);
            total_childs = rs.getInt(2);
            direct_childs = rs.getInt(3);
            _lft = getNodeBounds(rs, 4);
            _rgt = getNodeBounds(rs, 5);
            depth = rs.getInt(6);
            if (createMode) {
                // Reading these properties is slow, only do it when needed
                ref = rs.getLong(8);
                if (rs.wasNull())
                    ref = null;
                name = rs.getString(9);
                data = rs.getString(10);
                if (rs.wasNull())
                    data = null;
            }
            left = left.add(spacing).add(BigInteger.ONE);

            // Handle depth differences
            if (lastDepth - depth > 0) {
                BigInteger depthDifference = spacing.add(BigInteger.ONE);
                left = left.add(depthDifference.multiply(BigInteger.valueOf(lastDepth - depth)));
            }
            if (createMode) {
                if (lastDepth < depth) {
                    currentParent.push(newId);
                } else if (lastDepth > depth) {
                    for (int p = 0; p < (lastDepth - depth); p++)
                        currentParent.pop();
                }
            }

            right = left.add(spacing).add(BigInteger.ONE);

            // add child space if needed
            if (total_childs > 0) {
                BigInteger childSpace = spacing.multiply(BigInteger.valueOf(total_childs * 2));
                childSpace = childSpace.add(BigInteger.valueOf((total_childs * 2) - 1));
                right = right.add(childSpace);
                nextLeft = left;
            } else {
                nextLeft = right;
            }

            if (insertBoundaries != null) {
                // insert gap at requested position
                // If we're past the gap, keep adding the insert space to left/right because the added
                // space is never "injected" into the loop, i.e. without adding it the left/right boundaries of
                // nodes after the gap would be too far to the left.
                if (_lft.compareTo(insertBoundaries[0]) > 0) {
                    left = left.add(insertSpace);
                }
                if (_rgt.compareTo(insertBoundaries[0]) > 0) {
                    right = right.add(insertSpace);
                }
            }

            // sanity checks
            if (left.compareTo(right) >= 0) {
                throw new FxTreeException(LOG, "ex.tree.reorganize.failed", counter, left, right,
                        "left greater than right");
            }
            if (insertParent != null && right.compareTo((BigInteger) insertParent.getRight()) > 0) {
                throw new FxTreeException(LOG, "ex.tree.reorganize.failed", counter, left, right,
                        "wrote past parent node bounds");
            }

            // Update the node
            if (createMode) {
                newId = createKeepIds ? id : seq.getId(destMode.getSequencer());
                if (firstCreatedNodeId == -1)
                    firstCreatedNodeId = newId;

                // Create the main entry
                ps.setLong(1, newId);
                ps.setLong(2, currentParent.peek());
                ps.setLong(3, depth + depthDelta);
                ps.setBoolean(4, destMode != FxTreeMode.Live); //only flag non-live tree's dirty
                if (ref == null) {
                    ps.setNull(5, java.sql.Types.NUMERIC);
                } else {
                    ps.setLong(5, ref);
                }
                if (data == null) {
                    ps.setNull(6, java.sql.Types.VARCHAR);
                } else {
                    ps.setString(6, data);
                }
                //                    System.out.println("=> id:"+newId+" left:"+left+" right:"+right);
                setNodeBounds(ps, 7, left);
                setNodeBounds(ps, 8, right);
                ps.setInt(9, direct_childs);
                ps.setString(10, name);
                ps.setLong(11, System.currentTimeMillis());
                ps.addBatch();
            } else {
                setNodeBounds(ps, 1, left);
                setNodeBounds(ps, 2, right);
                ps.setInt(3, depth + depthDelta);
                ps.setLong(4, id);
                ps.addBatch();
                //                    ps.executeBatch();
                //                    ps.clearBatch();
            }

            // Prepare variables for the next node
            left = nextLeft;
            lastDepth = depth;
            counter++;

            // Execute batch every 10000 items to avoid out of memory
            if (counter % 10000 == 0) {
                ps.executeBatch();
                ps.clearBatch();
            }
        }
        rs.close();
        stmt.close();
        stmt = null;
        ps.executeBatch();

        if (LOG.isDebugEnabled()) {
            final long time = System.currentTimeMillis() - start;

            LOG.debug("Tree reorganization of " + counter + " items completed in " + time + " ms (spaceLen="
                    + spacing + ")");
        }
        return firstCreatedNodeId;
    } catch (FxApplicationException e) {
        throw e instanceof FxTreeException ? (FxTreeException) e : new FxTreeException(e);
    } catch (SQLException e) {
        String next = "";
        if (e.getNextException() != null)
            next = " next:" + e.getNextException().getMessage();
        if (StorageManager.isDuplicateKeyViolation(e))
            throw new FxTreeException(LOG, e, "ex.tree.reorganize.duplicateKey");
        throw new FxTreeException(LOG, e, "ex.tree.reorganize.failed", counter, left, right,
                e.getMessage() + next);
    } catch (Exception e) {
        throw new FxTreeException(e);
    } finally {
        try {
            if (stmt != null)
                stmt.close();
        } catch (Throwable t) {
            /*ignore*/}
        try {
            if (ps != null)
                ps.close();
        } catch (Throwable t) {
            /*ignore*/}
    }
}

From source file:com.flexive.core.storage.genericSQL.GenericHierarchicalStorage.java

/**
 * {@inheritDoc}// ww w.j ava  2  s .co m
 */
@Override
public void convertContentType(Connection con, FxPK pk, long sourceTypeId, long destinationTypeId,
        boolean allVersions, Map<Long, Long> assignmentMap, List<Long> flatStoreAssignments,
        List<Long> nonFlatSourceAssignments, List<Long> nonFlatDestinationAssignments,
        Map<Long, String> sourcePathsMap, Map<Long, String> destPathsMap, Map<Long, String> sourceRemoveMap,
        FxEnvironment env) throws SQLException, FxApplicationException {

    PreparedStatement ps = null, ps_group = null;
    final long pkId = pk.getId();
    final Integer[] versions;
    // check if all versions should be converted or the current one only
    if (allVersions) {
        final FxContentVersionInfo versionInfo = getContentVersionInfo(con, pkId);
        versions = versionInfo.getVersions();
    } else {
        versions = new Integer[] { pk.getVersion() };
    }

    // cache the content versions
    final List<FxContent> contentVersions = new ArrayList<FxContent>(versions.length);
    for (int v : versions) {
        contentVersions.add(contentLoad(con, new FxPK(pkId, v), env, new StringBuilder(2000)));
    }
    long userId = FxContext.getUserTicket().getUserId();
    FxFlatStorage flatStorage = FxFlatStorageManager.getInstance();
    boolean flatRewrite = false;

    // lossy conversion source remove map
    if (sourceRemoveMap.size() > 0) {
        final Set<Long> removeSet = sourceRemoveMap.keySet();
        for (Long removeId : removeSet) {
            for (FxContent content : contentVersions) {
                List<FxData> data = content.getData(sourceRemoveMap.get(removeId));
                FulltextIndexer ft = getFulltextIndexer(pk, con);
                ft.remove(removeId);
                for (FxData d : data) {
                    deleteDetailData(con, new StringBuilder(2000), content.getPk(), d);
                }
            }
        }
        // if no flatstorage assignments remain, delete all orphaned flat entries
        final List<Long> compareRemoveList = new ArrayList<Long>(removeSet.size());
        for (Long flatStoreId : flatStoreAssignments) {
            for (Long removeId : removeSet) {
                if (assignmentMap.containsKey(removeId)) {
                    final Long mappedItem = assignmentMap.get(removeId);
                    if (mappedItem == null || (mappedItem != null && mappedItem.equals(flatStoreId)))
                        compareRemoveList.add(flatStoreId);
                }
            }
        }
        Collections.sort(flatStoreAssignments);
        Collections.sort(compareRemoveList);
        // compare compareRemoveList & flatStoreAssignments, if no differences found, remove all source flat store content
        final FxDiff diff = new FxDiff(compareRemoveList, flatStoreAssignments);
        if (diff.diff().size() == 0) {
            flatStorage.removeContent(con, sourceTypeId, pkId);
        }
    }

    /**
     * use cases:
     * 1. hierarchical --> flat conversion
     * 2. flat --> hierrarchical conversion
     * 3. hierarchical --> hierarchical conversion
     * 4. flat --> flat conversion
     */
    try {
        for (Long sourceId : assignmentMap.keySet()) {
            // do not convert any removed source content assignments
            if (sourceRemoveMap.containsKey(sourceId))
                continue;

            final String sourceXPath = sourcePathsMap.get(sourceId);
            final Long destinationId = assignmentMap.get(sourceId);
            final String destXPath = destPathsMap.get(destinationId);

            for (FxContent content : contentVersions) {
                if (destinationId == null) {
                    // delete from the source
                    if (nonFlatSourceAssignments.contains(sourceId) && content.getValue(destXPath) != null) { // source is hierarchical
                        final List<FxData> data = content.getData(destXPath);
                        for (FxData d : data) {
                            deleteDetailData(con, new StringBuilder(2000), content.getPk(), d);
                        }
                    }
                    continue; // goto next source id
                }

                // move on if no value was set
                if (content.getValue(sourceXPath) == null)
                    continue;

                final FxPropertyData propData = content.getPropertyData(destXPath);
                final List<FxData> data = content.getData(destXPath);
                // use case 1: hierarchical --> flat
                if (nonFlatSourceAssignments.contains(sourceId)
                        && flatStoreAssignments.contains(destinationId)) {
                    for (FxData d : data) {
                        deleteDetailData(con, new StringBuilder(2000), content.getPk(), d);
                    }
                    flatRewrite = true;
                }
                // use case 2: flat --> hierarchical
                if (!nonFlatSourceAssignments.contains(sourceId)
                        && nonFlatDestinationAssignments.contains(destinationId)) {
                    ps = con.prepareStatement(CONTENT_DATA_INSERT);

                    // data for the current xpath
                    createDetailEntries(con, ps, null, new StringBuilder(2000), content.getPk(),
                            content.isMaxVersion(), content.isLiveVersion(), data, true);
                    ps.executeBatch();
                    ps.close();

                    // remove the old entry from the flatstorage
                    flatStorage.deletePropertyData(con, content.getPk(), propData);
                    flatRewrite = true;
                }
            }

        }

        if (flatRewrite || flatStoreAssignments.size() > 0) {
            // re-create (remove old entries first) all flat storage entries with correct column settings
            flatStorage.removeContent(con, sourceTypeId, pkId);
            for (FxContent content : contentVersions) {
                List<FxPropertyData> data = new ArrayList<FxPropertyData>(5);
                for (Long id : flatStoreAssignments) {
                    final String destXPath = destPathsMap.get(id);
                    if (content.getValue(destXPath) != null)
                        data.add(content.getPropertyData(destXPath));
                }

                if (data.size() > 0) {
                    flatStorage.setConvertedPropertyData(con, content.getPk(), sourceTypeId, destinationTypeId,
                            content.getStepId(), content.isMaxVersion(), content.isLiveVersion(), data);
                }
            }
        }

        // update tables w/ new assignment ids (valid for all use cases)
        if (allVersions) {
            ps = con.prepareStatement(CONTENT_CONVERT_ALL_VERSIONS_UPDATE);
            ps.setLong(1, destinationTypeId);
            ps.setLong(2, userId);
            ps.setLong(3, System.currentTimeMillis());
            ps.setLong(4, pkId);
            ps.executeUpdate();
            ps.close();

            ps = con.prepareStatement(CONTENT_DATA_CONVERT_ALL_VERSIONS_UPDATE);
            // perform one update per assignmentMap entry
            for (Long sourceId : assignmentMap.keySet()) {
                final Long destId = assignmentMap.get(sourceId);
                if (destId != null) {
                    ps.setLong(1, destId);
                    ps.setLong(2, pkId);
                    ps.setLong(3, sourceId);
                    ps.executeUpdate();
                }
            }
            ps.close();

            ps = con.prepareStatement(CONTENT_DATA_FT_CONVERT_ALL_VERSIONS_UPDATE);
            // perform one update per assignmentMap entry
            for (Long sourceId : assignmentMap.keySet()) {
                final Long destId = assignmentMap.get(sourceId);
                if (destId != null) {
                    ps.setLong(1, destId);
                    ps.setLong(2, pkId);
                    ps.setLong(3, sourceId);
                    ps.executeUpdate();
                }
            }
            ps.close();
        } else { // convert single version
            ps = con.prepareStatement(CONTENT_CONVERT_SINGLE_VERSION_UPDATE);
            ps.setLong(1, destinationTypeId);
            ps.setLong(2, userId);
            ps.setLong(3, System.currentTimeMillis());
            ps.setLong(4, pkId);
            ps.setInt(5, pk.getVersion());
            ps.executeUpdate();
            ps.close();

            ps = con.prepareStatement(CONTENT_DATA_CONVERT_SINGLE_VERSION_UPDATE);
            // perform one update per assignmentMap entry
            for (Long sourceId : assignmentMap.keySet()) {
                final Long destId = assignmentMap.get(sourceId);
                if (destId != null) {
                    ps.setLong(1, destId);
                    ps.setLong(2, pkId);
                    ps.setLong(3, sourceId);
                    ps.setInt(4, pk.getVersion());
                    ps.executeUpdate();
                }
            }
            ps.close();

            ps = con.prepareStatement(CONTENT_DATA_FT_CONVERT_SINGLE_VERSION_UPDATE);
            // perform one update per assignmentMap entry
            for (Long sourceId : assignmentMap.keySet()) {
                final Long destId = assignmentMap.get(sourceId);
                if (destId != null) {
                    ps.setLong(1, destId);
                    ps.setLong(2, pkId);
                    ps.setLong(3, sourceId);
                    ps.setInt(4, pk.getVersion());
                    ps.executeUpdate();
                }
            }
            ps.close();
        }

    } catch (SQLException e) {
        throw new FxUpdateException(LOG, e, "ex.db.sqlError", e.getMessage());
    } finally {
        Database.closeObjects(GenericHierarchicalStorage.class, ps);
    }
}

From source file:com.app.das.business.dao.SearchDAO.java

/**
 * ?? .//from   ww  w .ja va2  s .  co m
 * @param myCatalogDO ??  ? ?  DataObject
 * @return
 * @throws Exception 
 */

public int[] insertMyCatalog(List myCatalogDO) throws Exception {
    /*   if(isThereMySearchItem(myCatalogDO))
       return 1;
    else
    {   */
    StringBuffer buf = new StringBuffer();
    buf.append("\n insert into DAS.MY_SRCHLIST_TBL( ");
    buf.append("\n    REQ_USRID, ");
    buf.append("\n    SEQ, ");
    buf.append("\n    MASTER_ID, ");
    buf.append("\n    CN_ID, ");
    buf.append("\n    PGM_ID, ");
    buf.append("\n    PGM_NM, ");
    buf.append("\n    EPN,  ");
    buf.append("\n    TITLE, ");
    buf.append("\n    BRD_DD, ");
    buf.append("\n    BRD_LENG, ");
    buf.append("\n    CN_LENG, ");
    buf.append("\n    ANNOT_CLF_CD, ");
    buf.append("\n    CONT,  ");
    buf.append("\n    RPIMG_KFRM_SEQ, ");
    buf.append("\n    GOOD_SC, ");
    buf.append("\n    NOT_USE, ");
    buf.append("\n    DILBRT, ");
    buf.append("\n    CHECK, ");
    buf.append("\n    KFRM_PATH, ");
    buf.append("\n    KFRM_SEQ, ");
    buf.append("\n    RPIMG_CT_ID, ");
    buf.append("\n    REG_DT, ");
    buf.append("\n    REGRID, ");
    buf.append("\n    MOD_DT, ");
    buf.append("\n    MODRID, ");
    buf.append("\n    ASP_RTO_CD, ");
    buf.append("\n    VD_QLTY, ");
    buf.append("\n    PILOT_YN, ");
    buf.append("\n    SUB_TTL, ");
    buf.append("\n    WEEKDAY, ");
    buf.append("\n    FINAL_BRD_YN, ");
    buf.append("\n    SCHD_PGM_NM ");
    buf.append(
            "\n )values(?, NEXTVAL FOR SEQ_MYSEQ, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");

    Connection con = null;
    PreparedStatement stmt = null;
    try {
        con = DBService.getInstance().getConnection();
        //logger.debug("######insertMyCatalog######## con : " + con);
        stmt = con.prepareStatement(buf.toString());

        // ? .
        String toDateTime = CalendarUtil.getDateTime("yyyyMMddHHmmss");
        int index = 0;

        for (int i = 0; i < myCatalogDO.size(); i++) {
            index = 0;
            MyCatalogDO myCatalog = (MyCatalogDO) myCatalogDO.get(i);

            stmt.setString(++index, myCatalog.getReqUsrId()); //REQ_USRID   

            if (myCatalog.getMasterId() != 0) {
                stmt.setLong(++index, myCatalog.getMasterId());//MASTER_ID

            } else {
                stmt.setLong(++index, 0);//MASTER_ID   
            }

            if (myCatalog.getCnId() != 0) {
                stmt.setLong(++index, myCatalog.getCnId());//CN_ID
            } else {
                stmt.setLong(++index, 0);
            }

            if (myCatalog.getPgmId() != 0) {
                stmt.setLong(++index, myCatalog.getPgmId());//PGM_ID
            } else {
                stmt.setLong(++index, 0);
            }

            if (!myCatalog.getPgmNm().equals("")) {
                stmt.setString(++index, myCatalog.getPgmNm()); //  PGM_NM 

            } else {
                stmt.setString(++index, "");
            }

            if (!myCatalog.getEpn().equals("0")) {
                stmt.setInt(++index, Integer.parseInt(myCatalog.getEpn()));//EPN
            } else {
                stmt.setInt(++index, 0);
            }

            if (!myCatalog.getTitle().equals("")) {
                stmt.setString(++index, myCatalog.getTitle()); //  TITLE 
            } else {
                stmt.setString(++index, "");
            }

            if (!myCatalog.getBrdDd().equals("")) {
                stmt.setString(++index, myCatalog.getBrdDd()); // BRD_DD  
            } else {
                stmt.setString(++index, "");
            }

            if (!myCatalog.getBrdLeng().equals("")) {
                stmt.setString(++index, myCatalog.getBrdLeng()); //BRD_LENG
            } else {
                stmt.setString(++index, "");
            }

            if (!myCatalog.getCnLeng().equals("")) {
                stmt.setString(++index, myCatalog.getCnLeng()); //CN_LENG
            } else {
                stmt.setString(++index, "");
            }

            if (!myCatalog.getAnnotClfCd().equals("")) {
                stmt.setString(++index, myCatalog.getAnnotClfCd()); //ANNOT_CLF_CD
            } else {
                stmt.setString(++index, "");
            }

            if (!myCatalog.getCont().equals("")) {

                stmt.setString(++index, myCatalog.getCont()); // CONT
            } else {
                stmt.setString(++index, "");
            }

            if (myCatalog.getRpImg() != 0) {
                stmt.setInt(++index, myCatalog.getRpImg());//RPIMG_KFRM_SEQ
            } else {
                stmt.setInt(++index, 0);
            }

            if (!myCatalog.getGoodSc().equals("")) {
                stmt.setString(++index, myCatalog.getGoodSc()); //  GOOD_SC
            } else {
                stmt.setString(++index, "");
            }

            if (!myCatalog.getNotUse().equals("")) {
                stmt.setString(++index, myCatalog.getNotUse()); //  NOT_USE
            } else {
                stmt.setString(++index, "");
            }

            if (!myCatalog.getDilbrt().equals("")) {
                //stmt.setString(++index, myCatalog.getDilbrt());  
                stmt.setString(++index, "");//  DILBRT
            } else {
                stmt.setString(++index, "");
            }

            if (!myCatalog.getCheck().equals("")) {
                stmt.setString(++index, myCatalog.getCheck()); //  CHECK
            } else {
                stmt.setString(++index, "");
            }

            if (!myCatalog.getKfrmPath().equals("")) {
                stmt.setString(++index, myCatalog.getKfrmPath());//KFRM_PATH
            } else {
                stmt.setString(++index, "");
            }

            if (myCatalog.getKfrmSeq() != 0) {
                stmt.setInt(++index, myCatalog.getKfrmSeq());//KFRM_SEQ
            } else {
                stmt.setInt(++index, 0);
            }

            if (myCatalog.getRpimgCtId() != 0) {
                stmt.setLong(++index, myCatalog.getRpimgCtId());//RPIMG_CT_ID
            } else {
                stmt.setLong(++index, 0);
            }

            stmt.setString(++index, toDateTime); //REG_DT

            if (!myCatalog.getReqUsrId().equals("")) {
                stmt.setString(++index, myCatalog.getReqUsrId()); //  REGRID 
            } else {
                stmt.setString(++index, "");
            }

            stmt.setString(++index, toDateTime); //MOD_DT

            if (!myCatalog.getRegrId().equals("")) {
                stmt.setString(++index, myCatalog.getRegrId()); //MODRID
            } else {
                stmt.setString(++index, "");
            }

            if (!myCatalog.getAspRtoCd().equals("")) {
                stmt.setString(++index, myCatalog.getAspRtoCd());//ASP_RTO_CD
            } else {
                stmt.setString(++index, "");
            }

            if (!myCatalog.getVdQlty().equals("")) {
                stmt.setString(++index, myCatalog.getVdQlty());//VD_QLTY
            } else {
                stmt.setString(++index, "");
            }

            if (!myCatalog.getPilot_yn().equals("")) {
                stmt.setString(++index, myCatalog.getPilot_yn());//PILOT_YN
            } else {
                stmt.setString(++index, "");
            }

            if (!myCatalog.getSub_ttl().equals("")) {
                stmt.setString(++index, myCatalog.getSub_ttl());//SUB_TTL
            } else {
                stmt.setString(++index, "");
            }

            if (!myCatalog.getDay().equals("")) {
                stmt.setString(++index, ""); //WEEKDAY
            } else {
                stmt.setString(++index, "");
            }

            if (!myCatalog.getFinal_brd_yn().equals("")) {
                stmt.setString(++index, myCatalog.getFinal_brd_yn());//FINAL_BRD_YN
            } else {
                stmt.setString(++index, "");
            }

            if (!myCatalog.getSchd_pgm_nm().equals("")) {
                stmt.setString(++index, myCatalog.getSchd_pgm_nm());//SCHD_PGM_NM
            } else {
                stmt.setString(++index, "");
            }

            stmt.addBatch();

        }
        int[] rInt = null;
        if (myCatalogDO.size() > 0)
            rInt = stmt.executeBatch();

        con.commit();
        return rInt;
    } catch (Exception e) {

        logger.error(buf.toString());

        throw e;

    } finally {
        release(null, stmt, con);
    }

}

From source file:org.wso2.carbon.appmgt.impl.dao.AppMDAO.java

/**
 * Delete the records of external app Store details.
 *
 * @param identifier APIIdentifier/*from w  ww .j av a  2 s  . com*/
 * @param appStores  AppStores set
 * @throws AppManagementException
 */
public void deleteExternalAppStoresDetails(APIIdentifier identifier, Set<AppStore> appStores)
        throws AppManagementException {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = APIMgtDBUtil.getConnection();
        conn.setAutoCommit(false);

        final String sqlQuery = "DELETE FROM APM_EXTERNAL_STORES WHERE APP_ID=? AND STORE_ID=? ";

        if (log.isDebugEnabled()) {
            String msg = String.format("Getting web app Id for provider:%s ,name :%s, version :%s",
                    identifier.getProviderName(), identifier.getApiName(), identifier.getVersion());
            log.debug(msg);
        }
        //Get app id
        int appId;
        appId = getAPIID(identifier, conn);
        if (appId == -1) {
            String msg = String.format("Could not load app record of app : provider:%s ,name :%s, version :%s",
                    identifier.getProviderName(), identifier.getApiName(), identifier.getVersion());
            log.error(msg);
            throw new AppManagementException(msg);
        }

        if (log.isDebugEnabled()) {
            String msg = String.format(
                    "Delete external app store details of app :" + " provider:%s ,name :%s, version :%s",
                    identifier.getProviderName(), identifier.getApiName(), identifier.getVersion());
            log.debug(msg);
        }

        ps = conn.prepareStatement(sqlQuery);
        Iterator it = appStores.iterator();
        while (it.hasNext()) {
            Object storeObject = it.next();
            AppStore store = (AppStore) storeObject;
            ps.setInt(1, appId);
            ps.setString(2, store.getName());
            ps.addBatch();
        }

        ps.executeBatch();
        conn.commit();
    } catch (SQLException e) {
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                log.error("Failed to rollback deleting external app store details  for  app" + " : "
                        + identifier.getApiName() + "-" + identifier.getVersion(), e1);
            }
        }
        handleException("Failed to delete external app store details for  app" + " : " + identifier.getApiName()
                + "-" + identifier.getVersion(), e);
    } finally {
        APIMgtDBUtil.closeAllConnections(ps, conn, null);
    }
}

From source file:org.wso2.carbon.appmgt.impl.dao.AppMDAO.java

/**
 * Store external AppStore details to which app successfully published.
 *
 * @param apiId     APIIdentifier/*from  w  w w.  j av a2  s.c  o  m*/
 * @param appStores AppStore set
 * @throws org.wso2.carbon.appmgt.api.AppManagementException
 */
public void addExternalAppStoresDetails(APIIdentifier apiId, Set<AppStore> appStores)
        throws AppManagementException {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = APIMgtDBUtil.getConnection();
        conn.setAutoCommit(false);

        //This query to add external App Stores to database table
        final String sqlQuery = "INSERT " + "INTO APM_EXTERNAL_STORES (APP_ID, STORE_ID) " + "VALUES (?,?)";

        if (log.isDebugEnabled()) {
            String msg = String.format("Getting web app id of app : provider:%s ,name :%s, version :%s",
                    apiId.getProviderName(), apiId.getApiName(), apiId.getVersion());
            log.debug(msg);
        }

        //Get app id
        int appId;
        appId = getAPIID(apiId, conn);
        if (appId == -1) {
            String msg = String.format("Could not load app record of app : provider:%s ,name :%s, version :%s",
                    apiId.getProviderName(), apiId.getApiName(), apiId.getVersion());
            log.error(msg);
            throw new AppManagementException(msg);
        }

        if (log.isDebugEnabled()) {
            String msg = String.format(
                    "Add published external app store details of app ->"
                            + " provider:%s ,name :%s, version :%s",
                    apiId.getProviderName(), apiId.getApiName(), apiId.getVersion());
            log.debug(msg);
        }
        ps = conn.prepareStatement(sqlQuery);
        Iterator it = appStores.iterator();
        while (it.hasNext()) {
            AppStore store = (AppStore) it.next();
            ps.setInt(1, appId);
            ps.setString(2, store.getName());
            ps.addBatch();
        }
        ps.executeBatch();
        conn.commit();
    } catch (SQLException e) {
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                log.error("Failed to rollback storing external app store details  for  app" + " : "
                        + apiId.getApiName() + "-" + apiId.getVersion(), e1);
            }
        }
        handleException("Failed to store external app store details for  app" + " : " + apiId.getApiName() + "-"
                + apiId.getVersion(), e);
    } finally {
        APIMgtDBUtil.closeAllConnections(ps, conn, null);
    }
}