Query by map key and value

erik.jogi · **Posted:** Wed Mar 24, 2004 5:36 pm

Hello,

I have a Person entity. Each Person can be part of several Organisations and in every Organisation the Person has a specific Role.

I have this set up so that a Person has a (Sorted)Map with an Organisation as the key and a Role as the value. Organisation is another entity and Role is a PersistentEnum.

It is mapped like this:

Code:

<hibernate-mapping>
  <class name="erik.koduke.entities.Person" table="eraisik">
    <id name="id" unsaved-value="0">
      <generator class="identity"/>
    </id>
    <property name="firstName" column="eesnimi"/>
    <property name="lastName" column="perekonnanimi"/>
    ...
    <map name="roles" table="role_map" cascade="all" sort="erik.koduke.entities.OrganisationComparator">
      <key column="personId"/>
      <index-many-to-many class="erik.koduke.entities.Organisation" column="organisationId"/>
      <element column="role" type="erik.koduke.enums.Role"/>
    </map>
  </class>
</hibernate-mapping>

Now I have to get a list of all persons that are in a specific Role in a specific Organisation. For this I came up with this query:

Code:

from Person person where person.roles[?]=?

The actual call looks like this:

Code:

      List allInRole = hibernateSession.find(
        "from Person person where person.roles[?]=?",
        new Object[] {user.getSelectedOrganisation(), role},
        new Type[] {Hibernate.entity(Organisation.class), Hibernate.enum(Role.class)});

The problem is that this query always returns 0 rows.

So I looked at hibernate log:

Code:

26:37,140 DEBUG QueryTranslator - HQL: from erik.koduke.entities.Person person where person.roles[?]=?
26:37,140 DEBUG QueryTranslator - SQL: select person0_.id as id, person0_.eesnimi as eesnimi, person0_.perekonnanimi as perekonn3_ from eraisik person0_, role_map roles1_ where (roles1_.role=?  and person0_.id=roles1_.personId and roles1_.organisationId = ?)
26:37,140 DEBUG BatcherImpl - about to open: 0 open PreparedStatements, 0 open ResultSets
26:37,140 DEBUG SQL - select person0_.id as id, person0_.eesnimi as eesnimi, person0_.perekonnanimi as perekonn3_ from eraisik person0_, role_map roles1_ where (roles1_.role=?  and person0_.id=roles1_.personId and roles1_.organisationId = ?)
26:37,140 DEBUG BatcherImpl - preparing statement
26:37,140 DEBUG Cascades - id unsaved-value: 0
26:37,140 DEBUG LongType - binding '3' to parameter: 1
26:37,140 DEBUG PersistentEnumType - binding '2' to parameter: 2
26:37,140 DEBUG Loader - processing result set
26:37,140 DEBUG Loader - done processing result set (0 rows)
26:37,140 DEBUG BatcherImpl - done closing: 0 open PreparedStatements, 0 open ResultSets
26:37,140 DEBUG BatcherImpl - closing statement
26:37,140 DEBUG Loader - total objects hydrated: 0

The select itself looked just fine. I ran it in phpMyAdmin and everything looked ok - it returned the data I was expecting. But then I looked at the lines after the SQL statement. The Organisation has an ID of type long and the Role is a PersistentEnum. So the LongType should bind '3' to parameter: 2 and PersistentEnumType should bind '2' to parameter: 1. At least it looks like this from the SQL statement !?

Am I doing something wrong or have I discovered a bug?