Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Duplicate entries when retreiving collections using Hibernate from a Database View

Posted on 2006-05-29
15
Medium Priority
?
404 Views
Last Modified: 2012-06-21
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;
0
Comment
Question by:vaibhavbajpai
  • 7
  • 5
  • 3
15 Comments
 
LVL 92

Expert Comment

by:objects
ID: 16783728
Make sure you have implemented equals() and hashCode() correctly in your bean.
0
 

Author Comment

by:vaibhavbajpai
ID: 16784169
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
 
LVL 6

Expert Comment

by:phuocnh
ID: 16784558
I think you should use "UNION" instead of "UNION ALL".
Phuoc H. Nguyen
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:vaibhavbajpai
ID: 16787341
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
 
LVL 6

Expert Comment

by:phuocnh
ID: 16787362
No "UNION ALL" make duplicate rows.
Why don't you try to use "UNION"?
Phuoc H. Nguyen
0
 
LVL 92

Expert Comment

by:objects
ID: 16787363
can you post your bean and its mapping
0
 

Author Comment

by:vaibhavbajpai
ID: 16787376
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
 
LVL 92

Expert Comment

by:objects
ID: 16787394

You're telling hibernate that uuid is the primary key but thats not the case is it?
0
 

Author Comment

by:vaibhavbajpai
ID: 16787410
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
 

Author Comment

by:vaibhavbajpai
ID: 16787417
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
 
LVL 92

Expert Comment

by:objects
ID: 16787420
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
 

Author Comment

by:vaibhavbajpai
ID: 16787432
Can a view have a primary key....
0
 
LVL 92

Accepted Solution

by:
objects earned 1500 total points
ID: 16787458
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
 
LVL 6

Expert Comment

by:phuocnh
ID: 16787473
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
 

Author Comment

by:vaibhavbajpai
ID: 16787734
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Suggested Courses
Course of the Month13 days, 15 hours left to enroll

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question