javasqlspringjpaspring-data-jpa

Query to get nextval from sequence with Spring JPA


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]

Solution

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