[hibernate-dev] Composite IDs with a null property/field
Gail Badner
gbadner at redhat.com
Tue Dec 17 13:41:41 EST 2019
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