How to use CriteriaBuilder to call db function

Question

I have a string that might eventually contain spaces. I would like to replace those spaces with a regex that matches against \t,\r,\n. After replacing I would like to call regexp_like (an oracle function) to match a field against this string.

I know it is possible to call db functions using criteria builder as described for example in this link

I am not very familiar with the difference between regex in java versus oracle or how to cobble this together (I have never called functions from criteriabuilder). Here are my tentative steps with places where I am stuck in the comments

// first replace all spaces with regex for \s,\r,\t, value is the original string
value.replaceAll(" +", "[\\t\\n\\r]+")
// build the db function call expression, seems I cant do table.<String>get(field) and cant pass value as a string
Expression<String> regExp = cb.function("regexp_like", String.class, table.<String>get(field), value);
// now create a predicate not sure how
Predicate fieldMatch = cb.equal(...)

Is this possible?

Answer 1

Its possible. You only need to do few small changes.

Extends your Oracle dialog

public class Oracle10gCustomDialect extends Oracle10gDialect {

public Oracle10gCustomDialect() {
    super();
    registerFunction("regexp_like", new SQLFunctionTemplate(StandardBasicTypes.BOOLEAN,
            "(case when (regexp_like(?1, ?2)) then 1 else 0 end)"));
}

}

Then use this path at your application.properties or hibernate properties config.

Then in your specification, need to do something like this.

Expression<Boolean> regExprLike = criteriaBuilder.function("regexp_like", Boolean.class, root.get("yourColumn"), criteriaBuilder.literal("<your regexp value>"));
predicates.add(criteriaBuilder.isTrue(regExprLike));
...

And thats all!