Oracle10环境下Hibernate如何为带START WITH的查询批量传入ID列表

核心思路：Oracle 10g支持CONNECT_BY_ROOT关键字，可以在分层查询中保留每个分支的起始ID，配合窗口函数按起始ID分组取最顶层父节点，即可实现批量传入ID列表一次查询返回所有结果。

改造后的查询语句 #

select 
    origin_id, 
    min(a.time) 
from table1 a
inner join (
    select 
        origin_id,
        parentid
    from (
        select 
            CONNECT_BY_ROOT id as origin_id,
            parentid,
            row_number() over(partition by CONNECT_BY_ROOT id order by LEVEL desc) as rn
        from table2 
        START WITH id in (:ids)
        CONNECT BY NOCYCLE PRIOR parentid = id
    )
    where rn = 1
) b on a.id = b.parentid
group by origin_id

Hibernate调用示例 #

NativeQuery sqlQuery = session.createSQLQuery("上面的SQL字符串");
// 旧版本Hibernate使用setParameterList方法传列表参数，和你期望的setListParameter逻辑完全一致
sqlQuery.setParameterList("ids", ids);
List<Object[]> list = sqlQuery.list();

注意事项 #

• 若传入的ID列表长度超过Oracle IN子句的1000条限制，可将ID列表拆分为多批分批调用，或改用Oracle嵌套表类型传参规避该限制。
• CONNECT_BY_ROOT为Oracle 9i即原生支持的特性，完全兼容10g版本，无需依赖递归WITH语法。
• 返回结果中第一个元素为对应的原始输入ID，第二个为计算得到的min(a.time)，与原单个查询的返回结构完全一致，无需调整后续业务处理逻辑。

内容的提问来源于stack exchange，提问作者tarsis