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();

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 :) — rogerdpack
– rogerdpack, Commented May 26, 2016 at 16:07

score 9 · Accepted Answer · 2015-04-17 07:45:09Z

9

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.

edited Apr 17, 2015 at 7:45

answered Apr 16, 2015 at 15:18

user330315

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

5 Comments

Quentin T. Over a year ago

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

user330315 Over a year ago

@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. Over a year ago

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

user330315 Over a year ago

@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. Over a year ago

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)

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related