• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to pass java List to SQL Update Statement

 
Omar Ali
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I need your help in passing the values which are stored in a java <List> to an update Statement to update the flag for all the records in the database table which are available the list.

The selected records are stored in below List:



The Car Class has (carID as int and carName as String)

And I need to fire an Update Statement that will update the flag to Yes for all the selected cars in the method:



So how can I pass the List in the Where Clause?
 
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
Omar Ali wrote:So how can I pass the List in the Where Clause?

You need a statement likeSo you need to create the 1, 2, 3 part using your list of cars. That can't be hard to do...

Or you find many alternatives here and here.

Best of luck!
Kind regards,
Roel
 
Omar Ali
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your reply. However, I am still unclear on what to do.

In the described case, I can't pass the list to the SQL update statement as I need to know the values but in my case I do not know:


I tried it with the above way, but I wasn't success. So can you please guide me more.
 
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
Omar Ali wrote:In the described case, I can't pass the list to the SQL update statement as I need to know the values but in my case I do not know:

I assume selectedCars is a list containing all cars (with their unique id) which flag should be updated. Correct?

If it is, I have the following assignment for you: can you write me the code to print the ids of these cars to standard output (using System.out.println), ids have to be seperated by a comma.
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Omar Ali wrote:Thanks for your reply. However, I am still unclear on what to do.

In the described case, I can't pass the list to the SQL update statement as I need to know the values but in my case I do not know:


I tried it with the above way, but I wasn't success. So can you please guide me more.


Dynamic SQL is inherently insecure and open to SQL injection, so, it's best not to use variables in a SQL statement. Instead, to pass a list in a SQL statement, use comma separated values and and the statement itself can change them into separate values. The way to do this may vary by RDBMS, but a recursive CTE ought to work in most of them.

You can also use a stored procedure to separate the comma separated values. A search for your RDBMS and commas ought to show you what to do. Or, you can do a less efficient but easier to code method, of preparing the sql statement and calling execute() inside a loop, once per value.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic