doubt about JPQL

Question

Hey there, guys. I’m using JPQL in my project to create some database access commands and I’m having trouble manipulating the language. I’m posting the snippet of code I’m having trouble with.

public Usuario porNome(String nome) {
    Usuario usuario = null;
    try {
        usuario = this.manager.createQuery(" from Usuario where lower(nome) = :nome", Usuario.class)
                .setParameter("nome",nome.toLowerCase()).getSingleResult();
    } catch (NoResultException e) {
        // Nenhum usuario encontrado com o nome informado.
    }
    return usuario;
}

In this method, I am receiving a parameter called "name" and I am looking in the table "User" to see if I can find any record with that name. I am using this method for a search field to query users of my system.

The problem lies in the fact that with this code that I posted above, it returns only records that have exactly the same name that I type in the search field, including the upper and lower case. For example, if there is a record in my database with the name "BRUNO DE TAL", I need to type "BRUNO DE TAL" in the search field for my search to find this record. If I type only "BRUNO" or "Rune of such", it does not find the record. What I want to know is how I should rewrite my JPQL code above and make it:

• Ignore the question of upper and lower case. For example, if there is a record with the name "Bruno" in the database, I want it to be found both when I type "Bruno" and when I type "Bruno" in the search field.

• Find bank records by entering only a portion of the name. For example, if there is any bank record with the name "Bruno of such", I want this record to be found when I type "Bruno" or "of" or "such" in the search field.

I am a beginner in JPQL and I still confuse some commands with SQL. That’s why I would like to ask your knowledge.

Thank you all for posting any reply or suggestion.

Answer 1

You can resolve your need using the LIKE command, which also exists in JPQL.

And to solve the problem of mai/min, just use the Power function on both sides.

...Where Lower(name) LIKE Lower(:name)...

And when it’s time to name it:

"%" + name + "%"

However, there is a possible problem with its implementation. If for some reason there is more than one person with the same "name piece", the singleresult function will return an exception, as it will find more than one record. In this case, you must return a list.