impossible to retrieve getGeneratedKey from a ResultSet, in an insert previously made in postgresql

Question

I can not recover the generated id after inserting values in the "test" table

Here the create script generated by pgadmi4 of the table "test":

CREATE TABLE school.test
(
    test_id bigint NOT NULL DEFAULT nextval('school.test_test_id_seq'::regclass),
    name character varying(10) COLLATE pg_catalog."default"
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE school.test
    OWNER to postgres;

Here the code to insert the values to the "test" table thourgh JDBC:

        Connection conn = pstgConn.dbConnection();
        String query = "INSERT INTO school.test(name) VALUES (?)";
        try (PreparedStatement pst = conn.prepareStatement(query)) {
            pst.setString(1, "anything");
            pst.executeUpdate();
            // Get Generated Keys
            getGeneratdKeys(pst);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

Here the getGeneratedKey function:

    public static void getGeneratdKeys(PreparedStatement statement) {
        try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
            generatedKeys.next();
                System.out.println(generatedKeys.getLong(1));
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

I changed the postgresql jar by a recent version.

Here the error:

org.postgresql.util.PSQLException: The ResultSet is not set correctly, you may need to call next ().
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkResultSet(AbstractJdbc2ResultSet.java:2675)
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getLong(AbstractJdbc2ResultSet.java:1988)
    at com.cgi.training.javase.school.dao.testDAO.getGeneratdKeys(testDAO.java:61)
    at com.cgi.training.javase.school.dao.testDAO.main(testDAO.java:23)

score 1 · Answer 1 · answered Jul 07 '19 at 18:41

You need to tell the driver to return the generated key, when you prepare the statement:

PreparedStatement pst = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS)

or by passing the names of the columns that are generated keys:

PreparedStatement pst = conn.prepareStatement(query, new String[]{"test_id"});

impossible to retrieve getGeneratedKey from a ResultSet, in an insert previously made in postgresql

1 Answers1