aspose file tools*
The moose likes Object Relational Mapping and the fly likes Iam getting duplicate entries when retreiving collections from a Database View Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Iam getting duplicate entries when retreiving collections from a Database View" Watch "Iam getting duplicate entries when retreiving collections from a Database View" New topic
Author

Iam getting duplicate entries when retreiving collections from a Database View

Vaibhav Bajpai
Greenhorn

Joined: Feb 27, 2006
Posts: 3
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;
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


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.


How do you know that user_id is unique across all ten tables? It will need to be unique in the view otherwise you will get duplicates in your results.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Vaibhav Bajpai
Greenhorn

Joined: Feb 27, 2006
Posts: 3
Ya...thanks Paul for pointing that out....i have now created a composite id definition in the mapping file and it works fine now
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Iam getting duplicate entries when retreiving collections from a Database View