• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Rob Spoor
  • Devaka Cooray
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • Tim Holloway
Bartenders:
  • Jj Roberts
  • Al Hobbs
  • Piet Souris

assigning a gauranteed incrementing number with no misses to a column

 
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 320
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 662
Eclipse IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What's stopping you from making the column a primary key?
Won't it solve the issue.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic