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?

cralfaro · Accepted Answer · 2022-03-16 16:16:16Z

3

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!

answered Mar 16, 2022 at 16:16

cralfaro

5,9463 gold badges23 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CharliePrm88 Over a year ago

Since Hibernate 6.1 this won't work anymore. Please watch this: discourse.hibernate.org/t/migration-of-dialect-to-hibernate-6/…

Collectives™ on Stack Overflow

How to use CriteriaBuilder to call db function

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related