How to use mysql syntax like ifnull (max (Id), 0) in Hibernate

Question

look likes Hibernate don't have this syntax, is that right?

public int MaxIdenx() { int max = 0; String hql = "select ifnull(max(empId),0)from Emp"; Query query = session.createQuery(hql); List currentSeq = query.list(); if (currentSeq == null) { return max; } else { max = (Integer) currentSeq.get(0); return max + 1; } } 

Answer 1

There are several problems with this ....

• IFNULL is COALESCE in HQL ( IFNULL equivalent in query language for sleep mode?. )
• If you need only one object back, use uniqueResult () instead of list().get(0) .
• HQL does not have any language construct that MySQL makes. It might be better to use raw SQL with session.createSqlQuery() .
• As the zerkms say, it looks like you control your own sequences. Take a look at IdentifierGenerator ( http://blog.anorakgirl.co.uk/2009/01/custom-hibernate-sequence-generator-for-id-field/ ).

Despite this, the code will ...

 public int MaxIdenx() { int max = (Integer)session .createQuery("SELECT COALESCE(MAX(empId), 0) FROM Emp") .uniqueResult(); return max + 1; }