From: | Daryl Beattie <dbeattie(at)insystems(dot)com> |
---|---|
To: | "'pgsql-jdbc(at)postgresql(dot)org'" <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Sequence ID from INSERT |
Date: | 2002-07-23 15:05:51 |
Message-ID: | 4160E6FC08ABD21191F000805F857E9304DF455E@mail.insystems.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
Dear Postgresql-JDBCers,
Let's say I have created a table like this:
CREATE TABLE Food (
Id SERIAL PRIMARY KEY,
Name VARCHAR(10)
);
What is the best way to use JDBC to insert a row into the database
and get the Sequence Id right out of it? I don't want to start a transaction
and have to do another query that looks like this:
BEGIN;
INSERT INTO Food (Name) VALUES ('Apple');
SELECT currval('Food_Id_seq');
COMMIT;
Or ones like these:
BEGIN;
new_id = output of "SELECT nextval('Food_Id_seq')";
INSERT INTO Food (Id, Name) VALUES (new_id, 'Apple');
COMMIT;
BEGIN;
INSERT INTO Food (Name) VALUES ('Apple');
SELECT MAX(Id) FROM Food; -- will check ResultSet of this separate query.
COMMIT;
I want to do it in one statement. Normally I would
setAutoCommit(false), execute two separate statements, and then commit().
However, I was wondering if there's a way to do all this in a ... "cleaner"
fashion? Can anybody provide me with a sample?
Thanks,
Daryl.
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Cramer | 2002-07-23 15:16:45 | Re: Sequence ID from INSERT |
Previous Message | Andrew Sullivan | 2002-07-23 14:45:41 | Re: Postgres JDBC |