createQuery question

forgotmyusernameagain2 · **Joined:** Sun Sep 24, 2006 11:48 am **Posts:** 21

Here is my database model:

LocalArea,WideArea, Country

The relationship:

LocalArea references a WideArea

WideArea references Country

I want to create a query that will return all LocalAreas of a certain country.

Criteria c = session.createCriteria(LocalArea.class);
c.add(Restrictions.eq("country", country));

but this can not work...so how do you solve this problem?

Thanks!

forgotmyusernameagain2 · **Joined:** Sun Sep 24, 2006 11:48 am **Posts:** 21

I have tried SQLQuery now instead:

Code:

SQLQuery query = session.createSQLQuery("SELECT localArea.* from LocalArea localArea"
            + " JOIN WideArea wideArea join Country country " 
            + " on wideArea.wideAreaID=localArea.wideAreaID and country.countryID=wideArea.countryID " +
                  " and country.countryID=70 and localArea.localAreaURL='" + localArea+"'");

   query.setMaxResults(1);
   query.setCacheable(true);
   localArea area = (LocalArea)query.uniqueResult();
   
            

But this strangely causes a ClasCastException....although i checked
the consistence of the table and the class - and it does work for simple queries on LocalArea without joins like that.

forgotmyusernameagain2 · **Joined:** Sun Sep 24, 2006 11:48 am **Posts:** 21

is my question too easy or too difficult? ;-)

sudhir0104 · **Posted:** Tue Sep 26, 2006 8:17 am

use the association feature friend, nice e.g of that are given on this page

http://www.amicabile.com/hybernate/hybe ... pter7.html

in short, define the relation between tables in your hbm files and then use association to write a query in order to get the deisired result....

also your query is not that complex, its very common

Sudhir

forgotmyusernameagain2 · **Joined:** Sun Sep 24, 2006 11:48 am **Posts:** 21

thanks! finally a reply ;-)

forgotmyusernameagain2 · **Joined:** Sun Sep 24, 2006 11:48 am **Posts:** 21

...however i could not find any example of such a criteria query on that website.. :-(

forgotmyusernameagain2 · **Joined:** Sun Sep 24, 2006 11:48 am **Posts:** 21

...and in the hibernate docs there is only an example about cats and kittens....

kochcp · **Posted:** Tue Sep 26, 2006 10:35 am

can your relationship be set up as 1:m from country -> wide area, and 1:m from wide area -> local area?

forgotmyusernameagain2 · **Joined:** Sun Sep 24, 2006 11:48 am **Posts:** 21

kochcp wrote:

can your relationship be set up as 1:m from country -> wide area, and 1:m from wide area -> local area?

yes....that is how it is set up...the question is how criteria queries work if you "skip" a table in between 2 tables.

kochcp · **Posted:** Tue Sep 26, 2006 2:20 pm

Criteria c = createCriteria(Country.class)
c.createAlias("wideArea", "wa");
c.createAlias("wa.localArea", "la");
c.add(Restrictions.eq("la.id", variable);

forgotmyusernameagain2 · **Joined:** Sun Sep 24, 2006 11:48 am **Posts:** 21

kochcp wrote:

Criteria c = createCriteria(Country.class)
c.createAlias("wideArea", "wa");
c.createAlias("wa.localArea", "la");
c.add(Restrictions.eq("la.id", variable);

Thanks! However I would like to find a LocalArea that belong to a Country,
ignoring the WideArea.

In SQL I would do something like:

Code:

select localArea.* from LocalArea as localArea
join WideArea as wideArea,
join Country as country 

on localArea.wideAreaID=wideArea.id
and wideArea.countryID=country.id

and localArea.url='some_region'
and country.id=70;

In an ideal world this would be something like:

Code:

Criteria c = session.createCriteria(LocalArea.class); 
c.add(Restrictions.eq("localArea.url", "some_region")); 
c.add(Restrictions.eq("wideArea", Hibernate.ANY)); 
c.add(Restrictions.eq("wideArea.country.countryID", 70);

forgotmyusernameagain2 · **Joined:** Sun Sep 24, 2006 11:48 am **Posts:** 21

Code:

Criteria crit = session.createCriteria(LocalArea.class);
      
crit.createAlias("wideArea", "w"); 
crit.createAlias("w.country", "c"); 
crit.add(Restrictions.eq("c.id", new Integer(70)));
      

okay solved it ;-)

always thought aliases where useless aliases ;-)

kochcp · **Posted:** Tue Sep 26, 2006 5:06 pm

Criteria c = createCriteria(Country.class)
c.add(Restrictions.eq("id", variable1);
c.createAlias("wideArea", "wa");
c.createAlias("wa.localArea", "la");
c.add(Restrictions.eq("la.url", variable2);