0

I want to pass a List of String coming from request parameter to the preparedStatement.setObject() as a single parameter.Here I have coverted list of Objects to a single String.

So while passing this converted String to setObject method it is converting ' to \'.

So my query is like : 

select * from category where category IN (?)

  for (int counter = 0; (!sqlParams.isEmpty()) && counter < sqlParams.size(); counter++) {
     System.out.println(sqlParams.get(counter)); 
     stmt.setObject(counter + 1, sqlParams.get(counter));
     System.out.println(stmt.toString());
}

here sqlParams.get(counter) is giving following value to me.

'Adult', 'Classic', 'Fantasy', 'Mystery'

but wen i am using stmt.setObject(), and printing the values of stmt, it is showing following value :

'\'Adult\', \'Classic\', \'Fantasy\', \'Mystery\''

So at query formation is something like this :

SELECT * FROM mytable WHERE category IN ('Adult\', \'Classic\', \'Fantasy\', \'Mistry\'');

There are other ways to solve these approach too, such as passing individual String and then query execution for each individual String.but It will increase time complexity for my code. Can anyone Suggest me the solution for this?

MankitaP
  • 57
  • 1
  • 1
  • 7
  • 2
    Why do you care about the return value of `PreparedStatement.toString()`? What do you mean by "they are increasing time complexity for my code"? I would be shocked if this loop became a bottleneck in your code. On the other hand, I don't think your code actually does what you want it to - you're only specifying a single parameter in your SQL, but then you're setting multiple parameter values... – Jon Skeet Aug 23 '13 at 13:37
  • 1
    Possible duplicate of [PreparedStatement IN clause alternatives?](http://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives) And get yourself over that "time complexity". It's not making any sense. – BalusC Aug 23 '13 at 13:39
  • Time complexity in java is virtually synonymous with performance. – Jeremy Johnson Aug 23 '13 at 13:56
  • @Jon Skeet: I think you are not getting question only.preparedStatement.toString() is used just to test the value which it is taking at that line. And here time complexity means I don't want to fire the query to my database multiple times though I can make use IN clause.I am concern specially for this because I dont want unnecessary processing during code execution. – MankitaP Sep 22 '13 at 17:32
  • @MankitaP: Why do you believe that calling `peparedStatement.toString()` will execute the query each time? Your question is *very* unclear, IMO. – Jon Skeet Sep 22 '13 at 17:49
  • No Its not unclear.But you are not getting it correctly.I simply stated the answers of some lines too in my question. And testing does not mean to execute query each time.It means debugging the value at some lines. Do not avoid my S.O.P statement. – MankitaP Sep 22 '13 at 18:24

1 Answers1

3

JDBC prepared statements cannot be used when the number of parameters is variable, like here. The ? in the query is expanded into a single value, not to several separated by commas.

What you can do is construct an sql with the appropriate number of ?s and then set each parameter in a loop.

Joni
  • 108,737
  • 14
  • 143
  • 193
  • I have come up with the same solution. But the problem is that I wil have to execute a for loop for appending " ? " to my query based on number of objects of my List....And I dont want to reduce time complexity as much as possible. – MankitaP Aug 23 '13 at 13:48
  • 1
    Use StringBuilder. The time complexity is amortized O(n). Less is impossible. – Joni Aug 23 '13 at 18:00
  • And I want to reduce time complexity* Well I know less than o(n) is impossible.but still I think this solution is better than querying DB for multiple time...Thank you. – MankitaP Sep 22 '13 at 17:36
  • @MankitaP: If you're really concerned with how long it takes to build up a string with the right number of question marks when you're going to end up executing a database query, I think you need to think very carefully about relative performance. It's like worrying about making multiple trips between the house and your driveway to pack the car when you're about to drive 1000 miles. – Jon Skeet Sep 22 '13 at 17:51