Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Passing a list as a parameter and getting the"The left parenthesis is missing" error

 
Jrcastro Ribeiro
Greenhorn
Posts: 29
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi guys, thats the code:



I get a list of names and I want to return a list that does not contain these names, but the following errors appear:


 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jrcastro Ribeiro
Greenhorn
Posts: 29
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.


Thank you for the help!

Change to this and solved the first problem :

If it is not too much to ask, what is the reason for this error, how to resolve?

 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 29
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.


i think that's correct right?


because now a getting a error when I try to get the search result

 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 29
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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


Will try!

But you can explain me why we dont need a SQL or where I insert the sql? because a still don't get it how this code work


And if I get it, I replace the "name" for my list, right?


Thanks again for losing your time trying to help this newbie
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 29
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.



Wow! That's really help!

I really have to read more about JPA. All I try to do is based on the internet examples, and this is not the best way to learn.

But his explanation was very good, now i already have a base to learn about Prepared statements

You could be a teacher, hehe, it's hard to find people on the Internet with the patience that you had to explain to me, thanks!

Can a ask a last question:

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
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 29
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.



Understood, have done everything, but he cast that erro: You have attempted to set a value of type class java.util.ArrayList for parameter names with expected type of class java.lang.String from query string

Thts the code:


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?



And thats how a grab the data from a jtable

 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


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
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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).
 
Jrcastro Ribeiro
Greenhorn
Posts: 29
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.


Yes, that was the error, a simple thing and I didn't realize.



One more time, Thank you!
 
Jrcastro Ribeiro
Greenhorn
Posts: 29
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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).


I know that makes no sense to use use the LIKE operator with IN. It was a mistake that in the rush, I didn't realize. Sorry!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic