Oracle and HIbernate, db sequence generator issues.

Question

I have the following entity (getters and setters ommited)...

@Entity
@Table(name = "TBL_PROJECT_RUN")
public class ProjectRunEntity {

    @Id
    @Column(name = "ID")
    @GeneratedValue(generator = "StakeholdersSequence")
    @SequenceGenerator(name = "StakeholdersSequence", sequenceName = "STAKEHOLDERS_UPDATE_SEQ", allocationSize = 1)
    private Integer id;

    @Column(name = "REPORT_RUN_DATE")
    private Date systemRunDate;

    @Column(name = "JIRA_PROJECT_NAME")
    private String jiraProjectName;

    @Column(name = "JIRA_PROJECT_DESC")
    private String jiraProjectDescription;

    @Column(name = "RUNBY")
    private String runBy;

    @Column(name = "HEADER_TEXT")
    private String headerText;

    @Column(name = "FOOTER_TEXT")
    private String footerText;

    @Column(name = "JIRA_ID")
    private String jiraId;

    @Column(name = "TO_EMAIL")
    private String toEmail;

    @Column(name = "CC_EMAIL")
    private String ccEmail;

    @OneToMany(fetch = FetchType.EAGER, mappedBy = "projectRunEntity")
    private List<ProjectRunDetailsEntity> projectRunDetailsEntities;

Then I commit it to the database like this...

 final Session session = sessionProvider.get();
    session.persist(projectRunEntity);
    session.flush();
    return projectRunEntity.getId();

The id returned here is not what the actual id in the database is, it is 1 or 2 off. Please note I am sharing one sequence for all ids in all tables in my project so that any given entity has a project-wide unique index. What would cause the id to be incorrect like this?

Answer 1

Two possibilities spring to mind:

You say you have a shared sequence. Are you inserting into other tables at the same time? (which could increment the sequence).

Is there a trigger on the database table which inserts a sequence value into the id column?

Answer 2

It turns out that the web ui for oracle express automatically created triggers than insert an id from this sequence before inserting my row object. This meant that the generator in oracle was always 1 behind. To solve this I removed the triggers.