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:HSqlPrimerDesign.java

@SuppressWarnings("Duplicates")
public static void locations(Connection connection) throws ClassNotFoundException, SQLException,
        InstantiationException, IllegalAccessException, IOException {
    long time = System.nanoTime();
    String base = new File("").getAbsolutePath();
    DpalLoad.main(new String[0]);
    Dpal_Inst = DpalLoad.INSTANCE_WIN64;
    System.out.println(Dpal_Inst);
    Connection db = connection;//  w  w  w .java2 s .  c o m
    db.setAutoCommit(false);
    Statement stat = db.createStatement();
    PrintWriter log = new PrintWriter(new File("javalog.log"));
    stat.execute("SET FILES LOG FALSE;");
    PreparedStatement st = db.prepareStatement("INSERT INTO Primerdb.MatchedPrimers("
            + "Primer, PrimerMatch, Comp,FragAVG,FragVAR,H2SD,L2SD, Cluster, Strain)"
            + "Values(?,?,?,?,?,?,?,?,?)");
    ResultSet call = stat.executeQuery("Select * From Primerdb.Phages;");
    List<String[]> phages = new ArrayList<>();
    while (call.next()) {
        String[] r = new String[3];
        r[0] = call.getString("Strain");
        r[1] = call.getString("Cluster");
        r[2] = call.getString("Name");
        phages.add(r);
        //            if(strain.equals("-myco")) {
        //                if (r[2].equals("xkcd")) {
        //                    strain = r[0];
        //                }
        //            }else if(strain.equals("-arthro")){
        //                if (r[2].equals("ArV1")) {
        //                    strain = r[0];
        //                }
        //            }
    }
    call.close();
    Set<String> strains = phages.stream().map(y -> y[0]).collect(Collectors.toSet());
    for (String x : strains) {
        Set<String> clust = phages.stream().filter(y -> y[0].equals(x)).map(y -> y[1])
                .collect(Collectors.toSet());
        String[] clusters = clust.toArray(new String[clust.size()]);
        //        String z ="A1";
        for (String z : clusters) {
            System.out.println("Starting:" + z);
            List<Primer> primers = new ArrayList<>();
            Set<Matches> matched = new HashSet<>();
            Set<String> clustphage = phages.stream().filter(a -> a[0].equals(x) && a[1].equals(z))
                    .map(a -> a[2]).collect(Collectors.toSet());
            String[] clustphages = clustphage.toArray(new String[clustphage.size()]);
            if (clustphages.length > 1) {
                try {
                    ResultSet resultSet = stat
                            .executeQuery("Select * from primerdb.primers" + " where Strain ='" + x
                                    + "' and Cluster ='" + z + "' and UniqueP = true" + " and Hairpin = false");
                    while (resultSet.next()) {
                        Primer primer = new Primer(resultSet.getString("Sequence"));
                        primer.setTm(resultSet.getDouble("Tm"));
                        primers.add(primer);
                    }
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    System.out.println("Error occurred at " + x + " " + z);
                }
                System.out.println(primers.size());
                Set<Primer> primerlist2 = primers.stream().collect(Collectors.toSet());
                Primer[] primers2 = primerlist2.toArray(new Primer[primerlist2.size()]);
                Map<String, Map<CharSequence, List<Integer>>> locations = Collections
                        .synchronizedMap(new HashMap<>());
                clustphage.stream().forEach(phage -> {
                    String[] seqs = Fasta.parse(base + "/Fastas/" + phage + ".fasta");
                    String sequence = seqs[0] + seqs[1];
                    Map<String, List<Integer>> seqInd = new HashMap<>();
                    for (int i = 0; i <= sequence.length() - 10; i++) {
                        String sub = sequence.substring(i, i + 10);
                        if (seqInd.containsKey(sub)) {
                            seqInd.get(sub).add(i);
                        } else {
                            List<Integer> list = new ArrayList<>();
                            list.add(i);
                            seqInd.put(sub, list);
                        }
                    }
                    Map<CharSequence, List<Integer>> alllocs = new HashMap<>();
                    for (Primer primer : primers2) {
                        List<Integer> locs = new ArrayList<>();
                        String sequence1 = primer.getSequence();
                        String frag = sequence1.substring(0, 10);
                        List<Integer> integers = seqInd.get(frag);
                        if (integers != null) {
                            for (Integer i : integers) {
                                if ((sequence1.length() + i) < sequence.length()
                                        && sequence.substring(i, sequence1.length() + i).equals(sequence1)) {
                                    locs.add(i);
                                }
                            }
                        }
                        alllocs.put(sequence1, locs);
                    }
                    locations.put(phage, alllocs);
                });
                System.out.println("locations found");
                System.out.println((System.nanoTime() - time) / Math.pow(10, 9) / 60.0);
                final int[] k = new int[] { 0 };
                primerlist2.parallelStream().forEach(a -> {
                    int matches = 0;
                    int i = 0;
                    while (primers2[i] != a) {
                        i++;
                    }
                    for (int j = i + 1; j < primers2.length; j++) {
                        double[] frags = new double[clustphages.length];
                        int phageCounter = 0;
                        Primer b = primers2[j];
                        boolean match = true;
                        if (matches > 0) {
                            break;
                        }
                        if (Math.abs(a.getTm() - b.getTm()) > 5.0 || a.getSequence().equals(b.getSequence())) {
                            continue;
                        }
                        for (String phage : clustphages) {
                            List<Integer> loc1 = locations.get(phage).get(a.getSequence());
                            List<Integer> loc2 = locations.get(phage).get(b.getSequence());
                            //                            if(loc1.size()==0){
                            //                                System.out.println(phage+" "+a.getSequence());
                            //                            }
                            if (loc1.size() == 0 || loc2.size() == 0) {
                                //                                if (loc1.size()!=1||loc2.size()!=1){
                                match = false;
                                break;
                            }
                            boolean found = false;
                            int fragCount = 0;
                            int l1 = loc1.get(0);
                            int l2 = loc2.get(0);
                            int count1 = 0;
                            int count2 = 0;
                            int frag = Math.abs(l1 - l2);
                            while (!found) {
                                if (frag >= 500 && frag <= 2000) {
                                    fragCount++;
                                    if (++count1 < loc1.size())
                                        l1 = loc1.get(count1);
                                    else if (++count2 < loc2.size())
                                        l2 = loc2.get(count2);
                                } else if (l1 < l2 && frag < 500) {
                                    count2++;
                                } else if (l1 > l2 && frag < 500) {
                                    count1++;
                                } else if (l1 > l2 && frag > 2000) {
                                    count2++;
                                } else if (l1 < l2 && frag > 2000) {
                                    count1++;
                                } else {
                                    break;
                                }
                                if (count1 < loc1.size() && count2 < loc2.size()) {
                                    l1 = loc1.get(count1);
                                    l2 = loc2.get(count2);
                                    frag = Math.abs(l1 - l2);
                                } else {
                                    if (fragCount == 1) {
                                        found = true;
                                        frags[phageCounter++] = frag + 0.0;
                                    } else {
                                        break;
                                    }
                                }
                            }
                            if (!found) {
                                match = false;
                                break;
                            }

                        }
                        if (match) {
                            matches++;
                            matched.add(new Matches(a, b, frags));
                        }
                    }
                    //                    k[0]++;
                    //                    System.out.println(k[0]);
                });
                System.out.println((System.nanoTime() - time) / Math.pow(10, 9) / 60.0);
                System.out.println("Primers matched");
                int c = 0;
                int i = 0;
                try {
                    for (Matches primerkey : matched) {
                        c++;
                        String primer1 = primerkey.one.getSequence();
                        String primer2 = primerkey.two.getSequence();
                        st.setString(1, primer1);
                        st.setString(2, primer2);
                        st.setDouble(3, complementarity(primer1, primer2, Dpal_Inst));
                        st.setDouble(4, primerkey.stats.getMean());
                        st.setDouble(5, primerkey.stats.getVariance());
                        st.setDouble(6, primerkey.stats.getMean() + 2 * primerkey.stats.getStandardDeviation());
                        st.setDouble(7, primerkey.stats.getMean() - 2 * primerkey.stats.getStandardDeviation());
                        st.setString(8, z);
                        st.setString(9, x);
                        st.addBatch();
                        i++;
                        if (i == 1000) {
                            i = 0;
                            st.executeBatch();
                            db.commit();
                        }
                    }

                    if (i > 0) {
                        st.executeBatch();
                        db.commit();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                    System.out.println("Error occurred at " + x + " " + z);
                }
                System.out.println(c);
            }
            log.println(z);
            log.flush();
            System.gc();
        }
    }
    stat.execute("SET FILES LOG TRUE;");
    st.close();
    stat.close();
    System.out.println("Matches Submitted");
}

From source file:org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.java

/**
 * Delete the records of external APIStore details.
 *
 * @param apiId       APIIdentifier//from   w w  w  .jav a2s.c  o m
 * @param apiStoreSet APIStores set
 * @return added/failed
 * @throws APIManagementException
 */
public boolean deleteExternalAPIStoresDetails(APIIdentifier apiId, Set<APIStore> apiStoreSet)
        throws APIManagementException {
    Connection conn = null;
    PreparedStatement ps = null;
    boolean state = false;
    try {
        conn = APIMgtDBUtil.getConnection();
        conn.setAutoCommit(false);

        String sqlQuery = SQLConstants.REMOVE_EXTERNAL_API_STORE_SQL;

        //Get API Id
        int apiIdentifier;
        apiIdentifier = getAPIID(apiId, conn);
        if (apiIdentifier == -1) {
            String msg = "Could not load API record for: " + apiId.getApiName();
            log.error(msg);
        }
        ps = conn.prepareStatement(sqlQuery);
        for (Object storeObject : apiStoreSet) {
            APIStore store = (APIStore) storeObject;
            ps.setInt(1, apiIdentifier);
            ps.setString(2, store.getName());
            ps.setString(3, store.getType());
            ps.addBatch();
        }
        ps.executeBatch();

        conn.commit();
        state = true;
    } catch (SQLException e) {
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                log.error("Failed to rollback deleting external apistore details ", e1);
            }
        }
        log.error("Failed to delete external apistore details", e);
        state = false;
    } catch (APIManagementException e) {
        log.error("Failed to delete external apistore details", e);
        state = false;
    } finally {
        APIMgtDBUtil.closeAllConnections(ps, conn, null);
    }
    return state;
}

