Java应用跨库适配：Derby中替代array_agg的SELECT子查询方案

原有实现说明 #

你当前的设计是在主查询SELECT子句中嵌入标量子查询，避免一对多关联表直接JOIN产生的冗余笛卡尔积，PostgreSQL环境下通过array_agg(prose)实现多条检查流程文本的聚合；由于Derby不支持该函数，可通过以下方案实现同等效果：

Derby侧等价实现 #

Derby 10.6及以上版本内置LISTAGG字符串聚合函数，可直接完成多行字段到单个字符串的拼接，替换原有子查询即可运行：
原PostgreSQL运行的子查询片段：

(
    SELECT array_agg(prose)
    FROM exam_procedure
    WHERE examination_id = " + examId
    GROUP BY examination_id
) AS agg_procedures

替换为Derby兼容的子查询逻辑：

(
    SELECT LISTAGG(prose, ',')
    FROM exam_procedure
    WHERE exam_procedure.examination_id = examination.id
    GROUP BY examination_id
) AS agg_procedures

优化提醒 #

• 原有写法中硬拼接examId变量的逻辑存在SQL注入风险，且在主查询返回多行场景下会出现聚合值和主表行不匹配的问题，建议改用相关子查询直接关联主表examination.id，无需额外传入外部变量
• 如果需要固定聚合后的字符串顺序（比如按检查流程的执行序号排序），可通过如下语法实现：
  • Derby：LISTAGG(prose, ',') WITHIN GROUP (ORDER BY procedure_sort_num)
  • PostgreSQL：可改用STRING_AGG(prose, ',' ORDER BY procedure_sort_num)，和Derby的语法行为完全对齐，减少跨库适配的分支判断
• 原拼接SQL中patient.middle_name AS pt_middle_name后多余的逗号需要调整，避免在严格SQL模式下触发语法错误
• 若使用的Derby版本低于10.6、无内置LISTAGG函数，可通过递归CTE遍历关联表的prose字段逐行拼接为单字符串，但性能会略低于内置聚合函数，建议优先使用内置函数方案

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