4

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

WebViewer
  • 761
  • 7
  • 21
  • 1
    You shouldn't be using `select max(..)` to generate unique values. It's either incorrect or will not scale. Use a sequence instead –  Feb 12 '14 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 Feb 12 '14 at 12:59
  • 1
    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. –  Feb 12 '14 at 13:00
  • 1
    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 Feb 12 '14 at 13:08
  • 1
    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. –  Feb 12 '14 at 13:48
  • @a_horse_with_no_name Unless someone at Oracle, implementing CC&B, decides to force a MAX_VALUE of 999 on a sequence... But in principle, yes you are right (again). – WebViewer Feb 12 '14 at 15:45

2 Answers2

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.

René Link
  • 48,224
  • 13
  • 108
  • 140
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.

Community
  • 1
  • 1
PRK
  • 63
  • 3
  • 1
    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! – WebViewer Feb 12 '14 at 13:11
  • http://stackoverflow.com/questions/5082175/how-to-use-sequence-in-hibernate-as-a-property-in-xml-mapping – PRK Feb 12 '14 at 13:14
  • Detailed examples at .. http://stackoverflow.com/questions/2155376/how-to-use-existing-oracle-sequence-to-generate-id-in-hibernate – PRK Feb 12 '14 at 13:18