Roel De Nijs wrote:Your query is wrong!
Have a look at the WHERE Clause section in the Java Persistence WikiBook to learn how you have to use the IN operation.
Roel De Nijs wrote:Your query is still wrong for your use case You should have a closer look at the examples given about the IN operation (just beneath the Comparison operations table), and use the appropriate one for your use case.
Jrcastro Ribeiro wrote:i think that's correct right?
Roel De Nijs wrote:
Jrcastro Ribeiro wrote:i think that's correct right?
No, unfortunately it isn't.
You want to use a list of values, so you are looking for a condition likeAnd then you need to set the list as a query parameter, like
Hope it helps!
Kind regards,
Roel
Jrcastro Ribeiro wrote:But you can explain me why we dont need a SQL or where I insert the sql?
Jrcastro Ribeiro wrote:And if I get it, I replace the "name" for my list, right?
Roel De Nijs wrote:
Jrcastro Ribeiro wrote:But you can explain me why we dont need a SQL or where I insert the sql?
It seems from the code snippets you have provided, you are using JPA. Then you always have the option to use native SQLor to use JPQLThis JPQL query will be transformed by your persistence provider (e.g. Hibernate) into a native SQL query based on your entity mappings and the database dialect you have defined.
Jrcastro Ribeiro wrote:And if I get it, I replace the "name" for my list, right?
You could. But you can not simply concatenate the list with your query as you have done several times. This will not work at all! It's much more complicated.
Again you have 2 options: either use a regular statement or use a prepared statementWith a prepared statement you provide one fixed query and replace the parameters just before executing the query. So let's say we need to use a query for values 4, 5, and 6 as well. Using a regular statement, you'll create a new queryBut using a prepared statement you use exactly the same query and just provide other parameters to replace the placeholders (question marks). Prepared statements will have a better performance than regular statements, because you reuse an existing query so the database doesn't have to calculate the execution plan every time.
And the query I provided is an example of a prepared statement. But instead of an anonymous placeholder, I used a named placeholder (:names). The next line in the code snippetwill replace the named placeholder with the list containing the names.
Hope it helps!
Kind regards,
Roel
PS. Being a newbie, you should definitely read some basic tutorials about sql querying and jpa basics. It will give you a much better understanding and make you a better developer.
Jrcastro Ribeiro wrote:Can a ask a last question:
Jrcastro Ribeiro wrote:I change my method to receive a list of strings and return a list of Integers: The idea is to get a list with the names and return their respective ID's
Roel De Nijs wrote:
Jrcastro Ribeiro wrote:Can a ask a last question:
Sure!
Jrcastro Ribeiro wrote:I change my method to receive a list of strings and return a list of Integers: The idea is to get a list with the names and return their respective ID's
That's a really easy one. And probably you could find this one yourself if you would have looked at the javadoc for the createQuery method. The second parameter is the type of the query results. Now it's String.class but you want integers. So simply change to Integer.class. Doing so will require you to change the generic type of TypedQuery to Integer as well. And the compiler error will be gone
Hope it helps!
Kind regards,
Roel
PS. If you are just reading from the database, I don't think it's very useful to start (and commit) a transaction. That's only useful when you are modifying stuff.
Jrcastro Ribeiro wrote:The error seems to be in the query parameter. From what I understood, the "name" is just a parameter that makes reference to the list. Maybe i have to say that this parameter is a Integer?
Dave Tolls wrote:
The LIKE keyword in SQL is to compare against a single String parameter, something like '%FOO%', where '%' is the wildcard (not sure if that's a standard wildcard, but it's the one Oracle uses).
You were using IN before, which is used to check against an array of values.
Roel De Nijs wrote:
Jrcastro Ribeiro wrote:The error seems to be in the query parameter. From what I understood, the "name" is just a parameter that makes reference to the list. Maybe i have to say that this parameter is a Integer?
No! You should (like Dave already said) use the appropriate operator. I don't have any clue why you have changed it to LIKE instead of IN
Maybe you should really start reading some SQL tutorial first, instead of doing things you actually have no clue about. The W3Schools SQL tutorial could be a good starting point. It provides examples in each chapter which you can try yourself. You could even edit them and run them again using nothing but your favourite browser (so no additional software needs to be installed).
This will take every ounce of my mental strength! All for a tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
|