I have a map. The map has a composite-map-key. I need to do a query against a part of the map-key. In my case The composit-map-key has 3 different columns. I want to query against 2 of them. See below. But is this possible at all?
Hibernate accepts the query but does not generate valid SQL.
Suggestions?
Hibernate version: 3.2.5ga
Mapping documents:
<class name="com.amplafi.core.BroadcastEnvelope" table="BROADCAST_ENVELOPES" batch-size="10">
<id name="entityId" column="ID">
<generator class="native"/>
</id>
<property name="createTime" column="CREATE_TIME" not-null="true" access="field"/>
<property name="lastUpdate" column="LAST_UPDATE" not-null="true" access="field"/>
<many-to-one name="receivingMessageSourcePoint" column="RECEIVING_POINT" not-null="true"
class="com.amplafi.core.messagehandling.MessageSourcePointImpl" access="field"
foreign-key="FK_BROADCAST_ENVELOPES__RECEIVING_MESSAGE_SOURCE_POINT"/>
<property name="currentStatus" column="CURRENT_STATUS" access="field" length="3">
<type name="com.amplafi.core.persistence.usertypes.PersistedEnumType">
<param name="enumClass">com.amplafi.core.EnvelopeStatus</param>
</type>
</property>
<property name="privateComments" column="PRIVATE_COMMENTS" length="400" access="field"/>
<many-to-one name="message" column="MESSAGE" cascade="persist,merge,save-update"
not-null="true" class="com.amplafi.core.BroadcastMessage" access="field"
foreign-key="FK_BROADCAST_ENVELOPES__MESSAGE"/>
<many-to-one name="substitutedMessage" column="SUB_MESSAGE"
cascade="persist,merge,save-update" not-null="false"
class="com.amplafi.core.BroadcastMessage" access="field"
foreign-key="FK_BROADCAST_ENVELOPES__SUBSTITUTED_MESSAGE"/>
<set name="topics" access="field" table="BROADCAST_ENVELOPES_TOPICS">
<key column="ENVELOPE" foreign-key="FK_BROADCAST_ENVELOPES_TOPICS__ENVELOPE"/>
<many-to-many class="com.amplafi.core.messagehandling.BroadcastTopic" column="TOPIC"
foreign-key="FK_BROADCAST_ENVELOPES_TOPICS__TOPIC"/>
</set>
<component name="tagSet" access="field">
<set name="selected" access="field" table="BROADCAST_ENVELOPES_TAGS">
<key column="ENVELOPE" foreign-key="FK_BROADCAST_ENVELOPES_TAGS__ENVELOPE"/>
<many-to-many class="com.amplafi.core.Tag" column="TAG"
foreign-key="FK_BROADCAST_ENVELOPES_TAGS__TAG" />
</set>
</component>
</class>
<class name="com.amplafi.core.BroadcastMessage" table="BROADCAST_MESSAGES" batch-size="10">
<id name="entityId" column="ID">
<generator class="native"/>
</id>
<many-to-one name="baseMessage" column="BASE_MESSAGE" access="field"
foreign-key="FK_BROADCAST_MESSAGES__BASE_MESSAGE"/>
<many-to-one name="messageSynonymGroup" column="SYNONYM_GROUP" access="field"
cascade="persist,save-update,merge"
foreign-key="FK_BROADCAST_MESSAGES__MESSAGE_SYNONYM_GROUP" />
<property name="messageType" column="MESSAGE_TYPE" length="3" access="field">
<type name="com.amplafi.core.persistence.usertypes.PersistedEnumType">
<param name="enumClass">com.amplafi.core.BroadcastMessageType</param>
</type>
</property>
<many-to-one name="originatingEnvelope" column="ORIG_ENV" access="field"
foreign-key="FK_BROADCAST_MESSAGES__ORIGINAL_ENVELOPE"/>
<many-to-one name="location" column="LOCATION" access="field" cascade="persist,save-update,merge"
class="com.amplafi.core.LocationImpl" foreign-key="FK_BROADCAST_MESSAGES__LOCATION"/>
<component name="calendarInfo">
<property name="startDate" column="START_TS" access="field"/>
<property name="endDate" column="END_TS" access="field"/>
<property name="validStartTimeOfDay" column="VALID_START_TOD" access="field"/>
<property name="validEndTimeOfDay" column="VALID_END_TOD" access="field"/>
<property name="timeZoneId" column="TIME_ZONE_ID" access="field" length="50"/>
<!-- offset in minutes from GMT -->
<property name="timeZoneOffset" column="TIME_ZONE_OFFSET" access="field"/>
</component>
<component name="keyValueMap" access="field">
<map name="parameters" access="field" table="MESSAGE_PARAMETERS"
cascade="all-delete-orphan" lazy="true" sort="natural">
<key column="BROADCAST_MESSAGE" not-null="true"
foreign-key="FK_MESSAGE_PARAMETERS__BROADCAST_MESSAGE" />
<composite-map-key class="com.amplafi.core.messagehandling.KeyValueMapKey">
<key-property name="namespace" column="NAMESPACEKEY" length="100" access="field"/>
<key-property name="key" column="SUBKEY" length="100" access="field"/>
<key-property name="index" column="IDXKEY" access="field"/>
</composite-map-key>
<element type="string" column="VALUE" length="512"/>
</map>
</component>
<many-to-one name="contactInformation" column="CONTACT_INFO" access="field"
cascade="persist,save-update,merge"
foreign-key="FK_BROADCAST_MESSAGES__CONTACT_INFORMATION"/>
<property name="deadline" column="DEADLINE_DATE" access="field" />
<property name="publishDate" column="PUB_DATE" access="field" />
<property name="latestDate" column="LATEST_DATE"/>
<property name="externalLastUpdate" column="LAST_UPDATE_EXTERN" access="field"/>
<property name="lastUpdate" column="LAST_UPDATE" access="field" />
<many-to-one name="language" cascade="all" column="LANGUAGE" access="field"
foreign-key="FK_BROADCAST_MESSAGES__LANGUAGE"/>
</class>
<query name="BroadcastProvider.getMessagesByParams"><![CDATA[
select be from BroadcastEnvelope be
left join be.message.keyValueMap.parameters as param where
param.index(namespace) = :namespace AND
param.index(key) = :key AND param=:value
and be.receivingMessageSourcePoint.owner = :broadcastProvider
]]></query>
</hibernate-mapping>
[/b]
Name and version of the database you are using:mysql 5.0.27
[b]The generated SQL (show_sql=true):
select broadcaste0_.ID as ID18_, broadcaste0_.CREATE_TIME as CREATE2_18_, broadcaste0_.LAST_UPDATE as LAST3_18_, broadcaste0_.RECEIVING_POINT as RECEIVING4_18_, broadcaste0_.CURRENT_STATUS as CURRENT5_18_, broadcaste0_.PRIVATE_COMMENTS as PRIVATE6_18_, broadcaste0_.MESSAGE as MESSAGE18_, broadcaste0_.SUB_MESSAGE as SUB8_18_ from BROADCAST_ENVELOPES broadcaste0_ left outer join BROADCAST_MESSAGES broadcastm1_ on broadcaste0_.MESSAGE=broadcastm1_.ID left outer join MESSAGE_PARAMETERS parameters2_ on broadcastm1_.ID=parameters2_.BROADCAST_MESSAGE where param.index ( namespace ) ='test'
|