[hibernate-dev] Composite IDs with a null property/field
Gail Badner
gbadner at redhat.com
Thu Dec 19 18:59:04 EST 2019
I agree with Steve. I don't think that we should add a feature to
explicitly support this.
That said, there is a workaround that avoids the issue, by using a UserType
to convert null to an empty string for Java processing, and convert the
empty string to null for inserting, update, and deleting.
I've created 2 tests that illustrate how this can be done with a UserType
and a custom @Loader, @SQLUpdate, and @SQLDelete:
Using an embedded ID:
https://github.com/gbadner/hibernate-core/blob/composite-id-with-null-property/hibernate-core/src/test/java/org/hibernate/test/cid/EmbeddedIdUserTypeNullableColumnTest.java
Using an IdClass:
https://github.com/gbadner/hibernate-core/blob/composite-id-with-null-property/hibernate-core/src/test/java/org/hibernate/test/cid/IdClassUserTypeNullableColumnTest.java
It will not be possible to execute an HQL/JPL query like:
CompositeKeyEntity compositeKeyEntity = session.createQuery(
"from CKE where compositeKey = ?1",
CompositeKeyEntity.class
).setParameter( 1, new CompositeKey( "abc", LocaleType.DEFAULT )
).uniqueResult();
because Hibernate generates the SQL:
select embeddable0_.id1 as id1_0_ embeddable0_.id2 as id2_0_,
embeddable0_.name as name3_0_ from CKE embeddable0_ where
embeddable0_.id1=? and embeddable0_.id2=?
If an entity has id2 == null, then it will not be possible to load it using
this query.
The following will work though:
CompositeKeyEntity compositeKeyEntity = session.createQuery(
"from CKE where compositeKey.id1 = ?1 and (compositeKey.id2 = ?2 or
compositeKey.id2 is null )" ,
CompositeKeyEntity.class
).setParameter( 1, "abc" ).setParameter( 2, LocaleType.DEFAULT
).uniqueResult();
Regards,
Gail
On Wed, Dec 18, 2019 at 1:48 PM Steve Ebersole <steve at hibernate.org> wrote:
> I've already chimed in ;)
>
> I don't think this is something we should do.
>
> On Wed, Dec 18, 2019, 3:44 PM Gail Badner <gbadner at redhat.com> wrote:
>
>> The main obstacle I see is that Hibernate assumes that ID columns are
>> non-nullable, even if ID columns are explicitly mapped as nullable.
>>
>> This is consistent with the JPA spec, which says:
>>
>> "Every entity must have a primary key."
>>
>> ANSI SQL 92 says:
>>
>> "In addition, if the unique constraint was defined with PRIMARY KEY, then
>> it requires that none of the values in the specified column or columns be
>> the null value."
>>
>> javax.persistence.Column#nullable has a default of true.
>>
>> Since Hibernate ignores the default (as appropriate for a primary key), I
>> think that it is also appropriate to ignore an explicit mapping to make it
>> nullable (i.e., @Column(nullable="true")).
>>
>> I don't think it's a good idea to add a new property that would change
>> this behavior for ID columns.
>>
>> Aside from the issue I mentioned above about ComponentType#hydrate [1],
>> SQL Hibernate generates for loading an entity by ID would have to change.
>> Currently, Hibernate generates SQL like the following:
>>
>> select ... from ... where bundle = ? and key = ? and locale = ?
>>
>> If the locale column is null, then Session#get will return null.
>>
>> It is possible to create a custom (@Loader) to change the query used by
>> Session#get.
>>
>> Unfortunately, that won't work when loading entities with a Query like
>> "from ... where id = ?".
>>
>> IMO, if we want to support this use case, we should use
>> Hibernate-specific annotations to indicate that a composite ID column
>> should be forced to be nullable.
>>
>> I haven't thought too much about this yet, but something like the
>> following comes to mind:
>>
>> @EnbeddedId
>> @UniqueKeyIdColumns( uniqueKeyIdColumns = {
>> @UniqueKeyIdColumn( name="BUNDLE_NAME" ),
>> @UniqueKeyIdColumn( name="KEY" ),
>> @UniqueKeyIdColumn( name="LOCALE", nullable="true" )
>> }
>>
>> The default for UniqueKeyIdColumn#nullable would be false.
>>
>> This would likely affect SPIs.
>>
>> Honestly, I am on the fence about whether this use case should be
>> supported.
>>
>> Steve, Emmanuel, please chime in with your opinion?
>>
>> Thanks,
>> Gail
>>
>> [1]
>> https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/java/org/hibernate/type/ComponentType.java#L671-L675
>>
>> On Tue, Dec 17, 2019 at 10:41 AM Gail Badner <gbadner at redhat.com> wrote:
>>
>>> Jan-Willem, thanks very much for your feedback!
>>>
>>> You may be right about Hibernate not allowing a formula for a composite
>>> ID property. It appears it's not allowed when hbm mapping is used. I'll
>>> have to check to see if a formula is allowed when mapped with annotations..
>>>
>>> Looking a the mapping for Address again, I see the composite ID does not
>>> contain a formula. Instead Address is associated with 2 different entity
>>> names, each with a specified "where" attribute that indicates which entity
>>> name the specific mapping is for:
>>>
>>> <class name="Address"
>>> table="Address"
>>> entity-name="BillingAddress"
>>> where="add_type='BILLING'"
>>> check="add_type in ('BILLING', 'SHIPPING')"
>>> select-before-update="true"
>>> dynamic-update="true">
>>>
>>> <composite-id name="addressId">
>>> <key-property name="addressId"/>
>>> <key-property name="type" column="add_type"/>
>>> </composite-id>
>>> ...
>>>
>>> </class>
>>>
>>> <class name="Address"
>>> table="Address"
>>> entity-name="ShippingAddress"
>>> where="add_type='SHIPPING'"
>>> select-before-update="true"
>>> dynamic-update="true">
>>>
>>> <composite-id name="addressId">
>>> <key-property name="addressId"/>
>>> <key-property name="type" column="add_type"/>
>>> </composite-id>
>>> ...
>>> </class>
>>>
>>> We would need to check the entity's "where" attribute or @Where clause
>>> attribute value to see if the fragment references a composite ID column.
>>>
>>> Regards,
>>> Gail
>>>
>>> On Tue, Dec 17, 2019 at 1:29 AM Jan-Willem Gmelig Meyling <
>>> jan-willem at youngmediaexperts.nl> wrote:
>>>
>>>> PostgreSQL doesn’t allow nullable columns in a compound primary key. It
>>>> allows a unique constraint on a nullable column, but then it happily
>>>> inserts the two values below - without constraint violation error.
>>>>
>>>> I too have stumbled upon the need for nullable identifiers in Hibernate
>>>> however. Mostly when I want to map native query results without an actual
>>>> PK to an entity class. Another use case is mapping a @Subselect entity.
>>>> These may not have an actual identifier, for example when creating a join
>>>> product, so in order for all results to appear at all, you specify a
>>>> compound key based on your requirements. I have experienced that this
>>>> compound key may contain null values (in case of a LEFT JOIN for example).
>>>>
>>>> As far as I am aware, it is currently not allowed to use Formula’s as
>>>> @Id - I’ve stumbled upon this exception recently and I think correctly so .
>>>> Maybe it is allowed for compound keys however. I think its safe to only
>>>> allow null properties in an compound identifier if none of the identifier
>>>> properties is a formula.
>>>>
>>>> Kind regards,
>>>>
>>>> Jan-Willem
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> > On 17 Dec 2019, at 00:26, Gail Badner <gbadner at redhat.com> wrote:
>>>> >
>>>> > I've confirmed that the same inserts result in a constraint violation
>>>> using
>>>> > Oracle 12c:
>>>> >
>>>> > insert into bundle (BUNDLE_NAME, KEY, LOCALE) values('bundle1',
>>>> 'key1',
>>>> > null);
>>>> > insert into bundle (BUNDLE_NAME, KEY, LOCALE) values('bundle1',
>>>> 'key1',
>>>> > null);
>>>> >
>>>> > I'm trying to figure out what would be needed to support this.
>>>> >
>>>> > I tried simply commenting out this line:
>>>> >
>>>> https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/java/org/hibernate/type/ComponentType.java#L673
>>>> >
>>>> > When I ran the unit
>>>> > tests,
>>>> org.hibernate.test.typedmanytoone.TypedManyToOneTest#testCreateQueryNull
>>>> > failed because of a lazy many-to-one with a formula for a foreign key
>>>> > column:
>>>> >
>>>> > <many-to-one name="billingAddress"
>>>> > entity-name="BillingAddress"
>>>> > cascade="persist,save-update,delete"
>>>> > fetch="join">
>>>> > <column name="billingAddressId"/>
>>>> > <formula>'BILLING'</formula>
>>>> > </many-to-one>
>>>> >
>>>> > The change results in a Customer with a lazy ShippingAddress proxy
>>>> > containing an AddressId with #addressId == null and #type ==
>>>> "BILLING').
>>>> > When the proxy is initialized, ObjectNotFoundException is thrown.
>>>> >
>>>> > The reason this happens is a bit complicated.
>>>> >
>>>> > The problematic query is:
>>>> >
>>>> > "from Customer cust left join fetch cust.billingAddress where
>>>> > cust.customerId='xyz123'"
>>>> >
>>>> > The SQL that gets generated is:
>>>> >
>>>> > select
>>>> > customer0_.customerId as customer1_1_0_,
>>>> > billingadd1_.addressId as addressI1_0_1_,
>>>> > billingadd1_.add_type as add_type2_0_1_,
>>>> > customer0_.name as name2_1_0_,
>>>> > customer0_.billingAddressId as billingA3_1_0_,
>>>> > customer0_.shippingAddressId as shipping4_1_0_,
>>>> > 'BILLING' as formula0_0_,
>>>> > 'SHIPPING' as formula1_0_,
>>>> > billingadd1_.street as street3_0_1_,
>>>> > billingadd1_.city as city4_0_1_,
>>>> > billingadd1_.state as state5_0_1_,
>>>> > billingadd1_.zip as zip6_0_1_
>>>> > from
>>>> > Customer customer0_
>>>> > left outer join
>>>> > Address billingadd1_
>>>> > on customer0_.billingAddressId=billingadd1_.addressId
>>>> > and 'BILLING'=billingadd1_.add_type
>>>> > where
>>>> > customer0_.customerId='xyz123'
>>>> >
>>>> > In this case, the Customer entity does not have a billingAddress.
>>>> >
>>>> > Hibernate correctly determines that the join fetched Address is null
>>>> > because addressI1_0_1_ and add_type2_0_1_ are both null.
>>>> >
>>>> > The problem happens when the Customer entity gets initialized. Since
>>>> > Customer#billingAddress is mapped as lazy, it gets resolved as a
>>>> proxy with
>>>> > AddressId#addressId == null and #type == "BILLING").
>>>> >
>>>> > Similarly, Customer#shippingAddress gets resolved as a proxy with
>>>> > AddressId#addressId == null and #type == "SHIPPING").
>>>> >
>>>> > Without the change Customer#billingAddress and #shippingAddress are
>>>> null.
>>>> >
>>>> > I don't see any way to maintain functionality demonstrated
>>>> > by TypedManyToOneTest at the same time as allowing a composite ID to
>>>> have a
>>>> > null property at the same time.
>>>> >
>>>> > I suppose we could allow a composite ID to have a null property only
>>>> when
>>>> > it has no properties that are formulas.
>>>> >
>>>> > WDYT?
>>>> >
>>>> > Thanks,
>>>> > Gail
>>>> >
>>>> > On Thu, Dec 12, 2019 at 10:26 AM Gail Badner <gbadner at redhat.com>
>>>> wrote:
>>>> >
>>>> >> Thinking about this more, I realized that, depending on the
>>>> database, the
>>>> >> use case may be invalid.
>>>> >>
>>>> >> For H2, at least, the following is allowed with a unique constraint:
>>>> >>
>>>> >> insert into bundle (BUNDLE_NAME, KEY, LOCALE) values('bundle1',
>>>> 'key1',
>>>> >> null);
>>>> >> insert into bundle (BUNDLE_NAME, KEY, LOCALE) values('bundle1',
>>>> 'key1',
>>>> >> null);
>>>> >>
>>>> >> The reason why the unique constraint is not violated is because null
>>>> !=
>>>> >> null.
>>>> >>
>>>> >> If we allow a null value for a composite ID property, it should be
>>>> >> specific to the dialects that would assume null == null in a unique
>>>> key. I
>>>> >> believe SQL Server behaves this way. I'm not sure about other
>>>> databases.
>>>> >>
>>>> >> On Wed, Dec 11, 2019 at 3:06 AM Emmanuel Bernard <
>>>> emmanuel at hibernate.org>
>>>> >> wrote:
>>>> >>
>>>> >>> My answer is that if the code change looks too impactful I'm fine
>>>> with no
>>>> >>> supporting such scenario.
>>>> >>>
>>>> >>> On 11 Dec 2019, at 11:24, Joerg Baesner wrote:
>>>> >>>
>>>> >>>> ... I suppose some means it as default.
>>>> >>>
>>>> >>> Yes, exactly.
>>>> >>>
>>>> >>> Your reply doesn't answer the question if Hibernate shouldn't
>>>> support
>>>> >>> this scenario. Anyhow, what Gail already wrote is that Hibernate
>>>> returns
>>>> >>> null for the entity result, leading to a null value in a returned
>>>> >>> ResultList, which seem to be wrong...
>>>> >>>
>>>> >>> On Wed, Dec 11, 2019 at 11:16 AM Emmanuel Bernard <
>>>> emmanuel at hibernate.org>
>>>> >>> wrote:
>>>> >>>
>>>> >>>> We have been trying to keep a balance of maintainable code base for
>>>> >>>> Hibernate vs legacy/counter intuitive/plain wrong DB designs. The
>>>> answer is
>>>> >>>> never clear cut. In your case I'm not sure what a bundle + key
>>>> means if it
>>>> >>>> does not have a locale - I suppose some means it as default.
>>>> >>>>
>>>> >>>> On 11 Dec 2019, at 10:49, Joerg Baesner wrote:
>>>> >>>>
>>>> >>>>> I think in the past we argued the same for attributes of a
>>>> composite
>>>> >>>> id,
>>>> >>>>> like you said, if one of the element can be nul, why is it in the
>>>> id
>>>> >>>>> property in the first place.
>>>> >>>>
>>>> >>>> As an example you might Imagine someone wants to put
>>>> >>>> internationalization properties into a database and having a table
>>>> >>>> structure like this (this might be an old legacy application that
>>>> doesn't
>>>> >>>> have a PK column):
>>>> >>>>
>>>> >>>> BUNDLE_NAME (not nullable)
>>>> >>>> KEY (not nullable)
>>>> >>>> LOCALE (nullable)
>>>> >>>> VALUE (not nullable)
>>>> >>>>
>>>> >>>> The first 3 (BUNDLE_NAME, KEY, LOCALE) are the CompositeKey and
>>>> there's
>>>> >>>> a unique constraint on the database on these columns.
>>>> >>>>
>>>> >>>> It is fine to have the LOCALE as <null>, as in this case the
>>>> systems
>>>> >>>> default locale would be used, but for each BUNDLE_NAME/KEY
>>>> combination you
>>>> >>>> could only have a single composite key with a <null> LOCALE.
>>>> >>>>
>>>> >>>> Hibernate should be (must be?) able to handle this scenario, what
>>>> do you
>>>> >>>> think?
>>>> >>>>
>>>> >>>> Joerg
>>>> >>>>
>>>> >>>> On Wed, Dec 11, 2019 at 10:18 AM Emmanuel Bernard <
>>>> >>>> emmanuel at hibernate.org> wrote:
>>>> >>>>
>>>> >>>>> Just talking about simple id, even if we allow the column to be
>>>> >>>>> nullable
>>>> >>>>> (if the DB even allows that), I don't think Hibernate allows null
>>>> to be
>>>> >>>>> a valid id value. Because null means I don't know or not
>>>> applicable.
>>>> >>>>> I think in the past we argued the same for attributes of a
>>>> composite
>>>> >>>>> id,
>>>> >>>>> like you said, if one of the element can be nul, why is it in the
>>>> id
>>>> >>>>> property in the first place.
>>>> >>>>>
>>>> >>>>> As for whether there is a strong implementation detail reason to
>>>> not
>>>> >>>>> allow it, I don't know but I assume the null checking assuming
>>>> "not an
>>>> >>>>> id" is pretty much all over the place.
>>>> >>>>>
>>>> >>>>> Emmanuel
>>>> >>>>>
>>>> >>>>> On 11 Dec 2019, at 3:37, Gail Badner wrote:
>>>> >>>>>
>>>> >>>>>> Currently, there is no way to load an entity that exists in the
>>>> >>>>>> database
>>>> >>>>>> with a composite ID, if one of the composite ID columns is null.
>>>> >>>>>>
>>>> >>>>>> This behavior is due to this code in ComponentType#hydrate:
>>>> >>>>>>
>>>> >>>>>
>>>> https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/java/org/hibernate/type/ComponentType.java#L671-L675
>>>> >>>>>>
>>>> >>>>>> Basically, if any field/property in a composite ID is null,
>>>> Hibernate
>>>> >>>>>> assumes the entire ID is null. An entity cannot have a null ID,
>>>> so it
>>>> >>>>>> returns null for the entity result.
>>>> >>>>>>
>>>> >>>>>> I believe that Hibernate does allow a primary key column to be
>>>> >>>>>> nullable.
>>>> >>>>>>
>>>> >>>>>> TBH, it seems strange to have a property in a composite ID that
>>>> can be
>>>> >>>>>> null. If it can be null, it seems that the property could be
>>>> removed
>>>> >>>>>> from
>>>> >>>>>> the composite key.
>>>> >>>>>>
>>>> >>>>>> I don't see anything in the spec about a requirement that all
>>>> >>>>>> composite ID
>>>> >>>>>> fields/properties must be non-null. Am I missing something?
>>>> >>>>>>
>>>> >>>>>> The code I referenced above is 13 years old. Does anyone have
>>>> insight
>>>> >>>>>> into
>>>> >>>>>> why Hibernate does this?
>>>> >>>>>>
>>>> >>>>>> Thanks,
>>>> >>>>>> Gail
>>>> >>>>>> _______________________________________________
>>>> >>>>>> hibernate-dev mailing list
>>>> >>>>>> hibernate-dev at lists.jboss.org
>>>> >>>>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>>> >>>>>
>>>> >>>>> _______________________________________________
>>>> >>>>> hibernate-dev mailing list
>>>> >>>>> hibernate-dev at lists.jboss.org
>>>> >>>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>>> >>>>>
>>>> >>>>>
>>>> >>>>
>>>> >>>> --
>>>> >>>>
>>>> >>>> JOERG BAESNER
>>>> >>>>
>>>> >>>> SENIOR SOFTWARE MAINTENANCE ENGINEER
>>>> >>>>
>>>> >>>> Red Hat
>>>> >>>>
>>>> >>>> <https://www.redhat.com/>
>>>> >>>>
>>>> >>>> jbaesner at redhat.com T: +49-211-95439691
>>>> >>>> <https://red.ht/sig>
>>>> >>>> TRIED. TESTED. TRUSTED. <https://redhat.com/trusted>
>>>> >>>>
>>>> >>>>
>>>> >>>
>>>> >>> --
>>>> >>>
>>>> >>> JOERG BAESNER
>>>> >>>
>>>> >>> SENIOR SOFTWARE MAINTENANCE ENGINEER
>>>> >>>
>>>> >>> Red Hat
>>>> >>>
>>>> >>> <https://www.redhat.com/>
>>>> >>>
>>>> >>> jbaesner at redhat.com T: +49-211-95439691
>>>> >>> <https://red.ht/sig>
>>>> >>> TRIED. TESTED. TRUSTED. <https://redhat.com/trusted>
>>>> >>>
>>>> >>>
>>>> > _______________________________________________
>>>> > hibernate-dev mailing list
>>>> > hibernate-dev at lists.jboss.org
>>>> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>>>
>>>>
More information about the hibernate-dev
mailing list