Java Code Examples for org.wso2.carbon.identity.core.util.IdentityDatabaseUtil#getDBConnection()

/**
 * Clear all the parameters that stored under workflow Id
 *
 * @param workflowId WorkflowId
 * @throws InternalWorkflowException
 */
public void removeWorkflowParams(String workflowId) throws InternalWorkflowException {

    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement prepStmt = null;
    String query = SQLConstants.DELETE_WORKFLOW_PARAMS_QUERY;
    try {
        prepStmt = connection.prepareStatement(query);
        prepStmt.setString(1, workflowId);
        prepStmt.executeUpdate();
        IdentityDatabaseUtil.commitTransaction(connection);
    } catch (SQLException e) {
        IdentityDatabaseUtil.rollbackTransaction(connection);
        throw new InternalWorkflowException(errorMessage, e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, null, prepStmt);
    }
}
 

/**
 * Method to return the user Ids of the users in a given user store from the database.
 *
 * @param userDomain name of the user Store domain
 * @param tenantId   id of the tenant domain
 * @return the list of user Ids of users stored in the given user store
 * @throws UserSessionException if an error occurs when retrieving the user id list from the database
 */
public List<String> getUserIdsOfUserStore(String userDomain, int tenantId) throws UserSessionException {

    List<String> userIds = new ArrayList<>();
    try (Connection connection = IdentityDatabaseUtil.getDBConnection(false)) {
        try (PreparedStatement preparedStatement = connection
                        .prepareStatement(SQLQueries.SQL_SELECT_USER_IDS_OF_USER_STORE)) {
            preparedStatement.setString(1, userDomain.toUpperCase());
            preparedStatement.setInt(2, tenantId);
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                while (resultSet.next()) {
                    userIds.add(resultSet.getString(1));
                }
            }
        } catch (SQLException e1) {
            throw new UserSessionException("Error while retrieving user Ids stored in the user domain: " +
                    userDomain + ", Tenant Id: " + tenantId, e1);
        }

    } catch (SQLException e) {
        throw new UserSessionException("Error while retrieving user Ids stored in the user domain: " + userDomain
                + ", Tenant Id: " + tenantId, e);
    }
    return userIds;
}
 

/**
 *
 * @param associationName
 * @param workflowId
 * @param eventId
 * @param condition
 * @throws InternalWorkflowException
 */
public void addAssociation(String associationName, String workflowId, String eventId, String condition)
        throws InternalWorkflowException {

    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement prepStmt = null;

    String query = SQLConstants.ASSOCIATE_WF_TO_EVENT;
    try {
        prepStmt = connection.prepareStatement(query);
        prepStmt.setString(1, eventId);
        prepStmt.setString(2, associationName);
        prepStmt.setString(3, condition);
        prepStmt.setString(4, workflowId);
        prepStmt.executeUpdate();
        IdentityDatabaseUtil.commitTransaction(connection);
    } catch (SQLException e) {
        IdentityDatabaseUtil.rollbackTransaction(connection);
        throw new InternalWorkflowException(errorMessage, e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, null, prepStmt);
    }
}
 

/**
 * Remove Workflow from the DB
 *
 * @param workflowId workflow Id
 * @throws InternalWorkflowException
 */
public void removeWorkflow(String workflowId) throws InternalWorkflowException {

    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement prepStmt = null;
    String query = SQLConstants.DELETE_WORKFLOW_QUERY;
    try {
        prepStmt = connection.prepareStatement(query);
        prepStmt.setString(1, workflowId);
        prepStmt.executeUpdate();
        connection.commit();
    } catch (SQLException e) {
        throw new InternalWorkflowException(errorMessage, e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, null, prepStmt);
    }
}
 

/**
 * Update state of workflow of a request
 *
 * @param requestId requestId to update relationships of.
 * @throws InternalWorkflowException
 */
public void updateStatusOfRelationshipsOfPendingRequest(String requestId, String status) throws
                                                                                         InternalWorkflowException {

    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement prepStmt = null;
    String query = SQLConstants.UPDATE_STATUS_OF_RELATIONSHIPS_OF_REQUEST;
    try {
        Timestamp updatedDateStamp = new Timestamp(System.currentTimeMillis());
        prepStmt = connection.prepareStatement(query);
        prepStmt.setString(1, status);
        prepStmt.setTimestamp(2, updatedDateStamp);
        prepStmt.setString(3, requestId);
        prepStmt.setString(4, WFConstant.HT_STATE_PENDING);
        prepStmt.execute();
        IdentityDatabaseUtil.commitTransaction(connection);
    } catch (SQLException e) {
        IdentityDatabaseUtil.rollbackTransaction(connection);
        throw new InternalWorkflowException("Error when executing the sql query:" + query, e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, null, prepStmt);
    }
}
 

