How to use syntax of mysql such as ifnull(max(Id),0) in Hibernate

Question

look likes Hibernate have not this syntax,that is 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 a few problems with this....

• IFNULL is COALESCE in HQL (IFNULL equivalent in Hibernate Query Language?.)
• If you only want a single object back, use uniqueResult() instead of list().get(0).
• HQL doesn't have every language construct that MySQL does. It might be better to use raw SQL, with session.createSqlQuery().
• As zerkms says, it looks like you're managing your own sequences. Take a look at IdentifierGenerator (http://blog.anorakgirl.co.uk/2009/01/custom-hibernate-sequence-generator-for-id-field/).

Regardless though, the code would be...

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

    return max + 1;
}