Auto Increment RowID in oracle database using java

Question

Ok say I have a table with two columns. Entry_id and name. Entry_id is a ROWID NOT NULL. Essentially I just want it to increment every time something new is put in. How do i do this in the PreparedStatement. I won't know the correct Entry_id bc it doesn't matter. It should just increment everytime. So it would be nice if i could just insert the name into the table and entry_id increments automatically. Any idea how to do this?

Answer 1

A ROWID is a physical address for a row in a table. It does not make sense to use a ROWID as a key-- a ROWID will change over time if a table is moved from one tablespace to another, if you do an export and import, if row movement occurs, etc. And it does not make sense to increment a ROWID since the result would quite likely be invalid either in the sense that it would no longer be the physical address of an actual row or that it would not longer be a valid physical address.

If you want an auto-incrementing primary key in Oracle, you would declare the column as a NUMBER, not a ROWID. You would then create a sequence object

CREATE SEQUENCE entry_id_seq
  START WITH 1
  INCREMENT BY 1
  CACHE 100;

and reference the NEXTVAL of that sequence in your INSERT statement

INSERT INTO entry( entry_id, name )
  VALUES( entry_id_seq.nextval, :1 );

Of course, you could create a before-insert trigger to populate the primary key from the sequence

CREATE OR REPLACE TRIGGER get_entry_id
  BEFORE INSERT ON entry
  FOR EACH ROW
IS
BEGIN
  SELECT entry_id_seq.nextval
    INTO :new.entry_id
    FROM dual;
END;

Your INSERT statement could then omit the ENTRY_ID column, letting the trigger automatically populate it.

INSERT INTO entry( name )
  VALUES( :1 );

Answer 2

If you are happy with a database dependent way of doing this, then the usual approach is to use an oracle sequence.

After you create the sequence, which lives in the database your code would be along the lines of

p = conn.prepareStatement("insert into mytable (entry_id, name) values (mysequence.next_val,?)");
p.setString(1,"My Name");
p.executeUpdate();