OneToOne relationship and "is not null" query

kajh · **Joined:** Tue Aug 16, 2005 3:44 pm **Posts:** 33

I have onetoone relationship mapped with hibernate annotations.

When I try to do a "is not null" query from the "owning" side hibernate just check to see if the id is not null...

From what I can understand this is not the correct sql... Agree? If yes, have I done anything wrong here which can be the reason?

I'm not what is the best way in sql us to check is work.xmlMetadata is not null, but I guess one way could be a "not exists" subselect.

My hql:

Code:

select work.workId from Work work where work.xmlMetadata is not null

Generated sql:

Code:

select work0_.WORKID as col_0_0_ from BIB_WORK work0_ where work0_.WORKID is not null

Hibernate version:
hibernate-3.2.5.ga

Mapping documents:

Work.java

Code:

    @OneToOne()
    @JoinColumn(name = "WORKID")
    @Cascade( { CascadeType.ALL })
    public XmlMetadata getXmlMetadata() {
        return xmlMetadata;
    }

XmlMetadata.java

Code:

    @OneToOne(mappedBy = "xmlMetadata")
    public Work getWork() {
        return work;
    }

Name and version of the database you are using:
Oracle 10.2.0.3.0

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

It is a perfect way, because the relationship is one-to-one.
If it had been one-to-many, you could have used something in the from of

Code:

where exists (parent.children)

kajh · **Joined:** Tue Aug 16, 2005 3:44 pm **Posts:** 33

Thanks for answering!

It seems that this is a known issue, see http://opensource.atlassian.com/projects/hibernate/browse/HHH-2158

Feel free to vote on this issue :)

My workaround was to use jdbc to fetch the list of ids I was looking for.

-Kaj :)

ritesh_randomgmail · **Posted:** Fri Feb 29, 2008 4:11 pm

Kaj,

I am trying to understand this better. Would you mind positing the schema for your two tables? And if you feeling generous, then could you provide the ddl stmts that Hibernate used to created those tables?

Ritesh

kajh · **Joined:** Tue Aug 16, 2005 3:44 pm **Posts:** 33

ritesh_randomgmail wrote:

Kaj,
I am trying to understand this better. Would you mind positing the schema for your two tables? And if you feeling generous, then could you provide the ddl stmts that Hibernate used to created those tables?

Thanks for answering!

I have put a "desc" in sqlplus of the two tables at http://folk.uio.no/kajh/tmp/onetoone.txt

This is a legacy database we have "inherited", so I don't have the ddl stmts.

I'm beginning to think it might have been better to make the relationship a one-to-many (which it in fact is in the database).

but... since in practice each work object only can have one and only one xmlmetadata object we made it a one-to-one relationship in hibernate. I'm getting more and more unsure how smart this decision was...

After searching the hibernate jira for issues regarding one-to-one relationships I'm beginning to wonder if we should change this into a one-to-many and make a wrapper metod retining just the first object of the collection (getXmlMetadata returning the first object in getXmlMetadatas if size > 0 else return null.

It would be interesting to hear from other people out there who use (or have tried to use) one-to-one relationships in hibernate. What are their experience with one-to-one relationships?

-Kaj :)

ritesh_randomgmail · **Posted:** Fri Feb 29, 2008 5:51 pm

Kaj,

I am gonna monitor this topic to see if anybody else has the hibernate mapping solution for your problem.

I know you must've looked at this.. but in case you haven't do have a look here and see if it helps
http://www.allapplabs.com/hibernate/hib ... lement.htm

You have a 1 --> 0..1 association there. So making it a one-many relationship sounds fine to me. for now . :)

You could make your wrapper method enforce that relationship. If the constraint can't be implement by the ORM tool or the DB, then you could make the java DAO wrapper methods ensure that constraint, by making all the methods add-update-find methods throw an exception if that constraint is being violated.

hope that makes sense.. if at all :)