PAGES
====
ID
NAME

PAGE_CHILDREN
==========
PARENT_ID
CHILD_ID
PAGE_ORDER

<set name="children" lazy="true" table="PAGE_CHILDREN" order-by="PAGE_ORDER" fetch="join" inverse="false">
   <key column="FK_PARENT_PAGE_ID" />           
   <composite-element class="ChildPage">
      <property name="order" column="PAGE_ORDER" type="integer" />
      <many-to-one name="page" class="Page" column="FK_CHILD_PAGE_ID" lazy="false" />
   </composite-element>
</set>

select cp.page from Page p join p.children as cp
   where p.id = :id
   and upper( replace( cp.page.name, ' ', '_' ) ) = upper( :name )
   and cp.page.enabled = 1

<hibernate-mapping package="model.hibernate" >
    <class name="Page" table="PAGES">

        <id name="id" type="long">
            <column name="PK_PAGE_ID" />
            <generator class="native" />
        </id>

      <many-to-one name="site" class="Site" column="FK_SITE_ID" />

      <many-to-one name="type" class="PageType" column="FK_PAGE_TYPE_ID" />

        <property name="name" type="string">
            <column name="PAGE_NAME" length="50" not-null="true" />
        </property>

        <property name="title" type="string">
            <column name="PAGE_TITLE" length="150" not-null="true" />
        </property>

        <property name="metaKeywords" type="string">
            <column name="META_KEYWORDS" length="65535" not-null="true" />
        </property>

        <property name="metaDescription" type="string">
            <column name="META_DESCRIPTION" length="65535" not-null="true" />
        </property>

        <property name="bodyText" type="string">
            <column name="BODY_TEXT" length="65535" not-null="true" />
        </property>

        <property name="description" type="string">
            <column name="DESCRIPTION" length="250" not-null="true" />
        </property>

        <property name="navLevel" type="integer">
            <column name="NAV_LEVEL" not-null="true" />
        </property>

        <property name="thumbnailImage" type="string">
            <column name="THUMBNAIL_IMAGE" length="150" />
        </property>

        <property name="enabled" type="boolean">
            <column name="PAGE_ENABLED" not-null="true" />
        </property>

        <property name="createdDate" type="timestamp">
            <column name="CREATED_DATE" length="0" />
        </property>
       
      <one-to-one name="product"
         class="Product"
         property-ref="page"
         cascade="all"
         lazy="false"
         fetch="join"
      />

        <set name="children" lazy="true" table="PAGE_CHILDREN" order-by="PAGE_ORDER" fetch="join" inverse="false">
           <key column="FK_PARENT_PAGE_ID" />           
           <composite-element class="ChildPage">
              <property name="order" column="PAGE_ORDER" type="integer" />
              <many-to-one name="page" class="Page" column="FK_CHILD_PAGE_ID" lazy="false" insert="false"/>
           </composite-element>
        </set>

        <set name="links" lazy="true" table="PAGE_LINKED_PAGES" order-by="PAGE_ORDER" fetch="join" >
           <key column="FK_PARENT_PAGE_ID" />
           <composite-element class="LinkedPage">
              <property name="order" column="PAGE_ORDER" type="integer" />
              <many-to-one name="page" class="Page" column="FK_LINK_PAGE_ID" />
           </composite-element>
        </set>

    </class>
   
   
    <query name="getHomePage">
       <![CDATA[
          from Page p
             where p.site = :site
             and p.type = :type
             and p.enabled = 1
       ]]>
    </query>
   
    <query name="getPage">
       <![CDATA[
          from Page p
             where p.site = :site
             and upper( replace( p.name, ' ', '_' ) ) = upper( :name )
             and p.enabled = 1
       ]]>
    </query>
   
    <query name="getChildPage">
       <![CDATA[
          select cp.page from Page p join p.children as cp
             where p.site = :site
             and p.id = :id
             and upper( replace( cp.page.name, ' ', '_' ) ) = upper( :name )
             and cp.page.enabled = 1
       ]]>
    </query>
   
</hibernate-mapping>

public class Page implements Serializable {

     private long id;
     private Date createdDate;
     private Site site;
     private PageType type;
     private String name;
     private String title;
     private String metaKeywords;
     private String metaDescription;
     private String bodyText;
     private String description;
     private Integer navLevel;
     private String thumbnailImage;
     private boolean enabled;
     private Set<ChildPage> children = new HashSet<ChildPage>();
     private Set<LinkedPage> links = new HashSet<LinkedPage>();
     private Product product;


    // Constructors

    /** default constructor */
    public Page() {
    }

   
    // Property accessors
    public long getId() {
       return id;
    }
    public void setId( long _id ) {
       id = _id;
    }
   
    public Date getCreatedDate() {
       return createdDate;
    }
    public void setCreatedDate( Date _createdDate ) {
       createdDate = _createdDate;
    }
   
    public Site getSite() {
        return this.site;
    }
    public void setSite(Site site) {
        this.site = site;
    }

   
    public PageType getType() {
        return this.type;
    }
    public void setType(PageType type) {
        this.type = type;
    }

   
    public String getName() {
        return this.name;
    }
    public void setName(String _name) {
        this.name = _name;
    }

   
    public String getNavigationName() {
        return getName().replaceAll( " ", "_" );
    }


