Search for a property in Hibernate after aplying a function.

Question

In my db I store telephone numbers of things as input by the user (I want to let user decide how he format their phone number)

when users search for a phone number they most likely wont format the number in a way that I can just compare the two strings. Even 'like' wont do the trick since maybe the number has parenthesis or some other usual phone separator in the middle.

I want to be able to compare each telephone on the DB with with my search criteria after applying a filter and comparing the results. So far the filter I want to apply is this one:

private String numberFilter(String num){
    num = num.replace("+", "00");
    num = num.replace("(", "");
    num = num.replace("(", "");
    num = num.replace("-", "");
    num = num.replace(" ", "");
    if (num.contains("ext"))
        num = num.substring(0, num.indexOf("ext"));
    return num;
}

And what I'm doing to compare the telephones (still missing the filter)

public List<Entity> getEntityByTelephone(String telephone) {
    DetachedCriteria criteria = DetachedCriteria.forClass(Entity.class);
    criteria.createAlias("telephones", "tl", CriteriaSpecification.INNER_JOIN);
    criteria.add(Restrictions.like("tl.number", telephone));
    return (List<Entity>) getHibernateTemplate().findByCriteria(criteria);
}

So help on a way to use the filter above as a comparator in hibernate would be appreciated

Answer 1

I would suggest you use regular expressions to query the stored numbers. I would also suggest that the numbers be stored in a standard format, but I understand you want to keep the format that the user provided originally.

For instructions on using different regex queries with Hibernate, see here.

You will probably need to use Restrictions.sqlRestriction() because Hibernate does not support the rlike operator natively in the Restrictions API.

Answer 2

I don't know how to achieve exactly what you want (and I wonder about the performances) and actually, I would implement things differently.

If you really want to let the user enter phone numbers as he likes, then just store the user input but also a normalized version and perform the search on the normalized input.

For the normalization, I would use Google's library for dealing with phone numbers which seems to the perfect library for the job. Storing a PhoneNumber might require implementing a UserType but that's not a big deal.