Example usage for java.sql PreparedStatement getGeneratedKeys

List of usage examples for java.sql PreparedStatement getGeneratedKeys

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. getGeneratedKeys

Introduction

In this page you can find the example usage for java.sql PreparedStatement getGeneratedKeys.

Prototype

ResultSet getGeneratedKeys() throws SQLException;

Source Link

Document

Retrieves any auto-generated keys created as a result of executing this Statement object.

Usage

From source file:org.apache.sqoop.repository.common.CommonRepositoryHandler.java

/**
 * Save given inputs to the database./*from  w ww  .j ava  2  s  .c  o m*/
 *
 * Use given prepare statement to save all inputs into repository.
 *
 * @param config
 *          corresponding config
 * @param inputs
 *          List of inputs that needs to be saved
 * @param baseInputStmt
 *          Statement that we can utilize
 * @throws java.sql.SQLException
 *           In case of any failure on Derby side
 */
private void registerConfigInputs(MConfig config, List<MInput<?>> inputs, PreparedStatement baseInputStmt)
        throws SQLException {

    short inputIndex = 0;
    for (MInput<?> input : inputs) {
        baseInputStmt.setString(1, input.getName());
        baseInputStmt.setLong(2, config.getPersistenceId());
        baseInputStmt.setShort(3, inputIndex++);
        baseInputStmt.setString(4, input.getType().name());
        baseInputStmt.setBoolean(5, input.isSensitive());
        // String specific column(s)
        if (input.getType().equals(MInputType.STRING)) {
            MStringInput strInput = (MStringInput) input;
            baseInputStmt.setShort(6, strInput.getMaxLength());
        } else {
            baseInputStmt.setNull(6, Types.INTEGER);
        }

        baseInputStmt.setString(7, input.getEditable().name());

        // Enum specific column(s)
        if (input.getType() == MInputType.ENUM) {
            baseInputStmt.setString(8, StringUtils.join(((MEnumInput) input).getValues(), ","));
        } else {
            baseInputStmt.setNull(8, Types.VARCHAR);
        }

        int baseInputCount = baseInputStmt.executeUpdate();
        if (baseInputCount != 1) {
            throw new SqoopException(CommonRepositoryError.COMMON_0014, Integer.toString(baseInputCount));
        }

        ResultSet rsetInputId = baseInputStmt.getGeneratedKeys();
        if (!rsetInputId.next()) {
            throw new SqoopException(CommonRepositoryError.COMMON_0015);
        }

        long inputId = rsetInputId.getLong(1);
        input.setPersistenceId(inputId);
    }
}

From source file:org.wso2.carbon.identity.application.mgt.dao.impl.ApplicationDAOImpl.java

/**
 * @param applicationId/*from w w w  . jav a  2 s.  co  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:pt.iflow.flows.FlowHolderBean.java

/**
 * Instantiate a new Flow from FlowData. <br>
 * A new flow will be created from a template if necessary.
 * /*from w  w  w .ja va  2  s .co m*/
 * @param userInfo
 * @param fileName
 *            flow file name
 * @return null if OK, otherwise error occurred
 */
