使用 Spring JPA 查询从序列中获取 nextval

Question

我有一个存储库，我正在尝试从序列中获取下一个值。我需要参数化的序列名称。

存储库查询类似于：

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

但是，我得到以下异常：

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]

Answer 1

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;@Repositorypublic interface MyRepository extends JpaRepository<Myentity, Long> {&nbsp; &nbsp; @Query(value = "SELECT SCHEMA.SEQUENCE_NAME.nextval FROM dual", nativeQuery = true)&nbsp; &nbsp; public BigDecimal getNextValMySequence();}

Answer 2

在 PostgreSQL 中是：Long&nbsp;value&nbsp;=&nbsp;Long.parseLong(entityManager &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.createNativeQuery("select&nbsp;nextval('sequence_name')") &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.getSingleResult().toString());

Answer 3

@Entity@Table(name = "tabelName")public class yourEntity{&nbsp; &nbsp; @Id&nbsp; &nbsp; @SequenceGenerator(name = "yourName", sequenceName = "yourSeqName", allocationSize = 1)&nbsp; &nbsp; @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "yourName")&nbsp; &nbsp; @Column(name = "id", updatable = false)&nbsp; &nbsp; protected Long id;}@Query(value = "SELECT yourSeqName.nextval FROM tableName", nativeQuery = true)Long getNextSeriesId();编辑：Query q = em.createNativeQuery("SELECT yourSeqName.NEXTVAL FROM DUAL");return (java.math.BigDecimal)q.getSingleResult();

Answer 4

您可以使用EntityManager：entityManager.createNativeQuery("select&nbsp;seqname.nextval&nbsp;...").getSingleResult();