Auto generated primary key ID from prepared statement

Question

My first post here so go easy on me :).

So I created a database table using the following code.(Im using Oracle 10g and Oracle JDBC. Im having this servlet code communicate with a HTML form I made.)

CREATE TABLE GM_Recipes
(
  recipe_ID NUMBER(4) PRIMARY KEY,
  rec_name VARCHAR2(50),
  recipe_cat VARCHAR2(50),
  rec_desc VARCHAR2(1000),
  author VARCHAR2(50)
);

Now my servlet code is as such:

// Fill Recipes table
        PreparedStatement pstmt = con.prepareStatement("INSERT INTO GM_Recipes(rec_name,recipe_cat,rec_desc,author) VALUES (?,?,?,?)");
        pstmt.clearParameters();

        String opt1 = req.getParameter("RecName"); //parameters from HTML form
        String opt2 = req.getParameter("choice"); //parameters from HTML form
        String opt3 = req.getParameter("CookDesc"); //parameters from HTML form
        String opt4 = req.getParameter("author"); //parameters from HTML form

        pstmt.setString(1,opt1);
        pstmt.setString(2,opt2);
        pstmt.setString(3,opt3);
        pstmt.setString(4,opt4);

        ResultSet rs = pstmt.executeQuery();

What Id like to do is insert a row into the GM_Recipes table but have the recipe_ID be auto generated.(As through a HTML form I won't ask someone to enter a recipe ID for obvious reasons)

Later parts of my code are reliant on a recipe_ID for new recipes that would be created.

Ive tried some generate keys code that I found from google but I've been at it for a few hours and was curious if anyone could share some meaningful insight :) Thanks

I should mention that I hardcoded/ inserted some data into the table already before any of this code.(When I was writing the sql code) So at the moment I have 3 'test' recipes in the GM_Recipes table. SO if I insert a new recipe into the table, I would like a recipe_ID of 4 to be generated etc(increments with every new recipe added into the table). — ForgottenGhost, Apr 06 '16 at 22:13

score 2 · Answer 1 · answered Apr 06 '16 at 22:17

You should create a sequence db side (let's call it YOUR_SEQUENCE), then use it to generate the id:

PreparedStatement pstmt = con.prepareStatement("INSERT INTO GM_Recipes(recipe_id, rec_name,recipe_cat,rec_desc,author) VALUES (YOUR_SEQUENCE.nextval, ?,?,?,?)";

P.S. If you already have records on that table, watch out for a suitable sequence start value.

access_granted · Answer 2 · 2016-04-08T17:41:00.727

in 10g & 11g auto increment columns are not available. You have to create a named sequence, a trigger and use nextval expression to seed your primary key.

--create sequence seq_gm_recipes;
--this would work, if your table doesn't have records yet
--otherwise use the below
declare i_start int;
begin
  select max(recipe_id)+1 into i_start from GM_Recipes;
  execute immediate 'create sequence seq_gm_recipes start with ' || i_start;
end;

CREATE OR REPLACE TRIGGER trg_GM_Recipes
BEFORE INSERT ON GM_Recipes
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
  l_Var NUMBER;
BEGIN
   SELECT seq_gm_recipes.NEXTVAL INTO l_Var FROM dual;
   :NEW.recipe_id := l_Var;
END;

This is PL/SQL, obviously, to be executed directly in sql*plus or TOAD.

ForgottenGhost · Answer 3 · 2016-04-08T10:35:10.290

0

Thanks for everyones help!

I performed an unorthodox method and got what I wanted working.

stmt22 = con.createStatement();    
ResultSet ChoMax = stmt22.executeQuery("");
ChoMax.next();

int increm = ((Number) ChoMax.getObject(1)).intValue();
if(increm != 0){increm++;}

Then used the increm variable to insert back into my preparedStatement code

edited Apr 08 '16 at 10:35

answered Apr 08 '16 at 10:21

ForgottenGhost

11
4

Auto generated primary key ID from prepared statement

3 Answers3