Get RETURNING value from Postgresql via Java

Question

From Java, I'm calling a prepared statement in Postgresql with an insert that has a RETURNING clause for my identity column. In PG admin it comes right back, but not sure how to get it from my prepared statement:

        String insertStatement = "INSERT INTO person(\n" +
                "            name, address, phone, customer_type, \n" +
                "            start_dtm)\n" +
                "    VALUES (?, ?, ?, ?, \n" +
                "            ?)\n" +
                "    RETURNING person_id;";


        PreparedStatement stmt = connection.prepareStatement(insertStatement);

        stmt.setObject(1, perToSave.getName(null));
        stmt.setObject(2, editToSave.getAddress());
        stmt.setObject(3, editToSave.getPhone());
        stmt.setObject(4, editToSave.getCustType());
        long epochTime = java.lang.System.currentTimeMillis();
        stmt.setObject(5, new java.sql.Date(epochTime));

        stmt.executeUpdate();

Community · Accepted Answer · 2017-05-23 12:32:17Z

25

I do not have enough reputation to Comment and my Edit got rejected, so sorry for re-posting the already accepted answer from hd1.

executeUpdate expects no returns; use execute. Check if there is some results before trying to retrieve the value.

String insertStatement = "INSERT INTO person(\n" +
                "            name, address, phone, customer_type, \n" +
                "            start_dtm)\n" +
                "    VALUES (?, ?, ?, ?, \n" +
                "            ?)\n" +
                "    RETURNING person_id;";

PreparedStatement stmt = connection.prepareStatement(insertStatement);

stmt.setObject(1, perToSave.getName(null));
stmt.setObject(2, editToSave.getAddress());
stmt.setObject(3, editToSave.getPhone());
stmt.setObject(4, editToSave.getCustType());
long epochTime = java.lang.System.currentTimeMillis();
stmt.setObject(5, new java.sql.Date(epochTime));

stmt.execute();
ResultSet last_updated_person = stmt.getResultSet();
if(last_updated_person.next()) {
   int last_updated_person_id = last_updated_person.getInt(1);
}

edited May 23, 2017 at 12:32

CommunityBot

11 silver badge

answered Nov 24, 2016 at 13:14

Malba

3985 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jiri Tousek Over a year ago

Posting your own answer that completes another user's answer is OK as long as you provide credit (which you did) - see meta.stackoverflow.com/a/338292/5375403 It would be a good idea though if you stated clearly how your answer differs from the original one, and why it does so.

hd1 · Accepted Answer · 2021-06-16 00:43:47Z

6

According to the javadoc, PreparedStatement inherits from Statement and the latter contains a getResultSet() method. In other words, try this:

String insertStatement = "INSERT INTO person(\n" +
                "            name, address, phone, customer_type, \n" +
                "            start_dtm)\n" +
                "    VALUES (?, ?, ?, ?, \n" +
                "            ?)\n" +
                "    RETURNING person_id;";


PreparedStatement stmt = connection.prepareStatement(insertStatement);

stmt.setObject(1, perToSave.getName(null));
stmt.setObject(2, editToSave.getAddress());
stmt.setObject(3, editToSave.getPhone());
stmt.setObject(4, editToSave.getCustType());
long epochTime = java.lang.System.currentTimeMillis();
stmt.setObject(5, new java.sql.Date(epochTime));

stmt.execute();
ResultSet last_updated_person = stmt.getResultSet();
last_updated_person.next();
int last_updated_person_id = last_updated_person.getInt(1);

Leave a comment if you have further issues.

edited Jun 16, 2021 at 0:43

answered May 16, 2014 at 2:42

hd1

34.9k5 gold badges83 silver badges95 bronze badges

3 Comments

Omid S. Over a year ago

executeUpdate() throws a "org.postgresql.util.PSQLException: A result was returned when none was expected. " as it's not supposed to see a result returned. also getresultSet() without a next call throughs : org.postgresql.util.PSQLException: ResultSet not positioned properly, perhaps you need to call next.

Brian Over a year ago

This answer uses "executeUpdate", but the correct method to call is "execute", as shown in the highly-upvoted, unaccepted answer. Copy and paste from the next answer :-)

sergeych Over a year ago

this is not a correct answer. Correct upvoted but mistakingly unaccepted answer is below

hd1 · Accepted Answer · 2014-05-17 19:55:10Z

4

Calling executeUpdate() expects no result from statement. Call stmt.execute() and then stmt.getResultSet()

edited May 17, 2014 at 19:55

hd1

34.9k5 gold badges83 silver badges95 bronze badges

answered May 16, 2014 at 2:46

cardamo

8534 silver badges13 bronze badges

Comments

Jww · Accepted Answer · 2020-09-21 16:24:58Z

JDBC has built-in support for returning primary key values for insert statements. Remove the "returning" clause from your SQL and specify PreparedStatement.RETURN_GENERATED_KEYS as a second parameter to your prepareStatment(...) call. Then you can get the generated primary key by calling stmt.getGeneratedKeys() on your PreparedStatement object after calling executeUpdate().

String insertStatement = "INSERT INTO person(\n" +
    "            name, address, phone, customer_type, \n" +
    "            start_dtm)\n" +
    "    VALUES (?, ?, ?, ?, \n" +
    "            ?)";

PreparedStatement stmt = connection.prepareStatement(insertStatement,
    PreparedStatement.RETURN_GENERATED_KEYS);

stmt.setObject(1, perToSave.getName(null));
stmt.setObject(2, editToSave.getAddress());
stmt.setObject(3, editToSave.getPhone());
stmt.setObject(4, editToSave.getCustType());
long epochTime = java.lang.System.currentTimeMillis();
stmt.setObject(5, new java.sql.Date(epochTime));

stmt.executeUpdate();
    
ResultSet resultSet = stmt.getGeneratedKeys();
resultSet.next();
int lastInsertedPersonID = resultSet.getInt(1);

Collectives™ on Stack Overflow

Get RETURNING value from Postgresql via Java

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related