public synchronized String deployFlow(UserInfoInterface userInfo, String fileName) {
    String retObj = null;
    int flowId = -1;

    String sLogin = userInfo.getUtilizador();

    Logger.trace(this, "deployFlow", sLogin + " call for file " + fileName);

    Connection db = null;
    Statement st = null;
    PreparedStatement pst = null;
    ResultSet rs = null;
    boolean found = false;
    try {
        db = Utils.getDataSource().getConnection();
        db.setAutoCommit(false);

        pst = db.prepareStatement("select flowid from flow where flowfile=? and organizationid=?");
        pst.setString(1, fileName);
        pst.setString(2, userInfo.getOrganization());
        rs = pst.executeQuery();

        if (rs.next()) {
            found = true;
            flowId = rs.getInt("flowid");
        }
        rs.close();
        pst.close();

        if (!found) {
            Timestamp now = new Timestamp(System.currentTimeMillis());
            pst = db.prepareStatement(DBQueryManager.getQuery("FlowHolder.COPY_FLOW_TEMPLATE"),
                    new String[] { "flowid" });
            pst.setTimestamp(1, now);
            pst.setString(2, userInfo.getOrganization());
            pst.setTimestamp(3, now);
            pst.setString(4, fileName);

            int updated = pst.executeUpdate();

            if (updated == 0)
                throw new Exception("Could not copy flow template");

            rs = pst.getGeneratedKeys();
            if (rs.next()) {
                flowId = rs.getInt(1);
                found = true;
            } else {
                throw new Exception("Could not get new flow id from template copy");
            }
            rs.close();
            pst.close();

        }
        db.commit();
    } catch (Exception e) {
        e.printStackTrace();
        retObj = "O fluxo n&atilde;o foi encontrado: \"" + e.getMessage() + "\"";
        Logger.error(sLogin, this, "deployFlow", retObj);
    } finally {
        DatabaseInterface.closeResources(db, pst, rs);
        db = null;
        pst = null;
        rs = null;
    }

    if (null != retObj)
        return retObj;
    if (!found)
        return "O fluxo n&atilde;o existe ou n&atilde;o est&aacute; dispon&iacute;vel";

    FlowData flow = refreshFlow(userInfo, flowId);
    // if(null == flow) return "Fluxo invalido.";
    boolean online = flow != null && flow.isDeployed();
    flow.setOnline(online);
    notifyDeploy(userInfo, flowId, online);
    try {
        db = Utils.getDataSource().getConnection();
        st = db.createStatement();
        st.executeUpdate("update flow set enabled=" + (online ? 1 : 0) + " where flowid=" + flowId);
    } catch (Exception e) {
        e.printStackTrace();
        retObj = "N&atilde;o foi poss&iacute;vel actualizar o estado do fluxo.";
        Logger.error(sLogin, this, "deployFlow", retObj);
    } finally {
        DatabaseInterface.closeResources(db, st, rs);
        db = null;
        st = null;
        rs = null;
    }

    if (flow == null) {
        retObj = "Invalid flow"; // fd can be null at this point
    } else if (flow.hasError()) {
        retObj = flow.getError();
    }

    Logger.debug(sLogin, this, "deployFlow", fileName + " flow commited");
    return retObj;
}

From source file:org.wso2.carbon.identity.application.mgt.dao.impl.ApplicationDAOImpl.java

/**
 * Stores basic application information and meta-data such as the application name, creator and
 * tenant.//  ww  w . j ava2s.c  om
 *
 * @param serviceProvider
 * @throws IdentityApplicationManagementException
 */
