StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>An analysis of the various options available, and the pros and cons of each is available <a href="http://www.javaranch.com/journal/200510/Journal200510.jsp#a2" rel="noreferrer">here</a>.</p> <p>The suggested options are:</p> <ul> <li>Prepare <code>SELECT my_column FROM my_table WHERE search_column = ?</code>, execute it for each value and UNION the results client-side. Requires only one prepared statement. Slow and painful.</li> <li>Prepare <code>SELECT my_column FROM my_table WHERE search_column IN (?,?,?)</code> and execute it. Requires one prepared statement per size-of-IN-list. Fast and obvious.</li> <li>Prepare <code>SELECT my_column FROM my_table WHERE search_column = ? ; SELECT my_column FROM my_table WHERE search_column = ? ; ...</code> and execute it. [Or use <code>UNION ALL</code> in place of those semicolons. --ed] Requires one prepared statement per size-of-IN-list. Stupidly slow, strictly worse than <code>WHERE search_column IN (?,?,?)</code>, so I don't know why the blogger even suggested it.</li> <li>Use a stored procedure to construct the result set.</li> <li>Prepare N different size-of-IN-list queries; say, with 2, 10, and 50 values. To search for an IN-list with 6 different values, populate the size-10 query so that it looks like <code>SELECT my_column FROM my_table WHERE search_column IN (1,2,3,4,5,6,6,6,6,6)</code>. Any decent server will optimize out the duplicate values before running the query.</li> </ul> <p>None of these options are super great, though.</p> <p>Duplicate questions have been answered in these places with equally sane alternatives, still none of them super great:</p> <ul> <li><a href="https://stackoverflow.com/questions/3107044/preparedstatement-with-list-of-parameters-in-a-in-clause">PreparedStatement with list of parameters in a IN clause</a></li> <li><a href="https://stackoverflow.com/questions/1305240/how-to-set-list-of-parameters-on-prepared-statement">How to set list of parameters on prepared statement?</a></li> </ul> <p>The Right Answer, if you are using JDBC4 and a server that supports <code>x = ANY(y)</code>, is to use <code>PreparedStatement.setArray</code> as described here:</p> <ul> <li><a href="https://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives/10240302#10240302">PreparedStatement IN clause alternatives?</a></li> </ul> <p>There doesn't seem to be any way to make <code>setArray</code> work with IN-lists, though.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload