Hibernate criteria for compare SUM of three columns againts another column

Question

I have a table with columns as ID, col1, col2, col3, col4, col5. I want to add a hibernate criteria restriction as (col1 + col2 + col3) < col4

All these columns contains integer values. How could I achieve this requirement ? Any help appreciated.

Answer 1

I found that this can be achieving by writing customized Criterion. Following is sample code to share with others,

public class CostomizedExpression implements Criterion {

    private final TypedValue[] NO_TYPED_VALUES = new TypedValue[0];

    private String[] otherColumns;

    private String column;

    private String operator;

    public CostomizedExpression (String operator, String column, String... otherColumns) {
        this.column = column;
        this.otherColumns = otherColumns;
        this.operator = operator;
    }

    @Override
    public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
        String[] columns = criteriaQuery.getColumnsUsingProjection(criteria, column);
        StringBuffer buffer = new StringBuffer();
        buffer.append("(");
        for (int i = 0; i < otherColumns.length; i++) {
            buffer.append(" ");
            buffer.append(criteriaQuery.getColumnsUsingProjection(criteria, otherColumns[i])[0]);
            if (i < otherColumns.length - 1) {
                buffer.append("+");
            }
            buffer.append(" ");
        }

        buffer.append(" )");
        buffer.append(operator);
        buffer.append(" ");
        buffer.append(columns[0]);
        return buffer.toString();
    }

    @Override
    public TypedValue[] getTypedValues(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
        return NO_TYPED_VALUES;
    }

    @Override
    public String toString() {
        StringBuffer buffer = new StringBuffer();
        for (int i = 0; i < otherColumns.length; i++) {
            buffer.append(" ");
            buffer.append(otherColumns[i]);
            if (i < otherColumns.length - 1) {
                buffer.append("+");
            }
            buffer.append(" ");
        }

        buffer.append(" ");
        buffer.append(operator);
        buffer.append(" ");
        buffer.append(column);
        return buffer.toString();
    }
}