mysql query parameter string array

Question

I am trying to pass a parameter to a query, rather than write copious text I have narrowed it down to this simple explanation. The frament I am trying to insert into is

where pkw_0.keyword in (:kwd)

I have used a String[] to construct a string of the form vals="'AVal','BVal'" which I pass to the query using setParameter("kwd",vals); The query returns zero results. However if I construct the query by hand and use the mysql console the query returns 1 result which is expected.

So I am assuming that either a single string is incorrect for the parameter or there is some conditioning of the values that I need to do prior to passing them via the setParameter call.

eggyal · Accepted Answer · 2012-09-17 10:02:42Z

1

Each parameter can only represent a single literal value. You will need to create multiple placeholders in your prepared statement (one for each value) and then provide each value to MySQL as a separate parameter.

answered Sep 17, 2012 at 10:02

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jeff Lowcock Over a year ago

Hmmm, the problem is I have a variable number of values so I cannot predict how many or even what the maximum number of values there will be. So I think you are saying that given these conditions I cannot substitute into the '('...')' in a setParameter, do you have any suggestions for an alternative (other than attempting to preset the number of values)

eggyal Over a year ago

@JeffLowcock: What language are you using? Why can you not count the number of elements in your array?

Jeff Lowcock Over a year ago

It is all java - it is not the counting of the arguments that is the problem. I have simplified this (perhaps too far) I am actually using a namedNativeQuery with the associated SqlResultMapping so dynamically creating this is probably not possible. Hence the original approach - which may simply not be possible.

Jeff Lowcock Over a year ago

I found a link here coderanch.com/t/415118/ORM/databases/… that is right on target the solution is to pass to setParameter a List<String> which then is managed to create the correct query and I get the results that I expect. So thanks for the clues and assist and we have both learnt something new today.

Collectives™ on Stack Overflow

mysql query parameter string array

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related