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

From: "Gauss" <gauss21(at)comcast(dot)net>
To: <pgsql-general(at)postgresql(dot)org>, <pgsql-jdbc(at)postgresql(dot)org>, "'Martin Gainty'" <mgainty(at)hotmail(dot)com>
Subject: Re: [JDBC] need help of getting PK after insertRow in JDBC
Date: 2008-09-30 00:21:43
Message-ID: 5FEE976AC86948E0B8F4FFA65387B58C@Serotonin
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-jdbc

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(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Martin Gainty
Sent: Monday, September 29, 2008 2:16 PM
To: Chen, Dongdong (GE Healthcare, consultant);
pgsql-general(at)postgresql(dot)org; pgsql-jdbc(at)postgresql(dot)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(dot)Chen(at)ge(dot)com
To: pgsql-general(at)postgresql(dot)org; pgsql-jdbc(at)postgresql(dot)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
<http://clk.atdmt.com/MRT/go/msnnkwxp1020093175mrt/direct/01/>

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Abdul Rahman 2008-09-30 04:55:24 Re: Replication using slony-I
Previous Message Mike Diehl 2008-09-30 00:02:10 Re: Can't cast from char to integer...

Browse pgsql-jdbc by date

  From Date Subject
Next Message Kris Jurka 2008-09-30 04:56:50 Re: UUID datatype
Previous Message Martin Gainty 2008-09-29 20:16:21 Re: need help of getting PK after insertRow in JDBC