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
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.