Get nextval sequence value by simple java Query on a PostgreSQL DB

Question

I'm working on a PostgreSQL Database and I am trying to recover a nextval sequence by a simple Query by Java, but It's not working :

  Query q = entityManager.createQuery("SELECT nextval(numcallcartnewcart) as num");
    BigDecimal result=(BigDecimal)q.getSingleResult();   
    return result.longValue();

(Of course it's not the best solution, but I can't do better, because I'm blocked by the Hibernate configuration with the composite-id tag which don't accept a generator sequence like that :

        <column name="num_call" />
             <generator class="sequence">
                    <param name="sequence">numcallcartnewcart</param>
             </generator>

into the key-property tag :

   <key-property name="numCall" type="int">
    <column name="num_call"/>
   </key-property>

) Here is the error of the Query:

 \-[METHOD_CALL] MethodNode: '('
    +-[METHOD_NAME] IdentNode: 'nextval' {originalText=nextval}
    \-[EXPR_LIST] SqlNode: 'exprList'
       \-[IDENT] IdentNode: 'numcallcartnewcart' {originalText=numcallcartnewcart}

    at org.hibernate.hql.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:154)
    at org.hibernate.hql.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:845)
    at org.hibernate.hql.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:633)

It's more or less the same with a createNativeQuery (but not the same error) :

Caused by: org.postgresql.util.PSQLException: ERROR: column « numcallcartnewcart » does not exist
  Position: 16
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2101)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1834)

EDIT : With quote

Query q = entityManager.createNativeQuery("SELECT nextval('numcallcartnewcart') as num");

        BigDecimal result=(BigDecimal)q.getSingleResult();   
        return result.longValue();

--

Caused by: org.postgresql.util.PSQLException: ERREUR: la relation « numcallcartnewcart » n'existe pas
  Position: 16
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2101)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1834)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)

EDIT 2 : (The problem was that I don't have the sequence in my database (not in the good one...)

And we have to use a BigInteger, not a BigDecimal, and to use quote around sequence name :

    Query q = entityManager.createNativeQuery("SELECT nextval('numcallcartnewcart') as num");
    BigInteger result=(BigInteger)q.getSingleResult();   
    return result.longValue(); 

Solution

The name of the sequence has to be passed as a string literal, not as an identifier:

entityManager.createQuery("SELECT nextval('numcallcartnewcart') as num");

More details in the manual: http://www.postgresql.org/docs/current/static/functions-sequence.html

Edit

The error

ERREUR: la relation « numcallcartnewcart » n'existe pas

indicates that no sequence with the name numcallcartnewcart exists. You need to create the sequence first.

Comments

[rogerdpack]

I think you do have to use createNativeQuery here unfortunately...just get it working in the SQL console then copy it to your java code :)

[Quentin T.]

No, it's not working, I've tried it before of course ^^.

[a_horse_with_no_name]

@QuentinT.: The error message "ERROR: column « numcallcartnewcart » does not exist" is pretty clear. The cause of that is that you did not enclose the sequence name in single quotes. Do you really get that specific error message when put the sequence name in single quotes?

[Quentin T.]

Yes the error message does not exist maybe, it's a translation from French by myself.

[a_horse_with_no_name]

@QuentinT. "la relation" is "the relation" and that in turn means you do not have a sequence named numcallcartnewcart. Please show us your exact SQL code you used to create the sequence. Or the output of the \ds command in psql

[Quentin T.]

Ok... It's a little shame for me... I had well the sequence in my database, but not in the good one... I had it in the production environment and I'm in test... So it couldn't work... Thanks a lot... It's work well with the sequence in the good DB... (just have to change a cast in BigInteger : see my edit2)