HQL query ignoring accents

I've the following query:

select u from User u where upper(u.name) like upper(?1)

How this would be, but ignoring the accents in u.n开发者_如何学Pythoname and ?1?

I have found a way to implement this. Basically there are 2 steps:

1. normalize the parameter (in your case 1) and
2. normalize the data from the DB

Example

public User getByName(String paramName) {
   .....
}

Original HQL Query :

String queryString = "select u from User u where u.name : =paramName";

New Query:

paramName = normalizer(paramName );

String queryString = "select u from User u where " + replacer(u.name) +" : =paramName";

And the 2 methods:

public static String normalizer(String s)  {
    return Normalizer.normalize(s, Normalizer.Form.NFD).replaceAll("[^\\p{ASCII}]", "");
}

public String replacer(String param) {
    String t1 = "àâäçéèëêùûüôöïî";
    String t2 = "aaaceeeeuuuooii";
    String s = param;
    for (int i = 0; i < t1.length(); i++) {
        s = "replace(" + s + ",'" + t1.charAt(i) + "','" + t2.charAt(i) + "')";
    }
    return s;
}

What database are you using? In most of them you can send a specific SQL command to set the collation.

In portuguese, i missed the accents in some characters, so i added more characters in the replacer method:

public String replacer(String param) {
    String t1 = "áãàâäçéèëêùûüúóôöïîíÁÀÂÄÃÇÉÈËÊÙÛÜÚÓÔÖÏÎÍ";
    String t2 = "aaaaaceeeeuuuuoooiiiAAAAÃCEEEEUUUUOOOIII";
    String s = param;
    for (int i = 0; i < t1.length(); i++) {
        s = "replace(" + s + ",'" + t1.charAt(i) + "','" + t2.charAt(i) + "')";
    }
    return s;
}

With CriteriaBuilder you can use the following Specification

public static Specification<User> byNameIgnoringCaseAndAccents(String paramName) {

    return (root, cq, cb) -> {
        String paramNameNormalized = paramName != null ? normalize(paramName.toLowerCase()) : null;

        String characters = "áãàâäçéèëêùûüúóôöïîíÁÀÂÄÃÇÉÈËÊÙÛÜÚÓÔÖÏÎÍ";
        String replacements = "aaaaaceeeeuuuuoooiiiAAAAÃCEEEEUUUUOOOIII";
        Expression<String> replacedName = root.get("name");
        for (int i = 0; i < characters.length(); i++) {
            replacedName = cb.function("REPLACE", String.class, replacedName, cb.literal(characters.charAt(i)), cb.literal(replacements.charAt(i)));
        }
        replacedName = cb.lower(replacedName);

        return cb.like(cb.literal(paramNameNormalized), cb.concat("%", cb.concat(replacedName, "%")));
    };
}

public static String normalize(String s) {
    return s != null ? Normalizer.normalize(s, Normalizer.Form.NFD).replaceAll("[^\\p{ASCII}]", "") : s;
}