JpqlCondition for dynamic conditions

Support
#1

Hi,
I could not integrate JpaSpecifications into jmix project. So, I decided to write a service that would execute queries with dynamic conditions:

public class JmixPageRequestService {
    @Autowired
    private Metadata metadata;
    @Autowired
    private DataManager dataManager;

    public <T> Page<T> load(Class<T> clazz, LogicalCondition condition, Pageable pageable, FetchPlan fetchPlan) {

        LoadContext<T> countingContext = prepareLoadContext(clazz);
        LoadContext.Query countingQuery = Objects.requireNonNull(countingContext.getQuery());
        countingQuery.setCondition(condition);
        long count = dataManager.getCount(countingContext);

        LoadContext<T> dataContext = prepareLoadContext(clazz);
        dataContext.setFetchPlan(fetchPlan);
        LoadContext.Query dataQuery = Objects.requireNonNull(dataContext.getQuery());
        dataQuery.setCondition(condition);

        dataQuery.setFirstResult(pageable.getPageNumber());
        dataQuery.setMaxResults(pageable.getPageSize());

        List<T> content = dataManager.loadList(dataContext);

        return new PageImpl<>(content, pageable, count);
    }

    private <T> LoadContext<T> prepareLoadContext(Class<T> clazz) {
        LoadContext<T> loadContext = new LoadContext<>(metadata.getClass(clazz));
        String queryString = "select e from " + clazz.getSimpleName() + " e";
        loadContext.setQueryString(queryString);
        return loadContext;
    }
}

The condition here is a LogicalCondition anded with other conditions. These usually are PropertyConditions. My question is, is it possible to also add a JpqlCondition? If so, how? I have been looking for an example, but failed to find any

#3

Hello Olzhas,

I do apologize for the late reply.

Yes, it is possible to use LogicalCondition with JpqlCondition in the same way as with PropertyCondition, specifying where and join clauses as for JpqlFilter.
E.g. this condition may be used for User entity in standard Jmix application:

LogicalCondition.and(
        JpqlCondition.createWithParameters("e.username like :name", null, params),
        JpqlCondition.create("e.email is not null", null)
)

Please, take a note that condition will not be applied if it has one or more null values in parameters. This behaviour helps to not consider empty filter fields but may lead to unexpected results when null parameter value passed intentionally.

For more information see io.jmix.core.querycondition.JpqlCondition#actualize. You can override this method to always return this in order to always consider conditions regardless of the parameters passed.

Regards,
Dmitry

1 Like