[help]how can i query primary class by it's map son?

sojan · **Joined:** Tue Sep 02, 2003 10:28 pm **Posts:** 11

this is my xml:

Code:

<hibernate-mapping>
  <class name="com.southstandard.me.bean.OrgUser" table="ME_ORG_USER">
    <id name="id" column="ID" type="long" unsaved-value="null">
        <generator class="sequence">
                <param name="sequence">SEQ_ME_ORG_USER</param>
        </generator>
    </id>

    <property name="mobile" column="MOBILE" type="string" length="21" not-null="true"/>
    <property name="orgId" column="ORG_ID" type="long" not-null="true"/>
    <property name="createTime" column="CREATE_TIME" type="timestamp"/>
    <property name="updateTime" column="UPDATE_TIME" type="timestamp"/>
    <property name="name" column="NAME" type="string" length="20" not-null="true"/>
    <property name="sex" column="SEX" type="string" length="1" not-null="true"/>
    <property name="batchSendable" column="BATCHSENDABLE" type="boolean"/>
    <property name="memberable" column="MEMBERABLE" type="boolean"/>

    <map name="userProp" table="ME_ORG_USER_PROP" lazy="false" cascade="all" sort="unsorted">
      <key column="ID"/>
      <index column="PROP_NAME" type="string" length="40"/>
      <element column="PROP_VALUE" type="string" length="200" not-null="true"/>
    </map>
  </class>
</hibernate-mapping>

my object is to query OrgUser by given statement: userProp:PROP_NAME and Prop_VALUE.

I have got the goal just by jdbc:
sql:

Code:

select a.ID from ME_ORG_USER a, ME_ORG_USER_PROP b where a.ID = b.ID and b.PROP_NAME = 'age' and b.PROP_VALUE like '%8%'

now, I try to get the same goal by hibernat, this is my hql:

Code:

select a.ID from OrgUser a where a.userProp.PROP_NAME = 'address'

this hql failed, the wrong info is :
expecting 'elements' or 'indices' after: a.userProp.PROP_NAME

then i try replacing hql by sql: failed again, the wrong info is :

Code:

com.southstandard.util.hibernate.DAOException: in expected: a [select a.ID from ME_ORG_USER a, ME_ORG_USER_PROP b where a.ID = b.ID and b.PROP_NAME = 'age' and b.PROP_VALUE like '%8%'] wraps: [net.sf.hibernate.QueryException: in expected: a [select a.ID from ME_ORG_USER a, ME_ORG_USER_PROP b where a.ID = b.ID and b.PROP_NAME = 'age' and b.PROP_VALUE like '%8%']]

please tell me, how can i write proper hql to achieve my goal?

drj · **Posted:** Mon Dec 22, 2003 4:41 am

Code:

select a.ID from OrgUser a where a.userProp.PROP_NAME = 'address'

Quote:

this hql failed, the wrong info is :
expecting 'elements' or 'indices' after: a.userProp.PROP_NAME

What Hibernate is telling you is that you the userProp is not a single value - it is a collection of some sort. You either need to indicate the index of the element that you wish to restrict on, or join on the collection and then restrict on the joined tuples:

I think what you are after is something like:

Code:

select a.ID from OrgUser a join a.userProp up 
where up.PROP_NAME = 'address'

sojan · **Joined:** Tue Sep 02, 2003 10:28 pm **Posts:** 11

thank you for your reply!
i have tried your hql, but some warning message occur:

Code:

com.southstandard.util.hibernate.DAOException: collection of values in from clause: a.userProp.elements [select a.ID from com.southstandard.me.bean.OrgUser a join a.userProp up where up.PROP_NAME = 'address' ] wraps: [net.sf.hibernate.QueryException: collection of values in from clause: a.userProp.elements [select a.ID from com.southstandard.me.bean.OrgUser a join a.userProp up where up.PROP_NAME = 'address' ]]

it tell me the warning info is :
collection of values in from clause

i really want to know if there is some way that hibernate surport me while i am doing some query on one class's map?

emmanuel · **Posted:** Mon Dec 22, 2003 5:11 am

Code:

select distinct a.ID from OrgUser a where 'address' in indices(a.userProp)

Have a look at 10.8.

drj · **Posted:** Mon Dec 22, 2003 5:52 am

Quote:

select distinct a.ID from OrgUser a where 'address' in indices(a.userProp)

.. doesn't work in MySQL v4.0.14 (I assume that they don't support correlated subqueries but suspect that you are using Oracle/DB2 or Postgres anyway)

but this does:

Code:

select user from OrgUser user join user.userProp up " +
where index(up) = 'address'

emmanuel · **Posted:** Mon Dec 22, 2003 6:58 am

