How to return max of identity column via Hibernate accessing Oracle DB?

Question

Since it seems that HQL (createQuery) doesn't support scalar queries, I used a raw query via createSQLQuery:

        session.beginTransaction();
        Query query = session.createSQLQuery("select nvl(max(id), 0) + 1  from empty_table_for_now");

        session.getTransaction().commit();
        List listL = query.list();
        Iterator iterator = listL.iterator();
        while (iterator.hasNext()) {
            LOG.info("Infinite loop. This is disgusting!");
        }           

The query itself doesn't fail, but it seems to be returning a list with an infinite number of results? This doesn't make sense... Any idea why I am getting this?

Even better yet, is there a better, more elegant way, of getting the intended scalar, without having to confuse whoever maintains my code with a "list" (that should always contain one element)?

Answer 1

If you don't move the iterator the loop is definitely infinite (as long as there is at least one element).

Move the iterator with Iterator.next()

while (iterator.hasNext()) {
    Object nextElement = iterator.next();
    LOG.info("Next element is: " + nextElement);
} 

Maybe you had a ResultSet.next() in mind when writing the code, but an iterator is different. Take a look at the javadocs.

Answer 2

When using Oracle DB, I recommend the use of sequences.

To get the sequence to work, you should define the column and the sequence name in the mapping file and Hibernate will do the job of fetching the next sequence value.

FYI: SQL Server - Use Identity for autoincrement MySQL/Maria DB - Use autoincrement PostgreSQL - Use sequences or alternatively .. PostgreSQL Autoincrement

Alert: Trying to find the maximum value in a column and bouncing it up is not an acceptable design.