Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 406
jp-jee Avatar asked Jul 28 '14 13:07

jp-jee


People also ask

What is CriteriaBuilder in JPA?

CriteriaBuilderJPA interfaceUsed to construct criteria queries, compound selections, expressions, predicates, orderings. See JavaDoc Reference Page... interface serves as the main factory of criteria queries and criteria query elements. It can be obtained either by the EntityManagerFactory. persistence.

Is Criteria API deprecated?

The Criteria API allows us to build up a criteria query object programmatically, where we can apply different kinds of filtration rules and logical conditions. Since Hibernate 5.2, the Hibernate Criteria API is deprecated, and new development is focused on the JPA Criteria API.

What is CriteriaBuilder in Hibernate?

public interface CriteriaBuilder. Used to construct criteria queries, compound selections, expressions, predicates, orderings. Note that Predicate is used instead of Expression<Boolean> in this API in order to work around the fact that Java generics are not compatible with varags.

What is expression in JPA?

Query expressions are the foundations on which JPQL and criteria queries are built. Every query consists of clauses - SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY, and each clause consists of JPQL / Criteria query expressions.


2 Answers

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().

like image 128
zbig Avatar answered Sep 17 '22 15:09

zbig


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.

like image 40
Simon B Avatar answered Sep 19 '22 15:09

Simon B