Sequence ID from INSERT

Поиск
Список
Период
Сортировка
От Daryl Beattie
Тема Sequence ID from INSERT
Дата
Msg-id 4160E6FC08ABD21191F000805F857E9304DF455E@mail.insystems.ca
обсуждение исходный текст
Ответы Re: Sequence ID from INSERT  (Dave Cramer <Dave@micro-automation.net>)
Список 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.


В списке pgsql-jdbc по дате отправления:

Предыдущее
От: Andrew Sullivan
Дата:
Сообщение: Re: Postgres JDBC
Следующее
От: Dave Cramer
Дата:
Сообщение: Re: Sequence ID from INSERT