Examples with MySQL org.wso2.carbon.idp.mgt.util.IdPManagementConstants.MySQL used on opensource projects

Example 21 with MySQL

use of org.wso2.carbon.idp.mgt.util.IdPManagementConstants.MySQL in project carbon-identity-framework by wso2.

the class WorkflowRequestDAO method getRequestsFilteredByTime.

/**
 * Get requests created/updated in given time period
 *
 * @param beginTime    lower limit of date range to filter
 * @param endTime      upper limit of date range to filter
 * @param timeCategory filter by created time or last updated time ?
 * @param tenant       tenant id of currently logged in user
 * @return
 * @throws InternalWorkflowException
 */
public org.wso2.carbon.identity.workflow.mgt.bean.WorkflowRequest[] getRequestsFilteredByTime(Timestamp beginTime, Timestamp endTime, String timeCategory, int tenant, String status) throws InternalWorkflowException {
    Connection connection = IdentityDatabaseUtil.getDBConnection(false);
    PreparedStatement prepStmt = null;
    String query = "";
    ResultSet resultSet = null;
    try {
        String driverName = connection.getMetaData().getDriverName();
        if (driverName.contains("MySQL") || driverName.contains("MariaDB") || driverName.contains("H2")) {
            if (UPDATED_AT_FILTER.equals(timeCategory)) {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_UPDATED_TIME_MYSQL;
                } else {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_UPDATED_TIME_AND_STATUS_MYSQL;
                }
            } else {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_CREATED_TIME_MYSQL;
                } else {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_CREATED_TIME_AND_STATUS_MYSQL;
                }
            }
        } else if (connection.getMetaData().getDatabaseProductName().contains("DB2")) {
            if (UPDATED_AT_FILTER.equals(timeCategory)) {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_UPDATED_TIME_DB2SQL;
                } else {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_UPDATED_TIME_AND_STATUS_DB2SQL;
                }
            } else {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_CREATED_TIME_DB2SQL;
                } else {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_CREATED_TIME_AND_STATUS_DB2SQL;
                }
            }
        } else if (driverName.contains("MS SQL")) {
            if (UPDATED_AT_FILTER.equals(timeCategory)) {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_UPDATED_TIME_MSSQL;
                } else {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_UPDATED_TIME_AND_STATUS_MSSQL;
                }
            } else {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_CREATED_TIME_MSSQL;
                } else {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_CREATED_TIME_AND_STATUS_MSSQL;
                }
            }
        } else if (driverName.contains("Microsoft") || driverName.contains("microsoft")) {
            if (UPDATED_AT_FILTER.equals(timeCategory)) {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_UPDATED_TIME_MSSQL;
                } else {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_UPDATED_TIME_AND_STATUS_MSSQL;
                }
            } else {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_CREATED_TIME_MSSQL;
                } else {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_CREATED_TIME_AND_STATUS_MSSQL;
                }
            }
        } else if (driverName.contains("PostgreSQL")) {
            if (UPDATED_AT_FILTER.equals(timeCategory)) {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_UPDATED_TIME_POSTGRESQL;
                } else {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_UPDATED_TIME_AND_STATUS_POSTGRESQL;
                }
            } else {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_CREATED_TIME_POSTGRESQL;
                } else {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_CREATED_TIME_AND_STATUS_POSTGRESQL;
                }
            }
        } else if (driverName.contains("Informix")) {
            // Driver name = "IBM Informix JDBC Driver for IBM Informix Dynamic Server"
            if (UPDATED_AT_FILTER.equals(timeCategory)) {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_UPDATED_TIME_INFORMIX;
                } else {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_UPDATED_TIME_AND_STATUS_INFORMIX;
                }
            } else {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_CREATED_TIME_INFORMIX;
                } else {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_CREATED_TIME_AND_STATUS_INFORMIX;
                }
            }
        } else {
            if (timeCategory.equals(UPDATED_AT_FILTER)) {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_UPDATED_TIME_ORACLE;
                } else {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_UPDATED_TIME_AND_STATUS_ORACLE;
                }
            } else {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_CREATED_TIME_ORACLE;
                } else {
                    query = SQLConstants.GET_REQUESTS_FILTER_FROM_CREATED_TIME_AND_STATUS_ORACLE;
                }
            }
        }
        prepStmt = connection.prepareStatement(query);
        prepStmt.setTimestamp(1, beginTime);
        prepStmt.setTimestamp(2, endTime);
        prepStmt.setInt(3, tenant);
        if (!status.equals(ALL_TASKS_FILTER) && !status.equals("")) {
            prepStmt.setString(4, status);
        }
        resultSet = prepStmt.executeQuery();
        ArrayList<org.wso2.carbon.identity.workflow.mgt.bean.WorkflowRequest> requestDTOs = new ArrayList<>();
        while (resultSet.next()) {
            org.wso2.carbon.identity.workflow.mgt.bean.WorkflowRequest requestDTO = new org.wso2.carbon.identity.workflow.mgt.bean.WorkflowRequest();
            requestDTO.setRequestId(resultSet.getString(SQLConstants.REQUEST_UUID_COLUMN));
            requestDTO.setEventType(resultSet.getString(SQLConstants.REQUEST_OPERATION_TYPE_COLUMN));
            requestDTO.setCreatedAt(resultSet.getTimestamp(SQLConstants.REQUEST_CREATED_AT_COLUMN).toString());
            requestDTO.setUpdatedAt(resultSet.getTimestamp(SQLConstants.REQUEST_UPDATED_AT_COLUMN).toString());
            requestDTO.setStatus(resultSet.getString(SQLConstants.REQUEST_STATUS_COLUMN));
            requestDTO.setRequestParams((deserializeWorkflowRequest(resultSet.getBytes(SQLConstants.REQUEST_COLUMN))).getRequestParameterAsString());
            requestDTO.setCreatedBy(resultSet.getString(SQLConstants.CREATED_BY_COLUMN));
            requestDTOs.add(requestDTO);
        }
        org.wso2.carbon.identity.workflow.mgt.bean.WorkflowRequest[] requestArray = new org.wso2.carbon.identity.workflow.mgt.bean.WorkflowRequest[requestDTOs.size()];
        for (int i = 0; i < requestDTOs.size(); i++) {
            requestArray[i] = requestDTOs.get(i);
        }
        return requestArray;
    } catch (SQLException e) {
        throw new InternalWorkflowException("Error when executing the sql query:" + query, e);
    } catch (ClassNotFoundException | IOException e) {
        throw new InternalWorkflowException("Error when deserializing a workflow request.", e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, resultSet, prepStmt);
    }
}

