openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Henno Vermeulen <he...@huizemolenaar.nl>
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.

Regards,
Henno

-----Oorspronkelijk bericht-----
Van: Pinaki Poddar [mailto:ppoddar@apache.org] 
Verzonden: woensdag 11 mei 2011 4:49
Aan: users@openjpa.apache.org
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,
replacedPhone,
                cb.literal(replacements[i*2]),
cb.literal(replacements[i*2+1]));
        }
        // Create a named parameter expression
        ParameterExpression<String> param = cb.parameter(String.class,
"phone");
        
        // Where clause
        Predicate whereClause = cb.like(replacedPhone, param);
        c.where(whereClause);
        
        // Bind parameter and execute
        return em.createQuery(c)
                 .setParameter("phone", phone)
                 .getResultList();
    }


-----
Pinaki 
--
View this message in context: http://openjpa.208410.n2.nabble.com/query-that-uses-String-REPLACE-function-tp6344747p6350426.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Mime
View raw message