@Override
public int createApplication(ServiceProvider serviceProvider, String tenantDomain)
        throws IdentityApplicationManagementException {

    // get logged-in users tenant identifier.
    int tenantID = -123;

    if (tenantDomain != null) {
        try {
            tenantID = ApplicationManagementServiceComponentHolder.getInstance().getRealmService()
                    .getTenantManager().getTenantId(tenantDomain);
        } catch (UserStoreException e1) {
            throw new IdentityApplicationManagementException("Error while reading application");
        }
    }

    String qualifiedUsername = CarbonContext.getThreadLocalCarbonContext().getUsername();
    if (ApplicationConstants.LOCAL_SP.equals(serviceProvider.getApplicationName())) {
        qualifiedUsername = CarbonConstants.REGISTRY_SYSTEM_USERNAME;
    }
    String username = UserCoreUtil.removeDomainFromName(qualifiedUsername);
    String userStoreDomain = UserCoreUtil.extractDomainFromName(qualifiedUsername);
    String applicationName = serviceProvider.getApplicationName();
    String description = serviceProvider.getDescription();

    if (applicationName == null) {
        // check for required attributes.
        throw new IdentityApplicationManagementException("Application Name is required.");
    }

    if (ApplicationManagementServiceComponent.getFileBasedSPs().containsKey(applicationName)) {
        throw new IdentityApplicationManagementException(
                "Application with the same name laoded from the file system.");
    }

    if (debugMode) {
        log.debug("Creating Application " + applicationName + " for user " + qualifiedUsername);
    }

    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement storeAppPrepStmt = null;
    ResultSet results = null;

    try {
        String dbProductName = connection.getMetaData().getDatabaseProductName();
        storeAppPrepStmt = connection.prepareStatement(ApplicationMgtDBQueries.STORE_BASIC_APPINFO,
                new String[] { DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "ID") });

        // TENANT_ID, APP_NAME, USER_STORE, USERNAME, DESCRIPTION, AUTH_TYPE
        storeAppPrepStmt.setInt(1, tenantID);
        storeAppPrepStmt.setString(2, CharacterEncoder.getSafeText(applicationName));
        storeAppPrepStmt.setString(3, CharacterEncoder.getSafeText(userStoreDomain));
        storeAppPrepStmt.setString(4, CharacterEncoder.getSafeText(username));
        storeAppPrepStmt.setString(5, CharacterEncoder.getSafeText(description));
        // by default authentication type would be default.
        // default authenticator is defined system-wide - in the configuration file.
        storeAppPrepStmt.setString(6, ApplicationConstants.AUTH_TYPE_DEFAULT);
        storeAppPrepStmt.execute();

        results = storeAppPrepStmt.getGeneratedKeys();

        if (!connection.getAutoCommit()) {
            connection.commit();
        }

        int applicationId = 0;
        if (results.next()) {
            applicationId = results.getInt(1);
        }
        // some JDBC Drivers returns this in the result, some don't
        if (applicationId == 0) {
            if (debugMode) {
                log.debug("JDBC Driver did not return the application id, executing Select operation");
            }
            applicationId = getApplicationIDByName(applicationName, tenantID, connection);
        }

        if (debugMode) {
            log.debug("Application Stored successfully with application id " + applicationId);
        }

        return applicationId;

    } catch (SQLException e) {
        try {
            if (connection != null) {
                connection.rollback();
            }
        } catch (SQLException sql) {
            throw new IdentityApplicationManagementException("Error while Creating Application", sql);
        }
        throw new IdentityApplicationManagementException("Error while Creating Application", e);
    } finally {
        IdentityApplicationManagementUtil.closeResultSet(results);
        IdentityApplicationManagementUtil.closeStatement(storeAppPrepStmt);
        IdentityApplicationManagementUtil.closeConnection(connection);
    }
}

From source file:org.wso2.carbon.idp.mgt.dao.IdPManagementDAO.java

/**
 * @param provisioningConnectors//from w ww. jav a 2 s  .  co  m
 * @param dbConnection
 * @param idpId
 * @throws IdentityProviderManagementException
 * @throws SQLException
 */
