These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » NHibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 2 posts ] 
  Previous topic | Next topic 
Author Message
dturkel
 Post subject: Criteria Query problem with one-to-one mappings
PostPosted: Tue Sep 04, 2007 7:10 pm 
Newbie

Joined: Wed Oct 12, 2005 3:59 pm
Posts: 12
I'm having trouble properly mapping my one-to-one relationships OR setting up my criteria to properly have the foreign/primary key relationships join correctly... it's been a while since I've done much with Hibernate and could use some help.

KeywordCategorySlot should have a one-to-one with Keyword and KeywordCategory. For some reason, the KeywordCategorySlot is using its primary key, "KeywordCategorySlotId" instead of the respective foreign keys "KeywordId" and "KeywordCategoryId".

Details below... note the inline comments in the SHOW_SQL.

Thanks!


Hibernate version: 1.2GA

Mapping documents:
Code:
<?xml version="1.0" encoding="utf-8"?>
<!--Generated from NHibernate.Mapping.Attributes on 2007-09-04 18:54:48Z.-->
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="PhotoGalleries.Domain.Gallery, PhotoGalleryDomain" table="Gallery">
    <id name="GalleryId">
      <generator class="native" />
    </id>
    <property name="GalleryPublishState" type="string" column="GalleryPublishState" length="30" not-null="true" />
    <property name="CreateDate" type="timestamp" column="CreateDate" />
    <property name="UpdateDate" type="timestamp" column="UpdateDate" />
    <property name="CreateUser" type="string" column="CreateUser" length="30" />
    <property name="UpdateUser" type="string" column="UpdateUser" length="30" />
    <one-to-one name="GalleryType" class="PhotoGalleries.Domain.GalleryType, PhotoGalleryDomain" />
    <bag name="Albums" table="Album" lazy="false" cascade="all-delete-orphan" inverse="true">
      <key column="GalleryId" foreign-key="AlbumsGalleryId" />
      <one-to-many class="PhotoGalleries.Domain.Album, PhotoGalleryDomain" not-found="exception" />
    </bag>
    <bag name="KeywordCategories" table="KeywordCategory" lazy="false" cascade="all-delete-orphan" inverse="true">
      <key column="GalleryId" foreign-key="KeywordCategoriesGalleryId" />
      <one-to-many class="PhotoGalleries.Domain.KeywordCategory, PhotoGalleryDomain" not-found="exception" />
    </bag>
  </class>
  <class name="PhotoGalleries.Domain.Album, PhotoGalleryDomain" table="Album">
    <id name="AlbumId">
      <generator class="native" />
    </id>
    <property name="AlbumPublishState" type="string" column="AlbumPublishState" length="30" not-null="true" />
    <property name="CreateDate" type="timestamp" column="CreateDate" />
    <property name="UpdateDate" type="timestamp" column="UpdateDate" />
    <property name="CreateUser" type="string" column="CreateUser" length="30" />
    <property name="UpdateUser" type="string" column="UpdateUser" length="30" />
    <many-to-one name="Gallery" class="PhotoGalleries.Domain.Gallery, PhotoGalleryDomain" column="GalleryId" not-null="true" />
    <list name="Slides" table="Slide" lazy="false" cascade="all-delete-orphan" inverse="true">
      <key column="AlbumId" foreign-key="SlidesAlbumId" />
      <index column="SlidePosition" />
      <one-to-many class="PhotoGalleries.Domain.Slide, PhotoGalleryDomain" not-found="exception" />
    </list>
  </class>
  <class name="PhotoGalleries.Domain.Keyword, PhotoGalleryDomain" table="Keyword">
    <id name="KeywordId">
      <generator class="native" />
    </id>
    <property name="KeywordValue" type="string" column="KeywordValue" length="50" not-null="true" />
    <property name="CreateDate" type="timestamp" column="CreateDate" />
    <property name="UpdateDate" type="timestamp" column="UpdateDate" />
    <property name="CreateUser" type="string" column="CreateUser" length="30" />
    <property name="UpdateUser" type="string" column="UpdateUser" length="30" />
  </class>
  <class name="PhotoGalleries.Domain.SlideContentItem, PhotoGalleryDomain" table="SlideContentItem">
    <id name="SlideContentItemId">
      <generator class="native" />
    </id>
    <property name="Path" type="string" column="Path" length="300" not-null="true" />
    <property name="Credit" type="string" column="Credit" length="300" not-null="true" />
    <property name="CreateDate" type="timestamp" column="CreateDate" />
    <property name="UpdateDate" type="timestamp" column="UpdateDate" />
    <property name="CreateUser" type="string" column="CreateUser" length="30" />
    <property name="UpdateUser" type="string" column="UpdateUser" length="30" />
    <many-to-one name="Slide" class="PhotoGalleries.Domain.Slide, PhotoGalleryDomain" column="SlideId" not-null="true" />
  </class>
  <class name="PhotoGalleries.Domain.KeywordCategorySlot, PhotoGalleryDomain" table="KeywordCategorySlot">
    <id name="KeywordCategorySlotId">
      <generator class="native" />
    </id>
    <property name="CreateDate" type="timestamp" column="CreateDate" />
    <property name="UpdateDate" type="timestamp" column="UpdateDate" />
    <property name="CreateUser" type="string" column="CreateUser" length="30" />
    <property name="UpdateUser" type="string" column="UpdateUser" length="30" />
    <many-to-one name="Slide" class="PhotoGalleries.Domain.Slide, PhotoGalleryDomain" column="SlideId" not-null="true" />
    <one-to-one name="Keyword" class="PhotoGalleries.Domain.Keyword, PhotoGalleryDomain" foreign-key="FK_Keyword_KeywordCategorySlot" />
    <one-to-one name="KeywordCategory" class="PhotoGalleries.Domain.KeywordCategory, PhotoGalleryDomain" foreign-key="FK_KeywordCategory_KeywordCategorySlot" />
  </class>
  <class name="PhotoGalleries.Domain.SlideTag, PhotoGalleryDomain" table="SlideTag">
    <id name="SlideTagId">
      <generator class="native" />
    </id>
    <property name="SlideId" type="long" column="SlideId" not-null="true" />
    <property name="TagValue" type="string" column="TagValue" length="50" not-null="true" />
    <property name="CreateDate" type="timestamp" column="CreateDate" />
    <property name="UpdateDate" type="timestamp" column="UpdateDate" />
    <property name="CreateUser" type="string" column="CreateUser" length="30" />
    <property name="UpdateUser" type="string" column="UpdateUser" length="30" />
  </class>
  <class name="PhotoGalleries.Domain.Slide, PhotoGalleryDomain" table="Slide">
    <id name="SlideId">
      <generator class="native" />
    </id>
    <property name="SlidePosition" type="int" column="SlidePosition" not-null="true" />
    <property name="CreateDate" type="timestamp" column="CreateDate" />
    <property name="UpdateDate" type="timestamp" column="UpdateDate" />
    <property name="CreateUser" type="string" column="CreateUser" length="30" />
    <property name="UpdateUser" type="string" column="UpdateUser" length="30" />
    <many-to-one name="AlbumId" class="PhotoGalleries.Domain.Album, PhotoGalleryDomain" column="AlbumId" not-null="true" />
    <bag name="SlideContentItems" table="SlideContentItem" lazy="false" cascade="all-delete-orphan" inverse="true">
      <key column="SlideId" foreign-key="SlideContentSlideId" />
      <one-to-many class="PhotoGalleries.Domain.SlideContentItem, PhotoGalleryDomain" not-found="exception" />
    </bag>
    <bag name="KeywordCategorySlots" table="KeywordCategorySlot" lazy="false" cascade="all-delete-orphan" inverse="true">
      <key column="SlideId" foreign-key="SlideKeywordCategorySlotSlideId" />
      <one-to-many class="PhotoGalleries.Domain.KeywordCategorySlot, PhotoGalleryDomain" not-found="exception" />
    </bag>
  </class>
  <class name="PhotoGalleries.Domain.KeywordCategory, PhotoGalleryDomain" table="KeywordCategory">
    <id name="KeywordCategoryId">
      <generator class="native" />
    </id>
    <property name="CategoryName" type="string" column="CategoryName" length="30" not-null="true" />
    <property name="CreateDate" type="timestamp" column="CreateDate" />
    <property name="UpdateDate" type="timestamp" column="UpdateDate" />
    <property name="CreateUser" type="string" column="CreateUser" length="30" />
    <property name="UpdateUser" type="string" column="UpdateUser" length="30" />
    <many-to-one name="Gallery" class="PhotoGalleries.Domain.Gallery, PhotoGalleryDomain" column="GalleryId" not-null="true" />
    <bag name="PermittedKeywordValues" table="Keyword" lazy="false" cascade="all-delete-orphan" inverse="true">
      <key column="KeywordCategoryId" foreign-key="KeywordCategoryKeywordId" />
      <one-to-many class="PhotoGalleries.Domain.Keyword, PhotoGalleryDomain" not-found="exception" />
    </bag>
  </class>
  <class name="PhotoGalleries.Domain.GalleryType, PhotoGalleryDomain" table="REF_GalleryType">
    <id name="GalleryTypeId">
      <generator class="assigned" />
    </id>
    <property name="GalleryTypeName" type="string" column="GalleryTypeName" length="30" not-null="true" />
    <property name="CreateDate" type="timestamp" column="CreateDate" />
    <property name="UpdateDate" type="timestamp" column="UpdateDate" />
    <property name="CreateUser" type="string" column="CreateUser" length="30" />
    <property name="UpdateUser" type="string" column="UpdateUser" length="30" />
  </class>
