0

I have a server with connection to a database in postgreSQL and I want to return the ID of a new row to the client once it is inserted. I've tried this:

int id = stmt.execute("insert into [table] values(default,0) returning id;");

the SQL statement works if I'm working directly on the database but not here, I get this error: org.postgresql.util.PSQLException: A result was returned when none was expected.

I'm thinking its because execute() and executeUpdate() aren't supposed to return values so I added this:

stmt.execute("insert into [table] values(default,0) returning id;");
ResultSet id = stmt.executeQuery("select last_insert_id();");

Apparently that statement is for mySQL so it doesn't work, I've read that CURRVAL and NEXTVAL do this in postgreSQL but I haven't been able to do it and I've also read it's not always acurate.

Is there something I'm missing or another way to do it?

Aldie Yung
  • 65
  • 8
  • On PostgreSQL `insert into table` with `returning` statement - this is equivalent to any select statement. Try it: `stmt.executeQuery("insert into [table] values(default,0) returning id;")` – Ramin Faracov Nov 22 '22 at 16:15
  • now I'm getting this: ```java.io.NotSerializableException: org.postgresql.jdbc.PgResultSet ``` any idea what's wrong? I'm using ResultSet to send data to the client in other places in the code and it's wokring fine – Aldie Yung Nov 22 '22 at 16:42
  • @AldieYung You can't serialize a result set. If you need to serialize things, you need to get the values from the result set and pack them in a way that can be serialized. That said, you really shouldn't use Java serialization unless you really, really have to. – Mark Rotteveel Nov 23 '22 at 07:44

1 Answers1

1

You can use getGeneratedKeys()

String sql = "insert into the_table(some_column) values (?)";
PreparedStatement pstmt = connection.prepareStatement(sql, new String[]{"id"});    
pstmt.setInt(1, 0);
int numRows = pstmt.executeUpdate();
ResultSet keys = pstmt.getGeneratedKeys();
int newId = -1;
if (keys.next()) {
  newId = keys.getInt(1);
}

Alternatively you can also use:

PreparedStatement pstmt = connection.prepareStatement("...", PreparedStatement.RETURN_GENERATED_KEYS);

This also works with batchedStatements (after pstmt.executeBatch()) or multi-row inserts. In that case the if (keys.next()) must be changed to a while() loop to iterate over all generated IDs