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 asterix ‘*’ 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, '!' ) ) ) )
resolving to the SQL that we wanted:
SELECT * FROM users WHERE email like 'firstname!_lastname@example.org' escape '!';
and generating the search results that fulfilled the requirement of our client: firstname_lastname@example.org
This is one we’ll never forget!
| Published on Java Code Geeks with permission by Andy Somers, partner at our JCG program. See the original article here: Exact Matching of a String Containing a Wild Card in QueryDSL Opinions expressed by Java Code Geeks contributors are their own. |
