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