oracle传入一个可能为空的参数进行查询

在我们数据库的表中的某些字段可能为空，且传入的查询参数也可能为空。

例如

select psm.mapping_id,psm.party_id,psm.vendor_id,psm.supplier_reg_id from  pos_supplier_mappings  psm where psm.mapping_id in  (3324,4520067,4520097);

 

其查询结果集如下

MAPPING_ID	PARTY_ID	VENDOR_ID	SUPPLIER_REG_ID
3324	6092236	3761
4520067			1
4520097			29

 

大多数情况下，我们在java中拼接sql语句时候会判断传入的参数是否为空，

        //组织ID
        if((vendor_id != null)&&(!"".equals(vendor_id)))
        {
          if (clauseCount > 0) {
              whereClause.append(" AND ");
          }
          whereClause.append("vendor_id = :");
          whereClause.append(++bindCount);
          parameters.addElement(vendor_id);
          clauseCount++;
        }
        else{
            if (clauseCount > 0) {
                whereClause.append(" AND ");
            }
            whereClause.append("vendor_id  is null");
            clauseCount++;
        }

 

拼接出的SQL大多如下：

select psm.mapping_id,psm.party_id,psm.vendor_id,psm.supplier_reg_id from  pos_supplier_mappings  psm where psm.vendor_id is null;
select psm.mapping_id,psm.party_id,psm.vendor_id,psm.supplier_reg_id from  pos_supplier_mappings  psm where psm.party_id is null;
select psm.mapping_id,psm.party_id,psm.vendor_id,psm.supplier_reg_id from  pos_supplier_mappings  psm where psm.supplier_reg_id is null;

这样的拼接是繁琐的，并且容易出错。

 

方案二：灵活使用nvl(param1,param2)函数。

原始SQL如下：

SELECT psm.mapping_id,
       psm.party_id,
       psm.vendor_id,
       psm.supplier_reg_id
  FROM pos_supplier_mappings psm
 WHERE nvl(psm.party_id,
           -99) = nvl(:1,
                      nvl(psm.party_id,
                          -99))
   AND nvl(psm.vendor_id,
           -99) = nvl(:2,
                      nvl(psm.vendor_id,
                          -99))
   AND nvl(psm.supplier_reg_id,
           -99) = nvl(:3,
                      nvl(psm.supplier_reg_id,
                          -99))

思路，若传入的参数为空，那么则与表中的空值的列恒等。

传参形式如下：

this.setWhereClauseParam(0, PARTY_ID);
this.setWhereClauseParam(1, VENDOR_ID);
this.setWhereClauseParam(2, SUPPLIER_REG_ID);

注：在传入参数的时候不能使用以下形式

String PARTY_ID = PARTY_ID+"";

此形式会导致在SQL中将空值的PARTY_ID变为字符串'null'。