private void addProvisioningConnectorConfigs(ProvisioningConnectorConfig[] provisioningConnectors,
        Connection dbConnection, int idpId, int tenantId)
        throws IdentityProviderManagementException, SQLException {

    PreparedStatement prepStmt = null;
    ResultSet rs = null;

    try {
        // SP_IDP_ID,SP_IDP_PROV_CONNECTOR_TYPE, SP_IDP_PROV_CONFIG_KEY,
        // SP_IDP_PROV_CONFIG_VALUE, SP_IDP_PROV_CONFIG_IS_SECRET

        // SP_IDP_PROV_CONFIG_PROPERTY
        // TENANT_ID, PROVISIONING_CONFIG_ID, PROPERTY_KEY, PROPERTY_VALUE, PROPERTY_TYPE,
        // IS_SECRET
        String sqlStmt = IdPManagementConstants.SQLQueries.ADD_IDP_PROVISIONING_PROPERTY_SQL;
        prepStmt = dbConnection.prepareStatement(sqlStmt);

        String sqlBaseStmt = IdPManagementConstants.SQLQueries.ADD_IDP_PROVISIONING_CONFIG_SQL;
        String dbProductName = dbConnection.getMetaData().getDatabaseProductName();
        PreparedStatement prepBaseStmt = dbConnection.prepareStatement(sqlBaseStmt,
                new String[] { DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "ID") });

        if (provisioningConnectors != null) {
            for (ProvisioningConnectorConfig connector : provisioningConnectors) {
                Property[] connctorProperties = connector.getProvisioningProperties();

                if (connctorProperties != null) {

                    // SP_IDP_PROVISIONING_CONFIG
                    // TENANT_ID, IDP_ID, PROVISIONING_CONNECTOR_TYPE, IS_ENABLED, IS_DEFAULT
                    prepBaseStmt.setInt(1, tenantId);
                    prepBaseStmt.setInt(2, idpId);
                    prepBaseStmt.setString(3, CharacterEncoder.getSafeText(connector.getName()));

                    if (connector.isEnabled()) {
                        prepBaseStmt.setString(4, "1");
                    } else {
                        prepBaseStmt.setString(4, "0");
                    }

                    if (connector.isBlocking()) {
                        prepBaseStmt.setString(5, "1");
                    } else {
                        prepBaseStmt.setString(5, "0");
                    }

                    prepBaseStmt.executeUpdate();
                    rs = prepBaseStmt.getGeneratedKeys();

                    if (rs.next()) {
                        int provisioningConfigID = rs.getInt(1);

                        if (connctorProperties.length > 0) {
                            for (Property config : connctorProperties) {

                                if (config == null) {
                                    continue;
                                }

                                // SP_IDP_PROV_CONFIG_PROPERTY
                                //TENANT_ID, PROVISIONING_CONFIG_ID, PROPERTY_KEY,
                                // PROPERTY_VALUE, PROPERTY_BLOB_VALUE, PROPERTY_TYPE, IS_SECRET
                                prepStmt.setInt(1, tenantId);
                                prepStmt.setInt(2, provisioningConfigID);
                                prepStmt.setString(3, CharacterEncoder.getSafeText(config.getName()));

                                // TODO : Sect property type accordingly
                                if (IdentityApplicationConstants.ConfigElements.PROPERTY_TYPE_BLOB
                                        .equals(config.getType())) {
                                    prepStmt.setString(4, null);
                                    setBlobValue(config.getValue(), prepStmt, 5);
                                    prepStmt.setString(6, config.getType());
                                } else {
                                    prepStmt.setString(4, CharacterEncoder.getSafeText(config.getValue()));
                                    setBlobValue(null, prepStmt, 5);
                                    prepStmt.setString(6,
                                            IdentityApplicationConstants.ConfigElements.PROPERTY_TYPE_STRING);
                                }

                                if (config.isConfidential()) {
                                    prepStmt.setString(7, "1");
                                } else {
                                    prepStmt.setString(7, "0");
                                }
                                prepStmt.addBatch();

                            }
                        }

                    }

                    // Adding properties for base config
                    prepStmt.executeBatch();

                }
            }
        }
    } catch (IOException e) {
        throw new IdentityProviderManagementException("An error occurred while processing content stream.", e);
    } finally {
        IdentityApplicationManagementUtil.closeResultSet(rs);
        IdentityApplicationManagementUtil.closeStatement(prepStmt);
    }
}

From source file:org.jamwiki.db.CacheQueryHandler.java

/**
 *
 *///from   www.jav  a2  s.c  o m
