openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Henno Vermeulen <he...@huizemolenaar.nl>
Subject query that uses String REPLACE function
Date Mon, 09 May 2011 15:58:02 GMT
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

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message