/**
 * invalidate recovery data. it means delete user recovery data entry from store
 *
 * @param recoveryDataDO
 * @throws IdentityException
 */
@Override
public void invalidate(UserRecoveryDataDO recoveryDataDO) throws IdentityException {
    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement prepStmt = null;
    try {
        prepStmt = connection.prepareStatement(SQLQuery.INVALIDATE_METADATA); // TODO Delete entry
        prepStmt.setString(1, recoveryDataDO.getUserName());
        prepStmt.setInt(2, recoveryDataDO.getTenantId());
        prepStmt.setString(3, recoveryDataDO.getCode());
        prepStmt.execute();
        connection.commit();
    } catch (SQLException e) {
        throw IdentityException.error("Error while storing user identity data", e);
    } finally {
        IdentityDatabaseUtil.closeStatement(prepStmt);
        IdentityDatabaseUtil.closeConnection(connection);
    }
}
 

/**
 * Remove the entry from the database.
 *
 * @param opdo
 */
public void delete(OpenIDUserRPDO opdo, int tenantId) {

    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement prepStmt = null;

    try {

        if (isUserRPExist(connection, opdo, tenantId)) {
            prepStmt = connection.prepareStatement(OpenIDSQLQueries.REMOVE_USER_RP);
            prepStmt.setString(1, opdo.getUserName());
            prepStmt.setInt(2, tenantId);
            prepStmt.setString(3, opdo.getRpUrl());
            prepStmt.execute();
            connection.commit();
        }

    } catch (SQLException e) {
        log.error("Failed to remove RP: " + opdo.getRpUrl() + " of user: " + opdo.getUserName(), e);
    } finally {
        IdentityDatabaseUtil.closeStatement(prepStmt);
        IdentityDatabaseUtil.closeConnection(connection);
    }
}
 

/**
 * Method to check whether the user id is available in the IDN_AUTH_USER table.
 *
 * @param userId    Id of the user
 * @return the boolean decision
 * @throws UserSessionException if an error occurs when retrieving the mapping from the database
 */
public boolean isExistingUser(String userId) throws UserSessionException {

    Boolean isExisting = false;
    try (Connection connection = IdentityDatabaseUtil.getDBConnection(false)) {
        try (PreparedStatement preparedStatement = connection
                .prepareStatement(SQLQueries.SQL_SELECT_INFO_OF_USER_ID)) {
            preparedStatement.setString(1, userId);
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                if (resultSet.next()) {
                    isExisting = true;
                }
            }
        }
    } catch (SQLException e) {
        throw new UserSessionException("Error while retrieving information of user id: " + userId, e);
    }
    return isExisting;
}
 

public void renameUserStoreDomainInAuthorizationCodeTable(int tenantId, String currentUserStoreDomain, String
        newUserStoreDomain) throws IdentityOAuth2Exception {

    //we do not support access token partitioning here
    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement ps = null;
    try {
        String sqlQuery = SQLQueries.RENAME_USER_STORE_IN_AUTHORIZATION_CODES_TABLE;
        ps = connection.prepareStatement(sqlQuery);
        ps.setString(1, newUserStoreDomain.toUpperCase());
        ps.setInt(2, tenantId);
        ps.setString(3, currentUserStoreDomain.toUpperCase());
        int count = ps.executeUpdate();
        if (log.isDebugEnabled()) {
            log.debug("Number of rows being updated : " + count);
        }
        connection.commit();
    } catch (SQLException e) {
        IdentityDatabaseUtil.rollBack(connection);
        throw new IdentityOAuth2Exception("Error occurred while renaming user store : " + currentUserStoreDomain +
                "in tenant :" + tenantId, e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, null, ps);
    }
}
 

/**
 * Delete the federated association entries for the given user.
 *
 * @param tenantId        Tenant identifier.
 * @param userStoreDomain User store domain name.
 * @param username        Username without user store domain.
 * @throws UserProfileException {@link UserProfileException}.
 */
