ADF11g: VO SQL中存在绑定in查询

 

select xs.xsid,
       xs.xh,
       xs.xm,
       xs.sfzh,
       xs.nj,
       xs.xqdm,
       xs.xqmc,
       xs.yxdm,
       xs.yxmc,
       xs.zydm,
       xs.zymc,
       xs.bjdm,
       xs.bjmc,
       yj.xn,
       nvl(sum(decode(yj.fylx, 1, jl.je)), 0) xf, --已缴学费  
       nvl(sum(decode(yj.fylx, 2, jl.je)), 0) zsf --已缴住宿费  
  FROM sfgl_xsxx xs,
       sfgl_yjfy yj,
       (SELECT t.je, t.yjfyid, t.xn, t.create_by, t.create_time FROM sfgl_jfjl t 
        WHERE t.jflx in (select * from TABLE(CAST(:p_jflx AS IN_VARCHAR))) 
        and t.pjh is null
        and (t.create_by like:p_czr or :p_czr is null)
        and (to_char(t.create_time,'yyyy-MM-dd HH24:mi:ss') >=:p_kssj or :p_kssj is null)
        and (to_char(t.create_time,'yyyy-MM-dd HH24:mi:ss') <=:p_jssj or :p_jssj is null)
        ) jl 
 where 
 xs.xsid = yj.yjyh(+)
 AND yj.yjfyid = jl.yjfyid
 group by xs.xsid,
          xs.xh,
          xs.xm,
          xs.sfzh,
          xs.nj,
          xs.xqdm,
          xs.xqmc,
          xs.yxdm,
          xs.yxmc,
          xs.zydm,
          xs.zymc,
          xs.bjdm,
          xs.bjmc,
          yj.xn
 order by xs.xh

 上图代码中 条件 

t.jflx in (select * from TABLE(CAST(:p_jflx AS IN_VARCHAR))) 等于 t.jflx in (:p_jflx)

绑定变量 选 Array 类型，并且勾选Required。

然后oracle 数据中创建 in_varchar 类型：

 

然后voImpl 中则可以使用查询变量了：

 
 

        String[] jflxs = jflx.split(",");
        Array jflxArr = new Array(jflxs);
        HashMap context = new HashMap();
        context.put(DomainContext.ELEMENT_SQL_NAME, "IN_VARCHAR");
        context.put(DomainContext.ELEMENT_TYPE, String.class);
        jflxArr.setContext(null, null, context);
        this.setNamedWhereClauseParam("p_jflx", jflxArr);
        this.setp_jflx(jflxArr);
        this.executeQuery();

 

 
 功能摘自--收费系统 发票批量打印的查询