From source file:org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.java

/**
 * Updateexternal APIStores details to which APIs published
 *
 * @param apiIdentifier API Identifier//www  .j  a va 2s .co m
 * @throws APIManagementException if failed to add Application
 */
public void updateExternalAPIStoresDetails(APIIdentifier apiIdentifier, Set<APIStore> apiStoreSet,
        Connection conn) throws APIManagementException, SQLException {
    PreparedStatement ps = null;

    try {
        conn.setAutoCommit(false);
        //This query to add external APIStores to database table
        String sqlQuery = SQLConstants.UPDATE_EXTERNAL_API_STORE_SQL;

        ps = conn.prepareStatement(sqlQuery);
        //Get API Id
        int apiId;
        apiId = getAPIID(apiIdentifier, conn);
        if (apiId == -1) {
            String msg = "Could not load API record for: " + apiIdentifier.getApiName();
            log.error(msg);
        }

        for (Object storeObject : apiStoreSet) {
            APIStore store = (APIStore) storeObject;
            ps.setString(1, store.getEndpoint());
            ps.setString(2, store.getType());
            ps.setInt(3, apiId);
            ps.setString(4, store.getName());
            ps.addBatch();
        }

        ps.executeBatch();
        ps.clearBatch();

        conn.commit();
    } catch (SQLException e) {
        log.error("Error while updating External APIStore details to the database for API : ", e);
    } finally {
        APIMgtDBUtil.closeAllConnections(ps, null, null);
    }
}

