Problem querying for items stored in a hashmap

swinfough · **Joined:** Fri Sep 10, 2004 10:21 am **Posts:** 7

Greetings everyone. I'm at a loss right now as I'm stuck trying to query for a contact with a given phone number. I would think it's a pretty simple query but I'm stumped. I have a class called Contact that has a hash map for the various phone numbers the contact can have. The key in the hash map is an enumerated type that describes the phone number (i.e. business, personal, etc.). Is there a better way to map this relationship? I'm open for suggestions. I am at a loss as to the HQL calls to make to give me my desired results. I've listed what I'm trying right now and the exception I'm getting that is causing my confusion. I don't understand why I'd get this type of error when querying. I don't have any problems persisting the contacts and phone numbers when saving!?!?! If anyone can shed some light on my problem, I would be greatly appreciated.

Code:

phones = session.createCriteria(Contact.class)
                          .createAlias("phoneNumbers", "phone")
                          .add( Expression.like("phone.phoneNumber", phoneNumber, MatchMode.ANYWHERE ) )
                                               .list();

Debug level Hibernate log excerpt:
net.sf.hibernate.MappingException: collection was not an association: com.ourcompany.system.Contact.phoneNumbers

Hibernate version:
2.1.6

Mapping documents:
<hibernate-mapping package="com.ourcompany.system">
<class name="com.ourcompany.system.Contact" table="contacts" >

<id name="id" column="id">
<generator class="increment"/>
</id>


<property name="fullName" column="full_name" type="string" length="255" not-null="true" access="field"/>
<property name="firstName" column="first_name" type="string" length="128" not-null="true" access="field"/>
<property name="middleName" column="middle_name" type="string" length="128" not-null="false" access="field"/>
<property name="lastName" column="last_name" type="string" length="128" not-null="true" access="field"/>

<map name="phoneNumbers" table="phone_numbers">
<key column="contact_id"/>
<composite-index class="com.ourcompany.system.PhoneNumberType">
<key-property name="value" column="phone_number_type" type="integer" access="field"/>
</composite-index>
<composite-element class="com.ourcompany.system.PhoneNumber">
<property name="phoneNumber" column="phone_number" type="string" length="32" not-null="true" access="field"/>
<property name="countryCode" column="country_code" type="string" length="32" not-null="true" access="field"/>
<property name="areaCode" column="area_code" type="string" length="32" not-null="true" access="field"/>
<property name="extension" column="extension" type="string" length="32" not-null="true" access="field"/>
</composite-element>
</map>

</class>

Name and version of the database you are using:
MySQL, v. 4.1.5 gamma

Here are my classes...

Code:

/** Contact class **/
public class Contact {

    private String salutation = "";
    private String fullName = ""; 
    private String firstName = "";
    private String middleName = "";
    private String lastName = "";
    private String postName = "";
    private String title = "";

    private Map phoneNumbers = new HashMap();

    public PhoneNumber getBusinessPhoneNumber() {
        return (PhoneNumber) this.phoneNumbers.get(PhoneNumberType.BUSINESS_PHONE_NUMBER);
    }
}

/** Phone number class **/
public class PhoneNumber {

    private String countryCode = "";
    private String areaCode = "";
    private String phoneNumber = "";
    private String extension = "";

    /** Default constructor, no arguments */
    public PhoneNumber() {    }
}

/** Phone number type class**/
public class PhoneNumberType {

    private int value;

    /**A business type phone number */
    public static final PhoneNumberType BUSINESS_PHONE_NUMBER = new PhoneNumberType(1);
    ...
    /** constructor **/
    public PhoneNumberType(int value) {
        this.value = value;
    }
}

gavin · **Posted:** Tue Nov 23, 2004 12:48 pm

Querying inside collections of values is not supported by the Criteria API. Use HQL.

swinfough · **Joined:** Fri Sep 10, 2004 10:21 am **Posts:** 7

gavin wrote:

Querying inside collections of values is not supported by the Criteria API. Use HQL.

I appreciate your direction. I'm still at a loss with querying when there's a collection such as I have and the key is an enumerated type. I haven't been able to find any samples in the Hiberante in Action book similar to this. Could you post a sample for me to try.

Thanks in advance.
Steve

christian · **Posted:** Tue Nov 23, 2004 3:24 pm

The reference documentation also has many related examples.

swinfough · **Joined:** Fri Sep 10, 2004 10:21 am **Posts:** 7

christian wrote:

The reference documentation also has many related examples.

Hi Christian,
Which reference documentation are you referring to? The Hibernate Reference? Hibernate in Action? or something other? If you are talking about the first two I have combed them over MANY times but I still don't see any samples of HQL querying a collection (not a set or list, a map) that uses an enumerated type as the key; or any other key. If you can be a bit more specific (ie. doc name, page reference, sample reference, etc.) so I can find it.

Thanks.

Steve

christian · **Posted:** Tue Nov 23, 2004 3:52 pm

HQL has collection functions, elements(), indexes(), etc. You can even access indexed collections directly by index.

swinfough · **Joined:** Fri Sep 10, 2004 10:21 am **Posts:** 7

Okay, I have a solution that works but I'm curious as to how efficient this solution is, especially when the number of contacts and phone numbers is large. Here it is...

Code:

                        Query query = session.createQuery(
                                "select distinct c from Contact c " +
                                "join c.phoneNumbers phones " +
                                "where phones.phoneNumber = :phoneNumber");
                        query.setString("phoneNumber", phoneNumber);

christian · **Posted:** Tue Nov 23, 2004 5:43 pm

The best way to understand the performance characteristics is to look at the generated SQL and the execution plan in your database system. Use "SQL Tuning" from O'Reilly to understand and optimize both.

linuskamb · **Posted:** Tue Jan 04, 2005 2:21 pm

gavin wrote:

Querying inside collections of values is not supported by the Criteria API. Use HQL.

Will this change in 3.0?

I have a cool db query module that is built at runtime based on the mapping files, but it relies on the Criteria API and will be sorely hampered if I can't query inside maps.

Thanks,
Linus