Derby处理NULL值 [英] Derby's handling of NULL values

问题描述

我对Derby很陌生，我注意到我遇到类似的问题，就像使用DB2 RDBMS一样，只要$ code> null 值。 Derby文档指出，一个 null 值必须有一个与之相关联的类型（DB2最终在9.7版中被删除）：

http://db.apache.org/ derby / docs / 10.7 / ref / crefsqlj21305.html

现在，我试图找到一个一般解决方案在这里，这将是我的数据库抽象库 jOOQ 的一部分。下面的例子只是记录了问题。想想任何其他（更复杂）的例子。以下内容不起作用：

 插入到T_AUTHOR（
 ID，FIRST_NAME，LAST_NAME，
 DATE_OF_BIRTH ，YEAR_OF_BIRTH，ADDRESS）
选择
 1000，'Lukas'，'Eder'，
'1981-07-10'，null，null 
来自SYSIBM.SYSDUMMY1 
  

这也不是（实际上是由jOOQ做的）：

  ID，FIRST_NAME，LAST_NAME，
 DATE_OF_BIRTH，YEAR_OF_BIRTH，ADDRESS）
选择？，？，？，？ ，？，？ 
来自SYSIBM.SYSDUMMY1 
  

因为两个 null 值没有与之相关联的类型。该解决方案将是这样写：

 插入到T_AUTHOR（
 ID，FIRST_NAME，LAST_NAME，
 DATE_OF_BIRTH，YEAR_OF_BIRTH，ADDRESS）
选择
 1000，'Lukas'，'Eder'，
'1981-07-10'，cast（null as int），cast（null as varchar（500））
来自SYSIBM.SYSDUMMY1 
  

或者像这样分别

 插入T_AUTHOR（
 ID，FIRST_NAME，LAST_NAME，
 DATE_OF_BIRTH，YEAR_OF_BIRTH，ADDRESS）
选择
？，？，？，？，cast（？as int），cast（？as varchar（500））
来自SYSIBM.SYSDUMMY1 
  

但是经常在Java中，应该将 null 的类型转换为未知：

• 在这个例子中，这些类型可以从insert子句派生出来，但对于更一般的用例来说，这可能是复杂的或不可能的。


• 在其他示例中，我可以选择任何类型的转换（例如总是转换为 int ），但是不会在这个例子中工作，因为您不能将 cast（null as int） value放入 ADDRESS 。


• 使用HSQLDB（这个问题的另一个候选者），我可以在大多数情况下简单地写入 cast（null作为对象）。但是Derby没有对象类型。

这个问题一直很讨厌我之前使用DB2，我还没有找到解决方案。有谁知道任何这些RDBMS的这个问题的稳定和一般解决方案？

• Derby


• DB2

解决方案

如果您在INSERT上使用VALUES子句，则不必转换NULL值：

  insert into T_AUTHOR（ 
 ID，FIRST_NAME，LAST_NAME，
 DATE_OF_BIRTH，YEAR_OF_BIRTH，ADDRESS）
 VALUES（
 1000，'Lukas'，'Eder'，
'1981-07-10 '，null，null 
）; 
  

这将像你所期望的那样工作（即数据库可以确定NULL对应于整数和varchar （500），这可以在DB2和Derby中工作（也可以在几乎任何其他数据库引擎中工作）。

您可以使用带有参数标记的VALUES

发行插入到...中时必须投放的原因是从...选择语句是因为SELECT部分​​优先 - select语句返回某些数据类型，无论它们是否与要插入的表兼容，如果它们不兼容，您将要么得到一个错误（使用强类型的数据库引擎，如DB2< = 9.5），或引擎将进行隐式类型转换（如果可能）。

I am new to Derby and I noticed that I face similar problems as when using the DB2 RDBMS as far as null values are concerned. The Derby documentation states, that a null value must have a type associated with it (something that DB2 finally got rid of in version 9.7):

http://db.apache.org/derby/docs/10.7/ref/crefsqlj21305.html

Now, I am trying to find a general solution to this problem here as this will be a part of my database abstraction library jOOQ. The below example just documents the problem. Think of any other (more complex) example. The following doesn't work:

insert into T_AUTHOR (
  ID, FIRST_NAME, LAST_NAME, 
  DATE_OF_BIRTH, YEAR_OF_BIRTH, ADDRESS) 
select 
  1000, 'Lukas', 'Eder', 
  '1981-07-10', null, null 
from SYSIBM.SYSDUMMY1

Neither does this (which is what is actually done by jOOQ):

insert into T_AUTHOR (
  ID, FIRST_NAME, LAST_NAME, 
  DATE_OF_BIRTH, YEAR_OF_BIRTH, ADDRESS) 
select ?, ?, ?, ?, ?, ? 
from SYSIBM.SYSDUMMY1

Because the two null values have no type associated with it. The solution would be to write something like this:

insert into T_AUTHOR (
  ID, FIRST_NAME, LAST_NAME, 
  DATE_OF_BIRTH, YEAR_OF_BIRTH, ADDRESS) 
select 
  1000, 'Lukas', 'Eder', 
  '1981-07-10', cast(null as int), cast(null as varchar(500)) 
from SYSIBM.SYSDUMMY1

Or like this, respectively

insert into T_AUTHOR (
  ID, FIRST_NAME, LAST_NAME, 
  DATE_OF_BIRTH, YEAR_OF_BIRTH, ADDRESS) 
select 
  ?, ?, ?, ?, cast(? as int), cast(? as varchar(500)) 
from SYSIBM.SYSDUMMY1

But very often, in Java, the type that null should be cast to is unknown:

• In this example, the types could be derived from the insert clause, but that might prove to be complicated or impossible for more general use-cases.
• In other examples, I could just pick any type for the cast (e.g. always casting to int), but that wouldn't work in this example, as you cannot put a cast(null as int) value into ADDRESS.
• With HSQLDB (another candidate for this problem), I can simply write cast(null as object) which will work in most cases. But Derby does not have an object type.

This problem has been annoying me with DB2 before and I haven't found a solution yet. Does anyone know of a stable, and general solution to this problem for any of these RDBMS?

• Derby
• DB2

解决方案

If you use the VALUES clause on your INSERT, you don't have to cast the NULL values:

insert into T_AUTHOR (
  ID, FIRST_NAME, LAST_NAME, 
  DATE_OF_BIRTH, YEAR_OF_BIRTH, ADDRESS) 
VALUES ( 
  1000, 'Lukas', 'Eder', 
  '1981-07-10', null, null 
);

This will work like you expect (i.e. the database can determine that the NULLs correspond to an integer and varchar(500). This works in both DB2 and Derby (and should work in pretty much any other database engine, as well).

You can use VALUES with parameter markers as well, without having to CAST them.

The reason that you have to cast when issuing an insert into ... select from statement is because the SELECT portion takes precedence -- the select statement returns certain data types, regardless of whether they are compatible with the table you're trying to insert them in to. If they aren't compatible, you will either get an error (with strongly typed database engines like DB2 <= 9.5) or the engine will do implicit type conversion (when possible).

这篇关于Derby处理NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！