Example 22 with MySQL

use of org.wso2.carbon.idp.mgt.util.IdPManagementConstants.MySQL in project identity-inbound-auth-oauth by wso2-extensions.

the class OAuthScopeDAOImpl method getPreparedStatementForGetAllScopesWithPagination.

/**
 * Get SQL statement for get all scope with pagination. (including OAuth2 scopes and OIDC scopes).
 *
 * @param offset   Offset.
 * @param limit    Limit.
 * @param tenantID Tenet ID.
 * @param conn     Database connection.
 * @return
 * @throws SQLException
 */
private NamedPreparedStatement getPreparedStatementForGetAllScopesWithPagination(Integer offset, Integer limit, int tenantID, Connection conn) throws SQLException {
    String query;
    String driverName = conn.getMetaData().getDriverName();
    if (driverName.contains("MySQL") || driverName.contains("MariaDB") || driverName.contains("H2")) {
        query = SQLQueries.RETRIEVE_ALL_SCOPES_WITH_PAGINATION_MYSQL;
    } else if (conn.getMetaData().getDatabaseProductName().contains("DB2")) {
        query = SQLQueries.RETRIEVE_ALL_SCOPES_WITH_PAGINATION_DB2SQL;
    } else if (driverName.contains("MS SQL")) {
        query = SQLQueries.RETRIEVE_ALL_SCOPES_WITH_PAGINATION_MSSQL;
    } else if (driverName.contains("Microsoft") || driverName.contains("microsoft")) {
        query = SQLQueries.RETRIEVE_ALL_SCOPES_WITH_PAGINATION_MSSQL;
    } else if (driverName.contains("PostgreSQL")) {
        query = SQLQueries.RETRIEVE_ALL_SCOPES_WITH_PAGINATION_POSTGRESQL;
    } else if (driverName.contains("Informix")) {
        // Driver name = "IBM Informix JDBC Driver for IBM Informix Dynamic Server"
        query = SQLQueries.RETRIEVE_ALL_SCOPES_WITH_PAGINATION_INFORMIX;
    } else {
        query = SQLQueries.RETRIEVE_ALL_SCOPES_WITH_PAGINATION_ORACLE;
    }
    NamedPreparedStatement namedPreparedStatement = new NamedPreparedStatement(conn, query);
    namedPreparedStatement.setInt(Oauth2ScopeConstants.SQLPlaceholders.TENANT_ID, tenantID);
    namedPreparedStatement.setInt(Oauth2ScopeConstants.SQLPlaceholders.OFFSET, offset);
    namedPreparedStatement.setInt(Oauth2ScopeConstants.SQLPlaceholders.LIMIT, limit);
    return namedPreparedStatement;
}

