Examples with QueryBuilder org.meveo.commons.utils.QueryBuilder used on opensource projects

Example 1 with QueryBuilder

use of org.meveo.commons.utils.QueryBuilder in project meveo by meveo-org.

the class CustomEntityTemplateService method findByClassAndCode.

/**
 * A generic method that returns a filtered list of ICustomFieldEntity given an entity class and code.
 *
 * @param entityClass - class of an entity. eg. org.meveo.catalog.OfferTemplate
 * @param entityCode  - code of entity
 * @return customer field entity
 */
@SuppressWarnings({ "rawtypes", "unchecked" })
public ICustomFieldEntity findByClassAndCode(Class entityClass, String entityCode) {
    ICustomFieldEntity result = null;
    QueryBuilder queryBuilder = new QueryBuilder(entityClass, "a", null);
    queryBuilder.addCriterion("code", "=", entityCode, true);
    List<ICustomFieldEntity> entities = (List<ICustomFieldEntity>) queryBuilder.getQuery(getEntityManager()).getResultList();
    if (entities != null && !entities.isEmpty()) {
        result = entities.get(0);
    }
    return result;
}

Example 2 with QueryBuilder

use of org.meveo.commons.utils.QueryBuilder in project meveo by meveo-org.

the class CustomEntityInstanceService method list.

public List<CustomEntityInstance> list(String cetCode, boolean isStoreAsTable, Map<String, Object> values, PaginationConfiguration paginationConfiguration) {
    QueryBuilder qb = new QueryBuilder(getEntityClass(), "cei", null);
    qb.addCriterion("cei.cetCode", "=", cetCode, true);
    qb.addPaginationConfiguration(paginationConfiguration);
    final List<CustomEntityInstance> resultList = qb.getTypedQuery(getEntityManager(), CustomEntityInstance.class).getResultList();
    if (values != null && !values.isEmpty()) {
        return resultList.stream().filter(customEntityInstance -> filterOnValues(values, customEntityInstance, isStoreAsTable)).collect(Collectors.toList());
    }
    return resultList;
}

Example 3 with QueryBuilder

use of org.meveo.commons.utils.QueryBuilder in project meveo by meveo-org.

the class CustomEntityInstanceService method list.

public List<CustomEntityInstance> list(String cetCode, Map<String, Object> values) {
    QueryBuilder qb = new QueryBuilder(getEntityClass(), "cei", null);
    qb.addCriterion("cei.cetCode", "=", cetCode, true);
    final List<CustomEntityInstance> resultList = qb.getTypedQuery(getEntityManager(), CustomEntityInstance.class).getResultList();
    var ownValues = new HashMap<>(values);
    for (var entry : values.entrySet()) {
        if (entry.getValue() instanceof EntityReferenceWrapper) {
            ownValues.remove(entry.getKey());
        }
    }
    if (ownValues != null && !ownValues.isEmpty()) {
        return resultList.stream().filter(customEntityInstance -> filterOnValues(ownValues, customEntityInstance)).collect(Collectors.toList());
    }
    return resultList;
}

Example 4 with QueryBuilder

use of org.meveo.commons.utils.QueryBuilder in project meveo by meveo-org.

the class CustomEntityInstanceService method findByCode.

@SuppressWarnings("unchecked")
public List<CustomEntityInstance> findByCode(String cetCode, String code) {
    QueryBuilder qb = new QueryBuilder(getEntityClass(), "cei", null);
    qb.addCriterion("cei.cetCode", "=", cetCode, true);
    if (StringUtils.isNotEmpty(code)) {
        qb.like("cei.code", code, QueryBuilder.QueryLikeStyleEnum.MATCH_ANYWHERE, false);
    }
    return qb.getQuery(getEntityManager()).getResultList();
}

Example 5 with QueryBuilder

use of org.meveo.commons.utils.QueryBuilder in project meveo by meveo-org.

the class NativePersistenceService method getQuery.