From source file:org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.java

/**
 * Store external APIStore details to which APIs successfully published
 *
 * @param apiId       APIIdentifier/*from w  w w. ja  va2  s.  c o m*/
 * @param apiStoreSet APIStores set
 * @return added/failed
 * @throws APIManagementException
 */
public boolean addExternalAPIStoresDetails(APIIdentifier apiId, Set<APIStore> apiStoreSet)
        throws APIManagementException {
    Connection conn = null;
    PreparedStatement ps = null;
    boolean state = false;
    try {
        conn = APIMgtDBUtil.getConnection();
        conn.setAutoCommit(false);

        //This query to add external APIStores to database table
        String sqlQuery = SQLConstants.ADD_EXTERNAL_API_STORE_SQL;

        //Get API Id
        int apiIdentifier;
        apiIdentifier = getAPIID(apiId, conn);
        if (apiIdentifier == -1) {
            String msg = "Could not load API record for: " + apiId.getApiName();
            log.error(msg);
        }
        ps = conn.prepareStatement(sqlQuery);
        for (Object storeObject : apiStoreSet) {
            APIStore store = (APIStore) storeObject;
            ps.setInt(1, apiIdentifier);
            ps.setString(2, store.getName());
            ps.setString(3, store.getDisplayName());
            ps.setString(4, store.getEndpoint());
            ps.setString(5, store.getType());
            ps.addBatch();
        }

        ps.executeBatch();
        conn.commit();
        state = true;
    } catch (SQLException e) {
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                log.error("Failed to rollback storing external apistore details ", e1);
            }
        }
        log.error("Failed to store external apistore details", e);
        state = false;
    } catch (APIManagementException e) {
        log.error("Failed to store external apistore details", e);
        state = false;
    } finally {
        APIMgtDBUtil.closeAllConnections(ps, conn, null);
    }
    return state;
}

From source file:org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.java

/**
 * Remove scope entries from DB, when delete APIs
 *
 * @param apiIdentifier The {@link APIIdentifier} of the API
 *///from w  ww  .  j  ava2 s .  c  o m
