arundhaj

regression towards the datascience

getGeneratedKeys with PostgreSQL

 

In a complex SQL transaction where the tables are normalized and split, it becomes necessary to put primary key of first tables' insert as a foreign key of subsequent tables' inserts. Though java.sql.PreparedStatement have getGeneratedKeys, it works pretty different for PostgreSQL than MySQL.

The following code block shows the addition of Statement.RETURN_GENERATED_KEYS which is essential for PostgreSQL

String insertSql = "INSERT INTO table1 (c1, c2, c3) VALUES (?, ?, ?)";
PreparedStatement insertPs = conn.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);

insertPs.executeUpdate();

ResultSet rsKey = insertPs.getGeneratedKeys();

Long insertKey = null;

if(rsKey.next()) {
    insertKey = rsKey.getLong(1);
}

insertKey is the primary key created for the corresponding insert, which can be used for the subsequent inserts.

As far as SQL is concerned.

CREATE TABLE table1
(
  id serial NOT NULL,
  c1 character varying(500) NOT NULL,
  c2 character varying(500) NOT NULL,
  c3 character varying(500) NOT NULL,
  CONSTRAINT table1_pkey PRIMARY KEY (id)
);

Comments