Example 23 with MySQL

use of org.wso2.carbon.idp.mgt.util.IdPManagementConstants.MySQL in project identity-inbound-auth-oauth by wso2-extensions.

the class AccessTokenDAOImpl method getLatestAccessToken.

@Override
public AccessTokenDO getLatestAccessToken(String consumerKey, AuthenticatedUser authzUser, String userStoreDomain, String scope, String tokenBindingReference, boolean includeExpiredTokens) throws IdentityOAuth2Exception {
    if (log.isDebugEnabled()) {
        log.debug("Retrieving latest access token for client: " + consumerKey + " user: " + authzUser.getLoggableUserId() + " scope: " + scope);
    }
    String tenantDomain = authzUser.getTenantDomain();
    int tenantId = OAuth2Util.getTenantId(tenantDomain);
    boolean isUsernameCaseSensitive = IdentityUtil.isUserStoreCaseSensitive(authzUser.getUserStoreDomain(), tenantId);
    String tenantAwareUsernameWithNoUserDomain = authzUser.getUserName();
    String userDomain = OAuth2Util.getUserStoreDomain(authzUser);
    String authenticatedIDP = OAuth2Util.getAuthenticatedIDP(authzUser);
    Connection connection = IdentityDatabaseUtil.getDBConnection(false);
    PreparedStatement prepStmt = null;
    ResultSet resultSet = null;
    try {
        String sql;
        String driverName = connection.getMetaData().getDriverName();
        if (OAuth2ServiceComponentHolder.isIDPIdColumnEnabled()) {
            if (driverName.contains("MySQL") || driverName.contains("MariaDB") || driverName.contains("H2")) {
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_MYSQL;
            } else if (connection.getMetaData().getDatabaseProductName().contains("DB2")) {
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_DB2SQL;
            } else if (driverName.contains("MS SQL")) {
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_MSSQL;
            } else if (driverName.contains("Microsoft")) {
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_MSSQL;
            } else if (driverName.contains("PostgreSQL")) {
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_POSTGRESQL;
            } else if (driverName.contains("Informix")) {
                // Driver name = "IBM Informix JDBC Driver for IBM Informix Dynamic Server"
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_INFORMIX;
            } else {
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_ORACLE;
            }
        } else {
            if (driverName.contains("MySQL") || driverName.contains("MariaDB") || driverName.contains("H2")) {
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_MYSQL;
            } else if (connection.getMetaData().getDatabaseProductName().contains("DB2")) {
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_DB2SQL;
            } else if (driverName.contains("MS SQL")) {
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_MSSQL;
            } else if (driverName.contains("Microsoft")) {
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_MSSQL;
            } else if (driverName.contains("PostgreSQL")) {
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_POSTGRESQL;
            } else if (driverName.contains("Informix")) {
                // Driver name = "IBM Informix JDBC Driver for IBM Informix Dynamic Server"
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_INFORMIX;
            } else {
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_ORACLE;
            }
        }
        if (!includeExpiredTokens) {
            sql = sql.replace("TOKEN_SCOPE_HASH=?", "TOKEN_SCOPE_HASH=? AND TOKEN_STATE='ACTIVE'");
        }
        sql = OAuth2Util.getTokenPartitionedSqlByUserStore(sql, userDomain);
        if (!isUsernameCaseSensitive) {
            sql = sql.replace(AUTHZ_USER, LOWER_AUTHZ_USER);
        }
        String hashedScope = OAuth2Util.hashScopes(scope);
        if (hashedScope == null) {
            sql = sql.replace("TOKEN_SCOPE_HASH=?", "TOKEN_SCOPE_HASH IS NULL");
        }
        prepStmt = connection.prepareStatement(sql);
        prepStmt.setString(1, getPersistenceProcessor().getProcessedClientId(consumerKey));
        if (isUsernameCaseSensitive) {
            prepStmt.setString(2, tenantAwareUsernameWithNoUserDomain);
        } else {
            prepStmt.setString(2, tenantAwareUsernameWithNoUserDomain.toLowerCase());
        }
        prepStmt.setInt(3, tenantId);
        prepStmt.setString(4, userDomain);
        if (hashedScope != null) {
            prepStmt.setString(5, hashedScope);
        }
        prepStmt.setString(6, tokenBindingReference);
        if (OAuth2ServiceComponentHolder.isIDPIdColumnEnabled()) {
            prepStmt.setString(7, authenticatedIDP);
        }
        resultSet = prepStmt.executeQuery();
        if (resultSet.next()) {
            boolean returnToken = false;
            String tokenState = resultSet.getString(7);
            if (includeExpiredTokens) {
                if (OAuthConstants.TokenStates.TOKEN_STATE_ACTIVE.equals(tokenState) || OAuthConstants.TokenStates.TOKEN_STATE_EXPIRED.equals(tokenState)) {
                    returnToken = true;
                }
            } else {
                if (OAuthConstants.TokenStates.TOKEN_STATE_ACTIVE.equals(tokenState)) {
                    returnToken = true;
                }
            }
            if (returnToken) {
                String accessToken = getPersistenceProcessor().getPreprocessedAccessTokenIdentifier(resultSet.getString(1));
                String refreshToken = null;
                if (resultSet.getString(2) != null) {
                    refreshToken = getPersistenceProcessor().getPreprocessedRefreshToken(resultSet.getString(2));
                }
                long issuedTime = resultSet.getTimestamp(3, Calendar.getInstance(TimeZone.getTimeZone(UTC))).getTime();
                long refreshTokenIssuedTime = resultSet.getTimestamp(4, Calendar.getInstance(TimeZone.getTimeZone(UTC))).getTime();
                long validityPeriodInMillis = resultSet.getLong(5);
                long refreshTokenValidityPeriodInMillis = resultSet.getLong(6);
                String userType = resultSet.getString(8);
                String tokenId = resultSet.getString(9);
                String subjectIdentifier = resultSet.getString(10);
                String grantType = resultSet.getString(11);
                // data loss at dividing the validity period but can be neglected
                AuthenticatedUser user = OAuth2Util.createAuthenticatedUser(tenantAwareUsernameWithNoUserDomain, userDomain, tenantDomain, authenticatedIDP);
                user.setAuthenticatedSubjectIdentifier(subjectIdentifier);
                AccessTokenDO accessTokenDO = new AccessTokenDO(consumerKey, user, OAuth2Util.buildScopeArray(scope), new Timestamp(issuedTime), new Timestamp(refreshTokenIssuedTime), validityPeriodInMillis, refreshTokenValidityPeriodInMillis, userType);
                accessTokenDO.setAccessToken(accessToken);
                accessTokenDO.setRefreshToken(refreshToken);
                accessTokenDO.setTokenState(tokenState);
                accessTokenDO.setTokenId(tokenId);
                accessTokenDO.setGrantType(grantType);
                if (StringUtils.isNotBlank(tokenBindingReference) && !NONE.equals(tokenBindingReference)) {
                    setTokenBindingToAccessTokenDO(accessTokenDO, connection, tokenId);
                }
                if (log.isDebugEnabled() && IdentityUtil.isTokenLoggable(IdentityConstants.IdentityTokens.ACCESS_TOKEN)) {
                    log.debug("Retrieved latest access token(hashed): " + DigestUtils.sha256Hex(accessToken) + " for client: " + consumerKey + " user: " + authzUser.getLoggableUserId() + " scope: " + scope + " token binding reference: " + tokenBindingReference);
                }
                return accessTokenDO;
            }
        }
        return null;
    } catch (SQLException e) {
        String errorMsg = "Error occurred while trying to retrieve latest 'ACTIVE' " + "access token for Client ID : " + consumerKey + ", User ID : " + authzUser + " and  Scope : " + scope;
        if (includeExpiredTokens) {
            errorMsg = errorMsg.replace("ACTIVE", "ACTIVE or EXPIRED");
        }
        throw new IdentityOAuth2Exception(errorMsg, e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, resultSet, prepStmt);
    }
}

