Oracle将空字符串视为Java / JPA程序员的NULL问题 [英] Oracle treating empty string as NULL problem for a Java / JPA programmer

问题描述

你如何处理这种情况，Oracle将空字符串作为空值存储在数据库中？

我想将它存储为空字符串，因为它不是NULL，因为发出查询会更容易。

类似这样的将选择空字符串和非空字符串，但不是空值

  select * from mytable where myfield like'％'; 
  

如果我想选择空值（应该是最初的空字符串），我会必须像这样选择：

  select * from mytable where myfield like'％'or myfield is null; 
  

我很想跳过或myfield为空在我的sql语句中后面的所有时间

目前我想到的解决方案是在应用程序级别处理此问题，例如
，在实体中，我将所有的String字段默认值初始化为一个空格，例如：

  @Entity 
 public class MyEntity {
 private String name =; 
 $ b $ public void setName（String name）{
 if（isEmptyString（name））{
 name =; 
} 
} 
 ... 
 
} 
  

或者，我可以使用Oracle 11g中仍然未知的新类型，它可以保留空字符串，而不会将其更改为空值？

谢谢！

解决方案

是的，这就是Oracle的功能。空字符串被视为空值。

当然，您可以在应用程序级别修复它 - 例如通过存储的值，正如你所建议的那样 - 但首先要考虑的是，与code> NULL 值相比，空字符串值有什么区别？你为什么需要以不同的方式对待他们？我也经常遇到这种困境，但通常会发现，我真的需要分辨差异的情况很少。

How do you handle this situation where Oracle stores the empty string as a null in the database ?

I would like it to be stored as an empty string as it is not as NULL, since issuing the query would be easier.

Something like this would select the empty string and non-empty string, but not the null values

select * from mytable where myfield like '%';

if i would like to select also the null values (which should be originally empty string), i would have to select like this :

select * from mytable where myfield like '%' or myfield is null;

i would love to skip doing or myfield is null all the time later in my sql statements

The current solution i have in mind is to take care of this in the application level, for example, in the entity, i initialize all my String field default value to a space, for example :

@Entity
public class MyEntity {
  private String name = " ";

  public void setName(String name) {
    if (isEmptyString(name)) {
      name = " ";
    }
  }
  ...

}

Or perhaps, i can make use of a new type still unknown to me from Oracle 11g that can keep empty string as it is without changing it to null value ?

Thank you !

解决方案

Yup, that's the way Oracle functions. Empty strings are treated as nulls.

You can of course "fix" this on application level - for example by storing " " values as you suggested - but first consider, what exactly is the difference with your "empty string" values compared to NULL values? Why do you need to treat them differently? I used to run into this dilemma, too, but usually found out that there are very few cases where I really need to tell the difference.

这篇关于Oracle将空字符串视为Java / JPA程序员的NULL问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！