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.
似乎JOOQ完全忽略了数据库列的默认值。既没有更新ActiveRecord对象也没有在INSERT上跳过此列。相反，它尝试将其设置为NULL，这在NOT NULL列上失败。
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).
我期望的行为是newRecord（）用korrekt值初始化所有默认变量（虽然我知道如果结果是自定义函数的结果可能很难:-)）。或INSERT INTO执行不插入具有默认值的所有未修改列，然后INSERT INTO后跟一个SELECT，该SELECT从数据库中提取现有值（类似于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?
这真的是一个错误/限制，还是我错过了一些配置选项等，这使得可以使用“not null default”列？
1 个解决方案
#1
9
You've spotted a couple of things here (all relevant to jOOQ 3.1 and previous versions):
你在这里发现了一些东西（都与jOOQ 3.1和以前的版本有关）：
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.
事实上，这将是一个很好的功能。目前，jOOQ仅提取IDENTITY列值。您可以使用INSERT .. RETURNING语法或UPDATE .. RETURNING语法显式选择在插入或更新后应返回哪些列。但是能够在常规CRUD操作中这样做会好得多。
This had also been mentioned in this thread. The relevant feature request for this is #1859.
在这个帖子中也提到了这一点。相关的功能请求是＃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.
在我看来，这是一个错误。 jOOQ的CRUD操作应该是DEFAULT值安全的。只有那些在store（）/ insert（）/ update（）操作之前显式设置的值才应该在生成的SQL中呈现。我为此注册了＃2698。
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.
很好听。这对于解决/增强来说并不重要，因为POJO每列没有内部“更改”/“脏”标记。因此，不可能知道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:
另一方面，jOOQ已经知道列是否可以为空。如果jOOQ还维护有关列上存在DEFAULT子句的元数据，则可以推断组合NOT NULL DEFAULT必须导致：
INSERT INTO bug(foo, bar)
VALUES(3, DEFAULT)
And to
并
UPDATE bug SET bar = DEFAULT WHERE foo = 3
I have registered
我已经注册了
#1
9
You've spotted a couple of things here (all relevant to jOOQ 3.1 and previous versions):
你在这里发现了一些东西（都与jOOQ 3.1和以前的版本有关）：
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.
事实上，这将是一个很好的功能。目前，jOOQ仅提取IDENTITY列值。您可以使用INSERT .. RETURNING语法或UPDATE .. RETURNING语法显式选择在插入或更新后应返回哪些列。但是能够在常规CRUD操作中这样做会好得多。
This had also been mentioned in this thread. The relevant feature request for this is #1859.
在这个帖子中也提到了这一点。相关的功能请求是＃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.
在我看来，这是一个错误。 jOOQ的CRUD操作应该是DEFAULT值安全的。只有那些在store（）/ insert（）/ update（）操作之前显式设置的值才应该在生成的SQL中呈现。我为此注册了＃2698。
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.
很好听。这对于解决/增强来说并不重要，因为POJO每列没有内部“更改”/“脏”标记。因此，不可能知道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:
另一方面，jOOQ已经知道列是否可以为空。如果jOOQ还维护有关列上存在DEFAULT子句的元数据，则可以推断组合NOT NULL DEFAULT必须导致：
INSERT INTO bug(foo, bar)
VALUES(3, DEFAULT)
And to
并
UPDATE bug SET bar = DEFAULT WHERE foo = 3
I have registered
我已经注册了