private void removeAPIScope(APIIdentifier apiIdentifier) throws APIManagementException {
    Set<Scope> scopes = getAPIScopes(apiIdentifier);

    Connection connection = null;
    PreparedStatement prepStmt = null;
    PreparedStatement deleteOauth2ResourceScopePrepStmt = null;
    PreparedStatement deleteOauth2ScopePrepStmt = null;
    int scopeId;
    int apiId = -1;

    String deleteAPIScopeQuery = SQLConstants.REMOVE_FROM_API_SCOPES_SQL;
    String deleteOauth2ScopeQuery = SQLConstants.REMOVE_FROM_OAUTH_SCOPE_SQL;
    String deleteOauth2ResourceScopeQuery = SQLConstants.REMOVE_FROM_OAUTH_RESOURCE_SQL;

    try {
        connection = APIMgtDBUtil.getConnection();
        connection.setAutoCommit(false);

        prepStmt = connection.prepareStatement(deleteAPIScopeQuery);
        prepStmt.setInt(1, apiId);
        prepStmt.execute();

        if (!scopes.isEmpty()) {
            deleteOauth2ResourceScopePrepStmt = connection.prepareStatement(deleteOauth2ResourceScopeQuery);
            deleteOauth2ScopePrepStmt = connection.prepareStatement(deleteOauth2ScopeQuery);
            for (Scope scope : scopes) {
                scopeId = scope.getId();

                deleteOauth2ResourceScopePrepStmt.setInt(1, scopeId);
                deleteOauth2ResourceScopePrepStmt.addBatch();

                deleteOauth2ScopePrepStmt.setInt(1, scopeId);
                deleteOauth2ScopePrepStmt.addBatch();
            }
            deleteOauth2ResourceScopePrepStmt.executeBatch();
            deleteOauth2ScopePrepStmt.executeBatch();
        }

        connection.commit();
    } catch (SQLException e) {
        handleException("Error while removing the scopes for the API: " + apiIdentifier.getApiName()
                + " from the database", e);
    } finally {
        APIMgtDBUtil.closeAllConnections(deleteOauth2ResourceScopePrepStmt, null, null);
        APIMgtDBUtil.closeAllConnections(deleteOauth2ScopePrepStmt, null, null);
        APIMgtDBUtil.closeAllConnections(prepStmt, connection, null);
    }
}

From source file:org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.java

/**
 * Deletes an Application along with subscriptions, keys and registration data
 *
 * @param application Application object to be deleted from the database which has the application Id
 * @throws APIManagementException//from   w w w  .j  a v  a 2 s . co  m
 */