drj wrote:

Quote:

I assume that they don't support correlated subqueries

You're right indices are implemented as subquery

sojan · **Joined:** Tue Sep 02, 2003 10:28 pm **Posts:** 11

epbernard wrote:

Code:

select distinct a.ID from OrgUser a where 'address' in indices(a.userProp)

Have a look at 10.8.

Thanks! it does work!

But i need it work like: LIKE '%8%'

so, i try this hql:

Code:

select distinct a.id from OrgUser a where '%address%' like indices(a.userProp)

which product sql:

Code:

select distinct a.ID as x0_0_ from ME_ORG_USER a where ('%address%' like(SELECT use0_.PROP_NAME FROM ME_ORG_USER_PROP use0_ WHERE a.ID=use0_.ID))

this sql tell me the warning info:

ORA-01427: single-row subquery returns more than one row

then i have try another hql:

Code:

"select distinct a.id from OrgUser a where indices(a.userProp) like '%address%' " 

ok, hibernate generate another sql for me:

Code:

select distinct a.ID as x0_0_ from ME_ORG_USER a where ((SELECT use0_.PROP_NAME FROM ME_ORG_USER_PROP use0_ WHERE a.ID=use0_.ID)like '%address%' )

unfortunatley, oracle tell me the same warning info:
ORA-01427: single-row subquery returns more than one row

can anyone tell me how can i achive my goal, please?

Code:

select distinct a.id from OrgUser a where '%address%' like indices(a.userProp)

but

Code:

select distinct a.id from OrgUser a where 'address' = indices(a.userProp)

emmanuel · **Posted:** Mon Dec 29, 2003 6:32 am

Try to write and make work the SQL first. I'm not sure what you want is doable with subqueries.

sojan · **Joined:** Tue Sep 02, 2003 10:28 pm **Posts:** 11

maybe i have not expressed my request very clearly.
please allow me to repost my need again.

this is the brief xml of orgUser.

Code:

<hibernate-mapping> 
  <class name="com.southstandard.me.bean.OrgUser" table="ME_ORG_USER"> 
    <id name="id" column="ID" type="long" unsaved-value="null"> 
        <generator class="sequence"> 
                <param name="sequence">SEQ_ME_ORG_USER</param> 
        </generator> 
    </id> 

    <property name="name" column="NAME" type="string" length="20" not-null="true"/> 
    <property name="sex" column="SEX" type="string" length="1" not-null="true"/> 

    <map name="userProp" table="ME_ORG_USER_PROP" lazy="false" cascade="all" sort="unsorted"> 
      <key column="ME_ORG_USER_ID"/> 
      <index column="PROP_NAME" type="string" length="40"/> 
      <element column="PROP_VALUE" type="string" length="200" not-null="true"/> 
    </map> 
  </class> 
</hibernate-mapping>

it mapping this schema in Oracle database:

Code:

ME_ORG_USER
ID        Number
NAME   Varchar2(20)
SEX     Varchar2(1)

Code:

ME_ORG_USER_PROP
ME_ORG_USER_ID Number
PROP_NAME Varchar2(40)
PROP_VALUE Varchar2(200)

in this context, i would like to use orgUser to do something like this:
orgUser.setName("little gates");
orgUser.setSex("f");

orgUser.getUserProp.put("address","moon");
orgUser.getUserProp.put("'age' ","88");
...

the resean i use PROP to dealwith "address","'age' ", but some style like: orgUser.setAddress("moon"), orgUser.setAge("88") is that
orgUser is my valuable collect data, "name" and "sex" are its necessary fields, but some orgUser has "address" fields while others don't have.
so, i think it is flexible to attach some additional info to orgUser without do any change on database.
such as:
orgUser.getUserProp.put("'contactInfo' ","8373737892");

this is really my original intention.

now here comes some demand i don't know how to achieve with hibernate.

i want these orgUser who's age is like "%8%".
for instance:
id name age
47 tom 78
99 jimmy 8
104 smith 58
all these orgUsers are what i want.

so, i wrote a sql to do this:

Code:

select a.ID from ME_ORG_USER a, ME_ORG_USER_PROP b where a.ID = b.ID and b.PROP_NAME = 'age' and b.PROP_VALUE like '%8%'

it works.

please tell me can i gain my goal by hql?

Code:

emmanuel · **Posted:** Mon Dec 29, 2003 11:57 am

OK I made the test to be sure.

Code:

select distinct a.ID from OrgUser a join a.userProp prop where index(prop) like '%dres%'

sojan · **Joined:** Tue Sep 02, 2003 10:28 pm **Posts:** 11

YEAH, it works!
epbernard, thank you very much!