Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Funny number --- oracle sequence + Hibernate

 
tao gu
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, Gurus

I encountered this very funny problem when using Hibernate to insert a records to Oracel(v8.1.7) using sequence.

The code scriptlet is:

-------------------Main.java---------------------------------
NetworkService newns = new NetworkService();
newns.setExternalReferenceNo("testrefenece");
newns.setServiceNo("99999999");
newns.setServiceStartDate(new Timestamp(new Date().getTime()));
newns.setServiceEndDate(null);
newns.setServiceType(1);
newns.setStatus(1);
newns.setStatusStartDatetime(new Timestamp(new Date().getTime()));
newns.setTransactionID(1111111);
newns.setLastUpdatedDatetime(new Timestamp(new Date().getTime()));
session.save(newns);
-------------------------------------------------------
The primary key is serviceID and you can see that the primary key ServiceID is not set because I want Oracle to generate it using sequence.

So I indicate the usage of sequence in hbm file.

------------------------NetworkService.hbm.xml-----------------------------
<id name="serviceID" type="integer" column="SERVICE_ID" unsaved-value="-1">
<generator class="sequence">
<param name="sequence">ATM_STATUS_HISTORY_IDS</param>
</generator>
</id>
---------------------------------------------------------------------------

Then I run the program and the records is inserted successfully. I also show the generated SQL on the console and everything seems ok. No exception. No error.

But When I look at the database, I found the column serviceID(primary key generated by sequence) is some funny numbers : "1.47E-52" and "1.9E46" ...
. But it is supposed to be an integer start from 1.

I dont know what is wrong. Is it hibernate problem( I use both 2.1.8 and 3.0)? or Oracle problem? It's hard to debug since no exception is throw out. Do I need to read the hibernate source to find the cause? Or anyone can give a guess?

Thanks
Tony
 
tao gu
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, All

Regarding the above question, I got some new interesting findings.

When I run the following code, I got the problem:

---------------
NetworkService newns = new NetworkService();
newns.setExternalReferenceNo("testrefenece");
newns.setServiceNo("99999999");
newns.setServiceStartDate(new Timestamp(new Date().getTime()));
newns.setServiceEndDate(null);
newns.setServiceType(1);
newns.setStatus(1);
newns.setStatusStartDatetime(new Timestamp(new Date().getTime()));
newns.setTransactionID(1111111);
newns.setLastUpdatedDatetime(new Date());
session.save(newns);
------------------------------------------

The inserted record's primary key (generated by sequence) is "-3.7E-7"


But if I set the LastUpdateDateTime to null and run again
---------------
NetworkService newns = new NetworkService();
newns.setExternalReferenceNo("testrefenece");
newns.setServiceNo("99999999");
newns.setServiceStartDate(new Timestamp(new Date().getTime()));
newns.setServiceEndDate(null);
newns.setServiceType(1);
newns.setStatus(1);
newns.setStatusStartDatetime(new Timestamp(new Date().getTime()));
newns.setTransactionID(1111111);
newns.setLastUpdatedDatetime(null);
session.save(newns);
------------------------------------------

The primary key is 15, which is the expected.

For both cases, the generated sql code is :

Hibernate: select ATM_NETWORK_SERVICE_IDS.nextval from dual
Hibernate: insert into ATM_NETWORK_SERVICE (TRANSACTION_ID, SERVICE_NO, SERVICE_TYPE, EXTERNAL_REFERENCE_NO, SERVICE_START_DATE, SERVICE_END_DATE, STATUS, STATUS_START_DATETIME, LAST_UPDATED_DATETIME, SERVICE_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

This looks like an data overflow ...
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic