Hibernate JPQL for multiple likes

Question

Hibernate JPQL for multiple likes

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

I'm using hibernate in my project and I'm not sure whether to use multiple dynamic likes in the search.

I have a simple field on the page where the client can enter the name of a user, but I need to be able to enter 2 names if necessary to return a list.

Example: "John" (Until then, I give a simple like in the name John, and my namedQuery looks like this:

"select c from Cliente c where c.nome like :nome"

More when the person enters 2 names, I am separating in a list of names, if he type "John Diego" I am giving a split and separating in words to find names related to the 2 words obtained.

In the question of split everything ok, what would my JPQL look like? if I create one with 2 likes, and the person typing 3 names, it would not get dynamic.

Is there any way in JPQL to give multiple likes without the need to create a namedQuery with the quality of likes I'm going to do?

EDIT: ----------

I've found a way to do more, but it's not even a right way. I would like it if someone evaluated and could help me.

map.clear();
    map.put("cidade", cidade);

    String[] palavras = pesquisa.split(" ");

    if(palavras.length <= 1){
        map.put("pesquisa", "%"+pesquisa+"%");
        anuncios = (List<Anuncio>) daoMaster.listaPorNamedQuery("efetuaPesquisa", map, Anuncio.class);
    }else{
        query = "select a from Anuncio a where a.cidade = :cidade and (a.nome = '%"+palavras[0].toString()+"'";
        for(int i=1;i < palavras.length;i++){
            query += " OR a.nome like '%"+palavras[i].toString()+"%'";
        }
        for(int i=0;i < palavras.length;i++){
            query += " OR a.descricao like '%"+palavras[i].toString()+"%'";
        }
        query +=")";
        anuncios = (List<Anuncio>) daoMaster.listaPorQueryModificada(query, map, Anuncio.class);
    }

In question, if it is only 1 word identified I am doing a search on a namedQuery already registered in the system, but if the system identifies more than one word, I am creating a query in hand.

At the end the created query was as follows:

select a from Anuncio a where a.cidade = :cidade and (a.nome = '%Kelvin' OR a.nome like '%Poket%' OR a.descricao like '%Kelvin%' OR a.descricao like '%Poket%')

Can anyone take a look and tell me if there are any scratches in the way I used them? something like sql inject or something like this?

Thank you.

java hibernate jpql

asked by anonymous 06.01.2016 / 19:49

2 answers

Transitioning images with css3 - KeyFrames How to sum the column of a table in PHP

score 1 · Answer 1

I could assign to the second parameter :nome2 the second part of the String of the same attribute , for example, get the second name after a space.

SELECT c FROM Cliente c WHERE c.nome LIKE :nome OR c.nome LIKE :nome2

Alternative

SELECT c FROM Cliente c WHERE c.nome IN (:nome, :nome2)

In the second alternative,% % does not work to get bits of string.

EDIT

Concatenate is not secure in any query, the ideal is always to pass the values by parameters.

EDIT2

A clean, secure way to pass% % to LIKE is concatenating directly in the query.

Ex:

SELECT c FROM Cliente c WHERE c.nome LIKE CONCAT('%',CONCAT(:nome, '%')) OR c.nome LIKE CONCAT('%',CONCAT(:nome2, '%'))

So you can pass the value of the parameter directly, succinctly without this string of concatenation and strings.

score 1 · Answer 2

List<Cliente> clientes = sess.createCriteria(Cliente.class)
    .add( Restrictions.in( "nome", palavras ) ).list()

Try to do so. You do a search using in instead of or , which looks up all the values you indicated.