</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():
Code:
public List<Slide> FindAllSlidesByKeyword(Gallery gallery, KeywordCategory keywordCategory, Keyword keyword)
        {
            List<Slide> slides = null;

            DetachedCriteria criterionGallery = DetachedCriteria.For(typeof(Gallery));
            criterionGallery.Add(Expression.Eq("GalleryId", gallery.ID));

            DetachedCriteria criterionAlbum = criterionGallery.CreateCriteria("Albums");
            DetachedCriteria criterionSlide = criterionAlbum.CreateCriteria("Slides");
            DetachedCriteria criterionKeywordCategorySlots = criterionSlide.CreateCriteria("KeywordCategorySlots");
           
            DetachedCriteria criterionKeywordCategory = criterionKeywordCategorySlots.CreateCriteria("KeywordCategory");
            DetachedCriteria criterionKeyword = criterionKeywordCategorySlots.CreateCriteria("Keyword");
           
            criterionKeywordCategory.Add(Expression.Eq("KeywordCategoryId", keywordCategory.KeywordCategoryId));
            criterionKeyword.Add(Expression.Eq("KeywordId", keyword.KeywordId));

            slides = this.FindByCriteria(criterionSlide);
            return slides;

        }



Name and version of the database you are using: MSSQL2005

The generated SQL (show_sql=true):
Code:
SELECT kcs.*
FROM
Gallery this_ left outer join REF_GalleryType gallerytyp7_ on this_.GalleryId=gallerytyp7_.GalleryTypeId
inner join Album album1_ on this_.GalleryId=album1_.GalleryId
inner join Slide slide2_ on album1_.AlbumId=slide2_.AlbumId
inner join KeywordCategorySlot kcs on slide2_.SlideId=kcs.SlideId
-- PROBLEM 1: kcs.KeywordCategorySlotId should be kcs.KeywordId
inner join Keyword k on kcs.KeywordCategorySlotId=k.KeywordId
-- PROBLEM 2: kcs.KeywordCategorySlotId should be kcs.KeywordCategoryId
inner join KeywordCategory kc on kcs.KeywordCategorySlotId=kc.KeywordCategoryId

WHERE
this_.GalleryId = 2
and kc.KeywordCategoryId = 1
and k.KeywordId = 1




[/code]
Top
 Profile  
 
dturkel
 Post subject: My Fault! Bad Mapping
PostPosted: Tue Sep 04, 2007 9:57 pm 
Newbie

Joined: Wed Oct 12, 2005 3:59 pm
Posts: 12
I kept plugging away on this, and found that I was incorrectly using one-to-one instead of many-to-one for my relationships.

I have a hard time "reversing" my thinking between the relationships between my actual entities versus the Hibernate expects them.

Additionally, I had to change around the constuction of my Criteria to start with the "Slide" entity.

Thanks for looking,

Dave
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 2 posts ] 

Board index » Hibernate & Java Persistence » NHibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.