1

I need to do a large SQL query against a MySQL DB using a List of strings in Java, ex:

SELECT * FROM db_table WHERE id IN (....);

Unfortunately the input API is sort of limiting here and I am only given a list of strings as the input.

Normally I would just use a parameterized query, but that won't allow me to pass a comma delimited list so something like the following won't work:

SELECT * FROM db_Table WHERE id IN (?);
preparedStaement.Set(1, "A,B,C,D,E");

I took a look at this thread: PreparedStatement IN clause alternatives?

And unfortunately none of the more creative options will work since I don't have write access to the database, so I can't create a temporary table.

So - any ideas? I only care about MySQL as the underlying source, so if the solution is specific to MySQL that's fine.

Thanks in advance

Edit: Escaping each string individually would be fine -- but what method does that in Java? I haven't had very much luck finding one, I only see a few things from apache, which I can't use.

-- Dan

Community
  • 1
  • 1
debracey
  • 6,517
  • 1
  • 30
  • 56
  • 1
    So also my answer in the linked topic didn't help? Another dupe by the way: http://stackoverflow.com/questions/2861230/what-is-the-best-approach-using-jdbc-for-parameterizing-an-in-clause – BalusC Mar 03 '11 at 03:19
  • I did see that -- I was hoping I wouldn't have to do it that way -- but I guess since there is no better answer -- I'll go with that. – debracey Mar 03 '11 at 14:21

1 Answers1

2

You have two obvious options:

  1. Build the query dynamically as well as its inputs. That is, for each string you need to include, add a ? to the query before you pass it to the PreparedStatement.

    SELECT * FROM table WHERE id = ? OR id = ? OR id = ? OR id = ? ...

  2. Don't use binding here. Escape the strings individually and put them directly in the query.

...and probably some less obvious options I haven't thought of.

Dan Grossman
  • 51,866
  • 10
  • 112
  • 101
  • Escaping the strings individually would be fine -- but is there some method to do that in Java? I could write my own -- but I have a feelign there's a lot of corner cases – debracey Mar 03 '11 at 02:57