[HQL] Select an element in a map attribute

sregg · **Joined:** Thu Apr 20, 2006 9:35 am **Posts:** 10

Hi,

I have a problem with HQL and querying a map attribute.

Here is my mapping file of a class Service.

Code:

<class name="com.eurecia.backoffice.Service" table="SERVICE_EA">

...

<map name="descr" table="SERVICE_DESCR_EA" cascade="all" lazy="false">
          <key>
             <column name="ID_SERVICE" not-null="true"/>
             <column name="ID_COMPANY" not-null="true"/>
          </key>
          <map-key column="LOCALE" type="java.lang.String" length="10"/>
          <element column="DESCR_SERVICE" type="java.lang.String" not-null="true" length="255"/>
</map>

Like you can see, this class contains a map attribute named descr

I would like to query this class et get the descr for the current locale.

So here is my query :

Code:

StringBuffer queryString = new StringBuffer();
      queryString.append("SELECT srv.id.idService, srv.id.idCompany, srv.type,srv.descr['"+session.getLocale()+"'], cny.descr");
      queryString.append(" FROM Service as srv, Company as cny");
      queryString.append(" WHERE srv.id.idCompany = cny.id");
      
      if( filters != null )
      {
         queryString.append(" AND srv.id.idService like '%"+filters.get("id.idService")+"%'");
         queryString.append(" AND cny.descr like '%"+filters.get("companyDescr")+"%'");
         queryString.append(" AND srv.type like '%"+filters.get("type")+"%'");
         queryString.append(" AND srv.descr['"+session.getLocale()+"'] like '%"+filters.get("descr")+"%'");
      }

My problem is in the SELECT part :

Code:

srv.descr['"+session.getLocale()+"']

. I think this expression can't be used in a SELECT section.
But it works in the WHERE section :

Code:

AND srv.descr['"+session.getLocale()+"'] like '%"+filters.get("descr")+"%

How can I select on element of a map in the SELECT section ?

Thank you for your response.

Hibernate version: 3.0.5

axismundi · **Joined:** Mon Aug 22, 2005 5:45 am **Posts:** 146

sregg wrote:

Hi,

I have a problem with HQL and querying a map attribute.

Here is my mapping file of a class Service.

Code:

<class name="com.eurecia.backoffice.Service" table="SERVICE_EA">

...

<map name="descr" table="SERVICE_DESCR_EA" cascade="all" lazy="false">
          <key>
             <column name="ID_SERVICE" not-null="true"/>
             <column name="ID_COMPANY" not-null="true"/>
          </key>
          <map-key column="LOCALE" type="java.lang.String" length="10"/>
          <element column="DESCR_SERVICE" type="java.lang.String" not-null="true" length="255"/>
</map>

Like you can see, this class contains a map attribute named descr

I would like to query this class et get the descr for the current locale.

So here is my query :

Code:

StringBuffer queryString = new StringBuffer();
      queryString.append("SELECT srv.id.idService, srv.id.idCompany, srv.type,srv.descr['"+session.getLocale()+"'], cny.descr");
      queryString.append(" FROM Service as srv, Company as cny");
      queryString.append(" WHERE srv.id.idCompany = cny.id");
      
      if( filters != null )
      {
         queryString.append(" AND srv.id.idService like '%"+filters.get("id.idService")+"%'");
         queryString.append(" AND cny.descr like '%"+filters.get("companyDescr")+"%'");
         queryString.append(" AND srv.type like '%"+filters.get("type")+"%'");
         queryString.append(" AND srv.descr['"+session.getLocale()+"'] like '%"+filters.get("descr")+"%'");
      }

My problem is in the SELECT part :

Code:

srv.descr['"+session.getLocale()+"']

. I think this expression can't be used in a SELECT section.
But it works in the WHERE section :

Code:

AND srv.descr['"+session.getLocale()+"'] like '%"+filters.get("descr")+"%

How can I select on element of a map in the SELECT section ?

Thank you for your response.

Hibernate version: 3.0.5

Please have a look at how to create type-safe hql-queries programmatically:

http://www.hibernate.org/hib_docs/v3/re ... hql-select

The reason hql was created is to provide a java API to SQL.
Using plain-strings results in various trouble, especially with the 3.1.x branch.

sregg · **Joined:** Thu Apr 20, 2006 9:35 am **Posts:** 10

Thanks.

But it doesn't help me a lot...

How can i select an element of a map in HQL ?

It works in WHERE clause : srv.descr['"+session.getLocale()+"'] like ...

But not in SELECT clause.

How can i make it works ?

Thank you again...

sregg · **Joined:** Thu Apr 20, 2006 9:35 am **Posts:** 10

Hi,

I found my self a solution resolving my problem.

SELECT srv.id.idService, srv.id.idCompany, typeDescr, servDescr, cny.descr
FROM Service as srv, Company as cny, ListValue lv
left outer join srv.descr as servDescr
left outer join lv.descr as typeDescr
WHERE srv.id.idCompany = cny.id"
AND lv.id.type = 'service_type'
AND srv.type = lv.id.value");
AND srv.id.idCompany = lv.id.idCompany
AND index(servDescr) = '"+session.getLocale()+"'
AND index(typeDescr) = '"+session.getLocale()+"'

The index() method allow to access the key property of a map...