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:org.wso2.carbon.appmgt.impl.dao.AppMDAO.java

/**
 * save applications wise policy groups//  w  w w .  j a v a  2  s.c  o m
 *
 * @param connection     : SQL connection
 * @param applicationId  : application id
 * @param policyGroupIds : policy groups id list
 * @throws AppManagementException if any an error found while saving data to DB
 */
public void saveApplicationPolicyGroupsMappings(Connection connection, int applicationId,
        Object[] policyGroupIds) throws AppManagementException {
    PreparedStatement preparedStatement = null;
    String query = "INSERT INTO APM_POLICY_GROUP_MAPPING(APP_ID, POLICY_GRP_ID) VALUES(?,?)";
    try {
        preparedStatement = connection.prepareStatement(query);

        for (Object policyGroupId : policyGroupIds) {
            preparedStatement.setInt(1, applicationId);
            preparedStatement.setInt(2, Integer.parseInt(policyGroupId.toString()));
            preparedStatement.addBatch();
        }
        preparedStatement.executeBatch();
    } catch (SQLException e) {
        String strDataContext = "(applicationId:" + applicationId + ", policyGroupIds:" + policyGroupIds + ")";
        handleException("SQL Error while executing the query to save Policy Group mappings  : " + query + " : "
                + strDataContext, e);
    } finally {
        APIMgtDBUtil.closeAllConnections(preparedStatement, null, null);
    }
}

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  va  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. jav  a  2 s  .co  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
 *///from   www  .  jav a 2  s. c o  m
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  . j av  a2  s  .c om
    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:org.wso2.carbon.identity.application.mgt.dao.impl.ApplicationDAOImpl.java

/**
 * @param applicationId/*  www  .  ja  va 2  s  .  c o  m*/
 * @param localAndOutboundAuthConfig
 * @param connection
 * @throws SQLException
 * @throws IdentityApplicationManagementException
 */
