Hibernate JPA select with parameter and multiple LIKE

Question

I'm trying to make a little spring boot service that receives a parameter and then the query will apply a like to each word in the sentence. I want to know why this works:

    @GetMapping("/getAll")
//@CrossOrigin(origins = "http://localhost:4200")
private List<PersonaNegativa> getAll(){
    List<PersonaNegativa> listneg = new ArrayList<PersonaNegativa>();
    try {
        listneg = entityManager
                .createQuery("select p from PersonaNegativa p where p.NombreCompletoFonetico like '%ARKRP%' "
                        + "or p.NombreCompletoFonetico like '%ARLNS%'",
                        PersonaNegativa.class).getResultList();
    } catch (Exception e) {
        System.out.println(e.getMessage());
    }
        return listneg;
    }

But this doesn't.

@GetMapping("/getNombre/{nom}")
private List<PersonaNegativa> getByNombre(@PathVariable String nom){
DoubleMetaphone dm = new DoubleMetaphone();
dm.setMaxCodeLen(5);
String[] nomarray = nom.split(" ");
String nomconvertido = "";
for(int i = 0; i<=nomarray.length-1; i++) {
    if(i< nomarray.length-1) {
    nomconvertido += "%" + dm.doubleMetaphone(nomarray[i])+ "% or p.NombreCompletoFonetico like ";
    }
    else {
    nomconvertido += "%" +dm.doubleMetaphone(nomarray[i]) + "%";
    }
}

System.out.println(nomconvertido);

List<PersonaNegativa> listneg = new ArrayList<PersonaNegativa>();
try {
    listneg = entityManager
            .createQuery("select p from PersonaNegativa p where p.NombreCompletoFonetico like :nom",
                    PersonaNegativa.class)
            .setParameter("nom", nomconvertido).getResultList();
} catch (Exception e) {
    System.out.println(e.getMessage());
}
    return listneg;
}

}

when printing 'nomconvertido' I get this: "%ARKRP% or p.NombreCompletoFonetico like %ARLNS%"

which should give the same result as the test above, so i'm wondering why is it different.

So go and look at the SQL that was actually invoked (in the JPA providers log) and you understand better — , Sep 14 '18 at 16:57

score 0 · Accepted Answer · answered Sep 14 '18 at 17:16

Because you can't use the named parameter to add sql. You're passing the whole set of like elements as a value, so you need to build up the JPQL the same way you do in the first instance.

I've added some sample code that I absolutely did not test to get you moving in the right direction.

@GetMapping("/getNombre/{nom}")
private List<PersonaNegativa> getByNombre(@PathVariable String nom) {
    DoubleMetaphone dm = new DoubleMetaphone();
    dm.setMaxCodeLen(5);

    int parameterCount = 0;
    StringBuilder clause = new StringBuilder();
    List<String> parameters = new ArrayList<>();
    for (String string : nom.split(" ")) {
        clause.append((clause.length() == 0) ? " p.NombreCompletoFonetico like ?" : " or p.NombreCompletoFonetico like ?");
        parameters.add("%" + dm.doubleMetaphone(string));

    }

    String jpaql = "select p from PersonaNegativa p where " + clause.toString();

    List<PersonaNegativa> listneg = new ArrayList<PersonaNegativa>();
    try {
        TypedQuery<PersonaNegativa> query = entityManager
                .createQuery(jpaql,
                        PersonaNegativa.class);
        for (int i = 0; i < parameters.size(); i++) {
            query.setParameter(i, parameters.get(i))
        }
        return query.getResultList();

    } catch (Exception e) {
        System.out.println(e.getMessage());
    }
}

Thank you very much for explaining that to me, This was exactly what I needed to know, and i appreciate you took the time to improve my code. — Flavio Alarcon, Sep 14 '18 at 17:27

Hibernate JPA select with parameter and multiple LIKE

1 Answers1