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

assigning a gauranteed incrementing number with no misses to a column

 
Jeevan Sunkersett
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I am using Hibernate 3.0 with Oracle as my database and have a hard requirement like

I need to populate a table say XYZ, which has a column receiptNumber.
The hard requirement is that for each row, in the table the receiptNumber steadily incrementing BUT WITH NO MISSES allowed

In other words suppose the table has records, with receiptNumber say,
10001,
10002,
10004 (note 10003 has been missed ... a not acceptable situation)

For this initially I had thought of using a a sequence to generate the receiptNumber but in case of exceptions it is possible the sequence increments but no data gets inserted (leading to the missing number situation)

So my DBA suggested, a PL/SQL procedure, in which a number will selected from a table via

select rNumber from rcptNumber_TAB for update
--followed by
insert into table XYZ
insert rNumber + 1 into rcptNumber_TAB
commit

This way their will be NO misses in the incrementing rcpt Number inserted.

Could the above be achieved exclusively using java + hibernate and no PL/SQL?

Any ideas will be appreciated.

thank you.
Jeevan
 
Rajan Chinna
Ranch Hand
Posts: 320
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When you construct your object, you can hit the DB and get next primary/sequence key from rcptNumber_TAB entity.
Ex:
XYZ p = new XYZ(someservice.getNextPrimaryKey());
p.setFirstName(first);
p.setLastName(last);
somedao.save(p);

Also you could use trigger to fetch next key.
 
Jeevan Sunkersett
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you Rajan,

In my case the reciptNumber is not a primary key; but yes what you mention seems pausible.

I understand someservice.getxxxx() .... is DAO or similar

which will do a select rNumber from rcptNumber_TAB for update ................. trip 1
next save to the main table XYZ .....................................................................trip 2
followed by updating rcptNumber_TAB with incremented rcptNumber .............trip 3

I guess I should encapsulate everything into a single transaction.

But what I see here are multiple trips (as mentioned) from java-application-server to oracle-server.

will that not degrade performance?

PS: My DBA says use of triggers is a bad practice.

~g1
 
Arun Kumarr
Ranch Hand
Posts: 661
Eclipse IDE Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What's stopping you from making the column a primary key?
Won't it solve the issue.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic