JOOQ ignoring database columns with default values

Question

It seems that JOOQ is completely ignoring the default values of database columns. Neither gets the ActiveRecord object updated nor does it skip this column on INSERT. Instead it tries to set it to NULL which fails on NOT NULL columns.

Example:

CREATE TABLE bug (
  foo int,
  bar int not null default 42
);

  BugRecord b = jooq.newRecord(BUG);
  b.setFoo(3);
  b.store();          

  assertNotNull(b.getBar()); // fails

  Record r = jooq.select().from(BUG).fetchOne();
  assertEquals(new Integer(-1), r.getValue(BUG.BAR)); // fails

  // DataMapper pattern
  Bug b = new Bug();
  b.setFoo(3);
  bugDao.insert(b); // Fails because it tries to set "bar" to NULL

The behaviour I would expect is that either the newRecord() initializes all default variables with the korrekt values (although I understand that this could be difficult if the result is the outcome of a custom function :-)).or that the INSERT INTO does not insert all unmodified columns with default values and then that the INSERT INTO is followed by a SELECT that fetches the now existing values from the database (similar to a RETURNING).

Is this really a bug/limitation or am I missing some config option etc which makes it possible to use "not null default" columns?

Answer 1

You've spotted a couple of things here (all relevant to jOOQ 3.1 and previous versions):

Returning default values from inserts:

BugRecord b = jooq.newRecord(BUG);
b.setFoo(3);
b.store();          

assertNotNull(b.getBar()); // fails

That would be a nice-to-have feature, indeed. Currently, jOOQ only fetches IDENTITY column values. You can use the INSERT .. RETURNING syntax or the UPDATE .. RETURNING syntax to explicitly chose which columns ought to be returned after an insert or update. But being able to do so in regular CRUD operations would be much better.

This had also been mentioned in this thread. The relevant feature request for this is #1859.

You can work around this issue by calling

b.refresh();             // Refresh all columns
b.refresh(BUG.BAR, ...); // Refresh only some columns

Inserting NULL vs. inserting DEFAULTs through UpdatableRecord:

Record r = jooq.select().from(BUG).fetchOne();
assertEquals(new Integer(-1), r.getValue(BUG.BAR)); // fails

This is a bug, in my opinion. jOOQ's CRUD operations should be DEFAULT value safe. Only those values that have been set explicitly prior to a store() / insert() / update() operation ought to be rendered in the generated SQL. I have registered #2698 for this.

Inserting NULL vs. inserting DEFAULTs through DAO:

// DataMapper pattern
Bug b = new Bug();
b.setFoo(3);
bugDao.insert(b); // Fails because it tries to set "bar" to NULL

Nice catch. This is non-trivial to solve / enhance, as a POJO does not ship with an internal "changed" / "dirty" flag per column. It is thus not possible to know the meaning of a null reference in a POJO.

On the other hand, jOOQ already knows whether a column is nullable. If jOOQ also maintained metadata about the presence of a DEFAULT clause on a column, it could deduce that the combination NOT NULL DEFAULT would have to lead to:

INSERT INTO bug(foo, bar)
VALUES(3, DEFAULT)

And to

UPDATE bug SET bar = DEFAULT WHERE foo = 3

I have registered

• #2699: Adding some metadata information to generated code
• #2700: Leveraging the above metadata in SQL from DAOs