Need help with mapping and HQL

mikemil · **Joined:** Fri Oct 28, 2005 10:54 pm **Posts:** 12

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 2.1.2

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:SQLServer 2000

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

I am trying to create a mapping document and HQL to improve the performance of an existing query that uses a couple existing objects where a very small subset of the full object graph is needed.

We have a location table that include a primary key, name, manager and some other fields. We also have a (many-to-many) join table for phone numbers. Locations can have multiple numbers but for the query in question, we are only interested in the 'primary' phone number row. The existing query gets a LocationBean that includes entirely too much data for the search screeen that displays on the location name, manager, primary area code and primary phone number.

I have created a 'LocationSummaryBean' that maps the (id, name and manager) fields from the location table into the object. In this object I also want a string field for the primary area code and string field for the primary phone number.

How do I define the mapping for these fields that exists in a different table? Or can't I?

We use XDoclet tags for our mapping.

Initially I tried setting defining a property for each field without the column specified but that just generated the column attribute with same value as the name attribute, which got an error finding the areaCode column. Then I removed the hibernate xdoclet property tag.

Then the next question is "how do I write the HQL (or mapping?) to get only the primary phone number row instead of getting a set of phone numbers?

The basic idea of the sql is like this:

Code:

select l.party_id, l.location_number, p.area_code, p.phone_number
  from location l
  left outer join phone_number p on l.party_id = p.party_id
 where p.principle = 1

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Can you upgrade to Hibernate3? The "select new" syntax would help you here.

For the query, the (Hibernate2) HQL is

Code:

select l.party_id, l.location_number, p.area_code, p.phone_number
  from location l
  left join l.PhoneNumber p
  where p.principle = 1

If you had hibernate 3, you could get rid of your LocationSummaryBean mapping, and have hibernate create your bean class on the fly:

Code:

select new LocationSummaryBean(l.party_id, l.location_number, p.area_code, p.phone_number)
  from location l
  left join l.PhoneNumber p
  where p.principle = 1

mikemil · **Joined:** Fri Oct 28, 2005 10:54 pm **Posts:** 12

Thanks for the response - but we are not able to upgrade to H3 as of yet.

I was able to get something close running: the main bean returned contains a set of phone numbers rather than just the single primary phone number. I have ordered the set of phone numbers so that the primary gets sorted to the top and this appears to work - and still manages to return alot less data than our previous implementation:

LocationSearchSummaryBean mapping:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
   <class name="LocationSearchSummaryBean" table="LOCATION" dynamic-update="false" dynamic-insert="false">
      <id name="partyId" column="PARTY_ID" type="long" unsaved-value="0">
         <generator class="increment"></generator>
      </id>

      <property name="locationNumber" type="java.lang.String"
         update="true" insert="true" column="LOCATION_NUMBER" />

      <property name="managerName" type="java.lang.String"
         update="true" insert="true" column="MANAGER_NAME" />

      <set name="phoneNumbers" lazy="false" inverse="true"
         cascade="all-delete-orphan" sort="unsorted" order-by="PRINCIPLE DESC"
         outer-join="true">

         <key>
            <column name="PARTY_ID" />
         </key>

         <one-to-many
            class="LocationPhone" />
      </set>

      <!--
         To add non XDoclet property mappings, create a file named
         hibernate-properties-LocationSearchSummaryBean.xml
         containing the additional properties and place it in your merge dir.
      -->

   </class>

</hibernate-mapping>

LocationPhone mapping:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
   <class name="LocationPhone" table="PHONE_NUMBER" dynamic-update="false" dynamic-insert="false">
      <id name="phoneNumberId" column="PHONE_NUMBER_ID" type="long"
         unsaved-value="0">
         <generator class="increment"></generator>
      </id>
      <property name="areaCode" type="java.lang.String" update="true"
         insert="true" column="AREA_CODE" />
      <property name="phoneNumber" type="java.lang.String"
         update="true" insert="true" column="PHONE_NUMBER" />
      <!--
         To add non XDoclet property mappings, create a file named
         hibernate-properties-LocationPhone.xml
         containing the additional properties and place it in your merge dir.
      -->
   </class>
</hibernate-mapping>

I also set the outer-join=true for the set to generate the "outer join fetch" processing.

Any comments/suggestions are appreciated and thanks again for the response!

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

If you're getting HQL to return a mapped object, then it can't return part of that object; the LocationSearchSummaryBean is defined by its properties, including the full set of phone numbers. If you want to return a partial set of phone numbers, then you can't return a mapped entity. You have to return scalars, and build the bean yourself.

However, seeing as you're so close to getting it to work, there's not much point in completely overhauling your implementation. Just add 'where="principle=1"' to your <set> element, and then you'll have a set with just the single number that you want. Better solutions are possible, but there's no point in fixing what ain't broke.