title: Hibernate CriteriaAPI and like operator. Swap operands + database independent concatenation - is it possible?
body: <p><strong>Intro</strong></p>
  
  <p>I have a weird task - to write on Hibernate Criteria API (i.e. in database independent style) SQL query similar to</p>
  
  <pre><code>select * from branch b where '2/5/3/' like b.hier_path + '%' 
  </code></pre>
  
  <p>where <code>+</code> is concatenation operator. Concatenation operator is database dependent '+' in MS SQL, '||' in Oracle etc.</p>
  
  <p>I must use Criteria API (and no way to switch to HQL).</p>
  
  <p><strong>Problem #1 - like operator</strong></p>
  
  <p>Unfortunately, Hibernate allows to write only Criteria based on Java object property:</p>
  
  <pre><code>pCriteria.createCriteria(Branch.class).add(Restrictions.like("hierarchyPath", "2/5/3/%"));
  </code></pre>
  
  <p>Which is equivalent of </p>
  
  <pre><code>select * from branch where 'hier_path like 2/5/3/%'
  </code></pre>
  
  <p>I don't know how to swap operands of like operator.</p>
  
  <p><strong>Problem #2 - database independent concatenation</strong></p>
  
  <p>The SQL code must works on Oracle, MS SQL Server, DB2, Postgres, Sybase, MySQL, HSQLDB, Firebird (and some other new relational databases).</p>
  
  <p>What I've got for now is SQL based hack:</p>
  
  <pre><code>Restrictions.sqlRestriction("? like concat({alias}.hier_path,'%')", "2/5/3/", Hibernate.STRING)
  </code></pre>
  
  <p>Unfortunately, <code>concat</code> is <strong>database dependent</strong> 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 <code>concat</code> to databases that doesn't have it).</p>
  
  <p><strong>Conclusion</strong></p>
  
  <p>Could anybody propose an improvement to my hack (a database independent SQL) or correction to original CriteriaAPI?</p>
  
  <p><strong>UPDATE 28.09.12</strong></p>
  
  <p><code>concat</code> functions <a href="http://www.postgresql.org/docs/9.1/static/functions-string.html" rel="nofollow">appears in Postgres 9.1</a></p>