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?

The id returned here is not what the actual id in the database is - do you mean in the table or the currval? — Pascal Thivent
– Pascal Thivent, Commented Jun 23, 2010 at 21:28

benstpierre · Accepted Answer · 2010-06-23 22:38:07Z

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.

answered Jun 23, 2010 at 22:38

benstpierre

33.7k56 gold badges186 silver badges292 bronze badges

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

1 Comment

Jeffrey Kemp Over a year ago

Good answer. Alternatively, you could modify the trigger so that it doesn't insert an ID if one was already provided.

Matthew Farwell · Accepted Answer · 2010-06-23 21:46:49Z

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?

answered Jun 23, 2010 at 21:46

Matthew Farwell

61.8k18 gold badges134 silver badges174 bronze badges

Collectives™ on Stack Overflow

Oracle and HIbernate, db sequence generator issues

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related