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.

Answer 1

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;
}

Answer 2

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)

Answer 3

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