具有空值的JPA复合主键 [英] JPA composite primary key with null value
问题描述
我在oracle数据库中有一个包含客户数据的表。这是一个简化的定义:
I have a table containing customer data in an oracle database. Here is a simplified definition:
CUSTOMER (CUSTOMER_ID NUMBER NOT NULL,
SOURCE_SYSTEM VARCHAR2(30),
FULL_NAME VARCHAR2(360),
PHONE_NUMBER VARCHAR2(240)
)
该表的主键是(CUSTOMER_ID,SOURCE_SYSTEM)
。
该表有许多行 SOURCE_SYSTEM
为空。在数据库级别,没有问题,但是当我尝试通过JPA实体访问任何这些行时,它会导致许多问题:
The table has numerous rows for which SOURCE_SYSTEM
is null. At the database level, there is no issue, however when I try to access any of these rows via JPA Entity, it causes a number of issues:
1:使用 em.find()
获取一个空的行 SOURCE_SYSTEM
总是会返回null。
1: Using em.find()
to fetch a row with a null SOURCE_SYSTEM
always results in a null being returned.
2:使用 em.merge()
来插入一个空行 SOURCE_SYSTEM $ c如果表中不存在记录,则$ c>成功,但后续更新失败,因为合并ALWAYS导致插入正在运行。
2: Using em.merge()
to upsert a row with a null SOURCE_SYSTEM
succeeds if the record does not exist in the table, but fails on subsequent updates because the merge ALWAYS results in an insert being run.
3:使用 em.createQuery()
显式查询带有null的行会导致以下异常:
3: Using em.createQuery()
to explicitly query for a row with a null causes the following exception:
Exception [EclipseLink-6044] (Eclipse Persistence Services - 2.3.1.v20111018-r10243):
org.eclipse.persistence.exceptions.QueryException
Exception Description: The primary key read from the row [ArrayRecord(
CUSTOMER.CUSTOMER_ID => 1
CUSTOMER.FULL_NAME => GUY PERSON
CUSTOMER.PHONE_NUMBER => 555-555-1234
CUSTOMER.SOURCE_SYSTEM => null)] during the execution of the query was detected to be null.
Primary keys must not contain null.
Query: ReadAllQuery(referenceClass=Customer sql="SELECT CUSTOMER_ID, FULL_NAME, PHONE_NUMBER, SOURCE_SYSTEM FROM CUSTOMER WHERE ((CUSTOMER_ID = ?) AND (SOURCE_SYSTEM IS NULL))")
不幸的是,主键不能包含null似乎是最终的。我无法找到有关此错误的变通方法的太多信息,这使得它似乎没有解决方案。
Unfortunately, "Primary keys must not contain null" seems pretty final. I was unable to find too much information on workarounds for this error, which makes it seem like there is no solution.
问题:我想知道是否有人有任何 基于Java代码的解决方案 不涉及对数据库进行更改。我目前的解决方法是使用 ROW_ID
作为表的 @Id
,但这意味着我无法再使用 em.merge()
或 em.find()
。
THE QUESTION: I would like to know if anyone has any Java code-based solution that don't involve making changes to the database. My current workaround is to use ROW_ID
as the @Id
of the table, but this means I can no longer use em.merge()
or em.find()
.
以下是我的Java类:
Here are my Java classes:
Customer.java:
Customer.java:
@Entity
@Table(name = "CUSTOMER")
public class Customer implements Serializable {
private static final long serialVersionUID = 1L;
@EmbeddedId
private Customer_Id key;
@Column(name = "CUSTOMER_ID", nullable = false, insertable = false, updatable = false)
private Long customerId;
@Column(name = "SOURCE_SYSTEM", length = 30, insertable = false, updatable = false)
private String sourceSystem;
@Column(name = "FULL_NAME", length = 360)
private String fullName;
@Column(name = "PHONE_NUMBER", length = 240)
private String phoneNumber;
//Setters, Getters, etc
...
}
Customer_Id.java
Customer_Id.java
@Embeddable
public class Customer_Id implements Serializable {
private static final long serialVersionUID = 1L;
@Column(name = "CUSTOMER_ID", nullable = false)
private Long customerId;
@Column(name = "SOURCE_SYSTEM", length = 30)
private String sourceSystem;
//Setters, Getters, etc
...
}
推荐答案
主键不能包含null(在JPA或数据库中)。使用不同的值,例如或。
Primary keys cannot contain null (in JPA or in databases). Use a different value such as "" or " ".
customer_id是唯一的吗?如果是这样,那么只需从Id中删除sourceSystem。
Is the customer_id unique? if so then just remove the sourceSystem from the Id.
否则,您可以尝试记录错误以支持添加空ID。
Otherwise, you could try logging a bug to have support for null ids added.
这篇关于具有空值的JPA复合主键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!