public void deleteApplication(Application application) throws APIManagementException {
    Connection connection = null;
    PreparedStatement deleteMappingQuery = null;
    PreparedStatement prepStmt = null;
    PreparedStatement prepStmtGetConsumerKey = null;
    PreparedStatement deleteRegistrationQuery = null;
    PreparedStatement deleteSubscription = null;
    PreparedStatement deleteDomainApp = null;
    PreparedStatement deleteAppKey = null;
    PreparedStatement deleteApp = null;
    ResultSet rs = null;

    String getSubscriptionsQuery = SQLConstants.GET_SUBSCRIPTION_ID_OF_APPLICATION_SQL;

    String getConsumerKeyQuery = SQLConstants.GET_CONSUMER_KEY_OF_APPLICATION_SQL;

    String deleteKeyMappingQuery = SQLConstants.REMOVE_APPLICATION_FROM_SUBSCRIPTION_KEY_MAPPINGS_SQL;
    String deleteSubscriptionsQuery = SQLConstants.REMOVE_APPLICATION_FROM_SUBSCRIPTIONS_SQL;
    String deleteApplicationKeyQuery = SQLConstants.REMOVE_APPLICATION_FROM_APPLICATION_KEY_MAPPINGS_SQL;
    String deleteDomainAppQuery = SQLConstants.REMOVE_APPLICATION_FROM_DOMAIN_MAPPINGS_SQL;
    String deleteApplicationQuery = SQLConstants.REMOVE_APPLICATION_FROM_APPLICATIONS_SQL;
    String deleteRegistrationEntry = SQLConstants.REMOVE_APPLICATION_FROM_APPLICATION_REGISTRATIONS_SQL;

    try {
        connection = APIMgtDBUtil.getConnection();
        connection.setAutoCommit(false);
        prepStmt = connection.prepareStatement(getSubscriptionsQuery);
        prepStmt.setInt(1, application.getId());
        rs = prepStmt.executeQuery();

        List<Integer> subscriptions = new ArrayList<Integer>();
        while (rs.next()) {
            subscriptions.add(rs.getInt("SUBSCRIPTION_ID"));
        }

        deleteMappingQuery = connection.prepareStatement(deleteKeyMappingQuery);
        for (Integer subscriptionId : subscriptions) {
            deleteMappingQuery.setInt(1, subscriptionId);
            deleteMappingQuery.addBatch();
        }
        deleteMappingQuery.executeBatch();

        if (log.isDebugEnabled()) {
            log.debug("Subscription Key mapping details are deleted successfully for Application - "
                    + application.getName());
        }

        deleteRegistrationQuery = connection.prepareStatement(deleteRegistrationEntry);
        deleteRegistrationQuery.setInt(1, application.getId());
        deleteRegistrationQuery.execute();

        if (log.isDebugEnabled()) {
            log.debug("Application Registration details are deleted successfully for Application - "
                    + application.getName());
        }

        deleteSubscription = connection.prepareStatement(deleteSubscriptionsQuery);
        deleteSubscription.setInt(1, application.getId());
        deleteSubscription.execute();

        if (log.isDebugEnabled()) {
            log.debug(
                    "Subscription details are deleted successfully for Application - " + application.getName());
        }

        prepStmtGetConsumerKey = connection.prepareStatement(getConsumerKeyQuery);
        prepStmtGetConsumerKey.setInt(1, application.getId());
        rs = prepStmtGetConsumerKey.executeQuery();
        ArrayList<String> consumerKeys = new ArrayList<String>();

        deleteDomainApp = connection.prepareStatement(deleteDomainAppQuery);
        while (rs.next()) {
            String consumerKey = rs.getString("CONSUMER_KEY");

            // This is true when OAuth app has been created by pasting consumer key/secret in the screen.
            String mode = rs.getString("CREATE_MODE");
            if (consumerKey != null) {
                deleteDomainApp.setString(1, consumerKey);
                deleteDomainApp.addBatch();

                KeyManagerHolder.getKeyManagerInstance().deleteMappedApplication(consumerKey);
                // OAuth app is deleted if only it has been created from API Store. For mapped clients we don't
                // call delete.
                if (!"MAPPED".equals(mode)) {
                    // Adding clients to be deleted.
                    consumerKeys.add(consumerKey);
                }

            }
        }
        deleteDomainApp.executeBatch();

        deleteAppKey = connection.prepareStatement(deleteApplicationKeyQuery);
        deleteAppKey.setInt(1, application.getId());
        deleteAppKey.execute();

        if (log.isDebugEnabled()) {
            log.debug("Application Key Mapping details are deleted successfully for Application - "
                    + application.getName());
        }

        deleteApp = connection.prepareStatement(deleteApplicationQuery);
        deleteApp.setInt(1, application.getId());
        deleteApp.execute();

        if (log.isDebugEnabled()) {
            log.debug("Application " + application.getName() + " is deleted successfully.");
        }

        connection.commit();

        for (String consumerKey : consumerKeys) {
            //delete on oAuthorization server.
            KeyManagerHolder.getKeyManagerInstance().deleteApplication(consumerKey);
        }
    } catch (SQLException e) {
        handleException("Error while removing application details from the database", e);
    } finally {
        APIMgtDBUtil.closeAllConnections(prepStmtGetConsumerKey, connection, rs);
        APIMgtDBUtil.closeAllConnections(prepStmt, null, rs);
        APIMgtDBUtil.closeAllConnections(deleteApp, null, null);
        APIMgtDBUtil.closeAllConnections(deleteAppKey, null, null);
        APIMgtDBUtil.closeAllConnections(deleteMappingQuery, null, null);
        APIMgtDBUtil.closeAllConnections(deleteRegistrationQuery, null, null);
        APIMgtDBUtil.closeAllConnections(deleteSubscription, null, null);
        APIMgtDBUtil.closeAllConnections(deleteDomainApp, null, null);
        APIMgtDBUtil.closeAllConnections(deleteAppKey, null, null);
        APIMgtDBUtil.closeAllConnections(deleteApp, null, null);

    }
}

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

/**
 * Set a properties data for inserts or updates
 *
 * @param insert          perform insert or update?
 * @param prop            current property
 * @param allData         all data of the instance (might be needed to buld references, etc.)
 * @param con             an open and valid connection
 * @param data            current property data
 * @param ps              prepared statement for the data table
 * @param ft              fulltext indexer
 * @param upperColumnPos  position of the uppercase column (if present, else <code>-1</code>)
 * @param includeFullText add fulltext entries? Will be skipped for position only changes
 * @throws SQLException        on errors
 * @throws FxUpdateException   on errors
 * @throws FxDbException       on errors
 * @throws FxNoAccessException for FxNoAccess values
 *//*w w w.  j a v  a  2s  . com*/
