File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Object Relational Mapping and the fly likes oracle sequence id and table data insertion Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Reply locked New topic

oracle sequence id and table data insertion

Raj Ohadi
Ranch Hand

Joined: Jun 30, 2006
Posts: 316
In many document, it is recommeded to use unique system generated id as the PK for a table, for example

CD (id, author, title)
Track (id, cd_id, name, length)

"Track" tables contains a"cd_id" to refer to "CD" table. As a newbie, I have a few questions, please bear with me --

1. Create table. Suppose I use Oracle, when I create tables, besides theses two tables, do I have to manually create any other table in order to make these "id" be generated ?

2. Insert data -- Supose tables are created, if I want to do a regular insert of a row for Track, when I write my SQL, how do I know what's the "id" and "cd_id" ? Normally I just do "INSERT INTO TRACK VALUES("Gone with wind", 200)", but will this still work in this scenario ??

3. REgular SQL query, as an application user I have no idea about the value of "id" and "cd_id" for rows in the tables, so does the regular query
"SELECT * from CD, TRACK where = TRACK.cd_id and CD.arthor = "Steve""
still work (i.e. still query by regular attributes and just use the unique id for joining) ?

Shailesh Kini
Ranch Hand

Joined: Oct 17, 2001
Posts: 153
Hi Raj,

Your questions are oracle specific and not related to ORM.

1. You will have to create a sequence which will store the values for the id to be created.

2. you will have to use <SEQ>.NEXT_VAL in your insert statement. With JPA however you just have to annotate a sequence generator for our id column and JPA will take care of the insert statements for you.

3. Yes you should be able to execute that sql without any problems. You might have to use NaviteQuery annotation to declare it.

Shailesh Kini.
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33129

Closing this thread since the author found the correct forum on this own!

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
I agree. Here's the link:
subject: oracle sequence id and table data insertion
jQuery in Action, 3rd edition