Problem Retrieving Nextval in JPA Application

Question

I need have a need to return nextVal at various times in my application. I have an entity class like the following:

@Entity
@Table(name = "TBL_CACL")
public class Cacl {
@Id
@SequenceGenerator(name = "caclGenerator", sequenceName = "caclSeq", allocationSize = 1)
@GeneratedValue(strategy = GenerationType.IDENTITY, generator = "calcGenerator")
@Column(name = "ID")
private Integer id;

// more stuff below....

, and I added the following in my repository interface:

@Repository
public interface CaclRepository extends JpaRepository<Cacl, Integer> {

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

However when I attempt to read it like this:

long nextval = caclRepository.getNextSeriesId() + 1;

, I get this exception:

(can't show entire stack trace)

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet


Caused by: org.postgresql.util.PSQLException: ERROR: relation "cacl" does not exist

Its puzzling to me that I am getting error "cacl does not exist" because this application has been up and working for some time. All that I have done is add the @SequenceGenerator, updated the @GeneratorValue to link to the @SequenceGenerator annotation, and create the new query. I would be grateful for any ideas as to what I am doing wrong. thanks

Do you have a sequence exists with caclSeq?

SSK
– SSK

2021-02-18 03:37:34 +00:00
Commented Feb 18, 2021 at 3:37 — SSK
– SSK, Commented Feb 18, 2021 at 3:37

Timothy Clotworthy · Accepted Answer · 2021-02-19 15:33:48Z

1

My answer is based on simplifying my approach some. Now I am simply using the default sequences supplied by postgress. So for instance now I have:

@Entity
@Table(name = "TBL_CACL")
public class Cacl {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID")
    private Integer id;

    @Column(name="NAME")
    private String name;

// more stuff below...

, then in the repository (after querying postgress to get default sequences) I have:

@Query(value = "select last_value from tbl_cacl_id_seq", nativeQuery = true)
    public Integer getCurrentVal();

And then:

int nextval = caclRepository.getCurrentVal();

works fine.

answered Feb 19, 2021 at 15:33

Timothy Clotworthy

2,6727 gold badges36 silver badges68 bronze badges

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

Comments

score 0 · Accepted Answer · 2021-02-18 13:58:30Z

0

you could try removing the native query

@Query(value = "SELECT nextval('caclSeq')", nativeQuery =
        true)
Long getNextSeriesId();

edited Feb 18, 2021 at 13:58

answered Feb 17, 2021 at 21:41

user14243511

5 Comments

Timothy Clotworthy Over a year ago

Seabastion, thanks but that will not compile: "Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: Unable to resolve path [caclSeq.nextval], unexpected token [caclSeq] [SELECT caclSeq.nextval FROM fbi.prometheus.aps.clients.caclclient.model.Cacl] "

user14243511 Over a year ago

and if you try the dual? update the answer

Timothy Clotworthy Over a year ago

Sebastion, compiles but throws following exception when calling method: Caused by: org.postgresql.util.PSQLException: ERROR: relation "dual" does not exist Position: 29

user14243511 Over a year ago

Hi update the answer because the dual is bd oracle in use postgres

Timothy Clotworthy Over a year ago

Sebastian, that throws the original exception I got above: Caused by: org.postgresql.util.PSQLException: ERROR: relation "caclseq" does not exist

Collectives™ on Stack Overflow

Problem Retrieving Nextval in JPA Application

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related