Meaningless Drivel is fun!
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

Win a copy of Elasticsearch in Action this week in the Big Data forum!
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