Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
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 Android Security Essentials Live Lessons this week in the Android 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
Author

how to handle autogenerated numbers in oracle using CMP

Nova Bhojwani
Greenhorn

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.
thanks......
Desai Sandeep
Ranch Hand

Joined: Apr 02, 2001
Posts: 1157
Hi,
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.
Regards,
Sandeep

  • 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).]


<b>Sandeep</b> <br /> <br /><b>Sun Certified Programmer for Java 2 Platform</b><br /> <br /><b>Oracle Certified Solution Developer - JDeveloper</b><br /><b>-- Oracle JDeveloper Rel. 3.0 - Develop Database Applications with Java </b><br /><b>-- Object-Oriented Analysis and Design with UML</b><br /> <br /><b>Oracle Certified Enterprise Developer - Oracle Internet Platform</b><br /><b>-- Enterprise Connectivity with J2EE </b><br /><b>-- Enterprise Development on the Oracle Internet Platform </b>
Peter den Haan
author
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
Greenhorn

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.
rgds
Anonymous
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
Greenhorn

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.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: how to handle autogenerated numbers in oracle using CMP
 
Similar Threads
Auto generated Primary Key handling with CMP EJB
How to create a Finder method in CMP entity bean
automatic key generation for CMP entity bean(in WSAD/websphere)
CMP EJB's and Autonumber Field Problem
Casting EJBs in a Collection returned by a finder method