@Override
public void insertTopicVersions(List<TopicVersion> topicVersions) {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    boolean useBatch = (topicVersions.size() > 1);
    try {
        conn = DatabaseConnection.getConnection();
        if (!this.autoIncrementPrimaryKeys()) {
            stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC_VERSION);
        } else if (useBatch) {
            // generated keys don't work in batch mode
            stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC_VERSION_AUTO_INCREMENT);
        } else {
            stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC_VERSION_AUTO_INCREMENT,
                    Statement.RETURN_GENERATED_KEYS);
        }
        int topicVersionId = -1;
        if (!this.autoIncrementPrimaryKeys() || useBatch) {
            // manually retrieve next topic version id when using batch
            // mode or when the database doesn't support generated keys.
            topicVersionId = DatabaseConnection.executeSequenceQuery(STATEMENT_SELECT_TOPIC_VERSION_SEQUENCE);
        }
        for (TopicVersion topicVersion : topicVersions) {
            if (!this.autoIncrementPrimaryKeys() || useBatch) {
                // FIXME - if two threads update the database simultaneously then
                // it is possible that this code could set the topic version ID
                // to a value that is different from what the database ends up
                // using.
                topicVersion.setTopicVersionId(topicVersionId++);
            }
            StringReader sr = null;
            try {
                int index = 1;
                stmt.setInt(index++, topicVersion.getTopicVersionId());
                if (topicVersion.getEditDate() == null) {
                    topicVersion.setEditDate(new Timestamp(System.currentTimeMillis()));
                }
                stmt.setInt(index++, topicVersion.getTopicId());
                stmt.setString(index++, topicVersion.getEditComment());
                //pass the content into a stream to be passed to Cach
                sr = new StringReader(topicVersion.getVersionContent());
                stmt.setCharacterStream(index++, sr, topicVersion.getVersionContent().length());
                if (topicVersion.getAuthorId() == null) {
                    stmt.setNull(index++, Types.INTEGER);
                } else {
                    stmt.setInt(index++, topicVersion.getAuthorId());
                }
                stmt.setInt(index++, topicVersion.getEditType());
                stmt.setString(index++, topicVersion.getAuthorDisplay());
                stmt.setTimestamp(index++, topicVersion.getEditDate());
                if (topicVersion.getPreviousTopicVersionId() == null) {
                    stmt.setNull(index++, Types.INTEGER);
                } else {
                    stmt.setInt(index++, topicVersion.getPreviousTopicVersionId());
                }
                stmt.setInt(index++, topicVersion.getCharactersChanged());
                stmt.setString(index++, topicVersion.getVersionParamString());
            } finally {
                if (sr != null) {
                    sr.close();
                }
            }
            if (useBatch) {
                stmt.addBatch();
            } else {
                stmt.executeUpdate();
            }
            if (this.autoIncrementPrimaryKeys() && !useBatch) {
                rs = stmt.getGeneratedKeys();
                if (!rs.next()) {
                    throw new SQLException("Unable to determine auto-generated ID for database record");
                }
                topicVersion.setTopicVersionId(rs.getInt(1));
            }
        }
        if (useBatch) {
            stmt.executeBatch();
        }
    } catch (SQLException e) {
        throw new UncategorizedSQLException("insertTopicVersions", null, e);
    } finally {
        DatabaseConnection.closeConnection(conn, stmt, rs);
    }
}

From source file:org.apache.sqoop.repository.derby.DerbyRepositoryHandler.java

/**
 * {@inheritDoc}//w  w w  .ja  va 2 s.  c  o m
 */
@Override
public void createSubmission(MSubmission submission, Connection conn) {
    PreparedStatement stmt = null;
    int result;
    try {
        stmt = conn.prepareStatement(STMT_INSERT_SUBMISSION, Statement.RETURN_GENERATED_KEYS);
        stmt.setLong(1, submission.getJobId());
        stmt.setString(2, submission.getStatus().name());
        stmt.setString(3, submission.getCreationUser());
        stmt.setTimestamp(4, new Timestamp(submission.getCreationDate().getTime()));
        stmt.setString(5, submission.getLastUpdateUser());
        stmt.setTimestamp(6, new Timestamp(submission.getLastUpdateDate().getTime()));
        stmt.setString(7, submission.getExternalId());
        stmt.setString(8, submission.getExternalLink());
        stmt.setString(9, submission.getExceptionInfo());
        stmt.setString(10, submission.getExceptionStackTrace());

        result = stmt.executeUpdate();
        if (result != 1) {
            throw new SqoopException(DerbyRepoError.DERBYREPO_0012, Integer.toString(result));
        }

        ResultSet rsetSubmissionId = stmt.getGeneratedKeys();

        if (!rsetSubmissionId.next()) {
            throw new SqoopException(DerbyRepoError.DERBYREPO_0013);
        }

        long submissionId = rsetSubmissionId.getLong(1);

        if (submission.getCounters() != null) {
            createSubmissionCounters(submissionId, submission.getCounters(), conn);
        }

        // Save created persistence id
        submission.setPersistenceId(submissionId);

    } catch (SQLException ex) {
        logException(ex, submission);
        throw new SqoopException(DerbyRepoError.DERBYREPO_0034, ex);
    } finally {
        closeStatements(stmt);
    }
}

From source file:org.wso2.carbon.registry.core.jdbc.dao.JDBCResourceDAO.java

