JOOQ忽略具有默认值的数据库列 [英] JOOQ ignoring database columns with default values

问题描述

JOOQ似乎完全忽略了数据库列的默认值。既不会更新ActiveRecord对象，也不会跳过INSERT上的此列。相反，它尝试将其设置为NULL，这在NOT NULL列上失败。

示例：

  CREATE TABLE bug（
 foo int，
 bar int not null null默认值42 
）; 
 
 BugRecord b = jooq.newRecord（BUG）; 
.setFoo（3）; 
 b.store（）; 
 
 assertNotNull（b.getBar（））; //失败
 
记录r = jooq.select（）。from（BUG）.fetchOne（）; 
 assertEquals（new Integer（-1），r.getValue（BUG.BAR））; //失败
 
 // DataMapper模式
 Bug b = new Bug（）; 
.setFoo（3）; 
 bugDao.insert（b）; //失败，因为它尝试将 bar设置为NULL 
  

我期望的行为是newRecord（）使用korrekt值初始化所有默认变量（尽管我知道如果结果是自定义函数的结果可能很困难:-)。或者INSERT INTO不会使用默认值插入所有未修改的列然后在INSERT INTO之后执行SELECT，该SELECT从数据库中获取现有值（类似于RETURNING）。

这确实是错误/限制或我是否缺少某些配置选项等，使得
可以使用非空默认值列？

解决方案

您在这里发现了几件事（都与jOOQ 3.1和以前的版本有关）：

从插入中返回默认值：

  BugRecord b = jooq.newRecord（BUG）; 
.setFoo（3）; 
 b.store（）; 
 
 assertNotNull（b.getBar（））; //失败
  

确实这是一个不错的功能。当前，jOOQ仅获取IDENTITY列值。您可以使用 INSERT ..返回 语法或 UPDATE .. RETURNING 语法可明确选择在插入或更新后应返回哪些列。但是能够在常规的CRUD操作中这样做会更好。

此线程。与此相关的功能请求是＃1859 。

您可以通过以下方法解决此问题：

  b.refresh（）; //刷新所有列
 b.refresh（BUG.BAR，...）; //仅刷新某些列
  

插入NULL与通过 UpdatableRecord <插入默认值/ code>：

 记录r = jooq.select（）。from（BUG ）.fetchOne（）; 
 assertEquals（new Integer（-1），r.getValue（BUG.BAR））; //失败
  

我认为这是一个错误。 jOOQ的CRUD操作应具有 DEFAULT 值安全。仅在 store（） / insert（） / update（）操作应在生成的SQL中呈现。我已经为此注册了＃2698 。

插入NULL与通过 DAO 插入默认值：

  // DataMapper模式
 Bug b = new Bug（）; 
.setFoo（3）; 
 bugDao.insert（b）; //失败，因为它尝试将 bar设置为NULL 
  

很好的捕获。解决/增强功能并非易事，因为POJO并未在每列附带内部 changed / dirty标志。因此，不可能知道POJO中 null 引用的含义。

jOOQ已经知道列是否可为空。如果jOOQ还维护有关列上存在 DEFAULT 子句的元数据，则可以推断出组合 NOT NULL DEFAULT 必须导致：

 插入错误（foo，bar）
 VALUES（3，DEFAULT）
  

和

< pre $ = lang-sql prettyprint-override> UPDATE bug SET bar = DEFAULT WHERE foo = 3


我已经注册

• ＃2699 ：向生成的代码中添加一些元数据信息


• ＃2700 ：利用DAO中SQL中的上述元数据

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?

解决方案

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

这篇关于JOOQ忽略具有默认值的数据库列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！