How to set default zero SUM value with Hibernate Projection?

Question

I'm currently using Hibernate Criteria to create a summary query with SUM, When there are no values for that SUM I'd like to receive 0 instead of null,

Here's my pojo's code

public class DetalleLibroMayor {

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

And this is the query (just the projection part)

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));

Any idea?

Answer 1

I've finally solved this using MySQL's COALESCE function and writing my custom projection as follows:

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);
}
}

And then using it with my criteria:

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