File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes EJB and other Java EE Technologies and the fly likes how to handle autogenerated numbers in oracle using CMP Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "how to handle autogenerated numbers in oracle using CMP " Watch "how to handle autogenerated numbers in oracle using CMP " New topic

how to handle autogenerated numbers in oracle using CMP

Nova Bhojwani

Joined: May 14, 2001
Posts: 5
i am using CMP entity beans and am using oracle as my database, i beleive that oracle does not support autonumber generated fields as does SQL Server.
when using CMP we do not write any SQL code so how do i make a column autogenerated ...for example in a table users the userID should get incremented on it's own.....
if possible send through a snippet of the code of how it is handled.
Desai Sandeep
Ranch Hand

Joined: Apr 02, 2001
Posts: 1157
I believe Oracle supports autonumber generation using SEQUENCES.You have to create a sequence using CREATE SEQUENCE DDL statement.This may be done using SQL PLUS.Then you can autogenerate numbers using it using SEQUENCE_NAME.NEXTVAL in your SQL query statement.
Hope this helps.

  • Sun Certified Programmer for Java 2 Platform Scored 93 per cent
  • Oracle JDeveloper Rel. 3.0 - Develop Database Applications with Java Scored 56 out of 59
  • IBM Enterprise Connectivity with J2EE Scored 72 per cent
  • Enterprise Development on the Oracle Internet Platform Scored 44 out of 56

  • [This message has been edited by Desai Sandeep (edited May 17, 2001).]
Peter den Haan
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Sandeep's right. To elaborate just a little bit:
You can use that sequence in your EJB to retrieve the primary key before executing the actual INSERT statement. A frequently used alternative is to generate the key in an Oracle row-level insert trigger.
Since ejbCreate() needs to return the primary key value, doing a separate "SELECT sequence.NEXTVAL FROM DUAL" just before the INSERT is probably most convenient. If you would generate it in a trigger you would have to figure out how to get the generated value so you can return it. The only reliable ways as far as I know would be using an alternative primary key in your table (if there is one) to reread the record, or to add a RETURNING clause to the INSERT statement. I'm not sure how to do the latter using JDBC (anyone?).
Sequences can be a bit harder to use than SQL Server identity columns but they're much more flexible.
- Peter
syed mraza

Joined: Aug 23, 2001
Posts: 28
hi guys
i've a query, in CMP we dont write sql statements then how to handle this situation??? i have a table whose PK is autognerated(seq) so whn i'm calling ejbCreate(not_pk_field, some_other_not_pk_field)i'm not able to create a row in the table. the reason: CMP didnt somehow generated the key first. any suggestions how i can let it know tht it has a sequence to generated PK? i know there is some tags to be added if one is using weblogic 6.1 but i;m using 6.0, those tags dont work in 6.0.
any help in this regard would be appriciated.
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
In CMP you would create a method getNextID for example which gets a connection from the pool, and sets the value of the id field in the ejbCreate method of the bean.
However, WLS 6.1 has a framework for key generation for (AFAIR) Oracle, Sybase and DB2.
Jerome Lumpkin

Joined: Aug 28, 2001
Posts: 10
It seems a little odd to go with CMP and then place SQL code in your bean or helper class to access the database for a unique id.
Also, this type of access will ruin your portability as it is an Oracle Method, not a universal solution.
Why don't you consider either having an entity bean with one persistent field, an int/long that just increases by one or either perhaps consider Local Objects.
I agree. Here's the link:
subject: how to handle autogenerated numbers in oracle using CMP
It's not a secret anymore!