java - 如何处理 SQL 查询 IN 子句中使用的可选参数

Question

我目前正在使用 PreparedStatement 在 Java Web 服务中构建 SQL 查询。我的服务采用多个可选参数来过滤此查询，其中一个过滤器是一个 id 数组。我目前已经通过使用 nvl() 检查值来说明可选参数为 null(未指定)，但这不适用于 IN 子句。

我的查询目前看起来像这样:

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)

我正在设置准备好的语句值:

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

目前，当删除“AND...IN”子句时，我的查询使用可选参数，但当存在“AND...IN”子句时，我收到 500 响应。有没有更好的方法来构造我对可选列表/数组参数的查询？

Answer 1

我希望动态构建您的查询是可行的方法。如果你有一个基础对象，标准就可以工作，而且 hibernate 过滤器非常棒。一个简单的查询生成看起来像这样:

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

}