before beginning, just let me say thanks gavin for all the help you've given me. i've been piling up my questions over the past few days i've been unable to find answers to and you've been most helpful.
this one is probably something stupid i'm doing, but i can't for the life of me figure out what. we have a many-to-many association that looks like this in our DB:
Code:
BUSINESS <-> BUSINESS_CATEGORY <-> CATEGORY
BUSINESS_ID BUSINESS_ID CATEGORY_ID
CATEGORY_ID
extremely simple. i have defined the set in the business class like this:
Code:
<set name="categories"
lazy="true"
inverse="false"
cascade="none"
table="BUSINESS_CATEGORY">
<key column="BUSINESS_ID" />
<many-to-many
column="CATEGORY_ID"
class="com.sa.go.bto.Category" />
</set>
it is not a bidirectional association, so you cannot get the businesses in a category. now, when i execute a business.getCategories() i get queries like this:
Code:
Hibernate: select business0_.CATEGORY_ID as CATEGORY2___, business0_.BUSINESS_ID as BUSINESS1___ from BUSINESS_CATEGORY business0_ where business0_.BUSINESS_ID=?
Hibernate: select category0_.CATEGORY_ID as CATEGORY_ID0_, category0_.CATEGORY_NAME as CATEGORY2_0_, category0_.CATEGORY_DESC as CATEGORY3_0_, category0_.PARENT_ID as PARENT_ID0_ from CATEGORY category0_ where category0_.CATEGORY_ID=?
Hibernate: select category0_.CATEGORY_ID as CATEGORY_ID0_, category0_.CATEGORY_NAME as CATEGORY2_0_, category0_.CATEGORY_DESC as CATEGORY3_0_, category0_.PARENT_ID as PARENT_ID0_ from CATEGORY category0_ where category0_.CATEGORY_ID=?
in other words, it first pulls back all the CATEGORY_ID values from the BUSINESS_CATEGORY table and then issues a select for each record it finds against CATEGORY. it would be much faster to resolve the values with a single select like:
Code:
select c.*
from business_category bc,
category c
where bc.business_id = ? and bc.category_id = c.category_id
is there any way to tweak the configuration to do that?
thanks again!
james
Login
FAQ
Search
