Thread: need help of getting PK after insertRow in JDBC
Attachment
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
See how Windows connects the people, information, and fun that are part of your life. See Now
Attachment
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