Using Sequence ID Generator

[rrvvg_1976]

Dear all,

We are just moving from a jdbc stored-proc technology to spring/hibernate. Currently, I am using a regular db2-sql sequence generator

 select (nextval for column1_seq) from sysibm.SYSDUMMY1

Now, if my intention is to do the same using Spring/Hibernate, I did search the forum, but could not quite understand conceptually/technically as to how I should do this.

Could someone please point me to the right documentation/tutorial/code-tips?

Thank you.

-Vijay

[Jörg Heinicke]

Since Hibernate offers to set the id generator to "sequence" I guess they handle it internally and you don't have to bother with it.

Joerg

[rrvvg_1976]

Thank you Joerg. But I'm getting an exception when I don't set a value.

Here is my mapping:

<id name="txnId" type="java.lang.String" column="TRANSACTION_ID" >
    <generator class="sequence">
        <param name="table">sysibm.SYSDUMMY1</param>
        <param name="column">cdhc.transaction_id_seq</param>
    </generator>
</id>

And this is the exception:

Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not get next sequence value
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
	at org.hibernate.id.SequenceGenerator.generate(SequenceGenerator.java:96)
	at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:98)
	at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:186)
	at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:175)
	at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:98)
	at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
	at org.hibernate.impl.SessionImpl.fireSaveOrUpdate(SessionImpl.java:507)
	at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:499)
	at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:495)
	at cdhc.dao.hbn_impl.TransactionDAO.insertTxn(TransactionDAO.java:50)
	at abc.test.testDAO.main(testDAO.java:27)
Caused by: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/6000] SQL0204N  "DEVTEST.HIBERNATE_SEQUENCE" is an undefined name.  SQLSTATE=42704

	at COM.ibm.db2.jdbc.net.SQLExceptionGenerator.throw_SQLException(Unknown Source)
	at COM.ibm.db2.jdbc.net.SQLExceptionGenerator.check_return_code(Unknown Source)
	at COM.ibm.db2.jdbc.net.SQLExceptionGenerator.check_return_code(Unknown Source)
	at COM.ibm.db2.jdbc.net.DB2PreparedStatement.executeQuery(Unknown Source)
	at org.hibernate.id.SequenceGenerator.generate(SequenceGenerator.java:75)
	... 10 more

Thanks,
-Vijay

[rrvvg_1976]

Hi,

Thanks to Greg from myeclipse support (http://www.myeclipseide.com/PNphpBB2...ic-t-5286.html), I could fix this problem. Following is the solution:

<id name="txnId" type="java.lang.String" column="TRANSACTION_ID" >
    <generator class="sequence">
        <param name="sequence">cdhc.transaction_id_seq</param>
    </generator>
</id>
<!-- where cdhc is the name of the table, and transaction_id_seq is a sequence that has already been set in the database for the transaction_id column -->

Thanks to everyone.

-Vijay