I have a Contact entity with a telephone number field of type String. I wish to
query for Contacts that have a given telephone number. For this comparison to
succeed I have to replace some characters in the stored field ("+" by 00 and
"-" and space by an empty string). With a sql server native query I can do
something like this:
SELECT * FROM Contact WHERE
REPLACE(REPLACE(REPLACE(telephone,' ',''), '+', 00), '-', '') LIKE @phoneNumber
What would be my best option to do this with JPA?
I was thinking of one of these possibilities:
- Criteria API. Put the three REPLACE's in a user defined function. If
this is possible, call this UDF with the criteria API.
- Use a native query
- Map an entity to a database view that has a telephone column with
the characters replaced
- Always store the telephone number with the characters already
replaced
But the first three feel much too complicated for such a simple problem. The
last one is a bit too strict for my taste but I think it is the best option
unless anyone knows a better solution that I can use.
Regards,
Henno Vermeulen
Huize Molenaar
