<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping SYSTEM "hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.api.common">
   <class name="Address" table="USR_ADDRESS" mutable="true">

      <id name="id" column="ROW_ID">
         <generator class="sequence">
            <param name="sequence">ADDRESS_ROW_ID</param>
         </generator>
      </id>
      <property name="addressType" column="TYPE">
         <type name="com.util.HibernateUserType">
            <param name="targetClass">com.api.common.enumeration.AddressType</param>
         </type>
      </property>

      <component name="period" class="com.nyse.end.util.temporal.Period">
         <property name="startDate" column="START_DATE"></property>
         <property name="stopDate" column="STOP_DATE"></property>
      </component>

      <property name="city" column="CITY"></property>
      <property name="country" column="COUNTRY"></property>
      <property name="email" column="EMAIL"></property>
      <property name="fax" column="FAX"></property>
      <property name="fullName" column="FULL_NAME"></property>
      <property name="phone" column="PHONE"></property>
      <property name="room" column="ROOM"></property>
      <property name="state" column="STATE"></property>
      <property name="webSite" column="WEBSITE"></property>
      <property name="zip" column="ZIP"></property>
      <property name="address" column="address1"></property>
      <property name="address2" column="address2"></property>
      <property name="addressId" column="ADDRESS_ID"></property>      
      
      <many-to-one name="user" class="User" lazy="proxy" column="USER_ID"></many-to-one>

   </class>

   <query name="Address.findAllByUser">
      <![CDATA[from Address a where a.user.userId=?  and a.user.userType.id=? order by a.period.startDate asc]]>
   </query>

   <query name="Address.findLastByPK">
      <![CDATA[from Address a where a.user.userId=? and a.addressType.id=?  order by a.period.startDate desc]]>
   </query>

   <query name="Address.findAllByPKAndPeriod" cacheable="true" >
      <![CDATA[from Address a where a.user.userId=? and a.addressType.id=? and
      (((? between nvl(a.period.startDate,to_date('1000-01-01', 'yyyy-mm-dd')) and nvl(a.period.stopDate,to_date('5000-01-01', 'yyyy-mm-dd'))) or
      (? between nvl(a.period.startDate,to_date('1000-01-01', 'yyyy-mm-dd')) and nvl(a.period.stopDate,to_date('5000-01-01', 'yyyy-mm-dd'))))
       or ((nvl(a.period.startDate,to_date('1000-01-01', 'yyyy-mm-dd')) between ? and ?)
       or (nvl(a.period.stopDate,to_date('5000-01-01', 'yyyy-mm-dd')) between ? and ?)))
       order by a.period.startDate asc]]>
   </query>

   <query name="Address.findByPKAndDate" cacheable="true">
      <![CDATA[from Address a where a.user.userId=? and a.addressType.id=?  and
       ? between nvl(a.period.startDate, to_date('1000-01-01', 'yyyy-mm-dd'))
         and nvl(a.period.stopDate, to_date('5000-01-01', 'yyyy-mm-dd'))
       order by a.period.startDate asc]]>
   </query>

   <query name="Address.findByUserAndDate" cacheable="true">
      <![CDATA[from Address a where a.user=:user and
      :date between nvl(a.period.startDate, to_date('1000-01-01', 'yyyy-mm-dd'))
         and nvl(a.period.stopDate, to_date('5000-01-01', 'yyyy-mm-dd'))
       order by a.period.startDate asc]]>
   </query>

</hibernate-mapping>

select address0_.ROW_ID as ROW1_19_,
address0_.TYPE as TYPE19_,
address0_.START_DATE as START3_19_,
address0_.STOP_DATE as STOP4_19_,
address0_.CITY as CITY19_,
address0_.COUNTRY as COUNTRY19_,
address0_.EMAIL as EMAIL19_,
address0_.FAX as FAX19_,
address0_.FULL_NAME as FULL9_19_,
address0_.PHONE as PHONE19_,
address0_.ROOM as ROOM19_,
address0_.STATE as STATE19_,
address0_.WEBSITE as WEBSITE19_,
address0_.ZIP as ZIP19_,
address0_.address1 as address15_19_,
address0_.address2 as address16_19_,
address0_.ADDRESS_ID as ADDRESS17_19_,
address0_.USER_ID as USER18_19_ from USR_ADDRESS address0_ where address0_.USER_ID=? and address0_.ROW_ID=? and (? between nvl(address0_.START_DATE,
to_date('1000-01-01',
'yyyy-mm-dd')) and nvl(address0_.STOP_DATE,
to_date('5000-01-01',
'yyyy-mm-dd')) or ? between nvl(address0_.START_DATE,
to_date('1000-01-01',
'yyyy-mm-dd')) and nvl(address0_.STOP_DATE,
to_date('5000-01-01',
'yyyy-mm-dd')) or nvl(address0_.START_DATE,
to_date('1000-01-01',
'yyyy-mm-dd')) between ? and ? or nvl(address0_.STOP_DATE,
to_date('5000-01-01',
'yyyy-mm-dd')) between ? and ?) order by address0_.START_DATE asc|select address0_.ROW_ID as ROW1_19_,
address0_.TYPE as TYPE19_,
address0_.START_DATE as START3_19_,
address0_.STOP_DATE as STOP4_19_,
address0_.CITY as CITY19_,
address0_.COUNTRY as COUNTRY19_,
address0_.EMAIL as EMAIL19_,
address0_.FAX as FAX19_,
address0_.FULL_NAME as FULL9_19_,
address0_.PHONE as PHONE19_,
address0_.ROOM as ROOM19_,
address0_.STATE as STATE19_,
address0_.WEBSITE as WEBSITE19_,
address0_.ZIP as ZIP19_,
address0_.address1 as address15_19_,
address0_.address2 as address16_19_,
address0_.ADDRESS_ID as ADDRESS17_19_,
address0_.USER_ID as USER18_19_ from USR_ADDRESS address0_ where address0_.USER_ID=55494 and address0_.ROW_ID=2 and ('2006-11-01 00:00:00.0' between nvl(address0_.START_DATE,
to_date('1000-01-01',
'yyyy-mm-dd')) and nvl(address0_.STOP_DATE,
to_date('5000-01-01',
'yyyy-mm-dd')) or '5000-01-01 00:00:00.0' between nvl(address0_.START_DATE,
to_date('1000-01-01',
'yyyy-mm-dd')) and nvl(address0_.STOP_DATE,
to_date('5000-01-01',
'yyyy-mm-dd')) or nvl(address0_.START_DATE,
to_date('1000-01-01',
'yyyy-mm-dd')) between '2006-11-01 00:00:00.0' and '5000-01-01 00:00:00.0' or nvl(address0_.STOP_DATE,
to_date('5000-01-01',
'yyyy-mm-dd')) between '2006-11-01 00:00:00.0' and '5000-01-01 00:00:00.0') order by address0_.START_DATE asc