public void deleteFederatedAssociation(int tenantId, String userStoreDomain, String username)
        throws UserProfileException {

    try (Connection connection = IdentityDatabaseUtil.getDBConnection()) {
        try (PreparedStatement prepStmt = connection.prepareStatement(
                Constants.SQLQueries.DELETE_ALL_ASSOCIATIONS_FOR_USER)) {
            prepStmt.setInt(1, tenantId);
            prepStmt.setString(2, userStoreDomain);
            prepStmt.setString(3, username);
            prepStmt.executeUpdate();
            IdentityDatabaseUtil.commitTransaction(connection);
        } catch (SQLException e1) {
            IdentityDatabaseUtil.rollbackTransaction(connection);
            throw new UserProfileException("Error occurred while removing federated association entries of user: " +
                    username + ", of user store domain: " + userStoreDomain + ", in tenant: " + tenantId, e1);
        }
    } catch (SQLException e) {
        throw new UserProfileException("Error occurred while handling the database connection", e);
    }
}
 

/**
 * Get requestId of a relationship.
 *
 * @param relationshipId
 * @return
 * @throws InternalWorkflowException
 */
public String getStatusOfRelationship(String relationshipId) throws InternalWorkflowException {

    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement prepStmt = null;
    String query = SQLConstants.GET_STATUS_OF_RELATIONSHIP;
    ResultSet resultSet = null;
    try {
        prepStmt = connection.prepareStatement(query);
        prepStmt.setString(1, relationshipId);
        resultSet = prepStmt.executeQuery();
        if (resultSet.next()) {
            return resultSet.getString(SQLConstants.REQUEST_STATUS_COLUMN);
        }
        connection.commit();
    } catch (SQLException e) {
        throw new InternalWorkflowException("Error when executing the sql query:" + query, e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, resultSet, prepStmt);
    }
    return "";
}
 

private boolean isExistingUserDataValue(String userName, int tenantId, String key) throws SQLException {

        Connection connection = IdentityDatabaseUtil.getDBConnection();
        PreparedStatement prepStmt = null;
        ResultSet results;
        boolean isUsernameCaseSensitive = IdentityUtil.isUserStoreInUsernameCaseSensitive(userName, tenantId);
        try {
            String query;
            if (isUsernameCaseSensitive) {
                query = SQLQuery.CHECK_EXIST_USER_DATA;
            } else {
                query = SQLQuery.CHECK_EXIST_USER_DATA_CASE_INSENSITIVE;
            }
            prepStmt = connection.prepareStatement(query);
            prepStmt.setInt(1, tenantId);
            prepStmt.setString(2, userName);
            prepStmt.setString(3, key);
            results = prepStmt.executeQuery();
            if (results.next()) {
                return true;
            }
            connection.commit();
        } finally {
            IdentityDatabaseUtil.closeStatement(prepStmt);
            IdentityDatabaseUtil.closeConnection(connection);
        }
        return false;
    }
 

/**
 * Returns the default user profile corresponding to the given user name and
 * the RP URL.
 *
 * @param userName Unique user name
 * @param rpUrl    Relying party URL
 * @return Default user profile
 */
public String getOpenIDDefaultUserProfile(String userName, String rpUrl, int tenantId) {

    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement prepStmt = null;

    OpenIDUserRPDO rpdo = new OpenIDUserRPDO();
    rpdo.setUserName(userName);
    rpdo.setRpUrl(rpUrl);

    try {

        if (isUserRPExist(connection, rpdo, tenantId)) {
            prepStmt = connection.prepareStatement(OpenIDSQLQueries.LOAD_USER_RP_DEFAULT_PROFILE);
            prepStmt.setString(1, userName);
            prepStmt.setInt(2, tenantId);
            prepStmt.setString(3, rpUrl);
            return prepStmt.executeQuery().getString(7);
        } else {
            if(log.isDebugEnabled()) {
                log.debug("RP: " + rpUrl + " of user: " + userName + " not found in the database");
            }
        }
    } catch (SQLException e) {
        log.error("Failed to load RP: " + rpUrl + " for user: " + userName, e);
    } finally {
        IdentityDatabaseUtil.closeStatement(prepStmt);
        IdentityDatabaseUtil.closeConnection(connection);
    }
    return null;
}
 

/**
 * Updates the Relying Party if exists, if not, then creates a new Relying
 * Party
 *
 * @param rpdo
 */
