Hi...

I have created a View EPW3_USER_ACCTS in MySQL retrieving data from 10 tables from EPW3_USER_ACCT_0.....EPW3_USER_ACCT_9 all having identical columns with UserID as the primary key in all the columns

Hence the POJO mappings are made from UserAcct0....UserAcct9 for the tables and UserAcct for the view

Now when I run the query:-
List<UserAcct> userAccts = null;
Session session = HibernateUtil.getSession();
userAccts = session.createCriteria(UserAcct.class).
add(Restrictions.eq(EPW3AppConstants.UUID, uuid)).
setCacheable(true).list();
HibernateUtil.closeSession();

I get the a pouplated List with the right count of records but each UserAcct object in the list is the same and that too its a record from the first table , since userid is the primary key in all the tables there could be only one record for userid in each of the tables.

I must tell that retrieval from the view works fine when we want a single object , following is the query used to fetch a single record :-

Session session = HibernateUtil.getSession();
userAcct = (UserAcct) session.createCriteria(UserAcct.class).add(
Restrictions.eq(EPW3AppConstants.UUID, uuid)).add( Restrictions.eq(EPW3AppConstants.MAIL_ADDR, mailAddr)).
setCacheable(true).uniqueResult();
HibernateUtil.closeSession();

I also tried using the query that Hibernate creates for querying directly from the Query browser and it is selecting records as desired. Below is the query that is generated by Hibernate

select this_.USER_ID as USER1_0_0_, this_.NAME as NAME0_0_, this_.USER_NM as USER3_0_0_, this_.MAIL_PASS as MAIL4_0_0_, this_.MAIL_SRVR as MAIL5_0_0_, this_.MAIL_ADDR as MAIL6_0_0_, this_.MAIL_TOKEN as MAIL7_0_0_, this_.ACCT_TYPE as ACCT8_0_0_ from EPW3_USER_ACCTS this_ where this_.USER_ID='222222222222222222222222';

Now, Iam using EhCache to cache the UserAcct object but it behaves the same even if i remove its entry from the ehcache.xml and i also have tried removing the setCacheable(true) property from the query , still the same result

Is it a bug in Hibernate , Please tell me what is the right way of retrieving collections from a View using Hibernate

For everyone's reference below is the query I used to create the view :-

create view epw3_user_accts as
select user_id,name,user_nm,mail_pass,mail_srvr,mail_addr,mail_token,acct_type
from epw3.epw3_user_acct_0
union all
select user_id,name,user_nm,mail_pass,mail_srvr,mail_addr,mail_token,acct_type
from epw3.epw3_user_acct_1
union all
select user_id,name,user_nm,mail_pass,mail_srvr,mail_addr,mail_token,acct_type
from epw3.epw3_user_acct_2
union all
select user_id,name,user_nm,mail_pass,mail_srvr,mail_addr,mail_token,acct_type
from epw3.epw3_user_acct_3
union all
select user_id,name,user_nm,mail_pass,mail_srvr,mail_addr,mail_token,acct_type
from epw3.epw3_user_acct_4
union all
select user_id,name,user_nm,mail_pass,mail_srvr,mail_addr,mail_token,acct_type
from epw3.epw3_user_acct_5
union all
select user_id,name,user_nm,mail_pass,mail_srvr,mail_addr,mail_token,acct_type
from epw3.epw3_user_acct_6
union all
select user_id,name,user_nm,mail_pass,mail_srvr,mail_addr,mail_token,acct_type
from epw3.epw3_user_acct_7
union all
select user_id,name,user_nm,mail_pass,mail_srvr,mail_addr,mail_token,acct_type
from epw3.epw3_user_acct_8
union all
select user_id,name,user_nm,mail_pass,mail_srvr,mail_addr,mail_token,acct_type
from epw3.epw3_user_acct_9;

You haven't set up your Criteria to handle duplicates. Set its result transformer to DISTINCT_ROOT_ENTITY.

Hi

I tried the ResultPerformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY) ...but now its getting only one record that is from the first table and ignoring rest of the data from other tables as they were defined in the view creation did not come , in actual scenario there are 10 tables each having a record against a unique userId , ideally the list should contain 10 objects with one object from each of the tables.

Earlier the count of records that were populated in the list was correct as per the query but all object were from the first table.

I also have implemented the equals and hashcode in the POJO mapped to the view as

public boolean equals(Object obj){
if(this == obj){
return true;
}
if(obj == null){
return false;
}
if(!(obj instanceof UserAcct)){
return false;
}
final UserAcct userAcct = (UserAcct)obj;
if(mailAddr.equals(userAcct.getMailAddr()) &&
uuid.equals(userAcct.getUuid())){
return true;
}else{
return false;
}
}

public int hashCode() {
return mailAddr.hashCode()+ uuid.hashCode();
}

but iam still getting duplicate results.....what could be the reason....

Also iam using ehcache for caching the UserAcct(POJO mapped to the view) object and the logoutput shows that

19:15:48,530 [DEBUG] [ehcache.Cache][isExpired] [EPW3_USER_ACCT_0 now: 1148910348530]
19:15:48,530 [DEBUG] [ehcache.Cache][isExpired] [EPW3_USER_ACCT_0 Creation Time: 1148910347687 Next To Last Access Time: 0]
19:15:48,530 [DEBUG] [ehcache.Cache][isExpired] [EPW3_USER_ACCT_0 mostRecentTime: 1148910347687]
19:15:48,530 [DEBUG] [ehcache.Cache][isExpired] [EPW3_USER_ACCT_0 Age to Idle: 120000 Age Idled: 843]

it is showing that cache is created for the first table is that the reason Iam getting all records from the first table only....do I need to declare cache details for the other POJO objects as UserAcct0....UserAcct9 in the ehcache.xml ...i haven't declared one for UserAcct0 though at the moment ...but still i see the log output about the first table

I have only made entry for the POJO mapped to view in the ehcache.xml as
<cache name="net.earthlink.epw3.model.UserAcct"
maxElementsInMemory="1000"
eternal="false"
timeToIdleSeconds="1800"
timeToLiveSeconds="36000"
diskExpiryThreadIntervalSeconds="120"
overflowToDisk="false"
/>

Please help me resolve this...

Rather than battling through that raft of illegibility (see my sig for help on making your postings legible), I'll pose some questions of my own.

You've got 10 tables that are identical. Each table has distinct rows: a given ID cannot exist in more than one table (how are you enforcing that, as a matter of interest?). You've got one row in each table, each with a different ID. You've got one view, unioning all 10 tables together. You've got one mapping, referring to the view and not to any of the tables. Am I right so far?

You want a Criteria query that returns a list of objects created from all rows in the view, but it's returning a row from one of the tables, repeated 10 times. Am I still right?

If all my suppositions are correct, then I believe that the simple Criteria you posted should work. Seeing as it doesn't, something that I've said here must be false. So I guess I'll wait until you correct me, and I'll post again then.

Unfortunately I'm off home now. I'll respond in the morning.

Can you post (in code tags) your mapping, too?