File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes How to get the next number of a table before insertion a record Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to get the next number of a table before insertion a record" Watch "How to get the next number of a table before insertion a record" New topic
Author

How to get the next number of a table before insertion a record

Pavan Koppolu
Greenhorn

Joined: Aug 05, 2011
Posts: 6
Hi,
i have a scenario like this, my requirement is i get the next number(primary key) of a table before insertion a new record. I am using oracle database, so there no need to put auto increament option.

I have two possibilities,
1. write a sequence and get sequence.nextval to get the next number
2. Write a java code for getting the next number.
For this, i count the no of the records of the table and increament by 1. But the problem , when multiple users request the same page, there may be a chance to get primary key voilation.

Otherwise, my method is set to be synchorized, but it is a performance problem.


So, please tell me what is the best solution for this scenario.


Thanks
Pavan
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61094
    
  66

The problem with the 2nd approach isn't that it's a performance issue, but that it sets up a race condition.

Use a sequence.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Pavan Koppolu
Greenhorn

Joined: Aug 05, 2011
Posts: 6
First of all, thanks for your reply.

i think use the sequence, there may be a problem like this it dont give the next number of the previous record rather than it gives the some random number of the sequence.

So, please suggest me whether this issue is raised or not for going in the right direction.

Thanks
Pavan
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61094
    
  66

Why does it need to be consecutive? As long as it's unique in the table, the value is fine to use as a primary key. If you are using the id values for some other reason than uniquely identifying the record, your data model is screwed up. Primary keys should just be primary keys and serve no other business use.

That said, a sequence is guaranteed to give the next consecutive value from its own counter.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Bear Bibeault wrote:That said, a sequence is guaranteed to give the next consecutive value from its own counter.

Not exactly. Sequences are not gap-free and it is not possible to make them so.

Sequences were designed in Oracle to be used together with the RETURNING INTO clause. This way the value can be generated from the sequence, stored into a table and returned to the client in one (JDBC) call. Google up Oracle RETURNING INTO clause if you're interested. I can post an example if someone wants.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61094
    
  66

Not an Oracle user, so I guess you can't even count on that.

But the question remains, why is the actual value of the primary key important? it shouldn't be.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Bear Bibeault wrote:But the question remains, why is the actual value of the primary key important? it shouldn't be.

You may need the value of primary key to be able to refer to the new record in the application. You might need it to populate child tables too, though there is the sequence's CURRVAL value for this in Oracle.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61094
    
  66

A primary key should not be used for anything, including ordering. It should be a meaningless unique values whose only purpose is to identify a single record.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Bear Bibeault wrote:A primary key should not be used for anything, including ordering. It should be a meaningless unique values whose only purpose is to identify a single record.

I wholeheartedly agree that surrogate primary key values should be meaningless and not used for ordering (natural primary keys are something different, of course).

Often, the record can be created in the database and forgotten about. In this case, value of its PK is irrelevant. However, there are many perfectly legal usages of primary keys of newly created records:
  • Populating the child tables (already mentioned, though this is not a valid reason in Oracle, it is necessary in other databases).
  • Putting the newly created record into some application cache.
  • Writing the newly created record together with its primary key into a log to assist in tracking/debugging.
  • The newly created record may be displayed in the user interface somehow and the PK may be needed for further user interactions.

  • These are the most common cases I usually encounter. In all these cases the PK of newly created record needs to be read by the application, and in Oracle, the RETURNING INTO clause is the way to go.
    Bear Bibeault
    Author and ninkuma
    Marshal

    Joined: Jan 10, 2002
    Posts: 61094
        
      66

    I don't disagree with any of that. That's all orthogonal to my point is that expecting the PK to be a specific value for some business reason is a poor practice that will lead to trouble.
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: How to get the next number of a table before insertion a record