JPA查询返回空值-具有空列的复合键 [英] JPA Query returning nulls - Composite Key with null column
问题描述
我有一个遗留数据库(实际上是Cobol文件)，正在使用Hibernate/JPA专有的JDBC驱动程序进行访问.
实体具有一个复合主键，该主键包含2列:CODE和SITE.
在旧数据中，有相同CODE的记录，该记录可以具有SITE的特定值，或者在SITE列中可以有一个带有NULL的记录，表示所有站点".此文件的理论是，如果找不到特定的SITE的CODE，则可以在SITE(全部捕获")中查找带有NULL的记录.
我无法更改此表"的结构，因为它将涉及重写我们不想做的遗留Cobol系统的大部分.我也无法创建数据视图.
现在，当我使用包含特定code和site的null的主复合键类执行em.find时，Hibernate会在列中正确找到匹配记录并带有NULL值-很好！ /p>
但是，如果我尝试使用em.createQuery进行以下查询:
SELECT x FROM TareWeight x WHERE x.pk.code = 'LC2'
对于有2条记录的记录，它在记录的结果列表中返回一个空对象，而在SITE列中为NULL.
如果我使用Hibernate用于此查询的SQL，则数据库"将返回两条记录，一条记录带有NULL站点，一条记录带有特定站点.看来，当Hibernate从这些结果加载Entities时，它正在将其映射到一个空的Entity对象.
所以Hibernate支持它，或者不支持.为什么em.find不能工作，而em.createQuery不能工作?
我知道这个问题类似，但是答案似乎暗示这是不可能的.但是很明显，Hibernate可以正确执行查找，那么为什么查询不起作用?
好，所以我在null的默认表示)的实体.
仍然感觉这些应该表现相同.
更新2:有些人想知道有关数据库"的细节.
这是Genesis RDBMS引擎，由 Trifox Inc. 编写.数据存储在AcuCobol(现在为Micro Focus)Vision索引文件中.
我们已将配置设置为将空白(SPACES)字母数字字段转换为NULL，因此将包含PK字段空格的文件记录转换为NULL.我可以使用WHERE site_id IS NULL具体选择适当的记录，因此RDBMS 会将这些空白字段视为SQL NULL.
尽管我不相信此问题与数据库"有任何关系，但事实是，PK字段为空是不寻常的.
此外，如果我记录Hibernate用于find和查询的SQL，它们几乎是相同的.
以下是查找的SQL:
select tareweight0_.CODE as CODE274_0_, tareweight0_.SITE as SITE274_0_,
tareweight0_.WEIGHT as WEIGHT274_0_ from TARE_WEIGHT tareweight0_
where tareweight0_.CODE=? and tareweight0_.SITE=?
这是查询的SQL:
select tareweight0_.CODE as CODE274_, tareweight0_.SITE as SITE274_,
tareweight0_.WEIGHT as WEIGHT274_ from TARE_WEIGHT tareweight0_
where tareweight0_.CODE=? and tareweight0_.SITE=?
如您所见，唯一的区别是列别名.
更新3:以下是一些示例数据:
select code, site, weight from tare_weight where code like 'LC%';
CODE SITE WEIGHT
------ ------ -------
LC1 .81
LC2 .83
LC2 BEENLH .81
LC3 1.07
LC3 BEENLH 1.05
LC4 1.05
LCH1 .91
LCH2 .93
LCH2 BEENLH .91
LCH6 1.13
LCH6 BEENLH 1.11
并专门搜索NULL:
select code, site, weight from tare_weight where code like 'LC%' and site IS NULL;
CODE SITE WEIGHT
------ ------ -------
LC1 .81
LC2 .83
LC3 1.07
LC4 1.05
LCH1 .91
LCH2 .93
LCH6 1.13
所以他们要么支持它，要么不支持".
TL; DR 这种期望/感觉是不合理的.您的链接(下面的&我的)中不受支持的功能正是您所拥有的. 不支持"意味着如果您这样做，那么Hibernate可以做他们想要的任何事情.您很幸运，他们(似乎)返回了合理的价值. (尽管这只是猜测的行为方式.您没有规范.)没有理由期待任何事情，更不用说一致性了.当行为只是某些不受支持的情况的结果而已时，任何为什么"很可能只是考虑到其他情况而编写代码的一种人工产物.
这是一个(很旧的)支持线程，由休眠团队回答:
发布主题:一列具有空值的组合键
PostPosted:2006年7月3日，星期一2:21
我有一个带有复合主键的表，并创建了以下内容 表的映射.由于可以为任何表插入null值 只要所有列的组合为 唯一的，我在表中有记录，其中V_CHAR2的值为空 列(是复合键的一部分).当我执行查询时 这个实体我得到具有空值的记录的空值 V_CHAR2列的值.我的映射有什么问题， 实施..
发布时间:2006年7月11日，星期二，上午9:09
休眠团队
主键不能为null(不能全部或部分为空)
发布时间:2007年1月6日星期六5:35 am
休眠团队
很抱歉使您失望，但不支持主键为null- 主要是因为进行联接和比较将需要很多 非常愚蠢的代码，在其他任何地方都不需要... 关于它，您将看到(例如，您如何使用正确加入 这样的桌子)
这并不奇怪，因为SQL的PK列中不允许NULL. PRIMARY KEY声明是UNIQUE NOT NULL的同义词. NULL不等于任何东西(出于误导的意图)，即某些未记录的值不知道是相等的. (您对PK至少等于NULL的某些情况下对NULL的某些情况的期望与SQL相反.)鉴于PK值中不允许NULL，我们可以期望PK优化与1:1映射相关，并且与集合(而不是行袋)相关，以在方便时假定不存在NULL.可以期望Hibernate决定不担心它们的实现会对SQL中不应该出现的情况做了什么.太糟糕了，他们在编译或执行时没有告诉您.希望它在文档中.)
即使find与createQuery re NULL不同，也不足为奇.前者涉及一个值，而后者涉及不带NULL(但不是)的行集合(不是袋).
一种解决方法可能是不将主键的任何列都视为NULL，而是将其视为存储中实际的空格字符串. (无论这意味着给您的存储/DBMS/hibernate/JPA/Java堆栈.您没有给我们足够的信息来知道是否通过不将JPA的空间映射为NULL来阻止数据库的Cobol视图).对于数据，您仍然可以在列上声明UNIQUE索引.
I have a legacy database (Cobol files actually) that I am accessing using a proprietary JDBC driver with Hibernate/JPA.
The Entity has a composite primary key with 2 columns: CODE and SITE.
In the legacy data there are records for the same CODE that can have either a specific value for SITE, or there can be a record with NULL in the SITE column which represents 'All Sites'. The theory of this file is, if you cannot find the CODE for your specific SITE then you lookup the record with NULL in the SITE (the 'catch-all').
I cannot change the structure of this 'table' as it would involve rewriting large parts of the legacy Cobol system which we don't want to do. I also cannot create views of the data.
Now when I do an em.find with the primary composite key class containing a specific code and a null for site, then Hibernate correctly finds the matching record with the NULL value in the column - All good!
But if I try to do a query using em.createQuery similar to the following:
SELECT x FROM TareWeight x WHERE x.pk.code = 'LC2'
for which there are 2 records, it returns a null object in the resulting list for the record with the NULL in the SITE column.
If I take the SQL that Hibernate uses for this query, then the 'database' returns two records, one with the NULL site and one with a specific site. It seems that when Hibernate loads the Entities from these results, it is mapping it to a null Entity object.
So either Hibernate supports it or it doesn't. Why would the em.find work but not the em.createQuery?
I know that this question is similar, but the answers seem to suggest that it is impossible. But clearly Hibernate can do the find correctly, so why does the query not work?
EDIT: OK, so I found a NullableStringType class definition on this Hibernate JIRA Issue and added it to my project.
If I add a @Type definition on the site column of the PK using this type class, then I can successfully get non-null Entities back from the SELECT query, with the site field containing whatever String text I define as the representation of null.
However, it is still behaving differently. The find returns an Entity with the site field containing null, but the query returns an Entity with the site field containing "NaN" (the default representation of null).
It still feels like these should behave the same.
UPDATE 2: Some of you want to know specifics about the 'database' in question.
It is the Genesis RDBMS engine, written by Trifox Inc.. The data is stored in AcuCobol (now Micro Focus) Vision indexed files.
We have the configuration set to translate blank (SPACES) alphanumeric fields to NULL, hence our file records which contain spaces for the PK field are being translated to NULL. I can specifically select the appropriate record by using WHERE site_id IS NULL, so the RDBMS is treating these blank fields as an SQL NULL.
Having said all that I do not believe that this issue has anything to do with the 'database', apart from the fact that it is unusual to have PK fields being null.
What's more, if I log the SQL that Hibernate is using for both the find and the query, they are almost identical.
Here's the SQL for the find:
select tareweight0_.CODE as CODE274_0_, tareweight0_.SITE as SITE274_0_,
tareweight0_.WEIGHT as WEIGHT274_0_ from TARE_WEIGHT tareweight0_
where tareweight0_.CODE=? and tareweight0_.SITE=?
And here's the SQL for the Query:
select tareweight0_.CODE as CODE274_, tareweight0_.SITE as SITE274_,
tareweight0_.WEIGHT as WEIGHT274_ from TARE_WEIGHT tareweight0_
where tareweight0_.CODE=? and tareweight0_.SITE=?
As you can see, the only difference is the column alias names.
UPDATE 3: Here's some example data:
select code, site, weight from tare_weight where code like 'LC%';
CODE SITE WEIGHT
------ ------ -------
LC1 .81
LC2 .83
LC2 BEENLH .81
LC3 1.07
LC3 BEENLH 1.05
LC4 1.05
LCH1 .91
LCH2 .93
LCH2 BEENLH .91
LCH6 1.13
LCH6 BEENLH 1.11
And searching specifically for NULL:
select code, site, weight from tare_weight where code like 'LC%' and site IS NULL;
CODE SITE WEIGHT
------ ------ -------
LC1 .81
LC2 .83
LC3 1.07
LC4 1.05
LCH1 .91
LCH2 .93
LCH6 1.13
"So either they support it or they don't"
TL;DR That expectation/feeling is unjustified. The unsupported functionality in your link (& mine below) is exactly yours. "Not supporting" it means that if you do it then Hibernate can do anything they want. You are lucky that they (seem to) return reasonable values. (Although it's just a guess how they are acting. You don't have a specification.) There is no reason to expect anything, let alone consistency. When behaviour is just a consequence of some unsupported case arising, any "why" is most likely just an artifact of how the code was written with other cases in mind.
Here is a(n old) support thread answered by the Hibernate Team:
Post subject: Composite key with null value for one column
PostPosted: Mon Jul 03, 2006 2:21 am
I have table with composite primary key and I created following mapping for the table.As it is possible to insert a null value for any column in composite key as long as the combination of all columns is Unique, I have record in teh table which has null value for V_CHAR2 column ( which is part of composite key ) . when I execute a query on this entity I get null values for the records which are having null value of V_CHAR2 column. What's wrong in my mapping and implementation..
Posted: Tue Jul 11, 2006 9:09 am
Hibernate Team
a primary key cannot be null (neither fully or partial)
Posted: Sat Jan 06, 2007 5:35 am
Hibernate Team
sorry to disapoint you but null in primary key is not supported - primarily because doing join's and comparisons will require alot of painfullly stupid code that is just not needed anywhere else.....think about it and you will see (e.g. how do you do a correct join with such a table)
This is not surprising, because NULL is not allowed in a PK column in SQL. A PRIMARY KEY declaration is a synonym for UNIQUE NOT NULL. NULL is not equal to anything with the (misguided) intent that some unrecorded value is not known to be equal. (Your expectations of some kind of exception for at least some occasions of NULL in a PK equaling a NULL in a condition is contrary to SQL.) Given that NULL is not allowed in PK values, we can expect PK optimizations related to 1:1 mappings and to sets rather than bags of rows to assume there are no NULLs when it's convenient. One can expect that Hibernate decided to not worry about what their implementation did with cases that shouldn't arise in the SQL. Too bad they don't tell you that on compilation or execution. Hopefully it is in documentation.)
Even find differing from createQueryre NULL is not surprising. The former involves one value while the latter involves what are expected to be sets (not bags) of rows without NULLs (but aren't).
A workaround may be to not treat any column of a primary key as NULL but as the actual string of spaces in storage. (Whatever this means given your storage/DBMS/hibernate/JPA/Java stack. You haven't given us enough information to know whether your Cobol view of the database would be impeded by not mapping spaces to NULL for your JPA). With your data you can still declare a UNIQUE index on the columns.
这篇关于JPA查询返回空值-具有空列的复合键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
