java - 如何使用 Hibernate Projection 设置默认的零 SUM 值？

Question

我目前正在使用 Hibernate Criteria 通过 SUM 创建汇总查询，当该 SUM 没有值时，我希望接收 0 而不是 null，

这是我的 pojo 代码

public class DetalleLibroMayor {

private Cuenta cuenta;
private BigDecimal debe = BigDecimal.ZERO;
private BigDecimal haber = BigDecimal.ZERO;

这是查询(只是投影部分)

ProjectionList projection = Projections.projectionList();
projection.add(Projections.property("cuenta").as("cuenta"));
projection.add(Projections.sum("debe").as("debe"));
projection.add(Projections.sum("haber").as("haber"));
projection.add(Projections.groupProperty("cuenta.id"));

criteria.setProjection(projection);
criteria.setResultTransformer(Transformers.aliasToBean(DetalleLibroMayor.class));

有什么想法吗？

Answer 1

我终于使用 MySQL 的 COALESCE 解决了这个问题函数并编写我的自定义投影如下:

public class CoalesceAggregateProjection extends AggregateProjection {

private static final long serialVersionUID = 1L;

private String aggregate;
private Object defaultValue;

protected CoalesceAggregateProjection (String aggregate, String propertyName, Object defaultValue) {
    super(aggregate, propertyName);

    this.aggregate = aggregate;
    this.defaultValue = defaultValue;
}

@Override
public String toSqlString(Criteria criteria, int loc, CriteriaQuery criteriaQuery) throws HibernateException {
    return new StringBuffer()
    .append("coalesce(")
    .append(aggregate)
    .append("(")
    .append( criteriaQuery.getColumn(criteria, propertyName) )
    .append("),")
    .append(defaultValue.toString())
    .append(") as y")
    .append(loc)
    .append('_')
    .toString();
}

public static AggregateProjection sum (String propertyName, Object defaultValue) {
    return new CoalesceAggregateProjection("sum", propertyName, defaultValue);
}
}

然后按照我的标准使用它:

projection.add(CoalesceAggregateProjection.sum("debe", "0").as("debe"));