How to handle optional parameter used in SQL query IN clause

Question

I'm currently constructing an SQL query in a Java web service using a PreparedStatement. My service takes in multiple optional parameters to filter this query, including one of the filters being an array of ids. I've currently accounted for the optional parameters being null (unspecified) by checking the values with nvl(), but this is not working for an IN clause.

My query currently looks like this:

SELECT
    a.item_type
    a.item_flag
    a.item_id
FROM tableA a
WHERE
    a.item_type = nvl(?, a.item_type)
    AND a.item_flag = nvl(?, a.item_flag)
    AND a.item_id IN nvl(?, a.item_id)

And I'm setting my prepared statement values with:

private void assignStatementValues(final PreparedStatement statement,
        final String itemType, final int itemFlag,
        final List<Long> itemIds) throws SQLException {
    Integer itemFlag;
    if (Strings.isNullOrEmpty(itemType)) {
        statement.setNull(1, java.sql.Types.VARCHAR);
    } else {
        statement.setString(1, itemType);
    }
    if (itemFlag == null) {
        statement.setNull(2, java.sql.Types.INTEGER);
    } else {
        statement.setInt(2, itemFlag);
    }
    if (itemIds == null) {
        statement.setNull(3, java.sql.Types.ARRAY);
    } else {
        statement.setArray(3, statement.getConnection().createArrayOf("bigint", itemIds.toArray()));
    }

    statement.executeQuery();
}

Currently my query works with the optional parameters when the "AND...IN" clause is removed, but I receive a 500 response when the "AND...IN" clause is present. Is there a better way to structure my query for the optional list/array parameter?

Answer 1

Building your query dynamically is the way to go, I would expect. Criteria can work if you have a base object, and hibernate filters are amazing. A simple query generation could look something like this:

private final String the_Query = "SELECT a.item_type, a.item_flag, a.item_id FROM tableA a";

private String addWhereClause(String whereClause, boolean whereClauseAdded){
    String returnValue = "";

    if (!whereClauseAdded){
        returnValue = " where " + whereClause;
    }else{
        returnValue = whereClause;
    }

    return returnValue;
}

private StringBuilder generateQuery(String itemType, int itemFlag, List<Long> itemIds){
    StringBuilder b = new StringBuilder();
    b.append(the_Query);

    boolean whereClauseAdded = false;
    String whereClause = "";

    if (itemType != null){
        whereClause = " a.item_type = " + itemType;
        b.append(addWhereClause(whereClause, whereClauseAdded));
        whereClauseAdded = true;
    }

    if (itemFlag <> 0){ // itemFlag can never be null. int's are either set or 0.
        whereClause = " a.item_flag = " + itemFlag;
        b.append(addWhereClause(whereClause, whereClauseAdded));
        whereClauseAdded = true;
    }

    if (itemIds != null && itemIds.size() > 0){
        String inList = "";
        for (Long id : itemIds){
            if (inList == null){
                inList = " " + id;
            else
                inList = ", " + id;
        }


        whereClause = " a.item_id in (" + inList + ")";
        b.append(addWhereClause(whereClause, whereClauseAdded));
    }   

    return b;

}

private void executeQuery(Connection connection, String itemType, int itemFlag, List<Long> itemIds) throws SQLException{

    PreparedStatement statement = connection.prepareStatement(this.generateQuery(itemType, itemFlag, itemIds));
    statement.executeQuery();

}