sequence does not exist, hibernate and JPA 2.1

Question

I am getting an error saying

`Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause
java.sql.SQLSyntaxErrorException: ORA-02289: sequence does not exist`

This error happens when I try to create a user.

  @RequestMapping(method = POST)
    public UserDto createUser(@RequestBody userDto user) {
        Preconditions.checkNotNull(user);

        return Preconditions.checkNotNull(service.create(user));
    }

I am however able to delete and get just not create nor update. What is also frustrating is I get no error when trying to update, it just doesn't so it.

I am not getting any real lead on where to look. I have tried many different methods to resolve this with no avail.

I found a post that had this:

@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SEQUENCE1")
@SequenceGenerator(name="SEQUENCE1", sequenceName="SEQUENCE1", allocationSize=1)
private int user_id;

At this link: SOF link

It is complaining about this entity which I generated with netbeans and I am currently using Intellij. Any advice would be appreciated.

What happens when you run `SELECT COUNT(*) FROM user_sequences WHERE sequence_name = 'SHOP_SEQ';`? Do the same for `CAMPAIGN_SEQ`. — Dherik, Apr 20 '18 at 16:44
can you debug the exact query which is automatically executed wihtin Dao layer ? — this_is_om_vm, Apr 27 '18 at 16:23
@SHIVOMPANDEY I am outputting the query. I declared this is application.properties — Mike3355, Apr 27 '18 at 16:43

score 3 · Accepted Answer · answered Apr 24 '18 at 07:30

3

The code that creates new Campaign entity seems to be incorrect.

public CampaignDto create(CampaignDto campaignDto) {
    Campaign campaign = mapper.mapReverse(campaignDto);

    System.out.println(campaign.toString());

    // Following 2 lines must be added to obtain and use managed Shop entity
    Shop existingShop = shopRepository.findOne(campaignDto.getShopId());
    campaign.setShop(existingShop);

    campaign = campaignRepository.save(campaign);
    CampaignDto createdCampaign = mapper.map(campaign);

    return createdCampaign;
}

answered Apr 24 '18 at 07:30

Nikolai Shevchenko

7,083
8
33
42

@Drew1208 make sure that it was created in DB, otherwise you need to create it manually. `CREATE SEQUENCE CAMPAIGN_SEQ; COMMIT;` (or whatever name you use) – Nikolai Shevchenko Apr 24 '18 at 11:31
Emmm, `DEV_STAG.CAMPAIGN_CHECK_STATUS_TYPE` seems to be irrelevant to the code/scheme that you've posted so far. What is inside this constraint? How do you insert Shop record? – Nikolai Shevchenko Apr 24 '18 at 11:56
Of course you can play with different code combinations, but the root of problem is within your DB since you couldn't event insert a **shop_table** record correctly. Try to truncate `shop_table` (or whatever named) and insert first shop `insert into shop_table (id, ...) values (shop_seq.nextval, ...)` – Nikolai Shevchenko Apr 25 '18 at 06:47

Karol Dowbecki · Answer 2 · 2018-04-20T17:06:11.023

1

It looks like you might not be setting Campaign.shopId field when creating new Campaign.

@JoinColumn(name = "SHOP_ID", referencedColumnName = "SHOP_ID")
@ManyToOne(optional = false)
private Shop shopId;

You might want to rename this field to just shop to make it clear what it holds as it's not just an identifier.

Depending on how you are persisting new objects you might need to add CascadeType.ALL on @ManyToOne to ensure that a new Shop is persisted together with a new Campaign.

@ManyToOne(optional = false, cascade = CascadeType.ALL)

edited Apr 20 '18 at 17:06

answered Apr 20 '18 at 16:48

Karol Dowbecki

43,645
9
78
111

2

Are you sure that your `SequenceGenerator` and JPA dialect supports `BigDecimal` data type correctly? Looks like it currently fails to persist `BigDecimal` value. This might depend on the JPA dialect but it's generally easier to define `id` fields as `long`. – Karol Dowbecki Apr 20 '18 at 17:40
Did you persist the `Shop` object before persisting `Campaign`? If so please add the code that shows how are you doing it. Your mapping in your question still doesn't use cascades as suggested in my answer. – Karol Dowbecki Apr 23 '18 at 11:18

score 1 · Answer 3 · answered Apr 27 '18 at 16:54

1

Go to your application property file and put hibernate.hbm2ddl.auto=true; It might be helpful Hibernate created this sequence and added a new row

answered Apr 27 '18 at 16:54

this_is_om_vm

608
5
23

sequence does not exist, hibernate and JPA 2.1

3 Answers3