Using "Formula" in Hibernate

Good point for using Hibernate formula parameter is you can create a Dummy column even though you don't have a real column in your database; Then in your Dao layer, you can query the Dummy column as you want. For example : in the table "M_Confirmation" , it has 2 columns "Confirmation_Cost" and "Confirmation_Fee", both of which can tolerate null. The business logic is to sum up Confirmation_Cost and Confirmation_fee (temporary call it "Total_Price") and query Total_Cost >= a param passed from front end or a filter from UI;

SQL is ：

select (confirmation_cost + confirmation+fee) from m_confirmation where (confirmation_cost + confirmation+fee) >= ? ;

Now, we need to create a  dummy column in hibernate query , we need "formula" , then Hiberate will invoke a SQL like " select formula from M_Confirmation"  in its implementation; what we need to configure in xml is ;:

<property name="totalPrice" formula="((CASE WHEN Confirmation_Cost IS NULL THEN 0 ELSE Confirmation_Cost END) + (CASE WHEN Confirmation_Fee IS NULL THEN 0 ELSE Confirmation_Fee END))" />

Note: In DB, Any value + Null = null , so we must use case when rather than nvl, because nvl is only applicable in Oracle not suitable for DB Migration policy.

Then , In persistent model declare the corresponding field :

public class Confirmation extends ConfirmationBase implements BasicModel {
    private BigDecimal totalPrice;

    public BigDecimal getTotalPrice() {
        return totalPrice;
    }

    public void setTotalPrice(BigDecimal totalPrice) {
        this.totalPrice = totalPrice;
    }

    @Override
    public String toString() {
        return ToStringBuilder.reflectionToString(this, TafToStringStyles.PERSISTENCE_TOSTRING_STYLE);
    }

In this way , you can flexibly  fetch any columns' permutation and combinations:

        BigDecimal bpsPrice = filter.getBpsPrice();
        DetachedCriteria criteria = DetachedCriteria.forClass(Confirmation.class);
        if (bpsPrice != null) {
            criteria.add(Restrictions.ge("totalPrice", bpsPrice));
        }
        return getHibernateTemplate().findByCriteria(criteria);

This is how I use formula in my work, however, formula= "" will follow by the select query as the first column in Hibernate, so please write the queried columns in order  to aviod some mapping errors .

Beside, if you don't want to use formula , you can also add the criteria in your restriction like :

        BigDecimal bpsPrice = filter.getBpsPrice();
        DetachedCriteria criteria = DetachedCriteria.forClass(Confirmation.class);
        if (bpsPrice != null) {
           criteria
            .add(Restrictions.sqlRestriction("((CASE WHEN Confirmation_Cost IS NULL THE             N 0 ELSE Confirmation_Cost END) + (CASE WHEN Confirmation_Fee IS NULL THEN             0 ELSE Confirmation_Fee END)) >=") + bpsPrice );
        }
        return getHibernateTemplate().findByCriteria(criteria);

But this one maybe can't be accepted by code reviewer as it's hard to maintain!