Hibernate CriteriaAPI and like operator. Swap oper

Question

Intro

I have a weird task - to write on Hibernate Criteria API (i.e. in database independent style) SQL query similar to

select * from branch b where '2/5/3/' like b.hier_path + '%' 

where + is concatenation operator. Concatenation operator is database dependent '+' in MS SQL, '||' in Oracle etc.

I must use Criteria API (and no way to switch to HQL).

Problem #1 - like operator

Unfortunately, Hibernate allows to write only Criteria based on Java object property:

pCriteria.createCriteria(Branch.class).add(Restrictions.like("hierarchyPath", "2/5/3/%"));

Which is equivalent of

select * from branch where 'hier_path like 2/5/3/%'

I don't know how to swap operands of like operator.

Problem #2 - database independent concatenation

The SQL code must works on Oracle, MS SQL Server, DB2, Postgres, Sybase, MySQL, HSQLDB, Firebird (and some other new relational databases).

What I've got for now is SQL based hack:

Restrictions.sqlRestriction("? like concat({alias}.hier_path,'%')", "2/5/3/", Hibernate.STRING)

Unfortunately, concat is database dependent function that present in most from above mentioned databases (except Postgres and Firebird). The approach is a workaround and could not be used as constant solution (I'll try to add custom functions concat to databases that doesn't have it).

Conclusion

Could anybody propose an improvement to my hack (a database independent SQL) or correction to original CriteriaAPI?

UPDATE 28.09.12

concat functions appears in Postgres 9.1

Answer 1

Thanks @JB Nizet. Code inspired by his ideas:

private class InverseLikeExpression extends SimpleExpression{
    private static final String CONST_HQL_FUNCTION_NAME_CONCAT = "concat";
    private static final String CONST_LIKE = " like ";
    private static final String CONST_LIKE_SUFFIX = "'%'";

    private final String propertyName;

    protected InverseLikeExpression(String pPropertyName, Object pValue) {
        super(pPropertyName, pValue, CONST_LIKE);
        propertyName = pPropertyName;
    }

    @Override
    public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
        String[] columns = criteriaQuery.getColumnsUsingProjection(criteria, propertyName);
        Dialect dialect = criteriaQuery.getFactory().getDialect();
        SQLFunction concatFunction = (SQLFunction) dialect.getFunctions().get(CONST_HQL_FUNCTION_NAME_CONCAT);

        StringBuffer fragment = new StringBuffer();
        if (columns.length>1) fragment.append('(');
        for ( int i=0; i<columns.length; i++ ) {
            String fieldName = concatFunction.render(Arrays.asList(new Object[] {columns[i], CONST_LIKE_SUFFIX}), criteriaQuery.getFactory());
            fragment.append("?").append( getOp() ).append(fieldName);
            if ( i<columns.length-1 ) fragment.append(" and ");
        }
        if (columns.length>1) fragment.append(')');
        return fragment.toString();
    }       
}

Answer 2

You could write your own Criterion implementation, which would generate a SQL clause similar to the one you have in your question, except it would use the dialect associated with the criteria query to get the appropriate concat function and delegate the concatenation to this database-dependant concat function.