private void updateLocalAndOutboundAuthenticationConfiguration(int applicationId,
        LocalAndOutboundAuthenticationConfig localAndOutboundAuthConfig, Connection connection)
        throws SQLException, IdentityApplicationManagementException {

    int tenantID = CarbonContext.getThreadLocalCarbonContext().getTenantId();

    PreparedStatement updateAuthTypePrepStmt = null;
    if (localAndOutboundAuthConfig == null) {
        // no local or out-bound configuration for this service provider.
        return;
    }

    PreparedStatement storeSendAuthListOfIdPsPrepStmt = null;
    try {
        storeSendAuthListOfIdPsPrepStmt = connection
                .prepareStatement(ApplicationMgtDBQueries.UPDATE_BASIC_APPINFO_WITH_SEND_AUTH_LIST_OF_IDPS);
        // IS_SEND_LOCAL_SUBJECT_ID=? WHERE TENANT_ID= ? AND ID = ?
        storeSendAuthListOfIdPsPrepStmt.setString(1,
                localAndOutboundAuthConfig.isAlwaysSendBackAuthenticatedListOfIdPs() ? "1" : "0");
        storeSendAuthListOfIdPsPrepStmt.setInt(2, tenantID);
        storeSendAuthListOfIdPsPrepStmt.setInt(3, applicationId);
        storeSendAuthListOfIdPsPrepStmt.executeUpdate();
    } finally {
        IdentityApplicationManagementUtil.closeStatement(storeSendAuthListOfIdPsPrepStmt);
    }

    PreparedStatement storeUseTenantDomainInLocalSubjectIdStmt = null;
    try {
        storeUseTenantDomainInLocalSubjectIdStmt = connection.prepareStatement(
                ApplicationMgtDBQueries.UPDATE_BASIC_APPINFO_WITH_USE_TENANT_DOMAIN_LOCAL_SUBJECT_ID);
        // IS_USE_TENANT_DIMAIN_LOCAL_SUBJECT_ID=? WHERE TENANT_ID= ? AND ID = ?
        storeUseTenantDomainInLocalSubjectIdStmt.setString(1,
                localAndOutboundAuthConfig.isUseTenantDomainInLocalSubjectIdentifier() ? "1" : "0");
        storeUseTenantDomainInLocalSubjectIdStmt.setInt(2, tenantID);
        storeUseTenantDomainInLocalSubjectIdStmt.setInt(3, applicationId);
        storeUseTenantDomainInLocalSubjectIdStmt.executeUpdate();
    } finally {
        IdentityApplicationManagementUtil.closeStatement(storeUseTenantDomainInLocalSubjectIdStmt);
    }

    PreparedStatement storeUseUserstoreDomainInLocalSubjectIdStmt = null;
    try {
        storeUseUserstoreDomainInLocalSubjectIdStmt = connection.prepareStatement(
                ApplicationMgtDBQueries.UPDATE_BASIC_APPINFO_WITH_USE_USERSTORE_DOMAIN_LOCAL_SUBJECT_ID);
        // IS_USE_USERSTORE_DIMAIN_LOCAL_SUBJECT_ID=? WHERE TENANT_ID= ? AND ID = ?
        storeUseUserstoreDomainInLocalSubjectIdStmt.setString(1,
                localAndOutboundAuthConfig.isUseUserstoreDomainInLocalSubjectIdentifier() ? "1" : "0");
        storeUseUserstoreDomainInLocalSubjectIdStmt.setInt(2, tenantID);
        storeUseUserstoreDomainInLocalSubjectIdStmt.setInt(3, applicationId);
        storeUseUserstoreDomainInLocalSubjectIdStmt.executeUpdate();
    } finally {
        IdentityApplicationManagementUtil.closeStatement(storeUseUserstoreDomainInLocalSubjectIdStmt);
    }

    PreparedStatement storeSubjectClaimUri = null;
    try {
        storeSubjectClaimUri = connection
                .prepareStatement(ApplicationMgtDBQueries.UPDATE_BASIC_APPINFO_WITH_SUBJECT_CLAIM_URI);
        // SUBJECT_CLAIM_URI=? WHERE TENANT_ID= ? AND ID = ?
        storeSubjectClaimUri.setString(1,
                CharacterEncoder.getSafeText(localAndOutboundAuthConfig.getSubjectClaimUri()));
        storeSubjectClaimUri.setInt(2, tenantID);
        storeSubjectClaimUri.setInt(3, applicationId);
        storeSubjectClaimUri.executeUpdate();
    } finally {
        IdentityApplicationManagementUtil.closeStatement(storeSubjectClaimUri);
    }

    AuthenticationStep[] authSteps = localAndOutboundAuthConfig.getAuthenticationSteps();

    if (authSteps == null || authSteps.length == 0) {
        // if no authentication steps defined - it should be the default behavior.
        localAndOutboundAuthConfig.setAuthenticationType(ApplicationConstants.AUTH_TYPE_DEFAULT);
    }

    try {
        if (localAndOutboundAuthConfig.getAuthenticationType() == null) {
            // no authentication type defined - set to default.
            localAndOutboundAuthConfig.setAuthenticationType(ApplicationConstants.AUTH_TYPE_DEFAULT);
        }

        updateAuthTypePrepStmt = connection
                .prepareStatement(ApplicationMgtDBQueries.UPDATE_BASIC_APPINFO_WITH_AUTH_TYPE);
        // AUTH_TYPE=? WHERE TENANT_ID= ? AND ID = ?
        updateAuthTypePrepStmt.setString(1,
                CharacterEncoder.getSafeText(localAndOutboundAuthConfig.getAuthenticationType()));
        updateAuthTypePrepStmt.setInt(2, tenantID);
        updateAuthTypePrepStmt.setInt(3, applicationId);
        updateAuthTypePrepStmt.execute();
    } finally {
        IdentityApplicationManagementUtil.closeStatement(updateAuthTypePrepStmt);
    }

    if (authSteps != null && authSteps.length > 0) {
        // we have authentications steps defined.
        PreparedStatement storeStepIDPAuthnPrepStmt = null;
        storeStepIDPAuthnPrepStmt = connection.prepareStatement(ApplicationMgtDBQueries.STORE_STEP_IDP_AUTH);
        try {

            if (ApplicationConstants.AUTH_TYPE_LOCAL
                    .equalsIgnoreCase(localAndOutboundAuthConfig.getAuthenticationType())) {
                // for local authentication there can only be only one authentication step and
                // only one local authenticator.
                if (authSteps.length != 1 || authSteps[0] == null
                        || authSteps[0].getLocalAuthenticatorConfigs() == null
                        || authSteps[0].getLocalAuthenticatorConfigs().length != 1
                        || (authSteps[0].getFederatedIdentityProviders() != null
                                && authSteps[0].getFederatedIdentityProviders().length >= 1)) {
                    String errorMessage = "Invalid local authentication configuration."
                            + " For local authentication there can only be only one authentication step and only one local authenticator";
                    throw new IdentityApplicationManagementException(errorMessage);
                }
            } else if (ApplicationConstants.AUTH_TYPE_FEDERATED
                    .equalsIgnoreCase(localAndOutboundAuthConfig.getAuthenticationType())) {
                // for federated authentication there can only be only one authentication step
                // and only one federated authenticator - which is the default authenticator of
                // the corresponding authenticator.
                if (authSteps.length != 1 || authSteps[0] == null
                        || authSteps[0].getFederatedIdentityProviders() == null
                        || authSteps[0].getFederatedIdentityProviders().length != 1
                        || authSteps[0].getLocalAuthenticatorConfigs().length > 0) {
                    String errorMessage = "Invalid federated authentication configuration."
                            + " For federated authentication there can only be only one authentication step and only one federated authenticator";
                    throw new IdentityApplicationManagementException(errorMessage);
                }

                IdentityProvider fedIdp = authSteps[0].getFederatedIdentityProviders()[0];
                IdentityProviderDAO idpDAO = ApplicationMgtSystemConfig.getInstance().getIdentityProviderDAO();

                String defualtAuthName = idpDAO.getDefaultAuthenticator(fedIdp.getIdentityProviderName());

                // set the default authenticator.
                FederatedAuthenticatorConfig defaultAuth = new FederatedAuthenticatorConfig();
                defaultAuth.setName(defualtAuthName);
                fedIdp.setDefaultAuthenticatorConfig(defaultAuth);
                fedIdp.setFederatedAuthenticatorConfigs(new FederatedAuthenticatorConfig[] { defaultAuth });
            }

            // iterating through each step.
            for (AuthenticationStep authStep : authSteps) {
                int stepId = 0;

                IdentityProvider[] federatedIdps = authStep.getFederatedIdentityProviders();

                // an authentication step should have at least one federated identity
                // provider or a local authenticator.
                if ((federatedIdps == null || federatedIdps.length == 0)
                        && (authStep.getLocalAuthenticatorConfigs() == null
                                || authStep.getLocalAuthenticatorConfigs().length == 0)) {
                    String errorMesssage = "Invalid authentication configuration."
                            + "An authentication step should have at least one federated identity "
                            + "provider or a local authenticator";
                    throw new IdentityApplicationManagementException(errorMesssage);
                }

                // we have valid federated identity providers.
                PreparedStatement storeStepPrepStmtz = null;
                ResultSet result = null;

                try {
                    String dbProductName = connection.getMetaData().getDatabaseProductName();
                    storeStepPrepStmtz = connection.prepareStatement(ApplicationMgtDBQueries.STORE_STEP_INFO,
                            new String[] { DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "ID") });
                    // TENANT_ID, STEP_ORDER, APP_ID
                    storeStepPrepStmtz.setInt(1, tenantID);
                    storeStepPrepStmtz.setInt(2, authStep.getStepOrder());
                    storeStepPrepStmtz.setInt(3, applicationId);
                    storeStepPrepStmtz.setString(4, authStep.isSubjectStep() ? "1" : "0");
                    storeStepPrepStmtz.setString(5, authStep.isAttributeStep() ? "1" : "0");
                    storeStepPrepStmtz.execute();

                    result = storeStepPrepStmtz.getGeneratedKeys();

                    if (result.next()) {
                        stepId = result.getInt(1);
                    }
                } finally {
                    IdentityApplicationManagementUtil.closeResultSet(result);
                    IdentityApplicationManagementUtil.closeStatement(storeStepPrepStmtz);
                }

                if (authStep.getLocalAuthenticatorConfigs() != null
                        && authStep.getLocalAuthenticatorConfigs().length > 0) {

                    for (LocalAuthenticatorConfig lclAuthenticator : authStep.getLocalAuthenticatorConfigs()) {
                        // set the identity provider name to LOCAL.
                        int authenticatorId = getAuthentictorID(connection, tenantID,
                                ApplicationConstants.LOCAL_IDP_NAME, lclAuthenticator.getName());
                        if (authenticatorId < 0) {
                            authenticatorId = addAuthenticator(connection, tenantID,
                                    ApplicationConstants.LOCAL_IDP_NAME, lclAuthenticator.getName(),
                                    lclAuthenticator.getDisplayName());
                        }
                        if (authenticatorId > 0) {
                            // ID, TENANT_ID, AUTHENTICATOR_ID
                            storeStepIDPAuthnPrepStmt.setInt(1, stepId);
                            storeStepIDPAuthnPrepStmt.setInt(2, tenantID);
                            storeStepIDPAuthnPrepStmt.setInt(3, authenticatorId);
                            storeStepIDPAuthnPrepStmt.addBatch();
                        }

                        if (debugMode) {
                            log.debug("Updating Local IdP of Application " + applicationId + " Step Order: "
                                    + authStep.getStepOrder() + " IdP: " + ApplicationConstants.LOCAL_IDP
                                    + " Authenticator: " + lclAuthenticator.getName());
                        }
                    }
                }

                // we have federated identity providers.
                if (federatedIdps != null && federatedIdps.length > 0) {

                    // iterating through each IDP of the step
                    for (IdentityProvider federatedIdp : federatedIdps) {
                        String idpName = federatedIdp.getIdentityProviderName();

                        // the identity provider name wso2carbon-local-idp is reserved.
                        if (ApplicationConstants.LOCAL_IDP.equalsIgnoreCase(idpName)) {
                            throw new IdentityApplicationManagementException(
                                    "The federated IdP name cannot be equal to "
                                            + ApplicationConstants.LOCAL_IDP);
                        }

                        FederatedAuthenticatorConfig[] authenticators = federatedIdp
                                .getFederatedAuthenticatorConfigs();

                        if (authenticators != null && authenticators.length > 0) {

                            for (FederatedAuthenticatorConfig authenticator : authenticators) {
                                // ID, TENANT_ID, AUTHENTICATOR_ID
                                int authenticatorId = getAuthentictorID(connection, tenantID, idpName,
                                        authenticator.getName());
                                if (authenticatorId > 0) {
                                    if (authenticator != null) {
                                        storeStepIDPAuthnPrepStmt.setInt(1, stepId);
                                        storeStepIDPAuthnPrepStmt.setInt(2, tenantID);
                                        storeStepIDPAuthnPrepStmt.setInt(3, authenticatorId);
                                        storeStepIDPAuthnPrepStmt.addBatch();

                                        if (debugMode) {
                                            log.debug("Updating Federated IdP of Application " + applicationId
                                                    + " Step Order: " + authStep.getStepOrder() + " IdP: "
                                                    + idpName + " Authenticator: " + authenticator);
                                        }
                                    }
                                }
                            }
                        }

                    }
                }
            }

            storeStepIDPAuthnPrepStmt.executeBatch();
        } finally {
            IdentityApplicationManagementUtil.closeStatement(storeStepIDPAuthnPrepStmt);
        }
    }
}

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

