Thread: need help of getting PK after insertRow in JDBC

need help of getting PK after insertRow in JDBC

From
"Chen, Dongdong (GE Healthcare, consultant)"
Date:
Hi:
    I am a software engineer from GE. I am using JDBC to operate PostgreSQL8.3 in Ubuntu8.04. The develop environment is Eclipse3.2 My problem is:
    There is a PostgreSQL table XX containing 5 fields: AA, BB, CC, DD, EE, AA is primary key and auto-generated type, BB, CC, DD and EE is string type.
    I want to get the value of AA immediately after insert a row into the table. the code is like this:
 
    Statement st = db.creatStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    ResultSet rs=st.executeQuery("SELECT * FROM XX");
    rs.moveToInsertRow();
    rs.updateString(BB, "b");
    rs.updateString(CC, "c");
    rs.updateString(DD, "d");
    rs.updateString(EE, "e");
    rs.insertRow();
    rs.moveToCurrentRow();
    int index = rs.getInt("AA");
    System.out.println(index);
 
   in the last sentence I always get 0 no matter how many records I insert. I tried other ways of moving the cursor including next(), last() and so on, and also cannot get the correct value. I tried the drivers both postgresql-8.3-603.jdbc3.jar and postgresql-8.3-603.jdbc4.jar.
 
    But when I use pdadminIII to check the table XX, the AA field is already auto-generated with the correct value.
 
    I found a way to solve this: close resultset and statement after moveToCurrentRow() and re-open them, and rs.last(), then run int index=rs.getInt("AA"), I can get the correct value. I think this method is sort of awkward, anyone knows a better way and the correct operations?
 
I am not sure it is proper to send this mail to this mail list. Sorry if bring you any inconvenience.
Thanks a lot!
 
Best Regards
 
Kevin Chen/ChenDongdong
+8613810644051
 
 
Attachment

Re: need help of getting PK after insertRow in JDBC

From
Martin Gainty
Date:
Good Afternoon

add the CLOSE_CURSORS_AT_COMMIT Option to the Statements capabilities e.g.
    Statement st = db.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE.ResultSet.CLOSE_CURSORS_AT_COMMIT);

//then tell the connection handle to commit the DML to the DB
db.commit();

Martin

Control your own destiny or someone else will -- Jack Welch
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.



Subject: [GENERAL] need help of getting PK after insertRow in JDBC
Date: Fri, 26 Sep 2008 10:14:01 +0800
From: Dongdong.Chen@ge.com
To: pgsql-general@postgresql.org; pgsql-jdbc@postgresql.org

Hi:
    I am a software engineer from GE. I am using JDBC to operate PostgreSQL8.3 in Ubuntu8.04. The develop environment is Eclipse3.2 My problem is:
    There is a PostgreSQL table XX containing 5 fields: AA, BB, CC, DD, EE, AA is primary key and auto-generated type, BB, CC, DD and EE is string type.
    I want to get the value of AA immediately after insert a row into the table. the code is like this:
 
    Statement st = db.creatStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    ResultSet rs=st.executeQuery("SELECT * FROM XX");
    rs.moveToInsertRow();
    rs.updateString(BB, "b");
    rs.updateString(CC, "c");
    rs.updateString(DD, "d");
    rs.updateString(EE, "e");
    rs.insertRow();
    rs.moveToCurrentRow();
    int index = rs.getInt("AA");
    System.out.println(index);
 
   in the last sentence I always get 0 no matter how many records I insert. I tried other ways of moving the cursor including next(), last() and so on, and also cannot get the correct value. I tried the drivers both postgresql-8.3-603.jdbc3.jar and postgresql-8.3-603.jdbc4.jar.
 
    But when I use pdadminIII to check the table XX, the AA field is already auto-generated with the correct value.
 
    I found a way to solve this: close resultset and statement after moveToCurrentRow() and re-open them, and rs.last(), then run int index=rs.getInt("AA"), I can get the correct value. I think this method is sort of awkward, anyone knows a better way and the correct operations?
 
I am not sure it is proper to send this mail to this mail list. Sorry if bring you any inconvenience.
Thanks a lot!
 
Best Regards
 
Kevin Chen/ChenDongdong
+8613810644051
 
 


See how Windows connects the people, information, and fun that are part of your life. See Now
Attachment

Re: [JDBC] need help of getting PK after insertRow in JDBC

From
"Gauss"
Date:

Martin,

 

Have you tried using the “RETURNING” function in your SQL insert statement to immediately return the auto-generated key value?  For example, how about something like this:

 

String insert = “ INSERT INTO \”schema_name\”.\”XX\” (\”BB\”, \”CC\”, \”DD\”, \”EE\”) VALUES (?, ?, ?, ?) RETURNING \”AA\” ”

PreparedStatment ps = con.prepareStatement(insert);

ps.setString(1, “b”);

ps.setString(2, “c”);

ps.setString(3, “d”);

ps.setString(4, “e”);

ResultSet rs = ps.executeQuery();

if (rs.next()) {

  int index = rs.getInt(“AA”);

}

 

Hope this helps,

 

Greg


From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Martin Gainty
Sent: Monday, September 29, 2008 2:16 PM
To: Chen, Dongdong (GE Healthcare, consultant); pgsql-general@postgresql.org; pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] [GENERAL] need help of getting PK after insertRow in JDBC

 

Good Afternoon

add the CLOSE_CURSORS_AT_COMMIT Option to the Statements capabilities e.g.
    Statement st = db.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE.ResultSet.CLOSE_CURSORS_AT_COMMIT);

//then tell the connection handle to commit the DML to the DB
db.commit();

Martin

Control your own destiny or someone else will -- Jack Welch
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.


Subject: [GENERAL] need help of getting PK after insertRow in JDBC
Date: Fri, 26 Sep 2008 10:14:01 +0800
From: Dongdong.Chen@ge.com
To: pgsql-general@postgresql.org; pgsql-jdbc@postgresql.org

Hi:

    I am a software engineer from GE. I am using JDBC to operate PostgreSQL8.3 in Ubuntu8.04. The develop environment is Eclipse3.2 My problem is:

    There is a PostgreSQL table XX containing 5 fields: AA, BB, CC, DD, EE, AA is primary key and auto-generated type, BB, CC, DD and EE is string type.

    I want to get the value of AA immediately after insert a row into the table. the code is like this:

 

    Statement st = db.creatStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

    ResultSet rs=st.executeQuery("SELECT * FROM XX");

    rs.moveToInsertRow();

    rs.updateString(BB, "b");

    rs.updateString(CC, "c");

    rs.updateString(DD, "d");

    rs.updateString(EE, "e");

    rs.insertRow();

    rs.moveToCurrentRow();

    int index = rs.getInt("AA");

    System.out.println(index);

 

   in the last sentence I always get 0 no matter how many records I insert. I tried other ways of moving the cursor including next(), last() and so on, and also cannot get the correct value. I tried the drivers both postgresql-8.3-603.jdbc3.jar and postgresql-8.3-603.jdbc4.jar.

 

    But when I use pdadminIII to check the table XX, the AA field is already auto-generated with the correct value.

 

    I found a way to solve this: close resultset and statement after moveToCurrentRow() and re-open them, and rs.last(), then run int index=rs.getInt("AA"), I can get the correct value. I think this method is sort of awkward, anyone knows a better way and the correct operations?

 

I am not sure it is proper to send this mail to this mail list. Sorry if bring you any inconvenience.

Thanks a lot!

 

Best Regards

 

Kevin Chen/ChenDongdong

+8613810644051

 

 

 


See how Windows connects the people, information, and fun that are part of your life. See Now

Attachment