CODEWITHSUNDEEP
javamysqlregexhibernatejpa
jp-jee
jp-jee

Reputation: 1523

Use Regular Expressions in JPA CriteriaBuilder

I'm using the JPA CriteriaBuilder to select entities of type MyEntity from a MySQL db as follows:

String regExp = "(abc|def)"
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery query = cb.createQuery( MyEntity.class );
root = query.from( MyEntity.class );
predicates = new ArrayList<Predicate>();

predicates.add( cb.like( root.<String>get( "name" ), regExp ) );

Thus, the query result should contain any entity where the name value matches the given regExp. But the result list is always empty. Changing the regExp to /(abc|def)/g has no effect, neither does adding the wildcard %

How to make the pattern matching work?

Alternatively: How can I use native MySQL REGEXP together with the CriteriaBuilder?

Upvotes: 12

Views: 25109

Answers (4)

witek
witek

Reputation: 351

If you are using Postgres you can combine array_length and regexp_match functions, by invoking criteriaBuilder.function() to generate sql like this:

... where array_length(regexp_match(my_field,'<my_regexp_pattern>'),1)>0

the > 0 condition checks if there is a match for the my_regexp_pattern. If you want to verify that my_regexp_pattern does not match use:

regexp_match(my_field,'<my_regexp_pattern>') is null

Upvotes: 0

Simon B
Simon B

Reputation: 1844

I came across this recently and used the first post to implement the hibernate mysql function option.

To help save some time for others this is what I did:

set up the function in your custom dialect file in hibernate: 

public class MySQLDialect extends Dialect {

   public MySQLDialect() {
      super();
      ...
      registerFunction("regexp", new SQLFunctionTemplate(StandardBasicTypes.INTEGER, "?1 REGEXP ?2"));
      ...
   }
   ...
}

then within the criteria builder section:

CriteriaBuilder builder = ...;    

Pattern regexPattern = Pattern.compile("^[0-9]\\|[0-9]+");

Expression<String> patternExpression = builder.<String>literal(regexPattern.pattern());

Path<String> path = ... ;// regex comparison column

// regexp comes from the name of the regex function 
// defined in the Mysql Dialect file above
Predicate theRegexPredicate = builder.equal(builder.function("regexp", Integer.class, path, patternExpression), 1);

Then use theRegexPredicate to construct the where clause in your CriteriaBuilder query.

Upvotes: 5

LinuxLars
LinuxLars

Reputation: 4038

Maybe this snippet will help. We had to exclude characters in a search, and we using Oracle. CriteriaBuilder (at least as of 2.1) will let you call a function.

private static final Pattern UNDESIRABLES = Pattern.compile("[(){},.;!?<>%_-]");
private static final String UNDESIRABLE_REPLACEMENT = "";
...

In the search method, create a Predicate to use in your where clause:

Expression<String> undesirables = cb.literal(UNDESIRABLES.toString());
Expression<String> replaceWith = cb.literal(UNDESIRABLE_REPLACEMENT);
Expression<String> regExp = cb.function("REGEXP_REPLACE", String.class, client.get(Client_.companyName),
    undesirables, replaceWith);
Predicate companyNameMatch = cb.equal(cb.trim(cb.lower(regExp)), removeUndesireables(name).trim());
...

And create a method for the right hand comapare that uses the same values as the left:

private String removeUndesireables(String name) {
    return UNDESIRABLES.matcher(name).replaceAll(UNDESIRABLE_REPLACEMENT).toLowerCase();
}

Upvotes: 2

zbig
zbig

Reputation: 3956

Pattern matching in JPA queries is limited only to

  • _ - any character
  • % - any string

REGEXP has operator syntax in MySQL (SELECT 'a' REGEXP 'A') so it cannot be used with CriteriaBuilder.function() API. I'm afraid the best is to run native SQL query.

If you are using Hibernate you have one more option. You can wrap REGEXP operator in SQLFunctionTemplate, extend hibernate dialect and run with CriteriaBuilder.function().

Upvotes: 9

Related Questions