Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Exact Matching of a String Containing a Wild Card in QueryDSL

DZone's Guide to

Exact Matching of a String Containing a Wild Card in QueryDSL

Learn more about the exact matching of a string containing a wild card in QueryDSL. It may not be as easy as you think.

· Database Zone ·
Free Resource

RavenDB vs MongoDB: Which is Better? This White Paper compares the two leading NoSQL Document Databases on 9 features to find out which is the best solution for your next project.  

On one of our recent projects, our client asked for a single search field that would be able to search on first name, last name, and email address with the only wildcard being an asterisk "*" for partial matches. Sounds simple, but it threw us for a little loop.

In our project, we used QueryDSL in combination with JPA and MySQL as the underlying relational database management system. This means that the default wildcards are "_" for one character and "%" for multiple characters.

Supporting "*" for partial matches was easy: we just replace "*" by "%" in our search string before passing it as a value to the JPA Query LIKE expression.

Our code then looked like this:

searchText = searchText.replace( "*", "%" );
query.where( user.email.like( searchText ).or(
  user.firstName.like( searchText ).or(
    user.lastName.like( searchText )
  )
) )


When searching on firstname_lastname@example.org at this point, the current query still returned two results: firstname_lastname@example.org and firstname.lastname@example.org.

The "_" wildcard needed to be escaped, of course, but as it turned out, it wasn't quite that straightforward.

As defined by the JPQLTemplates class, the default escape character is "!". This means that replacing "_" by "!_" in the search string should be enough.

searchText = searchText.replace( "_", "!_" );
searchText = searchText.replace( "*", "%" );


However, the tests showed that this wasn't the case. After some debugging and turning on SQL logging (which we turn off by default), we found out that the resulting SQL resolved to:

SELECT * FROM users WHERE email like 'firstname!!_lastname@example.org' escape '!';

Turns out the escape character itself was escaped. Initially, our Google Fu failed us a bit, but then we stumbled on this GitHub issue from 2013.

Apparently, actual escaping can only be forced by explicitly stating the escape character, even when it's the same as the default escape character.

Adding the escape character to the QueryDSL expression solved our issue:

searchText = searchText.replace( "_", "!_" );
searchText = searchText.replace( "*", "%" );
query.where( user.email.like( searchText, '!' ).or(
  user.firstName.like( searchText, '!' ).or(
    user.lastName.like( searchText, '!' )
  )
) )


This resolved to the SQL that we wanted:

SELECT * FROM users WHERE email like 'firstname!_lastname@example.org' escape '!';

It also generated the search results that fulfilled the requirement of our client: firstname_lastname@example.org

This is one we’ll never forget!

Do you pay to use your database? What if your database paid you? Learn more with RavenDB.

Topics:
java ,query ,query builder for mysql ,database ,querydsl ,dsl ,tutorial

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}