java 具有空值的 JPA 连接列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1667687/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
JPA join column with null values
提问by javydreamercsw
I have a query that works in plain SQL but is not working on JPA and can't figure out why. As you can guess from the title I have a clue but I don't know how to "fix" it.
我有一个在普通 SQL 中工作的查询，但在 JPA 上不起作用，无法弄清楚原因。正如你可以从标题中猜到的，我有一个线索，但我不知道如何“修复”它。
Here's the actual important code:
这是实际的重要代码：
@Id
@Basic(optional = false)
@Column(name = "id", nullable = false)
private Integer id;
@Basic(optional = false)
@Column(name = "read_permission", nullable = false)
private boolean readPermission;
@Basic(optional = false)
@Column(name = "write_permission", nullable = false)
private boolean writePermission;
@Basic(optional = false)
@Column(name = "execute_permission", nullable = false)
private boolean executePermission;
@Basic(optional = false)
@Column(name = "admin_permission", nullable = false)
private boolean adminPermission;
@JoinColumn(name = "xinco_core_data_id", referencedColumnName = "id", nullable=true)
@ManyToOne(fetch = FetchType.LAZY)
private XincoCoreData xincoCoreDataId;
@JoinColumn(name = "xinco_core_group_id", referencedColumnName = "id", nullable=true)
@ManyToOne(fetch = FetchType.LAZY)
private XincoCoreGroup xincoCoreGroupId;
@JoinColumn(name = "xinco_core_node_id", referencedColumnName = "id", nullable=true)
@ManyToOne(fetch = FetchType.LAZY)
private XincoCoreNode xincoCoreNodeId;
@JoinColumn(name = "xinco_core_user_id", referencedColumnName = "id", nullable=true)
@ManyToOne(fetch = FetchType.LAZY)
private XincoCoreUser xincoCoreUserId;
And here's the working sql:
这是工作的sql：
select * from xinco_core_ace where xinco_core_user_id = 1 order by xinco_core_user_id, xinco_core_node_id, xinco_core_data_id;
And here's what I'm attempting to do:
这就是我正在尝试做的：
SELECT xca FROM XincoCoreAce xca WHERE xca.xincoCoreUserId.id = 1 ORDER BY xca.xincoCoreUserId.id, xca.xincoCoreGroupId.id, xca.xincoCoreNodeId.id, xca.xincoCoreDataId.id
The issue, I think, is that the xca.xincoCoreUserId.id, xca.xincoCoreGroupId.id, xca.xincoCoreNodeId.id, xca.xincoCoreDataId.id can be nulls.
我认为，问题在于 xca.xincoCoreUserId.id、xca.xincoCoreGroupId.id、xca.xincoCoreNodeId.id、xca.xincoCoreDataId.id 可以为空。
Any idea? Hopefully is easier to read :P
任何的想法？希望更容易阅读：P
回答by Repoker
Also happened to me with a simpler query:
我也遇到了一个更简单的查询：
select t from Task t where t.worker is not null order by t.worker.normalizedName
Found that any task result entity where the attribute worker is null (the task is unassigned) would be discarded. Later I found out that this is because path navigation in JPA is done using inner joins (the specification says so) and this will exclude any result where a part of the path is null..
发现任何属性worker为null（任务未分配）的任务结果实体都会被丢弃。后来我发现这是因为 JPA 中的路径导航是使用内部连接完成的（规范是这样说的），这将排除路径的一部分为空的任何结果。
This bug report describes accurately the issue:
此错误报告准确地描述了问题：
https://bugs.eclipse.org/bugs/show_bug.cgi?id=363798
https://bugs.eclipse.org/bugs/show_bug.cgi?id=363798
Unfortunately, this is not an implementation bugand you will have to refactor your entities/queries to avoid these kind of situations.
不幸的是，这不是一个实现错误，您将不得不重构您的实体/查询以避免这种情况。
回答by javydreamercsw
This is the actual query done (using eclipselink logging):
这是实际完成的查询（使用 eclipselink 日志记录）：
SELECT t1.id, t1.write_permission, t1.admin_permission, t1.execute_permission, t1.read_permission, t1.xinco_core_user_id, t1.xinco_core_data_id, t1.xinco_core_group_id, t1.xinco_core_node_id FROM xinco_core_data t4, xinco_core_node t3, xinco_core_group t2, xinco_core_ace t1, xinco_core_user t0 WHERE ((t3.id = ?) AND ((((t3.id = t1.xinco_core_node_id) AND (t0.id = t1.xinco_core_user_id)) AND (t2.id = t1.xinco_core_group_id)) AND (t4.id = t1.xinco_core_data_id))) ORDER BY t0.id ASC, t2.id ASC, t3.id ASC, t4.id ASC bind => [1]
For some reason having the Order by adds a lot of table crosschecking in which having nulls make the result come out empty.
出于某种原因，Order by 添加了很多表交叉检查，其中有空值会使结果为空。
Removing the order by gets the required result (out of order of course).
删除 order by 会得到所需的结果（当然是乱序的）。
See this Eclipselink bug
看到这个 Eclipselink错误
回答by kovica
I'm no JPA expert here, but I'd try to get the actual SQL that is run on the database. Maybe the FetchType.LAZY has something to do with the problem?
我不是这里的 JPA 专家，但我会尝试获取在数据库上运行的实际 SQL。也许 FetchType.LAZY 与问题有关？
