Is there anyway to insert a new record into a PostgreSQL database with Jooq straight from a POJO which extends a general identity class that has an id field without including the id in the insert statement?
An example POJO:
@Data
public abstract class PersistenceIdentity {
@Id
@Column(name = "id", unique = true, nullable = false, precision = 7, insertable = false)
private Integer id;
@Column(name = "created_date")
private LocalDateTime createdDate;
public abstract Table<R> getJooqTable();
}
@Data
public class SocialNetwork extends PersistenceIdentity {
@Column(name = "name")
private String name;
@Override
public Table<SocialNetworkRecord> getJooqTable() {
return Tables.SOCIAL_NETWORK;
}
}
The PostgreSQL schema is:
CREATE TABLE "social_network" (
id SERIAL NOT NULL PRIMARY KEY,
created_date TIMESTAMP DEFAULT now(),
name TEXT NOT NULL
);
My code to persist the POJO:
public <T extends PersistenceIdentity> T insertRecord(T record) {
Record newRecord = db.newRecord(record.getJooqTable(), record);
if (newRecord instanceof UpdatableRecord) {
((UpdatableRecord) newRecord).store();
}
return newRecord.into(record);
}
I realize I'm probably doing what Jooq really wasn't meant for (i.e. using generic types), however that (appears) to work just fine.
The problem is, Jooq includes the id in the insert statement and I then, of course, get a null value constraint. I don't want it inserted when it's a new record, however I do want it included when it returns the record (after inserting), when updating and also in select statements.
I can't simply exclude the id because I need it later on to easily get around some of the @OneToMany / @ManyToOne limitations.
And I would rather not have to insert the specific values for each POJO (that's why we annotated with @Column).
Does Jooq not honor the @Id or the insertable = false parameter in @Column?
Can anyone shed some light on this?
EDIT 1
Per request, below is the relevant snippet from the jOOQ generated table object. I'm not sure if this is correct or not for what I'm trying to do (i.e. allow the database to generate the ID), but I would think nextval('social_network_id_seq'::regclass) would accomplish that.
@Generated(
value = {
"http://www.jooq.org",
"jOOQ version:3.9.1"
},
comments = "This class is generated by jOOQ"
)
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class SocialNetwork extends TableImpl<SocialNetworkRecord> {
/**
* The column <code>public.social_network.id</code>.
*/
public final TableField<SocialNetworkRecord, Integer> ID = createField("id", org.jooq.impl.SQLDataType.INTEGER.defaultValue(org.jooq.impl.DSL.field("nextval('social_network_id_seq'::regclass)", org.jooq.impl.SQLDataType.INTEGER)), this, "");
}
Also, we use the mvn jooq-codegen:generate -Djooq.generator.name=org.jooq.util.XMLGenerator to generate the XML schema and then generate the the jOOQ table objects from that XML config. The thinking is we can push the XML config to github and all builds can simply regenerate the table objects from that.
Here is the XML:
<column>
<table_catalog></table_catalog>
<table_schema>public</table_schema>
<table_name>social_network</table_name>
<column_name>id</column_name>
<data_type>integer</data_type>
<character_maximum_length>0</character_maximum_length>
<numeric_precision>32</numeric_precision>
<numeric_scale>0</numeric_scale>
<ordinal_position>1</ordinal_position>
<column_default>nextval('social_network_id_seq'::regclass)</column_default>
</column>
<table_constraint>
<constraint_catalog></constraint_catalog>
<constraint_schema>public</constraint_schema>
<constraint_name>social_network_pkey</constraint_name>
<constraint_type>PRIMARY KEY</constraint_type>
<table_catalog></table_catalog>
<table_schema>public</table_schema>
<table_name>social_network</table_name>
</table_constraint>
<table_constraint>
<constraint_catalog></constraint_catalog>
<constraint_schema>public</constraint_schema>
<constraint_name>2200_17431_1_not_null</constraint_name>
<constraint_type>CHECK</constraint_type>
<table_catalog></table_catalog>
<table_schema>public</table_schema>
<table_name>social_network</table_name>
</table_constraint>
<table_constraint>
<constraint_catalog></constraint_catalog>
<constraint_schema>public</constraint_schema>
<constraint_name>2200_17431_3_not_null</constraint_name>
<constraint_type>CHECK</constraint_type>
<table_catalog></table_catalog>
<table_schema>public</table_schema>
<table_name>social_network</table_name>
</table_constraint>
<key_column_usage>
<column_name>id</column_name>
<constraint_catalog></constraint_catalog>
<constraint_schema>public</constraint_schema>
<constraint_name>social_network_pkey</constraint_name>
<ordinal_position>0</ordinal_position>
<table_catalog></table_catalog>
<table_schema>public</table_schema>
<table_name>social_network</table_name>
</key_column_usage>
EDIT 2
My SocialNetwork jOOQ-generated table object does not have a getIdentity() method, however it does have a getPrimaryKey() method and if it helps, my SocialNetworkRecord class has two Constructors:
public SocialNetworkRecord() {
super(SocialNetwork.SOCIAL_NETWORK);
}
/**
* Create a detached, initialised SocialNetworkRecord
*/
public SocialNetworkRecord(Integer id, Timestamp createdDate, String name) {
super(SocialNetwork.SOCIAL_NETWORK);
set(0, id);
set(1, createdDate);
set(2, name);
}