/**
 * Creates NATIVE query to filter entities according data provided in pagination
 * configuration.
 * <p>
 * Search filters (key = Filter key, value = search pattern or value).
 * <p>
 * Filter key can be:
 * <ul>
 * <li>SQL. Additional sql to apply. Value is either a sql query or an array
 * consisting of sql query and one or more parameters to apply</li>
 * <li>&lt;condition&gt; &lt;fieldname1&gt; &lt;fieldname2&gt; ...
 * &lt;fieldnameN&gt;. Value is a value to apply in condition</li>
 * </ul>
 * <p>
 * A union between different filter items is AND.
 * <p>
 * <p>
 * Condition is optional. Number of fieldnames depend on condition used. If no
 * condition is specified an "equals ignoring case" operation is considered.
 * <p>
 * <p>
 * Following conditions are supported:
 * <ul>
 * <li>fromRange. Ranged search - field value in between from - to values.
 * Specifies "from" part value: e.g value&lt;=fiel.value. Applies to date and
 * number type fields.</li>
 * <li>toRange. Ranged search - field value in between from - to values.
 * Specifies "to" part value: e.g field.value&lt;=value</li>
 * <li>list. Value is in field's list value. Applies to date and number type
 * fields.</li>
 * <li>inList/not-inList. Field value is [not] in value (list). A comma
 * separated string will be parsed into a list if values. A single value will be
 * considered as a list value of one item</li>
 * <li>minmaxRange. The value is in between two field values. TWO field names
 * must be provided. Applies to date and number type fields.</li>
 * <li>minmaxOptionalRange. Similar to minmaxRange. The value is in between two
 * field values with either them being optional. TWO fieldnames must be
 * specified.</li>
 * <li>overlapOptionalRange. The value range is overlapping two field values
 * with either them being optional. TWO fieldnames must be specified. Value must
 * be an array of two values.</li>
 * <li>likeCriterias. Multiple fieldnames can be specified. Any of the multiple
 * field values match the value (OR criteria). In case value contains *, a like
 * criteria match will be used. In either case case insensative matching is
 * used. Applies to String type fields.</li>
 * <li>wildcardOr. Similar to likeCriterias. A wildcard match will always used.
 * A * will be appended to start and end of the value automatically if not
 * present. Applies to
 * <li>wildcardOrIgnoreCase. Similar to wildcardOr but ignoring case String type
 * fields.</li>
 * <li>ne. Not equal.
 * </ul>
 * <p>
 * Following special meaning values are supported:
 * <ul>
 * <li>IS_NULL. Field value is null</li>
 * <li>IS_NOT_NULL. Field value is not null</li>
 * </ul>
 * <p>
 * <p>
 * <p>
 * To filter by a related entity's field you can either filter by related
 * entity's field or by related entity itself specifying code as value. These
 * two example will do the same in case when quering a customer account:
 * customer.code=aaa OR customer=aaa
 * <p>
 * To filter a list of related entities by a list of entity codes use "inList"
 * on related entity field. e.g. for quering offer template by sellers: inList
 * sellers=code1,code2
 *
 * <b>Note:</b> Quering by related entity field directly will result in
 * exception when entity with a specified code does not exists
 * <p>
 * <p>
 * Examples:
 * <ul>
 * <li>invoice number equals "1578AU": Filter key: invoiceNumber. Filter value:
 * 1578AU</li>
 * <li>invoice number is not "1578AU": Filter key: ne invoiceNumber. Filter
 * value: 1578AU</li>
 * <li>invoice number is null: Filter key: invoiceNumber. Filter value:
 * IS_NULL</li>
 * <li>invoice number is not empty: Filter key: invoiceNumber. Filter value:
 * IS_NOT_NULL</li>
 * <li>Invoice date is between 2017-05-01 and 2017-06-01: Filter key: fromRange
 * invoiceDate. Filter value: 2017-05-01 Filter key: toRange invoiceDate. Filter
 * value: 2017-06-01</li>
 * <li>Date is between creation and update dates: Filter key: minmaxRange
 * audit.created audit.updated. Filter value: 2017-05-25</li>
 * <li>invoice number is any of 158AU, 159KU or 189LL: Filter key: inList
 * invoiceNumber. Filter value: 158AU,159KU,189LL</li>
 * <li>any of param1, param2 or param3 fields contains "energy": Filter key:
 * wildcardOr param1 param2 param3. Filter value: energy</li>
 * <li>any of param1, param2 or param3 fields start with "energy": Filter key:
 * likeCriterias param1 param2 param3. Filter value: *energy</li>
 * <li>any of param1, param2 or param3 fields is "energy": Filter key:
 * likeCriterias param1 param2 param3. Filter value: energy</li>
 * </ul>
 *
 * @param tableName A name of a table to query
 * @param config    Data filtering, sorting and pagination criteria
 * @return Query builder to filter entities according to pagination
 *         configuration data.
 */
