How do you write the SQL for a PreparedStatement using a WHERE x IN clause?

Question

I have a query that looks like this:

SELECT last_name,
       first_name,
       middle_initial
FROM   names
WHERE  last_name IN ('smith', 'jones', 'brown')

I need to be able to parameterize the list in the IN clause to write it as a JDBC PreparedStatement. This list could contain any number of names in it.

Is the correct way to do this:

SELECT last_name,
       first_name,
       middle_initial
FROM   names
WHERE  last_name IN (?)

and then build a list of parameters? Or is there a better (more correct) way to do that?

Answer 1

In short, you can't out of the box. However, with Spring you can do what you want. See How to generate a dynamic "in (...)" sql list through Spring JdbcTemplate?

Answer 2

Standard SQL doesn't allow the IN clause to be parameterized into a single variable -- only dynamic SQL, the SQL query being constructed as a string prior to execution with the comma separated list of values is supported.

Answer 3

I'm going to research this topic, as well. I've been guilty of writing similar code and never felt 100% comfortable with it. I suppose I'd like to find something on "variable SQL parameter lists".

In code, using hibernate, and given a String of comma-delimited order Ids, I've used:

Session s = getSession();
Criteria crit = s.createCriteria(this.getOrderListingClass());
crit.add(Expression.sql(String.format("{alias}.orderId in (%s)", orderIds)));
crit.add(Expression.eq("status", OrderInfo.Order_STATUS_UNFILLED));
orders = crit.list();

Whereas orderId is really part of a "SELECT x FROM y WHERE IN (%s)".

1. I did run the orderIds String through a validator prior to passing it to hibernate - being fearful of injections, etc.

2. Something else that I've been meaning to do is check the limit on SQL parameters and number of characters in the query. I seem to recall hitting a limit somewhere around 2000+ (with MS SQL). That's something to consider if you go with this approach.

I think this is kludgy... to be passing off that many Ids in a Where-clause, but it's a section of code that needs refactoring. Thankfully, the use case has only seen a handful of Ids queried at any one time.

Answer 4

You could also construct your query as a stored procedure that takes the parameterized list as a varchar. For example, in sql server:

  CREATE PROCEDURE dbo.[procedure_name]

        @IN_LIST VARCHAR(MAX)
    AS
    BEGIN

        DECLARE @SQL VARCHAR(MAX)
        SET @SQL = '
            SELECT last_name,
                first_name,
                middle_initial
            FROM names
            WHERE  last_name IN (' + @IN_LIST + ')'

        EXECUTE(@SQL)
    END

Just make sure your @IN_LIST is formatted as a string that includes the single quotes and commas. For example in java:

String inList = "'smith','jones','brown'";

Answer 5

If You use MS SQL Server, try reshape your TSQL to use UDF, Maybe this my post can help You