Blaze-Persistence含UNION的查询如何返回FilterResult对象列表？

我编写了一个包含WITH CTE和UNION的Blaze-Persistence查询，SQL执行结果正确，但调用getResultList()后返回的是ArrayList<Object[4]>，而非预期的ArrayList<FilterResult>。移除UNION及其对应的查询部分时，.selectNew(FilterResult.class)可以正确构造ArrayList<FilterResult>。

如何让包含UNION的完整查询也返回ArrayList<FilterResult>？

相关代码 #

查询方法代码

public List<FilterResult> getFilters(HourSearchDto hourSearchDto) {
  ...
  FinalSetOperationCriteriaBuilder<FilterResult> cb = cbf.create(entityManager, FilterResult.class)
    // Start with the WITH()
    .with(HourCTE.class, false)
      .from(Hour.class, "hour")
      .bind("id").select("id")
      .bind("employeeId").select("employee.id")
      .bind("taskId").select("task.id")
      .where("UPPER(hour.description)").like().expression("'" + hourSearchDto.getDescription().toUpperCase() + "%'").noEscape()
    .end()
    // First select, for reference Employee. Use the selectNew(FilterResult.class) to map the result to FilterResult.
    .selectNew(FilterResult.class)
      .with("employee.id", "id")
      .with("'EMPLOYEE'", "referenceName")
      .with("COUNT(hour.employeeId)", "count")
      .with("FORMAT('%1$s (%2$s)', employee.firstName, COUNT(hour.employeeId))", "displayValue")
    .end()
    .from(Employee.class, "employee")
    .leftJoinOn(HourCTE.class, "hour")
      .on("hour.employeeId").eqExpression("employee.id")
    .end()
    // UNION to add next select.
    .union()
    // Next select, for reference Task. Simple select as first select above maps the result to FilterResult already.
    .select("task.id", "id")
    .select("'TASK'", "referenceName")
    .select("COUNT(hour.taskId)", "count")
    .select("FORMAT('%1$s (%2$s)', task.name, COUNT(hour.taskId))", "displayValue")
    .from(Task.class, "task")
    .leftJoinOn(HourCTE.class, "hour")
      .on("hour.taskId").eqExpression("task.id")
    .end()
    .endSet()
    .orderByAsc("referenceName")
    .orderByAsc("displayValue");

    return cb.getQuery().getResultList();
}

FilterResult类

public class FilterResult {

    @Id
    Long id;

    String referenceName;

    Long count;

    String displayValue;

    public FilterResult() {
    }

    public FilterResult(Long id, String referenceName, Long count, String displayValue) {
        this.id = id;
        this.referenceName = referenceName;
        this.count = count;
        this.displayValue = displayValue;
    }

    // Getters
}

HourCTE类

@CTE
@Entity
public class HourCTE {

    @Id
    private Long id;

    private Long employeeId;

    private Long taskId;
}

解决方案 #

Blaze-Persistence的UNION操作要求所有分支查询都明确指定结果映射规则，仅在第一个分支使用selectNew不足以让整个UNION查询返回目标DTO。你需要在UNION的第二个分支同样调用selectNew(FilterResult.class)来统一结果映射逻辑。

修改后的UNION分支代码如下：

// UNION to add next select.
.union()
// Next select, for reference Task. 显式指定结果映射到FilterResult
.selectNew(FilterResult.class)
  .with("task.id", "id")
  .with("'TASK'", "referenceName")
  .with("COUNT(hour.taskId)", "count")
  .with("FORMAT('%1$s (%2$s)', task.name, COUNT(hour.taskId))", "displayValue")
.end()
.from(Task.class, "task")
.leftJoinOn(HourCTE.class, "hour")
  .on("hour.taskId").eqExpression("task.id")
.end()

原因说明 #

UNION是多个独立查询的结果合并，Blaze-Persistence需要每个分支都明确结果如何映射到目标类。如果某一分支未指定映射规则，框架会默认返回字段值组成的Object数组，导致整个查询结果类型不一致，最终返回ArrayList<Object[4]>。只有所有分支都使用相同的selectNew配置，才能确保最终返回的是ArrayList<FilterResult>。

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