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)?

You shouldn't be using select max(..) to generate unique values. It's either incorrect or will not scale. Use a sequence instead — user330315
– user330315, Commented Feb 12, 2014 at 12:54
@a_horse_with_no_name Thanks. You are right of course but in my particular case a sequence is an overkill (comes with own problems, too). I will stick with max() for now since I will always have only one process creating and/or updating this table. — WebViewer
– WebViewer, Commented Feb 12, 2014 at 12:59
The max() approach is a much bigger "overkill". Especially on large tables the query will be really slow whereas using a sequence is a lot faster. — user330315
– user330315, Commented Feb 12, 2014 at 13:00
Once again you are right, but in my particular case performance is not an issue. OTOH, sequences tend to get out of sync (or max out) in organizations in which the DBAs don't talk to the developers and in which the tail is wagging the dog. ;) Then again, I just saw @PRK's answer so if I can get Hibernate to handle this for me, I will definitely take the correct route. — WebViewer
– WebViewer, Commented Feb 12, 2014 at 13:08
Sequences don't "max out". Their max value is 10^28. So even if you get a thousand values per second it would take ages to max it out. — user330315
– user330315, Commented Feb 12, 2014 at 13:48

René Link · Accepted Answer · 2014-02-12 13:03:54Z

5

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.

edited Feb 12, 2014 at 13:03

answered Feb 12, 2014 at 12:55

René Link

52k15 gold badges119 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

WebViewer Over a year ago

Duh! I should have know better. Thank you for pointing out my moment of blindness. +1 and accepting.

Community · Accepted Answer · 2017-05-23 11:33:27Z

4

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.

edited May 23, 2017 at 11:33

CommunityBot

11 silver badge

answered Feb 12, 2014 at 13:01

PRK

734 bronze badges

3 Comments

WebViewer Over a year ago

Thanks +1 for the tip regarding Hibernate doing the job of fetching the next sequence value by simply defining something in the hbm.xml file. Where can I learn more about this? A concrete example would be super. Thank you!

PRK Over a year ago

stackoverflow.com/questions/5082175/…

PRK Over a year ago

Detailed examples at .. stackoverflow.com/questions/2155376/…

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related