Duplicate entries when retreiving collections using Hibernate from a Database View

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;
vaibhavbajpaiAsked:
Who is Participating?
 
objectsCommented:
yes, whether its a view or table is irrelevant to hibernate. you need to tell hibernate what makes a row unique.

you may need to define a composite id

http://www.hibernate.org/hib_docs/reference/en/html/components.html
http://www.xylax.net/hibernate/compositeid.html
0
 
objectsCommented:
Make sure you have implemented equals() and hashCode() correctly in your bean.
0
 
vaibhavbajpaiAuthor Commented:
I have implemented the equals and hashcode in the POJO 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 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 only 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....i haven't declared one for UserAcct0 though...but still i see the log output abt the first table

Please help me resolve this...
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Nguyen Huu PhuocSenior ManagerCommented:
I think you should use "UNION" instead of "UNION ALL".
Phuoc H. Nguyen
0
 
vaibhavbajpaiAuthor Commented:
since all the columns in the tables are identical , it was easy to have a pOJO class with the thos properties and with UNION ALL we can have all the data under a generic column structure applicable to all the tables...

I don't think this is the problem becuase the query generated by Hibernate to retreive from the view exceutes correctly in the query browser for Mysql
0
 
Nguyen Huu PhuocSenior ManagerCommented:
No "UNION ALL" make duplicate rows.
Why don't you try to use "UNION"?
Phuoc H. Nguyen
0
 
objectsCommented:
can you post your bean and its mapping
0
 
vaibhavbajpaiAuthor Commented:
This is the bean object :-

public class UserAcct implements Serializable{

      private static final long serialVersionUID = 1L;

      private boolean passwdChange = false;
      private String uuid;
      private String acctName;
      private String userName;
      private String mailSrvr;
      private String mailAddr;
      private String mailToken;
      private byte[] mailPasswd;
      private String decryptedPasswd;
      private String oldElnkPasswd;
      //private String mailPasswd;
      private String acctType;
      
    public UserAcct() {
            
      }

      public UserAcct(String uuid, String acctName, String userName, String mailSrvr,
                  String mailAddr, String mailToken, String mailPasswd,
                  String acctType) {
            this.uuid = uuid;
            this.acctName = acctName;
            this.userName = userName;
            this.mailSrvr = mailSrvr;
            this.mailAddr = mailAddr;
            this.mailToken = mailToken;
            this.mailPasswd = mailPasswd.getBytes();
            //this.mailPasswd = mailPasswd;
            this.acctType = acctType;
      }
      
      /**
       * @return Returns the passwdChange.
       */
      public boolean isPasswdChange() {
            return passwdChange;
      }

      /**
       * @param passwdChange The passwdChange to set.
       */
      public void setPasswdChange(boolean passwdChange) {
            this.passwdChange = passwdChange;
      }
      
      /**
       * @return Returns the uuid.
       */
      public String getUuid() {
            return uuid;
      }

      /**
       * @param uuid The uuid to set.
       */
      public void setUuid(String uuid) {
            this.uuid = uuid;
      }
      
      /**
       * @return Returns the acctName.
       */
      public String getAcctName() {
            return acctName;
      }

      /**
       * @param acctName The acctName to set.
       */
      public void setAcctName(String accountName) {
            this.acctName = accountName;
      }

      /**
       * @return Returns the acctType.
       */
      public String getAcctType() {
            return acctType;
      }

      /**
       * @param acctType The acctType to set.
       */
      public void setAcctType(String accountType) {
            this.acctType = accountType;
      }

      /**
       * @return Returns the mailAddr.
       */
      public String getMailAddr() {
            return mailAddr;
      }

      /**
       * @param mailAddr The mailAddr to set.
       */
      public void setMailAddr(String mailAddress) {
            this.mailAddr = mailAddress;
      }

      /**
       * @return Returns the mailPasswd.
       */
      public byte[] getMailPasswd() {
            return mailPasswd;
      }

      /**
       * @param mailPasswd The mailPasswd to set.
       */
      public void setMailPasswd(byte[] mailPasswd) {
            this.mailPasswd = mailPasswd;
      }

      /**
       * @return Returns the decryptedPasswd.
       */
      public String getDecryptedPasswd() {
            return decryptedPasswd;
      }

