Обсуждение: need help of getting PK after insertRow in JDBC
need help of getting PK after insertRow in JDBC
Вложения
On Fri, 26 Sep 2008, Chen, Dongdong (GE Healthcare, consultant) wrote: > 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); The JDBC driver does not implement true SCROLL_SENSITIVE cursors. The driver copies the values you provide into its ResultSet and sends the query to the backend. On the server the auto-generated values are filled in, but the JDBC driver doesn't get any notification of that. Perhaps with newer server versions we can make it issue a INSERT ... RETURNING ... so that we get that data. > I am not sure it is proper to send this mail to this mail list. Sorry if > bring you any inconvenience. Generally cross posting should be avoided and JDBC/Java questions should be directed to the -jdbc list, but this is a very good question and one that does prompt further investigation about what can be done in the JDBC driver to make this better. Kris Jurka
Good Afternoonadd 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
Вложения
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