Select nvl(max(c.EmployeeId),0) in JPQL?

Question

I'm using oracle10g database and eclipselink, I need to obtain the last inserted key from the table so i've created this query

javax.persistence.Query q =   
                    em.createQuery("SELECT nvl(MAX(c.myAtt),0) " +   
                        "from myTable as c");   
             return Integer.parseInt(q.getSingleResult().toString());   `

But when the table is empy(sometimes it might get empty) i'm getting ILEGAL ARGUMENT EXCEPTION, cause: JPQL Exception, detail: "An exception occurred while creating a query in EntityManager". What i'm doing wrong?

Answer 1

In the meanwhile somebody else could have inserted something in Autorizaciones and then you receive the wrong id

Answer 2

NVL() is supported now in newer versions of jpql

Answer 3

You could use the COALESCE function. It can be used to achieve the same as nvl. For instance:

select nvl(columna,'1') from table
select COALESCE(columna,'1') from table

Answer 4

Paraphrasing Apu, "I don't know what part of that question to correct first" :-)

First of all, retrieving last inserted key in this way is a VERY BAD THING © It's dangerous, inefficient and most of all, unnecessary as your JPA already does it for you: once you insert your entity, its identifier property will automatically be updated to contain its primary key.

Secondly, as far as your query goes, "myTable" is not something you would use in JPQL, you need to specify your entity name instead (e.g. if you're mapping "Car" to "CAR_TABLE" you should use "Car" instead of "CAR_TABLE" in JPQL queries).

Finally, NVL() is not supported by JPQL. It is supported (sort of, via Expression.ifNull()) by EclipseLink Expressions. Not that you'd need it in a scenario like this, anyway.