public void addResourceDO(ResourceDO resourceDO) throws RegistryException {

    JDBCDatabaseTransaction.ManagedRegistryConnection conn = JDBCDatabaseTransaction.getConnection();
    PreparedStatement ps = null;
    PreparedStatement ps1 = null;
    ResultSet result = null;/*from   ww  w . j av  a 2  s . co  m*/

    try {
        String sql = "INSERT INTO REG_RESOURCE (REG_PATH_ID, REG_NAME, REG_MEDIA_TYPE, "
                + "REG_CREATOR, REG_CREATED_TIME, REG_LAST_UPDATOR, "
                + "REG_LAST_UPDATED_TIME, REG_DESCRIPTION, " + "REG_CONTENT_ID, REG_TENANT_ID, REG_UUID) "
                + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        String sql1 = "SELECT MAX(REG_VERSION) FROM REG_RESOURCE";

        String dbProductName = conn.getMetaData().getDatabaseProductName();
        boolean returnsGeneratedKeys = DBUtils.canReturnGeneratedKeys(dbProductName);
        if (returnsGeneratedKeys) {
            ps = conn.prepareStatement(sql,
                    new String[] { DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "REG_VERSION") });
        } else {
            ps = conn.prepareStatement(sql);
        }
        ps.setInt(1, resourceDO.getPathID());
        ps.setString(2, resourceDO.getName());
        ps.setString(3, resourceDO.getMediaType());
        ps.setString(4, resourceDO.getAuthor());
        ps.setTimestamp(5, new Timestamp(resourceDO.getCreatedOn()));
        ps.setString(6, resourceDO.getLastUpdater());
        ps.setTimestamp(7, new Timestamp(resourceDO.getLastUpdatedOn()));
        ps.setString(8, resourceDO.getDescription());
        if (resourceDO.getContentID() > 0) {
            ps.setInt(9, resourceDO.getContentID());
        } else {
            ps.setNull(9, Types.INTEGER);
        }
        ps.setInt(10, CurrentSession.getTenantId());
        ps.setString(11, resourceDO.getUUID());

        if (returnsGeneratedKeys) {
            ps.executeUpdate();
            result = ps.getGeneratedKeys();
        } else {
            synchronized (ADD_RESOURCE_LOCK) {
                ps.executeUpdate();
                ps1 = conn.prepareStatement(sql1);
                result = ps1.executeQuery();
            }
        }
        if (result.next()) {
            long version = result.getLong(1);
            resourceDO.setVersion(version);
        }

        ps.close();

    } catch (SQLException e) {
        String msg = "Failed to add resource to version " + resourceDO.getVersion() + ". " + e.getMessage();
        log.error(msg, e);
        throw new RegistryException(msg, e);
    } finally {
        try {
            try {
                if (result != null) {
                    result.close();
                }
            } finally {
                try {
                    if (ps1 != null) {
                        ps1.close();
                    }
                } finally {
                    if (ps != null) {
                        ps.close();
                    }
                }
            }
        } catch (SQLException ex) {
            String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
            log.error(msg, ex);
        }
    }
}

From source file:capture.MySQLDatabase.java

