Bookmark Topic Watch Topic
  • New Topic

oracle sequence id and table data insertion

 
Raj Ohadi
Ranch Hand
Posts: 316
  • Mark post as helpful
  • send pies
  • Report post to moderator
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 CD.id = TRACK.cd_id and CD.arthor = "Steve""
still work (i.e. still query by regular attributes and just use the unique id for joining) ?

Thanks.
 
Shailesh Kini
Ranch Hand
Posts: 153
  • Mark post as helpful
  • send pies
  • Report post to moderator
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.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34863
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Report post to moderator
Closing this thread since the author found the correct forum on this own!
 
    Bookmark Topic Watch Topic
  • New Topic