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?

You could always dynamically generate the SQL and adding the clauses if you need it. The longer answer is to use JPA or other framework to build "Predicates" or "Criteria" queries. — JustinKSU
– JustinKSU, Commented Mar 8, 2018 at 16:39
@JustinKSU Yeah it doesn't seem like there's a pretty way to do this, so I'm just loading a simple "base query" and adding my filters dynamically to it before executing the query and that seems to be working. Thanks for your help. — Evan G
– Evan G, Commented Mar 8, 2018 at 18:00

Serena C · Accepted Answer · 2018-03-08 18:34:41Z

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();

}

Collectives™ on Stack Overflow

How to handle optional parameter used in SQL query IN clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related