private void setPropertyData(boolean insert, FxProperty prop, List<FxData> allData, Connection con,
        FxPropertyData data, PreparedStatement ps, FulltextIndexer ft, int upperColumnPos,
        boolean includeFullText) throws SQLException, FxUpdateException, FxDbException, FxNoAccessException {
    FxValue value = data.getValue();
    if (value instanceof FxNoAccess)
        throw new FxNoAccessException("ex.content.value.noaccess");
    if (value.isMultiLanguage() != ((FxPropertyAssignment) data.getAssignment()).isMultiLang()) {
        if (((FxPropertyAssignment) data.getAssignment()).isMultiLang())
            throw new FxUpdateException("ex.content.value.invalid.multilanguage.ass.multi",
                    data.getXPathFull());
        else
            throw new FxUpdateException("ex.content.value.invalid.multilanguage.ass.single",
                    data.getXPathFull());
    }
    int pos_lang = insert ? INSERT_LANG_POS : UPDATE_ID_POS + 2;
    int pos_isdef_lang = insert ? INSERT_ISDEF_LANG_POS : UPDATE_MLDEF_POS;
    final FxEnvironment env = CacheAdmin.getEnvironment();
    if (prop.getDataType().isSingleRowStorage()) {
        //Data types that just use one db row can be handled in a very similar way
        Object translatedValue;
        GregorianCalendar gc = null;
        final long[] translatedLanguages = value.getTranslatedLanguages();
        for (long translatedLanguage : translatedLanguages) {
            translatedValue = value.getTranslation(translatedLanguage);
            if (translatedValue == null) {
                LOG.warn("Translation for " + data.getXPath() + " is null!");
            }
            ps.setLong(pos_lang, translatedLanguage);
            if (!value.isMultiLanguage())
                ps.setBoolean(pos_isdef_lang, true);
            else
                ps.setBoolean(pos_isdef_lang, value.isDefaultLanguage(translatedLanguage));
            if (upperColumnPos != -1) {
                final Locale locale = value.isMultiLanguage() ? env.getLanguage(translatedLanguage).getLocale()
                        : Locale.getDefault();
                ps.setString(upperColumnPos, translatedValue.toString().toUpperCase(locale));
            }
            int[] pos = insert ? getColumnPosInsert(prop) : getColumnPosUpdate(prop);
            switch (prop.getDataType()) {
            case Double:
                checkDataType(FxDouble.class, value, data.getXPathFull());
                ps.setDouble(pos[0], (Double) translatedValue);
                break;
            case Float:
                checkDataType(FxFloat.class, value, data.getXPathFull());
                ps.setFloat(pos[0], (Float) translatedValue);
                break;
            case LargeNumber:
                checkDataType(FxLargeNumber.class, value, data.getXPathFull());
                ps.setLong(pos[0], (Long) translatedValue);
                break;
            case Number:
                checkDataType(FxNumber.class, value, data.getXPathFull());
                ps.setInt(pos[0], (Integer) translatedValue);
                break;
            case HTML:
                checkDataType(FxHTML.class, value, data.getXPathFull());
                boolean useTidy = ((FxHTML) value).isTidyHTML();
                ps.setBoolean(pos[1], useTidy);
                final String extractorInput = doTidy(data.getXPathFull(), (String) translatedValue);
                if (useTidy) {
                    translatedValue = extractorInput;
                }
                final HtmlExtractor result = new HtmlExtractor(extractorInput, true);
                setBigString(ps, pos[2], result.getText());
                setBigString(ps, pos[0], (String) translatedValue);
                break;
            case String1024:
            case Text:
                checkDataType(FxString.class, value, data.getXPathFull());
                setBigString(ps, pos[0], (String) translatedValue);
                break;
            case Boolean:
                checkDataType(FxBoolean.class, value, data.getXPathFull());
                ps.setBoolean(pos[0], (Boolean) translatedValue);
                break;
            case Date:
                checkDataType(FxDate.class, value, data.getXPathFull());
                if (gc == null)
                    gc = new GregorianCalendar();
                gc.setTime((Date) translatedValue);
                //strip all time information, this might not be necessary since ps.setDate() strips them
                //for most databases but won't hurt either ;)
                gc.set(GregorianCalendar.HOUR, 0);
                gc.set(GregorianCalendar.MINUTE, 0);
                gc.set(GregorianCalendar.SECOND, 0);
                gc.set(GregorianCalendar.MILLISECOND, 0);
                ps.setDate(pos[0], new java.sql.Date(gc.getTimeInMillis()));
                break;
            case DateTime:
                checkDataType(FxDateTime.class, value, data.getXPathFull());
                if (gc == null)
                    gc = new GregorianCalendar();
                gc.setTime((Date) translatedValue);
                ps.setTimestamp(pos[0], new Timestamp(gc.getTimeInMillis()));
                break;
            case DateRange:
                checkDataType(FxDateRange.class, value, data.getXPathFull());
                if (gc == null)
                    gc = new GregorianCalendar();
                gc.setTime(((DateRange) translatedValue).getLower());
                gc.set(GregorianCalendar.HOUR, 0);
                gc.set(GregorianCalendar.MINUTE, 0);
                gc.set(GregorianCalendar.SECOND, 0);
                gc.set(GregorianCalendar.MILLISECOND, 0);
                ps.setDate(pos[0], new java.sql.Date(gc.getTimeInMillis()));
                gc.setTime(((DateRange) translatedValue).getUpper());
                gc.set(GregorianCalendar.HOUR, 0);
                gc.set(GregorianCalendar.MINUTE, 0);
                gc.set(GregorianCalendar.SECOND, 0);
                gc.set(GregorianCalendar.MILLISECOND, 0);
                ps.setDate(pos[1], new java.sql.Date(gc.getTimeInMillis()));
                break;
            case DateTimeRange:
                checkDataType(FxDateTimeRange.class, value, data.getXPathFull());
                if (gc == null)
                    gc = new GregorianCalendar();
                gc.setTime(((DateRange) translatedValue).getLower());
                ps.setTimestamp(pos[0], new Timestamp(gc.getTimeInMillis()));
                gc.setTime(((DateRange) translatedValue).getUpper());
                ps.setTimestamp(pos[1], new Timestamp(gc.getTimeInMillis()));
                break;
            case Binary:
                checkDataType(FxBinary.class, value, data.getXPathFull());
                BinaryDescriptor binary = (BinaryDescriptor) translatedValue;
                if (!binary.isNewBinary()) {
                    ps.setLong(pos[0], binary.getId());
                } else {
                    try {
                        //transfer the binary from the transit table to the binary table
                        BinaryDescriptor created = binaryStorage.binaryTransit(con, binary);
                        ps.setLong(pos[0], created.getId());
                        //check all other properties if they contain the same handle
                        //and replace with the data of the new binary
                        for (FxData _curr : allData) {
                            if (_curr instanceof FxPropertyData && !_curr.isEmpty()
                                    && ((FxPropertyData) _curr).getValue() instanceof FxBinary) {
                                FxBinary _val = (FxBinary) ((FxPropertyData) _curr).getValue();
                                _val._replaceHandle(binary.getHandle(), created);
                            }
                        }
                    } catch (FxApplicationException e) {
                        throw new FxDbException(e);
                    }
                }
                break;
            case SelectOne:
                checkDataType(FxSelectOne.class, value, data.getXPathFull());
                ps.setLong(pos[0], ((FxSelectListItem) translatedValue).getId());
                break;
            case SelectMany:
                checkDataType(FxSelectMany.class, value, data.getXPathFull());
                SelectMany sm = (SelectMany) translatedValue;

                for (int i1 = 0; i1 < sm.getSelected().size(); i1++) {
                    FxSelectListItem item = sm.getSelected().get(i1);
                    if (i1 > 0) {
                        if (batchContentDataChanges())
                            ps.addBatch();
                        else
                            ps.executeUpdate();
                    }
                    ps.setLong(pos[0], item.getId());
                    ps.setString(pos[1], sm.getSelectedIdsList());
                    ps.setLong(pos[2], sm.getSelectedIds().size());
                }
                if (sm.getSelected().size() == 0)
                    ps.setLong(pos[0], 0); //write the virtual item as a marker to have a valid row
                break;
            case Reference:
                //reference integrity check is done prior to saving
                ps.setLong(pos[0], ((FxPK) translatedValue).getId());
                break;
            case InlineReference:
            default:
                throw new FxDbException(LOG, "ex.db.notImplemented.store", prop.getDataType().getName());
            }
            int valueDataPos = insert ? getValueDataInsertPos(prop.getDataType())
                    : getValueDataUpdatePos(prop.getDataType());
            if (value.hasValueData(translatedLanguage)) {
                ps.setInt(valueDataPos, value.getValueDataRaw(translatedLanguage));
            } else
                ps.setNull(valueDataPos, Types.NUMERIC);
            if (batchContentDataChanges())
                ps.addBatch();
            else {
                try {
                    ps.executeUpdate();
                } catch (SQLException e) {
                    LOG.error(prop.getName(), e);
                    throw e;
                }
            }
        }
    } else {
        switch (prop.getDataType()) {
        //TODO: implement datatype specific insert
        default:
            throw new FxDbException(LOG, "ex.db.notImplemented.store", prop.getDataType().getName());
        }

    }
    if (ft != null && prop.isFulltextIndexed() && includeFullText)
        ft.index(data);
}