public void loadInputUrlFromFile(final String inputUrlsFile) {
    Element element;/*from w  w w  . j ava 2  s.c  o  m*/
    Connection con = this.getConnection();
    Statement stmt;
    PreparedStatement ps;
    ResultSet rs;
    String line, url_id, honeypotid = null;
    String operationid = null;
    boolean check = true;
    long count = 0;
    if (inputUrlsFile == null) {
        System.out.println("Error: There is no input-url file!");
        System.exit(1);
    }

    if ((ConfigManager.getInstance().getConfigOption("import_check") != null)
            && (ConfigManager.getInstance().getConfigOption("import_check").toLowerCase().equals("false"))) {
        check = false;
    }

    try {
        stmt = con.createStatement();
        //get honeypot id
        String serverip = ConfigManager.getInstance().getConfigOption("server-listen-address");
        String serverport = ConfigManager.getInstance().getConfigOption("server-listen-port");
        rs = stmt.executeQuery("SELECT honeypot_id FROM honeypot WHERE ipaddress=\'" + serverip + "\'");
        if (rs.next()) {
            honeypotid = rs.getString(1);
        } else {
            //insert a new honeypot 
            stmt.executeUpdate(
                    "INSERT INTO honeypot(ipaddress, port) Values(\'" + serverip + "\', " + serverport + ")",
                    Statement.RETURN_GENERATED_KEYS);
            rs = stmt.getGeneratedKeys();
            if (rs.next()) {
                honeypotid = rs.getString(1);
            } else {
                System.out.println("System can't find any honeypot ip=" + serverip);
                System.exit(0);
            }
        }
        setSystemStatus(true);
        //open url file
        BufferedReader in = new BufferedReader(
                new InputStreamReader(new FileInputStream(inputUrlsFile), "UTF-8"));

        //add new operation
        stmt.executeUpdate("INSERT INTO operation(description, honeypot_id) Values (\'" + inputUrlsFile
                + "\', \'" + honeypotid + "\')", Statement.RETURN_GENERATED_KEYS);
        rs = stmt.getGeneratedKeys();
        if (rs.next()) {
            operationid = rs.getString(1);
            setCurrentOperation(operationid);
        }
        System.out.println("The system is going to inspect urls in the new operation: " + operationid);

        //update visit start time for operation
        SimpleDateFormat sf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss.S");
        String date = sf.format(new Date());
        stmt.executeUpdate("UPDATE operation SET visitstarttime=\'" + date + "\' " + "WHERE operation_id="
                + operationid + " AND visitstarttime IS NULL");

        System.out.println("Please wait for inserting urls into database...");
        if (!check) //NO checking existance of url in database
        {
            while ((line = in.readLine()) != null) {
                if ((line.length() > 0)) {
                    //line = line.trim().toLowerCase();
                    line = line.trim();
                    if (!line.startsWith("#")) {
                        ps = con.prepareStatement("INSERT INTO url(url) Values (?)",
                                Statement.RETURN_GENERATED_KEYS);
                        ps.setString(1, line);
                        ps.executeUpdate();
                        rs = ps.getGeneratedKeys();
                        rs.next();
                        url_id = rs.getString(1);
                        stmt.executeUpdate("INSERT INTO url_operation(url_id, operation_id) Values (" + url_id
                                + ", " + operationid + ")");

                        element = new Element();
                        element.name = "url";
                        element.attributes.put("add", "");
                        element.attributes.put("id", url_id);
                        element.attributes.put("url", line);
                        EventsController.getInstance().notifyEventObservers(element);
                        count++;
                    }
                }
            }
        } else //checking existance of url in database. Inserting only if no existance
        {
            while ((line = in.readLine()) != null) {
                if ((line.length() > 0)) {
                    //line = line.trim().toLowerCase();
                    line = line.trim();
                    if (!line.startsWith("#")) {
                        ps = con.prepareStatement("SELECT url_id FROM url WHERE url.url = ?");
                        ps.setString(1, line);
                        rs = ps.executeQuery();
                        if (!rs.next()) {
                            ps = con.prepareStatement("INSERT INTO url(url) Values (?)",
                                    Statement.RETURN_GENERATED_KEYS);
                            ps.setString(1, line);
                            ps.executeUpdate();
                            rs = ps.getGeneratedKeys();
                            rs.next();
                            count++;
                        }
                        //check URL id and operation id: not exist
                        url_id = rs.getString(1);
                        ps = con.prepareStatement(
                                "SELECT url_id, operation_id FROM url_operation WHERE url_id = ? AND operation_id= ?");
                        ps.setLong(1, Long.parseLong(url_id));
                        ps.setLong(2, Long.parseLong(operationid));
                        rs = ps.executeQuery();
                        if (!rs.next()) {
                            stmt.executeUpdate("INSERT INTO url_operation(url_id, operation_id) Values ("
                                    + url_id + ", " + operationid + ")");
                            element = new Element();
                            element.name = "url";
                            element.attributes.put("add", "");
                            element.attributes.put("id", url_id);
                            element.attributes.put("url", line);
                            EventsController.getInstance().notifyEventObservers(element);
                        }
                    }
                }
            }
        }
        con.close();
        System.out.println("******** IMPORT URLs INTO DATABASE: " + count
                + " URLs have been inserted into database! ********");
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:com.wso2telco.dep.ratecardservice.dao.TariffDAO.java

public TariffDTO addTariff(TariffDTO tariff) throws BusinessException {

    Connection con = null;// ww  w .j av a  2  s. co  m
    PreparedStatement ps = null;
    ResultSet rs = null;
    Integer tariffId = 0;

    try {

        con = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_RATE_DB);
        if (con == null) {

            log.error("unable to open " + DataSourceNames.WSO2TELCO_RATE_DB + " database connection");
            throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
        }

        StringBuilder query = new StringBuilder("insert into ");
        query.append(DatabaseTables.TARIFF.getTObject());
        query.append(
                " (tariffname, tariffdesc, tariffdefaultval, tariffmaxcount, tariffexcessrate, tariffdefrate, tariffspcommission, tariffadscommission, tariffopcocommission, tariffsurchargeval, tariffsurchargeAds, tariffsurchargeOpco, createdby)");
        query.append(" values");
        query.append(" (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        ps = con.prepareStatement(query.toString(), Statement.RETURN_GENERATED_KEYS);

        log.debug("sql query in addTariff : " + ps);

        ps.setString(1, tariff.getTariffName());
        ps.setString(2, tariff.getTariffDescription());

        Double tariffDefaultVal = tariff.getTariffDefaultVal();
        if (tariffDefaultVal != null) {
            ps.setDouble(3, tariffDefaultVal);
        } else {
            ps.setNull(3, Types.DOUBLE);
        }

        Integer tariffMaxCount = tariff.getTariffMaxCount();
        if (tariffMaxCount != null) {
            ps.setInt(4, tariffMaxCount);
        } else {
            ps.setNull(4, Types.INTEGER);
        }

        Double tariffExcessRate = tariff.getTariffExcessRate();
        if (tariffExcessRate != null) {
            ps.setDouble(5, tariffExcessRate);
        } else {
            ps.setNull(5, Types.DOUBLE);
        }

        Double tariffDefRate = tariff.getTariffDefRate();
        if (tariffDefRate != null) {
            ps.setDouble(6, tariffDefRate);
        } else {
            ps.setNull(6, Types.DOUBLE);
        }

        Double tariffSPCommission = tariff.getTariffSPCommission();
        if (tariffSPCommission != null) {
            ps.setDouble(7, tariffSPCommission);
        } else {
            ps.setNull(7, Types.DOUBLE);
        }

        Double tariffAdsCommission = tariff.getTariffAdsCommission();
        if (tariffAdsCommission != null) {
            ps.setDouble(8, tariffAdsCommission);
        } else {
            ps.setNull(8, Types.DOUBLE);
        }

        Double tariffOpcoCommission = tariff.getTariffOpcoCommission();
        if (tariffOpcoCommission != null) {
            ps.setDouble(9, tariffOpcoCommission);
        } else {
            ps.setNull(9, Types.DOUBLE);
        }

        Double tariffSurChargeval = tariff.getTariffSurChargeval();
        if (tariffSurChargeval != null) {
            ps.setDouble(10, tariffSurChargeval);
        } else {
            ps.setNull(10, Types.DOUBLE);
        }

        Double tariffSurChargeAds = tariff.getTariffSurChargeAds();
        if (tariffSurChargeAds != null) {
            ps.setDouble(11, tariffSurChargeAds);
        } else {
            ps.setNull(11, Types.DOUBLE);
        }

        Double tariffSurChargeOpco = tariff.getTariffSurChargeOpco();
        if (tariffSurChargeOpco != null) {
            ps.setDouble(12, tariffSurChargeOpco);
        } else {
            ps.setNull(12, Types.DOUBLE);
        }

        ps.setString(13, tariff.getCreatedBy());

        ps.executeUpdate();

        rs = ps.getGeneratedKeys();

        while (rs.next()) {

            tariffId = rs.getInt(1);
        }

        tariff.setTariffId(tariffId);
    } catch (SQLException e) {

        log.error("database operation error in addTariff : ", e);
        throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
    } catch (Exception e) {

        log.error("error in addTariff : ", e);
        throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
    } finally {

        DbUtils.closeAllConnections(ps, con, rs);
    }

    return tariff;
}