      /**
       * @param decryptedPasswd The decryptedPasswd to set.
       */
      public void setDecryptedPasswd(String decryptedPasswd) {
            this.decryptedPasswd = decryptedPasswd;
      }
      
      /**
       * @return Returns the oldElnkPasswd.
       */
      public String getOldElnkPasswd() {
            return oldElnkPasswd;
      }

      /**
       * @param oldElnkPasswd The oldElnkPasswd to set.
       */
      public void setOldElnkPasswd(String oldElnkPasswd) {
            this.oldElnkPasswd = oldElnkPasswd;
      }

      /**
       * @return Returns the mailSrvr.
       */
      public String getMailSrvr() {
            return mailSrvr;
      }

      /**
       * @param mailSrvr The mailSrvr to set.
       */
      public void setMailSrvr(String mailServer) {
            this.mailSrvr = mailServer;
      }

      /**
       * @return Returns the mailToken.
       */
      public String getMailToken() {
            return mailToken;
      }

      /**
       * @param mailToken The mailToken to set.
       */
      public void setMailToken(String mailToken) {
            this.mailToken = mailToken;
      }

      /**
       * @return Returns the userName.
       */
      public String getUserName() {
            return userName;
      }

      /**
       * @param userName The userName to set.
       */
      public void setUserName(String userName) {
            this.userName = userName;
      }
      
      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();
      }
}

This is the mapping file for the view :-
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
      <class name="net.earthlink.epw3.model.UserAcct"
               table="EPW3_USER_ACCTS">
            <meta attribute="implement-equals">true</meta>
            <cache usage="read-write"/>
            <id name="uuid"
                column="USER_ID"
                type="java.lang.String">
                  <meta attribute="use-in-tostring">true</meta>
                  <generator class="assigned" />
            </id>
            <property name="acctName"
                          type="java.lang.String"
                          column="NAME" />
            <property name="userName"
                          type="java.lang.String"
                          column="USER_NM" />
            <property name="mailPasswd"
                          type="binary"
                          column="MAIL_PASS" />
            <property name="mailSrvr"
                          type="java.lang.String"
                          column="MAIL_SRVR" />
            <property name="mailAddr"
                          type="java.lang.String"
                          column="MAIL_ADDR" />
            <property name="mailToken"
                          type="java.lang.String"
                          column="MAIL_TOKEN" />
            <property name="acctType"
                          type="java.lang.String"
                          column="ACCT_TYPE" />
      </class>
</hibernate-mapping>

Also we have a similar mapping and bean structure for the rest of the 10 tables of which the view is created
0
 
objectsCommented:

You're telling hibernate that uuid is the primary key but thats not the case is it?
0
 
vaibhavbajpaiAuthor Commented:
It is a view and it can have mutkiple records from the same UUID...if I don't give the id tag hibernate throws an error

Basically in a gist :-
I've got 10 tables that are identical. Each table has distinct rows: a given ID cannot exist in more than one table. I've got one row in each table, each with a different ID. I've got one view, unioning all 10 tables together. I've got one mapping, referring to the view and separate mapping files for all of the of the other tables.
0
 
vaibhavbajpaiAuthor Commented:
Also I have a scenario where there 10 records for a single UUID in 10 tables......iam queryin the view to have all 10 records instead of hitting each of the tables directly

and when iam using criteraquery on the view its only getting me the data from the first table and duplicates the same data   to 9 other objects in the list while retrieving...though the count of records retrieved is correct

0
 
objectsCommented:
you need to tell hibernate what the correct primary key for your view is.

might also change equals() to check equality of all properties

http://www.hibernate.org/109.html
0
 
vaibhavbajpaiAuthor Commented:
Can a view have a primary key....
0
 
Nguyen Huu PhuocSenior ManagerCommented:
userid is a primary key in every table, but you cann't be sure that it is unique in the view.
When you use "UNION ALL" it return all rows which meet the query in the member table.
Read more
http://www.w3schools.com/sql/sql_union.asp
Phuoc H. Nguyen
0
 
vaibhavbajpaiAuthor Commented:
Thanks objects the composite key was the issue ....i declared a composite key of uuid and mailAddr and overrided the equals method 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();
      }
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.