public void update(OpenIDUserRPDO rpdo, int tenantId) {

    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement prepStmt = null;

    try {
        if (isUserRPExist(connection, rpdo, tenantId)) {
            // we should update the entry
            prepStmt = connection.prepareStatement(OpenIDSQLQueries.UPDATE_USER_RP);

            prepStmt.setString(1, rpdo.getUserName());
            prepStmt.setInt(2, tenantId);
            prepStmt.setString(3, rpdo.getRpUrl());
            prepStmt.setString(4, rpdo.isTrustedAlways() ? "TRUE" : "FALSE");
            prepStmt.setDate(5, new java.sql.Date(rpdo.getLastVisit().getTime()));
            prepStmt.setInt(6, rpdo.getVisitCount() + 1);
            prepStmt.setString(7, rpdo.getDefaultProfileName());
            prepStmt.execute();
            IdentityDatabaseUtil.commitTransaction(connection);
        } else {
            // we should create the entry
            if(log.isDebugEnabled()) {
                log.debug("Failed to update RP: " + rpdo.getRpUrl() + " for user: " + rpdo.getUserName() + ". " +
                        "Entry does not exist in the database.");
            }
        }
    } catch (SQLException e) {
        IdentityDatabaseUtil.rollbackTransaction(connection);
        log.error("Failed to update RP:  " + rpdo.getRpUrl() + " for user: " +
                rpdo.getUserName() + " Error while accessing the database", e);
    } finally {
        IdentityDatabaseUtil.closeStatement(prepStmt);
        IdentityDatabaseUtil.closeConnection(connection);
    }
}
 

public String getAuthzCodeByCodeId(String codeId) throws IdentityOAuth2Exception {

        Connection connection = IdentityDatabaseUtil.getDBConnection();

        PreparedStatement prepStmt = null;
        ResultSet resultSet = null;
        try {
            String sql = SQLQueries.RETRIEVE_AUTHZ_CODE_BY_CODE_ID;

            prepStmt = connection.prepareStatement(sql);
            prepStmt.setString(1, codeId);
            resultSet = prepStmt.executeQuery();

            if (resultSet.next()) {
                return resultSet.getString("AUTHORIZATION_CODE");
            }
            connection.commit();
            return null;

        } catch (SQLException e) {
            String errorMsg = "Error occurred while retrieving 'Authorization Code' for " +
                    "authorization code : " + codeId;
            throw new IdentityOAuth2Exception(errorMsg, e);
        } finally {
            IdentityDatabaseUtil.closeAllConnections(connection, resultSet, prepStmt);
        }

    }
 

/**
 * Get a Workflow object for given workflowid
 *
 * @param workflowId Workflow unique id
 * @return Workflow object
 * @throws InternalWorkflowException
 */
public Workflow getWorkflow(String workflowId) throws InternalWorkflowException {

    Connection connection = IdentityDatabaseUtil.getDBConnection(false);
    PreparedStatement prepStmt = null;
    ResultSet rs = null;
    String query = SQLConstants.GET_WORKFLOW;

    Workflow workflow = null ;

    try {
        prepStmt = connection.prepareStatement(query);
        prepStmt.setString(1, workflowId);
        rs = prepStmt.executeQuery();
        while (rs.next()) {
            String workflowName = rs.getString(SQLConstants.WF_NAME_COLUMN);
            String description = rs.getString(SQLConstants.DESCRIPTION_COLUMN);
            String templateId = rs.getString(SQLConstants.TEMPLATE_ID_COLUMN);
            String implId = rs.getString(SQLConstants.TEMPLATE_IMPL_ID_COLUMN);
            workflow = new Workflow();
            workflow.setWorkflowId(workflowId);
            workflow.setWorkflowName(workflowName);
            workflow.setWorkflowDescription(description);
            workflow.setTemplateId(templateId);
            workflow.setWorkflowImplId(implId);

            break ;
        }
    } catch (SQLException e) {
        throw new InternalWorkflowException(errorMessage, e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, null, prepStmt);
    }
    return workflow;
}
 

private boolean isExistingUserDataValue(String userName, int tenantId, String key) throws SQLException {

        Connection connection = IdentityDatabaseUtil.getDBConnection(false);
        PreparedStatement prepStmt = null;
        ResultSet results;
        boolean isUsernameCaseSensitive = IdentityUtil.isUserStoreInUsernameCaseSensitive(userName, tenantId);
        try {
            String query;
            if (isUsernameCaseSensitive) {
                query = SQLQuery.CHECK_EXIST_USER_DATA;
            } else {
                query = SQLQuery.CHECK_EXIST_USER_DATA_CASE_INSENSITIVE;
            }
            prepStmt = connection.prepareStatement(query);
            prepStmt.setInt(1, tenantId);
            prepStmt.setString(2, userName);
            prepStmt.setString(3, key);
            results = prepStmt.executeQuery();
            if (results.next()) {
                return true;
            }
        } finally {
            IdentityDatabaseUtil.closeStatement(prepStmt);
            IdentityDatabaseUtil.closeConnection(connection);
        }
        return false;
    }
 

