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:[email protected]] Verzonden: woensdag 11 mei 2011 4:49 Aan: [email protected] 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.
