aspose file tools*
The moose likes JDBC and the fly likes primary keys Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "primary keys" Watch "primary keys" New topic
Author

primary keys

karl koch
Ranch Hand

Joined: May 25, 2001
Posts: 388
hi,
waht would be a good way to create Primary Keys ?. (integer type is ok to me)
mysql suports the auto_increment. but oracle (as i know) does not have this (they have sequences, right ?) and other db's might have something different again.
i thought of wrapping the Primary Key in some java class with something like "public int getNextKey()".
then, according to the underlying DB, i change the implementation of the PrimaryKey class but the interface remains the same.
any commecnts, code, opinions on this ???
thanks,
karl
Bjarki Holm
Author
Ranch Hand

Joined: May 25, 2001
Posts: 65
Karl,
you are probably confusing primary keys with auto-incrementing sequence values. Primary keys can consist one or more columns, of any data type, as you probably know.
I've actually confronted this problem myself once. At that time, I was building an application that should have been independent of the RDBMS being used. To accomplish that, I constructed a set of interfaces/abstract classes for general database methods, and implemented each of these for the RDBMSs I wanted to support.
Once of these abstract classes was called <code>Sequence</code>. This class had methods for getting the next value in the sequence, the current value, and so on. For Oracle, this was easily implemented by calling a DB sequence (using <code>seq_name.nextval</code>, etc.). For SQL Server, the same could be accomplished by using a table with a single IDENTITY column, and querying for <code>@@IDENTITY</code>, and so on. (Of course, this means that Oracle would be more preferred than SQL Server in this context, as Oracle's SEQUENCE object would probably give better performance.)
Also, to minimize DB queries when selecting new identity values, the <code>Sequence</code> class prefetched a considerable buffer each time a new value was needed (that is, when the previous buffer was empty), and fetched identity values from that buffer to the application upon request.
Cheers,
Bjarki Holm
Author of Professional Java Data
[This message has been edited by Bjarki Holm (edited June 12, 2001).]


Bjarki Holm
karl koch
Ranch Hand

Joined: May 25, 2001
Posts: 388
hi bjarki,
no, i dont think im confusing auto_increment with primary keys.
i thought it would be good to use an auto_increment field to assure unique values (as a sequence in Oracle).
i like your <code>Sequence</code> class idea and the prefetching.
would it be a good idea to create it as a Singelton ?
how did your sequence obtain a Connection ? did you pass a reference every getNextVal() call ?
thanks,
karl
Bjarki Holm
Author
Ranch Hand

Joined: May 25, 2001
Posts: 65
Karl,
sorry, I didn't mean to insult
I implemented a static sequence manager class, which stored a hash table of <code>Sequence</code> objects. This, effectively, made each <code>Sequence</code> instance a Singleton, if you get my meaning (i.e. only a single instance of a <code>Sequence</code> for a specific database sequence).
The <code>Sequence</code> obtained an instance of a <code>DataSource</code> at initialization time. This was used to get connections in the <code>load()</code> method (which was called when the buffer was empty), and closed at the end of that method (the data source would use connection pooling behind the scenes, of course).
Let me know if you need more information; this is a fairly common scenario when using JDBC in a web environment.
Cheers,
------------------
Bjarki Holm
Author of Professional Java Data
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
oracle has sequences in it. You use the CREATE SEQUENCE....
Then when inserting insert into testtab (seqname.nextval, name, address, ...)
Its pretty simple to use
Dan
karl koch
Ranch Hand

Joined: May 25, 2001
Posts: 388
hi dan,

i know about the sequence in oracle, im looking for a "generic" aproach to be not bound to a specific RDBMS (mysql has no sequence eventhough you can "simulate" it with auto_increment fields....
thats why i thought of having a java class doing this. when the RDBMS system is changed, then i only (well, probably there is more) have to change the Sequence.class .

karl
karl koch
Ranch Hand

Joined: May 25, 2001
Posts: 388
hi bjarki,
ok, i think i got it. and, im not insulted :-).

thanks
karl
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Originally posted by karl koch:
im looking for a "generic" aproach to be not bound to a specific RDBMS [...] when the RDBMS system is changed, then i only [...] have to change the Sequence.class.

You can do one better than that, if portability is your goal.
Remember the good old "nextnum" type table everyone used when sequences weren't yet supported at the database level? If you build your Sequence class around an oldfashioned nextnum table, it can be 100% portable.
If all you need is a primary key and you don't care about holes in the sequence, the Sequence class would not need to go to the table for each number but bulk allocate 100 or 1000 of them at a time. You can easily have several Sequence instances for the same table operating in parallel (important when it needs to work in a clustered environment; in J2EE, most singletons are not always singletons).
- Peter

[This message has been edited by Peter den Haan (edited June 13, 2001).]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: primary keys
 
Similar Threads
Entity Bean instances when mapped to a database table
Why EJB require Primary key class
To identify Primary Keys in a table
Create method [B&S]
two or more primary keys