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

hibernate, java, mysql

Solution

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;
}

Problem

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; } } ```

Original source