Searching dates and longs with CriteriaQueries using wildcards

Question

I've been wrapping my mind around this for a while, but still can't find a solution to it. I have a table, with a search input. My client desires to use this input mainly to search dates and a long type field. So basically I've been trying to make a criteria query using a like to match a Date to a string with wildcards.

For several reasons I cannot use HQL nor other types of "Manual" queries. I'm restricted to CriteriaQueries only.

This has been my best effort so far:

public Predicate toPredicate(Root<Absence> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        List<Predicate> predicates = new ArrayList<>();

        if (filter != null) {

            List<Predicate> or = new ArrayList<>();

            if (filter.getFilter() != null && !filter.getFilter().isEmpty()) {
                Expression<String> function= cb.function("CONVERT", String.class, cb.literal(String.class), root.get(Absence_.pk).get(AbsenceID_.fecha), cb.literal(3));

                or.add(cb.like(function, getLikeAnywhere(filter.getFilter())));
                or.add(cb.like(root.get(Absence_.apellidoYNombre), getLikeAnywhere(filter.getFilter())));
                or.add(cb.like(root.get(Absence_.pk).get(AbsenceID_.tipo), getLikeAnywhere(filter.getFilter())));
                or.add(cb.like(root.get(Absence_.causa), getLikeAnywhere(filter.getFilter())));
            }

            if (or.size() > 0) {
                predicates.add(cb.or(or.toArray(new Predicate[or.size()])));
            }

        }

        return cb.and(predicates.toArray(new Predicate[predicates.size()]));
    }

The getLikeAnywhere method just gets a string and adds "%" at the start and end of it.

The function I've tried to implement is my best effort to get a CONVERT(varchar,date_field, 3) inside a CriteriaQuery.

However, it fails and hibernate debug mode shows the next:

query +  where CONVERT(?, fecha, 3) like ? + more_query
 Incorrect syntax near '@P1'.

Almost worked, except for the first parameter where I got that ?. I tried adding more parameters in every fashionable way in the function with no result. I haven't found a way to pass the sql varchar type to it as a parameter.

Has anyone come up with this? Or solved it using another kind of function?

Thanks in advance,

Gonzalo.

Searching dates and longs with CriteriaQueries using wildcards

0 Answers0