Example 24 with MySQL

use of org.wso2.carbon.idp.mgt.util.IdPManagementConstants.MySQL in project identity-inbound-auth-oauth by wso2-extensions.

the class AccessTokenDAOImpl method getLatestAccessTokens.

@Override
public List<AccessTokenDO> getLatestAccessTokens(String consumerKey, AuthenticatedUser authzUser, String userStoreDomain, String scope, String tokenBindingReference, boolean includeExpiredTokens, int limit) throws IdentityOAuth2Exception {
    if (log.isDebugEnabled()) {
        log.debug("Retrieving " + (includeExpiredTokens ? " active" : " all ") + " latest " + limit + " access " + "token for user: " + authzUser.toString() + " client: " + consumerKey + " scope: " + scope);
    }
    if (authzUser == null) {
        throw new IdentityOAuth2Exception("Invalid user information for given consumerKey: " + consumerKey);
    }
    String tenantDomain = authzUser.getTenantDomain();
    int tenantId = OAuth2Util.getTenantId(tenantDomain);
    boolean isUsernameCaseSensitive = IdentityUtil.isUserStoreCaseSensitive(authzUser.getUserStoreDomain(), tenantId);
    String tenantAwareUsernameWithNoUserDomain = authzUser.getUserName();
    userStoreDomain = OAuth2Util.getSanitizedUserStoreDomain(userStoreDomain);
    String userDomain = OAuth2Util.getUserStoreDomain(authzUser);
    String authenticatedIDP = OAuth2Util.getAuthenticatedIDP(authzUser);
    Connection connection = IdentityDatabaseUtil.getDBConnection(false);
    PreparedStatement prepStmt = null;
    ResultSet resultSet = null;
    boolean sqlAltered = false;
    try {
        String sql;
        String driverName = connection.getMetaData().getDriverName();
        if (OAuth2ServiceComponentHolder.isIDPIdColumnEnabled()) {
            if (driverName.contains("MySQL") || driverName.contains("MariaDB") || driverName.contains("H2")) {
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_MYSQL;
            } else if (connection.getMetaData().getDatabaseProductName().contains("DB2")) {
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_DB2SQL;
            } else if (driverName.contains("MS SQL")) {
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_MSSQL;
            } else if (driverName.contains("Microsoft")) {
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_MSSQL;
            } else if (driverName.contains("PostgreSQL")) {
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_POSTGRESQL;
            } else if (driverName.contains("Informix")) {
                // Driver name = "IBM Informix JDBC Driver for IBM Informix Dynamic Server"
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_INFORMIX;
            } else {
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_ORACLE;
                sql = sql.replace("ROWNUM < 2", "ROWNUM < " + Integer.toString(limit + 1));
                sqlAltered = true;
            }
        } else {
            if (driverName.contains("MySQL") || driverName.contains("MariaDB") || driverName.contains("H2")) {
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_MYSQL;
            } else if (connection.getMetaData().getDatabaseProductName().contains("DB2")) {
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_DB2SQL;
            } else if (driverName.contains("MS SQL")) {
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_MSSQL;
            } else if (driverName.contains("Microsoft")) {
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_MSSQL;
            } else if (driverName.contains("PostgreSQL")) {
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_POSTGRESQL;
            } else if (driverName.contains("Informix")) {
                // Driver name = "IBM Informix JDBC Driver for IBM Informix Dynamic Server"
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_INFORMIX;
            } else {
                sql = SQLQueries.RETRIEVE_LATEST_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_ORACLE;
                sql = sql.replace("ROWNUM < 2", "ROWNUM < " + Integer.toString(limit + 1));
                sqlAltered = true;
            }
        }
        if (!includeExpiredTokens) {
            sql = sql.replace("TOKEN_SCOPE_HASH=?", "TOKEN_SCOPE_HASH=? AND TOKEN_STATE='ACTIVE'");
        }
        if (!sqlAltered) {
            sql = sql.replace("LIMIT 1", "LIMIT " + Integer.toString(limit));
        }
        sql = OAuth2Util.getTokenPartitionedSqlByUserStore(sql, userStoreDomain);
        if (!isUsernameCaseSensitive) {
            sql = sql.replace(AUTHZ_USER, LOWER_AUTHZ_USER);
        }
        String hashedScope = OAuth2Util.hashScopes(scope);
        if (hashedScope == null) {
            sql = sql.replace("TOKEN_SCOPE_HASH=?", "TOKEN_SCOPE_HASH IS NULL");
        }
        prepStmt = connection.prepareStatement(sql);
        prepStmt.setString(1, getPersistenceProcessor().getProcessedClientId(consumerKey));
        if (isUsernameCaseSensitive) {
            prepStmt.setString(2, tenantAwareUsernameWithNoUserDomain);
        } else {
            prepStmt.setString(2, tenantAwareUsernameWithNoUserDomain.toLowerCase());
        }
        prepStmt.setInt(3, tenantId);
        prepStmt.setString(4, userDomain);
        if (hashedScope != null) {
            prepStmt.setString(5, hashedScope);
        }
        prepStmt.setString(6, tokenBindingReference);
        if (OAuth2ServiceComponentHolder.isIDPIdColumnEnabled()) {
            prepStmt.setString(7, authenticatedIDP);
        }
        resultSet = prepStmt.executeQuery();
        long latestIssuedTime = new Date().getTime();
        List<AccessTokenDO> accessTokenDOs = new ArrayList<>();
        int iterationCount = 0;
        while (resultSet.next()) {
            long issuedTime = resultSet.getTimestamp(3, Calendar.getInstance(TimeZone.getTimeZone("UTC"))).getTime();
            if (iterationCount == 0) {
                latestIssuedTime = issuedTime;
            }
            if (latestIssuedTime == issuedTime) {
                String tokenState = resultSet.getString(7);
                String accessToken = getPersistenceProcessor().getPreprocessedAccessTokenIdentifier(resultSet.getString(1));
                String refreshToken = null;
                if (resultSet.getString(2) != null) {
                    refreshToken = getPersistenceProcessor().getPreprocessedRefreshToken(resultSet.getString(2));
                }
                long refreshTokenIssuedTime = resultSet.getTimestamp(4, Calendar.getInstance(TimeZone.getTimeZone("UTC"))).getTime();
                long validityPeriodInMillis = resultSet.getLong(5);
                long refreshTokenValidityPeriodInMillis = resultSet.getLong(6);
                String userType = resultSet.getString(8);
                String tokenId = resultSet.getString(9);
                String subjectIdentifier = resultSet.getString(10);
                String grantType = resultSet.getString(11);
                // data loss at dividing the validity period but can be neglected
                AuthenticatedUser user = OAuth2Util.createAuthenticatedUser(tenantAwareUsernameWithNoUserDomain, userDomain, tenantDomain, authenticatedIDP);
                ServiceProvider serviceProvider;
                try {
                    serviceProvider = OAuth2ServiceComponentHolder.getApplicationMgtService().getServiceProviderByClientId(consumerKey, OAuthConstants.Scope.OAUTH2, tenantDomain);
                } catch (IdentityApplicationManagementException e) {
                    throw new IdentityOAuth2Exception("Error occurred while retrieving OAuth2 application data " + "for client id " + consumerKey, e);
                }
                user.setAuthenticatedSubjectIdentifier(subjectIdentifier, serviceProvider);
                AccessTokenDO accessTokenDO = new AccessTokenDO(consumerKey, user, OAuth2Util.buildScopeArray(scope), new Timestamp(issuedTime), new Timestamp(refreshTokenIssuedTime), validityPeriodInMillis, refreshTokenValidityPeriodInMillis, userType);
                accessTokenDO.setAccessToken(accessToken);
                accessTokenDO.setRefreshToken(refreshToken);
                accessTokenDO.setTokenState(tokenState);
                accessTokenDO.setTokenId(tokenId);
                accessTokenDO.setGrantType(grantType);
                accessTokenDOs.add(accessTokenDO);
            } else {
                return accessTokenDOs;
            }
            iterationCount++;
        }
        return accessTokenDOs;
    } catch (SQLException e) {
        String errorMsg = "Error occurred while trying to retrieve latest 'ACTIVE' access token for Client " + "ID : " + consumerKey + ", User ID : " + authzUser + " and  Scope : " + scope;
        if (includeExpiredTokens) {
            errorMsg = errorMsg.replace("ACTIVE", "ACTIVE or EXPIRED");
        }
        throw new IdentityOAuth2Exception(errorMsg, e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, resultSet, prepStmt);
    }
}