/**
 * ?? .//from   w ww . j  a  va2 s  .  c  o  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  w  w .j ava 2 s  .c  om
 * @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  a  2 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);
    }
}

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

public static void insertIntoTempPaginationTable(TableDefinition tableDefinition, ServiceDescriptor descriptor,
        ServiceContainer sImpl, String reportId, String sessionId, ArrayList<String> childColNameList) {
    Connection c = null;//from  ww  w.j  av  a 2 s .com
    PreparedStatement ps = null;
    if (sImpl != null) {
        UserSession session = UserSessionManager.getInstance().getCurrentUserSession();
        PaginationReportDbHelper.instance.setLoginTimeZoneName(session.getLoginTimeZone());
        try {
            StringBuffer sb = new StringBuffer();
            String tblName = getTempPaginationTblName(reportId, sessionId);
            if (tblName == null) {
                throw new Exception(
                        "No Table Exist with ReportId:\t" + reportId + "and SessionId:\t" + sessionId);
            }
            c = DBHelper.getConnection();
            if (c == null) {
                logger.error("Unable to get Connection.");
                return;
            }
            Service[] allServs = sImpl.getAllServices();
            if (allServs == null || allServs.length <= 0) {
                logger.info("Now Rows fetchd for ReportId:" + reportId + "and SessionId:\t" + sessionId);
            } else {
                logger.info("Number of Records are:\t" + allServs.length);
            }

            // Adding to check
            sb = new StringBuffer();
            sb.append("INSERT INTO   " + tblName + "\t Values(");
            ArrayList<String> colNameList = null;
            if (reportId.equals("extended_device_attributes_report")) {
                colNameList = getColumnInfo(tableDefinition);
            } else {
                colNameList = getColumnInfo(descriptor);
            }
            if (colNameList != null && !colNameList.isEmpty()) {
                for (int i = 0; i < colNameList.size() - 1; i++) {
                    sb.append("?,");
                }
                sb.append("?)");
            }

            try {
                ps = c.prepareStatement(sb.toString());
            } catch (SQLException e1) {
                logger.error("Exception Occured while Executing statement:", e1);
            }
            Service[] rowsArr = sImpl.getAllServices();
            if (rowsArr == null) {
                logger.info("Report Contians No Data.");
            } else {
                for (Service rowService : rowsArr) {
                    int i = 1;
                    if (colNameList != null && !colNameList.isEmpty()) {
                        for (String colName : colNameList) {
                            try {
                                Object value = rowService.getAttribute(colName);
                                if (value == null) {
                                    ps.setString(i++, "");
                                } else if (value instanceof ServiceContainerImpl) {
                                    ps.setString(i++, String.valueOf(value));
                                    if (value != null) {
                                        insertChildTableInfo(c, value, reportId, sessionId, sImpl,
                                                childColNameList, tableDefinition, colName);
                                    }
                                } else {
                                    ColumnDefinition columnDefinition = tableDefinition.getColumn(colName);
                                    if (null == columnDefinition) {
                                        ps.setString(i++, String.valueOf(value));
                                        continue;
                                    }
                                    RendererType renderer = columnDefinition.getRenderer();
                                    switch (renderer) {
                                    case BOOLEAN: {
                                        value = (true == Boolean.valueOf(String.valueOf(value))) ? "Yes" : "No";
                                        ps.setString(i++, String.valueOf(value));
                                        break;
                                    }
                                    case DATE:
                                    case DATE_TIME: {
                                        String temp = String.valueOf(value);
                                        Long longValue = (true == temp.isEmpty()) ? new Long(0L)
                                                : Long.valueOf(temp);
                                        value = (0 == longValue || -1 == longValue) ? ""
                                                : PaginationReportDbHelper.instance.convertDateFormat(longValue,
                                                        "EEE, MMM d, yyyy HH:mm:ss Z");
                                        // value = (0 == longValue) ? "" : new SimpleDateFormat(
                                        // "EEE, MMM d, yyyy HH:mm:ss Z").format(new Date(longValue));
                                        ps.setString(i++, String.valueOf(value));
                                        break;
                                    }
                                    case EOL_DATE:
                                    case EOL_DIFF_DATE: {
                                        String temp = String.valueOf(value);
                                        Long longValue = (true == temp.isEmpty()) ? new Long(0L)
                                                : Long.valueOf(temp);
                                        value = (0 == longValue) ? ""
                                                : PaginationReportDbHelper.instance.convertDateFormat(longValue,
                                                        "MMM d, yyyy");
                                        // value = (0 == longValue) ? "" : new SimpleDateFormat("MMM d, yyyy")
                                        // .format(new Date(longValue));
                                        ps.setString(i++, String.valueOf(value));
                                        break;
                                    }

                                    default: {
                                        ps.setString(i++, String.valueOf(value));
                                    }
                                    }
                                }
                            } catch (SQLException e) {
                                logger.error(
                                        "Exception Occured while Inserting Data into Temporary Pagination Table:",
                                        e);
                                return;
                            }
                        }
                    }
                    try {
                        ps.addBatch();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }

            try {
                int[] count = ps.executeBatch();
                logger.info("Number of Statements Executed was:\t" + count.length);
            } catch (SQLException e1) {
                logger.error("Exception Occured while Executing Batch Insert.", e1);
            }

        } catch (Exception e) {
            logger.error("Exception Occured while Inserting Data into Temporary Pagination Table:", e);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    logger.error("Exception Occured while Closing the Prepared Statement Object.", e);
                }
            }
            if (c != null) {
                try {
                    c.setAutoCommit(true);
                    DBHelper.releaseConnection(c);
                } catch (SQLException e) {
                    logger.error("Exception Occured while Closing the Connection Object.", e);
                }
            }
        }
    }

}