Help with HQL query for a link table (join table)

shaikh.mshariq · **Posted:** Sat Jul 10, 2010 6:53 am

Hi all,

Here is the scenario in my application.

Quote:

Tables :

User
USER_ID
USER_NAME
AGE

ADDRESS
ADDRESS_ID
ADDRESS1
ADDRESS2
CITY
STATE
ZIP

USER_ADDRESS
USER_ADDRESS_ID
USER_ID
ADDRESS_ID

I have mapped this two tables using join table tag in User.hbm.xml file

Code:

 <join table="USER_ADDRESS">
        <key column="USER_ID"></key>
        <many-to-one name="userAddress" column="ADDRESS_ID" not-null="true" unique="true">
        </many-to-one>
     </join>

Also I am able to fetch address for a particular user but I am stuck in search. I have hql query in User.hbm.xml to search for a user.

Code:

<query name="findUsersByCriteria">
from User user where userId like :criteria OR userName like :criteria OR age like :criteria
  </query>

Here I want to achieve that it should also search in address table for a match. Means

Code:

address1 like :criteria OR address2 like :criteria

So join of two table should be considered as one row and I want to search in that row for any match.

Anyone has the idea how can I achieve this. I can modify my mappings but not the schema. It would be great if I can amend hql only to achieve this. Any help will be appreciated

Thanks In Advance,
Shariq