Link to home
Start Free TrialLog in
Avatar of intlgd
intlgd

asked on

Oracle sequence grows by inconsistant number when persisting with Hibernate

Hello there,

I have stripped my project down to a very simplistic project and am trying to persist two classes ( a parent and then the child class ). I'm not sure exactly where the issue is so I"m going to explain the whole problem.

1. Persisting the parent: When I persist the parent it saves to the database just fine. The weird thing is that I end up with a different value in my Key then the sequence actually generates. The sequence seems to be skipping one for some reason.

            //Monitor key is null - NEW record
            getHibernateTemplate().persist(monitor);
            //Monitor key is 1 less than the value in the db
            monitor = findByName(monitor.getName());
            //Monitor now +1 of the value before retrieving it by name

I am using sequence to generate the keys:

@SequenceGenerator(
    name="BTD_MON_SEQ",
    sequenceName="BTD_MON_SEQ",
    allocationSize=1
)
public class Monitor implements java.io.Serializable {
.....

     @Id
     @GeneratedValue(strategy=SEQUENCE, generator="BTD_MON_SEQ")
     @Column(name="mon_key", unique=true, nullable=false)
     public Long getKey() {
     .....
     }

    @OneToMany(targetEntity = com.cisco.btd.model.MonitorHistory.class,
                          cascade = CascadeType.ALL, fetch = FetchType.EAGER, mappedBy = "monitor")
    @Where(clause="end_date is null")
    public List<MonitorHistory> getHistory() {
        return this.history;
    }

.....

}

The sequence above is defined in the database to increment by 1. So I don't fully understand what's going on. So to clarify, I will have a value of 35 in my key property after the persist, but if I query the database at that point I have 36 in the database.

My next issue relating to this same problem I'm trying to solve is that if I query the database by name and get the correct key value and then try to create a child record and persist it I get a constraintviolation exception. See below for the full error.

As you can see in the above code snippet I have defined an @OneToMany for the child class in the parent. The relationship from the child is @ManyToOne. See code snippet below:

@Entity
@Table(name = "btd_mon_hist", catalog = "btd")
@SequenceGenerator(
    name="BTD_MON_HIST_SEQ",
    sequenceName="BTD_MON_HIST_SEQ",
    allocationSize=1
)
public class MonitorHistory implements java.io.Serializable {
......
    @Id
    @GeneratedValue(strategy=SEQUENCE, generator="BTD_MON_HIST_SEQ")
    @Column(name = "mon_hist_key", unique = true, nullable = false)
    public Long getKey() {
        return key;
    }

.......

    @ManyToOne(targetEntity = com.cisco.btd.model.Monitor.class, fetch = FetchType.LAZY)
    @JoinColumn(name = "mon_key", nullable = false)
    public Monitor getMonitor() {
        return monitor;
    }

.......

}

When I try to execute the following code I get the exception shown below:

MonitorHistory newHistory = new MonitorHistory(monitor);
newHistory.setStartDate(new Date());
getHibernateTemplate().persist(newHistory);



Please note that all of this works flawlessly when using MySQL. This issue has only surfaced in trying to migrate to Oracle. Any help would be greatly appreciated.

Thanks!
Exception in thread "main" org.springframework.dao.DataIntegrityViolationException: Could not execute JDBC batch update; nested exception is org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
        at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:622)
        at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
        at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:378)
        at org.springframework.orm.hibernate3.HibernateTemplate.persist(HibernateTemplate.java:749)
        at com.cisco.btd.service.MonitorServiceImpl.logHistory(MonitorServiceImpl.java:119)
        at com.cisco.btd.service.MonitorServiceImpl.save(MonitorServiceImpl.java:86)
        at Runner.main(Runner.java:42)
Caused by: org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:253)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:237)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:141)
        at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
        at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
        at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
        at org.springframework.orm.hibernate3.HibernateAccessor.flushIfNecessary(HibernateAccessor.java:390)
        at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:374)
        ... 4 more
Caused by: java.sql.BatchUpdateException: ORA-02291: integrity constraint (BTD.BTD_MON_HIST_FK) violated - parent key not found
 
        at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:343)
        at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10768)
        at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
        at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
        at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
        ... 11 more

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of intlgd
intlgd

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sean Stuber
Sean Stuber

Is your application multi-threaded?  If so, is one thread trying to insert children before another thread inserts the parent?  Even if it's not multi-threaded, is the code arranged so that children get created before the parent?

If so, you need to use deferred constraints or rearrange the code so that parents are created before children.

As for the sequence, if you select sequence_name.next_val, you will always get a new number.  You will need to select sequence_name.curr_val instead to get the most recently selected value.

Also note oracle sequences are not transactional (don't know if mysql are or not) so, if you select 35 then rollback your transaction, the next time you select it will be 36 because the sequence increment does not rollback.
oh too slow on my response

glad you found it though