openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Henno Vermeulen <>
Subject RE: query that uses String REPLACE function
Date Wed, 11 May 2011 09:12:24 GMT
Oh wow thank you for your elaborate help, I feel kind of bad for taking your time because I
already fixed my problem using native queries.

I'll record this help in case we need something similar in the future or when I need to adjust
my code and native queries become less maintainable.


-----Oorspronkelijk bericht-----
Van: Pinaki Poddar [] 
Verzonden: woensdag 11 mei 2011 4:49
Onderwerp: RE: query that uses String REPLACE function

> I guess the only drawback is that I need to learn some new API :). 

Hope the following may help to  get started :)

  public List  queryPhoneNumberWithReplacements(String phone) {
        // even-index element is replaced by next odd-index element
        // must contain even number of elements in total
        String[] replacements = {
            "+", "00", 
            " ", "", 
            "-", ""};
        // a CriteriaBuilder is the factory for Criteria and Expressions
        CriteriaBuilder cb = em.getCriteriaBuilder();
        // A Criteria query is created with intended result type as argument 
        CriteriaQuery  c = cb.createQuery(A.class);
        // This is the from clause
        Root  a = c.from(A.class);
        // An expression based on persistent attribute.
        // Using the String based (not strictly typed) version of the API
        // Still no casting is required
        Expression<String> replacedPhone = a.<String>get("telephone");
        // Build an expression using a database function
        // that replaces one string by another, repeatedly
        String databaseFunction = "REPLACE";
        for (int i = 0; i < replacements.length/2; i++) {
            replacedPhone = cb.function(databaseFunction, String.class,
        // Create a named parameter expression
        ParameterExpression<String> param = cb.parameter(String.class,
        // Where clause
        Predicate whereClause =, param);
        // Bind parameter and execute
        return em.createQuery(c)
                 .setParameter("phone", phone)

View this message in context:
Sent from the OpenJPA Users mailing list archive at

View raw message