@SuppressWarnings({ "rawtypes" })
public QueryBuilder getQuery(String tableName, PaginationConfiguration config) {
    String startQuery;
    tableName = PostgresReserverdKeywords.escapeAndFormat(tableName);
    String superType = config != null ? config.getSuperType() : null;
    if (superType != null) {
        superType = PostgresReserverdKeywords.escapeAndFormat(superType);
    }
    // If no fetch fields are defined, return everything
    if (config == null || config.getFetchFields() == null) {
        if (superType != null) {
            startQuery = "select a.*";
            // Declaratively fetch super-type fields
            if (config.getSuperTypeFields() != null) {
                for (var superTypeField : config.getSuperTypeFields()) {
                    String fieldName = PostgresReserverdKeywords.escapeAndFormat(superTypeField);
                    startQuery += ", b." + fieldName + " ";
                }
            } else {
                startQuery += ", b.*";
            }
            startQuery += "from {h-schema}" + tableName + " a ";
            startQuery += " INNER JOIN " + superType + " b ON a.uuid = b.uuid";
        } else {
            startQuery = "select * from {h-schema}" + tableName + " a ";
        }
    } else if (config.getFetchFields().isEmpty()) {
        // If fetch fields are empty, only return UUID
        startQuery = "select a.uuid from {h-schema}" + tableName + " a ";
        if (superType != null) {
            startQuery += " INNER JOIN " + superType + " b ON a.uuid = b.uuid";
        }
    } else {
        // Always return UUID
        StringBuilder builder = new StringBuilder("select a.uuid, ");
        config.getFetchFields().forEach(s -> {
            String fieldName = PostgresReserverdKeywords.escapeAndFormat(s);
            builder.append(fieldName).append(", ");
        });
        builder.delete(builder.length() - 2, builder.length());
        startQuery = builder.append(" from {h-schema}").append(tableName).append(" a ").toString();
        if (superType != null) {
            startQuery += " INNER JOIN " + superType + " b ON a.uuid = b.uuid";
        }
    }
    QueryBuilder queryBuilder = new QueryBuilder(startQuery, null);
    if (config == null) {
        return queryBuilder;
    }
    Map<String, Object> filters = config.getFilters();
    if (filters != null && !filters.isEmpty()) {
        for (String key : filters.keySet()) {
            Object filterValue = filters.get(key);
            if (filterValue == null) {
                continue;
            }
            // Key format is: condition field1 field2 or condition-field1-field2-fieldN
            // example: "ne code", condition=code, fieldName=code, fieldName2=null
            String[] fieldInfo = key.split(" ");
            String condition = fieldInfo.length == 1 ? null : fieldInfo[0];
            String fieldName = fieldInfo.length == 1 ? fieldInfo[0] : fieldInfo[1];
            String fieldName2 = fieldInfo.length == 3 ? fieldInfo[2] : null;
            String[] fields = null;
            if (condition != null) {
                fields = Arrays.copyOfRange(fieldInfo, 1, fieldInfo.length);
            }
            if (config.getSuperTypeFields() != null && config.getSuperTypeFields().contains(fieldName)) {
                fieldName = "b." + fieldName;
            } else {
                fieldName = "a." + fieldName;
            }
            // value: e.g value<=field.value
            if ("fromRange".equals(condition)) {
                if (filterValue instanceof Double) {
                    BigDecimal rationalNumber = new BigDecimal((Double) filterValue);
                    queryBuilder.addCriterion(fieldName, " >= ", rationalNumber, false);
                } else if (filterValue instanceof Number) {
                    queryBuilder.addCriterion(fieldName, " >= ", filterValue, false);
                } else if (filterValue instanceof Date) {
                    queryBuilder.addCriterionDateRangeFromTruncatedToDay(fieldName, ((Date) filterValue).toInstant());
                } else if (filterValue instanceof Instant) {
                    queryBuilder.addCriterionDateRangeFromTruncatedToDay(fieldName, (Instant) filterValue);
                }
            // if ranged search - field value in between from - to values. Specifies "to"
            // value: e.g field.value<=value
            } else if ("toRange".equals(condition)) {
                if (filterValue instanceof Double) {
                    BigDecimal rationalNumber = new BigDecimal((Double) filterValue);
                    queryBuilder.addCriterion(fieldName, " <= ", rationalNumber, false);
                } else if (filterValue instanceof Number) {
                    queryBuilder.addCriterion(fieldName, " <= ", filterValue, false);
                } else if (filterValue instanceof Date) {
                    queryBuilder.addCriterionDateRangeToTruncatedToDay(fieldName, ((Date) filterValue).toInstant());
                } else if (filterValue instanceof Instant) {
                    queryBuilder.addCriterionDateRangeToTruncatedToDay(fieldName, (Instant) filterValue);
                }
            // Value is in field value (list)
            } else if ("list".equals(condition)) {
                String paramName = queryBuilder.convertFieldToParam(fieldName);
                queryBuilder.addSqlCriterion(":" + paramName + " in elements(" + fieldName + ")", paramName, filterValue);
            // Field value is in value (list)
            } else if ("inList".equals(condition) || "not-inList".equals(condition)) {
                boolean isNot = "not-inList".equals(condition);
                if (filterValue instanceof String) {
                    queryBuilder.addSql(fieldName + (isNot ? " NOT " : "") + " IN (" + filterValue + ")");
                } else if (filterValue instanceof Collection) {
                    String paramName = queryBuilder.convertFieldToParam(fieldName);
                    queryBuilder.addSqlCriterion(fieldName + (isNot ? " NOT " : "") + " IN (:" + paramName + ")", paramName, filterValue);
                }
            // The value is in between two field values
            } else if ("minmaxRange".equals(condition)) {
                if (filterValue instanceof Double) {
                    BigDecimal rationalNumber = new BigDecimal((Double) filterValue);
                    queryBuilder.addCriterion(fieldName, " <= ", rationalNumber, false);
                    queryBuilder.addCriterion(fieldName2, " >= ", rationalNumber, false);
                } else if (filterValue instanceof Number) {
                    queryBuilder.addCriterion(fieldName, " <= ", filterValue, false);
                    queryBuilder.addCriterion(fieldName2, " >= ", filterValue, false);
                }
                if ((filterValue instanceof Date) || (filterValue instanceof Instant)) {
                    Date value = (filterValue instanceof Date) ? (Date) filterValue : (Date.from((Instant) filterValue));
                    Calendar c = Calendar.getInstance();
                    c.setTime(value);
                    int year = c.get(Calendar.YEAR);
                    int month = c.get(Calendar.MONTH);
                    int date = c.get(Calendar.DATE);
                    c.set(year, month, date, 0, 0, 0);
                    c.set(Calendar.MILLISECOND, 0);
                    value = c.getTime();
                    queryBuilder.addCriterion(fieldName, "<=", value, false);
                    queryBuilder.addCriterion(fieldName2, ">=", value, false);
                }
            // The value is in between two field values with either them being optional
            } else if ("minmaxOptionalRange".equals(condition)) {
                String paramName = queryBuilder.convertFieldToParam(fieldName);
                String sql = "((" + fieldName + " IS NULL and a." + fieldName2 + " IS NULL) or (" + fieldName + "<=:" + paramName + " and :" + paramName + "<a." + fieldName2 + ") or (" + fieldName + "<=:" + paramName + " and a." + fieldName2 + " IS NULL) or (a." + fieldName + " IS NULL and :" + paramName + "<a." + fieldName2 + "))";
                queryBuilder.addSqlCriterionMultiple(sql, paramName, filterValue);
            // The value range is overlapping two field values with either them being
            // optional
            } else if ("overlapOptionalRange".equals(condition)) {
                String paramNameFrom = queryBuilder.convertFieldToParam(fieldName);
                String paramNameTo = queryBuilder.convertFieldToParam(fieldName2);
                String sql = "((" + fieldName + " IS NULL and a." + fieldName2 + " IS NULL) or  (" + fieldName + " IS NULL and a." + fieldName2 + ">:" + paramNameFrom + ") or (a." + fieldName2 + " IS NULL and " + fieldName + "<:" + paramNameTo + ") or (" + fieldName + " IS NOT NULL and a." + fieldName2 + " IS NOT NULL and ((" + fieldName + "<=:" + paramNameFrom + " and :" + paramNameFrom + "<a." + fieldName2 + ") or (:" + paramNameFrom + "<=a." + fieldName + " and " + fieldName + "<:" + paramNameTo + "))))";
                if (filterValue.getClass().isArray()) {
                    queryBuilder.addSqlCriterionMultiple(sql, paramNameFrom, ((Object[]) filterValue)[0], paramNameTo, ((Object[]) filterValue)[1]);
                } else if (filterValue instanceof List) {
                    queryBuilder.addSqlCriterionMultiple(sql, paramNameFrom, ((List) filterValue).get(0), paramNameTo, ((List) filterValue).get(1));
                }
            // Any of the multiple field values wildcard or not wildcard match the value (OR
            // criteria)
            } else if ("likeCriterias".equals(condition)) {
                queryBuilder.startOrClause();
                if (filterValue instanceof String) {
                    String filterString = (String) filterValue;
                    for (String field : fields) {
                        queryBuilder.addCriterionWildcard(field, filterString, false);
                    }
                }
                queryBuilder.endOrClause();
            // Any of the multiple field values wildcard match the value (OR criteria) - a
            // diference from "likeCriterias" is that wildcard will be appended to the value
            // automatically
            } else if (PersistenceService.SEARCH_WILDCARD_OR.equals(condition)) {
                queryBuilder.startOrClause();
                for (String field : fields) {
                    queryBuilder.addSql(field + " like '%" + filterValue + "%'");
                }
                queryBuilder.endOrClause();
            // Just like wildcardOr but ignoring case :
            } else if (PersistenceService.SEARCH_WILDCARD_OR_IGNORE_CAS.equals(condition)) {
                queryBuilder.startOrClause();
                for (String field : fields) {
                    // since SEARCH_WILDCARD_OR_IGNORE_CAS , then filterValue is necessary a String
                    // lowercase functions may give different results in postgres/java => to avoid mismatch, postrges's function is the only one used. Example of mismath : Danışmanlık_İth
                    queryBuilder.addSqlCriterion("lower(a." + field + ") like concat('%', lower(:" + field + "), '%')", field, filterValue);
                }
                queryBuilder.endOrClause();
            // Search by additional Sql clause with specified parameters
            } else if (PersistenceService.SEARCH_SQL.equals(condition)) {
                if (filterValue.getClass().isArray()) {
                    String additionalSql = (String) ((Object[]) filterValue)[0];
                    Object[] additionalParameters = Arrays.copyOfRange(((Object[]) filterValue), 1, ((Object[]) filterValue).length);
                    queryBuilder.addSqlCriterionMultiple(additionalSql, additionalParameters);
                } else {
                    queryBuilder.addSql((String) filterValue);
                }
            } else {
                if (filterValue instanceof String && PersistenceService.SEARCH_IS_NULL.equals(filterValue)) {
                    queryBuilder.addSql(fieldName + " is null ");
                } else if (filterValue instanceof String && PersistenceService.SEARCH_IS_NOT_NULL.equals(filterValue)) {
                    queryBuilder.addSql(fieldName + " is not null ");
                } else if (filterValue instanceof String) {
                    // if contains dot, that means join is needed
                    String filterString = (String) filterValue;
                    boolean wildcard = (filterString.indexOf("*") != -1);
                    if (wildcard) {
                        queryBuilder.addCriterionWildcard(fieldName, filterString, false, "ne".equals(condition));
                    } else {
                        queryBuilder.addCriterion(fieldName, "ne".equals(condition) ? " != " : " = ", filterString, false);
                    }
                } else if (filterValue instanceof Date) {
                    queryBuilder.addCriterionDateTruncatedToDay(fieldName, ((Date) filterValue).toInstant());
                } else if (filterValue instanceof Instant) {
                    queryBuilder.addCriterionDateTruncatedToDay(fieldName, (Instant) filterValue);
                } else if (filterValue instanceof Number || filterValue instanceof Boolean) {
                    queryBuilder.addCriterion(fieldName, "ne".equals(condition) ? " != " : " = ", filterValue, false);
                } else if (filterValue instanceof Enum) {
                    if (filterValue instanceof IdentifiableEnum) {
                        String enumIdKey = new StringBuilder(fieldName).append("Id").toString();
                        queryBuilder.addCriterion(enumIdKey, "ne".equals(condition) ? " != " : " = ", ((IdentifiableEnum) filterValue).getId(), false);
                    } else {
                        queryBuilder.addCriterionEnum(fieldName, (Enum) filterValue, "ne".equals(condition) ? " != " : " = ");
                    }
                } else if (filterValue instanceof List) {
                    queryBuilder.addSqlCriterion(fieldName + ("ne".equals(condition) ? " not in  " : " in ") + ":" + fieldName, fieldName, filterValue);
                }
            }
        }
    }
    queryBuilder.addPaginationConfiguration(config, "a");
    // FIXME: Will only works for Postgres and few others ...
    if (config.isRandomize()) {
        queryBuilder.getSqlStringBuffer().append("ORDER BY RANDOM() ");
    }
    return queryBuilder;
}