From source file:org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.java

/**
 * Adds URI templates define for an API/*w  ww .  java  2 s  . c o  m*/
 *
 * @param apiId
 * @param api
 * @param connection
 * @throws APIManagementException
 */
public void addURLTemplates(int apiId, API api, Connection connection) throws APIManagementException {
    if (apiId == -1) {
        //application addition has failed
        return;
    }
    PreparedStatement prepStmt = null;
    PreparedStatement scopePrepStmt = null;

    String query = SQLConstants.ADD_URL_MAPPING_SQL;
    String scopeQuery = SQLConstants.ADD_OAUTH2_RESOURCE_SCOPE_SQL;
    try {
        //connection = APIMgtDBUtil.getConnection();
        prepStmt = connection.prepareStatement(query);
        scopePrepStmt = connection.prepareStatement(scopeQuery);

        Iterator<URITemplate> uriTemplateIterator = api.getUriTemplates().iterator();
        URITemplate uriTemplate;
        for (; uriTemplateIterator.hasNext();) {
            uriTemplate = uriTemplateIterator.next();

            prepStmt.setInt(1, apiId);
            prepStmt.setString(2, uriTemplate.getHTTPVerb());
            prepStmt.setString(3, uriTemplate.getAuthType());
            prepStmt.setString(4, uriTemplate.getUriTemplate());
            //If API policy is available then set it for all the resources
            if (StringUtils.isEmpty(api.getApiLevelPolicy())) {
                prepStmt.setString(5, uriTemplate.getThrottlingTier());
            } else {
                prepStmt.setString(5, api.getApiLevelPolicy());
            }
            InputStream is;
            if (uriTemplate.getMediationScript() != null) {
                is = new ByteArrayInputStream(
                        uriTemplate.getMediationScript().getBytes(Charset.defaultCharset()));
            } else {
                is = null;
            }
            if (connection.getMetaData().getDriverName().contains("PostgreSQL")
                    || connection.getMetaData().getDatabaseProductName().contains("DB2")) {
                if (uriTemplate.getMediationScript() != null) {
                    prepStmt.setBinaryStream(6, is,
                            uriTemplate.getMediationScript().getBytes(Charset.defaultCharset()).length);
                } else {
                    prepStmt.setBinaryStream(6, is, 0);
                }
            } else {
                prepStmt.setBinaryStream(6, is);
            }
            prepStmt.addBatch();
            if (uriTemplate.getScope() != null) {
                scopePrepStmt.setString(1, APIUtil.getResourceKey(api, uriTemplate));

                if (uriTemplate.getScope().getId() == 0) {
                    String scopeKey = uriTemplate.getScope().getKey();
                    Scope scopeByKey = APIUtil.findScopeByKey(api.getScopes(), scopeKey);
                    if (scopeByKey != null) {
                        if (scopeByKey.getId() > 0) {
                            uriTemplate.getScopes().setId(scopeByKey.getId());
                        }
                    }
                }

                scopePrepStmt.setInt(2, uriTemplate.getScope().getId());
                scopePrepStmt.addBatch();
            }
        }
        prepStmt.executeBatch();
        prepStmt.clearBatch();
        scopePrepStmt.executeBatch();
        scopePrepStmt.clearBatch();
    } catch (SQLException e) {
        handleException("Error while adding URL template(s) to the database for API : " + api.getId(), e);
    } finally {
        APIMgtDBUtil.closeAllConnections(prepStmt, null, null);
        APIMgtDBUtil.closeAllConnections(scopePrepStmt, null, null);
    }
}

