Re: [JBoss-user] Auto Incrementing Primary Key with PostgreSQL and JBoss

[Ricardo b. <ri...@te...>]

You should get the PRIMARY KEY before inserting the row.
Create a new method called getNewId() (or something like this...)

Use the following query to get the id:
"SELECT nextval('project_idproject_seq')"

the result of this will be your desired PK.

All you have to do now is to use this value in your INSERT:
"INSERT INTO project
  (idproject,\"projectName\",budget,start,end)
 VALUES ( ?, ? , ? , ? , ? )"

Now you have the PK to be returned by ejbCreate...

I hope this helps.

Ricardo Barone


Federico Vesco wrote:
> 
> Hi!
> I have the following problem.
> I create the following table (with PostgreSQL) :
> 
> CREATE TABLE "project" (
>    "idproject" SERIAL,
>    "projectName" varchar(64),
>    "budget" int4,
>    "start" date,
>    "end" date,
>     CONSTRAINT "project_pkey" PRIMARY KEY ("idproject")
> );
> 
> In this way  the primary key idproject is auto incrementing.
> I'm  using an BMP entity bean: as you know, the ejbCreate method must return
> the primary key.
> My ejbCreate method calls insertRow, a function which insert the newly
> created bean into the database.
> In insertRow i don't need to specify the value of idproject 'cause it's
> generated by the DB during the insertion.
> The code is:
> 
> private Integer insertRow (String projectName,int budget,java.sql.Date
> start,java.sql.Date end) throws SQLException {
> 
>           System.out.println(" insertRow() called ");
>           String insertStatement =
>                 "insert into project
> (\"projectName\",\"budget\",\"start\",\"end\") values ( ? , ? , ? , ? )";
>          ..............................
>          prepStmt.executeUpdate();
>           prepStmt.close();
>         ...............................................
> 
> How may  i obtain the value of the primary key generated by PostgreSQL ( i
> need to obtain this value because i have to return the primary key value) ?
> I thougth I migth obtain it with a simple select query, but in this way i'm
> not sure the value I obtain is exactly the one needed (by definition, only
> the primary key identifies univokely a record).
> Any suggestion? How can i manage this situation? Do you think I have do
> change my program?