It is my understanding that fetch="join" is used to return data with one query, using joins, rather that multiple selects without joins. However I find that placing fetch="join in the <set> element causes more queries and duplicate results to be returned.
Please note the criteria query is trivial and the example passed to it is an empty object for both results. Passing in additional properties for the example object (Branch) still returns multiple (duplicate) results.
I don't think this problem existed when we were using 2.1.6.
Regards,
Joe
Hibernate version: 3.0.5
Mapping documents:
Code:
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.adcware.rcs.model">
<class name="Branch" table="BRANCH" lazy="true">
<id name="id" type="string" column="ID">
<generator class="uuid.hex" />
</id>
<property name="name" type="string" column="NAME" not-null="true"/>
<property name="description" type="string" column="DESCRIPTION" />
<property name="active" type="boolean" column="IS_ACTIVE" />
<property name="number" type="string" column="BRANCH_NUM" not-null="true" unique="true"/>
<component name="address" class="Address">
<property name="line1" type="string" column="ADDRESS1" not-null="true"/>
<property name="line2" type="string" column="ADDRESS2"/>
<property name="city" type="string" column="CITY" not-null="true"/>
<property name="state" type="string" column="STATE"/>
<property name="zipcode" type="string" column="ZIPCODE" not-null="true"/>
<property name="country" type="string" column="COUNTRY"/>
</component>
<!-- The other side of this bidirectional one-to-many association to market. -->
<many-to-one name="microMarket" fetch="join" column="MICROMARKET_ID" class="MicroMarket" not-null="true" />
<!-- cascade="save-update" -->
<!-- One Branch to-many Employees -->
<set name="employees"
inverse="true"
lazy="true"
fetch="join"
cascade="delete">
<key>
<column name="BRANCH_ID" not-null="true"/>
</key>
<one-to-many class="Employee"/>
</set>
</class>
</hibernate-mapping>
Code:
public List findBranchesByLikeExample(Branch exampleBranch)
throws HibernateException {
Session hsession = _sessionFactory.getCurrentSession();
Criteria criteria = hsession.createCriteria(Branch.class);
criteria.add(Example.create(exampleBranch).ignoreCase().enableLike(MatchMode.ANYWHERE));
List list = criteria
.addOrder(Order.asc("name"))
.list();
return list;
}
Junit test case: Code:
public void testGetBranchByLikeExample() throws Exception {
MacroMarket mm1 = _mgr.createMacroMarket(TestData.makeMacroMarket(1));
Market mkt1 = _mgr.createMarket(TestData.makeMarket(1), mm1.getId());
MicroMarket micMkt1 = _mgr.createMicroMarket(TestData.makeMicroMarket(1), mkt1.getId());
Branch br = _mgr.createBranch(TestData.makeBranch(1), micMkt1.getId());
Employee e1 = _mgr.createEmployee(TestData.makeEmployee(1), br.getId());
Employee e2 = _mgr.createEmployee(TestData.makeEmployee(2), br.getId());
Employee e3 = _mgr.createEmployee(TestData.makeEmployee(3), br.getId());
Branch brex = new Branch();
_mgr.println("before");
List branches = _mgr.findBranchesByLikeExample(brex);
_mgr.println("after");
//assertEquals("number of branches is incorrect", 1, branches.size());
TestHelper.printCollection(branches);
}
Name and version of the database you are using:
MySQL 4.1.8Testcase results with fetch="join" on set name="employees" aboveCode:
// Size of collection = 3
402881e40442b9ba010442c2263c000d name1 null false 101 line1, city1, state1 zip1 country1
402881e40442b9ba010442c2263c000d name1 null false 101 line1, city1, state1 zip1 country1
402881e40442b9ba010442c2263c000d name1 null false 101 line1, city1, state1 zip1 country1
Testcase results *WITHOUT* fetch="join"Code:
// Size of collection = 1
402881e40442b9ba010442d980490015 name1 null false 101 line1, city1, state1 zip1 country1
The generated SQL (show_sql=true) for fetch="join" :Code:
( I formatted the query to make it readable ...)
4 select statements ...
1 as below:
11:37:29,642 INFO [STDOUT] before
11:37:29,652 INFO [STDOUT] Hibernate:
select
this_.ID as ID2_,
this_.NAME as NAME32_2_,
this_.DESCRIPTION as DESCRIPT3_32_2_,
this_.IS_ACTIVE as IS4_32_2_,
this_.BRANCH_NUM as BRANCH5_32_2_,
this_.ADDRESS1 as ADDRESS6_32_2_,
this_.ADDRESS2 as ADDRESS7_32_2_,
this_.CITY as CITY32_2_,
this_.STATE as STATE32_2_,
this_.ZIPCODE as ZIPCODE32_2_,
this_.COUNTRY as COUNTRY32_2_,
this_.MICROMARKET_ID as MICROMA12_32_2_,
micromarke2_.ID as ID0_,
micromarke2_.VERSION as VERSION38_0_,
micromarke2_.CREATED as CREATED38_0_,
micromarke2_.CREATED_BY as CREATED4_38_0_,
micromarke2_.UPDATED as UPDATED38_0_,
micromarke2_.UPDATED_BY as UPDATED6_38_0_,
micromarke2_.NAME as NAME38_0_,
micromarke2_.DESCRIPTION as DESCRIPT8_38_0_,
micromarke2_.IS_ACTIVE as IS9_38_0_,
micromarke2_.MARKET_ID as MARKET10_38_0_,
employees3_.BRANCH_ID as BRANCH16_4_,
employees3_.ID as ID4_,
employees3_.ID as ID1_,
employees3_.FIRSTNAME as FIRSTNAME35_1_,
employees3_.LASTNAME as LASTNAME35_1_,
employees3_.OFFICE_PHONE as OFFICE4_35_1_,
employees3_.EMPLOYEE_NUM as EMPLOYEE5_35_1_,
employees3_.EMAIL_ADDRESS as EMAIL6_35_1_,
employees3_.OFFICER_CODE as OFFICER7_35_1_,
employees3_.STATUS as STATUS35_1_,
employees3_.JOB_TITLE as JOB9_35_1_,
employees3_.JOB_CODE as JOB10_35_1_,
employees3_.JOB_SPECIAL_CAT_CODE as JOB11_35_1_,
employees3_.JOB_SPECIAL_CAT_NAME as JOB12_35_1_,
employees3_.JOB_STATUS as JOB13_35_1_,
employees3_.JOB_REFERENCE_TYPE as JOB14_35_1_,
employees3_.JOB_ADJUSTED_STATUS as JOB15_35_1_,
employees3_.BRANCH_ID as BRANCH16_35_1_
from
BRANCH this_
inner join MICROMARKET micromarke2_ on this_.MICROMARKET_ID=micromarke2_.ID
left outer join EMPLOYEE employees3_ on this_.ID=employees3_.BRANCH_ID
where (this_.IS_ACTIVE=?)
order by
this_.NAME asc
and 3 exactly the same as below:
select
account0_.ID as ID4_,
account0_.VERSION as VERSION28_4_,
account0_.CREATED as CREATED28_4_,
account0_.CREATED_BY as CREATED4_28_4_,
account0_.UPDATED as UPDATED28_4_,
account0_.UPDATED_BY as UPDATED6_28_4_,
account0_.USERID as USERID28_4_,
account0_.PASSWORD as PASSWORD28_4_,
account0_.PASSWORD_DATE as PASSWORD9_28_4_,
account0_.PASSWORD_PREVIOUS as PASSWORD10_28_4_,
account0_.FAILED_LOGIN_ATTEMPTS as FAILED11_28_4_,
account0_.IS_DISABLED as IS12_28_4_,
account0_.DISABLED_REASON as DISABLED13_28_4_,
account0_.EMPLOYEE_ID as EMPLOYEE14_28_4_,
employee1_.ID as ID0_,
employee1_.FIRSTNAME as FIRSTNAME35_0_,
employee1_.LASTNAME as LASTNAME35_0_,
employee1_.OFFICE_PHONE as OFFICE4_35_0_,
employee1_.EMPLOYEE_NUM as EMPLOYEE5_35_0_,
employee1_.EMAIL_ADDRESS as EMAIL6_35_0_,
employee1_.OFFICER_CODE as OFFICER7_35_0_,
employee1_.STATUS as STATUS35_0_,
employee1_.JOB_TITLE as JOB9_35_0_,
employee1_.JOB_CODE as JOB10_35_0_,
employee1_.JOB_SPECIAL_CAT_CODE as JOB11_35_0_,
employee1_.JOB_SPECIAL_CAT_NAME as JOB12_35_0_,
employee1_.JOB_STATUS as JOB13_35_0_,
employee1_.JOB_REFERENCE_TYPE as JOB14_35_0_,
employee1_.JOB_ADJUSTED_STATUS as JOB15_35_0_,
employee1_.BRANCH_ID as BRANCH16_35_0_,
referralso2_.ORIGINATING_ACCOUNT_ID as ORIGINA30_6_,
referralso2_.ID as ID6_,
referralso2_.ID as ID1_,
referralso2_.VERSION as VERSION40_1_,
referralso2_.ORIGINATED as ORIGINATED40_1_,
referralso2_.ORIGINATED_BY as ORIGINATED4_40_1_,
referralso2_.ASSIGNED as ASSIGNED40_1_,
referralso2_.ASSIGNED_BY as ASSIGNED6_40_1_,
referralso2_.ACCEPTED as ACCEPTED40_1_,
referralso2_.ACCEPTED_BY as ACCEPTED8_40_1_,
referralso2_.COMPLETED as COMPLETED40_1_,
referralso2_.COMPLETED_BY as COMPLETED10_40_1_,
referralso2_.CUSTOMER_FIRSTNAME as CUSTOMER11_40_1_,
referralso2_.CUSTOMER_LASTNAME as CUSTOMER12_40_1_,
referralso2_.CUSTOMER_PHONE1 as CUSTOMER13_40_1_,
referralso2_.CUSTOMER_PHONE2 as CUSTOMER14_40_1_,
referralso2_.CUSTOMER_NUM as CUSTOMER15_40_1_,
referralso2_.CUSTOMER_ADDRESS1 as CUSTOMER16_40_1_,
referralso2_.CUSTOMER_ADDRESS2 as CUSTOMER17_40_1_,
referralso2_.CUSTOMER_CITY as CUSTOMER18_40_1_,
referralso2_.CUSTOMER_STATE as CUSTOMER19_40_1_,
referralso2_.CUSTOMER_ZIPCODE as CUSTOMER20_40_1_,
referralso2_.CUSTOMER_COUNTRY as CUSTOMER21_40_1_,
referralso2_.STATUS as STATUS40_1_,
referralso2_.SOURCE as SOURCE40_1_,
referralso2_.REFERRAL_NUM as REFERRAL24_40_1_,
referralso2_.DISPOSITION as DISPOSI25_40_1_,
referralso2_.IS_QUALIFIED as IS26_40_1_,
referralso2_.IS_NOT_QUALIFIED as IS27_40_1_,
referralso2_.COMMENT as COMMENT40_1_,
referralso2_.PRODUCT_CATEGORY_ID as PRODUCT29_40_1_,
referralso2_.ORIGINATING_ACCOUNT_ID as ORIGINA30_40_1_,
referralso2_.ASSIGNED_ACCOUNT_ID as ASSIGNED31_40_1_,
referralsa3_.ASSIGNED_ACCOUNT_ID as ASSIGNED31_7_,
referralsa3_.ID as ID7_,
referralsa3_.ID as ID2_,
referralsa3_.VERSION as VERSION40_2_,
referralsa3_.ORIGINATED as ORIGINATED40_2_,
referralsa3_.ORIGINATED_BY as ORIGINATED4_40_2_,
referralsa3_.ASSIGNED as ASSIGNED40_2_,
referralsa3_.ASSIGNED_BY as ASSIGNED6_40_2_,
referralsa3_.ACCEPTED as ACCEPTED40_2_,
referralsa3_.ACCEPTED_BY as ACCEPTED8_40_2_,
referralsa3_.COMPLETED as COMPLETED40_2_,
referralsa3_.COMPLETED_BY as COMPLETED10_40_2_,
referralsa3_.CUSTOMER_FIRSTNAME as CUSTOMER11_40_2_,
referralsa3_.CUSTOMER_LASTNAME as CUSTOMER12_40_2_,
referralsa3_.CUSTOMER_PHONE1 as CUSTOMER13_40_2_,
referralsa3_.CUSTOMER_PHONE2 as CUSTOMER14_40_2_,
referralsa3_.CUSTOMER_NUM as CUSTOMER15_40_2_,
referralsa3_.CUSTOMER_ADDRESS1 as CUSTOMER16_40_2_,
referralsa3_.CUSTOMER_ADDRESS2 as CUSTOMER17_40_2_,
referralsa3_.CUSTOMER_CITY as CUSTOMER18_40_2_,
referralsa3_.CUSTOMER_STATE as CUSTOMER19_40_2_,
referralsa3_.CUSTOMER_ZIPCODE as CUSTOMER20_40_2_,
referralsa3_.CUSTOMER_COUNTRY as CUSTOMER21_40_2_,
referralsa3_.STATUS as STATUS40_2_,
referralsa3_.SOURCE as SOURCE40_2_,
referralsa3_.REFERRAL_NUM as REFERRAL24_40_2_,
referralsa3_.DISPOSITION as DISPOSI25_40_2_,
referralsa3_.IS_QUALIFIED as IS26_40_2_,
referralsa3_.IS_NOT_QUALIFIED as IS27_40_2_,
referralsa3_.COMMENT as COMMENT40_2_,
referralsa3_.PRODUCT_CATEGORY_ID as PRODUCT29_40_2_,
referralsa3_.ORIGINATING_ACCOUNT_ID as ORIGINA30_40_2_,
referralsa3_.ASSIGNED_ACCOUNT_ID as ASSIGNED31_40_2_,
accountrol4_.ACCOUNT_ID as ACCOUNT4_8_,
accountrol4_.ID as ID8_,
accountrol4_.ID as ID3_,
accountrol4_.CREATED as CREATED29_3_,
accountrol4_.CREATED_BY as CREATED3_29_3_,
accountrol4_.ACCOUNT_ID as ACCOUNT4_29_3_,
accountrol4_.ROLE_ID as ROLE5_29_3_
from
ACCOUNT account0_
inner join EMPLOYEE employee1_ on account0_.EMPLOYEE_ID=employee1_.ID
left outer join REFERRAL referralso2_ on account0_.ID=referralso2_.ORIGINATING_ACCOUNT_ID
left outer join REFERRAL referralsa3_ on account0_.ID=referralsa3_.ASSIGNED_ACCOUNT_ID
left outer join ACCOUNTROLE accountrol4_ on account0_.ID=accountrol4_.ACCOUNT_ID
where
account0_.EMPLOYEE_ID=?
11:37:30,952 INFO [STDOUT] after
The generated SQL (show_sql=true) *WITHOUT* fetch="join" :Code:
11:38:45,226 INFO [STDOUT] before
11:38:45,236 INFO [STDOUT] Hibernate:
select
this_.ID as ID1_,
this_.NAME as NAME18_1_,
this_.DESCRIPTION as DESCRIPT3_18_1_,
this_.IS_ACTIVE as IS4_18_1_,
this_.BRANCH_NUM as BRANCH5_18_1_,
this_.ADDRESS1 as ADDRESS6_18_1_,
this_.ADDRESS2 as ADDRESS7_18_1_,
this_.CITY as CITY18_1_,
this_.STATE as STATE18_1_,
this_.ZIPCODE as ZIPCODE18_1_,
this_.COUNTRY as COUNTRY18_1_,
this_.MICROMARKET_ID as MICROMA12_18_1_,
micromarke2_.ID as ID0_,
micromarke2_.VERSION as VERSION24_0_,
micromarke2_.CREATED as CREATED24_0_,
micromarke2_.CREATED_BY as CREATED4_24_0_,
micromarke2_.UPDATED as UPDATED24_0_,
micromarke2_.UPDATED_BY as UPDATED6_24_0_,
micromarke2_.NAME as NAME24_0_,
micromarke2_.DESCRIPTION as DESCRIPT8_24_0_,
micromarke2_.IS_ACTIVE as IS9_24_0_,
micromarke2_.MARKET_ID as MARKET10_24_0_
from
BRANCH this_
inner join MICROMARKET micromarke2_ on this_.MICROMARKET_ID=micromarke2_.ID
where
(this_.IS_ACTIVE=?)
order by
this_.NAME asc
11:38:45,536 INFO [STDOUT] after
Login
FAQ
Search