From source file:org.apache.hadoop.hive.metastore.MyXid.java

public void dropPartitionMeta(String dbName, long tblID, String partName, int level) {
    Connection con = null;//www.  ja  v a2  s .  co m
    Statement ps = null;
    PreparedStatement pss = null;

    dbName = dbName.toLowerCase();
    //partName = partName.toLowerCase();

    try {
        con = getSegmentConnection(dbName);

        pss = con.prepareStatement("delete from partitions where tbl_id=? and part_name=? and level=?");

        pss.setLong(1, tblID);
        pss.setString(2, partName.toLowerCase());
        pss.setInt(3, level);
        pss.addBatch();

        pss.executeBatch();

    } catch (Exception e1) {
        LOG.error("drop partition meta error, db=" + dbName + ", tblID=" + tblID + ", level=" + level + ", msg="
                + e1.getMessage());
    } finally {
        closeStatement(ps);
        closeConnection(con);
    }
}

From source file:org.apache.hadoop.hive.metastore.MyXid.java

public void dropPartitionMeta(String dbName, long tblID, AddPartitionDesc addPartitionDesc) {
    Connection con = null;//ww w .  j av  a2s  . co m
    Statement ps = null;
    PreparedStatement pss = null;

    dbName = dbName.toLowerCase();
    //partName = partName.toLowerCase();

    try {
        con = getSegmentConnection(dbName);

        pss = con.prepareStatement("delete from partitions where tbl_id=? and part_name=? and level=?");
        for (String partName : addPartitionDesc.getParSpaces().keySet()) {
            pss.setLong(1, tblID);
            pss.setString(2, partName.toLowerCase());
            pss.setInt(3, addPartitionDesc.getLevel());
            pss.addBatch();
        }

        pss.executeBatch();

    } catch (Exception e1) {
        LOG.error("drop partition meta error, db=" + dbName + ", tblID=" + tblID + ", level="
                + addPartitionDesc.getLevel() + ", msg=" + e1.getMessage());
    } finally {
        closeStatement(ps);
        closeConnection(con);
    }
}