    public String getFullNavigationName() {
       //TODO externalise ".html"
        return getNavigationName() + ".html";
    }

   
    public String getTitle() {
        return this.title;
    }
    public void setTitle(String ritle) {
        this.title = ritle;
    }

   
    public String getMetaKeywords() {
        return this.metaKeywords;
    }
    public void setMetaKeywords(String metaKeywords) {
        this.metaKeywords = metaKeywords;
    }

   
    public String getMetaDescription() {
        return this.metaDescription;
    }
    public void setMetaDescription(String metaDescription) {
        this.metaDescription = metaDescription;
    }

   
    public String getBodyText() {
        return this.bodyText;
    }
    public void setBodyText(String bodyText) {
        this.bodyText = bodyText;
    }

   
    public String getDescription() {
        return this.description;
    }
    public void setDescription(String description) {
        this.description = description;
    }

   
    public Integer getNavLevel() {
        return this.navLevel;
    }
    public void setNavLevel(Integer navLevel) {
        this.navLevel = navLevel;
    }

   
    public String getThumbnailImage() {
        return this.thumbnailImage;
    }
    public void setThumbnailImage(String thumbnailImage) {
        this.thumbnailImage = thumbnailImage;
    }


    public boolean getEnabled() {
        return this.enabled;
    }
    public boolean isEnabled() {
        return getEnabled();
    }
    public boolean getIsEnabled() {
        return getEnabled();
    }
    public void setEnabled(boolean enabled) {
        this.enabled = enabled;
    }

   
    public Set<ChildPage> getChildren() {
        return this.children;
    }
    public void setChildren(Set<ChildPage> children) {
        this.children = children;
    }

   
    public Set<LinkedPage> getLinks() {
        return this.links;
    }
    public void setLinks(Set<LinkedPage> links) {
        this.links = links;
    }

   
    public String toString() {
       return this.getClass().getName() + " [ ID: " + getId() + ", Name: " + name + ", Title: " + title + ", Type: " + type + " ]";
    }


   public boolean hasChildren() {
      return children != null && ! children.isEmpty();
   }

   
   public boolean hasLinkedPages() {
      return links != null && ! links.isEmpty();
   }


   public Product getProduct() {
      return product;
   }
   public void setProduct( Product _product ) {
      product = _product;
   }


   public boolean isHomePage() {
      return type == PageTypes.PAGE_TYPES.HOME.type();
   }
   public boolean getIsHomePage() {
      return isHomePage();
   }
   

   /**
    * @param _child
    * @param _order
    */
   public void addChild( Page _child, int _order ) {
      children.add( new ChildPage( _order, _child ) );
   }

}

SessionFactory factory = new Configuration().configure(_configFile).buildSessionFactory();
Session sess = factory.getCurrentSession();
Transaction t = sess.getTransaction();
if (!t.isActive()) {
   t.begin();
}
Query query = sess.getNamedQuery( "getChildPage" );
addQueryParameters( query, _params );
return query.uniqueResult();

Hibernate: delete from PAGE_CHILDREN where FK_PARENT_PAGE_ID=? and PAGE_ORDER=? and FK_CHILD_PAGE_ID=?
Hibernate: delete from PAGE_CHILDREN where FK_PARENT_PAGE_ID=? and PAGE_ORDER=? and FK_CHILD_PAGE_ID=?
Hibernate: delete from PAGE_CHILDREN where FK_PARENT_PAGE_ID=? and PAGE_ORDER=? and FK_CHILD_PAGE_ID=?
Hibernate: insert into PAGE_CHILDREN (FK_PARENT_PAGE_ID, PAGE_ORDER, FK_CHILD_PAGE_ID) values (?, ?, ?)
Hibernate: insert into PAGE_CHILDREN (FK_PARENT_PAGE_ID, PAGE_ORDER, FK_CHILD_PAGE_ID) values (?, ?, ?)
Hibernate: insert into PAGE_CHILDREN (FK_PARENT_PAGE_ID, PAGE_ORDER, FK_CHILD_PAGE_ID) values (?, ?, ?)
Hibernate: delete from PAGE_CHILDREN where FK_PARENT_PAGE_ID=? and PAGE_ORDER=? and FK_CHILD_PAGE_ID=?
Hibernate: delete from PAGE_CHILDREN where FK_PARENT_PAGE_ID=? and PAGE_ORDER=? and FK_CHILD_PAGE_ID=?
Hibernate: insert into PAGE_CHILDREN (FK_PARENT_PAGE_ID, PAGE_ORDER, FK_CHILD_PAGE_ID) values (?, ?, ?)
Hibernate: insert into PAGE_CHILDREN (FK_PARENT_PAGE_ID, PAGE_ORDER, FK_CHILD_PAGE_ID) values (?, ?, ?)
Hibernate: select page2_.PK_PAGE_ID as PK1_12_, page2_.FK_SITE_ID as FK2_12_, page2_.FK_PAGE_TYPE_ID as FK3_12_, page2_.PAGE_NAME as PAGE4_12_, page2_.PAGE_TITLE as PAGE5_12_, page2_.META_KEYWORDS as META6_12_, page2_.META_DESCRIPTION as META7_12_, page2_.BODY_TEXT as BODY8_12_, page2_.DESCRIPTION as DESCRIPT9_12_, page2_.NAV_LEVEL as NAV10_12_, page2_.THUMBNAIL_IMAGE as THUMBNAIL11_12_, page2_.PAGE_ENABLED as PAGE12_12_, page2_.CREATED_DATE as CREATED13_12_ from dazl.PAGES page0_ inner join PAGE_CHILDREN children1_ on page0_.PK_PAGE_ID=children1_.FK_PARENT_PAGE_ID inner join dazl.PAGES page2_ on children1_.FK_CHILD_PAGE_ID=page2_.PK_PAGE_ID where page0_.FK_SITE_ID=? and page0_.PK_PAGE_ID=? and upper(replace(page2_.PAGE_NAME, ' ', '_'))=upper(?) and page2_.PAGE_ENABLED=1