public OAuthAppDO getAppInformationByAppName(String appName) throws InvalidOAuthClientException, IdentityOAuth2Exception {
    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement prepStmt = null;
    ResultSet rSet = null;
    OAuthAppDO oauthApp = null;

    try {
        int tenantID = CarbonContext.getThreadLocalCarbonContext().getTenantId();
        prepStmt = connection.prepareStatement(SQLQueries.OAuthAppDAOSQLQueries.GET_APP_INFO_BY_APP_NAME);
        prepStmt.setString(1, appName);
        prepStmt.setInt(2, tenantID);

        rSet = prepStmt.executeQuery();
        List<OAuthAppDO> oauthApps = new ArrayList<>();
        oauthApp = new OAuthAppDO();
        oauthApp.setApplicationName(appName);
        AuthenticatedUser user = new AuthenticatedUser();
        user.setTenantDomain(IdentityTenantUtil.getTenantDomain(tenantID));
        /**
         * We need to determine whether the result set has more than 1 row. Meaning, we found an application for
         * the given consumer key. There can be situations where a user passed a key which doesn't yet have an
         * associated application. We need to barf with a meaningful error message for this case
         */
        boolean rSetHasRows = false;
        while (rSet.next()) {
            // There is at least one application associated with a given key
            rSetHasRows = true;
            if (rSet.getString(4) != null && rSet.getString(4).length() > 0) {
                oauthApp.setOauthConsumerSecret(persistenceProcessor.getPreprocessedClientSecret(rSet.getString(1)));
                user.setUserName(rSet.getString(2));
                user.setUserStoreDomain(rSet.getString(3));
                oauthApp.setUser(user);
                oauthApp.setOauthConsumerKey(persistenceProcessor.getPreprocessedClientId(rSet.getString(4)));
                oauthApp.setOauthVersion(rSet.getString(5));
                oauthApp.setCallbackUrl(rSet.getString(6));
                oauthApp.setGrantTypes(rSet.getString(7));
                oauthApp.setId(rSet.getInt(8));
                oauthApps.add(oauthApp);
            }
        }
        if (!rSetHasRows) {
            /**
             * We come here because user submitted a key that doesn't have any associated application with it.
             * We're throwing an error here because we cannot continue without this info. Otherwise it'll throw
             * a null values not supported error when it tries to cache this info
             */
            String message = "Cannot find an application associated with the given consumer key : " + appName;
            if(log.isDebugEnabled()) {
                log.debug(message);
            }
            throw new InvalidOAuthClientException(message);
        }
        connection.commit();
    } catch (SQLException e) {
        throw new IdentityOAuth2Exception("Error while retrieving the app information", e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, rSet, prepStmt);
    }
    return oauthApp;
}
 

/**
 * This method will be heavily used by the Authentication Framework. The framework would ask for
 * application data with the given client key and secrete
 *
 * @param clientId
 * @param type
 * @param tenantDomain
 * @return
 * @throws IdentityApplicationManagementException
 */
public ServiceProvider getApplicationData(String clientId, String type, String tenantDomain)
        throws IdentityApplicationManagementException {

    if (log.isDebugEnabled()) {
        log.debug("Loading Application Data of Client " + clientId);
    }

    int tenantID = -123;

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

    String applicationName = null;

    // Reading application name from the database
    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement storeAppPrepStmt = null;
    ResultSet appNameResult = null;
    try {
        storeAppPrepStmt = connection
                .prepareStatement(ApplicationMgtDBQueries.LOAD_APPLICATION_NAME_BY_CLIENT_ID_AND_TYPE);
        storeAppPrepStmt.setString(1, clientId);
        storeAppPrepStmt.setString(2, type);
        storeAppPrepStmt.setInt(3, tenantID);
        appNameResult = storeAppPrepStmt.executeQuery();
        connection.commit();
        if (appNameResult.next()) {
            applicationName = appNameResult.getString(1);
        }

    } catch (SQLException e) {
        throw new IdentityApplicationManagementException("Error while reading application", e);
    } finally {
        IdentityApplicationManagementUtil.closeResultSet(appNameResult);
        IdentityApplicationManagementUtil.closeStatement(storeAppPrepStmt);
        IdentityApplicationManagementUtil.closeConnection(connection);
    }

    return getApplication(applicationName, tenantDomain);
}
 

/**
 * Get a database connection instance from the Identity Persistence Manager
 * @return Database Connection
 */
public static Connection getDBConnection(){
    return IdentityDatabaseUtil.getDBConnection();
}