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();

Answer 1

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);