Query to get nextval from sequence with Spring JPA

Question

I have a repository with which I am trying to get next value from sequence. The sequence name I need to be parameterized.

The repository query looks a like:

@Query(value = "SELECT ?1.NEXTVAL from dual;", nativeQuery = true) String getSeqID(@Param("seqName") String seqName);

But, I am getting following exception:

org.hibernate.QueryException: JPA-style positional param was not an integral ordinal
    at org.hibernate.engine.query.spi.ParameterParser.parse(ParameterParser.java:187) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.engine.query.spi.ParamLocationRecognizer.parseLocations(ParamLocationRecognizer.java:59) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.engine.query.internal.NativeQueryInterpreterStandardImpl.getParameterMetadata(NativeQueryInterpreterStandardImpl.java:34) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.engine.query.spi.QueryPlanCache.getSQLParameterMetadata(QueryPlanCache.java:125) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]

This is a functional need and the nextval from the sequence is required to be retrieved so the it can be used as a employee code. — Ashish Chauhan, Mar 05 '19 at 15:47
No solution here highlighted how the sequence_name can be parameterized using a native query. — Kinglee, Jul 26 '23 at 16:14

score 13 · Accepted Answer · answered Apr 01 '20 at 20:04

13

package br.com.mypackage.projectname.repository;

import java.math.BigDecimal;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

@Repository
public interface MyRepository extends JpaRepository<Myentity, Long> {

    @Query(value = "SELECT SCHEMA.SEQUENCE_NAME.nextval FROM dual", nativeQuery = true)
    public BigDecimal getNextValMySequence();

}

answered Apr 01 '20 at 20:04

Hinotori

552
6
15

3

Native query similar to this worked for me ```select nextval('sequence_name')``` – Nitin Kamate Apr 19 '21 at 03:36
1

is there a way to get sequence number without using native query? – Venkata Ramireddy CH Jan 27 '22 at 18:51
How can I use a dynamic SCHEMA for the native query (different than the default schema). For example in the case of an Oracle DB where someone wants to select a sequence of a different schema which might be dynamic. – Mts Sep 01 '22 at 10:51
What would be `Myentity` for the repository if the sequence is not linked to a table? – Mauricio Toledo Jan 16 '23 at 22:49
@MauricioToledo you can't . In the Id of MyEntity, you should specify the sequence. And the entity is a table. If you are using Oracle, maybe should find something in Spring JPA that can access this sequence – Hinotori Jan 17 '23 at 00:03
1

@MauricioToledo thinking again, maybe you can use the sequence, creating another method in some repository, but you must change the schema.sequence_name that matches the one that you want to use... i don't know, never did this, maybe works – Hinotori Jan 17 '23 at 00:15
1

@Hinotori I set the sequence as an SQL Native query in another repo. And, it did work! – Mauricio Toledo Jan 24 '23 at 19:21

score 5 · Answer 2 · answered Oct 06 '20 at 13:20

5

In PostgreSQL it is:

Long value = Long.parseLong(entityManager
            .createNativeQuery("select nextval('sequence_name')")
            .getSingleResult().toString());

answered Oct 06 '20 at 13:20

Ondrej Sotolar

1,352
1
19
29

Romil Patel · Answer 3 · 2019-03-05T16:59:54.737

4

@Entity
@Table(name = "tabelName")
public class yourEntity{
    @Id
    @SequenceGenerator(name = "yourName", sequenceName = "yourSeqName", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "yourName")
    @Column(name = "id", updatable = false)
    protected Long id;
}

@Query(value = "SELECT yourSeqName.nextval FROM tableName", nativeQuery = true)
Long getNextSeriesId();

EDIT:

Query q = em.createNativeQuery("SELECT yourSeqName.NEXTVAL FROM DUAL");
return (java.math.BigDecimal)q.getSingleResult();

edited Mar 05 '19 at 16:59

answered Mar 05 '19 at 16:32

Romil Patel

12,879
7
47
76

can't we use it without defining entity? By any way we can call it with dual within repository? – Ashish Chauhan Mar 05 '19 at 16:53
@AshishChauhan yes, you can use dual – Romil Patel Mar 05 '19 at 17:01
do i need to invoke it with em? – Ashish Chauhan Mar 05 '19 at 17:03
Yes, em is Entity Manager – Romil Patel Mar 05 '19 at 17:08
@AshishChauhan Please Accept the answer and vote up so others can get know about right solution. – Romil Patel Mar 06 '19 at 04:29
Hi, I have faced similar issue like insufficient privileges while calling the sequence . How to solve the issue – Thadeuse Jun 30 '21 at 08:57

Andronicus · Answer 4 · 2019-03-05T17:54:37.677

2

You can use EntityManager:

entityManager.createNativeQuery("select seqname.nextval ...").getSingleResult();

edited Mar 05 '19 at 17:54

answered Mar 05 '19 at 15:40

Andronicus

25,419
17
47
88

The database I am using is oracle and the entity manager has been configured in another config class as a bean. The nextval(seqname) is giving error says unknown value. Can't I used anything with above approach within JPA repository? – Ashish Chauhan Mar 05 '19 at 15:48
I've edited the question with oracle dialect, I'm not sure, you can try putting it in `@Query` – Andronicus Mar 05 '19 at 17:55

score 1 · Answer 5 · answered Nov 29 '21 at 17:00

1

I fixed this issue using the following code :

@Query(value = "SELECT NEXT VALUE FOR code_sequence", nativeQuery = true)
public BigDecimal getNextValCodeSequence();

NB: you should replace code_sequence with your sequence name

answered Nov 29 '21 at 17:00

Ousama

2,176
3
21
26

Query to get nextval from sequence with Spring JPA

5 Answers5