How to mount a generic query using jpa nativequery

Asked

Viewed 383 times

0

Consider the following table:

table: info_people

||Row ||first name||last name ||nationality ||time_do_heart
|1|Marcelo |aragao |brazil |sp
|2 |maria |Fonseca |brasil |sp
|3 |Joao |timotio |brazil |palm trees
|4 |Rcelo |Vasconcelos |brasil |Corinthians

Imagine that I have a form with all these fields, where none of them is required. I need to take into account only the fields typed by the user, so if the user fills the field nationality = brazil and type_do_heart = sp, the application should bring lines 1 and 2.

in my jpa application I did the following:

em.createNativeQuery ("select nome from info_pessoas where nome = ? and sobrenome = ? and nacionalidade = ? and time_do_coracao = ?")

BS: the "?" will be filled with the values typed by the user, in this case the 3 and the 4 "?" were filled with Brazil and sp respectively.

The problem is that no line is being returned, which is the right way to mount the query?

sql jpa

1 answer

0

Felipe,

When creating a nativeQuery think about how you would do it directly in the database, that is, what works in your SQL will work in createNativeQuery. It works:

    em.createNativeQuery("select nome from info_pessoas where nacionalidade = 'Brasil' and time_do_coracao = 'SP'").getResultList();

  • Thanks for your attention, however I need to assemble Quey’s Where clause dynamically, so the above example would not work.

  • Felipe, the truth is that to dynamically assemble a query we would have that is concatenating string, and this is not a good practice. If you want I have an example with Hibernate Criteria. In your projects you use Maven?

  • Felipe, you could also use JPQL that will be but easy yet. Look, I didn’t put an example of this here because your question was for a Nativequery. Ask another question like this: "How to mount a generic query using jpa".

Browser other questions tagged sql jpa

You are not signed in. Login or sign up in order to post.