Examples with SqlBuilder org.wso2.carbon.user.core.model.SqlBuilder used on opensource projects

Example 1 with SqlBuilder

use of org.wso2.carbon.user.core.model.SqlBuilder in project identity-governance by wso2-extensions.

the class JDBCIdentityDataStore method listPaginatedUsersNames.

@Override
public List<String> listPaginatedUsersNames(List<ExpressionCondition> identityClaimFilterExpressionConditions, List<String> identityClaimFilteredUserNames, String domain, org.wso2.carbon.user.core.UserStoreManager userStoreManager, int limit, int offset) throws IdentityException {
    try {
        int tenantId = userStoreManager.getTenantId();
        try (Connection connection = IdentityDatabaseUtil.getDBConnection()) {
            // Based on the DB Type might need to extend support.
            String dBType = DatabaseCreator.getDatabaseType(connection);
            // To handle the offset being one leads to null response
            if (offset <= 0) {
                offset = 0;
            } else {
                offset = offset - 1;
            }
            SqlBuilder sqlBuilder = getQueryString(identityClaimFilterExpressionConditions, limit, offset, domain, tenantId, dBType);
            String fullQuery = sqlBuilder.getQuery();
            int startIndex = 0;
            int endIndex = 0;
            int occurrence = StringUtils.countMatches(fullQuery, QUERY_BINDING_SYMBOL);
            endIndex = endIndex + occurrence;
            try (PreparedStatement preparedStatement = connection.prepareStatement(fullQuery)) {
                populatePrepareStatement(sqlBuilder, preparedStatement, startIndex, endIndex);
                try (ResultSet resultSet = preparedStatement.executeQuery()) {
                    while (resultSet.next()) {
                        identityClaimFilteredUserNames.add(resultSet.getString("USER_NAME"));
                    }
                    IdentityDatabaseUtil.commitTransaction(connection);
                } catch (SQLException e) {
                    if (log.isDebugEnabled()) {
                        log.debug("Error occurred while retrieving users from Identity Store for " + domain + "with limit " + limit + "and offset " + offset, e);
                    }
                    IdentityDatabaseUtil.rollbackTransaction(connection);
                }
            } catch (SQLException e) {
                throw new IdentityException("Error occurred while retrieving users from Identity Store.", e);
            }
            return identityClaimFilteredUserNames;
        } catch (Exception e) {
            throw new IdentityException("Error occurred while retrieving users from Identity Store.", e);
        }
    } catch (org.wso2.carbon.user.core.UserStoreException e) {
        throw new IdentityException("Error occurred while retrieving users.", e);
    }
}

Example 2 with SqlBuilder

use of org.wso2.carbon.user.core.model.SqlBuilder in project identity-governance by wso2-extensions.

the class JDBCIdentityDataStore method getQueryString.

private SqlBuilder getQueryString(List<ExpressionCondition> identityClaimFilterExpressionConditions, int limit, int offset, String userStoreDomain, int tenantID, String dbType) {
    boolean hitClaimFilter = false;
    String userNameWithDomain;
    StringBuilder sqlStatement = new StringBuilder("SELECT DISTINCT USER_NAME FROM IDN_IDENTITY_USER_DATA ");
    SqlBuilder sqlBuilder = new SqlBuilder(sqlStatement);
    sqlBuilder.where("TENANT_ID = ? ", tenantID);
    if (StringUtils.equalsIgnoreCase(userStoreDomain, UserCoreConstants.PRIMARY_DEFAULT_DOMAIN_NAME)) {
        userNameWithDomain = SQL_FILTER_STRING_ANY + UserCoreConstants.DOMAIN_SEPARATOR + SQL_FILTER_STRING_ANY;
        sqlBuilder.where(" USER_NAME NOT LIKE ? ", userNameWithDomain);
    } else {
        userNameWithDomain = userStoreDomain.toUpperCase() + UserCoreConstants.DOMAIN_SEPARATOR + SQL_FILTER_STRING_ANY;
        sqlBuilder.where(" USER_NAME LIKE ? ", userNameWithDomain);
    }
    SqlBuilder header = new SqlBuilder(new StringBuilder(sqlBuilder.getSql()));
    addingWheres(sqlBuilder, header);
    for (ExpressionCondition expressionCondition : identityClaimFilterExpressionConditions) {
        String claimValue = expressionCondition.getAttributeValue();
        if (claimValue.contains(QUERY_FILTER_STRING_ANY)) {
            // This is to support LDAP like queries. Value having only * is restricted except one *.
            if (!claimValue.matches("(\\*)\\1+")) {
                // Convert all the * to % except \*.
                claimValue = claimValue.replaceAll("(?<!\\\\)\\*", SQL_FILTER_STRING_ANY);
            }
        }
        // Adding filter claims.
        multiClaimQueryBuilder(sqlBuilder, header, hitClaimFilter, expressionCondition);
        hitClaimFilter = true;
    }
    if (DB2.equals(dbType)) {
        sqlBuilder.setTail(" ORDER BY USER_NAME LIMIT ? , ? ", limit, offset);
    } else if (MSSQL.equals(dbType)) {
        sqlBuilder.setTail(" ORDER BY USER_NAME OFFSET ? ROWS FETCH NEXT ? ROWS ONLY ", offset, limit);
    } else if (ORACLE.equals(dbType)) {
        sqlBuilder.setTail(" ORDER BY USER_NAME OFFSET ? ROWS FETCH NEXT ? ROWS ONLY ", offset, limit);
    } else if (POSTGRE_SQL.equals(dbType)) {
        sqlBuilder.setTail(" ORDER BY USER_NAME OFFSET ? ROWS FETCH NEXT ? ROWS ONLY ", offset, limit);
    } else {
        sqlBuilder.setTail(" ORDER BY USER_NAME ASC LIMIT ? OFFSET ?", limit, offset);
    }
    return sqlBuilder;
}